How can make a consult from M$ Excel to a perl program?

[castroman]

Hello Monks: I've been a programming a CGI program, that access data in a datababase. I'd like to make consults to this database from Excel and be able to modify it? Can I make consults from EXCEL to a PERL program? I would like to reuse part of the code that i've already written.
Do you know how can I do this?

Thank you
                 Castroman

[orbital]

Here is a Win32::OLE example:

use Win32::OLE;

# If Excel is already running it uses the copy in memory
eval {$ex = Win32::OLE->GetActiveObject('Excel.Application')};
if ($@) {
   $ex = Win32::OLE->new('Excel.Application', sub {$_[0]->Quit;})
          or die;
}

# open an existing workbook
$book = $ex->Workbooks->Open( 'yourdoc.xls' );

#write to a cell
$book->Worksheets(1)->Cells(1,1)->Value = "whatever";

#save and exit
$book->Save;
undef $book;
undef $ex;

This should get you pointed in the right direction, if you are looking for more documentation check out some of the previous issue of "The Perl Journal" I know there was some good write up in there, and also O'Reillys Perl Resource Kit Win32 Edition has more then enough to tackle this type of job.

check this review as well Spreadsheet::WriteExcel

[jorg]

There is a CPAN module that wraps the Win32::Ole stuff in a nice class : Spreadsheet-ParseExcel

From the Readme :
This module allows you to get information from Excel file. This module can handle files of Excel95, 97 and 2000. (and now supports Excel4)

Hope this helps,
jorg

[c-era]

The easiest way to do this, is to buy the developer kit from activestate. This will allow you to make an activex componet with perl. After you have done that, you can now make calls to the componet from excel. Another option is to write an activex componet in c/vb and have it wrap around your perl program. Finaly, you can call your perl program from excel, then have your perl program use OLE to modify your excel spreadsheet.

[InfiniteSilence]

What???? I am sorry, but you do not have to create custom ActiveX components to access Excel/Access/Word from Perl. These already exist for you and they work very well.

May I introduce you to the Win32 libraries which contain the OLE object? The code to access Excel looks something like so:

use Win32::OLE;
$class = "Excel.Application";
$File = qq(c:\\temp\\Mytext.xls);
$Excel = Win32::OLE->GetActiveObject($Class);
$Excel->(Visible);

Now, the underlying mechanics of Win32 actually involve DLLs that are performing the low level calls to Windows resources, but why reinvent the wheel? Roth even offers you source code at his site. His book shows you how to extend Perl for Win32 as well.

For more information, look up Win32 at CPAN or buy Windows NT Win32 Perl Programming by Dave Roth.

Celebrate Intellectual Diversity

[eLore]

Reduce, reuse, recycle... Go perl!

You may also wish to consider obtaining modules for ActiveState via PPM, as well as CPAN.

[c-era]

That isn't what I said. I said you needed to create a custom activex compnent to access your perl program from Excel (which is what he asked for). What you show above is part of what is needed for my third sugestion.

P.S. In my opinion, OLE is better then Win32::OLE (as in OLE hasn't crashed and Win32::OLE has).

[jmcnamara]

It isn't exactly clear what you need to do so here are a few suggestions of the usual ways to get Perl to interoperate with Excel:



* DBI with DBD::ODBC or DBD::ADO. Excel files contain an internal index table which allows them to act like a database file. Using one of the standard Perl database modules you can connect to an Excel file as a database for reading or writing.

* The Win32::OLE module and office automation. This requires a Windows platform and an installed copy of Excel. This is the most powerful and complete method for interfacing with Excel for reading, writing or anything else. See the examples here and here.

* Spreadsheet::ParseExcel. This uses the OLE::Storage-Lite module to extract data from an Excel file.

Also, you can usually connect Excel directly to a database and manipulate the data in that way.

John.
--

[tinman]

Coincidentally, I was doing an app that interfaces with Excel sheets just yesterday.. all of these options are available..

there is also a somewhat more obscure workaround, where I open the Excel sheet as an OLE container and call the SaveAsCSV method... then use Text:CSV (Text::CSV_XS) to manipulate the data. One would have to convert the sheets offline.. I rejected this because I didn't really like to handle OLE inside a CGI app, just thought it might give me performance issues later on..

Spreadsheet::ParseExcel is quite good.. a new release was submitted to CPAN on the 17th of March (0.18, I do believe), and it allows display of an Excel sheet as a multi dimensional array..

Just a bit of insight on what I've done with Excel sheets.. if you want to see any examples (admittedly, almost all of it is off man pages ;o) just let me know..

[castroman]

Thanks to all!! What I intended was to make a consult from a Excel spreasheet to a Database (in this case MySQL). So I consider that the answers that fits more to my needs is the one of c-era. This projecto (I should have started by this) has the objective to manage de Database from Excel, and prepare ofers for our clients in Excel. Thanks to all.                Castroman  from Finisterrae.

[c-era]

If you need an example (or more help), just /msg me (I'm usually here most weekdays).