Programmatically format a date in an excel sheet using Office Open Xml SDK

Question

I'm building an Excel xlsx spreadsheet using the office open XML SDK. I can add dates to the sheet by converting them to their "AO" date representation and setting the Cell Value to number. I can't, however, figure out how to add the dd-mm-yy formatting to the cell.

I've seen a lot of complicated examples where you have to create a stylesheet from scratch, add the format to it and then reference that format, but I figure there must be an easier way. I'm creating my sheet from a template, and Excel already has builtin formats/styles. IMO I should be able to just load the stylesheet from the excel file I'm using as a template and then apply the format. Can't figure out how to do it though.

Answer 1

If you're creating an xlsx from scratch you can't avoid creating the style sheet. You minimally need a style sheet and cell format element. The cell format element can reference a built-in date format in which case you can avoid the custom number format, but if you want a date format that isn't built-in you need a number format element too.

If you're creating an xlsx from a template xlsx, then you can reference a cell format that's already defined, but it can be very sensitive since the references are based only on index, so you are safer looking through the defined cell formats to find the one that matches the date format you want as opposed to hard-coding the cell format index.

Answer 2

Try this - I used it when I was trying to make my own number formats. It's a good example: Advanced Styling in Excel OpenXML

You will still have to create a style sheet but this is a very good place to start. He has quite a few styles in his as he demonstrates what can be done. Your style sheet could be lots simpler if you only want the one format.

Also have a quick look at the list in this post. Your format may be built into Excel already. Built in Styles for Excel