How to output an Excel *.xls file from classic ASP

Question

I have a number of generated html tables that I need to output as an Excel file. The site is codded in classic ASP. Is this possible? Could it be done by somehow using the Open Office libraries?

---

EDIT: Thus far, I have tried some of the suggestions, but it seems to fail. Ideally, I want the user to be able to click a link that will begin the download of a .xls file. This code:

<%@ Language=VBScript %>
<%  option explicit

Response.ContentType = "application/vnd.ms-excel"
Response.AppendHeader "content-disposition", " filename=excelTest.xls"
%>
<table>
    <tr>
        <th>Test Col 1</th>
        <th>Test Col 2</th>
        <th>Test Col 3</th>
        <th colspan="2">Test Col 4</th>
        <th>Test Col 6</th>
        <th>Test Col 7</th>
    </tr>
    <tr>
        <td>Data</td>
        <td>Data</td>
        <td>Data</td>
        <td>Data</td>
        <td>Data</td>
        <td>Data</td>
        <td>Data</td>
    </tr>
</table>

seems to fail when IE7 is used to get the page. IE says that it "cannot download excelTest.asp" and that "The requested site is either unavailable or cannot be found."

Answer 1

you have to set the response content-type and content-disposition such that the browser doesn't treat it like "normal" html.

something like:

<%
Response.ContentType = "application/zip";
Response.AppendHeader("content-disposition", "attachment; filename=something.xls");
%>
<table>
<tr>
...
</tr>
</table>

Answer 2

You can always just export the HTML table to an XLS document. Excel does a pretty good job understanding HTML tables.

Another possiblitly is to export the HTML tables as a CSV or TSV file, but you would need to setup the formatting in your code. This isn't too difficult to accomplish.

There's some classes in the Microsoft.Office.Interop that allow you to create an Excel file programatically, but I have always found them to be a little clumsy. You can find a .NET version of creating a spreadsheet here, which should be pretty easy to modify for classic ASP.

As for .NET, I've always liked CarlosAG's Excel XML Writer Library. It has a nice generator so you can setup your Excel file, save it as an XML spreadsheet and it generates the code to do all the formatting and everything. I know it's not classic ASP, but I thought that I would throw it out there.

---

With what you're trying above, try adding the header:

"Content-Disposition", "attachment; filename=excelTest.xls"

See if that works. Also, I always use this for the content type:

  Response.ContentType = "application/octet-stream"
    Response.ContentType = "application/vnd.ms-excel"

Answer 3

There's a 'cheap and dirty' trick that I have used... shhhh don't tell anyone. If you output tab delimited text and make the file name *.xls then Excel opens it without objection, question or warning. So just crank the data out into a text file with tab delimitation and you can open it with Excel or Open Office.

Answer 4

You must specify the file to be downloaded (attachment) by the client in the http header:

Response.ContentType = "application/vnd.ms-excel"
Response.AppendHeader "content-disposition", "attachment: filename=excelTest.xls"

http://classicasp.aspfaq.com/general/how-do-i-prompt-a-save-as-dialog-for-an-accepted-mime-type.html

Answer 5

MS made a COM library called Office Web Components to do this. MSOWC.dll needs to be registered on the server. It can create and manipulate office document files.

How To Use the Spreadsheet Web Component with Visual Basic

Working with the Office Web Components

Answer 6

It's AddHeader, not AppendHeader.

<%@ Language=VBScript %>
<%  Option Explicit

Response.ContentType = "application/vnd.ms-excel"
Response.AddHeader "Content-Disposition", "attachment; filename=excelTest.xls"
%>
<table>
    <tr>
        <td>Test</td>
    </tr>
</table>

Update: I've written a blog post about how to generate Excel files in ASP Classic. It contains a rather useful piece of code to generate both xls and csv files.

Answer 7

I had the same issue until I added Response.Buffer = False. Try changing the code to the following.

Response.Buffer = False Response.ContentType = "application/vnd.ms-excel" Response.AddHeader "Content-Disposition", "attachment; filename=excelTest.xls"

The only problem I have now is that when Excel opens the file I get the following message.

The file you are trying to open, 'FileName[1].xls', is in a different format than specified by the file extension. Verify that the file is not corrupted and is from a trusted source before opening the file. Do you want to open the file now?

When you open the file the data all appears in separate columns, but the spreadsheet is all white, no borders between the cells.

Hope that helps.