bcp生成excel文件优化方案

来源：岁月联盟编辑：zhuzhu 时间：2010-04-05

　　一、综述：

　　目前页面生成excel的方法很多，总结起来，不外乎两类，一种是使用excel对象，一种是“伪文件”。两种方法

　　是各自有各自的优缺点，在不同的领域也都有很多成功的案例。前者使用对象的方式很灵活，可以生成任意表现

　　方式的excel文件，缺点也很明显，比如在asp下，使用excel如果发生异常，excel对象的资源是不会释放的，也

　　就是说在特殊情况下会把服务器“拖死”。后者的方式一般使用的是html文件，但是后缀是xls,也就是“伪文件

　　”,这样的操作在生成excel文件的时候，对比第一种方法系统开销比较小，但是由于是“伪文件”，在打开文件

　　的时候会有提示，但是由于生成html的方法很多，也是目前在我们系统中采用比较多的方式，另外他生成复杂样

　　式的时候也比较方便，可以采用tr td的方式加上style.

　　二、方案说明

　　下面我要说的这种方式，是目前能找到的最快的生成excel文件的方式，姑且叫做“bcp生成csv”方法吧。它有

　　以下几个适用的范围。

　　1.生成的excel格式比较单一，没有合并列等情况

　　2.生成的文件格式为csv，但是可以用excel默认打开

　　3.执行的存储过程用户需要xp_cmdshell权限

　　接下来，说说它的好处：

　　1.生成效率很高，由于是并发操作，每1000条数据传送一次

　　2.生成的文件没有冗余代码，全部为数据信息，保证了文件是所有类型中最小的

　　3.没有office2003中的excel的单sheet的6万多行的限制，就算输出10万条数据也能正常生成，但是用excel2003

　　打开失败，用excel2007打开正常

　　但是由于使用上的不方便，我就写了一个存储过程，只需要传递几个参数进去，就能自动生成对应的excel文件

　　。先贴上代码：

　　三、代码

　1USE [student] 　2GO 　3/**//****** 对象:　StoredProcedure [dbo].[proc_2csv]　　脚本日期: 12/30/2008 12:01:17 ******/ 　4SET ANSI_NULLS ON 　5GO 　6SET QUOTED_IDENTIFIER ON 　7GO 　8/**//************************************************************** 　9/************* copyright by James.wang(天生我豺)*************** 10/************* 欢迎转载，转载请注明原作者********************** 11/************* email:ec0312@163.com **************************/ 12 13create PROCEDURE [dbo].[proc_2csv] 14( 15　　--参数声明 16　　@sql1 varchar(4000)='',--from之前的SQL语句 17　　@sql2 varchar(4000)='',--from之后的SQL语句 18　　@columneName varchar(4000)=''--显示的列名，用英文,分割 19 20) 21 22AS 23BEGIN 24　　Set NOCOUNT ON 25　　Declare @ErrNum int, 26　　　　　　@tablename varchar(200), 27　　　　　　@ErrInfo varchar(400), 28 29　　　　　　@outfilename varchar(200), 30　　　　　　@tmpsql varchar(8000), 31　　　　　　@cursql varchar(8000), 32　　　　　　@csv varchar(8000) 33　　set @tablename='student.dbo.[tmp_'+Convert(varchar(50),newID())+']' 34 35 36　　　　　set @tmpsql=@sql1+' into '+ @tablename + ' '+@sql2 37　　　　　 38　　　　　exec (@tmpsql) 39　　　　　--print @tmpsql 40　　　　　if @@ERROR<>0 41　　　　　　 begin 42　　　　　　　　select @ErrNum=50001,@ErrInfo='生成物理表错误' 43　　　　　　　　goto On_Error 44　　　　　　 end 45 46　　set @tmpsql='' 47　　set @cursql='' 48　　set @outfilename=right(@tablename,len(@tablename)-12) 49　　 50　　/**//*column替换*/ 51　　set @columneName=replace(@columneName,',',''''',''''') 52　　set @columneName=''''''+@columneName+'''''' 53 54　　/**//*组合输出字符*/ 55　　Declare @curColName varchar(20) 56　　Declare currentcur cursor for 57　　select t2.name from sysobjects t1,syscolumns t2 where t1.id=t2.id and t1.xtype='U' and 58 59t1.id=object_id(@outfilename) 60　　Open currentcur 61　　FETCH NEXT From currentcur into @curColName 62 63　　WHILE @@FETCH_STATUS = 0 64　　　　BEGIN 65　　　　　　set @cursql=@cursql+'''''　　''''+'+@curColName+',' 66　　　　　　FETCH NEXT From currentcur into @curColName 67　　　　END 68　　CLOSE currentcur 69　　DEALLOCATE currentcur 70　　set @cursql=left(@cursql,len(@cursql)-1) 71　　set @tmpsql='select '+@columneName+' union all select '+@cursql+' from '+@tablename 72　　--print @tmpsql 73　　 74　　/**//*导出数据到csv*/ 75　　set @csv='master..xp_cmdshell ''bcp "'+ @tmpsql +'"　queryout　 76 77d:edufewebdufenew'+@outfilename+'.csv　-c　 -t","　 -r"n" -S"172.16.4.*"　 -U"sa"　 - 78 79P"password" '' ' 80　 81　　--print @csv 82　　exec(@csv) 83　　 84　　/**//*删除临时表*/ 85　　if exists(select 1 from　[dbo].[sysobjects] where　id = object_id(@tablename) and　type = 'U') 86　　　　begin 87　　　　 set @tmpsql='drop table '+@tablename 88　　　　　exec(@tmpsql) 89　　　　　if @@ERROR<>0 90　　　　　　　 begin 91　　　　　　　　select @ErrNum=50002,@ErrInfo='删除物理表错误' 92　　　　　　　　goto On_Error 93　　　　　　 end 94　　　 end　　 95 96　　Set NOCOUNT OFF 97　　　　select @outfilename 98　　Set NOCOUNT ON 99　　return 100 101On_Error: 102　 if exists(select 1 from　[dbo].[sysobjects] where　id = object_id(@tablename) and　type = 'U') 103　　　 begin 104　　　　 set @tmpsql='drop table '+@tablename 105　　　　 exec(@tmpsql) 106　　　 end 107　　raiserror @ErrNum @ErrInfo 108　　Return 109END

　　四、代码说明

　　1.@sql1：传入sql语句中的from的前面的语句

　　2.@sql2：传入sql语句中的from的后面的语句，包括from

　　3.@columneName:传入显示的列标题，用英文的逗号分割

　　4.例子：

/*测试 [proc_2csv] 'select top 1000 userid,cardname,cardid,studentname,case sex when ''1'' then ''男'' 　　 else ''女'' end sex','from registersys','用户名,证件类型,证件号码,姓名,性别' */

　　五、补充说明：

　　1.如果传入的sql语句中有单引号，在传入之前替换成两个单引号

　　2.如果传入的sql语句有英文的逗号，替换成全角的逗号

　　3.注意master..xp_cmdshell代码中的172.16.4.*替换成你机器ip,后面替换成对应的帐户和密码，注意这个

　　帐户必须有xp_cmdshell的权限

　　六、引申：

　　很多人会说用sa不安全，用xp_cmdshell不安全，确实是这样，但是我们可以采用临时授予当前用户执行系统

　　扩展存储过程权限，这个方面我也正在学习，如果大家有这方面的想法可以一起探讨。

　　以下是我找到的一些资源：

　　1.如何在不提升用户权限的情况下，使普通用户执行xp_cmdshell存储过程

　　http://blog.csdn.net/puddingpudding/archive/2008/12/04/3445833.aspx

　　2.重新设置代理和 SQLAgentCmdExec 帐户