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
 */