VB.NET 读取 xlsx 文件时，不会给出有效的路径

Question

我似乎无法使用以下连接字符串读取.xlsx文件：

网络配置

<add name="Excel07ConString" connectionString="Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties='Excel 8.0;HDR={1}'"/>

代码文件

conStr = ConfigurationManager.ConnectionStrings("Excel07ConString").ConnectionString
Dim connExcel As New OleDbConnection(conStr)
connExcel.Open()

我一直收到此错误：

无法初始化链接服务器"（null）"的 OLE DB 提供程序"Microsoft.ACE.OLEDB.12.0"的数据源对象。链接服务器"（null）"的 OLE DB 提供程序"Microsoft.ACE.OLEDB.12.0"返回消息"'F：\Ishan\Projects\ImportExcel2DB\ImportExcel2DB\Files\Whole Extract.xlsx"不是有效路径。确保路径名拼写正确，并且您已连接到文件所在的服务器。

是的，此特定位置有文件。任何帮助将不胜感激！

Answer 1

HDR 需要为"是"或"否"。 在下面查看我的项目

Imports System.IO
Imports System.Data
Imports System.Data.OleDb
Module Module1
    Sub Main()
        Dim reader As New CSVReader()
        Dim ds As DataSet = reader.ReadCSVFile("filename", True)
    End Sub
End Module
Public Class CSVReader
    Public Function ReadCSVFile(ByVal fullPath As String, ByVal headerRow As Boolean) As DataSet
        Dim path As String = fullPath.Substring(0, fullPath.LastIndexOf("") + 1)
        Dim filename As String = fullPath.Substring(fullPath.LastIndexOf("") + 1)
        Dim ds As DataSet = New DataSet()
        Dim header As String
        If headerRow Then
            header = "Yes"
        Else
            header = "No"
        End If
        Try
            If File.Exists(fullPath) Then
                Dim ConStr As String = String.Format("Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0}" + ";Extended Properties=""Text;HDR={1};FMT=Delimited""", path, header)
                Dim SQL As String = String.Format("SELECT * FROM {0}", filename)
                Dim adapter As OleDbDataAdapter = New OleDbDataAdapter(SQL, ConStr)
                adapter.Fill(ds, "TextFile")
                ds.Tables(0).TableName = "Table1"
            End If
            For Each col As DataColumn In ds.Tables("Table1").Columns
                col.ColumnName = col.ColumnName.Replace(" ", "_")
            Next
        Catch ex As Exception
            Console.WriteLine(ex.Message)
        End Try
        Return ds
    End Function
End Class