Beefy Boxes and Bandwidth Generously Provided by pair Networks
Perl: the Markov chain saw

Re^2: Printing extracted Excel data into a HTML table

by ww (Archbishop)
on Dec 09, 2005 at 15:14 UTC ( #515559=note: print w/replies, xml ) Need Help??

in reply to Re: Printing extracted Excel data into a HTML table
in thread Printing extracted Excel data into a HTML table

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,  , 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.

Replies are listed 'Best First'.
Re^3: Printing extracted Excel data into a HTML table
by Siddartha (Curate) on Dec 09, 2005 at 15:28 UTC
    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://515559]
and the web crawler heard nothing...

How do I use this? | Other CB clients
Other Users?
Others examining the Monastery: (5)
As of 2021-02-26 07:49 GMT
Find Nodes?
    Voting Booth?

    No recent polls found