使用 ADO.NET 實現高效批量匯出資料至 Excel

前言

在企業級應用開發中，將資料庫查詢結果大量匯出至 Excel 是常見需求。雖然 OleDB 也是可行方案，但利用 ADO 的 Recordset.CopyFromRecordset 方法可直接將資料集批量寫入工作表，效能極佳。本文將詳細介紹此技術的實作細節與完整程式碼範例。

核心原理

CopyFromRecordset 是 ADO Recordset 物件提供的原生方法，可一次性將資料列複製到 Excel Range 區域。需注意：此方法不會自動寫入欄位名稱，標題列需另行處理。開發時必須引用兩個 COM 組件：

• Microsoft ActiveX Data Objects (ADO) Library
• Microsoft Excel Object Library

關鍵實作步驟

1. 建立 Excel Application、Workbook、Worksheet 物件
2. 開啟 ADO Connection 連接資料庫
3. 執行 SQL 查詢獲取靜態唯讀 Recordset
4. 定位目標寫入範圍（如 A2 留出標題列）
5. 呼叫 range.CopyFromRecordset(rs, 65535, 65535) 批量寫入
6. 妥善處理例外並釋放 COM 物件

完整程式碼範例

using System;
using System.Collections.Generic;
using System.Text;
using System.Reflection;
using Excel = Microsoft.Office.Interop.Excel;
using ADODB;

namespace ConsoleApplication18
{
    class Program
    {
        static void Main(string[] args)
        {
            ExportDataToExcel(
                "server=(local);uid=sa;pwd=sqlgis;database=master",
                "select * from sysobjects",
                @"c:/testADO.xls",
                "sysobjects"
            );
        }

        static void ExportDataToExcel(string connectionString, string sql, string fileName, string sheetName)
        {
            Excel.Application app = new Excel.ApplicationClass();
            Excel.Workbook wb = (Excel.WorkbookClass)app.Workbooks.Add(Missing.Value);
            Excel.Worksheet ws = wb.Worksheets.Add(Missing.Value, Missing.Value, Missing.Value, Missing.Value) as Excel.Worksheet;
            ws.Name = sheetName;

            try
            {
                ADODB.Connection conn = new ADODB.ConnectionClass();
                conn.Open("driver={SQL Server};" + connectionString, "", "", 0);
                ADODB.Recordset rs = new ADODB.RecordsetClass();
                rs.Open(sql, conn, ADODB.CursorTypeEnum.adOpenStatic, ADODB.LockTypeEnum.adLockReadOnly, 0);

                Excel.Range range = ws.get_Range("A2", Missing.Value);
                range.CopyFromRecordset(rs, 65535, 65535); // 最大行列限制
            }
            catch (Exception ex)
            {
                string str = ex.Message;
                // 建議記錄日誌或拋出
            }
            finally
            {
                wb.Saved = true;
                wb.SaveCopyAs(fileName);
                app.Quit();
                // 釋放 COM 物件最佳實踐
                System.Runtime.InteropServices.Marshal.ReleaseComObject(ws);
                System.Runtime.InteropServices.Marshal.ReleaseComObject(wb);
                System.Runtime.InteropServices.Marshal.ReleaseComObject(app);
            }
        }
    }
}

效能優化建議

• 大量資料時，設定 app.ScreenUpdating = false 關閉畫面重繪
• 使用 Missing.Value 而非 Type.Missing 相容舊版
• 釋放 COM 物件防止 Excel 殭屍進程
• 考慮使用 OpenXML SDK 實現無 Office 依賴的伺服器端匯出

常見問題排查

• 欄位名稱缺失：需手動寫入標題列或使用 rs.Fields 迴圈
• 資料截斷：Excel 2003 (.xls) 單工作表限 65,536 列，大數據請分批或改用 .xlsx
• 型別不符：確保資料庫欄位型別與 Excel 儲存格格式相容