Infragistics.Excel.Workbook.Load -- Date column read as number

Answered (Verified) This post has 1 verified answer | 13 Replies | 6 Followers Thread's RSS feed.

gsadamastula
Points 215
Replied On: Wed, Aug 19 2009 5:33 PM Reply

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)

  • Post Points: 20

Verified Answer

Steve Smith
Points 3,550
Answered (Verified) Replied On: Sat, Aug 22 2009 2:05 PM Reply
Verified by gsadamastula

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

  • Post Points: 85

All Replies

gsadamastula
Points 215
Replied On: Wed, Aug 19 2009 5:34 PM Reply

Also let me know if there is already a method to read data from Excel to .Net DataTable

thanks

  • Post Points: 5
Steve Smith
Points 3,550
Answered (Verified) Replied On: Sat, Aug 22 2009 2:05 PM Reply
Verified by gsadamastula

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

  • Post Points: 85
gsadamastula
Points 215
Replied On: Tue, Aug 25 2009 12:19 PM Reply

The solution works. Thanks a lot for your help.

  • Post Points: 5
dargzero
Points 110
Replied On: Tue, Nov 3 2009 8:33 PM Reply

Stragely, the answer that Steve wrote, for me returns a day 2 days more then the actual date in the spreadsheet.

I figured out, that you should either set the initial DateTime to 12/31/1899., or add 1 day less, since we are adding up the days from "day 0".

But I still don't know where the other extra day is coming from...

  • Post Points: 35
Steve Smith
Points 3,550
Replied On: Fri, Nov 6 2009 2:07 PM Reply

What version of Excel are you using?

  • Post Points: 20
dargzero
Points 110
Replied On: Fri, Nov 6 2009 7:37 PM Reply

Infragistics Excel for WPF 9.2 and Microsoft Excel 2007.

  • Post Points: 5
unisoftsystems
Points 47
Replied On: Thu, Dec 10 2009 10:19 AM Reply

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 format
If 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))

 

 

End If

 

 

  • Post Points: 5
unisoftsystems
Points 47
Replied On: Thu, Dec 10 2009 10:20 AM Reply

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

  • Post Points: 20
Mike Saltzman
Points 440,610
Infragistics Employee
Replied On: Fri, Dec 11 2009 9:26 AM Reply

This happens on some browsers. Not sure why.

In the future you can surround code in a code tag to format it better.

Mike Saltzman

Manager - Windows Forms Development

Infragistics, Inc.

  • Post Points: 5
Answered (Not Verified) Replied On: Sun, Dec 20 2009 12:47 AM Reply

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.

  • Post Points: 50
nsanoir
Points 20
Replied On: Mon, Jan 18 2010 3:29 AM Reply

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  

  • Post Points: 20
Mike Saltzman
Points 440,610
Infragistics Employee
Replied On: Tue, Jan 19 2010 11:55 AM Reply

Hi Nenna,

The discussion here is referring to Windows Form. For web development, you should post in the ASP.Net forum.

Mike Saltzman

Manager - Windows Forms Development

Infragistics, Inc.

  • Post Points: 5
mquinn
Points 125
Replied On: Mon, Mar 1 2010 1:27 PM Reply

This is the "Verified" answer, however, Mike Dour's answer works the most consistently across versions.

  • Post Points: 5
Page 1 of 1 (14 items) | RSS