NAME

Excel::Table

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.pm - spreadsheet table processing. Retrieves worksheets as if they are structured tables array-format.

1. OBJ->list_workbooks

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

2a. OBJ->open(EXPR)

Parses the pathname specified by EXPR. The dir property optionally specified during the open will designate the search path, although EXPR can also be a full pathname. 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 path 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->force_null

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

6. OBJ->null

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

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

6. OBJ->sheet_name

Returns the sheet_name against which data was extracted via extract.

7. OBJ->titles

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

6. OBJ->trim

Flag which determines if trailing whitespace fields should be trimmed.

8. OBJ->widths

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

9. OBJ->columns (OBJ->rows)

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

10. OBJ->title_row

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

11a. 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.

11b. OBJ->title2colid(REGEXP)

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

VERSION

$Revision: 1.10 $

AUTHOR

Copyright (C) 2010 Tom McMeekin tmcmeeki@cpan.org

SEE ALSO

perl, Spreadsheet::ParseExcel, Spreadsheet::XLSX.