展会信息港展会大全

SQL2005CLR函数扩展-数据导出的实现详解
来源:互联网   发布日期:2016-03-01 10:10:32   浏览:1759次  

导读: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的循环里面的代码就行了。

赞助本站

人工智能实验室

相关热词: 开发 编程 android

AiLab云推荐
展开

热门栏目HotCates

Copyright © 2010-2024 AiLab Team. 人工智能实验室 版权所有    关于我们 | 联系我们 | 广告服务 | 公司动态 | 免责声明 | 隐私条款 | 工作机会 | 展会港