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

203 阅读 0 评论 134 点赞

我是靠谱客的博主超帅小懒猪，最近开发中收集的这篇文章主要介绍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的循环里面的代码就行了。