Wednesday, June 18, 2014

Gridview Searching textBox using OLEDB Connection Excel

Aspx
------------------------
<%@ Page Language="C#" AutoEventWireup="true" CodeFile="Excel.aspx.cs" Inherits="Excel" %>

<!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:TextBox ID="txtsearch" runat="server"></asp:TextBox>
    <asp:Button ID="btnsearch" runat="server" Text="Search" onclick="btnsearch_Click" />

    <asp:GridView ID="grd" runat="server" AutoGenerateColumns="False" BackColor="White"
            BorderColor="White" BorderStyle="Ridge" BorderWidth="2px" CellPadding="3"
            CellSpacing="1" GridLines="None">
        <Columns>
            <asp:TemplateField HeaderText="empid">
                <ItemTemplate>
                    <asp:Label ID="Label1" runat="server" Text='<%# bind("empid") %>'></asp:Label>
                </ItemTemplate>
            </asp:TemplateField >
            <asp:TemplateField HeaderText="empname">
                <ItemTemplate>
                    <asp:Label ID="Label2" runat="server" Text='<%# bind("empname") %>'></asp:Label>
                </ItemTemplate>
            </asp:TemplateField>
            <asp:TemplateField HeaderText="salary">
                <ItemTemplate>
                    <asp:Label ID="Label3" runat="server" Text='<%# bind("salary") %>'></asp:Label>
                </ItemTemplate>
            </asp:TemplateField>
        </Columns>
 
        <FooterStyle BackColor="#C6C3C6" ForeColor="Black" />
        <HeaderStyle BackColor="#4A3C8C" Font-Bold="True" ForeColor="#E7E7FF" />
        <PagerStyle BackColor="#C6C3C6" ForeColor="Black" HorizontalAlign="Right" />
        <RowStyle BackColor="#DEDFDE" ForeColor="Black" />
        <SelectedRowStyle BackColor="#9471DE" Font-Bold="True" ForeColor="White" />
        <SortedAscendingCellStyle BackColor="#F1F1F1" />
        <SortedAscendingHeaderStyle BackColor="#594B9C" />
        <SortedDescendingCellStyle BackColor="#CAC9C9" />
        <SortedDescendingHeaderStyle BackColor="#33276A" />
 
    </asp:GridView>
 
    </div>
    </form>
</body>

</html>
--------------------------------------------------------------------------------------------------
ASPX.CS
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.OleDb;

public partial class Excel : System.Web.UI.Page
{
    protected void Page_Load(object sender, EventArgs e)
    {
        if (!IsPostBack)
        {
            Session.Clear();
            grd.DataSource = getData();
            grd.DataBind();
        }
    }
    public DataSet getData()
        {
        OleDbConnection con = new OleDbConnection("provider=Microsoft.Jet.OLEDB.4.0;Data Source='D://Naresh.xlsx';Extended Properties=\'Excel 8.0;'");
        OleDbCommand cmd = new OleDbCommand("select * from [emp$]");
        cmd.Connection = con;
        OleDbDataAdapter dap = new OleDbDataAdapter(cmd);
        DataSet ds = new DataSet();
        dap.Fill(ds);
        return (ds);
    }
    protected void btnsearch_Click(object sender, EventArgs e)
    {



        //grd.DataSource = getValues(txtsearch.Text);
        //grd.DataBind();

        foreach (GridViewRow objgrd in grd.Rows)
        {

            Label lblname = (Label)objgrd.FindControl("Label2");
            Label lblid = (Label)objgrd.FindControl("Label1");
            Label lblsalary = (Label)objgrd.FindControl("Label3");
            if (txtsearch.Text == lblname.Text)
            {
                DataTable dt = new DataTable();
                dt.Columns.Add("empid", typeof(string));
                dt.Columns.Add("empname", typeof(string));
                dt.Columns.Add("salary", typeof(string));
                dt.Rows.Add(lblid.Text, lblname.Text, lblsalary.Text);
                grd.DataSource = dt;
                grd.DataBind();
            }
        }
        //}
        ////    //DataSet ds =
        ////    grd.DataSource = getValues(lblsalary.Text);
        ////    grd.DataBind();
        ////    //DataTable dt = new DataTable();
        ////    //dt.Columns.Add("empid", typeof(string));
        ////    //dt.Columns.Add("empname", typeof(string));
        ////    //dt.Columns.Add("salary", typeof(string));
        ////    //if (Session["dt"] == null)
        ////    //{
        ////    //    dt.Rows.Add(ds.Tables[0].Rows[0][0].ToString(), ds.Tables[0].Rows[0][2].ToString(), ds.Tables[0].Rows[0][2].ToString());
        ////    //    Session["dt"] = dt;
        ////    //    grd.DataSource = dt;
        ////    //    grd.DataBind();

        ////    //}
        ////    //else
        ////    //{
        ////    //    DataTable dt2 = (DataTable)Session["dt"];
        ////    //    dt2.Rows.Add(ds.Tables[0].Rows[0][0].ToString(), ds.Tables[0].Rows[0][2].ToString(), ds.Tables[0].Rows[0][2].ToString());
        ////    //    Session["dt"] = dt2;

        ////    //    grd.DataSource = dt2;
        ////    //    grd.DataBind();

        ////    //}


        ////}
     

    }
    public DataSet getValues(string name)
    {
        OleDbConnection con = new OleDbConnection("provider=Microsoft.Jet.OLEDB.4.0;Data Source='D://Naresh.xlsx';Extended Properties=\'Excel 8.0';");
        OleDbCommand cmd = new OleDbCommand("select * from [emp$] Where empname like '" + name + "%'");
        cmd.Connection = con;
     
        OleDbDataAdapter dap = new OleDbDataAdapter(cmd);
        DataSet ds = new DataSet();
        dap.Fill(ds);
        return (ds);
    }


}