Hi in this post i will show how to bind data in excel to gridview in asp.net.
1. This is my Excel data. I will bind this below Excel data to grid
3. Result:
1. This is my Excel data. I will bind this below Excel data to grid
2. Using a OleDb Connection we will get the data from Excel and then bind that data to the GridView.
Below is the code:
<!--ASPX-->
<%@ Page Language="C#" AutoEventWireup="true" CodeFile="ExcelToGrid.aspx.cs" Inherits="ExcelToGrid" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
<title></title>
</head>
<body>
<form id="form1" runat="server">
<div>
<asp:GridView ID="GridView1" runat="server">
</asp:GridView>
</div>
</form>
</body>
</html>
//CodeBehind :
using System;
using System.Web;
using System.Web.UI;
using System.Data;
using System.Data.SqlClient;
using System.Data.OleDb;
public partial class ExcelToGrid : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{
DataTable Ds = GetDataFromExcel(@"C:\Users\chandan.singh\Desktop\Test.xls", "Sheet1");
BindGrid(Ds);
}
public OleDbConnection GetExcelCon(string strFilePath)
{
OleDbConnection excelcon = new OleDbConnection();
try
{
excelcon = new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;" +
"Data Source=" + strFilePath + ";Jet OLEDB:Engine Type=5;" +
"Extended Properties='Excel 8.0;IMEX=1;'");
}
catch (Exception ex)
{
Response.Write(ex.Message);
}
return excelcon;
}
public DataTable GetDataFromExcel(string strFilePath, string SheetName)
{
DataTable dtCSV = new DataTable();
try
{
OleDbConnection cnCSV = GetExcelCon(strFilePath);
cnCSV.Open();
OleDbCommand cmdSelect = new OleDbCommand(@"SELECT * FROM [" + SheetName + "$]", cnCSV);
OleDbDataAdapter daCSV = new OleDbDataAdapter();
daCSV.SelectCommand = cmdSelect;
daCSV.Fill(dtCSV);
cnCSV.Close();
daCSV = null;
}
catch (Exception ex)
{
Response.Write(ex.Message);
}
return dtCSV;
}
public void BindGrid(DataTable Ds)
{
GridView1.DataSource = Ds;
GridView1.DataBind();
}
}
3. Result:
No comments:
Post a Comment