Beefy Boxes and Bandwidth Generously Provided by pair Networks
Problems? Is your data what you think it is?
 
PerlMonks  

Excel in Perl

by chuck_norris (Novice)
on Jan 30, 2008 at 14:52 UTC ( #665136=perlquestion: print w/ replies, xml ) Need Help??
chuck_norris has asked for the wisdom of the Perl Monks concerning the following question:

Hi monks, I'm trying to build a dynamic Excel table. For that purpose I build a little HTML file with tags like <table>,<TR>,<th> etc. and save it in a file with .xls extension. I get a very nice Excel table, but when I try to manipulate it - e.g sort it by a column , I get this error message : "This operation requires the merged cells to be identically sized". Do you know the meaning of this message? Maybe there is another simple way to create the Excel? Many thanks, Chuck

Comment on Excel in Perl
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 ]

      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
      Someone ... anyone ... please prove me wrong!

      ...roboticus

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?

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
    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?
      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&#39;Ã~Ico +le Française d&#39;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>
      Is there any problem with the HTML?

        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.

Log In?
Username:
Password:

What's my password?
Create A New User
Node Status?
node history
Node Type: perlquestion [id://665136]
Approved by halley
help
Chatterbox?
and the web crawler heard nothing...

How do I use this? | Other CB clients
Other Users?
Others having an uproarious good time at the Monastery: (13)
As of 2014-09-16 12:45 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    How do you remember the number of days in each month?











    Results (17 votes), past polls