SQL2005CLR函数扩展-数据导出

最新推荐文章于 2025-07-04 13:28:10 发布

最新推荐文章于 2025-07-04 13:28:10 发布 · 72 阅读

文章标签：

#Excel #SQL #SQL Server #Microsoft #单元测试

本文介绍了一种使用CLR存储过程将SQLServer中的数据高效地导出到Excel的方法，支持单个文件最大65535条记录，并可根据指定数量拆分为多个文件。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

SQLServer数据导出到excel有很多种方法，比如dts、ssis、还可以用sql语句调用openrowset。我们这里开拓思路，用CLR来生成Excel文件，并且会考虑一些方便操作的细节。

下面我先演示一下我实现的效果，先看测试语句

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 字节

文件 d:\test\testTable.2.xls, 共65534条 , 大小 20 ,040,589 字节

文件 d:\test\testTable.3.xls, 共65534条 , 大小 19 ,948,925 字节

文件 d:\test\testTable.4.xls, 共65534条 , 大小 20 ,080,974 字节

文件 d:\test\testTable.5.xls, 共65534条 , 大小 20 ,056,737 字节

文件 d:\test\testTable.6.xls, 共65534条 , 大小 20 ,590,933 字节

文件 d:\test\testTable.7.xls, 共26002条 , 大小 8,419,533 字节

导出数据完成

-------

共484740条数据，耗时 23812ms

上面的BulkCopyToXls存储过程是自定的CLR存储过程。他有四个参数：

第一个是sql语句用来获取数据集

第二个是文件保存的路径

第三个是结果集的名字，我们用它来给文件命名

第四个是限制单个文件可以保存多少条记录,小于等于0表示最多65534条。

前三个参数没有什么特别，最后一个参数的设置可以让一个数据集分多个excel文件保存。比如传统excel的最大容量是65535条数据。我们这里参数设置为-1就表示导出达到这个数字之后自动写下一个文件。如果你设置了比如100，那么每导出100条就会自动写下一个文件。

另外每个文件都可以输出字段名作为表头，所以单个文件最多容纳65534条数据。

用微软公开的biff8格式通过二进制流生成excel，服务器无需安装excel组件，而且性能上不会比sql自带的功能差，48万多条数据，150M，用了24秒完成。

下面我们来看下CLR代码。通过sql语句获取DataReader，然后分批用biff格式来写xls文件。

using System;

using System.Data;

using System.Data.SqlClient;

using System.Data.SqlTypes;

using Microsoft.SqlServer.Server;

public partial class StoredProcedures

{

/// <summary>

/// 导出数据

/// </summary>

/// <param name="sql"></param>

/// <param name="savePath"></param>

/// <param name="tableName"></param>

/// <param name="maxRecordCount"></param>

[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(" 输入信息不完整！" );

}

ushort _maxRecordCount = ushort .MaxValue-1;

if (maxRecordCount.IsNull == false && maxRecordCount.Value < ushort .MaxValue&&maxRecordCount.Value>0)

_maxRecordCount = (ushort )maxRecordCount.Value;

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

}

/// <summary>

/// 查询数据，生成文件

/// </summary>

/// <param name="sql"></param>

/// <param name="savePath"></param>

/// <param name="tableName"></param>

/// <param name="maxRecordCount"></param>

private static void ExportXls(string sql, string savePath, string tableName, System.UInt16 maxRecordCount)

{

if (System.IO.Directory .Exists(savePath) == false )

{

System.IO.Directory .CreateDirectory(savePath);

}

using (SqlConnection conn = new SqlConnection ("context connection=true" ))

{

conn.Open();

using (SqlCommand command = conn.CreateCommand())

{

command.CommandText = sql;

using (SqlDataReader reader = command.ExecuteReader())

{

int i = 0;

int totalCount = 0;

int tick = System.Environment .TickCount;

SqlContext .Pipe.Send(" 开始导出数据" );

while (true )

{

string fileName = string .Format(@"{0}\{1}.{2}.xls" , savePath, tableName, i++);

int iExp = Write(reader, maxRecordCount, fileName);

long size = new System.IO.FileInfo (fileName).Length;

totalCount += iExp;

SqlContext .Pipe.Send(string .Format(" 文件{0}, 共{1} 条, 大小{2} 字节" , fileName, iExp, size.ToString("###,###" )));

if (iExp < maxRecordCount) break ;

}

tick = System.Environment .TickCount - tick;

SqlContext .Pipe.Send(" 导出数据完成" );

SqlContext .Pipe.Send("-------" );

SqlContext .Pipe.Send(string .Format(" 共{0} 条数据，耗时{1}ms" ,totalCount,tick));

}

/// <summary>

/// 写单元格

/// </summary>

/// <param name="writer"></param>

/// &