Perl 操作Excel Monday, October 12, 2009

use strict;

use strict 'subs';

use Win32::OLE qw(in);

use Win32::OLE::Variant;

$Win32::OLE::Warn = 3;         # die on errors...

#############################initialize###############################

my$ex;

# use existing instance if Excel is already running

eval {$ex = Win32::OLE->GetActiveObject('Excel.Application')};

die "Excel not installed" if $@;

unless (defined $ex) {

    $ex = Win32::OLE->new('Excel.Application', sub {$_[0]->Quit;})

            or die "Oops, cannot start Excel";

}

$ex->{Visible} = 1; #当调用下面的函数时候，excel是否显示

######################### workbook #################################

# get a new workbook

#my$book = $ex->Workbooks->Add;

# open a workbook

my$book = $ex->Workbooks->Open("t:/test.xls");

############################ sheets ##################################

my$sheets = $book->Worksheets;

my$sheets_count = $sheets->Count();

print "sheets counts: $sheets_count\n";

foreach my$test(in $sheets){

       print $test->Name();

}

               

# add a worksheet

$sheets->Add();

# delete a workseet

#$ex->DisplayAlerts = 0;

 

$sheets->Item(1)->Delete(); #casue a alarm dialog in excel

############################## Sheet ####################################

#my$sheet = $book->Worksheets("my");

my$sheet = $book->Worksheets(1);

#my$sheet = $sheets->Item("my");

my$name = $sheet->Name();

my$inde = $sheet->Index();

print "$name,$inde\n";

# row counts and column counts of cells used

my$row_counts= $sheet->{UsedRange}->{Rows}->{Count};

my$column_counts = $sheet->{UsedRange}->{Columns}->{Count}

# activate the appointed page

$sheet->Activate();

             

# write to a particular cell  

$sheet->Cells(1,1)->{Value} = "foo";

# write a 2 rows by 3 columns range

$sheet->Range("A8:C9")->{Value} = [[ undef, 'Xyzzy', 'Plugh' ],

                                   [ 42,    'Perl', 3.1415  ]];

                                   

# should generate a warning under -w

my$ovR8 = Variant(VT_R8, "3 is a good number");

$sheet->Range("A1")->{Value} = $ovR8;

$sheet->Range("A2")->{Value} = Variant(VT_DATE, 'Jan 1,1970');

# print "XyzzyPerl"

my$array = $sheet->Range("A8:C9")->{Value};

    

for (@$array) {

    for (@$_) {

        print defined($_) ? "$_|" : "<undef>|";

    }

    print "\n";

}

##########################save#######################################

# save and exit

$book->Save();

# $book->As( 't:\\tes.xls' );

$book->Close;  //不要忘记

undef $book;

undef $ex;