将 Excel 工作表读入数据表的最佳/最快方法?

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 RetValEnd FunctionI'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 工作表读入数据表的最佳/最快方法?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！ 上岸，阿里云！