Excel::Table
use Excel::Table; my $xs = Excel::Table->new('dir' => '/cygdrive/c/Users/self/Desktop'); for ($xs->list_workbooks) { print "workbook [$_]\n"; } $xs->open('mybook.xls'); my $wb1 = $xs->open_re('foo*bar*'); for my $worksheet ($wb1->worksheets) { print "worksheet: " . $worksheet->get_name() . "\n"; } $xs->null("this is a null value"); $xs->force_null(1); $xs->rowid(0); $xs->trim(0); my @data = $xs->extract('Sheet1'); for (@data) { printf "rowid [%s] title [%s] max_width [%d] value [%s]\n", $_->[0], $xs->titles->[0], $xs->widths->[0], $data{$_}->[0]; } @data = $xs->extract_hash('Sheet1'); @data = $xs->select("column1,column2,column3", 'Sheet1'); @data = $xs->select_hash("column1,column2,column3", 'Sheet1'); printf "columns %d rows %d title_row %d\n", $xs->columns, $xs->rows, $xs->title_row; printf "regexp [%s] pathname [%s] sheet_name [%s]\n", $xs->regexp, $xs->pathname, $xs->sheet_name; printf "colid2title(0) = [%s]\n", $xs->colid2title(0); printf "title2colid('Foo') = %d\n", $xs->title2colid('Foo');
Excel::Table.pm - spreadsheet table processing. Retrieves worksheets as if they are structured tables array-format.
Override the directory location in which to look for workbooks. Defaults to "." (i.e. the current working directory). This location is critical to the list_workbooks, open, and open_re methods.
Returns an array of workbook files in the directory defined by the dir property.
Parses the filename specified by EXPR. The dir property will designate the search path. Once opened, via this method (or open_re) the workbook is available for use by the extract method.
This will search for a file which has a filename matching the regexp EXPR. A warning will be issued if multiple matches are found, only the first will be opened.
Returns the regexp used to search for the workbook on the filesystem.
Returns the pathname of the opened workbook.
This will extract all data from the worksheet named EXPR. Data is extracted into an array and returned. Format of data is per below:
[ value1, value2, value3, ... ], [ value1, value2, value3, ... ], [ value1, value2, value3, ... ], ...
The object OBJ will be populated with various properties to assist you to access the data in the array, including column titles and widths.
A worksheet object is temporarily created in order to populate the array. Once a worksheet is extracted, the associated worksheet object is destroyed. This routine can be called again on any worksheet in the workbook.
If the TITLE_ROW argument is specified, then the title_row property will also be updated prior to extraction.
Per the extract method, but returns an array of hashes, with the hash keys corresponding to the titles.
Similar to the extract method, this will extract all rows from the worksheet EXPR, constraining the columns to those specified by the clause argument, which is a comma-separated string, e.g. "column1,column2,column3".
As with the extract method, the titles and widths properties will be revised.
Per the select method, but returns an array of hashes.
Returns the number of columns or rows available in the sheet extracted via the extract method.
Flag which determines if whitespace fields should be replaced by specific text (see OBJ->null).
String to replace whitespace fields with. Defaults to "(null)".
Flag which determines whether a pseudo-column "rowid" is included in each tuple. The value will take the form "999999999" Defaults to FALSE.
Returns the sheet_name against which data was extracted via extract.
Flag which determines if trailing whitespace fields should be trimmed.
Returns the title row of the worksheet (defaults to zero), following extract.
Returns an array of title fields, the title row number having been defined as OBJ->title_row.
Converts the column number (colid) to a string column title (i.e. the offset within the title_row array). If no match, then returns undef.
Returns the column number of the title identified by REGEXP. If no match, then returns undef.
Returns an array of maximum lengths of any (non-title) data in each column.
Build V1.020
Copyright (C) 2012 Tom McMeekin tmcmeeki@cpan.org
perl, Spreadsheet::ParseExcel, Spreadsheet::XLSX.
To install Excel::Table, copy and paste the appropriate command in to your terminal.
cpanm
cpanm Excel::Table
CPAN shell
perl -MCPAN -e shell install Excel::Table
For more information on module installation, please visit the detailed CPAN module installation guide.