往excel 表格里填数据

 

#!C:\Perl64\bin\perl -w
#[e-mail]
#This perl script is to auto fill the excel with the date from *.rpt file

   use Win32::OLE;
   use Win32::OLE qw(in with);
   use Win32::OLE::Variant;
   use Win32::OLE::Const 'Microsoft Excel';

my @input_file ="";
my $n = 0;

my $input_dir = "";
 # print "we are dividing the Sheet into 4 sheets, just a few seconds\n";
        # use existing instance if Excel is already running
        eval {$excel = Win32::OLE->GetActiveObject('Excel.Application')};
        die "Excel not installed" if $@;
        #$excel -> { Visible} = 1;
        unless (defined $excel) {
            $excel = Win32::OLE->new('Excel.Application', sub {$_[0]->Quit;})
                    or die "Oops, cannot start Excel";
        }

        # get a new workbook
       my $book = $excel->Workbooks->Open
          ('C:\Users\zhangbo\My Documents\jue_tang\zx222050_top_DDR_skew_20121214.xlsx'); # open Excel file
   
&find_fileindir("C:/Users/zhangbo/My Documents/jue_tang/"); # find all the rpt log files and put it into @files.

sub find_fileindir(){
  local($dir) = @_;
  opendir(DIR,"$dir"|| die "can't open this$dir");
  local @files =readdir(DIR);
  closedir(DIR);
 
  for $file (@files){
  next if($file=~m/\.$/ || $file =~m/\.\.$/);
  if (-f "$dir/$file" && $file =~ m/\.rpt/){
       #print "$dir\/$file \n";
    $input_file[$n] ="$dir/$file";
    $n++;
   
  }
  elsif(-d "$dir/$file"){
           find_fileindir("$dir/$file" );
  }
  }
 
}
my $seet=1;
foreach $file (@input_file ){

print "$file\n";
#open IN, "$file" or die "Can't open the input file ! \n";
#open OUT, ">$file.txt" or die " Can't open the output file ! \n";
#$^I = "";
#while(<IN>){

#if ($_ =~ /www\.lsi/){
 #s/www\.lsi/wan\.lsi/;
#print OUT;
# $_,"\n";

my $line=0;
my $param_line=0;
my @date =();
my @param =();
my $stat=0;
my $how_many_date=0;
open(IN,"$file")||die("can not open the file!");
while(<IN>){
if ($_ =~ /\w+\s(max|min)\s(rise|fall)/){ #process FastColdCB max rise part
 #$FastColdCB_max_rise_stat=1;
#print $&,"\n";
$stat=1;
chomp($_);
$param[$param_line]=$_;
$param_line++;
next;
}# $_,"\n";
if ( $_=~/\d+/ && $stat == 1 ){
#rint "$_\n";
chomp($_);
$date[$line]=$_;
$line++;
}

}
print "all date line is $line\n";
print "pram line is $param_line\n";
my $date_line=$line/$param_line;
print "my date line is $date_line\n";
#rint "@date\n";
#print "@param\n";
 print "we are dividing the Sheet into 4 sheets, just a few seconds\n";
      
my $i=0;
my $h=0;
my $row=0;
my $col=0;
my $statu=0;
#print $date_line,"\n";
#print $param_line,"\n";
#
#
#
#
#
#
$sheet = $book->Worksheets($seet); #open sheet $seet begin from 1 to the last sheet
my $first_part=$param_line/2;
my $second_part_start=$first_part+1;
print "my sec part start is $second_part_start\n";
foreach(1..$first_part){
#print $param[$i];
print $param[$_-1],"\n";

if ($_%2==0){# do nothing, next step
$col=$col+1;
}
else{
$col=$_*2;
}
print "each row and col is $col\n";
foreach(1..$date_line){
#chomp($_);
$sheet->Cells($_+2,$col)->{Value} = $date[$h];
print $date[$h],"\n";
print "$_+2,$col\n";
$h++;
}
print "\n";
}
my $col_stat=0;
foreach($second_part_start .. $param_line ){
$col=$_;
print "param is ",$_,"\n";
print $param[$_-1],"hello","\n";
print "my col is $col \n";
if ($_%2==0){# do nothing, next step
$col=$col_stat+1;
}
else{
$col=$_-12;
$col=$col*2;
$col_stat=$col;
}
print "each row and col is $col\n";
foreach(1..$date_line){
chomp($_);
$sheet->Cells($_+17,$col)->{Value} = $date[$h];
print $date[$h],"\n";
print "$_+2,$col\n";
$h++;
}
print "\n";
}
$seet++;#will change to another sheet
}

#save to excel
 $excel-> {DisplayAlerts} = 0;
        $book->SaveAs('C:\Users\zhangbo\Documents\jue_tang\zx222050_top_DDR_skew_201212141.xlsx',51);
      undef $book;
      undef $excel;