Re: Excel in Perl

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?

Comment on Re: Excel in Perl Download Code

Replies are listed 'Best First'.
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]