Datagridview/Datatable到Excel引发异常 [英] Datagridview / Datatable to Excel throws exception

问题描述

嗨
首先，如果有人读过该线程-使用数组将数据复制到Excel工作表的速度比逐个单元复制数据的速度非常非常非常快.


现在的问题:

我有两种方法几乎相同，除了一种方法是将数据从Datagridview复制到Excel工作表，而另一种方法是将数据从Datatable复制到数据表.

Datatable的工作非常出色，但是datagridview的数据表在以下行引发了异常:

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

例外是:
未处理COMException
HRESULT的异常:0x800A03EC

我只是似乎找不到为什么一个有效但另一个却无效的原因.
起作用的是:

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

推荐答案

我发现了问题...太简单了:)

这就是我填充数组的方式.

对于数据表:

I found the problem... Soooo simple :)

It was the way I was populating the array.

For the datatable:

DataArray(i, j) = dt.Rows(i).Item(j)

但是对于Datagridview:

But for the Datagridview:

DataArray(i, j) = dgv.Rows(i).Cells(j)

在这里，我使用Datagridview Cells 而不是单元格的 value
填充数组
更改为:

Here I was populating the array with Datagridview Cells, and not the value of the cells

Changed it to:

DataArray(i, j) = dgv.Rows(i).Cells(j).Value

一切都很好

And all is fine

这篇关于Datagridview/Datatable到Excel引发异常的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！