Reading & Writing Excel Spreadsheets

merrymonk has asked for the wisdom of the Perl Monks concerning the following question:

I am using Win32::OLE::Const ‘Microsoft Excel’ to successfully read and write spreadsheets.
However, I believe that I am using only a small subset of the possible functions.
Does anyone know of sources (web sites books and so on) that give
* a summary of all the functions that can be used
* examples of how to use them.

I find that I can only use these features if Excel is also loaded on to the PC.
Is this absolutely necessary? If not what has to be done?

Many thanks in advance for any clues and help.

Comment on Reading & Writing Excel Spreadsheets

Replies are listed 'Best First'.
Re: Reading & Writing Excel Spreadsheets by Corion (Patriarch) on May 04, 2005 at 07:52 UTC
Win32::OLE is merely a gateway, a door, to Microsoft Excel. This is why you need Microsoft Excel installed on the machine where you want to use it. If you don't want to use it, consider SpreadSheet::WriteExcel. As Microsoft is the manufacturer of Excel, they also have vast documentation and example code for Excel on the MSDN. It is mostly written in VBA, but translation of VBA to Perl is not really hard.	[reply]
Re: Reading & Writing Excel Spreadsheets by displeaser (Hermit) on May 04, 2005 at 09:26 UTC
Hi, If you use the activestate version of Perl, it comes with an object browser, using this you can see what is available. For example, on selecting "Microsoft Excel 9.0 Object Library" in the main window, there are 2 lists populated with ole objects and functions available to the object. As Corion said MSDN is the best place for info. The later versions of the Activestate Perl dev kit come with a vbscript convertor, which may be useful when converting examples from msdn. Hope this helps.	[reply]
Re: Reading & Writing Excel Spreadsheets by tlm (Prior) on May 04, 2005 at 09:40 UTC
To read Excel, you can use Spreadsheet::ParseExcel; to write Excel, I second Corion's recommendation: Spreadsheet::WriteExcel. I have had great results with both of them. the lowliest monk	[reply]
Re^2: Reading & Writing Excel Spreadsheets by heathen (Acolyte) on May 04, 2005 at 13:09 UTC
Ditto tlm's remarks. I've used both ::ParseExcel and ::WriteExcel with much sucess. The only thing awkward I've found is creating and formating date/times. Alas, this problem rears it's ugly head all the time (PHP,MySQL,etc)	[reply]
Re^3: Reading & Writing Excel Spreadsheets by jmcnamara (Monsignor) on May 04, 2005 at 13:30 UTC
Creating dates and times is easier in more recent versions of Spreadsheet::WriteExcel with the addition of the write_date_time() and add_write_handler() worksheet methods. See the following example. -- John. See also: Spreadsheet::WriteExcel Google Group	[reply]
Re: Reading & Writing Excel Spreadsheets by gellyfish (Monsignor) on May 04, 2005 at 09:26 UTC
The Microsoft documentation for the Excel VBA is here. /J\	[reply]