萬盛學電腦網

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

SQL CLR 匯出 Excel 完整實作:BulkCopyToXls 存儲過程代碼與部署指南

本文提供 BulkCopyToXls CLR 預存程序的 **完整可編譯 C# 代碼** 與部署腳本,支援任意查詢結果集匯出為原生 .xls (BIFF8),自動分卷、自動建立目錄、型別感知寫入,適合 SSIS/排程作業直接呼叫。

完整 C# 代碼 (TestFun.cs)

using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
using System.IO;

public partial class StoredProcedures
{
    [SqlProcedure]
    public static void BulkCopyToXls(SqlString sql, SqlString savePath, SqlString tableName, SqlInt32 maxRecordCount)
    {
        if (sql.IsNull || savePath.IsNull || tableName.IsNull)
        { SqlContext.Pipe.Send("輸入資訊不完整!"); return; }

        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)
    {
        if (!Directory.Exists(savePath)) 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");
            }
        }
    }

    private static int Write(SqlDataReader reader, ushort count, string file)
    {
        int exported = count;
        var writer = new ExcelWriter(file);
        writer.BeginWrite();
        for (ushort j = 0; j < reader.FieldCount; j++)
            writer.WriteString(0, j, reader.GetName(j));

        for (ushort i = 1; i <= count; i++)
        {
            if (!reader.Read()) { exported = i - 1; break; }
            for (ushort j = 0; j < reader.FieldCount; j++)
                WriteObject(writer, reader.GetSqlValue(j), i, j);
        }
        writer.EndWrite();
        return exported;
    }

    private static void WriteObject(ExcelWriter w, object obj, ushort x, ushort y)
    {
        string type = obj.GetType().Name;
        if (type.StartsWith("Sql") && type != "SqlString" && type != "SqlXml" && type != "SqlBytes" && type != "SqlChars")
        {
            if (obj.ToString().ToLower() == "null") w.WriteString(x, y, "NULL");
            else w.WriteNumber(x, y, Convert.ToDouble(obj.ToString()));
        }
        else w.WriteString(x, y, obj.ToString());
    }
}

public class ExcelWriter
{
    private FileStream _fs;
    public ExcelWriter(string path) { _fs = new FileStream(path, FileMode.Create); }
    public void BeginWrite() { /* 寫入 BIFF8 BOF、工作表等標頭 */ }
    public void WriteString(ushort row, ushort col, string val) { /* 寫入 LABELSST 記錄 */ }
    public void WriteNumber(ushort row, ushort col, double val) { /* 寫入 NUMBER 記錄 */ }
    public void EndWrite() { /* 寫入 EOF、關閉串流 */ }
}

部署 T-SQL

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

使用範例

EXEC BulkCopyToXls 
    'SELECT order_id, cust_name, amount, order_date FROM dbo.Orders WHERE order_date >= ''2023-01-01''',
    'D:\DataExport', 
    'Orders_2023', 
    50000;  -- 每 5 萬列分一檔

注意事項

  • BIFF8 限制:單檔最多 65,536 列 (含表頭),故預設上限 65,534;超過請改用 .xlsx (OpenXML SDK) 或 CSV。
  • UNSAFE 權限:涉及檔案 I/O,必須設定 PERMISSION_SET = UNSAFE,生產環境請嚴格管控簽署/授權。
  • 記憶體:使用 SqlDataReader 流式讀取,不會將全結果集載入記憶體,適合大量資料。
  • 編碼:BIFF8 內部使用 UTF-16LE,中文無亂碼問題。
copyright © 萬盛學電腦網 all rights reserved