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.