SQL2005CLR函数扩展-数据导出的实现详解

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文件。
--------------------------------------------------------------------------------