Share via

Displaying an Excel Spreadsheet

  • Article

This lesson requires that you have Microsoft Excel installed on your system and is not supported on 64-bit platforms until Excel is developed for 64-bit platforms.

This lesson demonstrates how to display a Microsoft Excel spreadsheet in a Web page. As in the previous lesson, you use ADO. However, in this lesson you connect to an Excel spreadsheet instead of an Access database.

Prepare your Excel spreadsheet to display in an Active Server Page

  1. Using either Excel 98 or Excel 2000, create a spreadsheet and save it as ASPTOC.xls in x:\Inetpub\Wwwroot\Tutorial. Do not include any special formatting or column labels when creating the spreadsheet.

  2. Fill in some of the fields with random data. Treat the first row of cells as column names.

  3. Highlight the rows and columns on the spreadsheet that you want to display in the Web page. (Click one of the fields and drag your mouse diagonally to highlight a block of fields.)

  4. On the Insert menu, select Name, and click Define. This is where all your workbooks are defined by name and range of cells.

  5. Make sure the cell range you highlighted is displayed correctly at the bottom. Type the name MYBOOK for your workbook, and select Add. Whenever you change MYBOOK, make sure the correct cell range is displayed in the Refers to text box at the bottom of the Define Name window. Simply selecting MYBOOK after highlighting a new block of cells does not update the cell range.

  6. If the name shows up in the workbook list, click OK. Save your spreadsheet.

  7. Close Excel to remove the lock on the file so that your ASP page can access it.

In the examples in Lesson 3, you specified a provider name in the connection string, which maps to a specific ADO DLL. In this example, you use a driver name, which causes ASP to use the default provider for that driver name.

Copy and paste the following code in your text editor, and save the file as ViewExcel.asp in the x:\Inetpub\Wwwroot\Tutorial directory. View the example with your browser by typing https://localhost/Tutorial/ViewExcel.asp in the address bar.

<%@ Language= "VBScript" %>  

  <html>  
  <head>  
  <title>Display an Excel Spreadsheet in a Web Page</title>  
  </head>  
  <body> 
  <font face="MS Gothic">  
  <h2>Display an Excel Spreadsheet in a Web Page</h2>  

  <%  
   'Create your connection string, create an instance of the Connection object,  
   ' and connect to the database.  
   strDriver = "Driver={Microsoft Excel Driver (*.xls)};DBQ=C:\Inetpub\Wwwroot\Tutorial\MyExcel.xls;"  
   Set objConn = Server.CreateObject("ADODB.Connection")  
   objConn.Open strDriver  

   'Selects the records from the Excel spreadsheet using the workbook name you saved. 
   strSELECT = "SELECT * from `MYBOOK`"  

   'Create an instance of the ADO Recordset object, and connect it to objConn. 
   Set objRS = Server.CreateObject("ADODB.Recordset")  
   objRS.Open strSELECT, objConn  

   'Print the cells and rows in the table using the GetString method. 
   Response.Write "<H4>Get Excel data in one string with the GetString method</H4>"  
   Response.Write "<table border=1 ><tr><td>"  
   Response.Write objRS.GetString (, , "</td><td><font face=&quot;MS Gothic&quot;>", "</font></td></tr><tr><td>", NBSPACE)  
   Response.Write "</td></tr></table>"  

   'Move to the first record. 
   objRS.MoveFirst  

   'Print the cells and rows in the table using the ViewGB.asp method. 
   Response.Write "<H4>Get Excel data using MoveNext</H4>"  

   'Print out the field names using some of the methods and properties  
   ' of the Recordset object.  
   Response.Write "<table border=1 ><tr>"  

   'For each column in the current row...  
   For i = 0 to (objRS.Fields.Count - 1)  
     ' write out the field name.  
     Response.Write "<td><font face=&quot;MS Gothic&quot;><B>" & objRS(i).Name & "</B></font></td>"  
   Next  

   'While not at the end of the records in the set... 
   While Not objRS.EOF 

     Response.Write "</tr><tr>"  

     'For each column in the current row...  
     For i = 0 to (objRS.Fields.Count - 1)  
       ' write out the data in the field.  
       %><td><font face="MS Gothic"><%=objRS(i)%></font></td><%  
     Next  

     'Move to the next row.  
     objRS.MoveNext  

   Wend  

   Response.Write "</tr></table>" 

   'Close the Connection. 
   objConn.Close 
  %>  

  </font>  
  </body>  
  </html>

In the browser, you should see the following:

Display an Excel Spreadsheet in a Web Page

Get Excel data in one string with the GetString method

A2

B2

C2

A3

B3

C3

Get Excel data using MoveNext

A1

B1

C1

A2

B2

C2

A3

B3

C3