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