ASP Code

Create Excel .xls files with ASP

I used to work on a web application that tracked hardware inventory. I was requested to build a feature into the report page to be able to export to Excel (.xls) format.

Initially I looked into the code to instantiate a new spreadsheet object, and build the rows and cells one by one programatically. But I found a much simpler solution.

1. Create your report in HTML table format the same as you would your report page.
2. Strip out any header/footer info from the page, everything except the table.
3. Insert this line of code in the ASP header:


<% response.ContentType ="application/x-excel" %>

The table will be produced in HTML by the ASP server the same as it would on the report page. However, the returned content type is marked as .xls, so it will open in Excel as a spreadsheet!

Share

3 Comments

  1. Took mine just one step further. My manager wanted the spreadsheet to have a pre-defined name which included the current date (for example, let's say something like "HardwareInventory-2008-03-16.xls"). Pretty simple to do:

    Dim dtDateStamp
    dtDateStamp = now()
    Dim strFileName
    strFileName = "HardwareInventory-" & year(dtDateStamp) & "-" & month(dtDateStamp) & "-" & day(dtDateStamp)
    Response.ContentType = "application/vnd.ms-excel"
    Response.AddHeader "Content-Disposition", "filename="&strFileName&".xls"

  2. Do you know if there is a way to force the worksheet to be 'Sheet1'?

  3. Hello!
    I found this so interesting, that I translated and shared the info on a brazillian forum. I gave you the credits.

    The translation may be found here >>> http://www.canaldev.com.br/topico/564-relatorio-xls/page__gopid__3035&#entry3035

    if you disagree with it, mail me and I will remove the translation.

    Thanks for this solution!

Leave a Reply

Your email address will not be published. Required fields are marked *

*
To prove you're a person (not a spam script), type the security word shown in the picture. Click on the picture to hear an audio file of the word.
Anti-spam image