ASP导出到Excel
http://tomieric.wordpress.com/2010/06/24/export_to_excel/
简单导出到excel二则
1.利用IE和html的ContentType。
最简单且可导出6W以内记录。
'------------------------------------------- 'showData.asp '------------------------------------------- <% dim html html="<table><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><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><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><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></table>" response.write html %> <form action="export.asp" method="post"> <input type="hidden" name="export" value="<%=html%>"> <input type="button" name="submit" value="导出当前页面表数据"> </form>
'------------------------------- 'export.asp '导出最大65,000条记录 '------------------------------- <% Response.ContentType="application/vnd.ms-excel" Response.AddHeader "content-disposition","attachment;filename=Test.xls" Response.Buffer = True Response.ExpiresAbsolute = Now() - 1 Response.Expires = 0 Response.CacheControl = "no-cache" Response.AddHeader "Pragma", "No-Cache" dim i i=1 response.Write "<table>" while i<=75000 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>" i=i+1 if i=65000 then response.end wend response.Write "</table>" %>
2.利用模版生成excel文件
主目录下有download文件夹，download文件夹有temp.xls模版文件和temp文件夹（临时存放文件）。
需设置服务器端对temp文件夹设置可写权限。
先建立所需模版,利用查询出来的结果集再插入excel表中
‘————————————– ‘export.asp ‘————————————– <meta http-equiv=Content-Type content=”text/html; charset=GB2312″> <% Response.Charset= “GB2312″ Response.ContentType=”application/vnd.ms-excel” response.write request(“export”) %>
‘——————————-
‘模版字段 zd1,zd2,zd3….zdn(估计查询结果字段最多数量)
‘生成文件需删除每个文件第一行
‘固定格式，下面的中文标题转换就不需要做了，中文标题作为excel表temp表的字段
‘——————————-
Sql = “select * From books”
Set Rs = Db.CreateRS()
Rs.Open Sql, Db.Conn, 1, 1
Dim i,TabTitle,zdstr ‘i,表头，输出字段
if rs.eof and rs.bof then
response.Write “没有数据…”
response.End()
else
for i=0 to rs.fields.count-1
ReDim Preserve fileds_Array(i)
TabTitle=TabTitle&”‘”&ChangeToCn(rs.fields(i).name)&”‘,” ‘交换成中文字段，输出标题
zdstr=zdstr&”zd”&cstr(i)&”,”
fileds_Array(i)=rs.fields(i).name
next
end if
zdstr=left(zdstr,len(zdstr)-1) ‘去掉末尾“,”
TabTitle=left(TabTitle,len(TabTitle)-1) ‘去掉末尾“,”
‘———————————-删除之前生成的文件—————-
dim d1,d2,fso,path,temp
set fso = createobject(“scripting.filesystemobject”)
path = server.mappath(“download/temp/”)
Set temp = fso.getfolder(path)
for each file in temp.Files
d1=FormatDateTime(ShowDateCreated(server.mappath(“download/temp/”&file.name )),2)
d2=FormatDateTime(now(),2)
if (d1<d2) then
fso.deleteFile(server.mappath(“download/temp/”&file.name )) ‘删除前一天生成的文件
end if
next
‘——————打开excel———————————
Dim Xls_conn,Xls_Connstr,Xls_rs,Xls_Sql,iflag
dim NewFileName,NewFileName1,FileName
TempSource= Server.MapPath(“download/temp.xls”)
NewFileName1=”download/temp/” & Generator(10) & “.xls”
TempEnd=Server.MapPath( NewFileName1)
Call CopyFiles(TempSource,TempEnd) ‘复制文件到临时文件夹
FileName=”<divdownload”"><span>文件1 <a href=”"download/”&NewFileName1 & “”"><img src=”"images/btndown.gif”" /></a></span></div>”
Set Xls_conn=Server.CreateObject(“ADODB.Connection”)
if Xls_conn.state then set Xls_conn=nothing ‘如果连接已经打开.则先关闭
Xls_ConnStr=”Provider=Microsoft.Jet.OLEDB.4.0;Data Source=” &Server.MapPath(NewFileName1)& “;Extended Properties=Excel 8.0″ ‘连接字符串
Xls_conn.Open Xls_ConnStr
Set Xls_rs = Server.CreateObject(“ADODB.Recordset”)
Xls_Conn.execute(” insert into [temp$] (“&zdstr&”) values (“&TabTitle&”)”)
‘—————————————————————–
iflag=0
while not rs.eof
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条记录
‘——————–’复制文件到临时文件夹———————————
‘生成多文件
TempSource= Server.MapPath(“download/temp.xls”)
NewFileName1=”download/temp/” & Generator(6) & “.xls”
TempEnd=Server.MapPath( NewFileName1)
Call CopyFiles(TempSource,TempEnd) ‘复制文件到临时文件夹
’———————————
Set Xls_conn=Server.CreateObject(“ADODB.Connection”)
if Xls_conn.state then set Xls_conn=nothing ‘如果连接已经打开.则先关闭
Xls_Connstr=”Provider=Microsoft.Jet.OLEDB.4.0;Data Source=” &Server.MapPath(NewFileName1)& “;Extended Properties=Excel 8.0″
Xls_conn.Open Xls_Connstr
Xls_Conn.execute(” insert into [temp$] (“&zdstr&”) values (“&TabTitle&”)”)
FileName=FileName&”<divdownload”"><span>文件”&i&” <a href=”"download/”&NewFileName1 & “”"><img src=”"images/sjh/btndown.gif”" /></a></span></div>”
end if
‘可用select val1,val2 into temp
Xls_sql=”insert into [temp$] (“&zdstr&”) values (“
For i=0 To ubound(fileds_Array)
if CheckFieldsInt(rs.fields(i).name)=1 then
Xls_sql=Xls_sql&”‘”&rs(fileds_Array(i))&”‘,”
else
Xls_sql=Xls_sql&rs(fileds_Array(i))&”,”
end if
next
Xls_sql=left(Xls_sql,len(Xls_sql)-1)&”)”
Xls_Conn.execute(Xls_sql)
iflag=iflag+1
rs.movenext
wend
%>
<div id=”content”>
<span>下载地址:</span>
<%=NewFileName1%></p>
</div>
Set Xls_conn=Server.CreateObject(“ADODB.Connection”)
if Xls_conn.state then set Xls_conn=nothing ‘如果连接已经打开.则先关闭
Xls_ConnStr=”Provider=Microsoft.Jet.OLEDB.4.0;Data Source=” &Server.MapPath(NewFileName1)& “;Extended Properties=Excel 8.0″ ‘连接字符串
Xls_conn.Open Xls_ConnStr
Set Xls_rs = Server.CreateObject(“ADODB.Recordset”)
Xls_Conn.execute(” insert into [temp$] (“&zdstr&”) values (“&TabTitle&”)”)
‘—————————————————————–
iflag=0
while not rs.eof
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条记录
‘——————–’复制文件到临时文件夹———————————
‘生成多文件
TempSource= Server.MapPath(“download/temp.xls”)
NewFileName1=”download/temp/” & Generator(6) & “.xls”
TempEnd=Server.MapPath( NewFileName1)
Call CopyFiles(TempSource,TempEnd) ‘复制文件到临时文件夹
‘———————————
Set Xls_conn=Server.CreateObject(“ADODB.Connection”)
if Xls_conn.state then set Xls_conn=nothing ‘如果连接已经打开.则先关闭
Xls_Connstr=”Provider=Microsoft.Jet.OLEDB.4.0;Data Source=” &Server.MapPath(NewFileName1)& “;Extended Properties=Excel 8.0″
Xls_conn.Open Xls_Connstr
Xls_Conn.execute(” insert into [temp$] (“&zdstr&”) values (“&TabTitle&”)”)
FileName=FileName&”<divdownload”"><span>文件”&i&” <a href=”"download/”&NewFileName1 & “”"><img src=”"images/sjh/btndown.gif”" /></a></span></div>”
end if
‘可用select val1,val2 into temp
Xls_sql=”insert into [temp$] (“&zdstr&”) values (“
For i=0 To ubound(fileds_Array)
if CheckFieldsInt(rs.fields(i).name)=1 then
Xls_sql=Xls_sql&”‘”&rs(fileds_Array(i))&”‘,”
else
Xls_sql=Xls_sql&rs(fileds_Array(i))&”,”
end if
next
Xls_sql=left(Xls_sql,len(Xls_sql)-1)&”)”
Xls_Conn.execute(Xls_sql)
iflag=iflag+1
rs.movenext
wend
%>
<div id=”content”>
<span>下载地址:</span>
<%=NewFileName1%></p>
</div>
