perl操作excel3

#!/usr/bin/perl

use strict;

use warnings;

 

 

use Win32::OLE qw(in with);

use Win32::OLE::Const 'Microsoft Excel';

use Win32::OLE::NLS qw(:LOCALE :TIME);

my $Excel = Win32::OLE->GetActiveObject('Excel.Application')

        || Win32::OLE->new('Excel.Application', 'Quit');

 

my$excelfile='E:\学习程序\perl\excelfile.xls';

 

#关闭警告信息，例如保存等,不跳出确认窗口

$Excel->{DisplayAlerts}=0;  

 

#创建一个workbook

#my $Book=$Excel->Workbooks->Add();

#   $Book->SaveAs($excelfile); # 增加一个workbook，然后保存

 

#直接打开一个excel 若存在

my$Book=$Excel->Workbooks->Open($excelfile);

 

#创建一个worksheet对象

my $Sheet = $Book->Worksheets("Sheet1");

#   $Sheet->Activate();

#   $Sheet->{Name} = "DidItInPerl";

 

#插入数据

my ($mday,$mon,$year) = (localtime(time))[3,4,5];

$year += 1900;

$mon += 1;

my $str = $year."/".$mon."/".$mday;

 

#Range  行方法

$Sheet->Range("C1")->{Value}=$str;

$Sheet->Range("D1")->{Value}="今天的日期";

 

#迭代插入数据

foreach my$x (1 ..50) {

    my$range="A".$x;

    $Sheet->Range($range)->{Value}="这是第$range行";

}

 

 

#循环访问sheet

my $sheetcnt = $Book->Worksheets->Count();

foreach (1..$sheetcnt){

   print "\t" .$Book->Worksheets($_)->{Name} ."\n";

}

 

foreach my $Sheet(in $Book->{Worksheets}){

   print "\t" .$Sheet->{Name} ."\n";

}

 

 

#找到最后一行 最后一列方法

my $LastRow = $Sheet->UsedRange->Find({What=>"*",

    SearchDirection=>xlPrevious,

    SearchOrder=>xlByRows})->{Row};

 

my $LastCol = $Sheet->UsedRange->Find({What=>"*",

                  SearchDirection=>xlPrevious,

                  SearchOrder=>xlByColumns})->{Column};

print "最后一列:",$LastCol,"\n";

print "最后一行:",$LastRow,"\n";

 

 

#读取第一行到最后一行数据

print "#" x80,"\n";

print "读取A列第一行到最后一行数据\n";

my$tmp;

foreach my$last_data (1..$LastRow) {

    $tmp=$Sheet->Range("A".$last_data)->{Value};

    print "第$last_data行数据:",$tmp,"\n";

}

 

 

#学习来源perlmonks.org

 

#############################################################