将 Excel 工作表读入数据表的最佳/最快方法? [英] Best /Fastest way to read an Excel Sheet into a DataTable?

问题描述

我希望这里有人能指出我正确的方向 - 我正在尝试创建一个相当强大的实用程序，以尽快将 Excel 工作表(可能是 .xls 或 .xlsx)中的数据读取到 DataTable 中并且尽可能瘦.

我在 VB 中想出了这个例程(虽然我会很高兴得到一个好的 C# 答案):

公共共享函数 ReadExcelIntoDataTable(ByVal FileName As String, ByVal SheetName As String) As DataTableDim RetVal 作为新数据表Dim strConnString As StringstrConnString = "Driver={Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb)};DBQ=" &文件名 &";"Dim strSQL As StringstrSQL = "SELECT * FROM [" &SheetName &$]"Dim y As New Odbc.OdbcDataAdapter(strSQL, strConnString)y.Fill(RetVal)返回 RetVal结束函数

我想知道这是否是最好的方法，或者是否有更好/更有效的方法(或者只是更智能的方法 - 也许是 Linq/原生 .Net 提供者)来代替?

另外，只是一个快速而愚蠢的附加问题 - 我是否需要包含诸如 y.Dispose() 和 y = Nothing 之类的代码，或者会被注意因为变量应该在例程结束时死亡，对吗??

谢谢！！

解决方案

我一直使用 OLEDB 来解决这个问题，比如……

 Dim sSheetName As StringDim sConnection 作为字符串Dim dtTablesList As DataTableDim oleExcelCommand 作为 OleDbCommandDim oleExcelReader 作为 OleDbDataReaderDim oleExcelConnection 作为 OleDbConnectionsConnection = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:Test.xls;Extended Properties=""Excel 12.0;HDR=No;IMEX=1"""oleExcelConnection = 新的 OleDbConnection(sConnection)oleExcelConnection.Open()dtTablesList = oleExcelConnection.GetSchema("表格")如果 dtTablesList.Rows.Count >0 那么sSheetName = dtTablesList.Rows(0)("TABLE_NAME").ToString万一dtTablesList.Clear()dtTablesList.Dispose()如果 sSheetName <>"然后oleExcelCommand = oleExcelConnection.CreateCommand()oleExcelCommand.CommandText = "选择 * 从 [" &表格名称 &]"oleExcelCommand.CommandType = CommandType.TextoleExcelReader = oleExcelCommand.ExecuteReadernOutputRow = 0而 oleExcelReader.Read结束时间oleExcelReader.Close()万一oleExcelConnection.Close()

ACE.OLEDB 提供程序将同时读取 .xls 和 .xlsx 文件，我一直发现速度非常好.

I'm hoping someone here can point me in the right direction - I'm trying to create a fairly robust utility program to read the data from an Excel sheet (may be .xls OR .xlsx) into a DataTable as quickly and leanly as possible.

I came up with this routine in VB (although I'd be just as happy with a good C# answer):

Public Shared Function ReadExcelIntoDataTable(ByVal FileName As String, ByVal SheetName As String) As DataTable
    Dim RetVal As New DataTable

    Dim strConnString As String
    strConnString = "Driver={Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb)};DBQ=" & FileName & ";"

    Dim strSQL As String 
    strSQL = "SELECT * FROM [" & SheetName & "$]"

    Dim y As New Odbc.OdbcDataAdapter(strSQL, strConnString)

    y.Fill(RetVal)

    Return RetVal

End Function

I'm wondering if this is the best way to do it or if there are better / more efficent ways (or just more intelligent ways - Maybe Linq / native .Net providers) to use instead?

ALSO, just a quick and silly additional question - Do I need to include code such as y.Dispose() and y = Nothing or will that be taken care of since the variable should die at the end of the routine, right??

Thanks!!

解决方案

I have always used OLEDB for this, something like...

    Dim sSheetName As String
    Dim sConnection As String
    Dim dtTablesList As DataTable
    Dim oleExcelCommand As OleDbCommand
    Dim oleExcelReader As OleDbDataReader
    Dim oleExcelConnection As OleDbConnection

    sConnection = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:Test.xls;Extended Properties=""Excel 12.0;HDR=No;IMEX=1"""

    oleExcelConnection = New OleDbConnection(sConnection)
    oleExcelConnection.Open()

    dtTablesList = oleExcelConnection.GetSchema("Tables")

    If dtTablesList.Rows.Count > 0 Then
        sSheetName = dtTablesList.Rows(0)("TABLE_NAME").ToString
    End If

    dtTablesList.Clear()
    dtTablesList.Dispose()

    If sSheetName <> "" Then

        oleExcelCommand = oleExcelConnection.CreateCommand()
        oleExcelCommand.CommandText = "Select * From [" & sSheetName & "]"
        oleExcelCommand.CommandType = CommandType.Text

        oleExcelReader = oleExcelCommand.ExecuteReader

        nOutputRow = 0

        While oleExcelReader.Read

        End While

        oleExcelReader.Close()

    End If

    oleExcelConnection.Close()

The ACE.OLEDB provider will read both .xls and .xlsx files and I have always found the speed quite good.

这篇关于将 Excel 工作表读入数据表的最佳/最快方法?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！