Perl Win32::OLE Automation for Excel

1.pl

#From http://www.xav.com/perl/faq/Windows/ActivePerl-Winfaq12.html

#How do I extract a series of cells from Microsoft Excel?

#If you have a sheet object you can extract the values of a series of cells through $Sheet->Range-> #{'Value'}, for example:

    my $array = $Sheet->Range("A8:B9")->{'Value'};

#Now $array[0][0] contains the value of cell A8, $array[0][1] the value of cell B8, $array[1][0] the value #of cell A9 and $array[1][1] the value of cell B9.

#What is returned is an two-dimensional array (OK, an array with references to arrays) that contains the #values of the requested cells.

#A complete example is here:

 
    use strict;
    use Win32::OLE qw(in with);
    use Win32::OLE::Const 'Microsoft Excel';
    $Win32::OLE::Warn = 3;                                # die on errors...
    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->Open("C:\\DOCUMENTS\\test.xls"); # open Excel file
    my $Sheet = $Book->Worksheets(1);                     # select worksheet number 1
    my $array = $Sheet->Range("A8:B9")->{'Value'};        # get the contents
    $Book->Close;
    foreach my $ref_array (@$array) {                     # loop through the array
                                                          # referenced by $array
        foreach my $scalar (@$ref_array) {
            print "$scalar\t";
        }
        print "\n";
    } 

#To retrieve the formatted value of a cell you should use the {'Text'} property instead of the {'Value'} #property. This returns exactly what is being displayed on the screen though! If the column is not wide #enough, you get a value of '######':

    my $array = $Sheet->Range("A8:B9")->{'Text'};

2.pl

#From http://www.xav.com/perl/faq/Windows/ActivePerl-Winfaq12.html
#How do I make a chart in Microsoft Excel?

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

my $Excel = Win32::OLE->new("Excel.Application");
$Excel->{Visible} = 1;

my $Book = $Excel->Workbooks->Add;
my $Sheet = $Book->Worksheets(1);
my $Range = $Sheet->Range("A2:C7");
$Range->{Value} =
    [['Delivered', 'En route', 'To be shipped'],
     [504, 102, 86],
     [670, 150, 174],
     [891, 261, 201],
     [1274, 471, 321],
     [1563, 536, 241]];

my $Chart = $Excel->Charts->Add;
$Chart->{ChartType} = xlAreaStacked;
$Chart->SetSourceData({Source => $Range, PlotBy => xlColumns});
$Chart->{HasTitle} = 1;
$Chart->ChartTitle->{Text} = "Items delivered, en route and to be shipped";

3.pl

#How do I save a chart from Microsoft Excel as GIF/JPEG/PNG?

#You can use the Export method of a chart. If you have a chartobject the code looks like this

    $ChartObj->Chart->Export({
        FileName    => "$graphics_filename",
        FilterName  => 'GIF',
        Interactive => 0});

#A complete example that opens an Excel workbook, loops through all the charts and saves them as GIFs and #then closes the Excel workbook is here:

    use strict;
    use Win32::OLE qw(in with);
    use Win32::OLE::Const;
    use Win32::OLE::Const 'Microsoft Excel';
    $Win32::OLE::Warn = 3;        # die on errors...

    my $filename = 'c:\\documents\\test.xls';
    my $filter = 'GIF';           # can be GIF, JPG, JPEG or PNG
    my $count = 0;

    my $Excel = Win32::OLE->GetActiveObject('Excel.Application')
        || Win32::OLE->new('Excel.Application', 'Quit');  # use the Excel application if it's open, otherwise open new
    my $Book = $Excel->Workbooks->Open( $filename );      # open the file
    foreach my $Sheet (in $Book->Sheets) {                # loop through all sheets
        foreach my $ChartObj (in $Sheet->ChartObjects) {  # loop through all chartobjects in the sheet
            my $savename = "$filename." . $count++ . ".$filter";
            $ChartObj->Chart->Export({
                FileName    => $savename,
                FilterName  => $filter,
                Interactive => 0});
        }
    }
    $Book->Close;

4-5.pl

#How do I run a macro in Microsoft Excel?

#Macros in Microsoft Excel can be run by using the $Excel->Run method, for example:

    $Excel->Run("PrintPDFFile");

#In order to do this, you of course need to have a macro in Excel that's called 'PrintPDFFile'...

###

#How do I set the name of a cell in Microsoft Excel?

#Use the Names->Add method on a sheet, giving it a name and a range object to apply the name to, for example:

    $Sheet->Names->Add({Name => 'NetCost', RefersTo => $Sheet->Range('$B$10')});

gistfile1.txt

More useful links regarding this ->
* http://www.perlmonks.org/?node_id=153486
* http://www.tek-tips.com/viewthread.cfm?qid=567112
* http://www.tek-tips.com/faqs.cfm?fid=6715