数据库小知识:xcel文件优化方案

来源:发布时间:2009-01-15

一、综述:
  目前页面生成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
  24Set NOCOUNT ON
  25Declare @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)
  33set @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
  42select @ErrNum=50001,@ErrInfo=’生成物理表错误’
  43goto On_Error
  44 end
  45
  46set @tmpsql=’’
  47set @cursql=’’
  48set @outfilename=right(@tablename,len(@tablename)-12)
  49
  50/**//*column替换*/
  51set @columneName=replace(@columneName,’,’,’’’’’,’’’’’)
  52set @columneName=’’’’’’+@columneName+’’’’’’
  53
  54/**//*组合输出字符*/
  55Declare @curColName varchar(20)
  56Declare currentcur cursor for
  57select t2.name from sysobjects t1,syscolumns t2 where t1.id=t2.id and t1.xtype=’U’ and
  58
  59t1.id=object_id(@outfilename)
  60Open currentcur
  61FETCH NEXT From currentcur into @curColName
  62
  63WHILE @@FETCH_STATUS = 0
  64BEGIN
  65set @cursql=@cursql+’’’’’’’’’+’+@curColName+’,’
  66FETCH NEXT From currentcur into @curColName
  67END
  68CLOSE currentcur
  69DEALLOCATE currentcur
  70set @cursql=left(@cursql,len(@cursql)-1)
  71set @tmpsql=’select ’+@columneName+’ union all select ’+@cursql+’ from ’+@tablename
  72--print @tmpsql
  73
  74/**//*导出数据到csv*/
  75set @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
  82exec(@csv)
  83
  84/**//*删除临时表*/
  85if exists(select 1 from [dbo].[sysobjects] where id = object_id(@tablename) and type = ’U’)
  86begin
  87 set @tmpsql=’drop table ’+@tablename
  88 exec(@tmpsql)
  89 if @@ERROR<>0
  90  begin
  91select @ErrNum=50002,@ErrInfo=’删除物理表错误’
  92goto On_Error
  93 end
  94  end
  95
  96Set NOCOUNT OFF
  97select @outfilename
  98Set NOCOUNT ON
  99return
  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
  107raiserror @ErrNum @ErrInfo
  108Return
  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 帐户
  http://support.microsoft.com/kb/264155/zh-cn
纠错

育路版权与免责声明

① 凡本网注明稿件来源为"原创"的所有文字、图片和音视频稿件,版权均属本网所有。任何媒体、网站或个人转载、链接转贴或以其他方式复制发表时必须注明"稿件来源:育路网",违者本网将依法追究责任;

② 本网部分稿件来源于网络,任何单位或个人认为育路网发布的内容可能涉嫌侵犯其合法权益,应该及时向育路网书面反馈,并提供身份证明、权属证明及详细侵权情况证明,育路网在收到上述法律文件后,将会尽快移除被控侵权内容。