ASP导出到Excel
时间：2011-03-09 来源：开始测试
ASP导出到Excel
http://tomieric.wordpress.com/2010/06/24/export_to_excel/
简单导出到excel二则
1.利用IE和html的ContentType。
最简单且可导出6W以内记录。
| 07 | response.write html |
| 08 | %> |
| 09 | <form action="export.asp" method="post"> |
| 10 | <input type="hidden" name="export" value="<%=html%>"> |
| 11 | <input type="button" name="submit" value="导出当前页面表数据"> |
| 12 | </form> |
| 07 | Response.AddHeader "content-disposition","attachment;filename=Test.xls" |
| 08 | Response.Buffer = True |
| 09 | Response.ExpiresAbsolute = Now() - 1 |
| 10 | Response.Expires = 0 |
| 11 | Response.CacheControl = "no-cache" |
| 12 | Response.AddHeader "Pragma", "No-Cache" |
| 13 | dim i |
| 14 | i=1 |
| 15 | response.Write "<table>" |
| 16 | while i<=75000 |
| 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>" |
| 18 | i=i+1 |
| 19 | if i=65000 then response.end |
| 20 | wend |
| 21 | response.Write "</table>" |
| 22 | %> |
2.利用模版生成excel文件
主目录下有download文件夹，download文件夹有temp.xls模版文件和temp文件夹（临时存放文件）。
需设置服务器端对temp文件夹设置可写权限。
先建立所需模版,利用查询出来的结果集再插入excel表中
| 049 |
| 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”) |
| 055 |
| 056 | Xls_Conn.execute(” insert into [temp$] (“&zdstr&”) values (“&TabTitle&”)”) |
| 057 | ‘—————————————————————– |
| 058 | iflag=0 |
| 059 | while not rs.eof |
| 060 |
| 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 | ‘——————–’复制文件到临时文件夹——————————— |
| 063 |
| 064 | ‘生成多文件 |
| 065 | TempSource= Server.MapPath(“download/temp.xls”) |
| 066 | NewFileName1=”download/temp/” & Generator(6) & “.xls” |
| 067 | TempEnd=Server.MapPath( NewFileName1) |
| 068 | Call CopyFiles(TempSource,TempEnd) ‘复制文件到临时文件夹 |
| 069 |
| 070 | ’——————————— |
| 071 |
| 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&”)”) |
| 077 |
| 078 | FileName=FileName&”<divdownload”"><span>文件”&i&” <a href=”"download/”&NewFileName1 & “”"><img src=”"images/sjh/btndown.gif”" /></a></span></div>” |
| 079 |
| 080 |
| 081 | end if |
| 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))&”‘,” |
| 087 | else |
| 088 | Xls_sql=Xls_sql&rs(fileds_Array(i))&”,” |
| 089 | end if |
| 090 | next |
| 091 |
| 092 | Xls_sql=left(Xls_sql,len(Xls_sql)-1)&”)” |
| 093 | Xls_Conn.execute(Xls_sql) |
| 094 |
| 095 | iflag=iflag+1 |
| 096 | rs.movenext |
| 097 | wend |
| 098 | %> |
| 099 | <div id=”content”> |
| 100 | <span>下载地址:</span> |
| 101 | <%=NewFileName1%></p> |
| 102 | </div> |
| 103 |
| 104 |
| 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”) |
| 110 |
| 111 | Xls_Conn.execute(” insert into [temp$] (“&zdstr&”) values (“&TabTitle&”)”) |
| 112 | ‘—————————————————————– |
| 113 | iflag=0 |
| 114 | while not rs.eof |
| 115 |
| 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 | ‘——————–’复制文件到临时文件夹——————————— |
| 118 |
| 119 | ‘生成多文件 |
| 120 | TempSource= Server.MapPath(“download/temp.xls”) |
| 121 | NewFileName1=”download/temp/” & Generator(6) & “.xls” |
| 122 | TempEnd=Server.MapPath( NewFileName1) |
| 123 | Call CopyFiles(TempSource,TempEnd) ‘复制文件到临时文件夹 |
| 124 |
| 125 | ‘——————————— |
| 126 |
| 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&”)”) |
| 132 |
| 133 | FileName=FileName&”<divdownload”"><span>文件”&i&” <a href=”"download/”&NewFileName1 & “”"><img src=”"images/sjh/btndown.gif”" /></a></span></div>” |
| 134 |
| 135 | end if |
| 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))&”‘,” |
| 141 | else |
| 142 | Xls_sql=Xls_sql&rs(fileds_Array(i))&”,” |
| 143 | end if |
| 144 | next |
| 145 |
| 146 | Xls_sql=left(Xls_sql,len(Xls_sql)-1)&”)” |
| 147 | Xls_Conn.execute(Xls_sql) |
| 148 |
| 149 | iflag=iflag+1 |
| 150 | rs.movenext |
| 151 | wend |
| 152 | %> |
| 153 | <div id=”content”> |
| 154 | <span>下载地址:</span> |
| 155 | <%=NewFileName1%></p> |
| 156 | </div> |
排行榜 更多 +
