Win32::OLE and Excel

Win32::OLE for Perl on Windows systems provides convenient facilities for interfacing with any OLE object once you know the interface provided. I most often use this facility with Excel to transform data in plain text files into nicely formatted Excel sheets with uniform formatting. By its very nature, this method only works with Windows systems. (See SpreadSheet::WriteExcel and Spreadsheet::ParseExcel for cross-platform solutions).

Note that an excellent source of information for Microsoft Office applications is the Object Browser. You can access it via Tools -> Macro -> Visual Basic Editor. Once you are in the editor, hit F2 to browse the interfaces, methods, and properties provided by Microsoft Office applications.

In this example, I am going to show how to get some data into a specified range in Excel, and create a pie chart based on those data.

I take a couple of innocent short-cuts such as using a hardwired range. I am unable to find a way of setting a whole range by only specifying its top-left coordinate. On the other hand, it is not hard to construct the string to be used as the range if you know the number of rows and columns:

my ($start_col, $num_cols, $end_col) = ('A', 120, 'A');
++$end_col while --$num_cols;

my ($start_row, $num_rows) = (3, 50);
my $end_row = $start_row + $num_rows - 1;

my $range_str = "$start_col$start_row:$end_col$end_row";

Please note that while I provide this script in the hopes that it will be useful to you, I make NO GUARANTEES OR WARRANTIES OF ANY KIND. The code is provided subject to the same terms as Perl itself. For more information, please refer to the Perl Artistic License.

#!/usr/bin/perl

use strict; use warnings;

use FindBin qw( $Bin );
use File::Basename;
use File::Spec::Functions qw( catfile );

use Win32::OLE qw(in with);
use Win32::OLE::Const 'Microsoft Excel';
$Win32::OLE::Warn = 3;

my $output = shift @ARGV;
unless (defined $output and length $output) {
    my $progname = fileparse $0;
    warn "Usage: $progname output_filename\n";
    exit 1;
}

$output = catfile $Bin, $output;

my $excel = get_excel();
$excel->{Visible} = 1;

my $book = $excel->Workbooks->Add;
my $sheet = $book->Worksheets->Add;
$sheet->{Name} = 'Perl Win32-OLE Example';

my $data = get_data();

my $range = $sheet->Range('B2:C6');
$range->{Value} = $data;
$range->AutoFormat;

my $chart = $excel->Charts->Add;
$chart->{Name} = 'Pie Chart';
$chart->ApplyCustomType(xlPie);
$chart->SetSourceData($range);

$book->SaveAs($output);
$book->Close;

sub get_excel {
    my $excel;
    eval {
        $excel = Win32::OLE->GetActiveObject('Excel.Application');
    };

    if ( my $ex = $@ ) {
        die $ex, "\n";
    }

    unless(defined $excel) {
        $excel = Win32::OLE->new('Excel.Application', sub { $_[0]->Quit })
            or die "Oops, cannot start Excel: ", 
                   Win32::OLE->LastError, "\n";
    }
    return $excel;
}

sub get_data {
    my @data;
    while (<DATA>) {
        chomp;
        last unless length;
        my @cols = split /\s+/;
        push @data, [ @cols ];
    }
    return \@data;
}

__DATA__
    Sales
North   $1,000,000
South   $925,000
West    $1,333,456
East    $526,000