DateTime recognition

Mar 2, 2011 at 8:36 AM

I have a problem with dates being recognized as Doubles. For instance, using ".GetString()" on a cell with the date "19-01-2011" returns the value 40562. ".GetDateTime().ToShortDateString()" throws an exception saying that a Double can't be cast to a DateTime and ".GetFormattedString()" throws a KeyNotFoundException ("The given key was not present in the dictionary."). Using ".DataType" I can see that the DataType is XLCellValues.Number. I have confirmed that the cell is indeed seen by Excel as a Date by right clicking on the cell and choosing "Format cell". Can this have something to do with the Date not being in the US format (January 19, 2011 is written as 19-01-2011 instead of 01-19-2011)?

Coordinator
Mar 3, 2011 at 4:58 AM

I have a broken build right now but will address this as soon as I'm done with the new data validation feature.

Coordinator
Mar 3, 2011 at 4:59 AM
This discussion has been copied to a work item. Click here to go to the work item and continue the discussion.
Coordinator
Mar 5, 2011 at 11:03 PM

I can't reproduce it with US nor UK locales. Could you please give me a short piece of code that reproduces this error?

Mar 7, 2011 at 7:00 AM

I found out the Location of the Date formatting in Excel was set to "Armenian", probably due to the origin of the file. While it is possible to choose a "Type" which displays the date with special characters, the Type chosen for my example shows the Date in Excel in the format described above (e.g. 19-01-2011). Is it possible to avoid the KeyNotFoundException somehow, without having to change the Location in the Date formatting in Excel on every single spreadsheet?

This is a code example:

originalWorkbook = new XLWorkbook(fileName);
originalWorksheet = originalWorkbook.Worksheet("FINAL DATA");
MessageBox.Show(originalWorksheet.Cell("A1").GetString()); // Number
MessageBox.Show(originalWorksheet.Cell("A1").Value.ToString()); // Number
MessageBox.Show(originalWorksheet.Cell("A1").GetFormattedString()); //Exception


I have uploaded an example file for the work item.

 

Coordinator
Mar 7, 2011 at 7:28 PM

Thanks for the feedback. In the end it wasn't because the location was set to Armenian. I'll release the fix soon.

Coordinator
Mar 10, 2011 at 7:47 PM

Fixed. The changes will appear on v0.47

Thanks again for the feedback.