Prevent Excel from Manipulating Exported Numbers

I ran into a small frustration exporting number data to Microsoft Excel using HTML. My Account Numbers were being translated into exponential numbers. I needed these numbers to show as text (without formatting).

This site gave a great tip on how to format those numbers to show correctly. In my instance… I set the style of my cell to mso-number-format:\@.

But, we’ll make a bit a bit of exception in this case, to adopt a Microsoft-specific style attribute. Actually, the output is still HTML 4.0 compliant; it just isn’t standard CSS. No problem; doesn’t mess up other browsers, and it’s just a hint for the Internet Explorer “Export to Excel” option anyway. At least Microsoft chose to do this the “right” way — formatting hints are declared as style sheet directives, thus cleanly separating data from formatting. Nice.

The solution? The mso-number-format style attribute, to be put on table cells ( ). Several number formats are available. These are some of the more common:

• mso-number-format:\@
  • text
• mso-number-format:”0\.000″
  • 3 decimals
• mso-number-format:\#\,\#\#0\.000
  • comma separators (and 3 decimals)
• mso-number-format:”mm\/dd\/yy”
  • Date format
• mso-number-format:”d\\-mmm\\-yyyy”
  • another date format
• mso-number-format:Percent
  • percent

14 March 2008 | programming, web design | Comments