O'Reilly logo

Perl Hacks by Curtis Poe, Curtis Ovid Poe, Damian Conway, Chromatic

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

Hack #21. Use Any Spreadsheet As a Data Source

Make your data analysis independent of the spreadsheet program.

Spreadsheets are useful for holding structured data, usually based on columns and rows. Most often part of the data is calculated data from other cells in the same spreadsheet.

If you want to work with that data, you face the problem of too many standards and programs. Writing a script that has to read the data from the spreadsheet is more writing an interface to the spreadsheet than actually working with the interesting data.

Accessing Cell Data

The Spreadsheet::Read module gives you a single interface to the data of most spreadsheet formats available, hiding all the troublesome work that deals with the parsers and the portability stuff, yet being flexible enough to get to the guts of the spreadsheet.

It's easy to use:

use Spreadsheet::Read;

my $ref  = ReadData( 'test.xls' );
my $fval = $ref->[1]{A3};
my $uval = $ref->[1]{cell}[1][3];

Here $ref is a reference to a structure that represents the data from the spreadsheet (test.xls). The reference points to a list (the worksheets) of hashes (the data).

Every cell has two representations: either access it by its name (A3), in which case the interface gives you the formatted value, or the cell hash, in which case you get the unformatted value of the cell.

Do I need Spreadsheet::Read for that?

No you don't, but it makes life easier. Setting aside all the good things of the various user interfaces for the available spreadsheets (95% ...

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, interactive tutorials, and more.

Start Free Trial

No credit card required