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 Asp.net 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>
<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" />
</asp:GridView>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
FileUpload1.SaveAs(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;
break;
case ".xlsx": //Excel 07
conStr = ConfigurationManager.ConnectionStrings["Excel07ConString"].ConnectionString;
break;
}
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
connExcel.Open();
DataTable dtExcelSchema;
DataTable dt = new DataTable();
dtExcelSchema = connExcel.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
string SheetName = dtExcelSchema.Rows[0]["TABLE_NAME"].ToString();
connExcel.Close();
//Read Data from First Sheet
connExcel.Open();
cmdExcel.CommandText = "SELECT * From [" + SheetName + "]";
oda.SelectCommand = cmdExcel;
oda.Fill(dt);
connExcel.Close();
//Bind Data to GridView
GridView1.Caption = Path.GetFileName(FilePath);
GridView1.DataSource = dt;
GridView1.DataBind();
}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;
GridView1.DataBind();
}