Sunday, November 9, 2008

Styling Excel cells with mso-number-format

Styling Excel cells with mso-number-format
mso-number-format:"0"NO Decimals
mso-number-format:"0\.000"3 Decimals
mso-number-format:"\#\,\#\#0\.000"Comma with 3 dec
mso-number-format:"mm\/dd\/yy"Date7
mso-number-format:"mmmm\ d\,\ yyyy"Date9
mso-number-format:"m\/d\/yy\ h\:mm\ AM\/PM"D -T AMPM
mso-number-format:"Short Date"01/03/1998
mso-number-format:"Medium Date"01-mar-98
mso-number-format:"d\-mmm\-yyyy"01-mar-1998
mso-number-format:"Short Time"5:16
mso-number-format:"Medium Time"5:16 am
mso-number-format:"Long Time"5:16:21:00
mso-number-format:"Percent"Percent - two decimals
mso-number-format:"0%"Percent - no decimals
mso-number-format:"0\.E+00"Scientific Notation
mso-number-format:"\@"Text
mso-number-format:"\#\ ???\/???"Fractions - up to 3 digits (312/943)
mso-number-format:"\0022£\0022\#\,\#\#0\.00"£12.76
mso-number-format:"\#\,\#\#0\.00_ \;\[Red\]\-\#\,\#\#0\.00\ "

2 decimals, negative numbers in red and signed
(1.56 -1.56)

42 comments:

  1. Thanks very helpful. Another bit of cell formatting. To turn off Wrap Text in the cell add the following attribute to your TD tag, style='white-space:nowrap'

    ReplyDelete
  2. Very good site...it helps a lot in my work...thumbs up...great job!

    ReplyDelete
  3. Thank you bud. A great list.

    ReplyDelete
  4. How i can i applythe mso-number-format for differente columns?
    For example: in the same excel file can i have the field date and the filed amount.

    thanks

    ReplyDelete
  5. Thanks! You save my job =)

    ReplyDelete
  6. Hi i need text format in css, help me pls.

    ReplyDelete
  7. Petr, do you mean td {mso-number-format:\@;}

    ReplyDelete
  8. How about possitve number?

    ReplyDelete
  9. i guess mso-number-format:"0" is positive and negative integers

    ReplyDelete
  10. very informative! Good job

    ReplyDelete
  11. Replies
    1. that is for excel... you write a 'table', and style the 'td's with those css classes so that excel recognizes the style of the content

      Delete
    2. This is really helpful. Thanks for your post.

      Delete
  12. I have a formatted value of 1-5, when exported to excel it converts to 5-Jan...how can I use mso-number-format to maintain 1-5?

    ReplyDelete
  13. Anyone know where a reference list is with all the mso commands on it, like for text formatting, borders, alignment, etc??

    I have scoured the depths of google and I only come up with the pages talking about specific commands, but never a comprehensive list.

    ReplyDelete
  14. I was once looking for such a list, and only arrived to the few copied in this post.

    ReplyDelete
    Replies
    1. All I could find were these two sources for other mso commands:

      There are a few here scattered around in the left side links under the heading, "Subelements of Shape"

      http://msdn.microsoft.com/en-us/library/bb264073%28v=vs.85%29

      Also, on this blog entry, there are several commands in the style tags of the XML code.

      http://www.tcphp.org/meetings/code-to-generate-excel-documents

      Delete
  15. Great information. I was wondering what the heck does \@ mean and now I know.

    ReplyDelete
  16. This comment has been removed by a blog administrator.

    ReplyDelete
  17. mso stand for.. ? anyone?

    ReplyDelete
  18. ...hahahaha, you rock!:-)

    ReplyDelete
  19. ...wow - the [Red] trick is awsome! I incorporated it in my applicatins.

    Thank you.

    ReplyDelete
  20. mso-number-format:"Percent" using this giving me 10000.00%
    for value of 100.

    Help or suggestion really appriciated.

    ReplyDelete
    Replies
    1. when using PERCENT, Excel show 10% for a value of 0,1....

      so divide values by 100 before applying format

      Delete
  21. good knowledge.... beer:

    ReplyDelete
  22. Thank you! Solved my problem. I appreciate the sharing.

    ReplyDelete
  23. how to display 3.56 as 3 what is mso-number-format for it

    ReplyDelete
  24. It's fine..i have used date format so when i click date cell the formatting is custom but it will should be date.. can u help me?

    ReplyDelete
  25. Hi,

    Can anyone help me on formatting long number similar to this 5402199999999999999 from html to excelsheet.
    When I am using mso-number-format:0 in Style of a td tag, getting the number is excel as 5430689999999990000
    But the same format mso-number-format:0 is working fine for this number
    5402190000000000000.

    Any suggestion greatly appreciated.

    Thanks & Regards,
    Gopal

    ReplyDelete
  26. I want date format like "dd/mm", how I will achive this? I have tried force text option( "\@" ) but its not working.

    here is my html td style code **style="mso-number-format:mm/dd"**

    any help really appricated.

    ReplyDelete
  27. Hello do you know how can I apply the "mso-number-format: Currency" without the money symbol?? I need a little help!

    ReplyDelete
  28. When I set a custom formatting of [h]:mm the css file Excel generates shows: \[h\]\:mm. However when I define the mso-number-format as \[h\]\:mm I don't get the expected [h]:mm custom format. Worse: it generates an [h]:mm:ss custom format for any hour more than 24. Who needs those seconds! Any hint for elapsed time formatting? Thanks!

    ReplyDelete
  29. Hi,
    Do you know how I can put 'mso-number-format' in the xsl style sheet for table column? like .
    As I want to use xsl stylesheet to create an html page and set 'td' with mso-number-format:\@ css style.

    ReplyDelete

cancel script completely on ctrl-c

I found this question interesting: basically how to cancel completely a script and all child processes : You do this by creating a subro...