Tacy Nathan Tacy Nathan - 2 months ago 119
C# Question

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>

Answer

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