I am using load() method to read data from Excel document to DataTable. I am constructing the DataTable based on the column values.
The issue is for Date columns the WorkSheetCell object shows value of '39815' for date value of '1/2/2009'
How to resolve this issue?
Infragistics.Excel.Workbook wk = Infragistics.Excel.Workbook.Load(openFile.FileName); foreach (Infragistics.Excel.WorksheetRow wkRow in wk.WindowOptions.SelectedWorksheet.Rows) { foreach (Infragistics.Excel.WorksheetCell wkCell in wkRow.Cells)
This probably isn't really an Infragistics issue. If you consider that by default Excel stores its date time values in serial date format you would expect to receive these same values back when not looking at the date in Excel's formatted view.
As you probably know Excel uses the 1900 date system in which the date 1/1/1900 is considered 1, the date 1/2/1900 is considered 2 and so on. The Excel time model is very similar in that it uses numeric values to represent the time of day. Time in Excel is represented as a decimal value. This works alongside the date model so that the date and time values can be combined to create a single value that represents a specific date and time. So for example 1/1/1900 6:00 PM would be represented as 1.75 (the 0.75 time value representing 75% of a day or 24 hours * 0.75).
What I would do to convert these values is simply create a new DateTime variable and set its initial value to the date 1/1/1900. The DateTime type has a method called AddDays which works in the same way as Excel in that it will take the whole number as the number of days and the decimal values to represent the time. After initializing your DateTime variable with the date of 1/1/1900 the AddDays method will return a new DateTime that is adjusted for the value stored by Excel.
As far as converting an Excel worksheet into a data table I don't know a single method calls that can be used to do this. So by default I would simply write my own little conversion function that would take care of this for me and even possibly make it an extension method.
Here is a web link of such a function that someone else wrote: http://www.aspspider.com/resources/Resource510.aspx
Hi Steve,
I have tried same way as you told that create a new DateTime variable and set its initial value to the date 1/1/1900.Then in AddDays give the number got in value property of cell but I got difference of two days. For example:
For date "1/1/2017" value property of cell will return 42736.
But if I create a new DateTime variable and set its initial value to the date 1/1/1900.Now use method AddDays and add 42736 days then I am getting output as "1/3/2017" which is not correct.I should get "1/1/2017".
Can you please help me out on this if you have any code to covert to correct value?
Thanks,Sunil