How to insert a date to an Open XML worksheet?

Question

I'm using Microsoft Open XML SDK 2 and I'm having a really hard time inserting a date into a cell. I can insert numbers without a problem by setting Cell.DataType = CellValues.Number, but when I do the same with a date (Cell.DataType = CellValues.Date) Excel 2010 crashes (2007 too).

I tried setting the Cell.Text value to many date formats as well as Excel's date/numeric format to no avail. I also tried to use styles, removing the type attribute, plus many other pizzas I threw at the wall…

Can anyone point me to an example inserting a date to a worksheet?

Answer 1

When creating new SpreadsheetDocument from scratch, for Date formatting to work, minimal Stylesheet has to be created.

Critical are those few lines:

new CellFormat
{
    NumberFormatId = 14,
    ApplyNumberFormat = true
})

Full Stylesheet class:

using (var spreadSheet = SpreadsheetDocument.Create(ms, SpreadsheetDocumentType.Workbook))
{
    // Workbook
    var workbookPart = spreadSheet.AddWorkbookPart();
    workbookPart.Workbook =
        new Workbook(new Sheets(new Sheet { Name = "Sheet1", SheetId = (UInt32Value) 1U, Id = "rId1" }));

    // Add minimal Stylesheet
    var stylesPart = spreadSheet.WorkbookPart.AddNewPart<WorkbookStylesPart>();
    stylesPart.Stylesheet = new Stylesheet
    {
        Fonts = new Fonts(new Font()),
        Fills = new Fills(new Fill()),
        Borders = new Borders(new Border()),
        CellStyleFormats = new CellStyleFormats(new CellFormat()),
        CellFormats =
            new CellFormats(
                new CellFormat(),
                new CellFormat
                {
                    NumberFormatId = 14,
                    ApplyNumberFormat = true
                })
    };

    // Continue creating `WorksheetPart`...

After Stylesheet is added, DateTime can be formatted:

if (valueType == typeof(DateTime))
{
    DateTime date = (DateTime)value;
    cell.CellValue = new CellValue(date.ToOADate().ToString(CultureInfo.InvariantCulture));

    // "StyleIndex" is "1", because "NumberFormatId=14"
    // is in the 2nd item of `CellFormats` array.
    cell.StyleIndex = 1; 
}

Note that StyleIndex value depends on the order of CellFormat items in the CellFormats array or the Stylesheet object. In this example NumberFormatId = 14 item on the 2nd item in the array.