我希望这里有人能指出我朝着正确的方向 - 我试图创建一个相当强大的实用程序来读取一个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.