Export data to excel file from Classic ASP failing

excel asp-classic export-to-excel

15,725

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

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

15,725

Author by

Tim Meers

Programmer by day, arcane technology evangelist by night. If it's not Wang, its not for me.

Updated on June 05, 2022

Comments

Tim Meers almost 2 years
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?
Tim Meers over 14 years

No worries here, all US based useages. Well for this app only in my office. But yes I agree fairly bad. DO you have another method that would work better and still give that expected output?
Tim Meers over 14 years

There is a chance for some fairly large files, but I'd speculate nothing larger than 1 or 2 MB. I've also updated it with the different character set and removed the unneeded content types. Though still an issue.
Gary McGill over 14 years

One thing at a time - I'd rule it out by hard-coding the filename in your ASP code. And Anthony's right about the content-disposition; I'd fix that too. Finally, although it's definitely not the cause of this problem, I'd say that If Not rs.EOF Then ... End If is doing more harm than good. All that's doing is stopping you from closing the recordset if it happens to return an empty list (which is bad).
Tim Meers over 14 years

I've updated the post thanks for the tip on the EOF. I'll also try to hard code the file name for testing.
Tim Meers over 14 years

While you were not the one that figured out the problem (me not knowing how to type) you did give soem very helpful tips on the issue.