本文来自CSDN博客，转载请标明出处：http://blog.csdn.net/jinjazz/archive/2009/04/29/4138000.aspx...

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 .MaxValue&&maxRecordCount.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);

        }

    }

};

====================================以上编译成DLL=================================

 

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

=========================如果不能执行可能是级别不够，提升数据库级别==========================

exec BulkCopyToXls 'select * from testTable' , 'd:\test' , 'testTable' ,- 1

===================================以上是执行实例==================================

转载于:https://www.cnblogs.com/niubo/archive/2010/01/28/1658135.html