SQLServer數(shù)據(jù)導(dǎo)出到excel有很多種方法，比如dts、ssis、還可以用sql語句調(diào)用openrowset。我們這里開拓思路，用CLR來生成Excel文件，并且會考慮一些方便操作的細節(jié)。
下面我先演示一下我實現(xiàn)的效果，先看測試語句
--------------------------------------------------------------------------------
復(fù)制代碼 代碼如下:
exec BulkCopyToXls 'select * from testTable' , 'd:/test' , 'testTable' ,- 1
/*
開始導(dǎo)出數(shù)據(jù)
文件 d:/test/testTable.0.xls, 共65534條 , 大小20 ,450,868 字節(jié)
文件 d:/test/testTable.1.xls, 共65534條 , 大小 20 ,101,773 字節(jié)
文件 d:/test/testTable.2.xls, 共65534條 , 大小 20 ,040,589 字節(jié)
文件 d:/test/testTable.3.xls, 共65534條 , 大小 19 ,948,925 字節(jié)
文件 d:/test/testTable.4.xls, 共65534條 , 大小 20 ,080,974 字節(jié)
文件 d:/test/testTable.5.xls, 共65534條 , 大小 20 ,056,737 字節(jié)
文件 d:/test/testTable.6.xls, 共65534條 , 大小 20 ,590,933 字節(jié)
文件 d:/test/testTable.7.xls, 共26002條 , 大小 8,419,533 字節(jié)
導(dǎo)出數(shù)據(jù)完成
-------
共484740條數(shù)據(jù)，耗時 23812ms
*/
--------------------------------------------------------------------------------
上面的BulkCopyToXls存儲過程是自定的CLR存儲過程。他有四個參數(shù)：
第一個是sql語句用來獲取數(shù)據(jù)集
第二個是文件保存的路徑
第三個是結(jié)果集的名字，我們用它來給文件命名
第四個是限制單個文件可以保存多少條記錄,小于等于0表示最多65534條。
前三個參數(shù)沒有什么特別，最后一個參數(shù)的設(shè)置可以讓一個數(shù)據(jù)集分多個excel文件保存。比如傳統(tǒng)excel的最大容量是65535條數(shù)據(jù)。我們這里參數(shù)設(shè)置為-1就表示導(dǎo)出達到這個數(shù)字之后自動寫下一個文件。如果你設(shè)置了比如100，那么每導(dǎo)出100條就會自動寫下一個文件。
另外每個文件都可以輸出字段名作為表頭，所以單個文件最多容納65534條數(shù)據(jù)。
用微軟公開的biff8格式通過二進制流生成excel，服務(wù)器無需安裝excel組件，而且性能上不會比sql自帶的功能差，48萬多條數(shù)據(jù)，150M，用了24秒完成。
下面我們來看下CLR代碼。通過sql語句獲取DataReader，然后分批用biff格式來寫xls文件。
--------------------------------------------------------------------------------
復(fù)制代碼 代碼如下:
using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
public partial class StoredProcedures
{
/// summary>
/// 導(dǎo)出數(shù)據(jù)
/// /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>
/// 查詢數(shù)據(jù)，生成文件
/// /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(" 開始導(dǎo)出數(shù)據(jù)" );
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} 字節(jié)" , fileName, iExp, size.ToString("###,###" )));
if (iExp maxRecordCount) break ;
}
tick = System.Environment .TickCount - tick;
SqlContext .Pipe.Send(" 導(dǎo)出數(shù)據(jù)完成" );
SqlContext .Pipe.Send("-------" );
SqlContext .Pipe.Send(string .Format(" 共{0} 條數(shù)據(jù)，耗時{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>
/// 寫一批數(shù)據(jù)到一個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 數(shù)組
/// /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>
/// 寫一個數(shù)字到單元格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到服務(wù)器目錄。然后通過如下SQL語句部署存儲過程。
--------------------------------------------------------------------------------
復(fù)制代碼 代碼如下:
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
--------------------------------------------------------------------------------
當這項技術(shù)掌握在我們自己手中的時候，就可以隨心所欲的來根據(jù)自己的需求定制。比如，我可以不要根據(jù)序號來分批寫入excel，而是根據(jù)某個字段的值（比如一個表有200個城市的8萬條記錄）來劃分為n個文件，而這個修改只要調(diào)整一下DataReader的循環(huán)里面的代碼就行了。
