如何使用SQL CLR生成无大量不兼容依赖的XLS文件？

核心思路 #

放弃依赖WindowsBase的旧版OpenXML SDK，改用微软官方维护的DocumentFormat.OpenXml NuGet包——这个版本是重构后的纯托管实现，完全不依赖System.XAML或WindowsBase，完美兼容SQL CLR环境。

步骤1：创建SQL CLR项目 #

1. 新建.NET Framework 4.7.2类库项目（SQL Server 2019原生支持该版本）
2. 安装NuGet包：DocumentFormat.OpenXml（选择兼容.NET Framework 4.7.2的版本，如2.18.0）
3. 编写生成XLSX的CLR方法，示例代码如下：

using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
using DocumentFormat.OpenXml;
using DocumentFormat.OpenXml.Packaging;
using DocumentFormat.OpenXml.Spreadsheet;
using System.IO;

public class ExcelGenerator
{
    [SqlFunction(DataAccess = DataAccessKind.Read)]
    public static SqlBytes GenerateXlsxFromTable(SqlString tableName)
    {
        // 使用上下文连接访问当前数据库
        using (var conn = new SqlConnection("context connection=true"))
        {
            conn.Open();
            var cmd = new SqlCommand($"SELECT * FROM {tableName.Value}", conn);
            var adapter = new SqlDataAdapter(cmd);
            var dt = new DataTable();
            adapter.Fill(dt);

            // 内存中生成XLSX
            using (var ms = new MemoryStream())
            {
                using (var doc = SpreadsheetDocument.Create(ms, SpreadsheetDocumentType.Workbook))
                {
                    var workbookPart = doc.AddWorkbookPart();
                    workbookPart.Workbook = new Workbook();

                    var worksheetPart = workbookPart.AddNewPart<WorksheetPart>();
                    worksheetPart.Worksheet = new Worksheet(new SheetData());

                    // 添加工作表关联
                    var sheets = workbookPart.Workbook.AppendChild(new Sheets());
                    sheets.Append(new Sheet
                    {
                        Id = workbookPart.GetIdOfPart(worksheetPart),
                        SheetId = 1,
                        Name = "数据导出"
                    });

                    var sheetData = worksheetPart.Worksheet.GetFirstChild<SheetData>();

                    // 写入表头
                    var headerRow = new Row();
                    foreach (DataColumn col in dt.Columns)
                    {
                        headerRow.AppendChild(new Cell
                        {
                            DataType = CellValues.String,
                            CellValue = new CellValue(col.ColumnName)
                        });
                    }
                    sheetData.AppendChild(headerRow);

                    // 写入数据行
                    foreach (DataRow row in dt.Rows)
                    {
                        var dataRow = new Row();
                        foreach (var val in row.ItemArray)
                        {
                            dataRow.AppendChild(new Cell
                            {
                                DataType = CellValues.String,
                                CellValue = new CellValue(val?.ToString() ?? string.Empty)
                            });
                        }
                        sheetData.AppendChild(dataRow);
                    }

                    workbookPart.Workbook.Save();
                }

                ms.Position = 0;
                return new SqlBytes(ms);
            }
        }
    }
}

步骤2：部署到SQL Server #

1. 启用CLR并配置数据库权限：

-- 启用高级配置选项
sp_configure 'show advanced options', 1;
RECONFIGURE;

-- 启用CLR
sp_configure 'clr enabled', 1;
RECONFIGURE;

-- 设置数据库为TRUSTWORTHY（若用证书签名可替代此步骤，更安全）
ALTER DATABASE YourDatabaseName SET TRUSTWORTHY ON;

2. 注册依赖程序集和自定义CLR程序集：

-- 注册DocumentFormat.OpenXml（替换为你的DLL路径）
CREATE ASSEMBLY DocumentFormatOpenXml
FROM 'C:\Deploy\DocumentFormat.OpenXml.dll'
WITH PERMISSION_SET = SAFE;

-- 注册自定义CLR程序集（替换为你的DLL路径）
CREATE ASSEMBLY ExcelGeneratorCLR
FROM 'C:\Deploy\YourAssembly.dll'
WITH PERMISSION_SET = EXTERNAL_ACCESS; -- 需要访问数据库连接，故用此权限

3. 创建SQL函数：

CREATE FUNCTION dbo.GenerateXlsxFromTable(@tableName NVARCHAR(128))
RETURNS VARBINARY(MAX)
AS EXTERNAL NAME ExcelGeneratorCLR.ExcelGenerator.GenerateXlsxFromTable;

使用示例 #

执行函数并导出结果为XLSX文件：

-- 查询并生成XLSX字节流
SELECT dbo.GenerateXlsxFromTable('YourTableName') AS ExcelData;

将查询结果保存为.xlsx文件即可正常打开。

关键注意事项 #

• 避免使用旧版Microsoft.Office.Interop.Excel或依赖System.Drawing的ClosedXML，这类库不符合SQL CLR安全规范
• 若无需访问数据库，可将程序集权限设为SAFE，进一步降低安全风险
• 生产环境建议用证书签名替代TRUSTWORTHY ON，避免数据库权限过度开放

内容的提问来源于stack exchange，提问作者Sean