How do I alter an MS Excel spreadsheet with Perl?

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

Dear monks, This is related to perl interface with excel. I need to go through the columns and if the columns are blanks then I need to delete the column(shift to the left). I tried to convert the Visual basic code to Perl and it does not work. Please help. Thanks

20040402 Edit by BazB: Changed title from 'perl question'

Comment on How do I alter an MS Excel spreadsheet with Perl?

Replies are listed 'Best First'.
Re: How do I alter an MS Excel spreadsheet with Perl? by maa (Pilgrim) on Apr 02, 2004 at 20:04 UTC
Hi, Sue Since you've not posted any code, I'm not going to either, however here's one method you could use... Assumptions: You really do mean the column is empty... all 65525 rows... You know about Win32::OLE and you are able to write VB (or at least click Tools/Macros/Record then ALT+F11 when you stop) or read VB... Pseudocode Create Excel.Application object using Win32::OLE Create Workbook Object #$ExcelApp.WorkBooks("my book") Create Worksheet Object #$Book->Sheets("my sheet") Find Range "A1" using $Sheet->Cells(1,1)->Select Iterate over all cells in column Comparing the content to either "" or 0 (Excel is odd so this may depend on Formatting!) Move to next column and repeat If you get to the last cell and it has nothing in it.. There is a DeleteEntireColumn method lurking about. This isn't a really clever way to do it but I suspect your actual criteria doesn't involve checking 65525 rows, does it? HTH - Mark	[reply]
Re: How do I alter an MS Excel spreadsheet with Perl? by duff (Parson) on Apr 02, 2004 at 20:39 UTC
If it's not Excel you need to interface with directly, but rather files generated by Excel, have a look at Spreadsheet::ParseExcel and Spreadsheet::WriteExcel. duff	[reply]
Re: How do I alter an MS Excel spreadsheet with Perl? by Grygonos (Chaplain) on Apr 02, 2004 at 21:02 UTC
Show us what you have tried. Win32::OLE is fairly easy once you understand the object models. Grygonos	[reply]

Assumptions:

Pseudocode