Tom McMeekin > Excel-Table-1.016 > Excel::Table



Annotate this POD


New  1
Open  0
View/Report Bugs
Module Version: 1.016   Source   Latest Release: Excel-Table-1.020




        use Excel::Table;

        my $xs = Excel::Table->new('dir' => '/cygdrive/c/Users/self/Desktop');

        for ($xs->list_workbooks) {
                print "workbook [$_]\n";


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



        my @data = $xs->extract('Sheet1');

        for (@data) {
                printf "rowid [%s] title [%s] max_width [%d] value [%s]\n",

        @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 ^ - 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.


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.


Build V1.016


Copyright (C) 2012 Tom McMeekin


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

syntax highlighting: