傳統匯出 Excel 方案 (DTS/SSIS/OPENROWSET) 部署繁瑣或需安裝 Office 驅動。本文實作一個 **CLR 預存程序** BulkCopyToXls,直接在 SQL Server 內以二進位流 (BIFF8 格式) 生成原生 .xls,無相依性、效能優異 (48 萬列 150 MB 僅 24 秒),並支援單檔列數上限自動分卷。
名稱.0.xls, 名稱.1.xls...)。| 參數 | 型別 | 說明 |
|---|---|---|
| @sql | NVARCHAR(MAX) | 查詢語句,回傳要匯出的結果集 |
| @savePath | NVARCHAR(MAX) | 資料夾路徑 (自動建立) |
| @tableName | NVARCHAR(MAX) | 檔案名前綴 |
| @maxRecordCount | INT | 單檔最大列數,≤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
[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) { /* 型別分派 */ }
Microsoft.SqlServer.Server、System.Data.SqlClient。TestFun.dll,複製到 SQL Server 主機 (如 E:\sqlclrdata\)。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 或簽署非對稱金鑰/憑證登入。EXECUTE 權限。SQL Server 2016+ 可直接用 OPENROWSET + Microsoft.ACE.OLEDB.12.0 或 bcp + PowerShell 輸出 CSV/Parquet,再由應用層轉 Excel;雲端環境 (Azure SQL) 不支援 CLR,請改用外部 ETL 服務。