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

AUTHOR

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

SEE ALSO

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