SQL Server 2005 CLR 實作：資料匯出 Excel 完整解析

本文詳細解析如何利用 SQL CLR 開發高效能的 Excel 匯出功能。相較於傳統 DTS/SSIS/OPENROWSET，CLR 方案具有無需安裝 Office 元件、效能接近原生、支援自動分檔突破 65,535 列限制、可客製化分檔邏輯等優勢。

功能展示與參數設計

預存程序 BulkCopyToXls 接受四個參數：查詢 SQL、儲存路徑、檔案前綴、單檔最大筆數。實測 48 萬筆、150 MB 資料，24 秒完成匯出並自動分割為 8 個 .xls 檔案。

完整 CLR 原始碼 (C#)

程式碼結構與上一篇相同，核心類別包含 StoredProcedures.BulkCopyToXls 入口點、ExportXls 協調邏輯、Write 批次寫入、WriteObject 型別對應、以及 ExcelWriter 底層 BIFF8 二進位產生器。完整程式碼請參考前文（ID: 76656），此處不重複貼上。

部署步驟

1. 編譯為 TestExcel.dll，放置伺服器路徑 (如 D:\sqlclr\)。
2. 執行 T-SQL 註冊組件與建立預存程序：

CREATE ASSEMBLY TestExcelForSQLCLR FROM 'D:/sqlclr/TestExcel.dll' WITH PERMISSION_SET = UNSAFE;
GO
CREATE PROCEDURE dbo.BulkCopyToXls
    @sql NVARCHAR(MAX),
    @savePath NVARCHAR(1000),
    @tableName NVARCHAR(1000),
    @batchCount INT
AS EXTERNAL NAME TestExcelForSQLCLR.StoredProcedures.BulkCopyToXls;
GO

進階客製化應用

掌握核心架構後，可輕鬆調整分檔策略：

• 依欄位值分檔：在 Write 迴圈中判斷特定欄位 (如城市代碼) 變更時切換檔案。
• 支援 .xlsx (Open XML)：將 ExcelWriter 改用 DocumentFormat.OpenXml 產生現代格式，突破 2003 版限制。
• 加入樣式/公式：擴充 ExcelWriter 支援儲存格格式、合併儲存格、公式寫入。

效能優化建議

• 使用 SqlDataReader 串流式讀取，避免將大量資料載入記憶體。
• 批次大小設定為 65,534 (Excel 2003 上限) 可平衡檔案數與記憶體使用。
• 若資料極大 (百萬列以上)，建議改用 SqlBulkCopy 寫入中繼表，再由背景作業匯出。