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

(0)

相关推荐

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

    SQLServer数据导出到excel有很多种方法,比如dts.ssis.还可以用sql语句调用openrowset.我们这里开拓思路,用CLR来生成Excel文件,并且会考虑一些方便操作的细节. 下面我先演示一下我实现的效果,先看测试语句--------------------------------------------------------------------------------exec BulkCopyToXls 'select * from testTable' , 'd:

  • 公共POI导出Excel方法详解

    最早开始的时候做过一些数据Excel导出的功能,但是到后期每一次导出都需要写一些差不多类似的代码,稍微研究了一下写了个公共的导出方法. 这里用的是POI,然后写成了一个公共类,传入设置好格式的数据,就能弹出下载框. (补充下getResponse的方法,之前没注意这个有继承!) package com.hwt.glmf.common; import java.io.IOException; import java.io.OutputStream; import java.util.ArrayLi

  • 使用纯前端JavaScript实现Excel导入导出方法过程详解

    公司最近要为某国企做一个**统计和管理系统, 具体要求包含 Excel导入导出根据导入的数据进行展示报表图表展示(包括柱状图,折线图,饼图),而且还要求要有动画效果,扁平化风格Excel导出,并要提供客户端来管理Excel 文件... 要求真多! 现在总算是完成了,于是将我的经验分析出来. 在整个项目架构中,首先就要解决Excel导入的问题. 由于公司没有自己的框架做Excel IO,就只有通过其他渠道了. 嗯,我在github上找到了一个开源库xlsx,通过npm方式来安装. npm inst

  • Pandas数据结构中Series属性详解

    目录 Series属性 Series属性列表 Series属性详解 Series属性 Series属性列表 属性 说明 Series.index 系列的索引(轴标签) Series.array 系列或索引的数据 Series.values 系列的数据,返回ndarray Series.dtype 返回基础数据的数据类型 Series.shape 返回基础数据形状的元组 Series.nbytes 返回基础数据占的字节数 Series.ndim 基础数据的维数,永远是1 Series.size 返

  • SpringBoot+Vue实现EasyPOI导入导出的方法详解

    目录 前言 一.为什么做导入导出 二.什么是 EasyPOI 三.项目简介 项目需求 效果图 开发环境 四.实战开发 核心源码 前端页面 后端核心实现 五.项目源码 小结 前言 Hello~ ,前后端分离系列和大家见面了,秉着能够学到知识,学会知识,学懂知识的理念去学习,深入理解技术! 项目开发过程中,很大的需求都有 导入导出功能,我们依照此功能,来实现并还原真实企业开发中的实现思路 一.为什么做导入导出 为什么做导入导出 导入 在项目开发过程中,总会有一些统一的操作,例如插入数据,系统支持单个

  • mysql数据存储过程参数实例详解

    MySQL 存储过程参数有三种类型:in.out.inout.它们各有什么作用和特点呢? 一.MySQL 存储过程参数(in) MySQL 存储过程 "in" 参数:跟 C 语言的函数参数的值传递类似, MySQL 存储过程内部可能会修改此参数,但对 in 类型参数的修改,对调用者(caller)来说是不可见的(not visible). drop procedure if exists pr_param_in; create procedure pr_param_in ( in id

  • PHP中加速、缓存扩展的区别和作用详解(eAccelerator、memcached、xcache、APC )

    PHP中有eAccelerator.memcached.xcache.APC 4个加速.缓存扩展,下面给大家介绍下其区别,一起看看吧! 折腾VPS的朋友,在安装好LNMP等Web运行环境后都会选择一些缓存扩展安装以提高PHP运行速度,常被人介绍的有 eAccelerator.memcached.xcache.Alternative PHP Cache这几个缓存扩展,它们之间有什么区别?分别的作用又是什么?我们如何选择?这是本文给于大家的答案. 1.eAccelerator eAccelerato

  • C++ 中动态链接库--导入和导出的实例详解

    C++ 中动态链接库--导入和导出的实例详解 __declspec(dllexport)和__declspec(dllimport): __declspec(dllexport):编译器看到一个变量.函数或者C++类被它修饰,那么它就知道应该在生成的DLL 模块中导出该变量.函数或C++类. __declspec(dllimport):编译器看到一个变量.函数或者C++类被它修饰,那么它就知道可执行文件或DLL的源文件需要从其它DLL模块中导入一些变量和函数. DLL的导入段: 构建可执行模块时

  • IOS 数据库升级数据迁移的实例详解

    IOS 数据库升级数据迁移的实例详解 概要: 很久以前就遇到过数据库版本升级的引用场景,当时的做法是简单的删除旧的数据库文件,重建数据库和表结构,这种暴力升级的方式会导致旧的数据的丢失,现在看来这并不不是一个优雅的解决方案,现在一个新的项目中又使用到了数据库,我不得不重新考虑这个问题,我希望用一种比较优雅的方式去解决这个问题,以后我们还会遇到类似的场景,我们都想做的更好不是吗? 理想的情况是:数据库升级,表结构.主键和约束有变化,新的表结构建立之后会自动的从旧的表检索数据,相同的字段进行映射迁移

  • Oracle数据操作和控制语言详解

    正在看的ORACLE教程是:Oracle数据操作和控制语言详解.SQL语言共分为四大类:数据查询语言DQL,数据操纵语言DML, 数据定义语言DDL,数据控制语言DCL.其中用于定义数据的结构,比如 创建.修改或者删除数据库:DCL用于定义数据库用户的权限:在这篇文章中我将详细讲述这两种语言在Oracle中的使用方法. DML语言 DML是SQL的一个子集,主要用于修改数据,下表列出了ORACLE支持的DML语句. 插入数据 INSERT语句常常用于向表中插入行,行中可以有特殊数据字段,或者可以

随机推荐