Cultured Perl: Reading and Writing Excel Files with Perl

By Teodor Zlatanov
2004-05-01

Linux Example: Parsing

This section applies to UNIX, and specifically Linux. It has not been tested under Windows.

It would be difficult to give a better example of parsing with Linux than the one provided in the documentation for the Spreadsheet::ParseExcel module, so I will show that example and then explain how it works.

Listing 3: parse-excel.pl

#!/usr/bin/perl -w



use strict;

use Spreadsheet::ParseExcel;



my $oExcel = new Spreadsheet::ParseExcel;



die "You must provide a filename to $0 to be parsed as an Excel file" unless @ARGV;



my $oBook = $oExcel->Parse($ARGV[0]);

my($iR, $iC, $oWkS, $oWkC);

print "FILE  :", $oBook->{File} , "\n";

print "COUNT :", $oBook->{SheetCount} , "\n";



print "AUTHOR:", $oBook->{Author} , "\n"

 if defined $oBook->{Author};



for(my $iSheet=0; $iSheet < $oBook->{SheetCount} ; $iSheet++)

{

 $oWkS = $oBook->{Worksheet}[$iSheet];

 print "--------- SHEET:", $oWkS->{Name}, "\n";

 for(my $iR = $oWkS->{MinRow} ;

     defined $oWkS->{MaxRow} && $iR <= $oWkS->{MaxRow} ;

     $iR++)

 {

  for(my $iC = $oWkS->{MinCol} ;

      defined $oWkS->{MaxCol} && $iC <= $oWkS->{MaxCol} ;

      $iC++)

  {

   $oWkC = $oWkS->{Cells}[$iR][$iC];

   print "( $iR , $iC ) =>", $oWkC->Value, "\n" if($oWkC);

  }

 }

}

This example was tested with Excel 97. If it does not work, try converting to the Excel 97 format. The perldoc page for Spreadsheet::ParseExcel claims Excel 95 and 2000 compatibility as well.

The spreadsheet is parsed into a top-level object called $oBook. $oBook has properties to aid the program, such as "File," "SheetCount," and "Author." The properties are documented in the perldoc page for Spreadsheet::ParseExcel, in the workbook section.

The workbook contains several worksheets; iterate through them by using the workbook SheetCount property. Each worksheet has a MinRow and MinCol and corresponding MaxRow and MaxCol properties, which can be used to figure out the range the worksheet can access. The properties are documented in the perldoc page for Spreadsheet::ParseExcel, in the worksheet section.

Cells can be obtained from a worksheet through the Cells property; that's how the $oWkC object is obtained in Listing 3. Cell properties are documented in the perldoc page for Spreadsheet::ParseExcel, in the Cell section. There does not seem to be a way, according to the documentation, to obtain the formula listed in a particular cell.

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