RE: [perl-win32-gui-users] Using Win32::GUI and Win32::OLE togeth er to connect to MSExcel

[Piske, H. <Har...@bo...>]

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