The above is mostly right, but I beg to differ on the numFmtId scheme. There is not an entire freedom about which numFmtId you can use since a number of these are built-in number formats that Excel understands without serializing them in the file (any consumer/implementer will have to make wild guesses, unfortunately).

The latest draft ECMA 1.4 does not list the built-in number formats. It simply mentions that there are built-in number formats. Those need special attention though, because some are locale dependent, some are not.

But in short, if the numFmtId is lower than 0xA4 (164 in decimal) then it's a built-in number format. Anything above are free to use for custom number formats. Note that there is a limit too. If I am not wrong in current and earlier Excel versions, you'll start getting error messages such as "too many custom number formats" or something like that if you have more than 512 or so. I am not sure that this limit is increased in Excel 2007 though (there is no mention of it in the Excel 12 blog : http://blogs.msdn.com/excel/archive/2005/09/26/474258.aspx). It should be easy to test it, but I haven't taken the time to do so.

i have tried setting the format for the date(also for number) first in the whole column then in the cells that contain data(because the fill works only if set to a cell, not the whole column(if I want the cell that contains the data to be colored),everything by code). nothing. my formatCode="dd.mm.yyyy;@" . the sheet1.xml s attribute is set to s="2"(the third node in xf) but it doesn't do anything.
the line is like this:
" xf numFmtId="2" fontId="3" fillId="3" borderId="3" xfId="0"/ "

and i don't have to set attributes such as applyNumberFormat="1", applyFont="1", ... I have tried without it and it works fine(cell color - fill, font color/size, border) except the numFmtId(this one i tried with/without applyNumberFormat="1").

does it matter what ID I set? are some of them reserved, because there is some mapping to numFmtId="49", but there is no such numfmt inside styles.xml.

Can anyone tell me where the date and time formatCodes are defined? For example in the above thread where formatCode="[$-409]dddd\,\ mmmm\ dd\,\ yyyy", what is the meaning of [$-409]. I'd like to be able to correctly interpret the field value from the formatCode.

I am trying to do the same.
But want to do it through the SDK instead of modifying xml by myself.
Anyone knows how to do this?

When working with the SDK for anything nontrivial, you'll probably build up a suite of helper methods to assist you with the SDK as it's rather low-level. Here's a very simple example that assumes you've got a stylesheet object and worksheet object already loaded. It should give you some idea as to the SDK classes you're going to need to interact with.

Thank you! nice code snippet.
I run through the spec on style again last few days and get it done. Not nicely as your code though.
But there are some questions that I don't know yet.
first one, the Id of NumberFormat is any unique random number that we choose?

second, the FormatCode that I reverse engineer from Excel generated file is something like
formatCode="yyyy"/"m"/"d;@" it is not one string. the spec said the '@' will be the original value,
something like formatCode="john @ billy", if the string value is "alex", the cell will show "john alex billy".
but for my datetime case, it will show 2009/12/14. And the strangest thing is, if I assign the format like this
{FormatCode = "yyyy/m/d"}, the resulting style.xml will show formatCode=""yyyy/m/d"".
I am now work around this by assigning "yyyy\"/\"m\"/\"d".

It seems that the Numberformat ID can be of our choosing as long as it isn't the same as a built-in. I chose 200 for my example but 100 should work just as well. For the en-US locale it seems that the built-in formats stop at 49, but I figure starting at 100 gives a nice buffer in case the spec doesn't match up quite to how Microsoft did things ;)

What are you trying to achieve with your custom date format with @ & ;? The use of semicolons is described in a section on number formatting, but its use is not described in the section on dates and times. You shouldn't need to escape or quote forward slashes to include them in your datetime formats. Codes like "yyyy/mm/dd" should work well fine. (There's a table in the ECMA spec doc that is prefixed by the text, "The following characters are displayed without the use of quotation marks." and these are $+(:%'{ and the space character.