How to Filter Gridview and Export in Csv using SorPager in Asp.Net

In this Article we will Explain How to Filter Gridview using textbox and based on filter we will export data in Csv file. Here we will create handler for exporting data into Csv file and for filter we will use text for filter data in Gridview. For Bind Gridview we will use SqlDataSource to getting data from Sql Server and directly bind in Gridview. For paging we will create own paging index in Gridview.

Create Table in Sql Server like below and Fill Data

CREATE TABLE [dbo].[orders](

                [orderno] [int] NOT NULL,

                [OrderDate] [datetime] NULL,

                [ShipCountry] [varchar](10) NULL,

                [CustomerID] [int] NULL,

                [RequiredDate] [datetime] NULL,

                [ShipName] [varchar](10) NULL,

PRIMARY KEY CLUSTERED

(

                [orderno] ASC

)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]

) ON [PRIMARY]

GO

Step 1: Design your Webpage Like below to create this functionality in Webpage.

<div id="main"><br />

            Search&nbsp;<asp:TextBox ID="txtName" runat="server"></asp:TextBox>

            &nbsp <span class="loading">Loading...<img src="Scripts/loader.gif" alt="Loading..." /></span><br />

            <br />

            <div style="height: 400px">

<asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False" Width="75%"

DataKeyNames="CustomerID" DataSourceID="SqlDataSource1" CellPadding="0"

OnPreRender="GridView1_PreRender" OnRowDataBound="GridView1_RowDataBound" OnSelectedIndexChanged="GridView1_SelectedIndexChanged"                   GridLines="None">

                    <AlternatingRowStyle BackColor="White" />

                    <Columns>

                        <asp:BoundField DataField="CustomerID" HeaderText="CustomerID" />

                        <asp:BoundField DataField="OrderDate" HeaderText="Order Date" />

          <asp:BoundField DataField="RequiredDate" HeaderText="Required Date" />

          <asp:BoundField DataField="ShipName" HeaderText="Ship Name" />

          <asp:BoundField DataField="ShipCountry" HeaderText="Ship Country" />

          <asp:CommandField ShowSelectButton="True" />

                        <asp:TemplateField>

                            <ItemTemplate>

                                <a href="#" id="a1">Avoid PostBack</a>

                            </ItemTemplate>

                        </asp:TemplateField>

                    </Columns>

          <FooterStyle BackColor="#990000" Font-Bold="True" ForeColor="White" />

  <HeaderStyle BackColor="#990000" Font-Bold="True" ForeColor="White" CssClass="hand" />

  <RowStyle BackColor="White" ForeColor="Black" HorizontalAlign="Center" />

  </asp:GridView>

            </div>

            <div id="pager" class="pager">

                <br />

                <br />

                <img src="Scripts/first.png" alt="" class="first" />

                <img src="Scripts/prev.png" alt="" class="prev" />

                <input type="text" class="pagedisplay" />

                <img src="Scripts/next.png" alt="" class="next" />

                <img src="Scripts/last.png" alt="" class="last" />

                <select class="pagesize" style="display: block">

                    <option selected="selected" value="10">10</option>

</select><input value="Export as CSV" type="button" onclick="$('#GridView1').table2CSV()">

            </div>

<asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:UHConnection %>"

                SelectCommand="SELECT * FROM [orders]">

            </asp:SqlDataSource>

        </div>

Here you Can see we have use Script for Generating tabular format and for Quick Search data in Gridview for Sorting and Pager.

<script src="Scripts/jquery.min.js" type="text/javascript"></script>

    <script src="Scripts/jquery.quicksearch.js" type="text/javascript"></script>

    <script src="Scripts/jquery.tablesorter.js" type="text/javascript"></script>

    <script src="Scripts/jquery.tablesorter.pager.js" type="text/javascript"></script>

    <script src="Scripts/table2CSV.js" type="text/javascript"></script>

    <script type="text/javascript">

        $(function () {

            $(document).ready(function () {

                $("#txtName").quicksearch("table tbody tr", {

                    noResults: '#GridView1_noresults',

                    stripeRows: ['odd', 'even'],

                    loader: 'span.loading'

                });

                $("#GridView1").tablesorter();

                $("table")

                .tablesorter({ widthFixed: true, widgets: ['zebra'] })

                .tablesorterPager({ container: $("#pager") });

            });

        });

    </script>

For Style we have used little for onmouseover we have to change our cursor pointer.

<style type="text/css">

        .hand

        {

            cursor: pointer;

        }

    </style>

Now Create ConnectionString and Bind Gridview Data from Sql Server Using SqlDataSource.

<asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:UHConnection %>"

                SelectCommand="SELECT * FROM [orders]">

            </asp:SqlDataSource>

Step 2: Now Create Gridview PreRender event for Count Row from Gridview and Create Header in Gridview.

protected void GridView1_PreRender(object sender, EventArgs e)

        {

            if (GridView1.Rows.Count > 0)

            {

                GridView1.HeaderRow.TableSection = TableRowSection.TableHeader;

            }

        }

Step 3: Now Create Row_DataBound event to Check all Row Rows from Gridview and Checking When we filter from textbox and for footer rows and for export filter data In Csv File. Here when we click on Export button then it will send to Default page and from that it will create Csv file and Export data into Csv file.

protected void GridView1_RowDataBound(object sender, GridViewRowEventArgs e)

        {

            if (e.Row.RowType == DataControlRowType.DataRow)

            {

                HtmlAnchor a = new HtmlAnchor();

                a = (HtmlAnchor)e.Row.Cells[6].FindControl("a1");

                if (a != null)

                {

                    String sURL = String.Format("javascript:window.open('Default.aspx?i={0}','window','status=1,dependent=1,menubar=no,resizable=1,scrollbars=1,width=1000,height=700,left=0,top=0');", GridView1.DataKeys[e.Row.RowIndex].Value);

                    a.Attributes.Add("onclick", sURL);

                    a = null;

                }

            }

            if (e.Row.RowType == DataControlRowType.Footer)

            {

                GridViewRow row = new GridViewRow(-1, -1, DataControlRowType.DataRow, DataControlRowState.Normal);

                TableCell[] tc = new TableCell[1];

                TableCell tc1 = new TableCell();

                tc1.ColumnSpan = GridView1.Columns.Count;

                TableRow tr = new TableRow();

                tr.ID = "noresults";

                TableCell c1 = new TableCell();

                c1.Text = "No Records Found !";

                tr.Cells.Add(c1);

                tc1.Controls.Add(tr);

                tc[0] = tc1;

                row.Cells.AddRange(tc);

                Table tbl = (e.Row.Parent as Table);

                tbl.Rows.AddAt(GridView1.Rows.Count + 1, row);

            }

        }

Step 4: Now Create Selected IndexChanged event of Gridview to Checking when we changed Index of Gridview.

protected void GridView1_SelectedIndexChanged(object sender, EventArgs e)

        {

            String sURL = String.Format("javascript:window.open('Default.aspx?i={0}','window','status=1,dependent=1,menubar=no,resizable=1,scrollbars=1,width=1000,height=700,left=0,top=0');", GridView1.DataKeys[GridView1.SelectedIndex].Value);

            ClientScript.RegisterClientScriptBlock(typeof(string), "test", sURL, true);

        }

Step 5: Now Create Handler and Name it “ExporttoCsv” and Code for Exporting Data into Csv File. In ProcessRequest event.

public void ProcessRequest(HttpContext context)

        {

            context.Response.ContentType = "application/force-download";

            context.Response.AddHeader("content-disposition", "filename=Order.csv");

            context.Response.Write(context.Request.Form["exporttable"]);

        }

Step 6: Now you are done to do this you can filter data and Export into Csv File and if you want to Export all Data then you can also Done it.

Author : Umesh Patel

Related Posts