菩薩慈悲:菩薩您的表格是Excel還是MS Word?還是其他呢?
用VBA的話,就用 Scripting.Dictionary,在VB.NET的話,則應有類似且效能更好的Dictionary來實作,很容易的。若需詳情,俟覆再議。感恩感恩 南無阿彌陀佛
以OLEDB、ADODB 實作
昨天才在iT邦幫忙收到 IT 邦邦友大師菩薩的指點,才知道用Dictionary的效能較 ADO、SQL 差太多,
我用 3 萬筆資料測試結果如下:
1.Access : 不超過 1 秒
2.Excel +VBA + SQL (未上色) : 12 秒
3.Excel +VBA + SQL (上色) : 17 秒
4.Excel +VBA (你的) : 2 分 35 秒
因此先改用ADODB 與 OLEDB 實作看看。
ps.以下程式碼在測試對象檔案開啟時是可以測試成功的。
Microsoft.Jet.OLEDB.4.0 要用 .xls 而 Microsoft.ACE.OLEDB.12.0 才能用 .xlsm .xlsx .xlsb …… 在不開啟原檔時,才不會出錯。
Sub sumbyNameGroup()
' 設置讀取檔案路徑及連線字串
Dim filePath As String = "C:\Users\ssz3\Documents\t.xlsb" '"your_excel_file_path"
Dim sheetName = "工作表1"
'Dim connString As String = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + filePath + ";Extended Properties=Excel 12.0;"
Dim connString As String = "Provider=Microsoft.Jet.OLEDB.4.0;Excel 8.0;Database=" + filePath
Dim conn As New ADODB.Connection
conn.Open(connString)
' 構建 SQL 語句,選擇要操作的工作表及數據欄位
Dim sqlStr As String = "SELECT 姓名, SUM(成績1) AS 成績1總和, SUM(成績2) AS 成績2總和 FROM [" + sheetName + "$] GROUP BY 姓名"
Dim rst As New ADODB.Recordset
rst.Open(sqlStr, conn)
' 輸出結果
Do Until rst.EOF
Console.WriteLine(rst.Fields("姓名").Value + " " +
rst.Fields("成績1總和").Value.ToString() + " " +
rst.Fields("成績2總和").Value.ToString())
'Console.WriteLine(rst.Fields(0).Value + " " +
' rst.Fields(1).Value.ToString() + " " +
' rst.Fields(2).Value().ToString())
rst.MoveNext()
Loop
rst.Close() : conn.Close()
End Sub
或
Sub sumbyNameGroup()
' 設置讀取檔案路徑及連線字串
Dim filePath As String = "C:\Users\ssz3\Documents\t.xlsb" '"your_excel_file_path"
Dim sheetName = "工作表1"
'Dim connString As String = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + filePath + ";Extended Properties=Excel 12.0;"
Dim connString As String = "Provider=Microsoft.Jet.OLEDB.4.0;Excel 8.0;Database=" + filePath
' 創建 OleDbConnection
Dim conn As System.Data.OleDb.OleDbConnection = New System.Data.OleDb.OleDbConnection(connString)
' 構建 SQL 語句,選擇要操作的工作表及數據欄位
Dim sqlStr As String = "SELECT 姓名, SUM(成績1) AS 成績1總和, SUM(成績2) AS 成績2總和 FROM [" + sheetName + "$] GROUP BY 姓名"
' 創建 OleDbDataAdapter
Dim da As System.Data.OleDb.OleDbDataAdapter = New System.Data.OleDb.OleDbDataAdapter(sqlStr, conn)
' 創建 DataTable 物件
Dim dt As DataTable = New DataTable()
' 執行 SQL 查詢並將查詢結果填充到 DataTable
da.Fill(dt)
' 關閉資源
da.Dispose()
conn.Dispose()
' 輸出結果
For Each row As DataRow In dt.Rows
Console.WriteLine(row("姓名").ToString() + " " + row("成績1總和").ToString() + " " + row("成績2總和").ToString())
Next
End Sub
而若要參考ADODB組建,則須將其「內嵌Interop類型」屬性設成 False,如圖:
產出結果: