How to use ASP.NET, ADO.NET, and Visual C # .NET query and display Excel data
Suitable
For Microsoft Visual Basic .NET versions of this article, see
311731.
This article refers to the following Microsoft .NET Framework class library namespace:
System.Data.Oledb
This task content
summary
Create an example Excel worksheet Use Visual C # .NET Create an ASP.NET Example Other Code Description Reference
SUMMARY This article describes how to use Visual C # .NET to display data in the Excel worksheet via an ASP.NET (.ASPX) page.
Back to top
Create an example Excel worksheet
Start Microsoft Excel and create a new worksheet. Add the following information to the new worksheet to create a simple Excel database:
Ab1FirstnameLastName2Scottbishop3katiejordan Note: Although the data starts from cell A1 in this example, you can add this data to any adjacent cells in the worksheet. Highlight the rows and columns of this data. On the Insert menu, point to the name, then click Definition. In the "Name in the current workbook" text box, type myrange1, and then click OK. On the File menu, click Save. 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. Click OK. On the File menu, click Exit.
Back to top
Creating an ASP.NET example using Visual C # .NET This code shows how to query and display information in the Excel worksheet. The following code uses the worksheet created in the previous section.
Open Microsoft Visual Studio .NET. The Visual Studio .NET integration development environment (IDE) will be displayed. On the File menu, point to New, and then click Project. Below the Project Type of New Project Dialog, click Visual C # items. Below the template, click the ASP.NET web application. In the New Project dialog box, find the name and location text box. Note that the name text box is not available (it is displayed as gray). The location text box contains the following text (or a similar text): http: // localhost / webapplication1 replaces the text in the location text box to http: // localhost / excelcstest, and then click OK. A new project is created, including a web form called WebForm1.aspx. In Visual Studio .NET IDE, find the Solution Explorer window. If you can't find this window, click the Solution Explorer on the View menu. In the Solution Explorer, right-click WebForm1.aspx, and then click the View Designer to display the designer for design page appearance. You can use this designer, add controls and process the appearance of the page. Find toolbox. Depending on your "IDE Options" setting, the toolbox may appear in the form of a window or button (usually on the left side of the IDE). If you can't find your toolbox, click Toolbox on the View menu. If the toolbox appears in the form of a button, move the pointer to the button to display the contents of the toolbox. When the designer view of the web form is active, the toolbox is divided into several parts, including web forms, components, HTML, and other parts. Click the Web Form section. In the Web Form section of the Toolbox, click DataGrid and drag it to the designer of WebForm1. Right-click Webform1.aspx, then click View Code to display the source code of the code hidden page. Add the following statement to the namespace part of the top of the code hidden page: use system.Data.OleDb; use system.data;
Highlight the following code, right-click the code, and then click Copy. In WebForm1.aspx.cs, copy these code to the page_load event: // 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 (SCONNACTIONSTRING);
// Open Connection with the Database.
Objconn.open ();
// The code to follow buys 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 buy 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 ();
In the File menu, click Save to save the project file. On the Generative menu, click Generate to Generate Projects. This is ready to hide the code in the hidden page, so that it can be implemented. In the Solution Explorer, right-click WebForm 1.aspx, and then click View to run the code in your browser.
Back to top
Other Codes Description This article uses the Microsoft Jet OLE DB provider to access the Excel worksheet. This code is connected to the worksheet using the following connection string:
// 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 annotation, you must modify the path information of a particular Excel worksheet. In addition, you must also set
The value of the Extended Properties parameter is properly connected to the file.
Note that the connection string is used
Server.mappath function. This function uses a file relative to the path to the Microsoft Internet Information Service (IIS) and returns the hard disk path of the file. For example,
Create an example Excel Worksheet section, you created an Exceldata.xls in the web root directory, which is usually located in C: / INETPUB / WWWROOT. This also creates a subfolder called ExcelCstest in the wwwroot folder and creates a file called Webform1.aspx in the ExcelCstest folder. In this example, the file path on the hard disk is as follows:
C drive
- inetpub
- wwwroot (which contains ExcelData.xls)
- ExcelCstest (including WebForm1.aspx)
The IIS path of the file is as follows:
Web root directory (which contains ExcelData.xls)
- ExcelCstest (including WebForm1.aspx)
In this example, the WebForm1.aspx page is of the ExcelData.xls file.
The relative path is "../exceldata.xls". "../" character notifies IIS to go to the previous folder. Therefore, the code
Server.mappath ("../ ExcelData.xls")
Returns the following string:
C: /inetpub/wwroot/exceldata.xls you don't need to use
Server.mappath. You can also hardcode this information as a specific path, or use any way to provide the location of the Excel file on the hard disk.
Back to top
Refer to additional information about accessing an Excel file using ASP.NET, click the article number below to see the article in the Microsoft Knowledge Base:
307029 How to use Visual C # .NET to transmit XML data to Microsoft Excel 2002
306023 How to use Visual C # .NET to Transport Data to Excel Workbook For additional information about using ADO.NET, click the article number below to view the article in the Microsoft Knowledge Base:
306636 How to connect to the database using ADO.NET and Visual C # .NET and run commands
314145 How to use Visual C # .NET from Database Pack DataSet object
307587 How to use Visual C # .NET from Data Set Object Update Database
Note: The company, organization, product, domain name, email address, logo, name, place name, and events are completely fictional. Unintentional, should not be used to speculate any real company, organization, product, domain name, email address, logo, name, place name, and events.
Back to top
The information in this article applies to:
Microsoft Excel 2002 Microsoft Excel 2000 Microsoft ASP.NET (included with the .NET Framework 1.1) Microsoft ASP.NET (included with the .NET Framework) 1.0 Microsoft Visual C # .NET (2003) Microsoft Visual C # .NET (2002) Microsoft ADO .NET (Included with the .NET Framework 1.1) Microsoft ADO.NET (Included with the .NET FRAMEWORK)
