excel - 允许 VB.NET 应用程序将 Excel 文件转换为数据表

Question

由于 David 提供的代码，我的 VB.NET 应用程序目前允许我将 CSV 文件转换为数据表。在我发布的这个问题中:Previous Question

现在我试图允许将 .XLSX 文件也导入到数据表中。目前代码如下所示:

Private Function ConvertCSVToDataTable(ByVal path As String) As DataTable
    Dim dt As DataTable = New DataTable()
    Using con As OleDb.OleDbConnection = New OleDb.OleDbConnection()
        Try
            If System.IO.Path.GetExtension(path) = ".csv" Then
                con.ConnectionString = String.Format("Provider={0};Data Source={1};Extended Properties=""Text;HDR=YES;FMT=Delimited""", "Microsoft.Jet.OLEDB.4.0", IO.Path.GetDirectoryName(path))
            ElseIf System.IO.Path.GetExtension(path) = ".xlsx" Then
                con.ConnectionString = String.Format("Provider={0};Data Source={1};Extended Properties=""Excel 12.0 XML;HDR=Yes;""", "Microsoft.ACE.OLEDB.12.0", IO.Path.GetDirectoryName(path))
            End If
            Using cmd As OleDb.OleDbCommand = New OleDb.OleDbCommand("SELECT * FROM " & IO.Path.GetFileName(path), con)
                Using da As OleDb.OleDbDataAdapter = New OleDb.OleDbDataAdapter(cmd)
                    con.Open()
                    da.Fill(dt)
                    con.Close()
                End Using
            End Using
        Catch ex As Exception
            Console.WriteLine(ex.ToString())
        Finally
            If con IsNot Nothing AndAlso con.State = ConnectionState.Open Then
                con.Close()
            End If
        End Try
    End Using
    Return dt
End Function

但是，当我使用 .XLSX 文件运行代码时，出现以下错误:

{"The Microsoft Office Access database engine cannot open or write to the file 'C:\Users\XSLXFilePath'. It is already opened exclusively by another user, or you need permission to view and write its data."}

据我所知，该文件未在其他任何地方打开。当 .CSV 文件通过它时，该应用程序也可以正常运行。如何让应用程序正常工作于 .XLSX 或任何 Excel 文件格式？

Answer 1

我认为错误来自连接字符串和 OLEDB 命令:

连接字符串

您不必使用 IO.Path.GetDirectoryName(path)它返回目录名称，您必须提供文件完整路径:

con.ConnectionString = String.Format("Provider={0};Data Source={1};Extended Properties=""Excel 12.0 XML;HDR=Yes;""", "Microsoft.ACE.OLEDB.12.0", path)

excel连接字符串生成功能引用此链接: import data from excel 2003 to dataTable

OLEDB 命令

您必须在命令中提供工作表名称而不是文件名:

 Using cmd As OleDb.OleDbCommand = New OleDb.OleDbCommand("SELECT * FROM [Sheet1$]" , con)

如果工作表名称是动态的，并且您必须获取 excel 文件中的第一个工作表:

Dim dbSchema as DataTable = con.GetOleDbSchemaTable (OleDbSchemaGuid.Tables, null)

Dim firstSheetname as String = dbSchema.Rows(0)("TABLE_NAME").ToString
Using cmd As OleDb.OleDbCommand = New OleDb.OleDbCommand("SELECT * FROM [" & firstSheetname & "]" , con)

引用文献

Reading from excel using oledbcommand

Read and Write Excel Documents Using OLEDB