Exporting data to Excel in asp

One of the things that I have been asked for several times during web application development, was the possibility of exporting data to Ms Excel. That is because people need to further manipulate query results. I always thought that the best way to deal with data filtering and sorting, is to create a suitable parametric query. Sometimes that is not enough and you end up creating an asp page in order to export data to Ms Excel.

How you create such a page is quite easy. First of all you need to gather the data from your db (Ms Access, SQL Server or whatever).

Dim Cn,Rs
  Set Cn = Server.CreateObject("ADODB.Connection")
  Set Rs = server.createobject("ADODB.recordset")
    Cn.open CONNECTION_STRING
    Rs.open "SELECT * FROM Table",Cn,1,3

With that code you get the data to be used in filling in your Excel table.
Now you specify the header and the content type:

Response.AddHeader "application-Dispostition", "attachment;filename=filename.htm"
Response.ContentType = "application/vnd.ms-excel"

The head of your document is an interesting part where you specify a lot of information on how the Excel file will be presented.

The code will start with

response.write "

The best way to deal with this part (which is not required) is to create a new Excel sheet and then save it as XML. Look into the file with your favourite editor and try to understand how and why the head part is created. I learned a lot looking at it and I could create exported Excel files with structures and groups, filtering and so on.

Now you create the first row of your Excel document.

response.write "




"In the code provided, the first row is the columns heading (3 columns named 1, 2 and 3). Then you go on inserting the data from the recordset:

if Rs.eof <> true then
response.write ""
while not Rs.eof
response.write "





"
response.flush()You might have noticed two important things:
1) the mso-number-format which is used to determine the cell format;
2) the response.flush() which is used to speed up the process a bit in conjuction with Response.Buffer=true (please go to w3schools for more information).

At this point, you only need to close everything:

Rs.movenext
wend
response.write "

1	2	3
" & Rs.fields("1") & "	" & Rs.fields("2") & "	" & Rs.fields("3") & "

"
end if
set rs=nothing
Cn.close

Opening the asp file, you will be prompted with a request to open or save the file. Your Excel file will then be created.

That's all for now. Enjoy!