Re: Printing extracted Excel data into a HTML table

[Siddartha]

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";
}

[ww]

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.

[Siddartha]

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.