HOWTO: Display ASP Results Using Excel in IE with MIME Types

ID: Q199841

The information in this article applies to:

Microsoft Visual InterDev, version 6.0
Microsoft Internet Information Server version 4.0
Microsoft Excel 97 for Windows

SUMMARY

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.

MORE INFORMATION

To follow along with the steps and test the results, you need the following:

Microsoft Windows NT Server 4.0, SP3
Microsoft Internet Information Server (IIS), version 4.0
Microsoft SQL Server 6.5
Microsoft Excel 97

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.

(c) Microsoft Corporation 1999, All Rights Reserved. Contributions by Joe Crump, Microsoft Corporation.

Additional query words: activexdocument docobject kbGrpDSO

Keywords : kbADO kbASP kbExcel kbSQLServ kbVisID kbGrpDSO
Version : WINDOWS:6.0,97; winnt:4.0
Platform : WINDOWS winnt
Issue type : kbhowto