一、綜述：
目前頁面生成excel的方法很多，總結(jié)起來，不外乎兩類，一種是使用excel對(duì)象，一種是“偽文件”。兩種方法是各自有各自的優(yōu)缺點(diǎn)，在不同的領(lǐng)域也都有很多成功的案例。前者使用對(duì)象的方式很靈活，可以生成任意表現(xiàn)方式的excel文件，缺點(diǎn)也很明顯，比如在asp下，使用excel如果發(fā)生異常，excel對(duì)象的資源是不會(huì)釋放的，也就是說在特殊情況下會(huì)把服務(wù)器“拖死”。后者的方式一般使用的是html文件，但是后綴是xls,也就是“偽文件”,這樣的操作在生成excel文件的時(shí)候，對(duì)比第一種方法系統(tǒng)開銷比較小，但是由于是“偽文件”，在打開文件的時(shí)候會(huì)有提示，但是由于生成html的方法很多，也是目前在我們系統(tǒng)中采用比較多的方式，另外他生成復(fù)雜樣式的時(shí)候也比較方便，可以采用tr td的方式加上style.
二、方案說明
下面我要說的這種方式，是目前能找到的最快的生成excel文件的方式，姑且叫做“bcp生成csv”方法吧。它有以下幾個(gè)適用的范圍。
1.生成的excel格式比較單一，沒有合并列等情況
2.生成的文件格式為csv，但是可以用excel默認(rèn)打開
3.執(zhí)行的存儲(chǔ)過程用戶需要xp_cmdshell權(quán)限
接下來，說說它的好處：
1.生成效率很高，由于是并發(fā)操作，每1000條數(shù)據(jù)傳送一次
2.生成的文件沒有冗余代碼，全部為數(shù)據(jù)信息，保證了文件是所有類型中最小的
3.沒有office2003中的excel的單sheet的6萬多行的限制，就算輸出10萬條數(shù)據(jù)也能正常生成，但是用excel2003打開失敗，用excel2007打開正常，但是由于使用上的不方便，就寫了一個(gè)存儲(chǔ)過程，只需要傳遞幾個(gè)參數(shù)進(jìn)去，就能自動(dòng)生成對(duì)應(yīng)的excel文件。先貼上代碼：
三、代碼
1USE [student]
2GO
3/**//****** 對(duì)象: 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/************* 歡迎轉(zhuǎn)載，轉(zhuǎn)載請(qǐng)注明原作者**********************
11/************* email:ec0312@163.com **************************/
12
13create PROCEDURE [dbo].[proc_2csv]
14(
15--參數(shù)聲明
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=’生成物理表錯(cuò)誤’
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/**//*導(dǎo)出數(shù)據(jù)到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/**//*刪除臨時(shí)表*/
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=’刪除物理表錯(cuò)誤’
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:傳入顯示的列標(biāo)題，用英文的逗號(hào)分割
4.例子：
/*測(cè)試
[proc_2csv] ’select top 1000 userid,cardname,cardid,studentname,case sex when ’’1’’ then ’’男’’
else ’’女’’ end sex’,’from registersys’,’用戶名,證件類型,證件號(hào)碼,姓名,性別’
*/
五、補(bǔ)充說明：
1.如果傳入的sql語句中有單引號(hào)，在傳入之前替換成兩個(gè)單引號(hào)
2.如果傳入的sql語句有英文的逗號(hào)，替換成全角的逗號(hào)
3.注意master..xp_cmdshell代碼中的172.16.4.*替換成你機(jī)器ip,后面替換成對(duì)應(yīng)的帳戶和密碼，注意這個(gè)
帳戶必須有xp_cmdshell的權(quán)限
六、引申：
很多人會(huì)說用sa不安全，用xp_cmdshell不安全，確實(shí)是這樣，但是我們可以采用臨時(shí)授予當(dāng)前用戶執(zhí)行系統(tǒng)
擴(kuò)展存儲(chǔ)過程權(quán)限，這個(gè)方面我也正在學(xué)習(xí)，如果大家有這方面的想法可以一起探討。
以下是找到的一些資源：
1.如何在不提升用戶權(quán)限的情況下，使普通用戶執(zhí)行xp_cmdshell存儲(chǔ)過程
http://blog.csdn.net/puddingpudding/archive/2008/12/04/3445833.aspx
2.重新設(shè)置代理和 SQLAgentCmdExec 帳戶
http://support.microsoft.com/kb/264155/zh-cn
目前頁面生成excel的方法很多，總結(jié)起來，不外乎兩類，一種是使用excel對(duì)象，一種是“偽文件”。兩種方法是各自有各自的優(yōu)缺點(diǎn)，在不同的領(lǐng)域也都有很多成功的案例。前者使用對(duì)象的方式很靈活，可以生成任意表現(xiàn)方式的excel文件，缺點(diǎn)也很明顯，比如在asp下，使用excel如果發(fā)生異常，excel對(duì)象的資源是不會(huì)釋放的，也就是說在特殊情況下會(huì)把服務(wù)器“拖死”。后者的方式一般使用的是html文件，但是后綴是xls,也就是“偽文件”,這樣的操作在生成excel文件的時(shí)候，對(duì)比第一種方法系統(tǒng)開銷比較小，但是由于是“偽文件”，在打開文件的時(shí)候會(huì)有提示，但是由于生成html的方法很多，也是目前在我們系統(tǒng)中采用比較多的方式，另外他生成復(fù)雜樣式的時(shí)候也比較方便，可以采用tr td的方式加上style.
二、方案說明
下面我要說的這種方式，是目前能找到的最快的生成excel文件的方式，姑且叫做“bcp生成csv”方法吧。它有以下幾個(gè)適用的范圍。
1.生成的excel格式比較單一，沒有合并列等情況
2.生成的文件格式為csv，但是可以用excel默認(rèn)打開
3.執(zhí)行的存儲(chǔ)過程用戶需要xp_cmdshell權(quán)限
接下來，說說它的好處：
1.生成效率很高，由于是并發(fā)操作，每1000條數(shù)據(jù)傳送一次
2.生成的文件沒有冗余代碼，全部為數(shù)據(jù)信息，保證了文件是所有類型中最小的
3.沒有office2003中的excel的單sheet的6萬多行的限制，就算輸出10萬條數(shù)據(jù)也能正常生成，但是用excel2003打開失敗，用excel2007打開正常，但是由于使用上的不方便，就寫了一個(gè)存儲(chǔ)過程，只需要傳遞幾個(gè)參數(shù)進(jìn)去，就能自動(dòng)生成對(duì)應(yīng)的excel文件。先貼上代碼：
三、代碼
1USE [student]
2GO
3/**//****** 對(duì)象: 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/************* 歡迎轉(zhuǎn)載，轉(zhuǎn)載請(qǐng)注明原作者**********************
11/************* email:ec0312@163.com **************************/
12
13create PROCEDURE [dbo].[proc_2csv]
14(
15--參數(shù)聲明
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=’生成物理表錯(cuò)誤’
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/**//*導(dǎo)出數(shù)據(jù)到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/**//*刪除臨時(shí)表*/
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=’刪除物理表錯(cuò)誤’
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:傳入顯示的列標(biāo)題，用英文的逗號(hào)分割
4.例子：
/*測(cè)試
[proc_2csv] ’select top 1000 userid,cardname,cardid,studentname,case sex when ’’1’’ then ’’男’’
else ’’女’’ end sex’,’from registersys’,’用戶名,證件類型,證件號(hào)碼,姓名,性別’
*/
五、補(bǔ)充說明：
1.如果傳入的sql語句中有單引號(hào)，在傳入之前替換成兩個(gè)單引號(hào)
2.如果傳入的sql語句有英文的逗號(hào)，替換成全角的逗號(hào)
3.注意master..xp_cmdshell代碼中的172.16.4.*替換成你機(jī)器ip,后面替換成對(duì)應(yīng)的帳戶和密碼，注意這個(gè)
帳戶必須有xp_cmdshell的權(quán)限
六、引申：
很多人會(huì)說用sa不安全，用xp_cmdshell不安全，確實(shí)是這樣，但是我們可以采用臨時(shí)授予當(dāng)前用戶執(zhí)行系統(tǒng)
擴(kuò)展存儲(chǔ)過程權(quán)限，這個(gè)方面我也正在學(xué)習(xí)，如果大家有這方面的想法可以一起探討。
以下是找到的一些資源：
1.如何在不提升用戶權(quán)限的情況下，使普通用戶執(zhí)行xp_cmdshell存儲(chǔ)過程
http://blog.csdn.net/puddingpudding/archive/2008/12/04/3445833.aspx
2.重新設(shè)置代理和 SQLAgentCmdExec 帳戶
http://support.microsoft.com/kb/264155/zh-cn