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