从Classic ASP导出数据到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.

我正在尝试将记录集导出到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>

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.com

Internet Explorer无法从www.mydomain.com下载MyFile.asp

Internet Explorer was not able to open this Internet site. The requested site is either unavailable or cannot be found. Please try again later.

Internet Explorer无法打开此Internet站点。请求的网站不可用或无法找到。请稍后再试。

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?

超出错误是有任何方法使其导出,而不是显示为具有白色背景和没有行的HTML,即像真正的Excel文件一样吗?

Edit: Content types have been corrected based on Anthony's answer.

编辑:内容类型已根据Anthony的答案进行了更正。

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.

我已更新删除If Not EOF。我一直注意到很多长期连接,也许应用程序周围有很多类型的问题。谢谢你的提示。此外它仍然有效,因为没有记录集是按要求的。

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.

编辑2我已经修复了一个不正确的列名称问题(哎呀!),它现在可以在我的计算机上从生产中正确下载。我有Office 2007.但事情仍然不会在至少一台其他计算机上下载。这台其他计算机上有Office 2000。但是删除标题并允许它将所有机器上运行的HTML分解为jsut。

Might Office 2000 have an issue with this sort of thing?

可能Office 2000有这样的问题吗?

4 个解决方案

#1

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.

将Content-Type设置为3次没什么意义。只需坚持使用`application \ vnd.ms-excel'即可。

Rather than using "ANSI" as the character set use "Windows-1252".

而不是使用“ANSI”作为字符集使用“Windows-1252”。

How big is the output? Since you are buffering you may be hitting the ASP buffer default maximum of 4MB of IIS6.

输出有多大?由于你正在缓冲,你可能会达到ASP缓冲区默认最大4MB的IIS6。

Either turn off buffering or pop into metabase editor and increase the AspBufferingLimit value on your application.

关闭缓冲或弹出元数据库编辑器并增加应用程序的AspBufferingLimit值。

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?

我要尝试的下一件事是在我的客户端上安装Fiddler并尝试下载。当您尝试下载文件时,您在fiddler中看到了什么?

What version of MS office do you have installed?

你安装了什么版本的MS office?

#2

The hairs on the back of my neck went up when I saw:

当我看到时,脖子后面的毛发上升了:

 today = "_" + Replace(Date,"/","") + "_" + Replace(Time(),":", "")

...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.

如果出错,最终可能会出现无效的文件名。

#3

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>.

如果您的输出仅指定用于导出(excel),则无需放置HTML和BODY标记。您可以安全地只写

... 。

#4

Just disable the buffering in your code using the following code.

只需使用以下代码禁用代码中的缓冲即可。

Response.Buffer = False

#1