This article is a step-by-step demonstration of how to query and display excel data in the Gridview Control using ASP.NET and ADO.NET.  We will use C# and Visual Basic 2005 languages for the coding.

Visual Studio 2005

Step 1 – Create Excel worksheet

1) Open Microsoft Excel and create a new Worksheet.

2) The sample data is as follows.

We will keep the default name for the Worksheet (Sheet1).

3) Let us name and save the excel file as Running.xls.

Note: You can download the Excel file along with the code files from the Downloads section at the end of this article.

Step 2 –Display Excel Data using GridView Control

1) Start Visual Studio 2005.

2) Select Create Website and choose the Template ASP.NET Web Site.  We can choose the language as C#/VB.  Set the name for the project as ExcelGV.

3) Add the existing Excel file (Running.xls) that we created to the App_data folder.

4) Add a new .aspx file.  Choose language C#/VB and, depending on the language, we will set the appropriate names: ExcelGVCS.aspx for C# or ExcelGVVB for VB.NET.

4) Drag and drop the GridView control on the .aspx page.

5) We will add the following statement in code behind above the namespace section.

Listing 1

[Visual Basic 2005]

Imports System.Data
Imports System.Data.OleDb

[C#]

using System.Data.OleDb;

6) Below code goes in the Page_Load:.

Listing 2

[Visual Basic 2005]

Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs)
        Dim DBConnection = New OleDbConnection( _
          "Provider=Microsoft.Jet.OLEDB.4.0;" & _
          "Data Source=" & Server.MapPath("~/App_Data/Running.xls") & ";" & _
          "Extended Properties=""Excel 8.0;HDR=Yes""")
  DBConnection.Open()
  Dim SQLString As String = "SELECT * FROM [Sheet1$]"
  Dim DBCommand = New OleDbCommand(SQLString, DBConnection)
  Dim DBReader As IDataReader = DBCommand.ExecuteReader()
  GridView1.DataSource = DBReader
  GridView1.DataBind()
  DBReader.Close()
  DBConnection.Close()
End Sub

[C#]

protected void Page_Load(object sender, EventArgs e)
{
  OleDbConnection DBConnection = new
     OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" +
     Server.MapPath("~/App_Data/Running.xls") + ";" + "Extended
     Properties=\"Excel 8.0;HDR=Yes\"");
  DBConnection.Open();
  string SQLString = "SELECT * FROM [Sheet1$]";
  OleDbCommand DBCommand = new OleDbCommand(SQLString, DBConnection);
  IDataReader DBReader = DBCommand.ExecuteReader();
  GridView1.DataSource = DBReader;
  GridView1.DataBind();
  DBReader.Close();
  DBConnection.Close();
}

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.

[Download Sample]

Note: Place the downloaded file inside the root directory (Inetpub/wwwroot) and use Visual Studio 2005 to execute the project.

This sample shows how to query excel and display the data in GridView Control.  We can choose to display specific columns by modifying the SQL String in the Select clause with required columns or by using GridView's BoundField or TemplateField Columns.