CodeSnip: How to Query and Display Excel Data in GridView Control
 
Published: 18 Aug 2006
Unedited - Community Contributed
Abstract
This article shows how to display data from an Excel worksheet in GridView Control using ASP.NET 2.0.
by Sushila Bowalekar Patel
Feedback
Average Rating: 
Views (Total / Last 10 Days): 31893/ 47

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.



User Comments

Title: Thanx   
Name: Venkat
Date: 2007-06-08 11:07:54 AM
Comment:
Excellent worked first time itself thanx
Title: 10x!   
Name: Amit
Date: 2007-06-05 4:04:37 AM
Comment:
Excellent code!.
A succefull in the first time!
Title: Problem   
Name: Tony Ngo
Date: 2007-04-10 4:30:10 PM
Comment:
It's all work fine except if you column have the value '12345678' then the filed will be displayed as 1.23e+7
Title: How to Query and Display Excel Data in GridView Control   
Name: Sushila Bowalekar Patel
Date: 2007-04-08 1:00:21 AM
Comment:
Hi,
this was very helpful to me while i doing one of my friends assignment.
thanks a lot

eric
Title: sheet's name ?   
Name: CarlosW
Date: 2007-02-16 10:59:28 AM
Comment:
And what happen if i don't know the sheet's name ? how can i solve this problem ?
Title: WHY THIS KIND OF ERROR?   
Name: DYANETHI MARAN PONTATI
Date: 2007-01-24 2:07:14 AM
Comment:
'Sheet1$' is not a valid name. Make sure that it does not include invalid characters or punctuation and that it is not too long.
Title: Mr.   
Name: NimeX
Date: 2007-01-05 2:36:18 AM
Comment:
Can we also enable paging with this??
Title: introlux   
Name: Arif
Date: 2006-11-20 6:26:31 AM
Comment:
Really good code! top man! So easy to folow instructions! Wish everyone could demonstrate code like yourself!

thanks once again
Title: Mr.   
Name: Vinod Merchant
Date: 2006-11-13 7:21:17 PM
Comment:
This is very good
Where can I find more such articles
Vinod
vinodmerch@yahoo.co.uk

Product Spotlight
Product Spotlight 





Community Advice: ASP | SQL | XML | Regular Expressions | Windows


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