excel write
戻る
<![CDATA[
#!C:/perl/bin/perl
# excelwrite.pl
# 07.11.27
use strict;
use Win32::OLE qw(in with);
use Win32::OLE::Variant;
use Win32::OLE::Const 'Microsoft Excel';
use Encode; # set perl's OLE module to return Unicode
Win32::OLE->Option(CP => Win32::OLE::CP_UTF8, Warn=>3);
my (%t,$n,$e_ref,@fld);
# Read data (unicode)
open(IN,"C:\\temp\\map.txt") or die "Can't open the file map.txt.\n";
while(<IN>){
last if (/^END/);
if ( /^ITEM/ ) {
@fld = split;
push(@{ $$e_ref{NO} },$fld[1]);
push(@{ $$e_ref{Ns} },decode("utf8",$fld[2]));
push(@{ $$e_ref{Cs} },decode("utf8",$fld[3]));
}
}
close(IN);
# データ生成
for $n ( 1 .. 20 ) {
$t{name} = 'NO' . $n;
push(@{ $$e_ref{list} },$t{name});
$$e_ref{input}{$t{name}} = $n+100;
}
# Excel ファイルに出力
($e_ref) = write_excel($e_ref);
sub write_excel {
my($e_ref) = @_;
my(%t,$n);
# Get the object
$t{ex} = Win32::OLE->new('Excel.Application') or die "oops\n";
$t{ex}->{DisplayAlerts} = 'False';
# open the sample file map.xls
$t{book} = $t{ex}->Workbooks->Open("C:\\TEMP\\map.xls");
# write to a particular cell
$t{sheet} = $t{book}->Worksheets('Sheet1');
# EXCELファイルにデータを書き込む
for $n ( 0 .. $#{ $$e_ref{list} } ) {
$t{name1} = $$e_ref{list}[$n];
$t{value1} = $$e_ref{input}{$t{name1}};
$t{X1} = 3;
$t{Y1} = 10+$n;
$t{sheet}->Cells($t{Y1},$t{X1})->{'Value'} = $t{value1};
}
$t{Y1}++;
$t{XX} = 'C' . $t{Y1};
$t{sheet}->Range("$t{XX}:$t{XX}")->Borders(xlEdgeBottom)->{LineStyle} = xlDouble;
$t{Y1}++;
$t{C1} = $$e_ref{Cs}[0];
$t{sheet}->Cells($t{Y1},$t{X1})->{'Value'} = Variant(VT_BSTR, $t{C1}); # 中国語(Unicode)OK
# Save the excel file
$t{book}->SaveAs("C:\\TEMP\\map1.xls");
undef $t{book};
undef $t{ex};
print "The output file is C:\\TEMP\\map1.xls\n";
return($e_ref);
}
1;
__END__
]]>
戻る