Adding a date in an Excel cell using OpenXML

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