using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
public partial class StoredProcedures
{
///
/// 导出数据
///
///
///
///
///
[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);
}
///
/// 查询数据，生成文件
///
///
///
///
///
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));
}
}
}
}
///
/// 写单元格
///
///
///
///
///
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 ;
}
}
///
/// 写一批数据到一个excel 文件
///
///
///
///
///
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 数组
///
///
private void _writeFile(System.UInt16 [] values)
{
foreach (System.UInt16 v in values)
{
byte [] b=System.BitConverter .GetBytes(v);
_wirter.Write(b, 0, b.Length);
}
}
///
/// 写文件头
///
public void BeginWrite()
{
_writeFile(new System.UInt16 [] { 0x809, 8, 0, 0x10, 0, 0 });
}
///
/// 写文件尾
///
public void EndWrite()
{
_writeFile(new System.UInt16 [] { 0xa, 0 });
_wirter.Close();
}
///
/// 写一个数字到单元格x,y
///
///
///
///
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);
}
///
/// 写一个字符到单元格x,y
///
///
///
///
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);
}
}
};
相关文章
