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>