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 "<html xmlns:o=""urn:schemas-microsoft-com:office:office"" xmlns:x=""urn:schemas-microsoft-com:office:excel""...
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 "<table border=1 bordercolor='#CCCCCC' WIDTH='100%'>
<tr>
<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>
</tr>"
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 "<tr>
<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>
</tr>"
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 "</table>"
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!
how to use progress bar while exporting data to excel in asp?
ReplyDeleteAceng, you can build a progress bar to show progress of the export job, however, as it is in every progress bar, you first need to know how long it will take, and then start the exporting job showing the progress depending on the total time value. I don't think that it is worth it (I mean do the job twice). In any case check my progress bar articles and see if they can help:
DeleteProgress element
Progress indicator