为什么 Visual Basic 不能将我的数据表转换为 Excel 数据表？

Question

我正在尝试使用本地数据库制作一个程序，在那里我可以概述我们公司的节省情况。没什么特别的。无论如何，我想在单击导出按钮时将数据集导出到 excel 文件。这是我的代码

Dim dt As DataTable
    Dim xl As New Microsoft.Office.Interop.Excel.Application
    Dim ds As New DataSet
    ds = GeldopslagDataSet
    xl.UserControl = True
    Dim oldCI As System.Globalization.CultureInfo = _
        System.Threading.Thread.CurrentThread.CurrentCulture
    System.Threading.Thread.CurrentThread.CurrentCulture = _
        New System.Globalization.CultureInfo("en-US")
    xl.Workbooks.Add()
    System.Threading.Thread.CurrentThread.CurrentCulture = oldCI
    xl.ActiveSheet.Name = "Spaardoel"
    xl.Visible = True
    xl.Range("A1").Value = "Loading the DataSet...."
    Try
        xl.ScreenUpdating = False
        dt = ds.Tables("Geldopslag")
        'Add the column headings for Geldopslag
        Dim dc As DataColumn
        Dim iCols As Int32 = 0
        For Each dc In dt.Columns
            xl.Range("A1").Offset(0, iCols).Value = dc.ColumnName
            iCols += 1
        Next
        'Add the data
        Dim iRows As Int32
        For iRows = 0 To dt.Rows.Count - 1
            xl.Range("A2").Offset(iRows).Resize(1, iCols).Value = _
              dt.Rows(iRows).ItemArray()
        Next
    Catch ex As Exception
    Finally
        xl.ScreenUpdating = True
    End Try
    'Make the sheet pretty
    With xl.ActiveSheet.Range("A1")
        .AutoFilter()
        .AutoFormat(Microsoft.Office.Interop.Excel.XlRangeAutoFormat.xlRangeAutoFormatSimple)
    End With
    xl = Nothing

呜呜，我开始去窃听，我发现以下错误：

COMException未处理旧格式或无效的类型库。（HRESULT的例外：0x80028018（TYPE_E_INVDATAREAD））

此外，如您所见，我将xl设置为Microsoft.Office.Interop.Excel.Application，而不是在顶部使用Imports Microsoft.interop.excel。当我这样做时，我收到错误，我的数据表无法转换为 excel 数据表。

Answer 1

我使用这个函数，它工作得很好：

Imports Microsoft.Office.Interop
Public Class ExcelTools
' Export a DataTable into an Excel Datasheet
Public Shared Function DatatableToExcel(ByVal aDataTable As DataTable) As Boolean
    Dim myExcel As New Microsoft.Office.Interop.Excel.Application
    Try
        ' Excel.Application
        myExcel.Application.Workbooks.Add()
        myExcel.Visible = True
        Dim myColumn As DataColumn
        Dim colIndex As Integer
        Dim rowIndex As Integer
        For Each myColumn In aDataTable.Columns
            colIndex += 1
            myExcel.Cells(1, colIndex) = myColumn.ColumnName
        Next
        Dim myRow As DataRow
        rowIndex = 1
        For Each myRow In aDataTable.Rows
            rowIndex += 1
            colIndex = 0
            Dim myColumn2 As DataColumn
            For Each myColumn2 In aDataTable.Columns
                colIndex += 1
                myExcel.Cells(rowIndex, colIndex) = myRow( _
                  myColumn2.ColumnName)
            Next myColumn2
        Next myRow
    Catch ex As Exception
        MsgBox(ex.Message)
    End Try
    myExcel.Quit()
End Function
end class