Its great producing data for users to look at in your web pages and generally that is sufficient for their needs but sometimes you may want to supply your user with the data in a form they can actually do something more with. This code shows you how to display data from your database and then how to give that data to the user in the form of a useable Excel spreadsheet which they can then take away and play with themselves. The way I have shown this done here is to display the data first, using my article 'How To Dynamically Search A Database Table' you could give your users the ability to re-query the data before they produce it. This is the code for the first page called 01viewdata.asp :
This is the clever bit which exports to excel. You could fire this out straight from a link without the need to display it like we did above if you wanted to which would result in the Excel spreadsheet coming straight into existence. This is the code for a page called 02extoex.asp which the link from the first page produces.
-
-
-
<%@ Language=VBScript %>
-
<!-- #include file="../incfiles/adovbs.inc" -->
-
<%
-
'Declare some variables
-
dim Conn,RS1
-
-
'==================CONNECTION CODE TO SQL SERVER=======================
-
'Create a connection object to the database
-
Set Conn = Server.CreateObject("ADODB.Connection")
-
-
'Create a recordset object
-
Set RS1 = Server.CreateObject("ADODB.RecordSet")
-
-
'Feed the connection string into a variable called strConn
-
strConn = "Provider=SQLOLEDB;Data Source=MYSERVERNAME;Initial Catalog=MYDATABASENAME;"&_
-
"User Id=USERNAME;Password=PASSWORD;"
-
-
'Feed the connection string into the connection object
-
Conn.Open strConn
-
'==========================CONNECTION CODE=============================
-
-
'Obtain the sql statement which we fed into the hidden field in the last page
-
sql1 = Request("InpSQL")
-
-
'If you aren't happy with doing that repeat your sql statement here
-
'sql1 = "Select * from tblMYTABLE WHERE ID < 10000"
-
-
'The assumption here is that if we saw data in the last page the data exists so there is no need to
-
'test again for errors so we just go for the data
-
-
RS1.Open sql1, Conn
-
-
'This is the the code which tells the page to open Excel and give it the data to display
-
Response.ContentType = "application/vnd.ms-excel"
-
'You can give the spreadsheet a name at the point its produced
-
Response.AddHeader "Content-Disposition", "attachment; filename=MYSPREADSHEETNAME.xls"
-
%>
-
-
<!--
-
Note that I have formatted the output header here to a dark blue background and white text
-
this will be reflected in the spreadsheet when its produced and you could extend this to your own tastes of course.
-
-->
-
-
<table border="1" width="100%">
-
<tr>
-
<td bgcolor="#000080"><font color="#FFFFFF">Field1Header</font></td>
-
<td bgcolor="#000080"><font color="#FFFFFF">Field2Header</font></td>
-
<td bgcolor="#000080"><font color="#FFFFFF">Field3Header</font></td>
-
<td bgcolor="#000080"><font color="#FFFFFF">Field4Header</font></td>
-
<td bgcolor="#000080"><font color="#FFFFFF">Field5Header</font></td>
-
<td bgcolor="#000080"><font color="#FFFFFF">Field6Header</font></td>
-
<td bgcolor="#000080"><font color="#FFFFFF">Field7Header</font></td>
-
</tr>
-
<%Do While Not RS1.eof%>
-
<tr>
-
<td><%=RS1("Field1Result")%></td>
-
<td><%=RS1("Field2Result")%></td>
-
<td><%=RS1("Field3Result")%></td>
-
<td><%=RS1("Field4Result")%></td>
-
<td><%=RS1("Field5Result")%></td>
-
<td><%=RS1("Field6Result")%></td>
-
<td><%=RS1("Field7Result")%></td>
-
</tr>
-
<%
-
RS1.Movenext
-
Loop
-
%>
-
</table>
-
<%
-
RS1.Close
-
Conn.Close
-
-
Set Conn = Nothing
-
Set RS1 = Nothing
-
%>
-
That's how its done. A practical and easy way to make your site more inter-active.
