Excel: Export as HTML with Formatting

Originally published 

Mon 16 Jan 2012

Editors Notes

Here's a small tip for making those automatically generated Excel Spreadsheets a tad prettier.

Original Post
Exporting to Excel from ASP or LotusScript is a nice, old, trick which has a lot of utility for web based applications. One challenge I'd had recently was that the data should come out formatted as dollars, but was stored as longs. I found the following info regarding how to inject formatting style commands into the style tag of the table cells.

 <td  STYLE="vnd.ms-excel.numberformat:$* #,##0">

That produces the desired dollar formatting. Some other options:

Thousands, number, with 2 decimals:
vnd.ms-excel.numberformat:#,##0.00_)[semicolon](#,##0.00);

Dollar, showing thousands, two decimals, black.
vnd.ms-excel.numberformat:$* #,##0.00_)[semicolon][Black]$* (#,##0.00);

As Text:
vnd.ms-excel.numberformat:@;

Number:
vnd.ms-excel.numberformat:0

Decimal:
vnd.ms-excel.numberformat:0.00

European two digit year:
vnd.ms-excel.numberformat:dd/mm/yy

European 4 digit year:
vnd.ms-excel.numberformat:dd/mm/yyyy

U.S. Date format
vnd.ms-excel.numberformat:mm/dd/yyyy