3

Possible Duplicate:
Open Xml and Date format in Excel cell

I'm trying to take data from a DataGridView and generate an Excel file with the contents. I have a problem with dates though.

The problem is that, having written the worksheet, saved and opened it in Excel, my date cell has a date value in it and, if I do "Format cells", has the format I requested, yet Excel hasn't applied that format. For example, if I export the date value using ".ToOADate()", I get something like "40690.5270454051" in the cell. If I click into the cell, and then out again, the correct formatting is applied.

I've tried omitting the "ToOADate" call, and the same thing happens (I get a recognisable date value, but only formatted using my custom format after I click in and out of the cell).

I've tried setting the DataType of the OpenXml.Spreadsheet.Cell object to CellValues.Date (or EnumValue(CellValues.Date)) but then I get an error about "unreadable content" when I try and open the xlsx file.

Thanks, Ross

Community
  • 1
  • 1
Black Light
  • 2,358
  • 5
  • 27
  • 49

1 Answers1

2

What you need to do is set a value that is a floating point number (number of days since Jan 1, 1900 or 1904 - there is a setting in the file to decide which). The hh:mm:ss is the fraction part of the number.

You then set the cell format to be the datetime format you want. Excel will then display the number as the formatted date. This is how Excel stores all dates.

What I think is happening in your case is you are setting the value to a string. When you click on the cell, Excel parses the string, sees that it can be turned into a date, and then saves it as the floating point value.

Save the file after clicking and then look at the cell contents to see the difference.

David Thielen
  • 28,723
  • 34
  • 119
  • 193
  • Hi David, Thanks for your suggestions. I was indeed saving the cell value as a string (because I couldn't get CellValues.Date to work). Using the ToOADate() function and setting the cell value to a number worked just fine. I wonder how CellValues.Date is supposed to work ? – Black Light Jun 01 '11 at 08:16