Cultured Perl: Reading and Writing Excel Files with Perl

By Teodor Zlatanov
2004-05-01

Windows Example: Parsing

This section applies to Windows machines only. All the other sections apply to Linux.

Before you proceed, install ActiveState Perl (version 628 used here) or the ActiveState Komodo IDE for editing and debugging Perl. Komodo comes with a free license for home users, which you can get in a matter of minutes. (See Resources later in this article for the download sites.)

Installing the Spreadsheet::ParseExcel and Spreadsheet::WriteExcel modules using the ActiveState PPM package manager is difficult. PPM has no history, options are hard to set, help scrolls off the screen, and the default is to install modules ignoring dependencies. You can invoke PPM from the command line by typing "ppm" and issuing the following commands:

ppm> install OLE::Storage_Lite

ppm> install Spreadsheet::ParseExcel

ppm> install Spreadsheet::WriteExcel

The module install will fail in this case, because IO::Scalar is not yet available, so you may want to give up trying to find the problem with PPM, and switch to the built-in Win32::OLE module. However, by the time you read this, ActiveState may have released a fix for this problem.

With Win32::OLE from the ActiveState toolkit, you can dump a worksheet, cell by cell, using the code listed below:

#!/usr/bin/perl -w



use strict;

use Win32::OLE qw(in with);

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



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



# get already active Excel application or open new

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

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



# open Excel file

my $Book = $Excel->Workbooks->Open("c:/komodo projects/test.xls"); 



# You can dynamically obtain the number of worksheets, rows, and columns

# through the Excel OLE interface.  Excel's Visual Basic Editor has more

# information on the Excel OLE interface.  Here we just use the first

# worksheet, rows 1 through 4 and columns 1 through 3.



# select worksheet number 1 (you can also select a worksheet by name)

my $Sheet = $Book->Worksheets(1);



foreach my $row (1..4)

{

 foreach my $col (1..3)

 {

  # skip empty cells

  next unless defined $Sheet->Cells($row,$col)->{'Value'};



 # print out the contents of a cell  

  printf "At ($row, $col) the value is %s and the formula is %s\n",

   $Sheet->Cells($row,$col)->{'Value'},

   $Sheet->Cells($row,$col)->{'Formula'};        

 }

}



# clean up after ourselves

$Book->Close;

Note that you can assign values to cells very easily in the following way:

$sheet->Cells($row, $col)->{'Value'} = 1;

Tutorial Pages:
» Using the Spreadsheet::WriteExcel and Spreadsheet::ParseExcel Modules
» Spreadsheet::WriteExcel and Spreadsheet::ParseExcel
» Windows Example: Parsing
» Linux Example: Parsing
» Conclusion
» Resources

First published by IBM DeveloperWorks

Related Tutorials:
» Random subroutines in Perl
» Log Script Use
» Creating Perl Modules for Web Sites
» Bit Vector, Using Perl Vec
» Build a Perl/CGI Voting System
» Perl Range Operator