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.

score 7 · Accepted Answer · answered Oct 14 '11 at 10:53

7

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..

answered Oct 14 '11 at 10:53

Swemail

98
1
7

+1 this had me pulling my hair out, all existing examples on the interweb assume starting with an empty stylesheet and add the NumberingFormats first using AppenChild(), but I was adding to existing template with no existing NumberFormats, so it was adding the numFmts section at the end of the stylesheet which is no good. I finally stumbled on the solution just before I found your 9yo answer. – controlbox Jan 28 '20 at 12:57

score 6 · Answer 2 · edited Jun 21 '17 at 18:06

6

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

edited Jun 21 '17 at 18:06

Wallace Kelly

15,565
8
50
71

answered May 20 '11 at 18:52

MvcCmsJon

609
7
12

10

It's not if you are dealing with large amounts of data. We identified a memory issue using this library with large datasets. – Phil Cooper Jan 21 '16 at 08:54
It's not really an answer to the question, per se. But THANK YOU for the suggestion! I was able to instantly replace days of struggling with OpenXML to do what I needed with 5 lines of code. – Andrew Bennett Oct 31 '22 at 03:49

score 6 · Answer 3 · edited Jun 21 '17 at 18:07

Another BIG BIG vote for: https://github.com/closedxml/closedxml

After trying to build my own class from the bits and pieces spread around the net, including StackOverFlow, I found the above mentioned library and in a few moments had a fully functional Excel file.

I have pasted my attempt below for the edification of anyone that feels the urge to complete it. It is partially complete and has issues with the date and string cell creation.

Before you try to use this class, first download closedXML and try that first.

Consider yourself warned.

    /// <summary>
    /// This class allows for the easy creation of a simple Excel document who's sole purpose is to contain some export data.
    /// The document is created using OpenXML.
    /// </summary>
    internal class SimpleExcelDocument : IDisposable
    {
        SheetData sheetData;

        /// <summary>
        /// Constructor is nothing special because the work is done at export.
        /// </summary>
        internal SimpleExcelDocument()
        {
            sheetData = new SheetData();
        }

        #region Get Cell Reference
        public Cell GetCell(string fullAddress)
        {
            return sheetData.Descendants<Cell>().Where(c => c.CellReference == fullAddress).FirstOrDefault();
        }
        public Cell GetCell(uint rowId, uint columnId, bool autoCreate)
        {
            return GetCell(getColumnName(columnId), rowId, autoCreate);
        }
        public Cell GetCell(string columnName, uint rowId, bool autoCreate)
        {
            return getCell(sheetData, columnName, rowId, autoCreate);
        }
        #endregion

        #region Get Cell Contents
        // See: http://msdn.microsoft.com/en-us/library/ff921204.aspx
        // 
        #endregion


        #region Set Cell Contents
        public void SetValue(uint rowId, uint columnId, bool value)
        {
            Cell cell = GetCell(rowId, columnId, true);
            cell.DataType = CellValues.Boolean;
            cell.CellValue = new CellValue(BooleanValue.FromBoolean(value));
        }
        public void SetValue(uint rowId, uint columnId, double value)
        {
            Cell cell = GetCell(rowId, columnId, true);
            cell.DataType = CellValues.Number;
            cell.CellValue = new CellValue(DoubleValue.FromDouble(value));
        }
        public void SetValue(uint rowId, uint columnId, Int64 value)
        {
            Cell cell = GetCell(rowId, columnId, true);
            cell.DataType = CellValues.Number;
            cell.CellValue = new CellValue(IntegerValue.FromInt64(value));
        }
        public void SetValue(uint rowId, uint columnId, DateTime value)
        {
            Cell cell = GetCell(rowId, columnId, true);
            //cell.DataType = CellValues.Date;
            cell.CellValue = new CellValue(value.ToOADate().ToString());
            cell.StyleIndex = 1;
        }
        public void SetValue(uint rowId, uint columnId, string value)
        {
            Cell cell = GetCell(rowId, columnId, true);
            cell.InlineString = new InlineString(value.ToString());
            cell.DataType = CellValues.InlineString;
        }
        public void SetValue(uint rowId, uint columnId, object value)
        {             
            bool boolResult;
            Int64 intResult;
            DateTime dateResult;
            Double doubleResult;
            string stringResult = value.ToString();

            if (bool.TryParse(stringResult, out boolResult))
            {
                SetValue(rowId, columnId, boolResult);
            }
            else if (DateTime.TryParse(stringResult, out dateResult))
            {
                SetValue(rowId, columnId,dateResult);
            }
            else if (Int64.TryParse(stringResult, out intResult))
            {
                SetValue(rowId, columnId, intResult);
            }
            else if (Double.TryParse(stringResult, out doubleResult))
            {
                SetValue(rowId, columnId, doubleResult);
            }
            else
            {
                // Just assume that it is a plain string.
                SetValue(rowId, columnId, stringResult);
            }
        }
        #endregion

        public SheetData ExportAsSheetData()
        {
            return sheetData;
        }

        public void ExportAsXLSXStream(Stream outputStream)
        {
            // See: http://blogs.msdn.com/b/chrisquon/archive/2009/07/22/creating-an-excel-spreadsheet-from-scratch-using-openxml.aspx for some ideas...
            // See: http://stackoverflow.com/questions/1271520/opening-xlsx-in-office-2003

            using (SpreadsheetDocument package = SpreadsheetDocument.Create(outputStream, SpreadsheetDocumentType.Workbook))
            {
                // Setup the basics of a spreadsheet document.
                package.AddWorkbookPart();
                package.WorkbookPart.Workbook = new Workbook();
                WorksheetPart workSheetPart = package.WorkbookPart.AddNewPart<WorksheetPart>();
                workSheetPart.Worksheet = new Worksheet(sheetData);
                workSheetPart.Worksheet.Save();

                // create the worksheet to workbook relation
                package.WorkbookPart.Workbook.AppendChild(new Sheets());
                Sheet sheet = new Sheet { 
                    Id = package.WorkbookPart.GetIdOfPart(workSheetPart), 
                    SheetId = 1, 
                    Name = "Sheet 1" 
                };
                package.WorkbookPart.Workbook.GetFirstChild<Sheets>().AppendChild<Sheet>(sheet);
                package.WorkbookPart.Workbook.Save();
                package.Close();
            }
        }

        #region Internal Methods
        private static string getColumnName(uint columnId)
        {
            if (columnId < 1)
            {
                throw new Exception("The column # can't be less then 1.");
            }
            columnId--;
            if (columnId >= 0 && columnId < 26)
                return ((char)('A' + columnId)).ToString();
            else if (columnId > 25)
                return getColumnName(columnId / 26) + getColumnName(columnId % 26 + 1);
            else
                throw new Exception("Invalid Column #" + (columnId + 1).ToString());
        }

        // Given a worksheet, a column name, and a row index, 
        // gets the cell at the specified column 
        private static Cell getCell(SheetData worksheet,
                  string columnName, uint rowIndex, bool autoCreate)
        {
            Row row = getRow(worksheet, rowIndex, autoCreate);

            if (row == null)
                return null;

            Cell foundCell = row.Elements<Cell>().Where(c => string.Compare
                   (c.CellReference.Value, columnName +
                   rowIndex, true) == 0).FirstOrDefault();

            if (foundCell == null && autoCreate)
            {
                foundCell = new Cell();
                foundCell.CellReference = columnName;
                row.AppendChild(foundCell);
            }
            return foundCell;
        }


        // Given a worksheet and a row index, return the row.
        // See: http://msdn.microsoft.com/en-us/library/bb508943(v=office.12).aspx#Y2142
        private static Row getRow(SheetData worksheet, uint rowIndex, bool autoCreate)
        {
            if (rowIndex < 1)
            {
                throw new Exception("The row # can't be less then 1.");
            }

            Row foundRow = worksheet.Elements<Row>().Where(r => r.RowIndex == rowIndex).FirstOrDefault();

            if (foundRow == null && autoCreate)
            {
                foundRow = new Row();
                foundRow.RowIndex = rowIndex;
                worksheet.AppendChild(foundRow);
            }
            return foundRow;
        } 
        #endregion
        #region IDisposable Stuff
        private bool _disposed;
        //private bool _transactionComplete;

        /// <summary>
        /// This will dispose of any open resources.
        /// </summary>
        public void Dispose()
        {
            Dispose(true);

            // Use SupressFinalize in case a subclass
            // of this type implements a finalizer.
            GC.SuppressFinalize(this);
        }

        protected virtual void Dispose(bool disposing)
        {
            // If you need thread safety, use a lock around these 
            // operations, as well as in your methods that use the resource.
            if (!_disposed)
            {
                if (disposing)
                {
                    //if (!_transactionComplete)
                    //    Commit();
                }

                // Indicate that the instance has been disposed.
                //_transaction = null;
                _disposed = true;
            }
        }
        #endregion
    }

score 3 · Answer 4 · answered Jun 05 '15 at 08:09

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);

This causes Repaired Records warning for me when opening the spreadsheet — Adam Garner, Jan 11 '18 at 12:09

score 3 · Answer 5 · answered Oct 01 '18 at 21:03

After trying numerous posts, I discovered that .ToOADate() and CellValues.Number and cell.StyleIndex = 4 were all needed...PLUS! All template date columns MUST be formatted to the default date style for the dates to be FILTERABLE as dates. Without these a error appeared upon opening the Excel file or the values were displayed as a number.

using DocumentFormat.OpenXml.Packaging;  
using DocumentFormat.OpenXml.Spreadsheet;  

//  IMPORTANT! All template date columns MUST be formatted to the default date style for the dates to be filterable as dates  
Cell cell = new Cell();  
dataMember = dataMember.ToOADate().ToString();  //OA Date needed to export number as Date  
cell.DataType = CellValues.Number;                
cell.CellValue = new CellValue(dataMember);  
cell.StyleIndex = 4;                            // Date format: M/d/yyyy

score 3 · Answer 6 · answered May 17 '11 at 15:48

3

I believe your problem is on NumberFormatId. Built-in number formats are numbered 0 - 163. Custom formats must start at 164.

answered May 17 '11 at 15:48

Samuel Neff

73,278
17
138
182

If I change the value of NumberFormatId and the StyleIndex (cell3) for 164, I still have an error. Is the StyleIndex must be have the same value as the NumberingFormatId? – Dan May 17 '11 at 17:05
@daner06, no, StyleIndex refers to the cell style index. – Samuel Neff May 18 '11 at 00:24
1

Is there a list of these NumberFormatId values somewhere? – David C May 23 '14 at 14:49
1

@DavidC Yes, its in ECMA-376 Part 1, Section 18.8.30 (numFmt) on page 1767. http://www.ecma-international.org/publications/standards/Ecma-376.htm – Frison Alexander Apr 23 '15 at 14:19

score 1 · Answer 7 · answered Apr 10 '14 at 17:51

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.

score 1 · Answer 8 · answered Apr 23 '15 at 14:29

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)

score 1 · Answer 9 · answered Jul 17 '18 at 19:48

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

score 1 · Answer 10 · edited May 23 '17 at 12:02

1

Your answer can be found at What indicates an Office Open XML Cell contains a Date/Time value?

The trick is that the StyleIndex (s-attribute) of the cell is literally an index into the list of cell styles (XF-elements) in the styles part of your spreadsheet. Each of those will point to the predefined number format ids that Samuel mentions. If I remember correctly the number format id you are looking for is either 14 or 15.

edited May 23 '17 at 12:02

Community

1
1

answered May 18 '11 at 06:57

Jesper Lund Stocholm

1,973
2
27
49

Unless there are 14 or 15 's inside , where ? > 14 or 15, you will receive this message. And if that 14'th or 15'th cell style doesn't actually equal the formatting you are looking for, you will get unexpected results. applyNumberFormat="1", and numFmtId="X", and , where X is a number > 163 should help. I have never got custom formatting to work without explicitly adding it to the xlsx file. – TamusJRoyce Jun 23 '11 at 19:43

score 0 · Answer 11 · answered Aug 21 '17 at 16:27

I have encountered the same problem concerns formatting date field after save document. And the solution is to add number format as follows:

new NumberingFormat() { NumberFormatId = 164, FormatCode = StringValue.FromString($"[$-409]d\\-mmm\\-yyyy;@") }

and add cell like this:

cell.CellValue = new CellValue(date.ToOADate().ToString());
cell.StyleIndex = 1; // your style index using numbering format above
cell.DataType = CellValues.Number;

OpenXml and Date format in Excel cell

11 Answers11

Linked

Related