November 21st, 2006
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!
Digg this
post to del.icio.us
on March 16th, 2008 at 7:41 pm
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"
on September 8th, 2008 at 7:54 am
Do you know if there is a way to force the worksheet to be 'Sheet1'?