DBD::Excel(3) A class for DBI drivers that act on Excel File.

DESCRIPTION

This is still alpha version.

The DBD::Excel module is a DBI driver. The module is based on these modules:

* Spreadsheet::ParseExcel
reads Excel files.
* Spreadsheet::WriteExcel
writes Excel files.
* SQL::Statement
a simple SQL engine.
* DBI
Of course. :-)

This module assumes TABLE = Worksheet. The contents of first row of each worksheet as column name.

Adding that, this module accept temporary table definition at ``connect'' method with ``xl_vtbl''.

ex.
    my $hDb = DBI->connect(
            ``DBI:Excel:file=dbdtest.xls'', undef, undef, 
                        {xl_vtbl => 
                            {TESTV => 
                                {
                                    sheetName => 'TEST_V',
                                    ttlRow    => 5,
                                    startCol  => 1,
                                    colCnt    => 4,
                                    datRow    => 6,
                                    datLmt    => 4,
                                }
                            }
                        });

For more information please refer sample/tex.pl included in this distribution.

Metadata

The following attributes are handled by DBI itself and not by DBD::Excel, thus they all work like expected:

    Active
    ActiveKids
    CachedKids
    CompatMode             (Not used)
    InactiveDestroy
    Kids
    PrintError
    RaiseError
    Warn                   (Not used)

The following DBI attributes are handled by DBD::Excel:

AutoCommit
Always on
ChopBlanks
Works
NUM_OF_FIELDS
Valid after "$hSt->execute"
NUM_OF_PARAMS
Valid after "$hSt->prepare"
NAME
Valid after "$hSt->execute"; undef for Non-Select statements.
NULLABLE
Not really working, always returns an array ref of one's. Valid after "$hSt->execute"; undef for Non-Select statements.

These attributes and methods are not supported:

    bind_param_inout
    CursorName
    LongReadLen
    LongTruncOk

Additional to the DBI attributes, you can use the following dbh attribute:

xl_fmt
This attribute is used for setting the formatter class for parsing.
xl_dir
This attribute is used only with "data_sources" on setting the directory where Excel files ('*.xls') are searched. It defaults to the current directory (``.'').
xl_vtbl
assumes specified area as a table. See sample/tex.pl.
xl_skiphidden
skip hidden rows(=row height is 0) and hidden columns(=column width is 0). See sample/thidden.pl.
xl_ignorecase
set casesensitive or not about table name and columns. Default is sensitive (maybe as SQL::Statement). See sample/thidden.pl.

Driver private methods

data_sources
The "data_sources" method returns a list of '*.xls' files of the current directory in the form ``DBI:Excel:xl_dir=$dirname''.

If you want to read the subdirectories of another directory, use

    my($hDr) = DBI->install_driver("Excel");
    my(@list) = $hDr->data_sources( 
                    { xl_dir => '/usr/local/xl_data' } );
list_tables
This method returns a list of sheet names contained in the $hDb->{file}. Example:

    my $hDb = DBI->connect("DBI:Excel:file=test.xls");
    my @list = $hDb->func('list_tables');