NAME

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

DESCRIPTION

Provides methods for simple reading of a Excel spreadsheet, where the columns are returned in the order defined.

Assumes a specific format of the spreadsheet where the first row of data defined the names of the columns.

SYNOPSIS

 use Spreadsheet::BasicReadNamedCol;

 my $xlsFileName = 'Excel Price Sheet 021203.xls';
 my @columnHeadings = (
    'Supplier Part Number',
    'Customer Price',
    'Currency Code',
    'Price UOM',
    'Short Description',
    'Long Description',
 );

 my $ss = new Spreadsheet::BasicReadNamedCol($xlsFileName) ||
    die "Could not open '$xlsFileName': $!";
 $ss->setColumns(@columnHeadings);

 # Print each row of the spreadsheet in the order defined in
 # the columnHeadings array
 my $row = 0;
 while (my $data = $ss->getNextRow())
 {
    $row++;
    print join('|', $row, @$data), "\n";
 }

REQUIRED MODULES

The following modules are required:

 Spreadsheet::BasicRead
 Spreadsheet::ParseExcel

METHODS

There are no class methods, the object methods are described below. Private class method start with the underscore character '_' and should be treated as Private.

new

Called to create a new BasicReadNamedCol object. The arguments can be either a single string (see 'SYNOPSIS') which is taken as the filename of the spreadsheet of as named arguments.

 eg.  my $ss = Spreadsheet::BasicReadNamedCol->new(
                  columns       => \@columnNames,
                  fileName      => 'MyExcelSpreadSheet.xls',
                  skipHeadings  => 1,
                  skipBlankRows => 1,
                  log           => $log,
                  );

The following named arguments are available:

columns

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)

 where:
   realColName - is the actual column name in the spreadsheet and
   name        - is the pattern to match

skipHeadings

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

skipBlankRows

Skip blank lines in the spreadsheet if true.

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.

log

Use the File::Log object to log exceptions.

fileName

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

getNextRow()

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

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

numSheets()

Returns the number of sheets in the spreadsheet

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.

currentSheetNum()

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

currentSheetName()

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

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'.

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.

getFirstSheet()

Returns the first sheet "ssBook" object.

cellValue(row, col)

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

getFirstRow()

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

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.

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.

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.

KNOWN ISSUES

None

SEE ALSO

Spreadsheet::BasicRead

AUTHOR

 Greg George, IT Technology Solutions P/L, Australia
 Mobile: 0404-892-159, Email: gng@cpan.org

LICENSE

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.

CVS ID

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

UPDATE HISTORY

$Log: BasicReadNamedCol.pm,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

cpanm Spreadsheet::BasicReadNamedCol

perl -MCPAN -e shell
install Spreadsheet::BasicReadNamedCol