SQL通过bcp导出数据到excel文件

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

/*
 将查询结果集直接导出到excel文件中(包含表头,别名不能包含'(')，通过SQLServer数据库内置对象提高数据导出速率(事理 2011.5) 
 exec proc_ExportDataToExcel '.\SQL2005','OA','asdfef85','','select  top 10* from SL_User where UserId<500','D:/1.xls'
 判断xp_cmdshell存储过程是否存在select count(*) from master.dbo.sysobjects where xtype='X' and name='xp_cmdshell'
 注意：使用此存储过程，数据库登录用户要有sysadmin权限，需要创建表AppLock管理xp_cmdshell的开启与关闭
    create table AppLock (Id int not null identity(1,1) primary key,SessionCount int not null,Name varchar(50) not null)
    insert into AppLock values(0,'proc_ExportDataToExcel')
*/
create PROC [dbo].[proc_ExportDataToExcel]
(
    @server nvarchar(50),--数据库服务名称
    @database nvarchar(50),--数据库名称
    @uid nvarchar(50),--数据库登录用户名
    @pwd varchar(50),--数据库登录密码
    @selectSQL varchar(7000),--查询语句
    @fileSavePath nvarchar(500)--excel文件存放目录如，D:/1.xls
)
AS 
BEGIN       
    declare @errorSum int --记录错误标志   
    declare @sql varchar(8000)
    declare @tableName varchar(55)--随机临时表名称
    declare @tempTableName varchar(55)     
    set @errorSum = 1

    --生成随机名称，防止多个人同时导出数据问题               
    select @tableName = replace('##ExportDataToExcel'+Convert(varchar(36),newid()),'-','')
    set @tempTableName=@tableName+'Temp'
    
    --拼接复制表结构的sql语句
    declare @tempSQL varchar(7000)
    --判断第一个select后面是否有top
    declare @hasTop varchar(10)
    declare @index int
    set @index=charindex(' ',@selectSQL)
    set @hasTop=lower(ltrim(substring(@selectSQL,@index+1,10)))
    set @hasTop=substring(@hasTop,0,4)
    if(@hastop='top ')
        begin
            --将其它top换成top 0
            set @tempSQL=substring(@selectSQL,12,len(@selectSQL)-11)--截取"select top "之后字符串
            set @index=patindex('%[0-9][^0-9]%', @tempSQL)--查询top后最后一个数字位置
            set @tempSQL='select top 0 '+substring(@tempSQL,@index+1,len(@tempSQL)-@index)    
        end
    else
        begin
            --在第一个select后面加上top 1
            set @tempSQL='select top 0 '+substring(@selectSQL,8,len(@selectSQL)-7)
        end

    --通过查询语句创建用于复制表结构的空临时表
    begin try
        set @sql='select * into '+@tempTableName+' from ('+@tempSQL+') as temp where 1=0'        
        exec (@sql)
        set @errorSum = @errorSum+1        
    end try
    begin catch
        raiserror('创建复制表结构的空临时表失败！',16,1)
        return @errorSum
    end catch;

    --查询表结构
    declare @columnName nvarchar(4000)
    declare @columnName2 nvarchar(4000)
    select @columnName=isnull(@columnName+',','')+''''+SC.name+'''',@columnName2=
    case when ST.name in('text','ntext') then isnull(@columnName2+',','')+SC.name
         when ST.name in('char','varchar') then isnull(@columnName2+',','')+'cast('+SC.name+' as varchar('+cast((case when SC.length<255 then 255 else SC.length end) as varchar)+')) '+SC.name         
         when ST.name in('nchar','nvarchar') then isnull(@columnName2+',','')+'cast('+SC.name+' as nvarchar('+cast((case when SC.length<255 then 255 else SC.length end) as varchar)+')) '+SC.name          
         else isnull(@columnName2+',','')+'cast('+SC.name+' as varchar(1000)) '+SC.name end
    from tempdb..sysobjects SO,tempdb..syscolumns SC,tempdb..systypes ST 
    where SO.id=SC.id and SO.xtype='U' and SO.status>=0 and SC.xtype=ST.xusertype and SO.name=@tempTableName
    and ST.name not in('image','sql_variant','varbinary','binary')
    order by SC.colorder
    
    declare @dropTableSql varchar(200)
    begin try        
        --创建全字符串类型的空临时表
        set @sql='select * into '+@tableName+' from (select '+@columnName2+' from '+@tempTableName+' where 1=0) as temp'
        exec (@sql)

        --删除临时空临时表
        set @dropTableSql='if exists(select * from tempdb..sysobjects where name='''+@tempTableName+''') drop table '+@tempTableName
        exec (@dropTableSql)    
        
        --插入列名(表头)
        set @sql='insert into '+@tableName+' values('+@columnName+')'
        exec (@sql)    
        
        --插入数据到临时表
        set @sql='insert into '+@tableName+' select * from ('+@selectSQL+') as temp'
        exec (@sql)    
        set @errorSum = @errorSum+1            
    end try
    begin catch
        raiserror('创建数据临时表或往临时表中插入数据失败！',16,1)
        exec (@dropTableSql)
        return @errorSum
    end catch

    --删除数据临时表
    set @dropTableSql='if exists(select * from tempdb..sysobjects where name='''+@tableName+''') drop table '+@tableName
    --导出数据
    begin try
        declare @sessionCount int
        select @sessionCount=SessionCount from AppLock where [Name]='proc_ExportDataToExcel'
        if @sessionCount=0
        begin
            /*开启xp_cmdshell,数据库登录用户要有sysadmin权限*/        
            begin try            
                EXEC sp_configure 'show advanced options', 1
                RECONFIGURE
                EXEC sp_configure 'xp_cmdshell', 1
                RECONFIGURE        
                EXEC sp_configure 'show advanced options', 0
                RECONFIGURE
            end try
            begin catch
            end catch;
        end

        --更新一个表时，默认有排他锁
        update AppLock set SessionCount=SessionCount+1 where [Name]='proc_ExportDataToExcel'
        set @sql='master..xp_cmdshell ''bcp "select * from '+@database+'.dbo.'+@tableName+'" queryout "'+@fileSavePath+'" -c -S"'+@server+'" -U"'+@uid+'" -P"'+@pwd+'"'''        
        exec (@sql)
        update AppLock set SessionCount=SessionCount-1 where [Name]='proc_ExportDataToExcel'
        set @errorSum = @errorSum+1

        declare @sessionCount2 int
        select @sessionCount2=SessionCount from AppLock where [Name]='proc_ExportDataToExcel'
        if @sessionCount2=0
        begin
            /*关闭xp_cmdshell，加锁使用才能不造成冲突*/
            begin try            
                EXEC sp_configure 'show advanced options', 1
                RECONFIGURE
                EXEC sp_configure 'xp_cmdshell', 0
                RECONFIGURE    
                EXEC sp_configure 'show advanced options', 0
                RECONFIGURE
            end try
            begin catch
            end catch;
        end
    end try
    begin catch  
        exec (@dropTableSql)
        declare @errorMsg nvarchar(4000)
        set @errorMsg=ERROR_MESSAGE()  
        if(@errorMsg is not null)
            raiserror(@errorMsg,16,1)
        return @errorSum
    end catch;
        
    exec (@dropTableSql)  --删除数据临时表      
    return @errorSum
END

//调用

        /// <summary>
        /// 导出数据到excel文件中
        /// </summary>
        /// <param name="selectSQL">select查询sql语句</param>
        /// <param name="fileSavePath">文件保存路径</param>
        /// <returns>返回消息</returns>
        public static void ExportDataToExcel(string selectSQL, string fileSavePath)
        {
            string connectionString = SQLHelper.ConnectionString;
            //获取连接字符串数据库服务器、数据库名称、用户名和密码
            Dictionary<string, string> dic = new Dictionary<string, string>();
            string[] tempArray = connectionString.Split(new char[] { ';' }, StringSplitOptions.RemoveEmptyEntries);
            foreach (string str in tempArray)
            {
                string[] temp = str.Split('=');
                dic.Add(temp[0].Trim().ToLower(), temp[1].Trim());
            }

            string server = dic.Keys.Contains("data source") ? dic["data source"] : dic["server"];
            string database = dic.Keys.Contains("initial catalog") ? dic["initial catalog"] : dic["database"];
            string pwd = string.Empty;
            if (dic.Keys.Contains("password"))
                pwd = dic["password"];
            else if (dic.Keys.Contains("pwd"))
                pwd = dic["pwd"];
            string userID = string.Empty;
            if (dic.Keys.Contains("user id"))
                userID = dic["user id"];

            SQLHelper helper = new SQLHelper();
            helper.InitStoredProcedure("[proc_ExportDataToExcel]");
            helper.AddParameter("@server", server);
            helper.AddParameter("@database", database);
            helper.AddParameter("@uid", userID);
            helper.AddParameter("@pwd", pwd);
            helper.AddParameter("@selectSQL", selectSQL);
            helper.AddParameter("@fileSavePath", fileSavePath);
            helper.AddReturnParameter("@errorSum");
            try
            {
                helper.ExecuteNonQuery();
                int result = (int)helper.GetValue("@errorSum");
                if (result == 1)
                    throw new Exception("创建复制表结构的空临时表失败!");
                else if (result == 2)
                    throw new Exception("创建临时表或往临时表中插入数据失败!");
                else if (result == 3)
                    throw new Exception("导出数据失败!");
                else if (result == 4)////"导出成功!";
                {
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

第二种

/*有存在1.xls文件，并且设置好了表头
 INSERT INTO OPENDATASOURCE('Microsoft.JET.OLEDB.4.0',   
 'Extended Properties=Excel 8.0;Data source=d:\1.xls')...[sheet1$]   
 select 销售数量,销售金额,零售金额,专柜代码,供应商品名称,成本,毛利额,毛利率 from (select sum( quantity )as 销售数量 ,sum( salessum ) as 销售金额,sum( salessum +rebatesum ) as 零售金额,d_product_sale_list.shoppeno as 专柜代码 ,'('+isnull(A_TradeMember.memcode,0) + ')'+isnull(A_TradeMember.memname,0) as 供应商品名称, sum(isnull(cost,0)+isnull(costtax,0)) as 成本,sum(salessum)-sum( isnull(cost,0) +isnull(costtax,0) ) as 毛利额,case  when sum(salessum)<>0 then (sum(salessum)-sum( isnull(cost,0) +isnull(costtax,0) ))/sum(salessum)else 0 end  as 毛利率  from (SELECT  * FROM d_product_sale_list UNION ALL SELECT  * FROM d_product_sale_list20114) d_product_sale_list , A_TradeMember,a_protocol  where shoppeno is not null and a_protocol.mem_id = A_TradeMember.mem_id  and saledate>='2011-4-01'  and  saledate<='2011/4/22 0:00:00'  group by d_product_sale_list.shoppeno, A_TradeMember.memname,A_TradeMember.memcode) a
 */

 

转载于:https://www.cnblogs.com/slyzly/articles/2657424.html