Instead of displaying your Web data in HTML tables, you can
provide users with the option of displaying the data in Microsoft Excel. This
article demonstrates how to create a Web page with tabular data obtained from
Microsoft SQL Server, and render it in Excel inside the browser by associating
the Web-page content with the MIME type in Excel.
To follow along with the steps and to test the results, you
need the following:
- Microsoft Windows NT Server 4.0, SP3
- Microsoft Internet Information Server (IIS), version 4.0 or
later
- Microsoft SQL Server 6.5 or later
- Microsoft Excel 97, Excel 2000, and Excel 2002
Step-by-Step Procedures
- Create a System DSN called "pubs" using the ODBC
Control-Panel applet. Set up the DSN to reference your local SQL Server, use
SQL Server authentication, and use "pubs" as the default database. The default
login ID to the pubs database is sa, with no password.
- Use Notepad.exe to create a file called XlTest.asp in your
IIS home directory (for example, inetpub\wwwroot), and add the following to
it:
<%@ Language=VBScript %>
<%
'Change HTML header to specify Excel's MIME content type
Response.Buffer = TRUE
Response.ContentType = "application/vnd.ms-excel"
%>
<HTML>
<BODY>
Here is the info you requested.<p>
<%
' Create ADO Connection object
dim myConnection
set myConnection = CreateObject("ADODB.Connection")
' Open SQL Server Pubs database...
' myConnection.Open "DSN=pubs;UID=sa"
' Get a recordset of info from Authors table...
sqlStr = "SELECT au_fname,au_lname,phone FROM authors"
set rsAuthors = myConnection.Execute(sqlStr)
%>
<!-- Our table which will be translated into an Excel spreadsheet -->
<TABLE WIDTH=75% BORDER=1 CELLSPACING=1 CELLPADDING=1>
<TR>
<TD><font size=+2>First Name</font></TD>
<TD><font size=+2>Last Name</font></TD>
<TD><font size=+2>Phone</font></TD>
</TR>
<!-- server-side loop adding Table entries -->
<% do while not rsAuthors.EOF %>
<TR>
<TD><%=rsAuthors("au_fname")%></TD>
<TD><%=rsAuthors("au_lname")%></TD>
<TD><%=rsAuthors("phone")%></TD>
</TR>
<% rsAuthors.MoveNext
loop
' Clean up
rsAuthors.Close
set rsAuthors = Nothing
myConnection.Close
set myConnection = Nothing
%>
</TABLE>
</BODY>
</HTML>
- Save XlTest.asp, and then view it from a client browser.
For example, http://MyServer/XlTest.asp (replace MyServer with the name of
your server.)
If Microsoft Excel 97 is installed on the client computer,
Microsoft Excel displays the data inside the browser.
Article ID: 199841 - Last Review: July 15, 2004 - Revision: 5.4
APPLIES TO
- Microsoft Visual InterDev 6.0 Standard Edition
- Microsoft Internet Information Server 4.0
- Microsoft Internet Information Services 5.0
- Microsoft Excel 97 Standard Edition
- Microsoft Excel 2000 Standard Edition
- Microsoft Excel 2002 Standard Edition
- Microsoft Office Excel 2003
Back to the top
