Beefy Boxes and Bandwidth Generously Provided by pair Networks
The stupid question is the question not asked

Re: Printing extracted Excel data into a HTML table

by Siddartha (Curate)
on Dec 09, 2005 at 13:01 UTC ( #515528=note: print w/replies, xml ) Need Help??

in reply to Printing extracted Excel data into a HTML table

If it's just a table you want just generate the html table and then add whatever other html you want around it.
foreach my $WorksheetObject (@{$BookObject->{Worksheet}}) { print "<table>\n"; for(my $RowIndex = '0'; # starting at minimu+m row value defined $WorksheetObject->{MaxRow} && $RowIndex <= '7'; # ending at maximum +row value $RowIndex++) { print "<tr>"; for (my $ColumnIndex = '0'; defined $WorksheetObject->{MaxCol} && $ColumnIndex <= '2'; $ColumnIndex++) { my $CellObject = $WorksheetObject->{Cells}[$RowIndex][$Colum +nIndex]; # get cell #print "($RowIndex, $ColumnIndex)", print "<td>" . $CellObject->Value . "</td>" if ($CellObject +); } print "</tr>\n"; } print "</table>\n"; }

Replies are listed 'Best First'.
Re^2: Printing extracted Excel data into a HTML table
by ww (Archbishop) on Dec 09, 2005 at 15:14 UTC
    Siddartha's will work for a fully-populated spreadsheet though the hardcoding of $RowIndex and $ColumnIndex will have to be tweaked manually... or better, modified with code similar to that in bmann's and traveler's observations (which use Win32::OLE -- but IMO, that's a small price in this case).

    However, there are *potentially SERIOUS* problems if the spread is sparsely populated.

    By way of illustration, we'll identify rows and cells of the .html table using notation similar to that used by the spreadsheet... ie, A1 is top left cell, A2 is the leftmost cell of the second row, B2 is the second cell in the second row and so on. (In the diagram below, the first row and first col are solely to show the grid; they are not data)

    Trials with a sparsely populated .xls source:

       A    B   C   D   E   F      (r1 and c1 are labels only)
    1           C1  C2      F1
    2  A2       C2  D2  E2 
    3       B3          E3  F3

    The alphanumerics represent "locations" or "addresses" used as data in the spreadsheet cell. The html output (below) shows that empty cells are (as the code makes clear) ignored...

       A    B   C   D   E   F      (labels only)
    1  C1  C2   F1
    2  A2  C2  D2  E2 
    3  B3  E3  F3

    if positional relationships have significance, you just might care (in fact, you should care, a lot), and the cure will depend on what you're willing to accept in your html table. One notion that I would consider would be inserting a non-breaking space, &nbsp;, in the table cell identified with an empty spreadsheet cell.

    UNtested: I think the empty spread cells will be undef, so tweaking Siddartha's line 9, defined $WorksheetObject->{MaxCol} &&..., into an if...else construct would facilitate populating the .html table with non-breaking spaces when a spreadheet cell is empty.

    NB that the algo also produces empty <table>-</table> pairs at the end of its output.

      yes sorry, didn't think about that. This is all the original code with a few extra prints in. Didn't want to rewrite his code for him. But yes I did forget about empty rows. So instead of:
      print "<td>" . $CellObject->Value . "</td>" if ($CellObject); I would do: print ($CellObject ? ("<td>" . $CellObject->Value . "</td>") :"<td>&nb +sp;</td>");
      Updated: added &nbsp; as ww suggested. Otherwise it might render like crap.

Log In?

What's my password?
Create A New User
Node Status?
node history
Node Type: note [id://515528]
and all is quiet...

How do I use this? | Other CB clients
Other Users?
Others pondering the Monastery: (7)
As of 2017-05-29 16:44 GMT
Find Nodes?
    Voting Booth?