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