Tom McMeekin > Excel-Table-1.020 > Excel::Table



Annotate this POD


Open  0
View/Report Bugs
Module Version: 1.020   Source  




        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.

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.


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


Copyright (C) 2012 Tom McMeekin


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

syntax highlighting: