• 企业400电话
  • 微网小程序
  • AI电话机器人
  • 电商代运营
  • 全 部 栏 目

    企业400电话 网络优化推广 AI电话机器人 呼叫中心 网站建设 商标✡知产 微网小程序 电商运营 彩铃•短信 增值拓展业务
    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 .MaxValuemaxRecordCount.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的循环里面的代码就行了。
    上一篇:SQL2005CLR函数扩展-解析天气服务的实现
    下一篇:SQL2005CLR函数扩展-深入环比计算的详解
  • 相关文章
  • 

    © 2016-2020 巨人网络通讯 版权所有

    《增值电信业务经营许可证》 苏ICP备15040257号-8

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