CodeAve.com/ASP - Output To MSExcel

CodeAve.com - ASP - Miscellaneous

ASP
·Read/Write
·Script Writers
·Database Display
·Response Objects
·Server Variables
·Random Events
·Articles
·Miscellaneous
·Contents
·What's New?
JavaScript
CSS
HTML
Maps
Links
Mail List
Search
Sitemap

Output To MSExcel

Writing from a database to a web page is certainly useful, but sometimes putting data to a web page is just as useless as printing it on paper when it comes to doing analysis. Fortunately, if you can bring data to a browser you can bring it to excel where further manipulation can take place. This only difference between the code below and a .asp page that would output to the browser is <%Response.ContentType = "application/vnd.ms-excel"%> which opens Excel within the browser window and providing that MSExcel97 or better is installed on the PC it will output to an excel spreadsheet.

•Text View

•Print View

•Mail this Link

• View the Data

<%
dim accessdb, cn, rs, sql
' Name of the Accessdb being read
accessdb="fidrych"

' Connect to the db with a DSN-less connection
cn="DRIVER={Microsoft Access Driver (*.mdb)};"
cn=cn & "DBQ=" & server.mappath(accessdb)

' Create a server recordset object
Set rs = Server.CreateObject("ADODB.Recordset")

' Select all data from the table the_bird
sql = "select * from the_bird "

' Execute the sql
rs.Open sql, cn
%>

<%
' Tells the browser to open excel
Response.ContentType = "application/vnd.ms-excel" %>
<html>
<body>
<table BORDER="1" align="center">
<tr>
<td>Year</td>
<td>Team</td>
<td>W</td>
<td>L</td>
<td>G</td>
<td>GS</td>
<td>CG</td>
<td>IP</td>
<td>H</td>
<td>BB</td>
<td>SO</td>
<td>ShO</td>
<td>ERA</td>
</tr>

<%
' Move to the first record
rs.movefirst

' Start a loop that will end with the last record
do while not rs.eof
%>

<tr>
<td>
<%= rs("year") %>
</td>

<td>
<%= rs("team") %>
</td>

<td>
<%= rs("w") %>
</td>

<td>
<%= rs("l") %>
</td>

<td>
<%= rs("g") %>
</td>

<td>
<%= rs("gs") %>
</td>

<td>
<%= rs("cg") %>
</td>

<td>
<%= rs("ip") %>
</td>

<td>
<%= rs("h") %>
</td>

<td>
<%= rs("bb") %>
</td>

<td>
<%= rs("so") %>
</td>

<td>
<%= rs("sho") %>
</td>

<td>
<%= rs("era") %>
</td>
</tr>

<%
' Move to the next record
rs.movenext
' Loop back to the do statement
loop %>
</table>

</body>
</html>

<%
' Close and set the recordset to nothing
rs.close
set rs=nothing
%>