读一个Excel工作表到一个DataTable最佳/最快的方法？ [英] Best /Fastest way to read an Excel Sheet into a DataTable?

问题描述

我希望这里有人能指出我朝着正确的方向 - 我试图创建一个相当强大的实用程序来读取一个Excel工作表中的数据（可能是.xls或的.xlsx）成一个DataTable迅速和leanly尽可能

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.

我想出了这个程序在VB（但我会很高兴地具有良好的C＃的答案）：

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

我不知道这是否是做的最好的办法，或者有更好的/比较有效的方式（或者只是更智能的方式 - 也许LINQ的/本地.NET提供者）？使用，而不是

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?

另外，只是一个快速的和愚蠢的另一个问题 - 我需要包括code，如 y.Dispose（）和ÿ =没有或会，采取自变量应该死在程序结束时，右??

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??

谢谢！

推荐答案

我一直使用 OLEDB 此，类似...

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()

在 ACE.OLEDB 供应商将同时读取的.xls 和的.xlsx 文件和我一直觉得速度相当不错。

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

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