从 Classic ASP 将数据导出到 Excel 文件失败
我正在尝试将记录集导出到 Excel，但它在生产服务器上似乎一直失败。 然而，它似乎在我的开发工作站上运行得很好。 我想知道我适合与服务器相关的问题，但我有其他应用程序可以使用相同的确切代码、相似的代码相同的设置很好地导出。
<%@ 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>
同样，这可以在我的机器上运行。 但是当我从生产环境中执行此操作时，它会向我显示以下消息：
Internet Explorer 无法下载 来自 www.mydomain.com 的 MyFile.asp
Internet Explorer 无法打开 这个网站。 请求的站点 不可用或不能 成立。 请稍后重试。
除了错误之外，还有什么方法可以使其导出而不是显示为具有白色背景且没有线条的 HTML，即像真正的 Excel 文件那样？
编辑： 内容类型已根据安东尼的回答进行了更正。
该日期未进行硬编码，以允许每天创建多个文件，而无需任何用户干预（用户请求）。
我已更新以删除 If Not EOF。 我注意到有很多长时间运行的连接，也许应用程序存在许多此类问题。 谢谢你的提示。 尽管没有所要求的记录集，它仍然有效。
编辑2 我已经用不正确的列名称修复了 eof 问题（哎呀！），现在它可以从生产环境正确下载到我的计算机上。 我有 Office 2007。但至少在另一台计算机上仍然无法下载该内容。 这另一台计算机上装有 Office 2000。 但是删除标头并允许它溢出 HTML 就可以在所有机器上运行。
Office 2000 可能会遇到此类问题吗？
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 cannot download
MyFile.asp from www.mydomain.comInternet Explorer was not able to open
this Internet site. The requested site
is either unavailable or cannot be
found. Please try again later.
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?
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).
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.
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.
Might Office 2000 have an issue with this sort of thing?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（4）
首先是一些家务事。
将 Content-Type 设置 3 次没有什么意义。 只需坚持使用“application\vnd.ms-excel”即可。
不要使用“ANSI”作为字符集，而是使用“Windows-1252”。
输出有多大？由于您正在缓冲，因此可能会达到 ASP 缓冲区默认值最大 4MB 的 IIS6。
要么关闭缓冲，要么弹出元数据库编辑器并增加应用程序上的 AspBufferingLimit 值
编辑：
我要尝试的下一件事是安装 Fiddler 在我的客户端上并尝试下载。当您尝试下载该文件时，您在 fiddler 中看到什么？
您安装了哪个版本的 MS Office ？
First a couple of house keeping things.
There is little point setting the Content-Type 3 times. Just stick with the `application\vnd.ms-excel" one.
Rather than using "ANSI" as the character set use "Windows-1252".
How big is the output? Since you are buffering you may be hitting the ASP buffer default maximum of 4MB of IIS6.
Either turn off buffering or pop into metabase editor and increase the AspBufferingLimit value on your application.
Edit:
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?
我脖子后面的汗毛都竖起来了
当我看到： ...这对服务器上的区域设置非常敏感时， 。 难道一台服务器具有美国日期格式，而另一台服务器具有不同的日期格式？
如果出错，您可能会得到无效的文件名。
The hairs on the back of my neck went up when I saw:
...which is very sensitive to the locale settings on the server. Could it be that one server has US date format, and another has a different date format?
If that went wrong, you might end up with an invalid filename.
如果您的输出仅指定用于导出（导出到 Excel），则无需放置 HTML 和 BODY 标记。 您可以安全地仅写入
...。If your output is designated only for export (to excel) there's no need to put HTML and BODY tags around. You can safely write only the
<table>...</table>.只需使用以下代码禁用代码中的缓冲即可。
Just disable the buffering in your code using the following code.