Basic HTML to Excel Formatting

ColdFusion , CSS , MS Office , Tips and Tricks , Default Add comments

I've come across this before but have never thought to blog about it, simply because it was quite an easy implementation in the end-up.

If you're exporting data from an HTML table to an Excel workbook, then the following will save you some time when trying to get Microsoft Excel to format the data. and display it as you desire.

Using Microsofts own Office XML format, you can style \ format the cell data using "CSS like" syntax.

For example, if you wish to format a date column, you can simply add a style attribute with the content style='mso-number-format:"mm\/dd\/yyyy"'.  This format is pretty self explanatory, but will format a given date in the format "10/01/2011" for 10th January 2011.

<td style="mso-number-format:"mm\/dd\/yyyy">
    10/01/2011
</td>

If you're a stickler for seperation, like I am, you can take this further by defining CSS Rules in a stylesheet and specifying the class name in your HTML elements.

.ukDateFormat {
    mso-number-format:"mm\/dd\/yyyy"
}

<td class="ukDateFormat">
    10/01/2011
</td>

Several formating options are also available for formatting other cell data.

Below are some common examples:

  • Plain Text: mso-number-format:\@
  • Format a number to 2 decimal places: mso-number-format:"0\.00"
  • Comma separators with 2 decimal places: mso-number-format:\#\,\#\#0\.00
  • Date \ Time Formating:
    • American date: mso-number-format:mm\/dd\/yy
    • Month name: mso-number-format:d\\-mmm\\-yyyy
    • Date and Time: mso-number-format:d\/m\/yyyy\ h\:mm\ AM\/PM
    • Short Date: mso-number-format:"Short Date" (05/06/2011)
    • Medium Date: mso-number-format:"Medium Date" (10-jan-2011)
    • Short Time: mso-number-format:"Short Time" (8:67)
    • Medium Time: mso-number-format:"Medium Time" (8:67 AM)
    • Long Time: mso-number-format:"Long Time"  (8:67:25:00)
  • Percentage: mso-number-format:Percent (To two decimal places)
  • Scientific Notation: mso-number-format:"0\.E+00"
  • Fractions - up to 3 digits: mso-number-format:"\#\ ???\/???"
  • Currency (£12.76): mso-number-format:"\0022£\0022\#\,\#\#0\.00"
  • 2 decimals, negative numbers in red and signed: mso-number-format:"\#\,\#\#0\.00_ \;\[Red\]\-\#\,\#\#0\.00\ " (1.86-1.66)
  • Accounting Format –5,(5): mso-number-format:”\\#\\,\\#\\#0\\.00_\\)\\;\\[Black\\]\\\\(\\#\\,\\#\\#0\\.00\\\\)”

As per the title - this is simply for "basic" formatting, so enjoy!

Bookmark and Share

13 responses to “Basic HTML to Excel Formatting”

  1. Misty Says:
    Cool! Dude awesome Stuff
  2. Jocelyn Says:
    Great tips!

    With the providing formatting tips, I could open my HTML page with MS Excel 2007, but except the below case.

    My HTML page has some Chinese characters, when I only it with MS Excel 2007, the Chinese characters became symbols. I have added this line "<meta http-equiv=Content-Type content="text/html; charset=utf-8">" in my HTML page.

    Do you have any idea to make the Chinese characters display correctly on the Excel worksheet?

    Thank you.
  3. Niall O'Doherty Says:
    Thanks Jocelyn!
    To be honest I'm not sure but if I were you I'd start with the below.

    You can check that it is enabled in the Office Language settings:

    Start > Programs > Microsoft Office > Microsoft Office Tools > Language Settings

    Under available languages you can try adding the Chinese variations.

    If that doesn't work you could try adding the Language Pack for Chinese (Simplified). Check out this site for details:

    http://www.pinyinjoe.com/windows-7/win7-pinyin-setup.htm


    Hope this helps!
  4. Thomas Says:
    Thanks a lot. This is really helpfull.
  5. Henk Op den brouw Says:
    Thanks!
    Do you have a solution to include a "comment" to a datacell?
    I tried "Acronym" end title with no aviale.
  6. PS Says:
    Greate article... Saved my time
  7. Bardamu Says:
    Great thanks a lot !
  8. Mark C Says:
    I am trying to output HTML to Excel. It is working great except I am getting trailing  for trailing spaces. I can't seem to TRIM() the trailing spaces either. I use style="mso-number-format:\@", but still get the trailing Â. Any ideas?
  9. Niall O'Doherty Says:
    Hi Mark,
    Pinged you an email, lets see if I can help you out.

    Cheers,
    Niall
  10. Aaron Says:
    So this is a kind of weird scenario, but do you know what value would reset the mso-number-format back to the default?

    I'm dealing with a scenario where the format is already set to \@ and I'm only able to override that with custom CSS. What I want is for it to go back to allowing Excel to dynamically assign formats based on cell contents.

    I've tried setting it to "none" and "null" (both work, but trigger an error opening the file). I've tried "General", but that forces "General" instead of dynamically determining the format. Thoughts?
  11. Niall O'Doherty Says:
    Hi Aaron,
    I'm not sure how you're applying the formatting, can you supply some sample code?

    Based on the assumption that you followed the guidelines above, you could try firstly removing the CSS rule. That should work if you don't require any formatting. Remember though that the content of the cell can also have an affect on the type Excel determines to be correct for the cell content.

    If you want to force it then you can try both:

    mso-number-format:"General" or mso-number-format:"\@"

    If you wish, drop me an email with the code samples and I'll see what I can do.
  12. Sai Krishna Says:
    To show amount fields in US format with negative amount should be parentheses is

    Style["mso-number-format"] = @"\#\#\,\#\#\#\,\#\#\#\,\#\#\#\,\#\#0_\)\;\[Black\]\\(\#\#\,\#\#\#\,\#\#\#\,\#\#\#\,\#\#0\)";
  13. Sai Krishna Says:
    What is the format to set the width of the cell?

Leave a Reply

Leave this field empty:

Powered by Mango Blog. Design and Icons by N.Design Studio