OpenXml and Date format in Excel cell

Question

I am trying to create an Excel file in xlsx format using OpenXML because I need to use that on a web server.

I don’t have any problem to fill the values in the sheets; however I am struggling to set the classic Date format in a cell.

Below a quick test using DocumentFormat.OpenXml and WindowsBase references.

class Program
{
    static void Main(string[] args)
    {
        BuildExel(@"C:\test.xlsx");
    }

    public static void BuildExel(string fileName)
    {
        using (SpreadsheetDocument myWorkbook =
               SpreadsheetDocument.Create(fileName,
               SpreadsheetDocumentType.Workbook))
        {
            // Workbook Part
            WorkbookPart workbookPart = myWorkbook.AddWorkbookPart();
            var worksheetPart = workbookPart.AddNewPart<WorksheetPart>();
            string relId = workbookPart.GetIdOfPart(worksheetPart);

            // File Version
            var fileVersion = new FileVersion { ApplicationName = "Microsoft Office Excel" };

            // Style Part
            WorkbookStylesPart wbsp = workbookPart.AddNewPart<WorkbookStylesPart>();
            wbsp.Stylesheet = CreateStylesheet();
            wbsp.Stylesheet.Save();

            // Sheets
            var sheets = new Sheets();
            var sheet = new Sheet { Name = "sheetName", SheetId = 1, Id = relId };
            sheets.Append(sheet);

            // Data
            SheetData sheetData = new SheetData(CreateSheetData1());

            // Add the parts to the workbook and save
            var workbook = new Workbook();
            workbook.Append(fileVersion);
            workbook.Append(sheets);
            var worksheet = new Worksheet();
            worksheet.Append(sheetData);
            worksheetPart.Worksheet = worksheet;
            worksheetPart.Worksheet.Save();
            myWorkbook.WorkbookPart.Workbook = workbook;
            myWorkbook.WorkbookPart.Workbook.Save();
            myWorkbook.Close();
        }
    }

    private static Stylesheet CreateStylesheet()
    {
        Stylesheet ss = new Stylesheet();

        var nfs = new NumberingFormats();
        var nformatDateTime = new NumberingFormat
        {
            NumberFormatId = UInt32Value.FromUInt32(1),
            FormatCode = StringValue.FromString("dd/mm/yyyy")
        };
        nfs.Append(nformatDateTime);
        ss.Append(nfs);

        return ss;
    }

    private static List<OpenXmlElement> CreateSheetData1()
    {
        List<OpenXmlElement> elements = new List<OpenXmlElement>();

        var row = new Row();

        // Line 1
        Cell[] cells = new Cell[2];

        Cell cell1 = new Cell();
        cell1.DataType = CellValues.InlineString;
        cell1.InlineString = new InlineString { Text = new Text { Text = "Daniel" } };
        cells[0] = cell1;

        Cell cell2 = new Cell();
        cell2.DataType = CellValues.Number;
        cell2.CellValue = new CellValue((50.5).ToString());
        cells[1] = cell2;

        row.Append(cells);
        elements.Add(row);

        // Line 2
        row = new Row();
        cells = new Cell[1];
        Cell cell3 = new Cell();
        cell3.DataType = CellValues.Date;
        cell3.CellValue = new CellValue(DateTime.Now.ToOADate().ToString());
        cell3.StyleIndex = 1; // <= here I try to apply the style...
        cells[0] = cell3;

        row.Append(cells);
        elements.Add(row);

        return elements;
    }

The code executed creates the Excel document. However when I try to open the document, I receive this message: “Excel found unreadable content in 'test.xlsx'. Do you want to recover the contents of this workbook? If you trust the source of this workbook, click Yes.”

If I remove the row:

cell3.StyleIndex = 1;

I can open the document but the date if not formatted, only the number of the date appears.

Thank you for your help to format the date.

Answer 1

Here is how to apply a custom date format on a cell. First, we have to lookup or create the format in the Workbook's Stylesheet:

// get the stylesheet from the current sheet    
var stylesheet = spreadsheetDoc.WorkbookPart.WorkbookStylesPart.Stylesheet;
// cell formats are stored in the stylesheet's NumberingFormats
var numberingFormats = stylesheet.NumberingFormats;

// cell format string               
const string dateFormatCode = "dd/mm/yyyy";
// first check if we find an existing NumberingFormat with the desired formatcode
var dateFormat = numberingFormats.OfType<NumberingFormat>().FirstOrDefault(format => format.FormatCode == dateFormatCode);
// if not: create it
if (dateFormat == null)
{
    dateFormat = new NumberingFormat
                {
                    NumberFormatId = UInt32Value.FromUInt32(164),  // Built-in number formats are numbered 0 - 163. Custom formats must start at 164.
                    FormatCode = StringValue.FromString(dateFormatCode)
                };
numberingFormats.AppendChild(dateFormat);
// we have to increase the count attribute manually ?!?
numberingFormats.Count = Convert.ToUInt32(numberingFormats.Count());
// save the new NumberFormat in the stylesheet
stylesheet.Save();
}
// get the (1-based) index of the dateformat
var dateStyleIndex = numberingFormats.ToList().IndexOf(dateFormat) + 1;

Then, we can apply our format to a cell, using the resolved styleindex:

cell.StyleIndex = Convert.ToUInt32(dateStyleIndex);

Answer 2

I hope the following links will be of help to future visitors.

First, Get the standards documentation.

ECMA-376 4th Edition Part 1 is the most helpful document. Sections in this document that relate to this question are:

18.8.30

18.8.31 (sematics of this shitty shit)

18.8.45 (definition of a style as understood by excel)

L.2.7.3.6 (How styles are referenced)

Answer 3

https://github.com/closedxml/closedxml is basically the correct answer I think.

Answer 4

For understanding why the CellValues.Date DataType does not work (at least not in all Excel versions it seems) please refer to this:

Adding a date in an Excel cell using OpenXML

For a complete, working, and well explained solution please refer to this:

OpenXML -Writing a date into Excel spreadsheet results in unreadable content

Answer 5

I had the same issue and ended up writing my own export to Excel writer. The code is in there to solve this problem, but you would truly be better off just using the whole exporter. It is fast and allows for substantial formatting of the cells. You can review it at

https://openxmlexporttoexcel.codeplex.com/

I hope it helps.

Answer 6

This blog helped me: http://polymathprogrammer.com/2009/11/09/how-to-create-stylesheet-in-excel-open-xml/

My problem was that I wanted to add NumberingFormats to the stylesheet rather than adding a new stylesheet altogether. If you want to to that, use

Stylesheet.InsertAt<NumberingFormats>(new NumberingFormats(), 0);

rather than

Stylesheet.AppendChild<NumberingFormats>(new NumberingFormats(), 0);

surprise, order counts..