vb.net datatable to EXCEL

Question

For some reason when I am writing my datatable to excel I am getting the below error message. It appears that the error is happening within my second for loop.

Exception from HRESULT: 0x800A03EC

Public Shared Sub ExportExcel(ByVal dt As DataTable)

    Try

        Dim strFile As String = MYFilelocation
        Dim excel As New Microsoft.Office.Interop.Excel.ApplicationClass
        Dim wBook As Microsoft.Office.Interop.Excel.Workbook
        Dim wSheet As Microsoft.Office.Interop.Excel.Worksheet

        wBook = excel.Workbooks.Add()
        wSheet = wBook.ActiveSheet()

        Dim dc As System.Data.DataColumn
        Dim dr As System.Data.DataRow
        Dim colIndex As Integer = 0
        Dim rowIndex As Integer = 0

        For Each dc In dt.Columns
            colIndex = colIndex + 1
            excel.Cells(1, colIndex) = dc.ColumnName
        Next

        For Each dr In dt.Rows
            rowIndex = rowIndex + 1
            colIndex = 0
            For Each dc In dt.Columns
                colIndex = colIndex + 1
                excel.Cells(rowIndex + 1, colIndex) = dr(dc.ColumnName)
            Next
        Next

        wSheet.Columns.AutoFit()
        wBook.SaveAs(strFile)
        wBook.Close()

    Catch ex As Exception
        MessageBox.Show("there was an issue Exporting to Excel" & ex.ToString)
    End Try


End Sub

Answer 1

I have tried many methods to create multiple Excel files required for my project. But all are slow down the process. Ultimately, I found that this is the fastest method to create Excel files.

Imports:

Imports DocumentFormat.OpenXml.Packaging

Add references to project

• WindowsBase

• DocumentFormat.OpenXml

  Install-Package DocumentFormat.OpenXml

Full Code:

Private Sub ExportDataSet(ByVal DataTable_In As DataTable, ByVal Destination As String, Optional ds As DataSet = Nothing)

    Dim FileName As String = "ExcelFileName" &   ".xlsx"

    Using workbook = SpreadsheetDocument.Create(Destination & "\" & FileName, DocumentFormat.OpenXml.SpreadsheetDocumentType.Workbook)
        Dim workbookPart = workbook.AddWorkbookPart()
        workbook.WorkbookPart.Workbook = New DocumentFormat.OpenXml.Spreadsheet.Workbook()
        workbook.WorkbookPart.Workbook.Sheets = New DocumentFormat.OpenXml.Spreadsheet.Sheets()

        If Not DataTable_In Is Nothing Then

            Dim sheetPart = workbook.WorkbookPart.AddNewPart(Of WorksheetPart)()
            Dim sheetData = New DocumentFormat.OpenXml.Spreadsheet.SheetData()
            sheetPart.Worksheet = New DocumentFormat.OpenXml.Spreadsheet.Worksheet(sheetData)
            Dim sheets As DocumentFormat.OpenXml.Spreadsheet.Sheets = workbook.WorkbookPart.Workbook.GetFirstChild(Of DocumentFormat.OpenXml.Spreadsheet.Sheets)()
            Dim relationshipId As String = workbook.WorkbookPart.GetIdOfPart(sheetPart)
            Dim sheetId As UInteger = 1

            If sheets.Elements(Of DocumentFormat.OpenXml.Spreadsheet.Sheet)().Count() > 0 Then
                sheetId = sheets.Elements(Of DocumentFormat.OpenXml.Spreadsheet.Sheet)().[Select](Function(s) s.SheetId.Value).Max() + 1
            End If

            Dim sheet As DocumentFormat.OpenXml.Spreadsheet.Sheet = New DocumentFormat.OpenXml.Spreadsheet.Sheet() With {
            .Id = relationshipId,
            .SheetId = sheetId,
            .Name = DataTable_In.TableName
         }
            sheets.Append(sheet)
            Dim headerRow As DocumentFormat.OpenXml.Spreadsheet.Row = New DocumentFormat.OpenXml.Spreadsheet.Row()
            Dim columns As List(Of String) = New List(Of String)()

            For Each column As System.Data.DataColumn In DataTable_In.Columns
                columns.Add(column.ColumnName)
                Dim cell As DocumentFormat.OpenXml.Spreadsheet.Cell = New DocumentFormat.OpenXml.Spreadsheet.Cell()
                cell.DataType = DocumentFormat.OpenXml.Spreadsheet.CellValues.String
                cell.CellValue = New DocumentFormat.OpenXml.Spreadsheet.CellValue(column.ColumnName)
                headerRow.AppendChild(cell)
            Next

            sheetData.AppendChild(headerRow)

            For Each dsrow As System.Data.DataRow In DataTable_In.Rows
                Dim newRow As DocumentFormat.OpenXml.Spreadsheet.Row = New DocumentFormat.OpenXml.Spreadsheet.Row()

                For Each col As String In columns
                    Dim cell As DocumentFormat.OpenXml.Spreadsheet.Cell = New DocumentFormat.OpenXml.Spreadsheet.Cell()
                    cell.DataType = DocumentFormat.OpenXml.Spreadsheet.CellValues.String
                    cell.CellValue = New DocumentFormat.OpenXml.Spreadsheet.CellValue(dsrow(col).ToString())
                    newRow.AppendChild(cell)
                Next

                sheetData.AppendChild(newRow)
            Next
        Else
            For Each table As System.Data.DataTable In ds.Tables
                Dim sheetPart = workbook.WorkbookPart.AddNewPart(Of WorksheetPart)()
                Dim sheetData = New DocumentFormat.OpenXml.Spreadsheet.SheetData()
                sheetPart.Worksheet = New DocumentFormat.OpenXml.Spreadsheet.Worksheet(sheetData)
                Dim sheets As DocumentFormat.OpenXml.Spreadsheet.Sheets = workbook.WorkbookPart.Workbook.GetFirstChild(Of DocumentFormat.OpenXml.Spreadsheet.Sheets)()
                Dim relationshipId As String = workbook.WorkbookPart.GetIdOfPart(sheetPart)
                Dim sheetId As UInteger = 1

                If sheets.Elements(Of DocumentFormat.OpenXml.Spreadsheet.Sheet)().Count() > 0 Then
                    sheetId = sheets.Elements(Of DocumentFormat.OpenXml.Spreadsheet.Sheet)().[Select](Function(s) s.SheetId.Value).Max() + 1
                End If

                Dim sheet As DocumentFormat.OpenXml.Spreadsheet.Sheet = New DocumentFormat.OpenXml.Spreadsheet.Sheet() With {
                .Id = relationshipId,
                .SheetId = sheetId,
                .Name = table.TableName
             }
                sheets.Append(sheet)
                Dim headerRow As DocumentFormat.OpenXml.Spreadsheet.Row = New DocumentFormat.OpenXml.Spreadsheet.Row()
                Dim columns As List(Of String) = New List(Of String)()

                For Each column As System.Data.DataColumn In table.Columns
                    columns.Add(column.ColumnName)
                    Dim cell As DocumentFormat.OpenXml.Spreadsheet.Cell = New DocumentFormat.OpenXml.Spreadsheet.Cell()
                    cell.DataType = DocumentFormat.OpenXml.Spreadsheet.CellValues.String
                    cell.CellValue = New DocumentFormat.OpenXml.Spreadsheet.CellValue(column.ColumnName)
                    headerRow.AppendChild(cell)
                Next

                sheetData.AppendChild(headerRow)

                For Each dsrow As System.Data.DataRow In table.Rows
                    Dim newRow As DocumentFormat.OpenXml.Spreadsheet.Row = New DocumentFormat.OpenXml.Spreadsheet.Row()

                    For Each col As String In columns
                        Dim cell As DocumentFormat.OpenXml.Spreadsheet.Cell = New DocumentFormat.OpenXml.Spreadsheet.Cell()
                        cell.DataType = DocumentFormat.OpenXml.Spreadsheet.CellValues.String
                        cell.CellValue = New DocumentFormat.OpenXml.Spreadsheet.CellValue(dsrow(col).ToString())
                        newRow.AppendChild(cell)
                    Next

                    sheetData.AppendChild(newRow)
                Next
            Next
        End If


    End Using
End Sub

Answer 2

Try this:

    Dim workbook = New ExcelFile
    Dim worksheet = workbook.Worksheets.Add("DataTable to Sheet")

    Dim dataTable = New DataTable

    dataTable.Columns.Add("ID", Type.GetType("System.Int32"))
    dataTable.Columns.Add("FirstName", Type.GetType("System.String"))
    dataTable.Columns.Add("LastName", Type.GetType("System.String"))

    dataTable.Rows.Add(New Object() {100, "John", "Doe"})
    dataTable.Rows.Add(New Object() {101, "Fred", "Nurk"})
    dataTable.Rows.Add(New Object() {103, "Hans", "Meier"})
    dataTable.Rows.Add(New Object() {104, "Ivan", "Horvat"})
    dataTable.Rows.Add(New Object() {105, "Jean", "Dupont"})
    dataTable.Rows.Add(New Object() {106, "Mario", "Rossi"})

    worksheet.Cells(0, 0).Value = "DataTable insert example:"

    ' Insert DataTable to an Excel worksheet.
    worksheet.InsertDataTable(dataTable,
        New InsertDataTableOptions() With
        {
            .ColumnHeaders = True,
            .StartRow = 2
        })

    workbook.Save("DataTable to Sheet.xlsx")