Perl对Excel文件的各种操作

mgjj

　　use strict;
use Cwd;
use strict 'subs';
use Win32::OLE qw(in);
use Win32::OLE::Variant;
$Win32::OLE::Warn = 3; # die on errors...
　　
my@files=("1.xls","2.xls","3.xls","4.xls","5.xls","6.xls");
foreach my$file (@files)
{
&EditExcel($file);
alarm (1500);
}
　　sub EditExcel
{
my$dir = getcwd;
my$fullpath = "$dir/$_[0]";
print $fullpath."/n";
#############################initialize###############################
my$excel;
# use existing instance if Excel is already running
eval
{
Win32::OLE->GetActiveObject('Excel.Application');
};
if ($@)
{
print "Excel not installed $@";
}
unless (defined $excel)
{
$excel = Win32::OLE->new('Excel.Application', sub {$_[0]->Quit;}) or print "cannot start Excel.";
}
alarm (500);
#############################current date###############################
my$sec,my$min,my$hour,my$mday,my$month,my$year,my$wday,my$yday,my$isdst;
($sec,$min,$hour,$mday,$month,$year,$wday,$yday,$isdst)=localtime(time());
$year+=1900;
$month+=1;
#print "$year-$month-$mday";
　　#$excel->{Visible} = 0; #show excel
######################### workbook #################################
# open a workbook
my$book = $excel->Workbooks->Open($fullpath);
############################ sheets ##################################
my$sheets = $book->Worksheets;
############################## Sheet ####################################
#my$sheet = $book->Worksheets(1);
my$curr_mon = sprintf("%-02.2d月", $month);
my$curr_day = sprintf("%-02.2d", $mday);
my$sheet = $sheets->Item($curr_mon);
# activate the appointed page
$sheet->Activate();
############################## Set value ####################################
#set YRP
$sheet->Cells(2,1)->{Value} = "YRP";
$sheet->Cells(3,2)->{Value} = "Bug解析";
# row counts and column counts of cells used
my$row_counts= $sheet->{UsedRange}->{Rows}->{Count};
my$column_counts = $sheet->{UsedRange}->{Columns}->{Count};
for( my$col = 1; $col <= $column_counts; $col++)
{
if ($sheet->Cells(1,$col)->{Value} =~ /^$curr_day$/i )
{
$sheet->Cells(3,$col)->{Value} = 8;
$sheet->Cells(4,$col)->{Value} = 100;
print "--Set value OK!!--/n";
last;
}
}
##########################save#######################################
# save and exit
$book->Save();
$book->Close();
undef $book;
undef $excel;
}