excel - HRESULT 异常：0x800A03EC excel.range

Question

我Exception from HRESULT: 0x800A03EC在 excel.range 中收到此错误。
这是我的以下代码

    Private Sub excelInsertData(rowIndex As Integer, ColIndex As Integer, EDT As DataTable)
    Try

        Dim arr As Object(,) = New Object(EDT.Rows.Count - 1, EDT.Columns.Count - 1) {}

        For r As Integer = 0 To EDT.Rows.Count - 1
            Dim dr As DataRow = EDT.Rows(r)

            For c As Integer = 0 To EDT.Columns.Count - 1
                arr(r, c) = dr(c)
            Next
        Next

        Dim c1 As Excel.Range = CType(xlWorkSheet.Cells(rowIndex, ColIndex), Excel.Range)
        Dim c2 As Excel.Range = CType(xlWorkSheet.Cells(rowIndex + EDT.Rows.Count - 1, EDT.Columns.Count + ColIndex - 1), Excel.Range)
        Dim range As Excel.Range = xlWorkSheet.Range(c1, c2)
        range.Value = arr '---------ERROR THROWN HERE

    Catch ex As Exception
        MsgBox(ex.toString)
    Finally
        EDT.Rows.Clear() : EDT.Columns.Clear()
    End Try
End Sub

我正在尝试将数据从数据表（在本例中为 EDT）传输到 Excel 模板。

以下是值范围、arr 和 EDT。

range.Value - 长度为 5

长度与范围相同

我的数据表中的值（EDT）

此外，如果需要，我可以附上我的 3b.xlsm 文件的模板。

请询问我是否遗漏了任何需要提供的信息。

标签： excelvb.netexcel-interop

Answer 1

第一行只是从函数中获取数据表。您也可以只向 Sub 添加一个参数并传入一个参数。此代码创建一个新的 Excel 文件。第一个循环添加列名，第二个循环添加数据。

Private Sub FillExcelFromDataTable()
    Dim dt = GetWorksheetData()
    Dim oExcel As New Excel.Application
    Dim oBook = oExcel.Workbooks.Add
    Dim oSheet = DirectCast(oBook.Worksheets.Add, Excel.Worksheet)
    Dim ColumnIndex = 1 'in Excel worksheet
    For Each col As DataColumn In dt.Columns 'This loop adds the header row
        oSheet.Cells(1, ColumnIndex) = col.ColumnName
        ColumnIndex += 1
    Next
    ColumnIndex = 1 'The columns and rows in the spreadsheet
    Dim RowIndex = 2 'The columns and rows in the spreadsheet
    For rowI = 0 To dt.Rows.Count - 1
        For Each col As DataColumn In dt.Columns
            oSheet.Cells(RowIndex, ColumnIndex) = dt(rowI)(col)
            ColumnIndex += 1
        Next
        ColumnIndex = 1 'Reset back to the first column
        RowIndex += 1
    Next
    oBook.Save()
    oBook.SaveAs(Filename:="ExcelDat.xlsx") 'Saved to "C:\Users\xxx\Documents\ExcelDat.xlsx"
    oBook.Close()
    oExcel.Quit()
End Sub