SQL2005CLR函数扩展-数据导出的实现详解
2023-06-13 09:15:02 时间
SQLServer数据导出到excel有很多种方法，比如dts、ssis、还可以用sql语句调用openrowset。我们这里开拓思路，用CLR来生成Excel文件，并且会考虑一些方便操作的细节。
下面我先演示一下我实现的效果，先看测试语句
--------------------------------------------------------------------------------
execBulkCopyToXls"select*fromtestTable","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文件。
--------------------------------------------------------------------------------
复制代码代码如下:
下面我先演示一下我实现的效果，先看测试语句
--------------------------------------------------------------------------------
/*
开始导出数据
文件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文件。
--------------------------------------------------------------------------------
usingSystem;
usingSystem.Data;
usingSystem.Data.SqlClient;
usingSystem.Data.SqlTypes;
usingMicrosoft.SqlServer.Server;
publicpartialclassStoredProcedures
{
///<summary>
///导出数据
///</summary>
///<paramname="sql"></param>
///<paramname="savePath"></param>
///<paramname="tableName"></param>
///<paramname="maxRecordCount"></param>
[Microsoft.SqlServer.Server.SqlProcedure]
publicstaticvoidBulkCopyToXls(SqlStringsql,SqlStringsavePath,SqlStringtableName,SqlInt32maxRecordCount)
{
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>
///<paramname="sql"></param>
///<paramname="savePath"></param>
///<paramname="tableName"></param>
///<paramname="maxRecordCount"></param>
privatestaticvoidExportXls(stringsql,stringsavePath,stringtableName,System.UInt16maxRecordCount)
{
if(System.IO.Directory.Exists(savePath)==false)
{
System.IO.Directory.CreateDirectory(savePath);
}
using(SqlConnectionconn=newSqlConnection("contextconnection=true"))
{
conn.Open();
using(SqlCommandcommand=conn.CreateCommand())
{
command.CommandText=sql;
using(SqlDataReaderreader=command.ExecuteReader())
{
inti=0;
inttotalCount=0;
inttick=System.Environment.TickCount;
SqlContext.Pipe.Send("开始导出数据");
while(true)
{
stringfileName=string.Format(@"{0}/{1}.{2}.xls",savePath,tableName,i++);
intiExp=Write(reader,maxRecordCount,fileName);
longsize=newSystem.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>
///<paramname="writer"></param>
///<paramname="obj"></param>
///<paramname="x"></param>
///<paramname="y"></param>
privatestaticvoidWriteObject(ExcelWriterwriter,objectobj,System.UInt16x,System.UInt16y)
{
stringtype=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>
///<paramname="reader"></param>
///<paramname="count"></param>
///<paramname="fileName"></param>
///<returns></returns>
privatestaticintWrite(SqlDataReaderreader,System.UInt16count,stringfileName)
{
intiExp=count;
ExcelWriterwriter=newExcelWriter(fileName);
writer.BeginWrite();
for(System.UInt16j=0;j<reader.FieldCount;j++)
{
writer.WriteString(0,j,reader.GetName(j));
}
for(System.UInt16i=1;i<=count;i++)
{
if(reader.Read()==false)
{
iExp=i-1;
break;
}
for(System.UInt16j=0;j<reader.FieldCount;j++)
{
WriteObject(writer,reader.GetSqlValue(j),i,j);
}
}
writer.EndWrite();
returniExp;
}
///<summary>
///写excel的对象
///</summary>
publicclassExcelWriter
{
System.IO.FileStream_wirter;
publicExcelWriter(stringstrPath)
{
_wirter=newSystem.IO.FileStream(strPath,System.IO.FileMode.OpenOrCreate);
}
///<summary>
///写入short数组
///</summary>
///<paramname="values"></param>
privatevoid_writeFile(System.UInt16[]values)
{
foreach(System.UInt16vinvalues)
{
byte[]b=System.BitConverter.GetBytes(v);
_wirter.Write(b,0,b.Length);
}
}
///<summary>
///写文件头
///</summary>
publicvoidBeginWrite()
{
_writeFile(newSystem.UInt16[]{0x809,8,0,0x10,0,0});
}
///<summary>
///写文件尾
///</summary>
publicvoidEndWrite()
{
_writeFile(newSystem.UInt16[]{0xa,0});
_wirter.Close();
}
///<summary>
///写一个数字到单元格x,y
///</summary>
///<paramname="x"></param>
///<paramname="y"></param>
///<paramname="value"></param>
publicvoidWriteNumber(System.UInt16x,System.UInt16y,doublevalue)
{
_writeFile(newSystem.UInt16[]{0x203,14,x,y,0});
byte[]b=System.BitConverter.GetBytes(value);
_wirter.Write(b,0,b.Length);
}
///<summary>
///写一个字符到单元格x,y
///</summary>
///<paramname="x"></param>
///<paramname="y"></param>
///<paramname="value"></param>
publicvoidWriteString(System.UInt16x,System.UInt16y,stringvalue)
{
byte[]b=System.Text.Encoding.Default.GetBytes(value);
_writeFile(newSystem.UInt16[]{0x204,(System.UInt16)(b.Length+8),x,y,0,(System.UInt16)b.Length});
_wirter.Write(b,0,b.Length);
}
}
};
--------------------------------------------------------------------------------
把上面代码编译为TestExcel.dll，copy到服务器目录。然后通过如下SQL语句部署存储过程。
--------------------------------------------------------------------------------
CREATEASSEMBLYTestExcelForSQLCLRFROM"d:/sqlclr/TestExcel.dll"WITHPERMISSION_SET=UnSAFE;
--
go
CREATEprocdbo.BulkCopyToXls
(
@sqlnvarchar(max),
@savePathnvarchar(1000),
@tableNamenvarchar(1000),
@bathCountint
)
ASEXTERNALNAMETestExcelForSQLCLR.StoredProcedures.BulkCopyToXls
go
--------------------------------------------------------------------------------
当这项技术掌握在我们自己手中的时候，就可以随心所欲的来根据自己的需求定制。比如，我可以不要根据序号来分批写入excel，而是根据某个字段的值（比如一个表有200个城市的8万条记录）来划分为n个文件，而这个修改只要调整一下DataReader的循环里面的代码就行了。
相关文章
- 【2022新书】可扩展系统的基础设计：分布式架构
- 「Python」面向对象封装案例1——小夏爱跑步、案例扩展
- Linux lvextend命令：扩展逻辑卷空间
- 测试之路 pytest接口自动化框架扩展-思路梳理+成果展示
- PHP ImageMagick扩展有哪些函数？PHP ImageMagick扩展的使用方法
- 【Spring源码】- 08 扩展点之mybatis集成
- Linux 格式化扩展分区(Extended)详解程序员
- 横向扩展MySQL 分库技术实现横向扩展（mysql分库实现）
- 学习Linux：用视频扩展知识边界（linux就该这么学视频）
- Chrome增强安全属性 使其更容易发现可疑下载和扩展
- Redis数据库：高性能、可扩展的NoSQL数据库（redis数据库特点）
- Redis群集实现快速可扩展的构建（redis群构建）
- Rokid发布消费级MR眼镜，支持手机扩展、双屏异显、6DoF
- mysql数据库应付大流量网站的的3种架构扩展方式介绍
- windows2008R264位服务器中开启phpcurl扩展的方法