How to query and display Excel data using ASP.net, ado.net, and Visual C #. NET

ado|asp.net|excel|visual| Data | Show create a sample Excel worksheet
Start Microsoft Excel, and then create a new worksheet.
Add the following information to a 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 with cell A1, you can add this data to any adjacent cells in the worksheet.
Highlight the row and column in which this data resides.
On the Insert menu, point to the name, and then click Define.
In the name in the current Workbook text box, type MyRange1, and then click OK.
On the File menu, click Save. In the Save in list, select the Web server's root directory (usually C:\InetPub\Wwwroot\). In the File Name text box, type ExcelData.xls. Click OK.
On the File menu, single repel out.
Back to the top of the page

To create a asp.net sample using Visual C #. NET
This code example shows 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 Development Environment (IDE) is displayed.
On the File menu, point to New, and then click Item.
Under the project type of the new Project dialog box, click the Visual C # project. Under the template, click the ASP.net Web application.
In the New Project dialog box, locate the name and location text box. Note that the Name text box is not available (it appears dimmed). The Location text box contains the following text (or similar text):
Http://localhost/WebApplication1

Replace the text in the Location text box with Http://localhost/ExcelCSTest, and then click OK. A new project is created that includes a Web form named WebForm1.aspx.
In the Visual Studio. NET IDE, locate the Solution Explorer window. If you cannot find the window, click Solution Explorer on the View menu.
In Solution Explorer, right-click WebForm1.aspx, and then click View Designer to display the designer that is used to design the appearance of the page. You can use the designer to add controls and work with the appearance of the page.
Find the tool box. Depending on your IDE options settings, the Toolbox may appear as a window or a button (usually on the left side of the IDE). If the toolbox is not found, click the Toolbox on the View menu.

If the toolbox appears as a button, move the pointer over the button to display the contents of the toolbox.
When the designer view of a Web form is active, the toolbox is divided into sections, including Web Forms, components, HTML, and other parts. Click the Web forms section.
In the Web Forms section of the Toolbox, click the DataGrid, and then drag it to the WebForm1 designer.
Right-click WebForm1.aspx, and then click View Code to display the source code for the code-behind page.
Add the following statement above the namespace section at the top of the code-behind page:
Using System.Data.OleDb;
Using System.Data;

Highlight the following code, right-click the code, and then click Copy. In WebForm1.aspx.cs, copy the 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;";

The 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 are 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 the DataGrid control.
DataGrid1.DataSource = Objdataset1.tables[0]. DefaultView;
Datagrid1.databind ();

Clean up objects.
Objconn.close ();

In the File menu, click Save All to save the project file.
On the Build menu, click Build 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 the browser to run the code.
Back to the top of the page

Other code Descriptions
The sample code in this article accesses an EXCEL worksheet using the Microsoft Jet OLE DB provider. 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 note, you must modify the path information for a specific Excel worksheet. In addition, you must set the value of the Extended Properties parameter so that you can connect to the file correctly.

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

In this example, the path to the file on the hard disk is as follows:
C Drive
-Inetpub
-Wwwroot (which contains ExcelData.xls)
-ExcelCSTest (including WebForm1.aspx)

The IIS path to the file is as follows:
Web root directory (which contains ExcelData.xls)
-ExcelCSTest (including WebForm1.aspx)

In this case, the relative path to the WebForm1.aspx page to the ExcelData.xls file is "... /exceldata.xls ". “.. /"character notifies IIS to go to the folder on the previous level. Therefore, the code
Server.MapPath (".. /exceldata.xls ")

Returns the following string:
C:\Inetpub\Wwwroot\ExcelData.xls

You do not need to use Server.MapPath. You can also hard-code this information as a specific path, or use any method to provide the location of the Excel file on your hard disk.