How to insert a date to an Open XML worksheet?

Question

I'm using Microsoft Open XML SDK 2 and I'm having a really hard time inserting a date into a cell. I can insert numbers without a problem by setting Cell.DataType = CellValues.Number, but when I do the same with a date (Cell.DataType = CellValues.Date) Excel 2010 crashes (2007 too).

I tried setting the Cell.Text value to many date formats as well as Excel's date/numeric format to no avail. I also tried to use styles, removing the type attribute, plus many other pizzas I threw at the wall...

Can anyone point me to an example inserting a date to a worksheet?

Thanks,

Answer 1

You have to convert datetime to double using function ToODate i.e.:

DateTime dtValue = DateTime.Now;
string strValue = dtValue.ToOADate().ToString(); //if Your culture use "," as 
// decimal separator change it to "."

then set it as CellValue

Cell cell;
cell.DataType = new EnumValue<CellValues>(CellValues.Date);
cell.CellValue = new CellValue(strValue);

rembember to format cell using datetime formatting, otherwise you will see double value, not date :)