BulkCopyToXls

using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;

public partial class myProcedure
{
    [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(" 输入信息不完整！");
        }

        //每个excel文件最大容纳65534
        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;
        }
    }
   
    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;
    }

   
    public class ExcelWriter
    {
        System.IO.FileStream _wirter;
        //创建文件
        public ExcelWriter(string strPath)
        {
            _wirter = new System.IO.FileStream(strPath, System.IO.FileMode.OpenOrCreate);
        }
        
        //写数组
        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();
        }
       //写数字到单元格
        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);
        }
       //写字符到单元格
        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);
        }
    }
};