Programmatically format a date in an Excel worksheet using the Office Open Xml SDK

Question

I am creating an Excel xlsx spreadsheet using the open Office XML package. I can add the dates on the worksheet by translating them into my "AO" date view and setting the Cell value to number. However, I cannot figure out how to add dd-mm-yy formatting to a cell.

I saw a lot of complex examples where you need to create a stylesheet from scratch, add a format to it and then reference that format, but I think there should be an easier way. I create my sheet from a template, and Excel already has built-in formats / styles. IMO I just need to load the stylesheet from the excel file that I use as a template, and then apply the format. I can’t figure out how to do this.

Answer 1

If you create xlsx from scratch, you cannot avoid creating a stylesheet. You minimally need a stylesheet and a cell element. The cell format element can refer to the built-in date format, in which case you can avoid the custom number format, but if you want the date format not to be built-in, you also need an element with a number format.

If you create xlsx from an xlsx template, you can reference an already defined cell format, but it can be very sensitive, since the links are based only on the index, so you are safer to look at a specific cell format to find one that matches the date format, which you want, as opposed to hard-coding the cell format index.

Answer 2

Try this - I used it when I tried to create my own number formats. This is a good example: Advanced style in Excel OpenXML

You still have to create a stylesheet, but this is a very good place to start. He has many styles in him when he demonstrates what can be done. Your stylesheet can be much simpler if you need only one format.

Also quickly review the list in this post. Your format may already be embedded in Excel. Built-in styles for Excel