Export an HTML table to a Spreadsheet (XLS)

If is often you wish to give users the ability to export a previously rendered HTML <TABLE> to the offline XLS Spreadsheet format. Instead of re-rendering the table, you can simply take the html block (via Javascript if on the client side) and then pass it back to the server who will then convert it to a spreadsheet object.

This is done using the very powerful html() and SpreadSheetNew OpenBD CFML functions.

<cfscript>
tableBody = "<table><tr><td>a</td><td>b</td><td>c</td></tr></table>";
doc = html( tableBody );

// Replace the anchor tags
e = doc.select("a");
for ( x=1; x <= ArrayLen(e); x++ ){
  ee = e[x];
  t = ee.html();
  ee.after( t );
  ee.remove();
}

// Create the spreadsheet object
xls = SpreadSheetNew();

// Loop over each TR/row of the table
tr = doc.select( "tbody tr" );
for ( trx=1; trx <= ArrayLen(tr); trx++ ){

  trrow	= tr[trx];

  // Loop over each column
  tds	= trrow.select("td, th");

  for ( tdx=1; tdx <= ArrayLen(tds); tdx++ ){
    tdcell    = tds[tdx];
    cellText  = tdcell.text();
    SpreadsheetColumnFitToSize( xls, tdx );
    SpreadsheetSetcellvalue ( xls, cellText, trx, tdx );
  }
}

xlsFile = ExpandPath("/WEB-INF/tmp/#GetTickCount()#.xls");
SpreadsheetWrite( xls, xlsFile, true );
</cfscript>