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. AnonymousOctober 7, 2009 at 11:37 PM

    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. TrezJanuary 5, 2010 at 10:58 PM

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

    ReplyDelete
  3. AnonymousAugust 13, 2010 at 7:10 AM

    Thank you bud. A great list.

    ReplyDelete
  4. AnonymousAugust 26, 2010 at 2:56 AM

    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. AnonymousApril 4, 2011 at 7:07 AM

    Thanks! You save my job =)

    ReplyDelete
  6. UnknownApril 12, 2011 at 11:01 PM

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

    ReplyDelete
  7. AnonymousMay 12, 2011 at 9:19 AM

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

    ReplyDelete
  8. AnonymousJuly 7, 2011 at 10:54 AM

    How about possitve number?

    ReplyDelete
  9. AnonymousJuly 10, 2011 at 2:00 PM

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

    ReplyDelete
  10. AnonymousNovember 22, 2011 at 12:05 AM

    very informative! Good job

    ReplyDelete
  11. AnonymousDecember 29, 2011 at 4:41 AM

    thanks man!

    ReplyDelete
  12. DannylexJanuary 19, 2012 at 11:45 AM

    It doesn't work with jsp.

    ReplyDelete
    Replies
    1. siranJanuary 19, 2012 at 11:55 AM

      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. Rama krishna MulpuriApril 10, 2012 at 1:31 PM

      This is really helpful. Thanks for your post.

      Delete
  13. AnonymousMarch 23, 2012 at 4:25 AM

    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
  14. RaySuaveJune 20, 2012 at 1:45 PM

    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
  15. siranJune 20, 2012 at 1:59 PM

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

    ReplyDelete
    Replies
    1. RaySuaveJune 21, 2012 at 6:16 AM

      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
  16. DannyBoyJune 26, 2012 at 8:40 AM

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

    ReplyDelete
  17. UnknownAugust 31, 2012 at 12:27 AM

    Thank you. This really helps!.

    ReplyDelete
  18. Huu NhanSeptember 13, 2012 at 1:14 AM

    This comment has been removed by a blog administrator.

    ReplyDelete
  19. AnonymousNovember 12, 2012 at 12:24 AM

    mso stand for.. ? anyone?

    ReplyDelete
  20. AnonymousJanuary 7, 2013 at 9:26 AM

    ...hahahaha, you rock!:-)

    ReplyDelete
  21. AnonymousJanuary 7, 2013 at 9:34 AM

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

    Thank you.

    ReplyDelete
  22. SamJanuary 30, 2013 at 10:01 AM

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

    Help or suggestion really appriciated.

    ReplyDelete
    Replies
    1. AnonymousJanuary 30, 2013 at 12:11 PM

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

      so divide values by 100 before applying format

      Delete
  23. AnonymousFebruary 1, 2013 at 9:50 AM

    Gud1 thx

    ReplyDelete
  24. AnonymousMay 14, 2013 at 9:04 PM

    good knowledge.... beer:

    ReplyDelete
  25. AnonymousJune 3, 2013 at 10:56 AM

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

    ReplyDelete
  26. RutuJuly 3, 2013 at 12:21 AM

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

    ReplyDelete
  27. rajeshSeptember 27, 2013 at 3:39 AM

    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
  28. UnknownOctober 4, 2013 at 11:30 AM

    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
  29. UnknownApril 24, 2014 at 1:08 AM

    good knowledge :)

    ReplyDelete
  30. firojAugust 17, 2015 at 6:05 AM

    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
  31. ronnyAugust 17, 2015 at 10:43 AM

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

    ReplyDelete
  32. UnknownOctober 9, 2016 at 1:15 AM

    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
  33. UnknownJanuary 29, 2017 at 11:16 PM

    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

Subscribe to: Post Comments (Atom)

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...