使用 VB.Net 应用程序将 Excel 数据导入 SQL Server 表

我找到了几篇关于这个主题的文章，但问题是不同的。我正在尝试使用 VB.net 应用程序将数据从 Excel 2016 文件导入 SQL Server 2017，以便最终用户不需要在他们的机器上安装 SQL Server。我在调试模式下运行应用程序以识别问题。

我再次阅读了有关与以下错误相关的各种实例的文章：

“Microsoft.ACE.OLEDB.15.0”提供程序未在本地计算机上注册

我的代码：

Dim TheData As DataTable = GetExcelData(OpenFileDialog1)

Private Function GetExcelData(File As OpenFileDialog) As DataTable
    Dim oleDbConnection As New OleDbConnection

    Try
        OpenOLEDBConnections(oleDbConnection, "Provider=Microsoft.ACE.OLEDB.15.0;Data Source=" & File.InitialDirectory & _
        File.FileName & "; ;Extended Properties=""Excel 12.0 XML;HDR=YES;""")

        Dim oleDbDataAdapter As OleDbDataAdapter = New OleDbDataAdapter("Select '' as ID, 'AL' as Supplier, LTRIM(RTRIM(GRADE)) AS GRADE, [ship date] as shipdate, LTRIM(RTRIM(coil)) AS coil, L15*1000 AS L15, H15*1000 AS H15 FROM [sheet1$] where COIL is not null group by GRADE, [SHIP DATE], COIL, L15, H15", oleDbConnection)
        Dim dataTable As DataTable = New DataTable()
        oleDbDataAdapter.Fill(dataTable)
        oleDbConnection.Close()

        Return dataTable
    Catch ex As Exception
        oleDbConnection.Close()

        Return Nothing
    End Try

End Function

Private Sub OpenOLEDBConnections(ByRef cnData As OleDbConnection, cnDataConstr As String)

    If Not cnData.State = ConnectionState.Closed Then cnData.Close()
    If cnData.State = ConnectionState.Closed Then
        cnData.ConnectionString = cnDataConstr
        cnData.Open()
    End If

End Sub