Try to import an excel file into xamgrid, but can't get right data for datatime.
in excel cell, the data like: 2012-10-10.
Code to import excel file: use OpenFileDialog to open excel file, then get data in Workbook. but data for above cell is: value =41223.0 , type = object{double}. The code is something like:
TestDate = DateTime.Parse(row.Cells[3].Value.ToString())
and this code stop the app.
How to resolve this problem?
Dates are actually stored as numbers in an Excel file. Excel only displays them as dates to the user because the format string on the cell is set so that the number converts to a date. If you are using the 12.1 version of the Excel library, you can now use the GetText(...) method on cells to get the date string. Otherwise, you will need to look at the FormatString property on the cell format returned from a cell's GetResolvedCellFormat() method. If it is a date format string, you can use the ExcelCalcValue.ExcelDateToDateTime(...) static methods to convert the number to a DateTime.
Actually I use DateTime.FromOADate as the solution. Thanks.
That will work fine for all dates except 1/1/1900 to 2/29/1900, but the ExcelDateToDateTime methods correct this problem. Basically, Lotus 1-2-3 had a bug which incorrectly viewed the year 1900 as a leap year. This was intentionally implemented in Excel for backward compatibility with Lotus.
Also, if your workbook has a date system which starts from 1904 instead of 1900, your dates will be off by 4 years (another thing which ExcelDateToDateTime corrects). Workbooks created on Macs use the 1904 date system by default and the workbook creator can decide which date system to use in the workbook options.
Thank you very much. I will use your solution.