Help-Site Computer Manuals
  Algorithms & Data Structures   Programming Languages   Revision Control
  Cameras   Computers   Displays   Keyboards & Mice   Motherboards   Networking   Printers & Scanners   Storage
  Windows   Linux & Unix   Mac

Methods to easily read data from spreadsheets with columns in the order you want based on the names of the column headings

Spreadsheet::BasicReadNamedCol - Methods to easily read data from spreadsheets with columns in the order you want based on the names of the column headings

Value expected to be an array reference to a list of column names that appear in the first line of the spreadsheet. The order of the column names defines the order in which the data is returned by the 'getNextRow' method.

This is really useful where spreadsheet files from sources out of your control are not consistant in the ordering of columns.

Note that the match on column name uses the following pattern match:

 if ($realColName =~ /^\Q$name/i)


   realColName - is the actual column name in the spreadsheet and

   name        - is the pattern to match

=item skipHeadings

Don't output the headings line in the first call to 'getNextRow' if true.

=item skipBlankRows

Skip blank lines in the spreadsheet if true.

=item setColumns(array or array_ref)

Sets the order that columns will be returned in based on the names in the array provided. The names are expected to match the values in the first row of the spreadsheet.

Use the File::Log object to log exceptions.

=item fileName

The name (and optionally path) of the spreadsheet file to process.


Get the next row of data from the spreadsheet. The data is returned as an array reference.

  eg.  $rowDataArrayRef = $ss->getNextRow();

=head2 numSheets()

Returns the number of sheets in the spreadsheet

=head2 openSpreadsheet(fileName)

Open a new spreadsheet file and set the current sheet to the first sheet. The name and optionally path of the spreadsheet file is a required argument to this method.

=head2 currentSheetNum()

Returns the current sheet number or undef if there is no current sheet. 'setCurrentSheetNum' can be called to set the current sheet.

=head2 currentSheetName()

Return the name of the current sheet or undef if the current sheet is not defined. see 'setCurrentSheetNum'.

=head2 setCurrentSheetNum(num)

Sets the current sheet to the integer value 'num' passed as the required argument to this method. Note that this should not be bigger than the value returned by 'numSheets'.

=head2 getNextSheet()

Returns the next sheet ``ssBook'' object or undef if there are no more sheets to process. If there is no current sheet defined the first sheet is returned.

=head2 getFirstSheet()

Returns the first sheet ``ssBook'' object.

=head2 cellValue(row, col)

Returns the value of the cell defined by (row, col)in the current sheet.

=head2 getFirstRow()

Returns the first row of data from the spreadsheet (possibly skipping the column headings 'skipHeadings') as an array reference.

=head2 setHeadingRow(rowNumber)

Sets the effective minimum row for the spreadsheet to 'rowNumber', since it is assumed that the heading is on this row and anything above the heading is not relavent.

Note: the row (and column) numbers are zero indexed.

=head2 logexp(message)

Logs an exception message (can be a list of strings) using the File::Log object if it was defined and then calls die message.

=head2 logmsg(debug, message)

If a File::Log object was passed as a named argument 'new') and if 'debug' (integer value) is equal to or greater than the current debug Level (see File::Log) then the message is added to the log file.

If a File::Log object was not passed to new then the message is output to STDERR.






 Greg George, IT Technology Solutions P/L, Australia

 Mobile: 0404-892-159, Email:


Copyright (c) 1999- Greg George. All rights reserved. This program is free software; you can redistribute it and/or modify it under the same terms as Perl itself.


$Id:,v 1.3 2006/04/30 05:57:29 Greg Exp $


$Log:,v $ Revision 1.3 2006/04/30 05:57:29 Greg - removed tabs from file

Revision 1.2 2006/03/07 10:03:26 Greg - minor pod changes

Revision 1.1 2006/03/05 03:07:58 Greg - initial CPAN upload

Revision 1.0  2003/12/02 23:58:34  gxg6

- Initial development, need POD