SQL2005CLR函数扩展-数据导出的实现详解
程序员文章站
2023-10-31 08:47:40
sqlserver数据导出到excel有很多种方法,比如dts、ssis、还可以用sql语句调用openrowset。我们这里开拓思路,用clr来生成excel文件,并且会...
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的循环里面的代码就行了。
下面我先演示一下我实现的效果,先看测试语句
--------------------------------------------------------------------------------
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的循环里面的代码就行了。