/ 
Excel-Table-1.023
River stage zero No dependents
/ Excel::Table

NAME

Excel::Table - Table processing class for Excel worksheets.

AUTHOR

Copyright (C) 2012-2020 Tom McMeekin <tmcmeeki@cpan.org>

SYNOPSIS

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');

DESCRIPTION

"Excel::Table" retrieves worksheets as if they are structured tables in array-format or optionally in hash-format.

1a. OBJ->dir(EXPR)

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.

1b. OBJ->list_workbooks

Returns an array of workbook files in the directory defined by the dir property.

2a. OBJ->open(EXPR)

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.

2b. OBJ->open_re(EXPR)

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.

3. OBJ->regexp

Returns the regexp used to search for the workbook on the filesystem.

4. OBJ->pathname

Returns the pathname of the opened workbook.

5a. OBJ->extract(EXPR,[TITLE_ROW])

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.

5b. OBJ->extract_hash(EXPR,[TITLE_ROW])

Per the extract method, but returns an array of hashes, with the hash keys corresponding to the titles.

5c. OBJ->select(CLAUSE,EXPR,[TITLE_ROW])

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.

5d. OBJ->select_hash(CLAUSE,EXPR,[TITLE_ROW])

Per the select method, but returns an array of hashes.

6. OBJ->columns or OBJ->rows

Returns the number of columns or rows available in the sheet extracted via the extract method.

7a. OBJ->force_null

Flag which determines if whitespace fields should be replaced by specific text (see OBJ->null).

7b. OBJ->null

String to replace whitespace fields with. Defaults to "(null)".

8. OBJ->rowid

Flag which determines whether a pseudo-column "rowid" is included in each tuple. The value will take the form "999999999" Defaults to FALSE.

9. OBJ->sheet_name

Returns the sheet_name against which data was extracted via extract.

10. OBJ->trim

Flag which determines if trailing whitespace fields should be trimmed.

11a. OBJ->title_row

Returns the title row of the worksheet (defaults to zero), following extract.

11b. OBJ->titles

Returns an array of title fields, the title row number having been defined as OBJ->title_row.

11c. OBJ->colid2title(colid)

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.

11d. OBJ->title2colid(REGEXP)

Returns the column number of the title identified by REGEXP. If no match, then returns undef.

12. OBJ->widths

Returns an array of maximum lengths of any (non-title) data in each column.

VERSION

Build V1.023

LICENSE

This program is free software; you can redistribute it and/or modify it under the terms of the GNU General Public License as published by the Free Software Foundation; either version 3 of the License, or any later version.

This program is distributed in the hope that it will be useful, but WITHOUT ANY WARRANTY; without even the implied warranty of MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU General Public License for more details.

You should have received a copy of the GNU General Public License along with this program. If not, see <http://www.gnu.org/licenses/>.

SEE ALSO

perl, Spreadsheet::ParseExcel, Spreadsheet::ParseXLSX.