Export data to excel file from Classic ASP failing

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

Internet 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?

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

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?

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

回答4:

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