Reading Excel Files with Perl

Sometimes you need to read data from Excel files with Perl. There are several Perl Modules (Spreadsheet::ParseExcel, Spreadsheet::ParseExcel::Simple) that make that job quite easy. Spreadsheet::ParseExcel::Simple is an lightweight abstraction of Spreadsheet::ParseExcel.

Sample Code:

#!/usr/bin/perl -w

use Spreadsheet::ParseExcel;

my $parser = new Spreadsheet::ParseExcel();
my $xls = $parser->Parse("PerlExcelTest.xls")
  or die "Can't read File!";

# Loop over all tables in Excel file
for(my $i = 0; $i < $xls->{SheetCount}; $i++) {
  $sheet = $xls->{Worksheet}[$i];

  print "\nSheet: " . $sheet->{Name} . "\n";

  # has the current sheet cells?
  next unless (exists ($sheet->{MaxRow}) and (exists ($sheet->{MaxCol})));

  # loop over the row of the current sheet
  foreach my $row ($sheet->{MinRow} .. $sheet->{MaxRow}) {
    
    # loop over the cells in the current row
    foreach my $column ($sheet->{MinCol} .. $sheet->{MaxCol}) {
      next unless (defined $sheet->{Cells}[$row][$column]);

      print $sheet->{Cells}[$row][$column]->Value . ' | ';
    }
    print "\n";
  }
}

During my tests I came across several issues:

• If you create Excel files with OpenOffice numeric values show up as string GENERAL. There is a patch for that problem. Please note also that you could use $cell->Val instead of $cell->Value.
• I also have problems to extract date values from the excel file. In that case I am not able to determine the type of a date cell. Normally I should see Date but I turns out to be Numeric.