Darshan Parekh

Hi,

I need to upload the data in excel in to MSCRM. I am using ExcelSnapIn for this.

While reading cell by cell data, i am facing very wiered problem.

The Date value in Excel is getting converted as double in the dot net code.

My code looks like this.

Dim Sheet1 As Excel.Worksheet = DirectCast(Application.ActiveSheet, Excel.Worksheet)

Dim rng1 As Excel.Range = DirectCast(Sheet1.UsedRange(), Excel.Range)

Dim rowCount As Integer = rng1.Rows.Count

Dim colCount As Integer = rng1.Columns.Count

Dim dt As New DataTable

dt.Columns.Add("Status")

dt.Columns.Add("IssueType")

dt.Columns.Add("Title")

dt.Columns.Add("Description")

dt.Columns.Add("Assigned")

dt.Columns.Add("Priority")

dt.Columns.Add("DueDate")

dt.Columns.Add("DateCompleted")

dt.Columns.Add("ID")

dt.Columns.Add("ItemType")

dt.Columns.Add("Path")

Dim i, k As Integer

'Skipping first row as its a header

For i = 2 To rowCount

Dim dr As DataRow = dt.NewRow()

For k = 0 To colCount - 2

If rng1(i, k + 1).Value2 = Nothing Then

dr(k) = DBNull.Value

Else

dr(k) = rng1(i, k + 1).Value2

End If

Next

dt.Rows.Add(dr)

Next

Dim pu As New ProjectUpdate

For Each row As DataRow In dt.Rows

pu.UpdateIssue(row)

Next

Any idea as to whats wrong




Re: Visual Studio Tools for Office Problem Reading Excel Dates. Excel date gets converted to Double value in DotNet

Mohit Gupta - MSFT

Hi Darshan.

Dates are stored in Excel as doubles, only displayed as dates per the formatting on the sheet. The Value2 property on the cells returns the stored long value. You need to convert this value to the date yourself. You can use the DateTime.FromOADate method to do this conversion.

HTH

Mohit.






Re: Visual Studio Tools for Office Problem Reading Excel Dates. Excel date gets converted to Double value in DotNet

Darshan Parekh

Thanks mohit.