Perl Win32::OLE csv2excel
戻る
<![CDATA[
# csv2excel.pl 07.11.28
# 複数CSVファイルを1つのexcelファイルに縮約
use strict;
use Win32::OLE;
use Win32::OLE::Const 'Microsoft Excel';
my(%t,$array);
# use existing instance if Excel is already running
eval {$t{ex} = Win32::OLE->GetActiveObject('Excel.Application')};
die "Excel not installed" if $@;
unless (defined $t{ex}) {
$t{ex} = Win32::OLE->new('Excel.Application', sub {$_[0]->Quit;})
or die "Oops, cannot start Excel";
}
# get a new workbook
$t{book} = $t{ex}->Workbooks->Add;
# write to a particular cell
$t{sheet} = $t{book}->Worksheets(1);
$t{sheet}->Cells(2,2)->{Value} = "foo";
# write a 2 rows by 3 columns range
$t{sheet}->Range("A8:C9")->{Value} = [[ undef, 'Xyzzy', 'Plugh' ],
[ 82, 'Perl', 3.1415 ]];
# 線を引く
$t{sheet}->Range("B5:D5")->Borders(xlEdgeBottom)->{LineStyle} = xlDouble;
# print "XyzzyPerl"
$array = $t{sheet}->Range("A7:C9")->{Value};
for (@$array) {
for (@$_) {
print defined($_) ? "$_|" : "<undef>|";
}
print "\n";
}
$t{book}->Sheets->Add("C:\\test\\g.csv"); # ==>失败
# save and exit
$t{book}->SaveAs("C:\\test\\test.xls");
undef $t{book};
undef $t{ex};
-------------------------------------------------------------
Sub Macro1()
'
' Macro1 Macro
'
Workbooks.Open Filename:="C:\test\g.csv"
Windows("g.csv").Activate
Cells.Select
Selection.Copy
Windows("test.xls").Activate
Cells.Select
ActiveSheet.Paste
Sheets("Sheet1").Select
Sheets("Sheet1").Name = "g"
End Sub
----------------------------------->
# csv2excel2.pl 07.12.04
# 複数CSVファイルを1つのexcelファイルに縮約
use strict;
use Win32::OLE;
use Win32::OLE::Const 'Microsoft Excel';
my(%t,$array);
# use existing instance if Excel is already running
eval {$t{ex} = Win32::OLE->GetActiveObject('Excel.Application')};
die "Excel not installed" if $@;
unless (defined $t{ex}) {
$t{ex} = Win32::OLE->new('Excel.Application', sub {$_[0]->Quit;})
or die "Oops, cannot start Excel";
}
# open a file
$t{bookg} = $t{ex}->Workbooks->Add("C:\\test\\g.csv");
$t{sheetg} = $t{bookg}->Worksheets("g");
$t{sheetg}->Cells->Select;
$t{sheetg}->Copy;
$t{book} = $t{ex}->Workbooks->Add();
$t{sheet} = $t{book}->Worksheets("Sheet1");
$t{sheet}->Cells->Select;
$t{sheet}->Paste;
$t{sheet}->{Name} = 'g'; # OK,给Sheet取名成功
$t{book}->SaveAs("C:\\test\\ttt.xls");
undef $t{bookg};
undef $t{book};
undef $t{ex};
# 実行結果
# クリップボードに保存されているデータの大きさや形が、指定された領域と異なります。貼り付けますか？
# この場所に'C:\test\ttt.xls'という名前のファイルが既にあります。置き換えますか?
# 'Book1.xls'への変更を保存しますか?
# Book1.xlsに正しくファイルがセーブされた。ttt.xlsにコピーされなかった。
-------------------------------------------------------------------------
# test2.pl 07.12.06
use strict;
use Win32::OLE;
use Win32::OLE::Enum;
use Win32::OLE::Const 'Microsoft Excel';
my(%t,$sheet,$Enum);
# EXCELを起動
$t{ex} = Win32::OLE->new('Excel.Application') or die "oops\n";
# 「xxへの変更を保存しますか？」が出ない
$t{ex}->{DisplayAlerts} = 'False';
$t{book1} = $t{ex}->Workbooks->Open("C:\\test\\n.csv");
$t{sheet1} = $t{book1}->Worksheets('n');
$t{cells} = $t{sheet1}->Cells->Select;
#$Enum = Win32::OLE::Enum->new($t{sheet1});
#$t{book1} = $t{ex}->Workbooks->Open("C:\\test\\n.csv");
#$t{sheet1} = $t{book1}->Worksheets('n');
#$Enum = Win32::OLE::Enum->new($t{sheet1});
#my @Sheets = $Enum->All;
$t{ex}->Workbooks(1)->Worksheets->Paste($t{cellsc});
$t{ex}->Workbooks(1)->SaveAs("C:\\test\\ttt.xls");
#$t{ex}->{Visible} = 1; # 表示する
$t{ex}->quit();
__END__;
------------>执行结果:ttt.xls文件里名为ttt的Sheet生成。"$t{ex}->quit()"のせい？
-------------------------------------------------------------------------
成功!
# test2.pl 07.12.06
use strict;
use Win32::OLE qw(in with);
use Win32::OLE::Const 'Microsoft Excel';
my(%t,$sheet,$Enum);
# EXCELを起動
$t{ex} = Win32::OLE->new('Excel.Application') or die "oops\n";
# 「xxへの変更を保存しますか？」が出ない
$t{ex}->{DisplayAlerts} = 'False';
$t{ex}->Workbooks->OpenText({Filename => "C:\\test\\n.csv"});
#$t{ex}->ActiveSheet->Cells->{NumberFormatLocal} = "@";
$t{ex}->ActiveWorkbook->SaveAs({Filename => "C:\\test\\ttt.xls", Fileformat => xlWorkbookNormal});
$t{ex}->ActiveWorkbook->Close();
$t{ex}->Quit();
__END__;
------------------------------------------------------------------------------
# test3.pl 07.12.07
use strict;
use Win32::OLE qw(in with);
use Win32::OLE::Const 'Microsoft Excel';
my(%t);
Win32::OLE->Option(Warn => 3);
# EXCELを起動
$t{ex} = Win32::OLE->new('Excel.Application') or die "oops\n";
# 「xxへの変更を保存しますか？」が出ない
$t{ex}->{DisplayAlerts} = 'False';
eval {
$t{ex}->Workbooks->OpenText({Filename => "C:\\test\\x.csv"});
};
if ($@) {
die "Can't open the file g.csv.\n";
}
# シートを選択
$t{ex}->ActiveSheet->Cells->Select;
# シートをコピー
$t{ex}->Selection->Copy;
# 新しいBookを作成
$t{book1} = $t{ex}->Workbooks->Add;
# 新しいSheetを追加
$t{sheet1} = $t{book1}->Worksheets(1);
# シートを選択(?)
#$t{sheet1}->Cells->Select;
# Paste
$t{sheet1}->Paste;
# シート名を変更
$t{sheet1}->{name} = 'g';
$t{ex}->Workbooks->OpenText({Filename => "C:\\test\\y.csv"});
$t{ex}->ActiveSheet->Cells->Select;
$t{ex}->Selection->Copy;
$t{sheet1} = $t{book1}->Worksheets(2);
#$t{sheet1}->Cells->Select;
$t{sheet1}->Paste;
$t{sheet1}->{name} = 'n';
#$t{ex}->ActiveSheet->Cells->{NumberFormatLocal} = "@";
#$t{ex}->ActiveWorkbook->SaveAs({Filename => "C:\\test\\t3.xls", Fileformat => xlWorkbookNormal});
$t{book1}->SaveAs({Filename => "C:\\test\\t3.xls", Fileformat => xlWorkbookNormal});
$t{book1}->Close();
$t{ex}->Quit();
print "Finished. The output file is t3.xls.\n";
__END__;
]]>
戻る