Menu
▾
▴
[perl-win32-gui-users] Using Win32::GUI and Win32::OLE together to connect to MSExcel
|
From: Eric H. <Ha...@sw...> - 2002-06-20 21:09:22
|
I am finding that MS-Excel does not shut down if I read my entire =
spreadsheet of 4550 rows with Win32::OLE. But if I read 500 rows and then =
close Excel and reopen it and read the next 500 rows (keeping track of =
where I left off) and continue this way to the end, then Excel closes down =
fine. Not sure why so I made a workaround as explained above. Must be a =
big memory overhead to read more than 500 rows without shutting down Excel =
when using Win32::OLE. It could be something to do with running in =
conjunction with Win32::GUI. Anybody had similar problems with OLE alone, =
or OLE and GUI combined applications?
Thanks,
Eric
Garland, Texas USA
# This application transfers data from an Excel spreadsheet straight into =
an Access database. Excel is shutdown
# after each read of 500 rows from the spreadsheet and restarted to read =
the next 500 rows and continued
# till all rows are read. The program keeps track of last record read in =
spreadsheet so knows where to begin
# reading of next 500 rows.
use Win32;
use Win32::GUI;
use Win32::ODBC;
use Win32::OLE;
use File::Copy;
$PWD=3DWin32::GetCwd(); # save application directory so ODBC knows where =
to look for MSAccess FILEDSN=20
$FILEDSN=3D"FILEDSN=3D$PWD\\MyAccessDB.dsn";
$M =3D new Win32::GUI::Menu(
"&File" =3D> "File",
" > &Port" =3D> "Port",
" > E&xit" =3D> "Exit",
"&Help" =3D> "Help",
" > &About - Excel port to Access" =3D> "About",
);
$W =3D new Win32::GUI::Window(
-title =3D> "Excel port to Access",
-left =3D> 100,=20
-top =3D> 100,=20
-width =3D> 500,=20
-height =3D> 200,
-menu =3D> $M,
-style =3D> ws_sysmenu,
-name =3D> "Window",
);
$B =3D new Win32::GUI::Bitmap("$PWD\\Logo.bmp");=20
$BITMAP =3D $W->AddLabel(
-left =3D> 0,=20
-top =3D> 0,
-style =3D> 14 | WS_VISIBLE,
-name =3D> "Bitmap",
);
$BITMAP->SetImage($B);
$SFont =3D new Win32::GUI::Font(
-name =3D> "Courier New",
-size =3D> 8,
-weight =3D> 700,
-height =3D> -11,
);
$Status =3D $W->AddStatusBar(
-name =3D> "Status",
-text =3D> "Select menu option File->Port to begin.",
-font =3D> $SFont,
);
$W->Show();
$W->BringWindowToTop();
#-------------------------------#
# Event Handler=20
#-------------------------------#
Win32::GUI::Dialog();
END {
$db->Close;
undef $db;
$sheet->Close;
undef $sheet;
$book->Close;
undef $book;
$Excel->Quit;
undef $Excel;
}
#------------------------------------#
# Event Subroutines
#------------------------------------#
sub Exit_Click {
return -1; # exits the Win32 GUI Event Handler Win32::GUI::Dialog();
}
sub About_Click {
$msg=3D"";
$msg=3D$msg . "Program: Excel port to Access\n";
$msg=3D$msg . " Version: 1.0\n";
$msg=3D$msg . " Date: June 2002\n";
$msg=3D$msg . " By: Eric Hansen\n";
Win32::GUI::MessageBox($W,"$msg",
"About - Excel port to Access",64,);
}
sub Port_Click {
=20
# Open File DialogBox showing files with=20
# *.xls extension starting in application directory
$file =3D "*.xls\0" . " " x 256;
$file =3D Win32::GUI::GetOpenFileName(
-owner =3D> $W,
-directory =3D> $PWD,
-title =3D> "Select MS-Excel file to port to MS-Access",=20
-file =3D> $file,
);
# if file is not valid, or contains no data, then return
if (! -s $file) {
Win32::GUI::MessageBox($W,"Microsoft Excel file notfound or is =
empty",
"Excel port to Access - Error & Abort",16,);
return -1;
}
=20
$M->{'Exit'}->Enabled(0); #-- Disable menu option
$M->{'Port'}->Enabled(0); #-- Disable menu option
$Status->Text("Connecting. Please wait...");
$Status->Update(); =20
=20
$stat=3Dcopy("$PWD\\CopyOfMyAccessDB.mdb","$PWD\\MyAccessDB.mdb"); =
#-- File::Copy=20
if ($stat !=3D 1) {
$msg=3D"Cannot COPY empty database file:\n" .
"$PWD\\CopyOfMyAccessDB.mdb\n" .
"to file:\n" .
"$PWD\\MyAccessDB.mdb\n";=20
Win32::GUI::MessageBox($W,$msg,
"Excel port to Access - Error & Abort",16,);
return -1;
} =20
=20
# connect to the MS-Access database =20
$db =3D new Win32::ODBC($FILEDSN); =20
if (! $db) {
$error=3DWin32::ODBC::Error();
Win32::GUI::MessageBox($W,
"Can't Establish Database Connection using file DSN:\n$FILEDSN\n$=
error",=20
"Excel port to Access - Error & Abort",16,);
return -1;
}
=20
$exitwhileloop=3D"N"; =20
$cnt=3D0;=20
$j=3D1;
=20
# 500 times 130 =3D 65,000 which is max rows in an Excel spreadsheet =
worksheet.
# We have to close Excel after each read of 500 records and restart =
Excel
# otherwise Excel won't shutdown. Some kind of memory problem =
develops.=20
=20
while ($j <=3D 130) {
=20
if ($j > 1) {
$sheet->Close;
undef $sheet;=20
$book->Close;
undef $book;
$Excel->Quit;
undef $Excel;
$Excel =3D Win32::OLE->new('Excel.Application');
$book =3D $Excel->Workbooks->Open($file);
$sheet =3D $book->Worksheets(1);
$start=3D($stop + 1);
$i=3D$start;
$stop=3D($start + 499);
} else {
$Excel =3D Win32::OLE->new('Excel.Application');
$book =3D $Excel->Workbooks->Open($file);
$sheet =3D $book->Worksheets(1);
$i=3D2;
$start=3D2;
$stop=3D500;
}
=20
for ($i=3D$start;$i<=3D$stop;$i++) {
=20
# build the sql insert statement
$sqltxt=3D"INSERT INTO MyAccessTBL VALUES ("; =20
=20
$cell=3D"A" . $i;
$market=3D$sheet->Range($cell)->{Value};
$market=3D~s/\'/\"/g;
$sqltxt=3D$sqltxt . "'" . $market . "'";
=20
$cell=3D"B" . $i;
$custnbr=3D$sheet->Range($cell)->{Value};
$custnbr=3D~s/\'/\"/g;
$sqltxt=3D$sqltxt . ",'" . $custnbr . "'";
=20
$cell=3D"C" . $i;
$custname=3D$sheet->Range($cell)->{Value};
$custname=3D~s/\'/\"/g;
$sqltxt=3D$sqltxt . ",'" . $custname . "'";
=20
$cell=3D"F" . $i;
$current=3Dsprintf("%9.2f",($sheet->Range($cell)->{Value}));
$sqltxt=3D$sqltxt . "," . $balance;
if ($market eq "" || $market eq " ") {
# this check tells us when we are at the end of our data =
since we know in this case
# that market field should not be blank. Otherwise we would =
continue reading to the
# 65,000th row in the spreadsheet which is a waste of time. =
=20
$exitwhileloop=3D"Y";
last;
}
=20
####################################################### =20
# execute the insert statement and get return status
#######################################################
$ret=3D$db->Sql($sqltxt); =20
if ($ret) {
$error=3D$db->Error; # capture the sql error message
=20
Win32::GUI::MessageBox($W,"$error\n$sqltxt",
"Excel port to Access - SQL Error($ret)",16,);
return -1; =20
}
$cnt++;
=20
$Status->Text($cnt);
$Status->Update();
=20
Win32::GUI::DoEvents(); # keep screen refreshed
=20
} # end of for loop
=20
if ($exitwhileloop eq "Y") {
last;
}
=20
$j++;
=20
} # end of while loop
=20
=20
Win32::GUI::MessageBox($W,"There were $cnt records ported to MS-Access.=
",=20
"Excel port to Access - Process Complete.",64,);
=20
$Status->Text("Exiting Program - Please wait ...");
$Status->Update();
sleep 3;
=20
return -1; =20
=20
} # Port_Click event
# End Script
|
