Read Excel Sheet Data and Display in Gridview by Using C# Coding

In these Article i'm trying to explain about "How to Read Data from Excel Sheet and Display Excel Data into Gridview by using C# Coding". And also show how to manipulate excel sheet data in gridview control by using RowEditing Event and RowUpdateing Event in Gridview.

Design Page :

First, Add Control's to HTML Page

1.) FileUpload Control : The FileUpload Control allows the user to browse for and select the file to be uploaded
2.) Button Control : Once Button Click Event fired its read data from selected excel sheet data and display in Gridview.
3.) RadioButtonList : Its used for Excel Sheet Data Header Column to Display or Not.
4.) GridView Control : Grdiview Control is used to dispaly data in tabular format and also shown page numbers.

HTML Code :

<asp:FileUpload ID="FileUpload1" runat="server" BorderColor="#999999"
BorderStyle="Solid" BorderWidth="1px" Height="25px" Width="250px" />
<asp:Button ID="btnUpload" runat="server" Text="Upload" OnClick="btnUpload_Click" />
<br />
<asp:Label ID="Label1" runat="server" Text="Has Header ?" /><br /><br />
<asp:RadioButtonList ID="rbHDR" runat="server" RepeatDirection="Horizontal">
<asp:ListItem Text = "Yes" Value = "Yes" Selected = "True" >
<asp:ListItem Text = "No" Value = "No"></asp:ListItem>
</asp:RadioButtonList><br />
<asp:GridView ID="GridView1" runat="server" OnPageIndexChanging = "PageIndexChanging"
AllowPaging = "True" BackColor="White" BorderColor="#3366CC" BorderStyle="None"
BorderWidth="1px" CellPadding="4">
<FooterStyle BackColor="#99CCCC" ForeColor="#003399" />
<HeaderStyle BackColor="#003399" Font-Bold="True" ForeColor="#CCCCFF" />
<PagerStyle BackColor="#99CCCC" ForeColor="#003399" HorizontalAlign="Left" />
<RowStyle BackColor="White" ForeColor="#003399" />
<SelectedRowStyle BackColor="#009999" Font-Bold="True" ForeColor="#CCFF99" />
<SortedAscendingCellStyle BackColor="#EDF6F6" />
<SortedAscendingHeaderStyle BackColor="#0D4AC4" />
<SortedDescendingCellStyle BackColor="#D6DFDF" />
<SortedDescendingHeaderStyle BackColor="#002876" />

Namespaces :

before writing code, first we need to add namespace

using System;
using System.Collections.Generic;
using System.Linq;
using System.IO;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Configuration;
using System.Data;
using System.Data.OleDb;

Now, to write this code Double Click on Button to Upload selected file from FileUploader

protected void btnUpload_Click(object sender, EventArgs e)
if (FileUpload1.HasFile)
// this FileName is used to get name of the uploaded file from FileUpload
string FileName = Path.GetFileName(FileUpload1.PostedFile.FileName);
// this Extension is used to get .ext of the uploaded file from FileUpload to validate is that uploaded file is excel or not.
string Extension = Path.GetExtension(FileUpload1.PostedFile.FileName);
// this folderpath is indicate the internal path of excel file stored location
string FolderPath = ConfigurationManager.AppSettings["FolderPath"];
// FilePath is used to get New Path of Internal Stored Location with Filename
string FilePath = Server.MapPath(FolderPath + FileName);
//FileUploaded file will be saved in New Location which indicate the FilePath
FillGrid(FilePath, Extension, rbHDR.SelectedItem.Text);

Fill Gridview

private void FillGrid(string FilePath, string Extension, string isHDR)
string conStr = "";
switch (Extension)
case ".xls": //Excel 97-03
conStr = ConfigurationManager.ConnectionStrings["Excel03ConString"].ConnectionString;
case ".xlsx": //Excel 07
conStr = ConfigurationManager.ConnectionStrings["Excel07ConString"].ConnectionString;
conStr = String.Format(conStr, FilePath, isHDR);
OleDbConnection connExcel = new OleDbConnection(conStr);
OleDbCommand cmdExcel = new OleDbCommand();
OleDbDataAdapter oda = new OleDbDataAdapter();

cmdExcel.Connection = connExcel;

//Get the name of First Sheet
DataTable dtExcelSchema;
DataTable dt = new DataTable();
dtExcelSchema = connExcel.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
string SheetName = dtExcelSchema.Rows[0]["TABLE_NAME"].ToString();

//Read Data from First Sheet
cmdExcel.CommandText = "SELECT * From [" + SheetName + "]";
oda.SelectCommand = cmdExcel;

//Bind Data to GridView
GridView1.Caption = Path.GetFileName(FilePath);
GridView1.DataSource = dt;

Gridview PageIndexChanging Event Code :

protected void PageIndexChanging(object sender, GridViewPageEventArgs e)
string FolderPath = ConfigurationManager.AppSettings["FolderPath"];
string FileName = GridView1.Caption;
string Extension = Path.GetExtension(FileName);
string FilePath = Server.MapPath(FolderPath + FileName);

FillGrid(FilePath, Extension, rbHDR.SelectedItem.Text);
GridView1.PageIndex = e.NewPageIndex;

Related Articles

More articles: Gridview Export to excel sql queries excel Microsoft.Office.Interop.Excel Dll Create Data in Excel Create excel file Control Excel From Visual Basic.Net. Bind Excel Data to Dataset Add Contents to Excel Sheet From VB.Net .net Excel C# Excel


No responses found. Be the first to comment...

  • Do not include your name, "with regards" etc in the comment. Write detailed comment, relevant to the topic.
  • No HTML formatting and links to other web sites are allowed.
  • This is a strictly moderated site. Absolutely no spam allowed.
  • Name: