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

复制代码 代码如下:

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>
/// <param name="obj"></param>
/// <param name="x"></param>
/// <param name="y"></param>
private static void WriteObject(ExcelWriter writer, object obj, System.UInt16 x, System.UInt16 y)
{
string type = obj.GetType().Name.ToString();
switch (type)
{
case "SqlBoolean" :
case "SqlByte" :
case "SqlDecimal" :
case "SqlDouble" :
case "SqlInt16" :
case "SqlInt32" :
case "SqlInt64" :
case "SqlMoney" :
case "SqlSingle" :
if (obj.ToString().ToLower() == "null" )
writer.WriteString(x, y, obj.ToString());
else
writer.WriteNumber(x, y, Convert .ToDouble(obj.ToString()));
break ;
default :
writer.WriteString(x, y, obj.ToString());
break ;
}
}
/// <summary>
/// 写一批数据到一个excel 文件
/// </summary>
/// <param name="reader"></param>
/// <param name="count"></param>
/// <param name="fileName"></param>
/// <returns></returns>
private static int Write(SqlDataReader reader, System.UInt16 count, string fileName)
{
int iExp = count;
ExcelWriter writer = new ExcelWriter (fileName);
writer.BeginWrite();
for (System.UInt16 j = 0; j < reader.FieldCount; j++)
{
writer.WriteString(0, j, reader.GetName(j));
}
for (System.UInt16 i = 1; i <= count; i++)
{
if (reader.Read() == false )
{
iExp = i-1;
break ;
}
for (System.UInt16 j = 0; j < reader.FieldCount; j++)
{
WriteObject(writer, reader.GetSqlValue(j), i, j);
}
}
writer.EndWrite();
return iExp;
}

/// <summary>
/// 写excel 的对象
/// </summary>
public class ExcelWriter
{
System.IO.FileStream _wirter;
public ExcelWriter(string strPath)
{
_wirter = new System.IO.FileStream (strPath, System.IO.FileMode .OpenOrCreate);
}
/// <summary>
/// 写入short 数组
/// </summary>
/// <param name="values"></param>
private void _writeFile(System.UInt16 [] values)
{
foreach (System.UInt16 v in values)
{
byte [] b = System.BitConverter .GetBytes(v);
_wirter.Write(b, 0, b.Length);
}
}
/// <summary>
/// 写文件头
/// </summary>
public void BeginWrite()
{
_writeFile(new System.UInt16 [] { 0x809, 8, 0, 0x10, 0, 0 });
}
/// <summary>
/// 写文件尾
/// </summary>
public void EndWrite()
{
_writeFile(new System.UInt16 [] { 0xa, 0 });
_wirter.Close();
}
/// <summary>
/// 写一个数字到单元格x,y
/// </summary>
/// <param name="x"></param>
/// <param name="y"></param>
/// <param name="value"></param>
public void WriteNumber(System.UInt16 x, System.UInt16 y, double value)
{
_writeFile(new System.UInt16 [] { 0x203, 14, x, y, 0 });
byte [] b = System.BitConverter .GetBytes(value);
_wirter.Write(b, 0, b.Length);
}
/// <summary>
/// 写一个字符到单元格x,y
/// </summary>
/// <param name="x"></param>
/// <param name="y"></param>
/// <param name="value"></param>
public void WriteString(System.UInt16 x, System.UInt16 y, string value)
{
byte [] b = System.Text.Encoding .Default.GetBytes(value);
_writeFile(new System.UInt16 [] { 0x204, (System.UInt16 )(b.Length + 8), x, y, 0, (System.UInt16 )b.Length });
_wirter.Write(b, 0, b.Length);
}
}
};

--------------------------------------------------------------------------------
把上面代码编译为TestExcel.dll，copy到服务器目录。然后通过如下SQL语句部署存储过程。
--------------------------------------------------------------------------------

复制代码 代码如下:

CREATE ASSEMBLY TestExcelForSQLCLR FROM 'd:/sqlclr/TestExcel.dll' WITH PERMISSION_SET = UnSAFE;
--
go
CREATE proc dbo. BulkCopyToXls
(
@sql nvarchar ( max ),
@savePath nvarchar ( 1000),
@tableName nvarchar ( 1000),
@bathCount int
)
AS EXTERNAL NAME TestExcelForSQLCLR. StoredProcedures. BulkCopyToXls

go

--------------------------------------------------------------------------------
当 这项技术掌握在我们自己手中的时候，就可以随心所欲的来根据自己的需求定制。比如，我可以不要根据序号来分批写入excel，而是根据某个字段的值（比如 一个表有200个城市的8万条记录）来划分为n个文件，而这个修改只要调整一下DataReader的循环里面的代码就行了。

时间： 2024-10-29 16:59:34

SQL2005CLR函数扩展-数据导出的实现详解的相关文章

Oracle数据操作和控制语言详解_oracle

正在看的ORACLE教程是:Oracle数据操作和控制语言详解.SQL语言共分为四大类:数据查询语言DQL,数据操纵语言DML, 数据定义语言DDL,数据控制语言DCL.其中用于定义数据的结构,比如 创建.修改或者删除数据库:DCL用于定义数据库用户的权限:在这篇文章中我将详细讲述这两种语言在Oracle中的使用方法.  DML语言 DML是SQL的一个子集,主要用于修改数据,下表列出了ORACLE支持的DML语句. 插入数据 INSERT语句常常用于向表中插入行,行中可以有特殊数据字段,或者可

c语言计时函数返回时间不定，求详解为什么（只点击了两次运行，并未改变代码）

问题描述 c语言计时函数返回时间不定,求详解为什么(只点击了两次运行,并未改变代码) 代码如上, 结果如下: 第一次 第二次: 解决方案 同样的代码,执行过程中计算机可能遇到各种小问题,一般来说短期内第二次运行会快点. 第一次会进行资源的分配,将代码拷到内存里,再执行. 如果在很短的时间内进行第二次运行,代码还在内存里,CPU少了将代码拷到内存的操作,所以会快上一点. 你问的这个问题设计到了操作系统的CPU局部性策略,操作系统在执行程序时,并不是像C一样,顺序执行,因为同一时间,并不是只有你的程

JavaScript函数节流概念与用法实例详解_javascript技巧

本文实例讲述了JavaScript函数节流概念与用法.分享给大家供大家参考,具体如下: 最近在做网页的时候有个需求,就是浏览器窗口改变的时候需要改一些页面元素大小,于是乎很自然的想到了window的resize事件,于是乎我是这么写的 <!DOCTYPE html> <html> <head> <title>Throttle</title> </head> <body> <script type="text

Oracle误删除表数据后的数据恢复详解_oracle

Oracle误删除表数据后的恢复详解   测试环境: SYSTEM:IBM AIX 5L                         Oracle Version:10gR2   1. undo_retention参数的查询与修改 使用show parameter undo命令查看当前的数据库参数undo_retention设置. 显示如下: SQL> show parameter undo NAME                                 TYPE        V

Android 模仿iPhone列表数据View刷新动画详解_Android

因为我本人很喜欢在不同的页面之间跳转时加点好玩的动画,今天无意间看到一个动画效果感觉不错,几种效果图如下:既然好玩就写在博客中,直接说就是:该效果类似于iPhone中View的切换动画效果,今天就只介绍上面展示的效果. 废话不多说,先上效果,再看代码!! 效果一: 效果二: 效果三: 效果四:(犯错的效果): 效果五(回旋效果一): 效果六(回旋效果二): 效果看完了,就来看下上面效果实现的具体代码吧, 中间会把我自己试验的.犯的错误都以注释的形式写下来的, 大家使用的时候别出错就行了!先来看下

Struts2数据输入验证教程详解_java

一.前言 1.1.什么是输入验证?为什么需要输入验证? 在上一篇文章中,我们学习了数据类型转换,我们提到了表示层数据处理的两个方法,也提到了用户输入数据需要进行类型转换才能得到我们想要的数据,那么,我们怎么确定类型转换后的数据,是我们想要的数据呢?这里有点绕.你可以这样想:一个成年男子年龄是18岁,你现在想要得到18这个数据,但是,用户输入32,经过类型转换也是对的,但是数据不是你想要的.这时候,我们要怎么办?所以输入验证在这里就有用处了. 类型转换和输入验证的关系是:类型转换是输入验证的前提,

Android 两个Fragment之间的跳转和数据的传递实例详解

Android  两个Fragment之间的跳转和数据的传递实例详解 作为一个Android的菜鸟,前些天在做项目的时候用到了fragment,需求是从一个Fragment跳转到另一个Fragment,并且还要传递数据,就像Activity的跳转一样.在网上找了好久都没找到很好的列子,最后通过看别人的博客和查文档终于做好了,现在整理一下,希望能帮助有需要的童鞋. 1.首先在第一个Fragment 里面拿到FragmentManger 和FragmentTransaction 代码如下. @Ove

MySQL数据库的数据备份和恢复详解

本文讨论 MySQL 的备份和恢复机制,以及如何维护数据表,包括最主要的两种表类型:MyISAM 和 Innodb,文中设计的 MySQL 版本为 5.0.22. 目前 MySQL 支持的免费备份工具有:mysqldump.mysqlhotcopy,还可以用 SQL 语法进行备份:BACKUP TABLE 或者 SELECT INTO OUTFILE,又或者备份二进制日志(binlog),还可以是直接拷贝数据文件和相关的配置文件.MyISAM 表是保存成文件的形式,因此相对比较容易备份,上面提到

mysqldump备份还原和mysqldump导入导出语句大全详解_Mysql

MYSQLdump参数详解 mysqldump备份: 复制代码 代码如下: mysqldump -u用户名 -p密码 -h主机 数据库 a -w "sql条件" –lock-all-tables > 路径 mysqldump还原: 复制代码 代码如下: mysqldump -u用户名 -p密码 -h主机 数据库 < 路径 mysqldump按条件导出: 复制代码 代码如下: mysqldump -u用户名 -p密码 -h主机 数据库 a –where "条件语句&