目前页面生成excel的方法很多,总结起来,不外乎两类,一种是使用excel对象,一种是“伪文件”。两种方法是各自有各自的优缺点,在不同的领域也都有很多成功的案例。前者使用对象的方式很灵活,可以生成任意表现方式的excel文件,缺点也很明显,比如在asp下,使用excel如果发生异常,excel对象的资源是不会释放的,也就是说在特殊情况下会把服务器“拖死”。后者的方式一般使用的是html文件,但是后缀是xls,也就是“伪文件”,这样的操作在生成excel文件的时候,对比第一种方法系统开销比较小,但是由于是“伪文件”,在打开文件的时候会有提示,但是由于生成html的方法很多,也是目前在我们系统中采用比较多的方式,另外他生成复杂样式的时候也比较方便,可以采用tr td的方式加上style.
一、综述:
目前页面生成excel的方法很多,总结起来,不外乎两类,一种是使用excel对象,一种是“伪文件”。两种方法
是各自有各自的优缺点,在不同的领域也都有很多成功的案例。前者使用对象的方式很灵活,可以生成任意表现
方式的excel文件,缺点也很明显,比如在asp下,使用excel如果发生异常,excel对象的资源是不会释放的,也
就是说在特殊情况下会把服务器“拖死”。后者的方式一般使用的是html文件,但是后缀是xls,也就是“伪文件
”,这样的操作在生成excel文件的时候,对比第一种方法系统开销比较小,但是由于是“伪文件”,在打开文件
的时候会有提示,但是由于生成html的方法很多,也是目前在我们系统中采用比较多的方式,另外他生成复杂样
式的时候也比较方便,可以采用tr td的方式加上style.
二、方案说明
下面我要说的这种方式,是目前能找到的最快的生成excel文件的方式,姑且叫做“bcp生成csv”方法吧。它有
以下几个适用的范围。需要说明我的这篇文章不是要生成完美的excel文件,是要解决大数据量下快速生成excel文件的问题,我发现很多园子的朋友都误解了,特此说明
1.生成的excel格式比较单一,没有合并列等情况
2.生成的文件格式为csv,但是可以用excel默认打开
3.执行的存储过程用户需要xp_cmdshell权限
接下来,说说它的好处:
1.生成效率很高,由于是并发操作,每1000条数据传送一次
2.生成的文件没有冗余代码,全部为数据信息,保证了文件是所有类型中最小的
3.没有office2003中的excel的单sheet的6万多行的限制,就算输出10万条数据也能正常生成,但是用excel2003
打开失败,用excel2007打开正常
但是由于使用上的不方便,我就写了一个存储过程,只需要传递几个参数进去,就能自动生成对应的excel文件
。先贴上代码:
三、代码

Code
1
USE [student]
2
GO
3
/**//****** 对象: StoredProcedure [dbo].[proc_2csv] 脚本日期: 12/30/2008 12:01:17 ******/
4
SET ANSI_NULLS ON
5
GO
6
SET QUOTED_IDENTIFIER ON
7
GO
8
/**//**************************************************************
9
/************* copyright by James.wang(天生我豺)***************
10
/************* 欢迎转载,转载请注明原作者**********************
11
/************* email:ec0312@163.com **************************/
12
13
create 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
22
AS
23
BEGIN
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
59
t1.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
77
d:\edufeweb\dufenew\\’+@outfilename+\’.csv –c –t“,“ –r“\n“ –S“172.16.4.*“ –U“sa“ –
78
79
P“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
101
On_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
109
END
四、代码说明
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