萬盛學電腦網

您现在的位置: 萬盛學電腦網 >> 程式設計 >  >> 資料庫 >> MS SQL 資料庫

SQL CLR 大量匯出 Excel:BulkCopyToXls 預存程序實作詳解 (支援分卷、無需 Office 元件)

傳統匯出 Excel 方案 (DTS/SSIS/OPENROWSET) 部署繁瑣或需安裝 Office 驅動。本文實作一個 **CLR 預存程序** BulkCopyToXls,直接在 SQL Server 內以二進位流 (BIFF8 格式) 生成原生 .xls,無相依性、效能優異 (48 萬列 150 MB 僅 24 秒),並支援單檔列數上限自動分卷。

功能特色

  • 🚀 **純 managed 代碼**,伺服器無需安裝 Excel/ACE 驅動。
  • 📦 **自動分卷**:單檔超過指定列數 (預設 65,534,Excel 2003 上限) 自動建立下一檔 (名稱.0.xls, 名稱.1.xls...)。
  • 🏷️ **自動輸出欄位名** 做表頭。
  • 🔢 **型別感知寫入**:數值型別寫入數值格式,其餘寫字串,保真度高。

參數定義

參數型別說明
@sqlNVARCHAR(MAX)查詢語句,回傳要匯出的結果集
@savePathNVARCHAR(MAX)資料夾路徑 (自動建立)
@tableNameNVARCHAR(MAX)檔案名前綴
@maxRecordCountINT單檔最大列數,≤0 表示 65,534

測試呼叫與輸出

EXEC BulkCopyToXls 
    'SELECT * FROM testTable', 
    'D:\Export', 
    'testTable', 
    -1;  -- -1 表示使用預設 65534 分卷

訊息輸出範例:

開始導出資料
檔案 D:\Export\testTable.0.xls, 共 65534 筆, 大小 20,450,868 位元組
檔案 D:\Export\testTable.1.xls, 共 65534 筆, 大小 20,101,773 位元組
...
導出資料完成
-------
共 484740 筆資料,耗時 23812 ms

CLR 核心架構 (節錄)

[SqlProcedure]
public static void BulkCopyToXls(SqlString sql, SqlString savePath, SqlString tableName, SqlInt32 maxRecordCount)
{
    // 參數驗證、預設值處理
    ushort limit = (ushort)(maxRecordCount.IsNull || maxRecordCount.Value <= 0 || maxRecordCount.Value > UInt16.MaxValue - 1
        ? UInt16.MaxValue - 1 : maxRecordCount.Value);

    ExportXls(sql.Value, savePath.Value, tableName.Value, limit);
}

private static void ExportXls(string sql, string savePath, string tableName, ushort maxRecordCount)
{
    Directory.CreateDirectory(savePath);
    using (var conn = new SqlConnection("context connection=true"))
    using (var cmd = conn.CreateCommand())
    {
        cmd.CommandText = sql;
        conn.Open();
        using (var reader = cmd.ExecuteReader())
        {
            int fileIdx = 0, total = 0, tick = Environment.TickCount;
            SqlContext.Pipe.Send("開始導出資料");
            while (true)
            {
                string file = Path.Combine(savePath, $"{tableName}.{fileIdx++}.xls");
                int exported = Write(reader, maxRecordCount, file);
                total += exported;
                SqlContext.Pipe.Send($"檔案 {file}, 共 {exported} 筆, 大小 {new FileInfo(file).Length:N0} 位元組");
                if (exported < maxRecordCount) break;
            }
            SqlContext.Pipe.Send($"導出完成\n共 {total} 筆,耗時 {Environment.TickCount - tick} ms");
        }
    }
}

// 以 BIFF8 格式寫入單一 .xls
private static int Write(SqlDataReader reader, ushort count, string file) { /* ... */ }
private static void WriteObject(ExcelWriter w, object obj, ushort x, ushort y) { /* 型別分派 */ }

部署步驟

  1. 建立 C# Class Library 專案,目標 .NET Framework 3.5/4.0 (對應 SQL 版本)。
  2. 參考 Microsoft.SqlServer.Server、System.Data.SqlClient。
  3. 編譯為 TestFun.dll,複製到 SQL Server 主機 (如 E:\sqlclrdata\)。
  4. SSMS 執行:
CREATE ASSEMBLY TestForSQLCLR FROM 'E:\sqlclrdata\TestFun.dll' WITH PERMISSION_SET = UNSAFE;
GO
CREATE PROCEDURE BulkCopyToXls
    @sql NVARCHAR(MAX), @savePath NVARCHAR(MAX), @tableName NVARCHAR(MAX), @maxRecordCount INT
AS EXTERNAL NAME TestForSQLCLR.[StoredProcedures].BulkCopyToXls;
GO

權限與安全性

  • 需 PERMISSION_SET = UNSAFE (存取檔案系統、非受控代碼)。
  • 資料庫需設定 TRUSTWORTHY ON 或簽署非對稱金鑰/憑證登入。
  • 僅授信任 DBA/ETL 帳戶 EXECUTE 權限。

現代替代方案

SQL Server 2016+ 可直接用 OPENROWSET + Microsoft.ACE.OLEDB.12.0 或 bcp + PowerShell 輸出 CSV/Parquet,再由應用層轉 Excel;雲端環境 (Azure SQL) 不支援 CLR,請改用外部 ETL 服務。

copyright © 萬盛學電腦網 all rights reserved