Wednesday, 18 March 2015

Import Excel Sheet Data To GridView on Page_Loading GridView Binding Using Asp.Net C#

Import Excel Sheet Data To GridView Page_Loading


Import Excel Sheet Data To GridView Display Source From Solution Explorer Folder  Page_Loading Time Using in Asp.Net C#.

                                 
                                                       Download Coding  
                          Download
                      

DEMO PAGE_LOAD BINDED GRIDVIEW




DEMO PAGE_LOAD 





                    HTML CODING


<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
    <title></title>
</head>
<body>
    <form id="form1" runat="server">
    <div>
   
    </div>
        
        <asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False" Width="300px">
            <Columns>
                <asp:TemplateField HeaderText="SELECT">
                    <ItemTemplate>
                        <asp:CheckBox ID="chkSelect" runat="server" />
                    </ItemTemplate>
                </asp:TemplateField>
                <asp:TemplateField HeaderText="COUNTRY">
                    <ItemTemplate>
                        <asp:Label ID="lblCountry" runat="server" Text='<%# Eval("COUNTRY"%>'></asp:Label>
                    </ItemTemplate>
                </asp:TemplateField>
                <asp:TemplateField HeaderText="CITY">
                    <ItemTemplate>
                        <asp:Label ID="lblCity" runat="server" Text='<%# Eval("CITY"%>'></asp:Label>
                    </ItemTemplate>
                </asp:TemplateField>
            </Columns>
             <HeaderStyle BackColor="#FF3300" />
            <RowStyle BackColor="Silver" />
            <AlternatingRowStyle BackColor="#9999FF" />
        </asp:GridView>
       

        <asp:Button ID="Button1" runat="server" OnClick="Button1_Click" Text="Import To Labels" />
        
        <asp:Label ID="lblCountry" runat="server" ForeColor="#CC3300"></asp:Label>
        
        <asp:Label ID="lblCity" runat="server" ForeColor="#CC3300"></asp:Label>
        
    </form>
</body>
</html>


            C# CODING


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

public partial class Default5 : System.Web.UI.Page
{
    protected void Page_Load(object sender, EventArgs e)
    {
        if (!IsPostBack)
        {
            BindGridView();
        }

}
    public void BindGridView()
    {

        string filePath = Server.MapPath("file/1.xlsx");
        string conn = System.Configuration.ConfigurationManager.ConnectionStrings["dbconExcel07"].ConnectionString;

        conn = String.Format(conn, filePath, "yes");
        OleDbConnection con = new OleDbConnection(conn);
        OleDbCommand ExcelCommand = new OleDbCommand();
        ExcelCommand.Connection = con;
        con.Open();

        DataTable ExcelDataSet = new DataTable();
        ExcelDataSet = con.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
        DataTable dt = new DataTable();

        string ExcelSheetName = ExcelDataSet.Rows[0]["table_name"].ToString();
        ExcelCommand.CommandText = "SELECT * From [" + ExcelSheetName + "]";
        OleDbDataAdapter ExcelAdapter = new OleDbDataAdapter(ExcelCommand);
        ExcelAdapter.SelectCommand = ExcelCommand;
        ExcelAdapter.Fill(dt);
        con.Close();

        GridView1.DataSource = dt;
        GridView1.DataBind();
    }

    protected void Button1_Click(object sender, EventArgs e)
    {
        lblCountry.Text = "";
        lblCity.Text = "";

        foreach (GridViewRow gvRow in GridView1.Rows)
        {
            CheckBox checkbox = (CheckBox)gvRow.Cells[0].FindControl("chkSelect");
            if (checkbox.Checked)
            {
                Label lblCnt = (Label)gvRow.FindControl("lblCountry");
                Label lblCty = (Label)gvRow.FindControl("lblCity");
                lblCountry.Text += lblCnt.Text + ",";
                lblCity.Text += lblCty.Text + ",";


            }
        }
    }

}

                ConnectionString(Web.Config)


<connectionStrings>

                        // Xlsx Format

              <add name ="dbconExcel03" connectionString="Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0}; Extended Properties='Excel 8.0;HDR={1}'"/>
             
              // Xlsx Format

              <add name ="dbconExcel07" connectionString="Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0}; Extended Properties='Excel 12.0;HDR={1}'"/>
                                          
                   

    </connectionStrings>



















































0 comments:

Post a Comment