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.
That's how its done. A practical and easy way to make your site more inter-active.
Code:
<%@ 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=============================
'Create your sql statement
sql1 = "Select * from tblMYTABLE WHERE ID < 10000"
'Obtain the data using all the work done above
RS1.Open sql1, Conn
'Test to see if we have a recordset coming back from the database and trap the error if there is no data
If RS1.eof Then
%>
<html>
<body>
<table border="0" width="100%" cellspacing="0" cellpadding="2">
<tr>
<td> <font face="Arial" color="#FF0000" size="2">No Records Match Your Search</font></td>
</tr>
</table>
<%
'If there is data carry on
Else
%>
<!--
We will need to fire the data we gather to the next page which produces the Excel sheet so here
we use a form to do so instead of formulating the sql statement again when we export to excel
I pass the statement above inside a hidden field called 'InpSQL' below, this makes sure that
what they see on this page is exactly what they get in Excel.
-->
<form method="POST" action="02extoex.asp">
<table border=0 width="100%" cellspacing="0" cellpadding="2">
<tr>
<td>
<input type="submit" value="Export To Excel Spread Sheet" name="B1" style="font-size: 8pt">
<input type="hidden" name="InpSQL" size="63" value="<%=sql1%>"></td>
</tr>
</table>
</form>
<!-- This area is for display to the user and shows how the data to be exported will look -->
<table border="1" width="100%" cellspacing="0" cellpadding="2" bordercolor="#000000">
<tr>
<td bgcolor="#000080"><font face="Arial" size="2" color="#FFFFFF">Field1Header</font></td>
<td bgcolor="#000080"><font face="Arial" size="2" color="#FFFFFF">Field2Header</font></td>
<td bgcolor="#000080"><font face="Arial" size="2" color="#FFFFFF">Field3Header</font></td>
<td bgcolor="#000080"><font face="Arial" size="2" color="#FFFFFF">Field4Header</font></td>
<td bgcolor="#000080"><font face="Arial" size="2" color="#FFFFFF">Field5Header</font></td>
<td bgcolor="#000080"><font face="Arial" size="2" color="#FFFFFF">Field6Header</font></td>
<td bgcolor="#000080"><font face="Arial" size="2" color="#FFFFFF">Field7Header</font></td>
</tr>
<%
'Whilst there are records to show keep going
Do While Not RS1.eof
%>
<!--
Note the use of in each field this makes sure the data looks OK in
a bordered table if the return is null.
-->
<tr>
<td><font face="Arial" size="2"><%=RS1("Field1Result")%> </font></td>
<td><font face="Arial" size="2"><%=RS1("Field2Result")%> </font></td>
<td><font face="Arial" size="2"><%=RS1("Field3Result")%> </font></td>
<td><font face="Arial" size="2"><%=RS1("Field4Result")%> </font></td>
<td><font face="Arial" size="2"><%=RS1("Field5Result")%> </font></td>
<td><font face="Arial" size="2"><%=RS1("Field6Result")%> </font></td>
<td><font face="Arial" size="2"><%=RS1("Field7Result")%> </font></td>
</tr>
<%
'Loop through each record and write it to the screen
RS1.Movenext
Loop
End If
%>
</table>
</body>
</html>
<%
'Clean up and close the connections and recordset objects
RS1.Close
Conn.Close
Set Conn = Nothing
Set RS1 = Nothing
%>
Code:
<%@ 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
%>
Comment