Excel in Perl

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

Replies are listed 'Best First'.
Re: Excel in Perl by halley (Prior) on Jan 30, 2008 at 14:57 UTC
If you're not interested in pretty formatting, just consider a CSV format instead. It's plain text, it can be generated and parsed very simply in Perl. See Text::CSV among other modules. Also, you can see it and edit it in Notepad or Emacs or whatever. Excel reads and writes CSV files just fine. -- `[ e d @ h a l l e y . c c ]`	[reply]
Re^2: Excel in Perl by roboticus (Chancellor) on Jan 31, 2008 at 09:48 UTC
One problem with using .CSV files and Excel is long digit strings (such as account numbers). I've not found a way in a .CSV file to get them to appear correctly in a spreadsheet. I'm always forced to add a prefix to turn it into a string, and the sheet shows the annoying prefix. So unless I'm missing something, there are a few cases (in addition to pretty formatting) where creating a spreadsheet is preferable to a .CSV file. Example: 1234567890123456,"1234567890123456","'1234567890123456",'1234567890123 +456 1234567890123456,"1234567890123456","'1234567890123456",'1234567890123 +456 1234567890123456,"1234567890123456","'1234567890123456",'1234567890123 +456 1234567890123456,"1234567890123456","'1234567890123456",'1234567890123 +456 1234567890123456,"1234567890123456","'1234567890123456",'1234567890123 +456 1234567890123456,"1234567890123456","'1234567890123456",'1234567890123 +456 1234567890123456,"1234567890123456","'1234567890123456",'1234567890123 +456 1234567890123456,"1234567890123456","'1234567890123456",'1234567890123 +456 [download] Someone ... anyone ... please prove me wrong! ...roboticus	[reply] [d/l]
Re: Excel in Perl by moklevat (Priest) on Jan 30, 2008 at 15:19 UTC
Excel thinks that some or all of the cells in the column you are trying to sort are merged. Perhaps you set up a heading with merged cells? To get the sort to work, you can either split all of the cells in the column or merge all of the cells in the column. However, to echo halley's point, perhaps you should consider taking a different approach to generating your files. Have you looked into Spreadsheet::WriteExcel?	[reply]
Re: Excel in Perl by whakka (Hermit) on Jan 30, 2008 at 15:25 UTC
You can ad hoc a solution with Win32::OLE and use its methods `$Sheet->Range("x:x")->{ColumnWidth}=.. $Sheet->Range("x:x")->Merge` [download] Or you can just re-write it entirely in Win32::OLE, sorting in perl before placing in Excel. Obviously the error is Excel-specific, so you could fiddle with your selection or un-merge some cells to make it work...do you have more details?	[reply] [d/l]
Re^2: Excel in Perl by chuck_norris (Novice) on Jan 31, 2008 at 10:41 UTC
Well, I would rather keep it simple like an HTML page. Right now, the HTML looks like this : <HTML><HEAD> <META HTTP-EQUIV='Content-Type' CONTENT='application/vnd.ms-excel'> </HEAD> <BODY> <TABLE border="1"> <TR> <TH align="center">Full Name</TH> <TH align="center">Test No' 1 WRD=(new)</TH> <TH align="center">Test No' 2 WRD=(science)</TH> <TH align="center">Pass/Fail</TH> <TH align="center">Comments</TH> </TR> <TR> <TD align="center" valign="top"> CKB03317 - DBPIA</TD> <TD align="left" valign="top">0 hits <br>Title= </TD> <TD align="left" valign="top">4450 hits <br>Title= </TD> <TD align="center" valign="top">FAIL</TD> <TD align="center" valign="top"></TD> </TR> <TR> <TD align="center" valign="top"> CKB05086 - Cefael (L'Ã~Ico +le FranÃ§aise d'AthÃ¨nes)</TD> <TD align="left" valign="top">3 hits <br>Title= </TD> <TD align="left" valign="top">2 hits <br>Title= </TD> <TD align="center" valign="top">FAIL</TD> <TD align="center" valign="top"></TD> </TR> <TR> <TD align="center" valign="top"> CKB02805 - Chinese Journals Fu +lltext Database (CNKI)</TD> <TD align="left" valign="top">624918 hits <br>Title= </TD> <TD align="left" valign="top">79947 hits <br>Title= </TD> <TD align="center" valign="top">FAIL</TD> <TD align="center" valign="top"></TD> </TR> </TABLE> <b>Total Resources Tested = 3<b><br> <b>Total Passed = 0<b><br> <b>Total Failed = 3<b><br> </BODY> </HTML> [download] Is there any problem with the HTML?	[reply] [d/l]
Re^3: Excel in Perl by jmcnamara (Monsignor) on Jan 31, 2008 at 14:48 UTC
The problem is caused by the `<br>` tags in the `"hits"` cells. This is causing Excel to assume that the other cells in the row are merged over 2 rows. This is turn prevents the data from being sorted. I don't have any suggestion on how to fix this in Html apart from splitting the "hits/Title" data into two rows. If you don't find an easy solution to the Html problem I'd suggest looking at Spreadsheet::WriteExcel which can handle all of the formatting that you are are trying to achieve without much more effort. -- John.	[reply]