How to access Excel from Perl

 

#Example 1

# Before use Win32::OLE, make sure this module has been installed properly

#! perl -w

use strict;

use Win32::OLE;

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

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

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

Win32::OLE->new('Excel.Application', 'Quit');   # get already active # excel application

                                                                       # or open new

my $book = $excel->Workbooks->Add;              # open excel file

my $sheet = $book->Worksheets(1);               # select worksheet number 1

foreach my $i (1..9) {

my $j = 1;

    while ($j <= $i) {

        my $result = $j * $i;

        my $cell_content = "$j".' X '."$i".' = '."$result";

        $sheet->Cells($i, $j)->{Value} = $cell_content;    # set value of cells

        $j ++;

    }

}

$book->SaveAs('D:/MyProject/Perl/test.xls');

$book->Close;

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

#Exmple 2

#! perl -w

 

use strict;

use Win32::OLE qw(in with);

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

 

$Win32::OLE::Warn = 3;        # die on erros

 

# Read from "cpu_mem_status.log" and store the data in hash

my %data = (rows    => 6,

              columns => 3,

              1         => ['Time', '%CPU', '%MEM'],

              2         => ['09:24:57', 0.3, 1.0],

              3         => ['09:25:00', 1.2, 2.0],

              4         => ['09:25:03', 1.3, 2.0],

              5         => ['09:25:06', 1.4, 2.1],

              6         => ['09:25:09', 1.5, 2.2],

             );

 

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

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

my $book  = $excel->Workbooks->Open('D:/MyProject/Perl/读写Excel/test.xls');

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

my $range = $sheet->Range('A1:C5');

 

foreach my $i ( 1..$data{'rows'} ) {

    foreach my $j (1..$data{'columns'}) {

        $sheet->Cells($i, $j)->{'Value'} = $data{$i}[$j-1];

    }

}

 

my $chart = $excel->Charts->Add;        # Add chart

$chart->{'ChartType'} = xlLineMarkers; # Set chart type

$chart->SetSourceData( {Source => $range,

                             PlotBy  => xlColumns} );

$chart->{'HasTitle'} = 1;

$chart->ChartTitle->{Text} = 'Cpu, Memory status';  # Set chart title

 

$book->Save;

$book->Close;