Use ASP. NET (C #) to query and display Excel Data

Create an Excel worksheet

1.	Start Microsoft Excel and create a new worksheet.
2.	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 in this example starts from cell A1, you can add the data to any adjacent cells in the worksheet.
3.	Highlight the row and column of the data.
4.	InInsertMenu, pointingNameAnd then clickDefinition.
5.	In the "name in the current workbook" text box, type myrange1, and then clickOK.
6.	InFileClickSave. In the "Save location" list, select the root directory of the Web server (usually c: \ Inetpub \ wwwroot \). In the "file name" text box, type exceldata.xls. ClickOK.
7.	InFileClickExit.

Back to Top

Use Visual C #. Net to create an ASP. NET example

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

1.	Open Microsoft Visual Studio. NET. The Visual Studio. NET integrated development environment (IDE) is displayed ).
2.	InFileMenu, pointingNewAnd then clickProject.
3.	InCreate a projectDialog BoxProject TypeClickVisual C # project. InTemplateClickASP. NET web applications.
4.	InCreate a projectIn the dialog box, findNameAndLocationText Box. Note,NameThe text box is unavailable (it is displayed in gray ).LocationThe text box contains the following text (or similar text ): Http: // localhost/webapplication1 SetLocationReplace the text in the text box with http: // localhost/excelcstest, and then clickOK. A new project is created, including a web form named webform1.aspx.
5.	In Visual Studio. net ide, find the Solution Explorer window. If the window cannot be found, clickViewOn the menuSolution Resource Manager.
6.	In Solution Explorer, right-clickWebform1.aspxAnd then clickView designerTo display the designer used to design the page appearance. You can use this designer to add controls and process the appearance of the page.
7.	Find the toolbox. Depending on your ide options, the toolbox may appear in the form of a window or button (usually on the left side of the IDE ). If you cannot find the toolbox, clickViewOn the menuToolbox. If the Toolbox appears as a button, move the pointer to this button to display the Toolbox content.
8.	When the designer view of a web form is active, the toolbox is divided into several parts, including the web form, component, HTML, and other parts. Click the web form section.
9.	In the web forms section of the toolbox, clickDataGridAnd then drag it to the webform1 designer.
10.	Right-clickWebform1.aspxAnd then clickView codeTo show the source code of the code hidden page.
11.	Add the following statement to the namespace section at the top of the Code hiding page: using System.Data.OleDb;using System.Data;
12.	Highlight the following code, right-click the code, and then clickCopy. Copy the code to webform1.aspx. CSPage_loadEvent: // Create connection string variable. Modify the "Data Source"// parameter as appropriate for your environment.String sConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" +"Data Source=" + Server.MapPath("../ExcelData.xls") + ";" +"Extended Properties=Excel 8.0;";// Create connection object by using the preceding connection string.OleDbConnection objConn = 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.OleDbCommand objCmdSelect =new OleDbCommand("SELECT * FROM myRange1", objConn);// Create new OleDbDataAdapter that is used to build a DataSet// based on the preceding SQL SELECT statement.OleDbDataAdapter objAdapter1 = new OleDbDataAdapter();// Pass the Select command to the adapter.objAdapter1.SelectCommand = objCmdSelect;// Create new DataSet to hold information from the worksheet.DataSet objDataset1 = new DataSet();// Fill the DataSet with the information from the worksheet.objAdapter1.Fill(objDataset1, "XLData");// Bind data to DataGrid control.DataGrid1.DataSource = objDataset1.Tables[0].DefaultView;DataGrid1.DataBind();// Clean up objects.objConn.Close();
13.	InFileIn the menu, clickSave allTo save the project file.
14.	InGenerateClickGenerateTo generate a project. In this case, the code in the Code hiding page is ready for execution.
15.	In Solution Explorer, right-clickWebform1.aspxAnd then clickView in a browserTo run the code.

Back to Top

Other Code Description

The sample code 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.String sConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" +"Data Source=" + Server.MapPath("../ExcelData.xls") + ";" +"Extended Properties=Excel 8.0;";

As described in the comment, the path information of a specific Excel worksheet must be modified. You must also setExtended PropertiesParameter Value to correctly connect to the file.

Note: Use the connection stringServer. mappathFunction. This function uses the path of the file relative to Microsoft Internet Information Service (IIS) and returns the hard disk path of the file. For example, in the worksheet section of the example Excel file, you have created exceldata.xls in the web root directory, which is usually located in c: \ Inetpub \ wwwroot. This will also create a subfolder named excelcstest in the wwwroot folder, and create a file named webform1.aspx in the excelcstest folder.

In this example, the file path on the hard disk is as follows:

C drive-inetpub-wwwroot (including exceldata.xls)-excelcstest (including webform1.aspx)

The IIS path of the file is as follows:

Web root directory (including exceldata.xls)-excelcstest (including webform1.aspx)

In this example, go toRelativePath: ../exceldata.xls ". The "../" character notifies IIS to go to the upper-level folder. Therefore, the code

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

Returns the following string:

C: \ Inetpub \ wwwroot \ exceldata.xls

You do not need to useServer. mappath. You can also hard encode this information into a specific path, or provide the location of the Excel file on the hard disk in any way.

Back to Top
For more information about using ASP. NET to access an Excel file, click the following article number to view the article in the Microsoft Knowledge Base:

307029 How to Use Visual C #. Net to transfer XML data to Microsoft Excel 2002 306023 How to Use Visual C #. Net to transfer data to an Excel Workbook

For other information about using ADO. net, click the following article number to view the article in the Microsoft Knowledge Base:

306636 how to use ADO. net and Visual C #. net to connect to the database and Run Command 314145 How to Use Visual C #. net from the database fill DataSet object 307587 How to Use Visual C #. net updates the database from a DataSet object

Note:The companies, organizations, products, domain names, email addresses, logos, names, place names, and events cited in this example are completely fictitious. No shadow is intended, and no real company, organization, product, domain name, email address, logo, person name, place name, or event should be tested.