Perl對Excel檔案的各種操作

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;
}