Knowledge Base

How To Query and Display Excel Data by Using ASP.NET, ADO.NET, and Visual Basic .NET

Article ID: 311731

Article Last Modified on 6/29/2004

APPLIES TO

Microsoft .NET Framework 1.1 Service Pack 1
Microsoft Excel 2002 Standard Edition
Microsoft Excel 2000 Standard Edition
Microsoft .NET Framework 1.1 Service Pack 1
Microsoft Visual Basic .NET 2002 Standard Edition

This article was previously published under Q311731

For additional information about how to perform this task by using Microsoft Active Server Pages, click the article numbers below to view the articles in the Microsoft Knowledge Base:

195591 How To Display Free/Busy Information from an ASP Page

268948 How To Use an Excel Worksheet as a Data Source in FrontPage 2000

For a Microsoft Visual C# .NET version of this article, see 306572.

For a Microsoft Visual Basic 6.0 version of this article, see 195951.

This article refers to the following Microsoft .NET Framework Class Library namespaces:

System.Data.OleDb

IN THIS TASK

SUMMARY

Create Sample Excel Worksheet
Create ASP.NET Sample Using Visual Basic .NET
Additional Code Explanation

REFERENCES

SUMMARY

This step-by-step article demonstrates how to display data from an Excel spreadsheet through an ASP.NET (.aspx) page by using Visual Basic .NET.

back to the top

Create Sample Excel Worksheet

Start Microsoft Excel, and create a new worksheet.
Add the following information to the new worksheet to create a simple Excel database:

A B

1 FirstName LastName

2 Scott Bishop

3 Katie Jordan

Note Although the data starts with cell A1 in this example, you can add this data to any adjacent cells within the worksheet.
Highlight the rows and columns where the data resides.
On the Insert menu, point to Name, and then click Define.
In the Names in workbook text box, type myRange1, and then click OK.
On the File menu, click Save. In the Save in list, select the Web server root (which is typically C:\InetPub\Wwwroot\). In the File name text box, type ExcelData.xls. Click Save.
On the File menu, click Exit.

back to the top

Create ASP.NET Sample Using Visual Basic .NET

This code sample demonstrates how to query and display information in an Excel worksheet. The following code uses the worksheet that you created in the previous section.

Open Microsoft Visual Studio .NET. The Visual Studio .NET Integrated Design Environment (IDE) is displayed.
On the File menu, point to New, and then click Project.
In the New Project dialog box, under Project Types, click Visual Basic Projects. Under Templates, click ASP.NET Web Application.
In the New Project dialog box, locate the Name and Location text boxes. Notice that the Name box is not available (it appears grayed out or dimmed). The Location text box contains the following text (or similar):

http://localhost/WebApplication1

Replace the text in the Location text box with http://localhost/ExcelVBTest, and then click OK. A new project is created, which includes a Web Form named WebForm1.aspx.
In the Visual Studio .NET IDE, locate the Solution Explorer window. If you cannot find it, click Solution Explorer on the View menu.
In Solution Explorer, right-click WebForm1.aspx, and then click View Designer to display the designer for the appearance of the page. The designer allows you to add controls and manipulate the appearance of the page.
Locate the toolbox. Depending on your IDE Option settings, the toolbox can appear as a window or a button (which often appears on the left side of the IDE). If you cannot find the toolbox, click Toolbox on the View menu.

If the toolbox appears as a button, move the pointer over the button so that the contents of the toolbox are displayed.
When the designer view of a Web Form is active, the toolbox is divided into sections, including the Web Forms, Components, HTML, and other sections. Click the Web Forms section.
In the Web Forms section of the toolbox, click DataGrid, and then drag it onto the designer for WebForm1.
Right-click WebForm1.aspx, and then click View Code to display the code-behind page source.
Add the following statements to the top of the code-behind page, above the "Public Class WebForm1" text:
```
Imports System.Data
Imports System.Data.OleDb
                    
```

Highlight the following code, right-click the code, and then click Copy. In WebForm1.aspx.vb, paste the code into the Page_Load event:

' Create variables that are used in code sample.
Dim i, j As Integer

' Create connection string variable. Modify the "Data Source" parameter as
' appropriate for your environment.
Dim sConnectionString As String = "Provider=Microsoft.Jet.OLEDB.4.0;" _
            & "Data Source=" & Server.MapPath("../ExcelData.xls") _
            & ";" & "Extended Properties=Excel 8.0;"

' Create the connection object by using the preceding connection string.
Dim objConn As New OleDbConnection(sConnectionString)

' Open connection with the database.
objConn.Open()

' The code to follow uses a SQL SELECT command to display the data from the worksheet.

' Create new OleDbCommand to return data from worksheet.
Dim objCmdSelect As New OleDbCommand("SELECT * FROM myRange1", objConn)

' Create new OleDbDataAdapter that is used to build a DataSet 
' based on the preceding SQL SELECT statement.
Dim objAdapter1 As New OleDbDataAdapter()

' Pass the Select command to the adapter.
objAdapter1.SelectCommand = objCmdSelect

' Create new DataSet to hold information from the worksheet.
Dim objDataset1 As New DataSet()

' Fill the DataSet with the information from the worksheet.
objAdapter1.Fill(objDataset1, "XLData")

' Build a table from the original data.
DataGrid1.DataSource = objDataset1.Tables(0).DefaultView
DataGrid1.DataBind()

' Clean up objects.
objConn.Close()

On the File menu, click Save All to save the project files.
On the Build menu, click Build ExcelVBTest to build the project. This prepares the code in the code-behind page so that it can be executed.
In Solution Explorer, right-click WebForm1.aspx, and then click View in Browser to run the code.

back to the top

Additional Code Explanation

The code sample in this article uses the Microsoft Jet OLE DB Provider to access the Excel worksheet. This code uses the following connection string to connect to the worksheet:

' Create connection string variable. Modify the "Data Source" parameter as
' appropriate for your environment.
Dim sConnectionString As String = "Provider=Microsoft.Jet.OLEDB.4.0;" _
            & "Data Source=" & Server.MapPath("../ExcelData.xls") _
            & ";" & "Extended Properties=Excel 8.0;"

As the comments indicate, you must modify the path information for the specific Excel worksheet. In addition, you must set the value of the Extended Properties parameter to properly connect to the file.

Note that the connection string uses the Server.MapPath function. This function takes a path that is relative to Microsoft Internet Information Services (IIS) to a file and returns a hard disk path to that file. For example, in the Create Sample Excel Worksheet section, you create ExcelData.xls in the Web root directory, which is typically located at C:\Inetpub\Wwwroot. This also creates a subfolder named ExcelVBTest within the Wwwroot folder and a file named WebForm1.aspx within the ExcelVBTest folder.

In this example, the hard disk path to the files is as follows:

     C drive
        - Inetpub
            - Wwwroot (which contains ExcelData.xls)
                - ExcelVBTest (which contains WebForm1.aspx)

The IIS path to the files is as follows:

     Web Root (which contains ExcelData.xls)
        - ExcelVBTest (which contains WebForm1.aspx)

In this case, the relative path from the WebForm1.aspx page to the ExcelData.xls file is "../ExcelData.xls". The "../" characters inform IIS to go up one folder level. Therefore, the code

Server.MapPath("../ExcelData.xls")

returns the following string:

C:\Inetpub\Wwwroot\ExcelData.xls

You are not required to use Server.MapPath. You can also hard code this information to a specific path, or you can use any method to supply the location of the Excel file on the hard disk.

back to the top

REFERENCES

For additional information about how to access Excel files with ASP.NET, click the following article numbers to view the articles in the Microsoft Knowledge Base:

307021 How To Transfer XML Data to Microsoft Excel 2002 by Using Visual Basic .NET

306022 How To Transfer Data to an Excel Workbook by by Using Visual Basic .NET

For additional information about using ADO.NET, click the following article numbers to view the articles in the Microsoft Knowledge Base:

301075 How To Connect to a Database and Run a Command by Using ADO.NET and Visual Basic .NET

301216 How To Populate a DataSet Object from a Database by Using Visual Basic .NET

301248 How To Update a Database from a DataSet Object by Using Visual Basic .NET

Note The example companies, organizations, products, domain names, e-mail addresses, logos, people, places, and events depicted herein are fictitious. No association with any real company, organization, product, domain name, email address, logo, person, places, or events is intended or should be inferred.

back to the top

Additional query words: spreadsheet

Keywords: kbhowtomaster kbwebforms kbdatabase KB311731

Microsoft KB Archive/311731

Contents

How To Query and Display Excel Data by Using ASP.NET, ADO.NET, and Visual Basic .NET

IN THIS TASK

SUMMARY

Create Sample Excel Worksheet

Create ASP.NET Sample Using Visual Basic .NET

Additional Code Explanation

REFERENCES

	A	B
1	FirstName	LastName
2	Scott	Bishop
3	Katie	Jordan