I’ve been working on a data import recently that required me to pull in the contents of a folder full of excel documents, all with the same structure containing data that needed to be collated into a single file.  There really isn’t an easy way to read Excel (xls files) in PHP so I’ve reverted back to my old friend Perl to get the job done.

The key is to use a simple and friendly CPAN module to do most of the work for you.  In this case, I’ve chosen Spreadsheet::Read as it provides a great abstraction and doesn’t require you to run your Perl script in Windows.

#!/usr/bin/perl -w

use strict;
use Spreadsheet::Read;
use Data::Dumper;

my $xls = ReadData ("Input/sample.xls");

print $xls->[1]{'A1'};
exit;

It really is as simple as that. Simply load the file with ReadData() – this produces an array:

$VAR1 = [
          {
            'sheets' => 1,
            'version' => '0.32',
            'sheet' => {
                         'Sheet 1 Name Here' => '1'
                       },
            'parser' => 'Spreadsheet::ParseExcel',
            'type' => 'xls'
          },
          {
            'D91' => '     ',
            'B6' => '',
            'F33' => '',
            'J2' => '',
            'E47' => '',
            'B130' => 'Residual fuel oil No.6',
            'F21' => '',
            'F122' => 'Tonnes',
        }
];

The first element contains a hash with meta-data about the excel document. Each element in the array after that contains the physical data from the excel file. This is perfect for extracting the data from the xls file! Enjoy.