Adding a date in an Excel cell using OpenXML

Question

This is what I am doing:

CellFormat cellFormat = 
                new CellFormat() 
                { NumberFormatId = (UInt32Value)14U, 
                    FontId = (UInt32Value)0U, 
                    FillId = (UInt32Value)0U, 
                    BorderId = (UInt32Value)0U, 
                    FormatId = (UInt32Value)0U, 
                    ApplyNumberFormat = true };

sd.WorkbookPart.WorkbookStylesPart.Stylesheet.CellFormats.AppendChild<CellFormat>(cellFormat);

_dateStyleIndex = sd.WorkbookPart.WorkbookStylesPart.Stylesheet.CellFormats.Count() - 1;

and then somewhere later in my code

else if (type == DataTypes.DateTime)
{                
    DateTime dateTime = DateTime.Parse(text);
    double oaValue = dateTime.ToOADate();
    cell.CellValue = new CellValue(oaValue.ToString(CultureInfo.InvariantCulture));
    cell.DataType = new EnumValue<CellValues>(CellValues.Date);
    cell.StyleIndex = Convert.ToUInt32(_dateStyleIndex);               
}

However, when I validate the generated excel file with Open XML SDK Tool, I get the following validation error: The attribute 't' has invalid value 'd'. The Enumeration constraint failed.

What am I missing here? Thank you for your help in advance.

PS: Add, this is how the x:sheetData looks like. It gives me the validation error:

<x:sheetData xmlns:x="http://schemas.openxmlformats.org/spreadsheetml/2006/main">
  <x:row r="2">
    <x:c r="B2" t="s">
      <x:v>0</x:v>
    </x:c>
    <x:c r="C2" t="s">
      <x:v>1</x:v>
    </x:c>
    <x:c r="D2" t="s">
      <x:v>2</x:v>
    </x:c>
  </x:row>
  <x:row r="3">
    <x:c r="B3" t="s">
      <x:v>3</x:v>
    </x:c>
    <x:c r="C3" t="s">
      <x:v>6</x:v>
    </x:c>
    <x:c r="D3" s="1" t="d">
      <x:v>42634.906087963</x:v>
    </x:c>
  </x:row>
  <x:row r="4">
    <x:c r="B4" t="s">
      <x:v>4</x:v>
    </x:c>
    <x:c r="C4" t="s">
      <x:v>7</x:v>
    </x:c>
    <x:c r="D4" s="1" t="d">
      <x:v>42634.9062037037</x:v>
    </x:c>
  </x:row>
  <x:row r="5">
    <x:c r="B5" t="s">
      <x:v>5</x:v>
    </x:c>
    <x:c r="C5" t="s">
      <x:v>8</x:v>
    </x:c>
    <x:c r="D5" s="1" t="d">
      <x:v>42634.9062847222</x:v>
    </x:c>
  </x:row>
</x:sheetData>

Solution

For broadest compatability use CellValues.Number as the cell data type.

According to the docs, CellValues.Date is for Excel 2010, so you may wish to avoid it for complete backwards compatability with Excel 2007 (and potentially other applications).

//broadly supported - earliest Excel numeric date 01/01/1900
DateTime dateTime = DateTime.Parse(text);
double oaValue = dateTime.ToOADate();
cell.CellValue = new CellValue(oaValue.ToString(CultureInfo.InvariantCulture));
cell.DataType = new EnumValue<CellValues>(CellValues.Number);
cell.StyleIndex = Convert.ToUInt32(_numericDateCellFormatIndex); 


//supported in excel 2010 - not XLSX Transitional compliant 
DateTime dateTime = DateTime.Parse(text);
cell.CellValue = new CellValue(dateTime.ToString("s"));
cell.DataType = new EnumValue<CellValues>(CellValues.Date);
cell.StyleIndex = Convert.ToUInt32(_sortableDateCellFormatIndex);

This earlier more complete answer suggests that Excel 2010 doesn't use the 'sortable' CellValues.Date data type itself by default.

Presumably the reason for the CellValues.Date type is to overcome the limitations of the numeric date such as the earliest Excel numeric date being 01/01/1900.

digitalpreservation.gov explains some of the historical intention behind the date cell type, and this page explains that XLSX Transitional is the version used by mainstream real world applications (tested in 2014).

XLSX Strict has a value type for cells of date, using the Complete, Extended Format Calendar representations in ISO 8601. For reasons of backwards compatibility, this typed use of ISO 8601 dates is not permitted in XLSX Transitional.

Late in the ISO standardization process for OOXML, a proposal was made to adopt the ISO 8601 format for dates and times in spreadsheets.

The experts present at the ISO 29500 Ballot Resolution Meeting where votes were held on the outstanding proposals for the OOXML format were primarily experts in XML and in textual documents rather than with spreadsheets

Since the intent of the Transitional variant of ISO 29500 was to be compatible with the existing corpus of .xlsx documents and the applications designed to handle them, an amendment to Part 4 to disallow ISO 8601 dates in the Transitional variant was introduced. Secondly, ISO 8601 is a very flexible format, and any use in a context that aims at interoperability needs to be specific about which particular textual string patterns are expected for dates and times.

... Tests in November 2014 indicated that Google Sheets and Libre Office both created new documents in the Transitional variant

Comments

[Tacy Nathan]

Thanks, it works now. But I do not understand why CellValues should be number and not Date. What is the use of Date then?

[Chris Adol]

CellValues.Date is less broadly supported (answer updated).

[Chris Rae]

Amusing coming across this question as I co-wrote the date support in ISO8601 and worked on that Library of Congress paper. The d attribute was added to appease several people involved in the standardisation process who didn't like the fact that dates were stored as "number of days since 1st Jan 1900" in spreadsheets and hoped to change that by changing the spec. Which might be a nice idea, but it's been like that since Lotus 1-2-3. As Chris says, you should use CellValues, and store dates as a number of days since 1st January 1900.

[Dunge]

By using CellValues.Number, the cell show a number and not a date when opening the file in Excel?

[thomas]

Can someone tell me the values behind _numericDateCellFormatIndex and _sortableDateCellFormatIndex ?