CGIによるExcel出力
戻る
VBAサンプル(test1.lzh)
<![CDATA[
# 指定セルに下線を描く
# addline.pl
use strict;
use Win32::OLE qw(in with);
use Win32::OLE::Const 'Microsoft Excel';
my($ex,$book,$sheet);
my $ex = Win32::OLE->new('Excel.Application') or die "oops\n";
# get a new workbook
$book = $ex->Workbooks->Add;
# write to a particular cell
$sheet = $book->Worksheets(1);
$sheet->Cells(3,2)->{Value} = "foo";
$sheet->Cells(3,3)->{Value} = "test";
with ( my $border = $sheet->Range("B3:C3")->Borders(xlEdgeBottom),
LineStyle => xlDouble,
Weight => xlThick,
ColorIndex => xlAutomatic);
$sheet->Range("B5:C5")->Borders(xlEdgeBottom)->{LineStyle} = xlDouble;
# save and exit
$book->SaveAs( "C:/temp/test.xls" );
undef $book;
undef $ex;
---------------------------------------------------------------
sub msc023 {
my $self = shift;
my ($ex,$sheet,$book,%t,$n,$n1,@fld,$ref);
# Get CGI query object
my $q = $self->query();
my $template = $self->load_tmpl('msc023.htm') || die "error loading tmpl";
# Read data
open(IN,"C:\\www\\cgi-bin\\map1.txt") or die "Can't open the file map1.txt.\n";
@{ $t{list} } = qw/REF DATE NAME THANK LIST1 LIST2 LIST3 LIST4 LIST5 LIST6 LIST7/;
@{ $t{X0} } = qw/B B B B B E B B B A F/;
@{ $t{Y0} } = qw/4 5 9 13 17 17 23 27 31 32 36/;
@{ $t{Direction} } = qw/Y Y Y Y Y Y Y Y X X Y/;
while(<IN>){
last if (/^END/);
chomp;
@fld = split(/==>/);
for $n ( 0 .. $#{ $t{list} } ) {
$t{list1} = $t{list}[$n];
if ( /^$t{list1}/ ) {
push(@{ $$ref{$t{list1}} },$fld[1]);
next;
}
}
}
# EXCELの座標を確定
for $n ( 0 .. $#{ $t{list} } ) {
$t{x1} = $t{X0}[$n];
$t{y1} = $t{Y0}[$n];
$t{XY1} = $t{x1} . $t{y1};
$t{list1} = $t{list}[$n];
$t{leng1} = $#{ $$ref{$t{list1}} };
$t{D1} = $t{Direction}[$n];
if ( $t{D1} eq 'Y' ) {
$t{y1} = $t{y1} + $t{leng1};
$t{XY2} = $t{x1} . $t{y1};
} else {
for $n1 ( 1 .. $t{leng1} ) {
++($t{x1});
}
$t{XY2} = $t{x1} . $t{y1};
}
$t{XY} = $t{XY1} . ':' . $t{XY2};
push(@{ $t{XYs} },$t{XY});
}
# Get the object
$ex = Win32::OLE->new('Excel.Application', sub {$_[0]->Quit;}) or die "Oops, cannot start Excel";
$ex->{DisplayAlerts} = 'False';
# open the sample file map.xls
$book = $ex->Workbooks->Open("C:\\database\\samples\\sample_ENQ.xls");
# write to a particular cell
$sheet = $book->Worksheets("Sheet1");
for $n ( 0 .. $#{ $t{list} } ) {
$t{XY} = $t{XYs}[$n];
$t{list1} = $t{list}[$n];
$t{D1} = $t{Direction}[$n];
if ( $t{D1} eq 'X' ) {
$sheet->Range("$t{XY}")->{'Value'} = \@{ $$ref{$t{list1}} };
} else {
$t{x1} = $t{X0}[$n];
$t{y1} = $t{Y0}[$n];
for $n1 ( 0 .. $#{ $$ref{$t{list1}} } ) {
$t{y2} = $t{y1} + $n1;
$t{XY} = $t{x1} . $t{y2};
$sheet->Range("$t{XY}")->{'Value'} = $$ref{$t{list1}}[$n1];
}
}
}
# Save the excel file
$book->SaveAs("C:\\database\\output\\ENQ.xls");
undef $book;
undef $ex;
return $template->output;
}
1;
]]>
戻る