Datagridview / Datatable to Excel throws exception

See more:

VB

Hi
First if anyone reads this thread - Using an Array to copy data to an Excel sheet is very, very, very much faster than copying data cell by cell..


Now the problem:

I have two methods that are virtually the same except that one copies data from a Datagridview to an Excel sheet, while the other copies data from a Datatable.

The Datatable works brilliantly, but the one for the datagridview throws an exception at the following line:

xlSheet.Range("A2").Resize(dt.Rows.Count, dt.Columns.Count).Value = DataArray

The exception is:
COMException was unhandled
Exception from HRESULT: 0x800A03EC

I just cant seem to find why the one works but the other doesn't.

The one that works is:

Private Sub cmdstkHistExport_Click(sender As System.Object, e As System.EventArgs) Handles cmdstkHistExport.Click
        Dim ExcelColFormats() As String = {"@", "@", "@", "@", "# ##0", "yyyy/MM/dd", "@", "@"}
        frmMain.ExportExcel(dtStkHist, ExcelColFormats)
    End Sub

Friend Sub ExportExcel(dt As DataTable, formats() As String)

        'Create a book to save to
        Dim xlApp As New Excel.Application
        Dim xlBook As Excel.Workbook
        Dim xlSheet As Excel.Worksheet

        xlBook = xlApp.Workbooks.Add
        xlSheet = xlBook.Sheets("sheet1")
        xlApp.Visible = False
        xlApp.Calculation = Excel.XlCalculation.xlCalculationManual

        'Headings
        For c As Integer = 0 To dt.Columns.Count - 1
            xlSheet.Cells(1, c + 1).Value = dt.Columns(c).ColumnName
            xlSheet.Cells(1, c + 1).Font.Bold = True
        Next

        'Formats
        For Col = 0 To formats.GetUpperBound(0) - 1
            xlSheet.Columns(Col + 1).numberformat = formats(Col)
        Next

        'Load Datatable into Array
        Dim DataArray(dt.Rows.Count, dt.Columns.Count) As Object
        For i As Integer = 0 To dt.Rows.Count - 1
            For j As Integer = 0 To dt.Columns.Count - 1
                DataArray(i, j) = dt.Rows(i).Item(j)
            Next
        Next

        'Dump the Array into the sheet
        xlSheet.Range("A2").Resize(dt.Rows.Count, dt.Columns.Count).Value = DataArray

        'Resize Columns
        xlSheet.Cells.Select()
        xlSheet.Cells.EntireColumn.AutoFit()

        xlApp.Visible = True
        xlApp.Calculation = Excel.XlCalculation.xlCalculationAutomatic


    End Sub

The one that does not work is:

Private Sub cmdBatchExport_Click(sender As System.Object, e As System.EventArgs) Handles cmdBatchExport.Click
        Dim ExcelColFormats() As String = {"@", "@", "@", "@", "@", "yyyy/MM/dd", "# ##0", "@", "@"}
        frmMain.ExportExcel(Me.dgvBatches, ExcelColFormats)
    End Sub

Friend Sub ExportExcel(dgv As DataGridView, ByVal Formats() As String)
        Cursor = Cursors.WaitCursor

        Dim xlApp As New Excel.Application
        Dim xlBook As Excel.Workbook
        Dim xlSheet As Excel.Worksheet

        xlBook = xlApp.Workbooks.Add
        xlSheet = xlBook.Sheets("sheet1")
        xlApp.Visible = False
        xlApp.Calculation = Excel.XlCalculation.xlCalculationManual

        'Headings
        For Each Col As DataGridViewColumn In dgv.Columns
            xlSheet.Cells(1, Col.Index + 1).Value = dgv.Columns(Col.Index).Name
            xlSheet.Cells(1, Col.Index + 1).Font.Bold = True
        Next

        'Formats
        For Col = 0 To Formats.GetUpperBound(0) - 1
            xlSheet.Columns(Col + 1).numberformat = Formats(Col)
        Next

        'Load Datatable into Array
        Dim DataArray(dgv.Rows.Count, dgv.Columns.Count) As Object
        For i As Integer = 0 To dgv.Rows.Count - 1
            For j As Integer = 0 To dgv.Columns.Count - 1
                DataArray(i, j) = dgv.Rows(i).Cells(j)
            Next
        Next

        'Dump the Array into the sheet
        xlSheet.Range("A2").Resize(dgv.Rows.Count, dgv.Columns.Count).Value = DataArray

        'Resize columns
        xlSheet.Cells.Select()
        xlSheet.Cells.EntireColumn.AutoFit()

        Cursor = Cursors.Default
        xlApp.Calculation = Excel.XlCalculation.xlCalculationAutomatic
        xlApp.Visible = True
    End Sub