CodeSnip: How to Query and Display Excel Data in GridView Control
page 3 of 5
by Sushila Bowalekar Patel
Feedback
Average Rating: 
Views (Total / Last 10 Days): 31882/ 56

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.


View Entire Article

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-03-03 3:32:49 PM  AspAlliance Recent Articles RSS Feed
About ASPAlliance | Newsgroups | Advertise | Authors | Email Lists | Feedback | Link To Us | Privacy | Search