CodeAve.com/ASP - Display an Excel DB

CodeAve.com - ASP - Database Table Display

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

Display an Excel DB

Excel can act as a database. It's not the fastest or the best. However, for small data bits on a server where the chance for significant simultaneous traffic is not likely excel can perform many of the basic tasks that can be accomplished in Access or SQL Server, but on a much smaller scale. For this example, we've taken our db on the career statistics of hank Greenberg and place it insided an excel spreadsheet. We've also named the range of his statistics, including the header rows hamerin_hank. This is not to be confused with naming a sheet in an excel. You must highlight the full range of cells you want to query. From the toolbar in excel select insert -> Name -> define. Name the cell range (I would avoid spaces and special characters) and click OK. Other the directing the query to the excel driver instead of the Access data most everything is the same as displaying an Access db.

•Text View

•Print View

•Mail this Link

• View the Data

<html>
<title>CodeAve.com(Display an Excel in ASP)</title>
<body bgcolor="#FFFFFF">


<%
' Import constants from adovbs.inc specifically
'adOpenStatic and adLockPessimistic

'Name of the excel file being displayed
exceldb="greenberg.xls"

' Create a server connection object
Set cn = Server.CreateObject("ADODB.Connection")
cn.Open "DBQ=" & Server.MapPath(exceldb) & ";" & _
"DRIVER={Microsoft Excel Driver (*.xls)};"

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

' Query to run against the exceldb
' hamerin_hank is the name of the
' cell range as defined in excel
sql="select * from hammerin_hank;"

' Execute the sql
rs.Open sql, cn, _
adOpenStatic, adLockPessimistic
%>
<table border=1 align=center>
<caption>Hank Greenberg Career Statistics</caption><%
For counter = 0 To rs.fields.count - 1 %>
<th>
<% ' Write out the field names
response.write rs.fields.item(counter).name %>
</th><%
' Move to the next field
next

' Move to the first record
rs.movefirst

' Write out the record set
do while not rs.eof %>
<tr><%
' Loop through all of the fileds
for counter = 0 to rs.fields.count - 1
%>
<td align=right>
<% ' Write out the field values
response.write rs.fields.item(counter).value %>
</td><%
' Move to the next field
next
%>
</tr><%
' Move to the next record
rs.movenext
loop
%>
</table>

<%
' Kill the recordset
rs.close
Set rs = nothing
' Kill the connection
cn.close
Set cn = nothing
%>

</body>
</html>