SQL Server 2005 CLR 實作：高效能匯出 Excel 大量資料

將 SQL Server 資料匯出至 Excel 的傳統方式有 DTS、SSIS 或 OPENROWSET，但這裡介紹一種更彈性、效能優異的作法：撰寫 CLR 預存程序，以二進位流直接產生 BIFF8 格式的 .xls 檔案。此法無需伺服器安裝 Excel 元件，且能自動依據記錄數分割多個檔案，突破單一 Excel 檔 65,535 列的限制。

實際執行效果展示

EXEC BulkCopyToXls 'SELECT * FROM testTable', 'D:/test', 'testTable', -1;
/*
開始匯出資料
檔案 D:/test/testTable.0.xls, 共 65,534 筆, 大小 20,450,868 位元組
檔案 D:/test/testTable.1.xls, 共 65,534 筆, 大小 20,101,773 位元組
...
檔案 D:/test/testTable.7.xls, 共 26,002 筆, 大小 8,419,533 位元組
匯出資料完成
-------
共 484,740 筆資料，耗時 23,812 ms
*/

預存程式參數說明

核心 CLR 程式碼 (C#)

主要邏輯：建立 SqlDataReader 讀取資料 → 依參數分批 → 以 ExcelWriter 類別撰寫 BIFF8 二進位格式。

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

public partial class StoredProcedures
{
    [Microsoft.SqlServer.Server.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 _maxRecordCount = ushort.MaxValue - 1; // 65534
        if (!maxRecordCount.IsNull && maxRecordCount.Value > 0 && maxRecordCount.Value < ushort.MaxValue)
            _maxRecordCount = (ushort)maxRecordCount.Value;

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

    private static void ExportXls(string sql, string savePath, string tableName, ushort maxRecordCount)
    {
        if (!Directory.Exists(savePath)) Directory.CreateDirectory(savePath);

        using (SqlConnection conn = new SqlConnection("context connection=true"))
        {
            conn.Open();
            using (SqlCommand cmd = conn.CreateCommand())
            {
                cmd.CommandText = sql;
                using (SqlDataReader reader = cmd.ExecuteReader())
                {
                    int fileIndex = 0, totalCount = 0;
                    int startTick = Environment.TickCount;
                    SqlContext.Pipe.Send("開始匯出資料");

                    while (true)
                    {
                        string fileName = string.Format(@"{0}/{1}.{2}.xls", savePath, tableName, fileIndex++);
                        int exported = Write(reader, maxRecordCount, fileName);
                        long size = new FileInfo(fileName).Length;
                        totalCount += exported;
                        SqlContext.Pipe.Send(string.Format("檔案 {0}, 共 {1} 筆, 大小 {2} 位元組", fileName, exported, size.ToString("###,###")));
                        if (exported < maxRecordCount) break;
                    }

                    int elapsed = Environment.TickCount - startTick;
                    SqlContext.Pipe.Send("匯出資料完成");
                    SqlContext.Pipe.Send("-------");
                    SqlContext.Pipe.Send(string.Format("共 {0} 筆資料，耗時 {1} ms", totalCount, elapsed));
                }
            }
        }
    }

    private static void WriteObject(ExcelWriter writer, object obj, ushort x, ushort y)
    {
        string typeName = obj.GetType().Name;
        switch (typeName)
        {
            case "SqlBoolean": case "SqlByte": case "SqlDecimal": case "SqlDouble":
            case "SqlInt16": case "SqlInt32": case "SqlInt64": case "SqlMoney": case "SqlSingle":
                if (obj.ToString().ToLower() == "null")
                    writer.WriteString(x, y, obj.ToString());
                else
                    writer.WriteNumber(x, y, Convert.ToDouble(obj.ToString()));
                break;
            default:
                writer.WriteString(x, y, obj.ToString());
                break;
        }
    }

    private static int Write(SqlDataReader reader, ushort count, string fileName)
    {
        int actual = count;
        ExcelWriter writer = new ExcelWriter(fileName);
        writer.BeginWrite();

        // 寫入欄位名稱 (第 0 列)
        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()) { actual = i - 1; break; }
            for (ushort j = 0; j < reader.FieldCount; j++)
                WriteObject(writer, reader.GetSqlValue(j), i, j);
        }
        writer.EndWrite();
        return actual;
    }

    public class ExcelWriter
    {
        private FileStream _stream;
        public ExcelWriter(string path) { _stream = new FileStream(path, FileMode.OpenOrCreate); }

        private void WriteBytes(ushort[] values)
        {
            foreach (ushort v in values)
            {
                byte[] b = BitConverter.GetBytes(v);
                _stream.Write(b, 0, b.Length);
            }
        }

        public void BeginWrite() { WriteBytes(new ushort[] { 0x809, 8, 0, 0x10, 0, 0 }); }
        public void EndWrite() { WriteBytes(new ushort[] { 0x0A, 0 }); _stream.Close(); }

        public void WriteNumber(ushort x, ushort y, double value)
        {
            WriteBytes(new ushort[] { 0x203, 14, x, y, 0 });
            byte[] b = BitConverter.GetBytes(value);
            _stream.Write(b, 0, b.Length);
        }

        public void WriteString(ushort x, ushort y, string value)
        {
            byte[] b = System.Text.Encoding.Default.GetBytes(value);
            WriteBytes(new ushort[] { 0x204, (ushort)(b.Length + 8), x, y, 0, (ushort)b.Length });
            _stream.Write(b, 0, b.Length);
        }
    }
}

部署 CLR 組件與建立預存程序

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

擴充應用建議

掌握此技術後，可依業務需求客製化分檔邏輯：例如依據特定欄位值（如城市、部門）將資料分散至不同檔案，而非單純依筆數分割，只需調整 DataReader 迴圈邏輯即可。