Description:-
In this
Example we Explain that How to Fetch Record or Data From ExcelSheet and Bind
into the Gridview . as we already know how to Fetch Data from SQlServer
Database and Binding the Gridview.but it
is some Difficult task to Retrieve Data From the Excel Database.
Here we have to use OleDB Connection instead of Sql
Connection. In this Example We Retrieve All Data From the ExcelSheet and Bind
into the Gridview and We also Bind All id’s of each Record in DropDownList. When
user Select id From the DropDownList at that time only this Record are Bind
Into the GridView.
That is the Image of the ExcelSheet database and Gridview to Bind Excel database are as shown Below.
So,How to Fetch or Retrieve Data or How to Create Connection
to the Excel database are shown in this Example.
to show Example of How to Export Gridview Data or row to PDF Export Gridview Data to PDF
how to sorting Data in Gridview Sorting Row in Gridview
how to upload File and Bind to Gridview upload file in Database and bind to Gridview
to show Example of How to Export Gridview Data or row to PDF Export Gridview Data to PDF
how to sorting Data in Gridview Sorting Row in Gridview
how to upload File and Bind to Gridview upload file in Database and bind to Gridview
To Download the Complete Project Click on the Below Download Image Link
Grid.aspx:-
<%@ Page Language="C#" AutoEventWireup="true" CodeFile="Excel to
Grid.aspx.cs" Inherits="Excel_to_Grid" %>
<!DOCTYPE html>
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
<title></title>
</head>
<body>
<form id="form1" runat="server">
<div>
<h1>Example of export Excel data to Gridview</h1>
<asp:DropDownList ID="ddlSlno" runat="server"
OnSelectedIndexChanged="ddlSlno_SelectedIndexChanged"
AutoPostBack="true" AppendDataBoundItems="True">
<asp:ListItem Selected="True"
Value="Select">- Select
-</asp:ListItem>
</asp:DropDownList>
<asp:GridView ID="grvData" runat="server">
</asp:GridView>
<asp:Label ID="lblError" runat="server" />
</div>
</form>
</body>
</html>
Grid.aspx.cs:-
using System;
using System.Data.OleDb;
using System.Data;
using System.IO;
public partial class Excel_to_Grid : System.Web.UI.Page
{
OleDbConnection oledbConn;
protected void Page_Load(object sender, EventArgs e)
{
if (!IsPostBack)
{
GenerateExcelData("Select");
}
}
protected void ddlSlno_SelectedIndexChanged(object sender, EventArgs e)
{
GenerateExcelData(ddlSlno.SelectedValue);
}
private void GenerateExcelData(string SlnoAbbreviation)
{
try
{
string path = System.IO.Path.GetFullPath("E:exceltogrid.xlsx");
if (Path.GetExtension(path) == ".xls")
{
oledbConn = new OleDbConnection(@"Provider=Microsoft.Jet.OLEDB.4.0;
Data Source=" + path + "; Extended
Properties=\"Excel 8.0;HDR=Yes;IMEX=2;';");
}
else if (Path.GetExtension(path) == ".xlsx")
{
oledbConn = new OleDbConnection(@"Provider=Microsoft.ACE.OLEDB.12.0;
Data Source=" + path + "; Extended
Properties='Excel 12.0;HDR=YES;IMEX=1;';");
}
oledbConn.Open();
OleDbCommand cmd = new OleDbCommand(); ;
OleDbDataAdapter oleda = new OleDbDataAdapter();
DataSet ds = new DataSet();
cmd.Connection = oledbConn;
cmd.CommandType = CommandType.Text;
cmd.CommandText = "SELECT distinct([id]) FROM [Sheet1$]";
oleda = new OleDbDataAdapter(cmd);
oleda.Fill(ds, "id");
ddlSlno.DataSource = ds.Tables["id"].DefaultView;
if (!IsPostBack)
{
ddlSlno.DataTextField = "id";
ddlSlno.DataValueField = "id";
ddlSlno.DataBind();
}
if (!String.IsNullOrEmpty(SlnoAbbreviation) &&
SlnoAbbreviation != "Select")
{
cmd.CommandText = "SELECT [id], [name], [age]" +
" FROM
[Sheet1$] where [id]= @Slno_Abbreviation";
cmd.Parameters.AddWithValue("@Slno_Abbreviation", SlnoAbbreviation);
}
else
{
cmd.CommandText = "SELECT [id], [name],[age] FROM [Sheet1$]";
}
oleda = new OleDbDataAdapter(cmd);
oleda.Fill(ds);
grvData.DataSource =
ds.Tables[1].DefaultView;
grvData.DataBind();
}
catch (Exception ex)
{
lblError.Text = ex.ToString();
}
finally
{
oledbConn.Close();
}
}
}
0 comments:
Post a Comment