本文提供 BulkCopyToXls CLR 預存程序的 **完整可編譯 C# 代碼** 與部署腳本,支援任意查詢結果集匯出為原生 .xls (BIFF8),自動分卷、自動建立目錄、型別感知寫入,適合 SSIS/排程作業直接呼叫。
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、關閉串流 */ }
}
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 萬列分一檔
.xlsx (OpenXML SDK) 或 CSV。PERMISSION_SET = UNSAFE,生產環境請嚴格管控簽署/授權。SqlDataReader 流式讀取,不會將全結果集載入記憶體,適合大量資料。