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)
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
This is the "Verified" answer, however, Mike Dour's answer works the most consistently across versions.
Hi Nenna,
The discussion here is referring to Windows Form. For web development, you should post in the ASP.Net forum.
Infragistics35.WebUI.Excel.v9.1
I am looking at the following Infragistics namespace:
Infragistics.Excel.CalcEngine Namespace : ExcelCalcValue Class
I have looked at the Infragistics.Excel.CalcEngine namespace. I do not see any static methods named DateTimeToExcelDate and ExcelDateToDateTime. I have also searched for them using the object browser and these static methods do not exist in 9.1 anyway. Have they been added to the latest release?
Thanks,
Nenna
You do not have to do manual conversions. The ExcelCalcValue class has static methods called DateTimeToExcelDate and ExcelDateToDateTime to convert between Excel's date format and DateTime values. In the WinForms and ASP.NET Excel assemblies, this class is in the Infragistics.Excel.CalcEngine namespace. In the Silverlight assembly, this class in is in the Infragistics.Silverlight.Excel.CalcEngine namespace.