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.