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 happens on some browsers. Not sure why.
In the future you can surround code in a code tag to format it better.
by the way, I've no idea where all the extra line spaces came from in the code - sorry about that as it looks really messy
I experienced the same thing and made the change that dargzero described. I also subtracted 1 more for the other additional day.
Another anomoly is that the underlying formats in Excel are also different to VB.Net. I need to format a column name in a datatable to be the same as the date. In excel the cell format was mmm-yy and I needed MMM-yy so I used the following to change that:
For Each myCell In myworksheet.Rows(1).Cells
Dim myDate As DateTime = #12/31/1889 12:00:00 PM#
Dim myDateString As String
' Default to MMM-yy
Dim myFormat As String = "MMM-yy"
' Get the format of the cell for the Column name formatIf myCell.ColumnIndex = 11 Then
myFormat = myCell.CellFormat.FormatString
' Replace the m for minute with the M for month myFormat = myFormat.Replace(
"m", "M")
End If
If myCell.ColumnIndex > 10 And (Not myCell.Value Is Nothing And myCell.Value.ToString().Length > 0) Then
myDate = myDate.AddDays(myCell.Value)
myDateString = myDate.ToString(myFormat)
myTable.Columns.Add(
New DataColumn(myDateString))
Infragistics Excel for WPF 9.2 and Microsoft Excel 2007.
What version of Excel are you using?