从经典 ASP 将数据导出到 excel 文件失败 [英] Export data to excel file from Classic ASP failing
问题描述
我正在尝试将记录集导出到 Excel，但它似乎在生产服务器上一直失败.但是，它似乎在我的开发工作站上工作得很好.我想知道我是否适合与服务器相关的问题，但我有其他应用程序可以使用相同的确切代码很好地导出，以及相同的代码设置.
I'm trying to export a record set into Excel but it seems to keep failing on the production servers. However, it seems to work just fine on my development workstation. I'm wondering i fit's a server related issue but I have other apps that can export just fine using the same exact code, well similar code same set up.
<%@ Language=VBScript %>
<%Response.expires = -1%>
<%response.buffer = true%>
<%
Dim today
today = "_" + Replace(Date,"/","") + "_" + Replace(Time(),":", "")
Response.Charset = "ANSI"
Response.ContentType = "application/octet-stream"
Response.ContentType = "application/vnd.ms-excel"
Response.AddHeader "Content-Disposition", "attachment; filename=List" + today + ".xls"
Response.ContentType = "application/download"
set Cnn = server.CreateObject("ADODB.connection")
Cnn.ConnectionString = Application("Cnn_ConnectionString")
Cnn.open
set rs1 = server.CreateObject("ADODB.Recordset")
SQLCollections = "Sp_MysProc @Param1=" & Session("var1")
rs1.open SQLCollections,cnn
%>
<html>
<body>
<table>
<tr>
<td>Number</td>
<td>Name</td>
</tr>
<%if not rs.eof then
do while not rs.eof %>
<tr>
<td><%=rs("Number") %></td>
<td><%=rs("Name") %></td>
</tr>
<%
rs.MoveNext
Loop
rs.Close
set rs = Nothing
End if
%>
</table>
</body>
</html>
同样，这适用于我的机器.但是当我从生产中执行它时，它会给我以下信息:
Again, this works from my machine. But when I do it from production it gives me the following message:
Internet Explorer 无法下载来自 www.mydomain.com 的 MyFile.asp
Internet Explorer cannot download MyFile.asp from www.mydomain.com
Internet Explorer 无法打开这个互联网站点.请求的站点不可用或不能成立.请稍后再试.
Internet Explorer was not able to open this Internet site. The requested site is either unavailable or cannot be found. Please try again later.
除了错误之外，还有什么方法可以使其导出而不显示为具有白色背景且没有线条的 HTML，即像真正的 Excel 文件那样?
Beyond the error is there any way to make it export and not display as HTML with a white background and no lines, i.e. like a real Excel file would?
编辑:内容类型已根据 Anthony 的回答进行了更正.
Edit: Content types have been corrected based on Anthony's answer.
日期没有硬编码，允许每天创建多个文件而无需任何用户干预(用户请求).
The date is not hard coded to allow multiple files to be created daily with out any user intervention (user requested).
我已更新以删除 If Not EOF.我注意到很多长时间运行的连接，也许应用程序周围存在许多此类问题.谢谢你的提示.而且它仍然可以工作，因为没有按照要求的记录集.
I've updated to remove the If Not EOF. I've been noticing a lot of long running connections, perhaps there are a number of these types of issues around the app. Thanks for the tip. Also it still works desipte there being no recordset which was as requested.
编辑 2我已经解决了列名不正确的问题(哎呀！)，现在它可以从生产中正确下载到我的计算机上.我有 Office 2007.但它仍然无法在至少另一台计算机上下载.这台计算机上装有 Office 2000.但是删除标题并允许它溢出它在所有机器上工作的 HTML.
Edit 2 I've fixed on eof the issue with an improper column name (oops!) and it now downloads correctly on my computer from production. I have Office 2007. But the thing still will not download on at least one other computer. This other computer has Office 2000 on it. But removeing the headers and allowing it to spill out jsut the HTML it works on all machines.
Office 2000 会不会遇到此类问题?
Might Office 2000 have an issue with this sort of thing?
推荐答案
先做几件家务.
设置 Content-Type 3 次没有什么意义.只需坚持使用 `applicationvnd.ms-excel" 即可.
There is little point setting the Content-Type 3 times. Just stick with the `applicationvnd.ms-excel" one.
使用Windows-1252"而不是使用ANSI"作为字符集.
Rather than using "ANSI" as the character set use "Windows-1252".
输出有多大?由于您正在缓冲，您可能会达到 IIS6 的 ASP 缓冲区默认最大值 4MB.
How big is the output? Since you are buffering you may be hitting the ASP buffer default maximum of 4MB of IIS6.
关闭缓冲或弹出配置数据库编辑器并增加应用程序的 AspBufferingLimit 值.
Either turn off buffering or pop into metabase editor and increase the AspBufferingLimit value on your application.
编辑:
接下来我会尝试在我的客户端上安装 Fiddler 并尝试下载.当您尝试下载文件时，您在 fiddler 中看到了什么?
The next thing I would try is install Fiddler on my client and attempt the download. What do you see in fiddler when you attempt to download the file?
你安装了什么版本的微软办公软件?
What version of MS office do you have installed?
这篇关于从经典 ASP 将数据导出到 excel 文件失败的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
