How to fix 0x800A03EC error when exporting a Datatable to Excel-CodePudding

I am trying to export a datatable to Excel and am getting the following Exception when running wb.SaveAs(path):

Exception from HRESULT: 0x800A03EC

Initially I was getting this error when setting the worksheet range. I fixed this by making it non-zero indexed. However, the datatable array is still zero indexed.

Unfortunately, no additional details for the error are given.

Code:

        Dim app As New Excel.Application
        Dim wb As Excel.Workbook = app.Workbooks.Add()
        Dim ws As Excel.Worksheet
        Dim strFN As String = "MyFileName.xlsx"   
        Dim dt As New DataTable

        Using da As New DataAdapter(dif)
            da.SetSelectCommand(SQL)
            da.Fill(dt)
        End Using

        ws = wb.Sheets.Add(After:=wb.Sheets(wb.Sheets.Count))
        DataTableToExcel(dt, ws, "TableName")

        wb.SaveAs(path)
        wb.Close()

Private Sub DataTableToExcel(dt As DataTable, ws As Excel.Worksheet, TabName As String)
    Dim arr(dt.Rows.Count, dt.Columns.Count) As Object
    Dim r As Int32, c As Int32

    For r = 0 To dt.Rows.Count - 1
        For c = 0 To dt.Columns.Count - 1
            arr(r, c) = dt.Rows(r).Item(c)
        Next
    Next

    ws.Name = TabName   
    c = 0

    For Each column As DataColumn In dt.Columns
        If column.ColumnName.Length > 4 Then
            If column.ColumnName.Substring(column.ColumnName.Length - 4, 4) = "_ID" Then
                ws.Cells(1, c   1) = column.ColumnName.Replace("_", " ").Substring(0, column.ColumnName.Length - 4)
            Else
                ws.Cells(1, c   1) = column.ColumnName.Replace("_", " ")
            End If
        Else
            ws.Cells(1, c   1) = column.ColumnName.Replace("_", " ")
        End If

        c  = 1
    Next

    ws.Range(ws.Cells(2, 1), ws.Cells(dt.Rows.Count, dt.Columns.Count))(1).Value = arr
End Sub

UPDATE:

I've managed to stop the error from occuring by changing SaveAs() to SaveCopyAs().

The file now exports but when I open it, all there is are column names and only the first value of the first column/row.

CodePudding user response：

Launching Excel just to export data to an Excel file is overkill. For web sites it's completely impractical for several reasons:

You need a license for every user of the site. That's a lot of money.
It's way too easy to leave Excel open, slowly eating up the server's RAM and CPU.
It's just too slow

xlsx is a ZIP package containing well-defined XML files, so one can create them directly, use the Open XML SDK or one of the many open source libraries that make this a lot easier, like Epplus, NPOI or ClosedXML.

For example, Epplus allows filling an Excel sheet from a DataTable, IEnumerable or IDbDataReader with a single call:

Dim dt As DataTable = ...

Dim fi New FileInfo(SomePath)
Using p As New ExcelPackage(fi)
    Dim ws = p.Workbook.Worksheets.Add(sheetName)
    ws.Cells("A1").LoadFromDataTable(dt, PrintHeaders:=True)
    p.Save()
End Using

You can also use LoadFromDataReader and avoid loading all the data into memory:

Using cmd As New SqlCommand(sql,connection)
    connection.Open()
    Using reader As SqlDataReader = cmd.ExecuteReader()
        Dim fi New FileInfo(SomePath)
        Using p As New ExcelPackage(fi)
            Dim ws = p.Workbook.Worksheets.Add(sheetName)
            ws.Cells("A1").LoadFromDataReader(reader, PrintHeaders:=True)
            p.Save()
        End Using
    End Using
End Using