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).
With that code you get the data to be used in filling in your Excel table.
Set Cn = Server.CreateObject("ADODB.Connection")
Set Rs = server.createobject("ADODB.recordset")
Rs.open "SELECT * FROM Table",Cn,1,3
Now you specify the header and the content type:
The head of your document is an interesting part where you specify a lot of information on how the Excel file will be presented.
Response.AddHeader "application-Dispostition", "attachment;filename=filename.htm"
Response.ContentType = "application/vnd.ms-excel"
The code will start with
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.
response.write "<html xmlns:o=""urn:schemas-microsoft-com:office:office"" xmlns:x=""urn:schemas-microsoft-com:office:excel""...
Now you create the first row of your Excel document.
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:
response.write "<table border=1 bordercolor='#CCCCCC' WIDTH='100%'>
<td nowrap width='100%' align='center' bgcolor='#CCCCCC'><strong>1</strong></td>
<td nowrap width='100%' align='center' bgcolor='#CCCCCC'><strong>2</strong></td>
<td nowrap width='100%' align='center' bgcolor='#CCCCCC'><strong>3</strong></td>
You might have noticed two important things:
if Rs.eof <> true then
while not Rs.eof
<td nowrap width='100%' align='right' style='mso-number-format:\@'><em>" & Rs.fields("1") & "</em></td>
<td nowrap width='100%' align='right' style='mso-number-format:\@'><em>" & Rs.fields("2") & "</em></td>
<td nowrap width='100%' align='right' style='mso-number-format:standard'>" & Rs.fields("3") & "</td>
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:
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!