Displaying Data from an EXCEL Table with ASP

We'll describe how to display a Microsoft Excel spreadsheet on a Web page. You will use ADO, but you will connect to an Excel spreadsheet instead of the Access database.

Prepare your Excel spreadsheet for viewing on the Active Server Page

Using Excel 98 or Excel 2000, create a spreadsheet and save it as ASPTOC.xls to the x: Inetpub / wwwroot / Tutorial directory. Do not include a custom formatting or column label when you create a spreadsheet.

Fill some of the fields with random data. Consider the first cell line as column names.

Highlight the rows and columns in the spreadsheet that you want to display on the web page. (To highlight an area block, click one of the fields and drag your mouse diagonally.)

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

Make sure that the highlighted cell range is displayed correctly at the bottom. Type the name MYBOOK for your workbook and select Add. When you change MYBOOK, make sure that the correct cell range is displayed in the Reference text box at the bottom of the Define Name window. After you highlight a new cell block, selecting MYBOOK only does not update the cell range.

If the name appears in the workbook list, click OK. Save the spreadsheet.

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 that matches a specific ADO DLL. In this example, you are using a driver name that causes ASP to use the default provider for that drive name. Copy and paste the following code into the text editor and

x: ınetpubwwwroottutorial directory

Save it as ViewExcel.asp.

To address bar

http: //localhost/tutorial/viewexcel.asp

View your typing example with your browser.

<% @ Language = VBScript%>

Excel Viewing an Excel Spreadsheet on a Web Page

<%
, Create your connection string, create an instance of the connection object,
‘And connect to the database.
strDriver =. Driver = {Microsoft Excel Driver (* .xls)}; DBQ = C: InetpubWwwrootTutorialMyExcel.xls; D
Set objConn = Server.CreateObject (re ADODB.Connection j)
objConn.Open strDriver
. Selects the records from the Excel spreadsheet using the workbook name you saved.
strSELECT = B SELECT * from * MYBOOK 'SEL
Ini Create an instance of the ADO Recordset object and bind it to objConn.
Set objRS = Server.CreateObject (b ADODB.Recordset Set)
objRS.Open strSELECT, objConn
Ini Print cells and lines using the GetString method.
Response.Write Resp
Eye Import Excel data into a single string with the GetString method
Response.Write objRS.GetString (,,, ing, et., NBSPACE)
Response.Write rite onse
‘First record.
objrs.movefirst
‘Print the cells and lines in the table using the ViewGB.asp method.
Import Excel data using MoveNext
Ini Using some methods and objects of the Recordset object
Yazdır print the domain names.
Response.Write rite onse
… For each column in the current row ‘
For i = 0 To (objRS.Fields.Count - 1)
Yaz type the domain name.
Response.Write rite onse
Next
Üm When not at the end of the records in the cluster ‘
While Not objRS.EOF
Response.Write rite onse
… For each column in the current row ‘
For i = 0 To (objRS.Fields.Count - 1)
‘Write the data in the field.
% <%
Next
‘Move to the next line.
objrs.movenext
Wendler
Response.Write N) & objRS (i) .Name & Resp
<% = ObjRS (i)%> "
Kapat Close the connection.
objConn.Close
%>