.\" -*- mode: troff; coding: utf-8 -*-
.\" Automatically generated by Pod::Man 5.01 (Pod::Simple 3.43)
.\"
.\" Standard preamble:
.\" ========================================================================
.de Sp \" Vertical space (when we can't use .PP)
.if t .sp .5v
.if n .sp
..
.de Vb \" Begin verbatim text
.ft CW
.nf
.ne \\$1
..
.de Ve \" End verbatim text
.ft R
.fi
..
.\" \*(C` and \*(C' are quotes in nroff, nothing in troff, for use with C<>.
.ie n \{\
.    ds C` ""
.    ds C' ""
'br\}
.el\{\
.    ds C`
.    ds C'
'br\}
.\"
.\" Escape single quotes in literal strings from groff's Unicode transform.
.ie \n(.g .ds Aq \(aq
.el       .ds Aq '
.\"
.\" If the F register is >0, we'll generate index entries on stderr for
.\" titles (.TH), headers (.SH), subsections (.SS), items (.Ip), and index
.\" entries marked with X<> in POD.  Of course, you'll have to process the
.\" output yourself in some meaningful fashion.
.\"
.\" Avoid warning from groff about undefined register 'F'.
.de IX
..
.nr rF 0
.if \n(.g .if rF .nr rF 1
.if (\n(rF:(\n(.g==0)) \{\
.    if \nF \{\
.        de IX
.        tm Index:\\$1\t\\n%\t"\\$2"
..
.        if !\nF==2 \{\
.            nr % 0
.            nr F 2
.        \}
.    \}
.\}
.rr rF
.\" ========================================================================
.\"
.IX Title "DBD::Excel 3pm"
.TH DBD::Excel 3pm 2024-02-18 "perl v5.38.2" "User Contributed Perl Documentation"
.\" For nroff, turn off justification.  Always turn off hyphenation; it makes
.\" way too many mistakes in technical documents.
.if n .ad l
.nh
.SH NAME
DBD::Excel \-  A class for DBI drivers that act on Excel File.
.PP
This is still alpha version.
.SH SYNOPSIS
.IX Header "SYNOPSIS"
.Vb 6
\&    use DBI;
\&    my $dbh = DBI\->connect("DBI:Excel:file=test.xls") or die "Cannot connect: " . $DBI::errstr;
\&    my $sth = $dbh\->prepare("CREATE TABLE a (id INTEGER, name CHAR(10))") or die "Cannot prepare: " . $dbh\->errstr();
\&    $sth\->execute() or die "Cannot execute: " . $sth\->errstr();
\&    $sth\->finish();
\&    $dbh\->disconnect();
.Ve
.SH DESCRIPTION
.IX Header "DESCRIPTION"
This is still \fBalpha version\fR.
.PP
The DBD::Excel module is a DBI driver.
The module is based on these modules:
.IP \(bu 4
Spreadsheet::ParseExcel
.Sp
reads Excel files.
.IP \(bu 4
Spreadsheet::WriteExcel
.Sp
writes Excel files.
.IP \(bu 4
SQL::Statement
.Sp
a simple SQL engine.
.IP \(bu 4
DBI
.Sp
Of course. :\-)
.PP
This module assumes TABLE = Worksheet.
The contents of the first row of each worksheet are used as column name.
.PP
Adding that, this module accepts a temporary table definition in the \f(CW\*(C`connect\*(C'\fR method with the \f(CW\*(C`xl_vtbl\*(C'\fR option.
.PP
Example:
.PP
.Vb 10
\&    my %options = (
\&        xl_vtbl => { # define temporary tables here
\&            TESTV => { # temporary table name => options
\&                sheetName => \*(AqTEST_V\*(Aq, # actual Excel sheet name
\&                ttlRow    => 5,
\&                startCol  => 1,
\&                colCnt    => 4,
\&                datRow    => 6,
\&                datLmt    => 4,
\&            }
\&        }
\&    );
\&    my $dbh = DBI\->connect("DBI:Excel:file=dbdtest.xls", undef, undef, \e%options);
.Ve
.PP
For more information please refer to the file sample/tex.pl which is included in this distribution.
.SS Metadata
.IX Subsection "Metadata"
The following attributes are handled by DBI itself and not by DBD::Excel,
thus they all work like expected:
.PP
.Vb 9
\&    Active
\&    ActiveKids
\&    CachedKids
\&    CompatMode             (Not used)
\&    InactiveDestroy
\&    Kids
\&    PrintError
\&    RaiseError
\&    Warn                   (Not used)
.Ve
.PP
The following DBI attributes are handled by DBD::Excel:
.IP AutoCommit 4
.IX Item "AutoCommit"
Always on
.IP ChopBlanks 4
.IX Item "ChopBlanks"
Works
.IP NUM_OF_FIELDS 4
.IX Item "NUM_OF_FIELDS"
Valid after \f(CW\*(C`$sth\->execute\*(C'\fR
.IP NUM_OF_PARAMS 4
.IX Item "NUM_OF_PARAMS"
Valid after \f(CW\*(C`$sth\->prepare\*(C'\fR
.IP NAME 4
.IX Item "NAME"
Valid after \f(CW\*(C`$sth\->execute\*(C'\fR; \f(CW\*(C`undef\*(C'\fR for Non-Select statements.
.IP NULLABLE 4
.IX Item "NULLABLE"
Not really working, always returns an array ref of one's.
Valid after \f(CW\*(C`$sth\->execute\*(C'\fR; \f(CW\*(C`undef\*(C'\fR for Non-Select statements.
.PP
These attributes and methods are not supported:
.PP
.Vb 4
\&    bind_param_inout
\&    CursorName
\&    LongReadLen
\&    LongTruncOk
.Ve
.PP
Additional to the DBI attributes, you can use the following attributes:
.IP xl_fmt 4
.IX Item "xl_fmt"
This attribute is used for setting the formatter class for parsing.
.IP xl_dir 4
.IX Item "xl_dir"
This attribute is used only with \f(CW\*(C`data_sources\*(C'\fR on setting the directory where 
Excel files ('*.xls') are searched. It defaults to the current directory (".").
.IP xl_vtbl 4
.IX Item "xl_vtbl"
assumes specified area as a table.
\&\fISee sample/tex.pl\fR.
.IP xl_skiphidden 4
.IX Item "xl_skiphidden"
skip hidden rows(=row height is 0) and hidden columns(=column width is 0).
\&\fISee sample/thidden.pl\fR.
.IP xl_ignorecase 4
.IX Item "xl_ignorecase"
set casesensitive or not about table name and columns. 
Default is sensitive (maybe as SQL::Statement).
\&\fISee sample/thidden.pl\fR.
.SS "Driver private methods"
.IX Subsection "Driver private methods"
.IP data_sources 4
.IX Item "data_sources"
The \f(CW\*(C`data_sources\*(C'\fR method returns a list of '*.xls' files of the current
directory in the form \f(CW"DBI:Excel:xl_dir=$dirname"\fR.
.Sp
If you want to read the subdirectories of another directory, use
.Sp
.Vb 2
\&    my($drh) = DBI\->install_driver("Excel");
\&    my(@list) = $drh\->data_sources( { xl_dir => \*(Aq/usr/local/xl_data\*(Aq } );
.Ve
.IP list_tables 4
.IX Item "list_tables"
This method returns a list of sheet names contained in the \f(CW\*(C`$dbh\-\*(C'\fR{file}>.
Example:
.Sp
.Vb 2
\&    my $dbh = DBI\->connect("DBI:Excel:file=test.xls");
\&    my @list = $dbh\->func(\*(Aqlist_tables\*(Aq);
.Ve
.SH TODO
.IX Header "TODO"
.IP "More tests" 4
.IX Item "More tests"
First of all...
.IP "Type and Format" 4
.IX Item "Type and Format"
The current version not support date/time and text formatting.
.IP Joins 4
.IX Item "Joins"
The current version of the module works with single table SELECT's
only, although the basic design of the SQL::Statement module allows
joins and the likes.
.SH "KNOWN BUGS"
.IX Header "KNOWN BUGS"
.IP \(bu 8
There are too many TODO things. So I can't determine what is BUG. :\-)
.SH AUTHOR
.IX Header "AUTHOR"
Kawai Takanori (Hippo2000) originally created this module. 
Currently maintained by Alexander Becker.
.SH "SEE ALSO"
.IX Header "SEE ALSO"
DBI, Spreadsheet::WriteExcel, Spreadsheet::ParseExcel, SQL::Statement
.SH COPYRIGHT
.IX Header "COPYRIGHT"
Copyright (c) 2001 KAWAI,Takanori
All rights reserved.
.PP
You may distribute under the terms of either the GNU General Public
License or the Artistic License, as specified in the Perl README file.