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

There are comments towards the end of this: blogs.msdn.com/vsofficedeveloper/pages/… — Fionnuala
– Fionnuala, Commented Jan 14, 2009 at 14:13
The only comments I see on that page are some additional notes indicating the changes that need to be done on the server for the mime-types to be supported. Are you saying that this is the piece I'm missing? — cdeszaq
– cdeszaq, Commented Jan 14, 2009 at 15:12
I have a related question here, appreciate if anyone could take a look, stackoverflow.com/questions/1383366/generating-excel-file-error — George2
– George2, Commented Sep 5, 2009 at 13:49

Kristof Neirynck · Accepted Answer · 2009-03-02 21:19:52Z

19

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.

edited Mar 2, 2009 at 21:19

answered Feb 27, 2009 at 14:17

Kristof Neirynck

4,2132 gold badges38 silver badges52 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

George2 Over a year ago

I have a related question here, appreciate if anyone could take a look, stackoverflow.com/questions/1383366/generating-excel-file-error

ckpepper02 Over a year ago

Thanks, Kristof Neirynck!! This helped me figure out how to get IE to recognize the Excel file as download.

ssorrrell · Accepted Answer · 2009-02-20 21:10:07Z

4

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

answered Feb 20, 2009 at 21:10

ssorrrell

6791 gold badge8 silver badges19 bronze badges

Comments

JD Long · Accepted Answer · 2009-01-13 23:33:26Z

0

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.

answered Jan 13, 2009 at 23:33

JD Long

61k58 gold badges209 silver badges300 bronze badges

4 Comments

Ryan Smith Over a year ago

Actually Excel 2007 will pop a warning dialog if a file is in TSV format, but it's extension reads .xls I don't know of any way to avoid this either.

Tester101 Over a year ago

make the text file CSV but give it a .csv extension. Excel will open it with no message.

iokevins Over a year ago

Two potential issues: (1) Microsoft Excel will trim leading spaces and zeroes; and (2) Microsoft Excel 2010 will present warning when opening the file. #1 can be solved by wrapping text with ="{cell-data}" (via: creativyst.com/Doc/Articles/CSV/CSV01.htm#CSVAndExcel)

Neil Over a year ago

When I wanted to do this I think I ended up using either DIF or SYLK format to avoid conversion prompts, but it had the downside that it expected the file to use the OEM code page.

Espen · Accepted Answer · 2009-01-14 16:49:37Z

0

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

answered Jan 14, 2009 at 16:49

Espen

2,1684 gold badges19 silver badges19 bronze badges

Comments

Ryan Smith · Accepted Answer · 2009-01-14 19:18:57Z

0

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"

edited Jan 14, 2009 at 19:18

answered Jan 13, 2009 at 21:48

Ryan Smith

8,36422 gold badges79 silver badges103 bronze badges

1 Comment

cdeszaq Over a year ago

VBScript throws an error: Object doesn't support this property or method: 'Response.AppendHeader'

Mark · Accepted Answer · 2009-07-15 01:51:15Z

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.

karel · Accepted Answer · 2021-05-08 15:41:27Z

0

the XLS file is not a real Excel file. The code create an HTML file with .an xls extension. When you try open the XLS file with Notepad, you can see the HTML code.

edited May 8, 2021 at 15:41

karel

5,96562 gold badges58 silver badges60 bronze badges

answered May 7, 2021 at 14:13

user247513

111 bronze badge

Collectives™ on Stack Overflow

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

7 Answers 7

2 Comments

Comments

4 Comments

Comments

1 Comment

Comments

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

7 Answers 7

2 Comments

Comments

4 Comments

Comments

1 Comment

Comments

Comments

Linked

Related