您现在的位置：程式碼之家 >> 數據庫知識 > >> SQL Server 資料庫 >> 關於SqlServer

SQL Server CLR 預存程序大量匯出 Excel：Biff8 二進位格式、自動分卷、無需安裝 Office

編輯：關於SqlServer

傳統匯出 Excel 方式（DTS/SSIS/OPENROWSET）依賴外部元件或安裝 Office。本文實作 **CLR 預存程序** 直接以 **Biff8 (BIFF8) 二進位格式** 寫入 `.xls` 檔案，支援單檔 65,534 列自動分卷、欄位標題自動輸出、無需伺服器安裝 Excel，48 萬筆 150 MB 僅耗 24 秒。

功能特色

參數化：SQL 查詢、儲存路徑、檔名前綴、單檔最大列數（≤0 表 65,534）。
自動分卷：超過限制自動建立 `檔名.0.xls`、`檔名.1.xls` ...
每檔第一列自動輸出欄位名稱。
支援所有 SQL 資料型別對應寫入（數值/字串/Null）。
純 .NET `FileStream` + 位元組陣列寫入，效能接近原生 BCP。

呼叫範例


EXEC BulkCopyToXls 'SELECT * FROM testTable', 'D:/test', 'testTable', -1;

輸出訊息：

開始導出數據
檔案 D:/test/testTable.0.xls, 共65534條, 大小20,450,868 位元組
檔案 D:/test/testTable.1.xls, 共65534條, 大小20,101,773 位元組
...
導出數據完成
-------
共484740條數據，耗時 23812ms

CLR C# 核心程式碼


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

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 _max = (ushort)(maxRecordCount.IsNull == false && maxRecordCount.Value > 0 && maxRecordCount.Value < ushort.MaxValue ? maxRecordCount.Value : ushort.MaxValue - 1);
        ExportXls(sql.Value, savePath.Value, tableName.Value, _max);
    }

    private static void ExportXls(string sql, string path, string name, ushort maxRows)
    {
        if (!Directory.Exists(path)) Directory.CreateDirectory(path);
        using (var conn = new SqlConnection("context connection=true"))
        {
            conn.Open();
            using (var cmd = conn.CreateCommand()) { cmd.CommandText = sql; using (var rd = cmd.ExecuteReader()) { ExportReader(rd, path, name, maxRows); } }
        }
    }

    private static void ExportReader(SqlDataReader rd, string path, string name, ushort maxRows)
    {
        int fileIdx = 0, total = 0, tick = Environment.TickCount;
        SqlContext.Pipe.Send("開始導出數據");
        while (true)
        {
            string file = Path.Combine(path, $"{name}.{fileIdx++}.xls");
            int written = WriteXls(rd, maxRows, file);
            long sz = new FileInfo(file).Length;
            total += written;
            SqlContext.Pipe.Send($"檔案 {file}, 共 {written} 條, 大小 {sz:#,###} 位元組");
            if (written < maxRows) break;
        }
        SqlContext.Pipe.Send($"導出完成\n-------\n共 {total} 條，耗時 {Environment.TickCount - tick}ms");
    }

    private static int WriteXls(SqlDataReader rd, ushort maxRows, string file)
    {
        var writer = new ExcelWriter(file); writer.BeginWrite();
        for (ushort j = 0; j < rd.FieldCount; j++) writer.WriteString(0, j, rd.GetName(j));
        ushort i = 1; for (; i <= maxRows; i++) { if (!rd.Read()) break; for (ushort j = 0; j < rd.FieldCount; j++) WriteCell(writer, rd.GetSqlValue(j), i, j); }
        writer.EndWrite(); return i - 1;
    }

    private static void WriteCell(ExcelWriter w, object val, ushort r, ushort c)
    {
        string t = val.GetType().Name;
        if (t.StartsWith("Sql") && t != "SqlString" && t != "SqlBinary" && t != "SqlXml" && t != "SqlGuid" && t != "SqlBytes" && t != "SqlChars")
        { if (val.ToString().ToLower() == "null") w.WriteString(r, c, "NULL"); else w.WriteNumber(r, c, Convert.ToDouble(val.ToString())); }
        else w.WriteString(r, c, val.ToString());
    }

    public class ExcelWriter
    {
        FileStream _fs;
        public ExcelWriter(string p) { _fs = new FileStream(p, FileMode.Create); }
        void WriteShorts(ushort[] v) { foreach (var s in v) { byte[] b = BitConverter.GetBytes(s); _fs.Write(b, 0, b.Length); } }
        public void BeginWrite() { WriteShorts(new ushort[] { 0x0809, 8, 0, 0x10, 0, 0 }); } // BOF
        public void EndWrite() { WriteShorts(new ushort[] { 0x0A, 0 }); _fs.Close(); } // EOF
        public void WriteNumber(ushort r, ushort c, double v) { WriteShorts(new ushort[] { 0x0203, 14, r, c, 0 }); byte[] b = BitConverter.GetBytes(v); _fs.Write(b, 0, b.Length); }
        public void WriteString(ushort r, ushort c, string v) { byte[] b = Encoding.Default.GetBytes(v); WriteShorts(new ushort[] { 0x0204, (ushort)(b.Length + 8), r, c, 0, (ushort)b.Length }); _fs.Write(b, 0, b.Length); }
    }
}

部署 T-SQL


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