Follow by Email

Saturday, June 1, 2013

Bind Excel Data to Gridview in Asp.net | Excel to DataTable or Dataset c#

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


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: