<div class="codebody" id="code28607">
using System;
using System.Data;
using System.Data.sqlClient;
using System.Data.sqlTypes;
using Microsoft.sqlServer.Server; public partial class StoredProcedures
{
///

/// 导出数据
///

/// <param name="sql">
/// <param name="savePath">
/// <param name="tableName">
/// <param name="maxRecordCount">
[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);
} ///

/// 查询数据，生成文件
///

/// <param name="sql">
/// <param name="savePath">
/// <param name="tableName">
/// <param name="maxRecordCount">
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));
}
}
}
}
///

/// 写单元格
///

/// <param name="writer">
/// <param name="obj">
/// <param name="x">
/// <param name="y">
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,Convert .ToDouble(obj.ToString()));
break ;
default :
writer.WriteString(x,obj.ToString());
break ;
}
}
///

/// 写一批数据到一个excel 文件
///

/// <param name="reader">
/// <param name="count">
/// <param name="fileName">
///
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;
} ///

/// 写excel 的对象
///

public class ExcelWriter
{
System.IO.FileStream _wirter;
public ExcelWriter(string strPath)
{
_wirter = new System.IO.FileStream (strPath,System.IO.FileMode .OpenOrCreate);
}
///

/// 写入short 数组
///

/// <param name="values">
private void _writeFile(System.UInt16 [] values)
{
foreach (System.UInt16 v in values)
{
byte [] b = System.BitConverter .GetBytes(v);
_wirter.Write(b,b.Length);
}
}
///

/// 写文件头
///

public void BeginWrite()
{
_writeFile(new System.UInt16 [] { 0x809,8,0x10,0 });
}
///

/// 写文件尾
///

public void EndWrite()
{
_writeFile(new System.UInt16 [] { 0xa,0 });
_wirter.Close();
}
///

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

/// <param name="x">
/// <param name="y">
/// <param name="value">
public void WriteNumber(System.UInt16 x,System.UInt16 y,double value)
{
_writeFile(new System.UInt16 [] { 0x203,14,x,0 });
byte [] b = System.BitConverter .GetBytes(value);
_wirter.Write(b,b.Length);
}
///

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

/// <param name="x">
/// <param name="y">
/// <param name="value">
public void WriteString(System.UInt16 x,string value)
{
byte [] b = System.Text.Encoding .Default.GetBytes(value);
_writeFile(new System.UInt16 [] { 0x204,(System.UInt16 )(b.Length + 8),(System.UInt16 )b.Length });
_wirter.Write(b,b.Length);
}
}
};