Need to process Excel sheet through Perl

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

I am looking for some good Perl modules to process excel sheets.

I have a very basic requirement – I need a method to which I would provide the excel sheet and any column name of that excel sheet. It should return the list of all the elements present in that column which I can store may be in an array.

So just wanted to know the frequently used Perl modules for Excel sheet and which 1 would suffice my needs in best & in simple way.

--Parag

Comment on Need to process Excel sheet through Perl

Replies are listed 'Best First'.
Re: Need to process Excel sheet through Perl by CountZero (Bishop) on Dec 04, 2009 at 06:53 UTC
All the modules of the Spreadsheet family and especially Spreadsheet::ParseExcel are worthy of further examination. If the columns of your spreadsheet have headers, Spreadsheet::BasicReadNamedCol is probably all you need. CountZero A program should be light and agile, its subroutines connected like a string of pearls. The spirit and intent of the program should be retained throughout. There should be neither too little or too much, neither needless loops nor useless variables, neither lack of structure nor overwhelming rigidity." - The Tao of Programming, 4.1 - Geoffrey James	[reply]
Re: Need to process Excel sheet through Perl by Ratazong (Monsignor) on Dec 04, 2009 at 07:33 UTC
Hi! In case you work in a windows environment (and have Excel installed) Win32:: OLE is probably the best way to go. An excellent introduction can be found here: Using Win32::OLE and Excel - Tips and Tricks. Once you know the basic principles, the "Excel Visual Basic Reference" will help you in identifying objects and methods (you can find it by opening the "Visual Basic Editor" from Excel, then selecting "help"). You then "just" have to translate the visual-basic syntax to the perl one. HTH Rata	[reply]
Re: Need to process Excel sheet through Perl by paragkalra (Scribe) on Dec 04, 2009 at 10:02 UTC
Ok thanks a lot One more thing, in neither of the modules - 'Spreadsheet::ParseExcel' and 'Spreadsheet::BasicReadNamedCol', I couldn't find a method to get the column names (headers). Is it possible to extract column names of all the columns of the current excel sheet.	[reply]
Re: Need to process Excel sheet through Perl by paragkalra (Scribe) on Dec 04, 2009 at 11:43 UTC
So basically I am looking for a method to extract the default header names like 'A', 'B', 'C', 'D' etc.	[reply]
Re^2: Need to process Excel sheet through Perl by Ratazong (Monsignor) on Dec 04, 2009 at 13:16 UTC
So you want to have a way to convert the column-number to the A..XY-column-naming in Excel? This can be done by a simple algorithm (and without even "looking" at the spreadsheet...) A way how to do this can be found here . (Translating this visual-basic-script to perl is left as an exercise to the reader ;-) HTH Rata	[reply]
Re: Need to process Excel sheet through Perl by paragkalra (Scribe) on Dec 04, 2009 at 14:57 UTC
No no no... I am just looking for a function or a way which would return the list of all the column names present in an excel sheet By default it should return A, B, C ... so and so forth. Or if they are changed it will return the column names that are actually set. I hope I am clear on this. :)	[reply]