http://tomieric.wordpress.com/2010/06/24/export_to_excel/
简单导出到excel二则
1.利用IE和html的ContentType。
最简单且可导出6W以内记录。
|
09
|
<form action="export.asp" method="post">
|
|
10
|
<input type="hidden" name="export" value="<%=html%>">
|
|
11
|
<input type="button" name="submit" value="导出当前页面表数据">
|
|
07
|
Response.AddHeader "content-disposition","attachment;filename=Test.xls"
|
|
08
|
Response.Buffer = True
|
|
09
|
Response.ExpiresAbsolute = Now() - 1
|
|
11
|
Response.CacheControl = "no-cache"
|
|
12
|
Response.AddHeader "Pragma", "No-Cache"
|
|
15
|
response.Write "<table>"
|
|
17
|
response.Write "<tr><td>105</td><td>105223</td><td>中国</td><td>北京</td><td>广州</td><td>4.5</td><td>0</td><td>茂名</td><td>树仔</td><td>内园</td><td>电白 </td></tr>"
|
|
19
|
if i=65000 then response.end
|
|
21
|
response.Write "</table>"
|
2.利用模版生成excel文件
主目录下有download文件夹，download文件夹有temp.xls模版文件和temp文件夹（临时存放文件）。
需设置服务器端对temp文件夹设置可写权限。
先建立所需模版,利用查询出来的结果集再插入excel表中
|
050
|
Set Xls_conn=Server.CreateObject(“ADODB.Connection”)
|
|
051
|
if Xls_conn.state then set Xls_conn=nothing ‘如果连接已经打开.则先关闭
|
|
052
|
Xls_ConnStr=”Provider=Microsoft.Jet.OLEDB.4.0;Data Source=” &Server.MapPath(NewFileName1)& “;Extended Properties=Excel 8.0″ ‘连接字符串
|
|
053
|
Xls_conn.Open Xls_ConnStr
|
|
054
|
Set Xls_rs = Server.CreateObject(“ADODB.Recordset”)
|
|
056
|
Xls_Conn.execute(” insert into [temp$] (“&zdstr&”) values (“&TabTitle&”)”)
|
|
057
|
‘—————————————————————–
|
|
061
|
if i=60000 or i=120000 or i=18000 or i=240000 or i=30000 or i=36000 or i=420000 or i=480000 then ’分文件存放，excel表最多存放65，000条记录
|
|
062
|
‘——————–’复制文件到临时文件夹———————————
|
|
065
|
TempSource= Server.MapPath(“download/temp.xls”)
|
|
066
|
NewFileName1=”download/temp/” & Generator(6) & “.xls”
|
|
067
|
TempEnd=Server.MapPath( NewFileName1)
|
|
068
|
Call CopyFiles(TempSource,TempEnd) ‘复制文件到临时文件夹
|
|
072
|
Set Xls_conn=Server.CreateObject(“ADODB.Connection”)
|
|
073
|
if Xls_conn.state then set Xls_conn=nothing ‘如果连接已经打开.则先关闭
|
|
074
|
Xls_Connstr=”Provider=Microsoft.Jet.OLEDB.4.0;Data Source=” &Server.MapPath(NewFileName1)& “;Extended Properties=Excel 8.0″
|
|
075
|
Xls_conn.Open Xls_Connstr
|
|
076
|
Xls_Conn.execute(” insert into [temp$] (“&zdstr&”) values (“&TabTitle&”)”)
|
|
078
|
FileName=FileName&”<divdownload”"><span>文件”&i&” <a href=”"download/”&NewFileName1 & “”"><img src=”"images/sjh/btndown.gif”" /></a></span></div>”
|
|
082
|
‘可用select val1,val2 into temp
|
|
083
|
Xls_sql=”insert into [temp$] (“&zdstr&”) values (“
|
|
084
|
For i=0 To ubound(fileds_Array)
|
|
085
|
if CheckFieldsInt(rs.fields(i).name)=1 then
|
|
086
|
Xls_sql=Xls_sql&”‘”&rs(fileds_Array(i))&”‘,”
|
|
088
|
Xls_sql=Xls_sql&rs(fileds_Array(i))&”,”
|
|
092
|
Xls_sql=left(Xls_sql,len(Xls_sql)-1)&”)”
|
|
093
|
Xls_Conn.execute(Xls_sql)
|
|
101
|
<%=NewFileName1%></p>
|
|
105
|
Set Xls_conn=Server.CreateObject(“ADODB.Connection”)
|
|
106
|
if Xls_conn.state then set Xls_conn=nothing ‘如果连接已经打开.则先关闭
|
|
107
|
Xls_ConnStr=”Provider=Microsoft.Jet.OLEDB.4.0;Data Source=” &Server.MapPath(NewFileName1)& “;Extended Properties=Excel 8.0″ ‘连接字符串
|
|
108
|
Xls_conn.Open Xls_ConnStr
|
|
109
|
Set Xls_rs = Server.CreateObject(“ADODB.Recordset”)
|
|
111
|
Xls_Conn.execute(” insert into [temp$] (“&zdstr&”) values (“&TabTitle&”)”)
|
|
112
|
‘—————————————————————–
|
|
116
|
if i=60000 or i=120000 or i=18000 or i=240000 or i=30000 or i=36000 or i=420000 or i=480000 then ’分文件存放，excel表最多存放65，000条记录
|
|
117
|
‘——————–’复制文件到临时文件夹———————————
|
|
120
|
TempSource= Server.MapPath(“download/temp.xls”)
|
|
121
|
NewFileName1=”download/temp/” & Generator(6) & “.xls”
|
|
122
|
TempEnd=Server.MapPath( NewFileName1)
|
|
123
|
Call CopyFiles(TempSource,TempEnd) ‘复制文件到临时文件夹
|
|
127
|
Set Xls_conn=Server.CreateObject(“ADODB.Connection”)
|
|
128
|
if Xls_conn.state then set Xls_conn=nothing ‘如果连接已经打开.则先关闭
|
|
129
|
Xls_Connstr=”Provider=Microsoft.Jet.OLEDB.4.0;Data Source=” &Server.MapPath(NewFileName1)& “;Extended Properties=Excel 8.0″
|
|
130
|
Xls_conn.Open Xls_Connstr
|
|
131
|
Xls_Conn.execute(” insert into [temp$] (“&zdstr&”) values (“&TabTitle&”)”)
|
|
133
|
FileName=FileName&”<divdownload”"><span>文件”&i&” <a href=”"download/”&NewFileName1 & “”"><img src=”"images/sjh/btndown.gif”" /></a></span></div>”
|
|
136
|
‘可用select val1,val2 into temp
|
|
137
|
Xls_sql=”insert into [temp$] (“&zdstr&”) values (“
|
|
138
|
For i=0 To ubound(fileds_Array)
|
|
139
|
if CheckFieldsInt(rs.fields(i).name)=1 then
|
|
140
|
Xls_sql=Xls_sql&”‘”&rs(fileds_Array(i))&”‘,”
|
|
142
|
Xls_sql=Xls_sql&rs(fileds_Array(i))&”,”
|
|
146
|
Xls_sql=left(Xls_sql,len(Xls_sql)-1)&”)”
|
|
147
|
Xls_Conn.execute(Xls_sql)
|
|
155
|
<%=NewFileName1%></p>
|
