Exported numbers to Excel not recognized as number

[E_Jef]

Hi,

I have a written a little function to export a datatable to excel. This table can contain both string or double or decimal values.

Public Sub ExportDataTableToExcel(ByVal Table As DataTable,
ByVal TemplatePath As String,
ByVal SheetName As String,
ByVal StartingRowIndex As Integer,
ByVal StartingColumnIndex As Integer)

' Create new Application.
Dim oExcelApp As Excel.Application = New Excel.Application

' Open Excel spreadsheet.
Dim oWorkbook As Excel.Workbook = oExcelApp.Workbooks.Open(TemplatePath)

' Set reference to the specified excel sheet.
Dim oWorksheet As Excel.Worksheet = oWorkbook.Sheets.Item(SheetName)

Dim oRowIndex As Integer = StartingRowIndex
Dim oColumnIndex As Integer = StartingColumnIndex

' Loop the entire datatable
For i = 1 To Table.Rows.Count
Dim oRow As DataRow = Table.Rows.Item(i - 1)

' Loop each column in the table
For x = 0 To Table.Columns.Count - 1
oWorksheet.Cells(oRowIndex, oColumnIndex + x) = oRow.Item(x)
Next

' Increase the row index
oRowIndex += 1

' Insert a new row
If Not i = Table.Rows.Count Then
oWorksheet.Range("A" & oRowIndex).EntireRow.Insert()
End If

Next


' Show the result to the user.
oExcelApp.Visible = True

End Sub


Problem I have is that the numbers the are exported are not recognized as number in Excel. When I enter the cell and press enter this is corrected. Is there something I can do to avoid this behavior?

Continue reading...