If all you want to do is read the whole excel sheet, here's how I go about
it without OLE.
The only thing that keeps annoying me is that even though I specify
FIRSTROWHASNAMES=0,
with my Excel drivers, it still won't return the topmost row. But reading
huge files is
not an issue - all it takes is the "... $db->Data() while $db->FetchRow ();"
bit in
the second-to-last line. The rest is preparation.
$excelfile = "c:\\my documents\\testfile.xls";
use Win32::ODBC;
$XLDrv = "";
END {
Win32::ODBC::ConfigDSN (ODBC_REMOVE_DSN, $XLDrv, "DSN=$dsn")
if $XLDrv;
}
$excelfile =~ /((?<!:)\\)?([^\\]+)$/;
$path = $`; # path without trailing backslash except if root
(x:\)
$dsn = $2; # filename without path - used as dsn
# create a temp DSN
%Drivers = Win32::ODBC::Drivers()
or die "can\'t get list of ODBC drivers - aborting\n";
($XLDrv) = grep /excel/i, keys %Drivers;
die "don't have an ODBC driver for Excel files - aborting\n"
unless $XLDrv;
Win32::ODBC::ConfigDSN (ODBC_REMOVE_DSN, $XLDrv, "DSN=$dsn");
Win32::ODBC::ConfigDSN (ODBC_ADD_DSN, $XLDrv,
("DSN=$dsn", "Description=$dsn", "DBQ=$dsn", "DEFAULTDIR=$path",
"FIRSTROWHASNAMES=0", "UID=", "PWD="))
or die "can't create ODBC DSN for $dsn - aborting\n";
$db = new Win32::ODBC ($dsn)
or die "cannot access $dsn\n";
# read "table" name of current sheet
$db->Catalog ("", "", "%", "");
$db->FetchRow ();
%Data = $db->DataHash ();
$Data{'TABLE_NAME'} =~ /([^']+)/;
# select data
# need [] around table name because it's like "Sheet1$"
$db->Sql ($_ = "select * from [$1]")
and die "'$_' returned ", $db->Error, "\n";
# retrieve data
print join "; ", $db->FieldNames (), "\n";
print join "; ", $db->Data(), "\n" while $db->FetchRow ();
$db->Close ();
