C# 匯出 Excel 大量資料高效方案：QueryTable + 分頁工作表實戰

匯出 Excel 主流方案比較

QueryTable 核心原理

Excel 內建 QueryTable 可透過 OLEDB 直接連接資料庫，執行 SQL 查詢並將結果直接填入工作表，完全在 Excel 進程內完成，避免 COM 介面逐筆寫入的效能瓶頸。

關鍵程式碼架構 (Office 2003+ / Microsoft.Office.Interop.Excel)

using Excel = Microsoft.Office.Interop.Excel;
using System.Data.SqlClient;

public class ExcelExporter
{
    private Excel.Application _app;
    private Excel.Workbook _book;
    private Excel.Sheets _sheets;
    private readonly string _connStr = "Server=192.168.1.100;Database=ERP;User=sa;Password=...;";
    private const int SheetRowLimit = 65535; // .xls 上限；.xlsx 為 1048576

    public void Export(string tableName, string sqlTemplate, string outputPath)
    {
        InitializeExcel();
        try
        {
            int totalRows = GetRowCount(sqlTemplate);
            int sheetCount = (int)Math.Ceiling(totalRows / (double)SheetRowLimit);

            for (int i = 1; i <= sheetCount; i++)
            {
                Excel.Worksheet ws = (i == 1) ? (Excel.Worksheet)_sheets[1] : AddSheet();
                ExportPage(ws, i, tableName, sqlTemplate);
            }

            _book.SaveAs(outputPath, Excel.XlFileFormat.xlWorkbookNormal);
        }
        finally
        {
            Cleanup();
        }
    }

    private void InitializeExcel()
    {
        _app = new Excel.Application { Visible = false, DisplayAlerts = false };
        _book = _app.Workbooks.Add();
        _sheets = _book.Worksheets;
    }

    private Excel.Worksheet AddSheet()
    {
        return (Excel.Worksheet)_sheets.Add(After: _sheets[_sheets.Count]);
    }

    private int GetRowCount(string sqlTemplate)
    {
        using (var conn = new SqlConnection(_connStr))
        using (var cmd = new SqlCommand(sqlTemplate.Replace("SELECT *", "SELECT COUNT(*)"), conn))
        {
            conn.Open();
            return (int)cmd.ExecuteScalar();
        }
    }

    private void ExportPage(Excel.Worksheet ws, int pageNo, string tableName, string sqlTemplate)
    {
        string sql = BuildPageSql(pageNo, sqlTemplate);
        Excel.Range startCell = ws.get_Range("A2"); // A1 留給標題

        // 寫入標題列 (可從資料庫 schema 動態取得)
        ws.Cells[1, 1] = "欄位1"; ws.Cells[1, 2] = "欄位2"; /* ... */

        var qt = ws.QueryTables.Add($"OLEDB;Provider=SQLOLEDB.1;{_connStr}", startCell, sql);
        qt.RefreshStyle = Excel.XlCellInsertionMode.xlInsertEntireRows;
        qt.FieldNames = false; // 不重複寫入標題
        qt.Refresh(false); // 同步執行，完成才返回
    }

    private string BuildPageSql(int pageNo, string baseSql)
    {
        // SQL Server 分頁寫法 (SQL 2012+ OFFSET/FETCH 或舊版 ROW_NUMBER)
        // 此處示範舊版 TOP + NOT IN 邏輯 (原始碼方式)，效能較差
        // 建議改用 OFFSET/FETCH 或 Keyset Pagination
        int skip = (pageNo - 1) * SheetRowLimit;
        return $"{baseSql} ORDER BY Id OFFSET {skip} ROWS FETCH NEXT {SheetRowLimit} ROWS ONLY";
    }

    private void Cleanup()
    {
        if (_book != null) { _book.Close(false); Marshal.ReleaseComObject(_book); }
        if (_app != null) { _app.Quit(); Marshal.ReleaseComObject(_app); }
        GC.Collect(); GC.WaitForPendingFinalizers();
    }
}

關鍵優化細節

• 分頁策略： 單工作表列數上限 (.xls 65,535 / .xlsx 1,048,576)，超過必須分表。
• 分頁 SQL： 原始碼使用 TOP ... NOT IN (SELECT TOP ...) 效能極差，務必改用 OFFSET/FETCH (SQL 2012+) 或 Keyset Pagination (WHERE Id > @lastId ORDER BY Id LIMIT @pageSize)。
• 標題列處理： FieldNames=false 避免 QueryTable 重複寫入標題，自行寫入 A1 列。
• COM 物件釋放： 必須 Marshal.ReleaseComObject 並 GC.Collect()，否則 Excel 進程殘留。
• 伺服器端禁用： Interop 需安裝 Excel、非執行緒安全、記憶體大、權限複雜，**生產環境/伺服器/雲端請改用 OpenXML SDK / EPPlus / NPOI / ClosedXML**。

現代化替代：OpenXML SDK 範例

using DocumentFormat.OpenXml.Packaging;
using DocumentFormat.OpenXml.Spreadsheet;
using System.Data.SqlClient;

public void ExportOpenXml(string connStr, string sql, string filePath)
{
    using (var doc = SpreadsheetDocument.Create(filePath, SpreadsheetDocumentType.Workbook))
    {
        var wbPart = doc.AddWorkbookPart();
        wbPart.Workbook = new Workbook();
        var wsPart = wbPart.AddNewPart();
        wsPart.Worksheet = new Worksheet(new SheetData());

        var sheets = wbPart.Workbook.AppendChild(new Sheets());
        sheets.Append(new Sheet { Id = wbPart.GetIdOfPart(wsPart), SheetId = 1, Name = "Data" });

        var sheetData = wsPart.Worksheet.GetFirstChild();

        // 標題列
        var headerRow = new Row();
        // 動態建立標題...
        sheetData.Append(headerRow);

        using (var conn = new SqlConnection(connStr))
        using (var cmd = new SqlCommand(sql, conn))
        {
            conn.Open();
            using (var reader = cmd.ExecuteReader())
            {
                while (reader.Read())
                {
                    var row = new Row();
                    for (int i = 0; i < reader.FieldCount; i++)
                        row.Append(new Cell { CellValue = new CellValue(reader.GetValue(i)?.ToString() ?? ""), DataType = CellValues.String });
                    sheetData.Append(row);
                }
            }
        }
        wsPart.Worksheet.Save();
    }
}

OpenXML 純 managed、無 Excel 相依、支援 .xlsx、伺服器端部署無痛、效能優異。