none
Exported numbers to Excel not recognized as number RRS feed

  • Question

  • 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?

    Friday, January 11, 2019 12:56 PM

Answers

  • Hi

    Guessing here. You don't show the Table column types, so put a breakpoint on the line

    oWorksheet.Cells(oRowIndex, oColumnIndex + x) = oRow.Item(x)

    and check what oRow.Item(x) is - String perhaps? If not a number type then you would need to cast it accordingly.


    Regards Les, Livingston, Scotland


    • Edited by leshay Friday, January 11, 2019 1:02 PM
    • Marked as answer by E_Jef Friday, January 11, 2019 2:19 PM
    Friday, January 11, 2019 1:02 PM
  • Hello,

    Generally speaking Excel uses the first several rows (I believe top 8 rows) to determine data type of a column or if you give it a string w/o setting the format everything is treated as a string. In the following example I'm formatting as numeric via NumberFormat.

    Option Strict On
    Option Infer On
    
    Imports Excel = Microsoft.Office.Interop.Excel
    Imports System.Runtime.InteropServices
    Module OpenWorkSheets
        Public Sub OpenExcelWriteData(ByVal FileName As String, ByVal SheetName As String)
            Dim Proceed As Boolean = False
    
            Dim xlApp As Excel.Application = Nothing
            Dim xlWorkBooks As Excel.Workbooks = Nothing
            Dim xlWorkBook As Excel.Workbook = Nothing
            Dim xlWorkSheet As Excel.Worksheet = Nothing
            Dim xlWorkSheets As Excel.Sheets = Nothing
            Dim xlRange1 As Excel.Range = Nothing
            Dim xlInterior As Excel.Interior = Nothing
            'Dim xlColumns As Excel.Range = Nothing
    
            xlApp = New Excel.Application
            xlApp.DisplayAlerts = False
            xlWorkBooks = xlApp.Workbooks
            xlWorkBook = xlWorkBooks.Open(FileName)
    
            xlApp.Visible = False
    
            xlWorkSheets = xlWorkBook.Sheets
    
            For x As Integer = 1 To xlWorkSheets.Count
                xlWorkSheet = CType(xlWorkSheets(x), Excel.Worksheet)
    
                If xlWorkSheet.Name = SheetName Then
                    Proceed = True
                    Exit For
                End If
    
                Runtime.InteropServices.Marshal.FinalReleaseComObject(xlWorkSheet)
                xlWorkSheet = Nothing
    
            Next
    
            If Proceed Then
                Dim DictCellData As New Dictionary(Of String, String) _
                    From
                        {
                            {"A1", "Month"},
                            {"A2", "January"},
                            {"A3", "February"},
                            {"A4", "March"},
                            {"A5", "April"},
                            {"B1", "Money Spent"},
                            {"B2", "1000.00"},
                            {"B3", "1500.00"},
                            {"B4", "1200.00"},
                            {"B5", "1100.00"}
                        }
    
                ' Write cell, dispose object, repeat...
                For Each Item In DictCellData
                    xlRange1 = xlWorkSheet.Range(Item.Key)
                    xlRange1.Value = Item.Value
                    Marshal.FinalReleaseComObject(xlRange1)
                    xlRange1 = Nothing
                Next
    
                xlRange1 = xlWorkSheet.Range("A6")
                xlRange1.Value = "Total Expense"
    
                Marshal.FinalReleaseComObject(xlRange1)
                xlRange1 = Nothing
    
                xlRange1 = xlWorkSheet.Range("A7")
                xlRange1.Value = "Average Expense"
                Marshal.FinalReleaseComObject(xlRange1)
                xlRange1 = Nothing
    
                xlRange1 = xlWorkSheet.Range("B6")
                xlRange1.Formula = "=Sum(B2:B5)"
    
                Marshal.FinalReleaseComObject(xlRange1)
                xlRange1 = Nothing
    
                xlRange1 = xlWorkSheet.Range("B7")
                xlRange1.Formula = "=Average(B2:B5)"
                Marshal.FinalReleaseComObject(xlRange1)
                xlRange1 = Nothing
    
                xlRange1 = xlWorkSheet.Range("B2:B7")
                xlRange1.NumberFormat = "$#,##0.00"
    
                Marshal.FinalReleaseComObject(xlRange1)
                xlRange1 = Nothing
    
                xlWorkSheet.SaveAs(FileName)
            Else
                ' IMPORTANT NOTE
                ' For production throw an exception, for demoing a message
                ' This demo the only way the sheet does not exists if someone
                ' really tried to mess with this code outside with MS-Excel.
                '
                MessageBox.Show(SheetName & " not located.")
            End If
    
            xlWorkBook.Close()
            xlApp.UserControl = True
            xlApp.Quit()
    
    
            ReleaseComObject(xlRange1)
            ReleaseComObject(xlWorkSheets)
            ReleaseComObject(xlWorkSheet)
            ReleaseComObject(xlWorkBook)
            ReleaseComObject(xlWorkBooks)
            ReleaseComObject(xlApp)
        End Sub
        Private Sub ReleaseComObject(ByVal obj As Object)
            Try
                If obj IsNot Nothing Then
                    Marshal.ReleaseComObject(obj)
                    obj = Nothing
                End If
            Catch ex As Exception
                obj = Nothing
            End Try
        End Sub
    End Module
    

    Now if you are open to suggestions, there is a library SpreadSheetLight (totally free) which makes it easy to write numeric values. I wrote a code sample here on SpreadSheetLight then this one on exporting DataGridView to Excel using SpreadSheetLight.


    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

    • Marked as answer by E_Jef Friday, January 11, 2019 2:36 PM
    Friday, January 11, 2019 1:50 PM
    Moderator

All replies

  • Hi

    Guessing here. You don't show the Table column types, so put a breakpoint on the line

    oWorksheet.Cells(oRowIndex, oColumnIndex + x) = oRow.Item(x)

    and check what oRow.Item(x) is - String perhaps? If not a number type then you would need to cast it accordingly.


    Regards Les, Livingston, Scotland


    • Edited by leshay Friday, January 11, 2019 1:02 PM
    • Marked as answer by E_Jef Friday, January 11, 2019 2:19 PM
    Friday, January 11, 2019 1:02 PM
  • Hello,

    Generally speaking Excel uses the first several rows (I believe top 8 rows) to determine data type of a column or if you give it a string w/o setting the format everything is treated as a string. In the following example I'm formatting as numeric via NumberFormat.

    Option Strict On
    Option Infer On
    
    Imports Excel = Microsoft.Office.Interop.Excel
    Imports System.Runtime.InteropServices
    Module OpenWorkSheets
        Public Sub OpenExcelWriteData(ByVal FileName As String, ByVal SheetName As String)
            Dim Proceed As Boolean = False
    
            Dim xlApp As Excel.Application = Nothing
            Dim xlWorkBooks As Excel.Workbooks = Nothing
            Dim xlWorkBook As Excel.Workbook = Nothing
            Dim xlWorkSheet As Excel.Worksheet = Nothing
            Dim xlWorkSheets As Excel.Sheets = Nothing
            Dim xlRange1 As Excel.Range = Nothing
            Dim xlInterior As Excel.Interior = Nothing
            'Dim xlColumns As Excel.Range = Nothing
    
            xlApp = New Excel.Application
            xlApp.DisplayAlerts = False
            xlWorkBooks = xlApp.Workbooks
            xlWorkBook = xlWorkBooks.Open(FileName)
    
            xlApp.Visible = False
    
            xlWorkSheets = xlWorkBook.Sheets
    
            For x As Integer = 1 To xlWorkSheets.Count
                xlWorkSheet = CType(xlWorkSheets(x), Excel.Worksheet)
    
                If xlWorkSheet.Name = SheetName Then
                    Proceed = True
                    Exit For
                End If
    
                Runtime.InteropServices.Marshal.FinalReleaseComObject(xlWorkSheet)
                xlWorkSheet = Nothing
    
            Next
    
            If Proceed Then
                Dim DictCellData As New Dictionary(Of String, String) _
                    From
                        {
                            {"A1", "Month"},
                            {"A2", "January"},
                            {"A3", "February"},
                            {"A4", "March"},
                            {"A5", "April"},
                            {"B1", "Money Spent"},
                            {"B2", "1000.00"},
                            {"B3", "1500.00"},
                            {"B4", "1200.00"},
                            {"B5", "1100.00"}
                        }
    
                ' Write cell, dispose object, repeat...
                For Each Item In DictCellData
                    xlRange1 = xlWorkSheet.Range(Item.Key)
                    xlRange1.Value = Item.Value
                    Marshal.FinalReleaseComObject(xlRange1)
                    xlRange1 = Nothing
                Next
    
                xlRange1 = xlWorkSheet.Range("A6")
                xlRange1.Value = "Total Expense"
    
                Marshal.FinalReleaseComObject(xlRange1)
                xlRange1 = Nothing
    
                xlRange1 = xlWorkSheet.Range("A7")
                xlRange1.Value = "Average Expense"
                Marshal.FinalReleaseComObject(xlRange1)
                xlRange1 = Nothing
    
                xlRange1 = xlWorkSheet.Range("B6")
                xlRange1.Formula = "=Sum(B2:B5)"
    
                Marshal.FinalReleaseComObject(xlRange1)
                xlRange1 = Nothing
    
                xlRange1 = xlWorkSheet.Range("B7")
                xlRange1.Formula = "=Average(B2:B5)"
                Marshal.FinalReleaseComObject(xlRange1)
                xlRange1 = Nothing
    
                xlRange1 = xlWorkSheet.Range("B2:B7")
                xlRange1.NumberFormat = "$#,##0.00"
    
                Marshal.FinalReleaseComObject(xlRange1)
                xlRange1 = Nothing
    
                xlWorkSheet.SaveAs(FileName)
            Else
                ' IMPORTANT NOTE
                ' For production throw an exception, for demoing a message
                ' This demo the only way the sheet does not exists if someone
                ' really tried to mess with this code outside with MS-Excel.
                '
                MessageBox.Show(SheetName & " not located.")
            End If
    
            xlWorkBook.Close()
            xlApp.UserControl = True
            xlApp.Quit()
    
    
            ReleaseComObject(xlRange1)
            ReleaseComObject(xlWorkSheets)
            ReleaseComObject(xlWorkSheet)
            ReleaseComObject(xlWorkBook)
            ReleaseComObject(xlWorkBooks)
            ReleaseComObject(xlApp)
        End Sub
        Private Sub ReleaseComObject(ByVal obj As Object)
            Try
                If obj IsNot Nothing Then
                    Marshal.ReleaseComObject(obj)
                    obj = Nothing
                End If
            Catch ex As Exception
                obj = Nothing
            End Try
        End Sub
    End Module
    

    Now if you are open to suggestions, there is a library SpreadSheetLight (totally free) which makes it easy to write numeric values. I wrote a code sample here on SpreadSheetLight then this one on exporting DataGridView to Excel using SpreadSheetLight.


    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

    • Marked as answer by E_Jef Friday, January 11, 2019 2:36 PM
    Friday, January 11, 2019 1:50 PM
    Moderator
  • Hi

    Guessing here. You don't show the Table column types, so put a breakpoint on the line

    oWorksheet.Cells(oRowIndex, oColumnIndex + x) = oRow.Item(x)

    and check what oRow.Item(x) is - String perhaps? If not a number type then you would need to cast it accordingly.


    Regards Les, Livingston, Scotland


    It was undefined. Thanks for the help.
    Friday, January 11, 2019 2:20 PM