我希望这里的人可以为我指明正确的方向- 我试图创建一个相当健壮的实用程序,以将Excel工作表(可能是.xls或.xlsx)中的数据读取到DataTable中,就像可能。
我在VB中提出了这个例程(尽管我对一个好的C#回答同样满意):
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提供程序)代替?
另外,这只是一个快速而愚蠢的问题-我是否需要包含诸如y.Dispose()and y = Nothing或will这样的代码,因为变量应该在例程结束时死掉,对吗?
y.Dispose()
y = Nothing
谢谢!!
我一直OLEDB为此使用,就像…
OLEDB
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文件,我一直觉得速度相当不错。
ACE.OLEDB
.xls
.xlsx