Export Data to Excel using ASP

Related Articles

1. As we know that almost 90% of people all over the world use MS office package for their regular office activities. We too know that through web corporate
organizations do business to a larger extend. Most of the time the companies feel diffcult in logging each and every time on to net to view information.Here
is a simple and effective way to export the enquiries or other database information present in the server to a excel file.

2. Let us consider a enquiry form available in a website. Whenever a enquiry is placed in the form they are inserted in to a table (Database may be either
SQL server, Access). To view the enquiries the company in turn may have a seperate administration panel. The datas in turn will get reflected from the database. We provide you a simple and effective solution to export the datas to a Excel file so that they have a clear and a effective result.

3. Create a simple database using MSAccess Example (Sample.mdb) and table (Emp) with 4 fields ID,Name,Address,Descr. Enter 10 to 15 records in the table.

4. Create a ASP page with the below given code and save it as "export_to_excel.asp"

<!--
<%@ Language=VBScript %>
<%
dim Cn,Rs
set Cn=server.createobject("ADODB.connection")
set Rs=server.createobject("ADODB.recordset")
Cn.open "provider=microsoft.jet.oledb.4.0;data source=" & server.mappath("Sample.mdb")
Rs.open "select * from Emp",Cn,1,3
Response.ContentType = "application/vnd.ms-excel"
Response.AddHeader "Content-Disposition", "attachment; filename=Enquiry_List.xls"
if Rs.eof <> true then
response.write "<table border=1>"
while not Rs.eof
response.write "<tr><td>" & Rs.fields("ID") & "</td><td>" &

Rs.fields("Name") & "</td><td>" & Rs.fields("Address") & "</td><td>" & Rs.fields("Descr") &

"</td></tr>"
Rs.movenext
wend
response.write "</table>"
end if
set rs=nothing
Cn.close
%>
-->

5.Create another asp page to just list out all the datas to display. Name the page as
"View_Enquiry.asp"

<!--
<%@ Language=VBScript %>
<html>
<body><table border=0>
<tr><td><a href="export_to_excel.asp">Export</a></td></tr>
<tr><td>
<%
dim Cn,Rs
set Cn=server.createobject("ADODB.connection")
set Rs=server.createobject("ADODB.recordset")
Cn.open "provider=microsoft.jet.oledb.4.0;data source=" & server.mappath("Sample.mdb")
Rs.open "select * from Emp",Cn,1,3
if Rs.eof <> true then
response.write "<table border=1>"
while not Rs.eof
response.write "<tr><td>" & Rs.fields("ID") & "</td><td>" &


Rs.fields("Name") & "</td><td>" & Rs.fields("Address") & "</td><td>" & Rs.fields("Descr") &

"</td></tr>"
Rs.movenext
wend
response.write "</table>"
end if
set rs=nothing
Cn.close
%>
</td></tr>
</table>
</body>
</html>
-->

6. Now view the "View_Enquiry.asp" page in the browser. You can see the datas getting are

reflected from the database.To export all these dats to a excel fle click the link on the

top of the page. A small dialog box will get prompted which in turn will ask you to save

the Excel file to your local system.

7. For any queries and doubts related with the program kindly logon to http://www.aesasp.com and post your queries or alternatively mail to us at support@aesasp.com.


Publication Date: Saturday 21st August, 2004
Author: U.swaroop View profile

Related Articles