AspAlliance.com LogoASPAlliance: Articles, reviews, and samples for .NET Developers
URL:
http://aspalliance.com/articleViewer.aspx?aId=958&pId=-1
CodeSnip: How to Query and Display Excel Data in GridView Control
page
by Sushila Bowalekar Patel
Feedback
Average Rating: 
Views (Total / Last 10 Days): 31893/ 48

Introduction

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.

Requirement

Visual Studio 2005

How To

Step 1 – Create Excel worksheet

1) Open Microsoft Excel and create a new Worksheet.

2) The sample data is as follows.

Date

Dist

Type

Surface

Period

4/15/2006

4 mi

Almost Flat

Pavement, Black

40 min

4/17/2006

4 mi

Almost Flat

Treadmill

40 min

4/19/2006

4 mi

Almost Flat

Pavement, Black

38 min

4/21/2006

3.75 mi

Almost Flat

Pavement, Black

36 min

4/23/2006

4 mi

Almost Flat

Pavement, Black

36 min

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 ObjectByValAs 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.

Downloads

[Download Sample]

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

Conclusion

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.


Product Spotlight
Product Spotlight 

©Copyright 1998-2021 ASPAlliance.com  |  Page Processed at 2021-02-27 11:15:06 PM  AspAlliance Recent Articles RSS Feed
About ASPAlliance | Newsgroups | Advertise | Authors | Email Lists | Feedback | Link To Us | Privacy | Search