using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.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 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>
    /// 查詢數據，生成文件
    /// /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(" 開始導出數據" );
                    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 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>
    /// 寫一批數據到一個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 數組
        /// /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>
        /// 寫一個數字到單元格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);
        }
    }
};

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