SQL2005CLR函数扩展-数据导出

SQLServer数据导出到excel有很多种方法，比如dts、ssis、还可以用sql语句调用openrowset。我们这里开拓思路，用CLR来生成Excel文件，并且会考虑一些方便操作的细节。

 

下面我先演示一下我实现的效果，先看测试语句

 

---

 

 

exec BulkCopyToXls '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文件。

 

 

---

 

 

usingSystem;

usingSystem.Data;

usingSystem.Data.SqlClient;

usingSystem.Data.SqlTypes;

usingMicrosoft.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 staticvoid BulkCopyToXls(SqlStringsql, SqlString savePath, SqlString tableName, 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>

   /// <param name="sql"></param>

   /// <param name="savePath"></param>

   /// <param name="tableName"></param>

   /// <param name="maxRecordCount"></param>

   private staticvoid ExportXls(stringsql, string savePath, stringtableName, System.UInt16 maxRecordCount)

   {

 

       if (System.IO.Directory.Exists(savePath)== false)

       {

            System.IO.Directory.CreateDirectory(savePath);

       }

 

       using (SqlConnectionconn = new SqlConnection("context connection=true"))

       {

            conn.Open();

            using(SqlCommand command = conn.CreateCommand())

            {

                command.CommandText = sql;

                using(SqlDataReader reader =command.ExecuteReader())

                {

                    inti = 0;

                    inttotalCount = 0;

                    inttick = 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 staticvoid WriteObject(ExcelWriterwriter, object obj, System.UInt16 x, System.UInt16y)

   {

       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 staticint Write(SqlDataReaderreader, System.UInt16 count, string fileName)

   {

       int iExp = count;

       ExcelWriter writer = new ExcelWriter(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.UInt16 j = 0; j < reader.FieldCount;j++)

            {

                WriteObject(writer,reader.GetSqlValue(j), i, j);

            }

       }

       writer.EndWrite();

       return iExp;

   }

  

   /// <summary>

   /// 写excel的对象

   /// </summary>

   public classExcelWriter

   {

       System.IO.FileStream _wirter;

       public ExcelWriter(string strPath)

       {

            _wirter = newSystem.IO.FileStream(strPath, System.IO.FileMode.OpenOrCreate);

       }

       /// <summary>

       /// 写入short数组

       /// </summary>

       /// <param name="values"></param>

       private void_writeFile(System.UInt16[] values)

       {

            foreach(System.UInt16 v invalues)

            {

                byte[]b = System.BitConverter.GetBytes(v);

                _wirter.Write(b, 0, b.Length);

            }

       }

       /// <summary>

       /// 写文件头

       /// </summary>

       public voidBeginWrite()

       {

            _writeFile(newSystem.UInt16[] { 0x809, 8, 0, 0x10, 0, 0 });

       }

       /// <summary>

       /// 写文件尾

       /// </summary>

       public voidEndWrite()

       {

            _writeFile(newSystem.UInt16[] { 0xa, 0 });

            _wirter.Close();

       }

       /// <summary>

       /// 写一个数字到单元格x,y

       /// </summary>

       /// <param name="x"></param>

       /// <param name="y"></param>

       /// <param name="value"></param>

       public voidWriteNumber(System.UInt16 x, System.UInt16 y, 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>

       /// <param name="x"></param>

       /// <param name="y"></param>

       /// <param name="value"></param>

       public voidWriteString(System.UInt16 x, System.UInt16 y, 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语句部署存储过程。

 

---

 

 

CREATE ASSEMBLYTestExcelForSQLCLR FROM 'd:/sqlclr/TestExcel.dll' WITHPERMISSION_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的循环里面的代码就行了。