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)

31 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