允许VB.NET应用将Excel文件转换为数据表 [英] Allowing VB.NET app to convert Excel Files to Datatable

问题描述

由于 David 在我发布的这个问题:上一个问题

My VB.NET app currently allows me to convert CSV files to a datatable thanks to the code provided by David in this question I posted: Previous Question

现在，我试图将.XLSX文件也导入到数据表中.当前代码如下:

Now I am trying to allow .XLSX files to be imported to a datatable as well. Currently the code looks like this:

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文件运行代码时，出现以下错误:

However, when I run the code using the .XLSX file, I get the following error:

{"Microsoft Office Access数据库引擎无法打开或写入 文件"C:\ Users \ XSLXFilePath".它已经由 其他用户，或者您需要查看和写入其数据的权限.}

{"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文件格式?

The file is not open anywhere else to my knowledge. And the app also runs fine when .CSV file is put through it instead. How do I get the app to properly work for .XLSX, or any Excel file format?

推荐答案

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

I think that the error is that from the connection string and the OLEDB Command:

ConnectionString

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

You don't have to use IO.Path.GetDirectoryName(path) it returns the directory name, you have to provide the file full 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连接字符串生成功能:导入数据从excel 2003到dataTable

Refer to this link for excel connectionstring generation function: 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)

参考

• 使用oledbcommand从excel阅读
• 使用OLEDB读写Excel文档

• Reading from excel using oledbcommand
• Read and Write Excel Documents Using OLEDB

这篇关于允许VB.NET应用将Excel文件转换为数据表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！