Filtering on each column in GridView as Excel Sheet functionality

Tweet

As customers are using Excel application in various situations, they are much happier when providing the functionalities like the same in ASP.NET applications. Sometime the customer will be asking such kind of functionalities in the application.

Today in this post, I am planning to post one of an interesting feature from the Excel sheet - that is filtering on each column. For Example, I have an excel sheet with some column and I have enabled filtering on each column. The excel sheet will look as below –

As we seen, the filtering window shows on each column (which can be decided by the used). When user pressing the down array on a particular column, it shows a list which contains the distinct values of all the values on that column.

I have implemented the same in the GridView. The GridView with filtering window will be as below.

The requirement for this implementation is as below –

1. The GridView should show down arrow on each column, which can be decided by the programmer.
2. When clicking the down arrow on a particular column, the system should show distinct list of the values in a dropdown window.
3. There should be two button OK and Cancel in that dropdown window.
4. The user can select one or more values in the list and press OK button. When clicking the OK button, the system should query the filtered values based on the selected values in the dropdown list and show in the GridView.
5. When selecting multiple columns, the values must be filtered as per the multiple column values. Note: The filtering will be based on AND operator on all the columns.
   For Ex: When the user selects some countries on Country column and some courier types in CourierType column, then the query will be selected the rows where the countries are in selected countries and courier types are in selected courier types. (There is no OR operator involved anywhere here)
6. When clicking Cancel button, the window should hide. When pressing mouse outside of the window the dropdown window must hide.
7. There should be a clear all icon in the dropdown window, which helps the user to clear all the selection done in that particular column. So, by pressing that clear all icon, the system should clear the values and call the server to bind the records by clearing the selection.

Note: This implementation differs little bit from Excel functionalities such as -

1. The values in the dropdown list will not be selected by default. When all the records are not selected, the system will consider the user needs to retrieve all the rows without any filtering for that column.
2. There is no Select All checkbox in the dropdown list, as if all the values are unchecked - it will be considered as selected all.
3. The Clear All icon, will clear the selected values in the dropdown list, it means – the user needs all the values to be retrieved for that column.
4. By default the excel sheet will show the dropdown list based on the rows which are filtered by the previous filtering condition.
   For Ex: Consider we have an Excel sheet which shows all Countries, States and Cities in the world. The user selected Country = India, State = Tamil Nadu. When user pressing the filtering icon on City column, the excel sheet never shows distinct of all the cities in that column - rather it will first filter India, Tamil Nadu and shows the cities available in that two condition. So the cities will be in Tamil Nadu and in India.
   But in this example, we have no implementation to do that automatically. So the developer needs to write the SQL query in the code behind and return records based on other filtering condition.

The Concept
The concept of this implementation is very simple. When GridView gets rendered, I show the down arrow on each column (or decided by the programmer) by getting each column object and assigning css class to it. When user presses the down arrow, the JQuery calls the WCF service by passing the column name. The service will return the distinct values of that particular column. The JQuery will then create a dropdown window and show the list of values.

When the user selects some values and press OK button, the JQuery build a JSON object using the filter values on all the columns and assign to a hidden control. It also calls Refresh button click event on which the server gets the hidden control JSON value and decrypt it then build the SQL Query dynamically and bind to the GridView.

I am using Northwind database in this implementation. So make sure to have the same database for running this source code.

The implementation
The ASPX Script

<asp:GridView ID="grdViewOrders" runat="server"
    AllowPaging="True" AutoGenerateColumns="False" TabIndex="1"
    DataKeyNames="OrderID" Width="100%" GridLines="None"
    CellPadding="3" CellSpacing="1" AllowSorting="false" BorderWidth="1px" 
    onpageindexchanging="grdViewOrders_PageIndexChanging">
    <Columns>
        <asp:BoundField DataField="OrderID" HeaderText="Order ID" 
            SortExpression="OrderID" >
        <ItemStyle Width="10%" />
        </asp:BoundField>
        <asp:BoundField DataField="OrderDate" HeaderText="Order Date" 
            SortExpression="OrderDate" DataFormatString="{0:dd-MMM-yyyy}" >
        <ItemStyle Width="10%" />
        </asp:BoundField>
        <asp:BoundField DataField="ShipName" HeaderText="Ship Name" 
            SortExpression="ShipName" >
        <ItemStyle Width="20%" />
        </asp:BoundField>
        <asp:BoundField DataField="ShipCity" HeaderText="Ship City" 
            SortExpression="ShipCity" >
        <ItemStyle Width="20%" />
        </asp:BoundField>
        <asp:BoundField DataField="Freight" HeaderText="Freight" 
            SortExpression="Freight" DataFormatString="{0:#0.00}" >
        <ItemStyle Width="10%" />
        </asp:BoundField>
        <asp:BoundField DataField="ShipPostalCode" HeaderText="Ship Postal Code" 
            SortExpression="ShipPostalCode" >
        <ItemStyle Width="10%" />
        </asp:BoundField>
        <asp:BoundField DataField="ShipCountry" HeaderText="Ship Country" 
            SortExpression="ShipCountry" >
        <ItemStyle Width="20%" />
        </asp:BoundField>
    </Columns>
    <RowStyle BackColor="#F7F6F3" ForeColor="#333333" />
    <FooterStyle BackColor="#5D7B9D" Font-Bold="True" ForeColor="White" />
    <PagerStyle BackColor="#284775" ForeColor="White" HorizontalAlign="Right" />
    <SelectedRowStyle BackColor="#E2DED6" Font-Bold="True" ForeColor="#333333" />
    <HeaderStyle BackColor="#5D7B9D" Font-Bold="True" ForeColor="White" />
    <EditRowStyle BackColor="#999999" />
    <AlternatingRowStyle BackColor="White" ForeColor="#284775" />
</asp:GridView>
<asp:Button ID="Refresh" runat="server" Text="Refresh" OnClick="Refresh_Click" />
<asp:HiddenField ID="hndSelectedValue" runat="server" Value="" />

The C# Code

protected void Page_Load(object sender, EventArgs e)
{
    if (!Page.IsPostBack)
    {
        BindGrid();
    }
}

private string ArrayToString(IEnumerable<string> strValues)
{
    string strValue = string.Empty;
    foreach (string strVal in strValues)
        strValue = strValue + "'" + strVal + "', ";
    if (strValue.Length > 0)
        strValue = "(" + strValue.Substring(0, strValue.Length - 2) + ")";

    return strValue;
}
private void BindGrid()
{
    string strQuery = "SELECT OrderID, OrderDate, ShipName, ShipCity, Freight, ShipPostalCode, ShipCountry FROM Orders";
    string strShipName = string.Empty, strShipCity = string.Empty;
    var objJavaScriptSerializer = new JavaScriptSerializer();

    if (hndSelectedValue.Value.Trim().Length > 0)
    {
        SelectedDataCollection objSelectedDataCollection = new SelectedDataCollection();
        objSelectedDataCollection = objJavaScriptSerializer.Deserialize<SelectedDataCollection>(hndSelectedValue.Value);

        foreach (SelectedData selectedData in objSelectedDataCollection.DataCollection)
        {
            if (selectedData.SelectedValue.Count<string>() > 0)
                strQuery = strQuery + ((strQuery.Contains(" WHERE ") == true) ? " AND " : " WHERE ") + selectedData.ColumnName + " IN " + ArrayToString(selectedData.SelectedValue);
        }
    }

    OrderDAO objProductDAO = new OrderDAO();

    IList<OrderView> orderViewList = objProductDAO.GetOrders(strQuery);
    if (orderViewList.Count == 0)
        orderViewList.Add(new OrderView());

    grdViewOrders.DataSource = orderViewList;
    grdViewOrders.DataBind();

    for (int intRowIndex = 0; intRowIndex < grdViewOrders.Columns.Count; intRowIndex++)
    {
        TableCell tblCell = ((TableCell)grdViewOrders.HeaderRow.Cells[intRowIndex]);
        tblCell.CssClass = "filter";
        tblCell.Attributes.Add("alt", grdViewOrders.Columns[intRowIndex].SortExpression);
    }
}

protected void Refresh_Click(object sender, EventArgs e)
{
    BindGrid();
}

protected void grdViewOrders_PageIndexChanging(object sender, GridViewPageEventArgs e)
{
    grdViewOrders.PageIndex = e.NewPageIndex;
    BindGrid();
}

The DAO Class

public class OrderDAO
{
    public IList<OrderView> GetOrders()
    {
        return GetOrders(string.Empty);
    }
    public IList<OrderView> GetOrders(string strQuery)
    {
        SqlConnection connection = null;

        try
        {
            connection = new SqlConnection(ConfigurationManager.ConnectionStrings["ConnectionString"].ToString());
            if (strQuery == string.Empty)
                strQuery = @"SELECT OrderID, OrderDate, ShipName, ShipCity, Freight, ShipPostalCode, ShipCountry FROM Orders";

            SqlCommand command = new SqlCommand();
            command.CommandText = strQuery;
            command.Connection = connection;

            command.Connection.Open();
            SqlDataReader dataReader = command.ExecuteReader(CommandBehavior.CloseConnection);

            IList<OrderView> orderViewList = new List<OrderView>();

            while (dataReader.Read())
            {
                OrderView orderView = new OrderView();
                orderView.OrderID = Convert.ToInt32(dataReader["OrderID"].ToString());
                orderView.OrderDate = Convert.ToDateTime(dataReader["OrderDate"].ToString());
                orderView.ShipName = dataReader["ShipName"].ToString();
                orderView.ShipCity = dataReader["ShipCity"].ToString();
                orderView.Freight = Convert.ToDouble(dataReader["Freight"].ToString());
                orderView.ShipPostalCode = dataReader["ShipPostalCode"].ToString();
                orderView.ShipCountry = dataReader["ShipCountry"].ToString();

                orderViewList.Add(orderView);
            }

            connection.Close();

            //return
            return orderViewList;
        }
        catch (Exception ex)
        {
            if (connection != null) connection.Close();

            // Log
            throw ex;
        }
    }
}

The OrderView entity class

public class OrderView
{
    public int OrderID { get; set; }
    public DateTime OrderDate { get; set; }
    public string ShipName { get; set; }
    public string ShipCity { get; set; }
    public double Freight { get; set;}
    public string ShipPostalCode { get; set; }
    public string ShipCountry { get; set; }
}

The WCF Service code

[ServiceContract]
public interface IService1
{
    [OperationContract]
    [WebGet(UriTemplate = "/data?ColumnName={strColumnName}", ResponseFormat = WebMessageFormat.Json)]
    FilterValueSet[] GetDistinctValue(string strColumnName);
}

public class Service1 : IService1
{
    public FilterValueSet[] GetDistinctValue(string strColumnName)
    {
        SqlConnection connection = null;

        try
        {

            connection = new SqlConnection(ConfigurationManager.ConnectionStrings["ConnectionString"].ToString());

            string strSQL = string.Empty;
            switch (strColumnName) // This is the column name in the GridView defined
            {
                case "OrderID":
                    strSQL = @"SELECT distinct OrderID, OrderID FROM Orders Order By 1";
                    break;
                case "OrderDate":
                    strSQL = @"SELECT distinct OrderDate, CONVERT(VARCHAR(11), OrderDate, 106) AS [OrderDate] FROM Orders Order By 1";
                    break;
                case "ShipName":
                    strSQL = @"SELECT distinct ShipName, ShipName FROM Orders Order By 1";
                    break;
                case "ShipCity":
                    strSQL = @"SELECT distinct ShipCity, ShipCity FROM Orders Order By 1";
                    break;
                case "Freight":
                    strSQL = @"SELECT distinct Freight, Freight FROM Orders Order By 1";
                    break;
                case "ShipPostalCode":
                    strSQL = @"SELECT distinct ShipPostalCode, ShipPostalCode FROM Orders Order By 1";
                    break;
                case "ShipCountry":
                    strSQL = @"SELECT distinct ShipCountry, ShipCountry FROM Orders Order By 1";
                    break;

            }

            SqlCommand command = new SqlCommand();
            command.CommandText = strSQL;
            command.Connection = connection;

            command.Connection.Open();

            SqlDataReader dataReader = command.ExecuteReader(CommandBehavior.CloseConnection);

            IList<FilterValueSet> filterValueList = new List<FilterValueSet>();

            while (dataReader.Read())
            {
                filterValueList.Add(new FilterValueSet
                {
                    Id = dataReader[0].ToString(),
                    Value = dataReader[1].ToString()
                });
            }
            connection.Close();

            return filterValueList.ToArray<FilterValueSet>();
        }
        catch (Exception ex)
        {
            if (connection != null) connection.Close();

            // Log
            throw ex;
        }
    }
}

<system.serviceModel>
  <behaviors>
    <serviceBehaviors>
      <behavior>
        <serviceMetadata httpGetEnabled="true"/>
        <serviceDebug includeExceptionDetailInFaults="false"/>
      </behavior>
    </serviceBehaviors>
    <endpointBehaviors>
      <behavior name="WebBehavior">
        <webHttp />
      </behavior>
    </endpointBehaviors>
  </behaviors>
  <bindings>
    <wsHttpBinding>
      <binding name="NoSecurity">
        <security mode="None"/>
      </binding>
    </wsHttpBinding>
  </bindings>
  <services>
    <service name="FilterGridView.Service1">
      <endpoint address="" behaviorConfiguration="WebBehavior" binding="webHttpBinding" contract="FilterGridView.IService1"/>
    </service>
  </services>
  <serviceHostingEnvironment multipleSiteBindingsEnabled="true" />
</system.serviceModel>

Helper classes

public class SelectedData
{
    public string ColumnName { get; set; }
    public IEnumerable<string> SelectedValue { get; set; }
}
public class SelectedDataCollection
{
    public IEnumerable<selecteddata> DataCollection { get; set; }
}
public class FilterValueSet
{
    public string Id { get; set; }
    public string Value { get; set; }
}

The Style sheet

<style type="text/css">
    th.filter
    {
     background: url(../../images/Down.png) right center no-repeat;
     cursor:pointer;
    }
</style>

Finally the JQuery code

$(document).ready(function () {
    $('.filter').click(function () {

        var pos = $(this).position();
        var width = $(this).outerWidth();
        var height = $(this).outerHeight();
        var colName = $(this).attr("alt");

        jQuery.support.cors = true;

        $.ajax({

            type: "GET",
            url: "http://localhost:1609/Service1.svc/data?ColumnName=" + colName,
            processData: false,
            contentType: "application/json; charset=utf-8",
            dataType: "json",

            success: function (response) {

                var filterdiv = $("<div id='divFilterList'></div>");
                var div = $("<div></div>");
                var valuesdiv = $("<div id='filterChkBox'></div>");
                var table = $("<table />");
                var SelectedValue = {};
                var hiddenControlValue = $('#MainContent_hndSelectedValue').val();

                if (hiddenControlValue.length > 0) {
                    var alreadySelectedValueInfo = jQuery.parseJSON(hiddenControlValue);
                    for (var index = 0; index < alreadySelectedValueInfo.DataCollection.length; index++) {
                        if (alreadySelectedValueInfo.DataCollection[index].ColumnName == colName) {
                            SelectedValue = alreadySelectedValueInfo.DataCollection[index].SelectedValue;
                        }
                    }
                }

                $.each(response, function (index, obj) {
                    var IsSelected = false;
                    for (var index = 0; index < SelectedValue.length; index++) {
                        if (SelectedValue[index] == obj.Id)
                            IsSelected = true;
                    }

                    if (IsSelected == true)
                        table.append("<tr><td><input name='options' checked='checked' type='checkbox' value='" + obj.Id + "'>" + obj.Value + "</td></tr>");
                    else
                        table.append("<tr><td><input name='options' type='checkbox' value='" + obj.Id + "'>" + obj.Value + "</td></tr>");
                });

                valuesdiv.append(table);
                div.append(valuesdiv);
                filterdiv.append(div);
                $('body').append(filterdiv);

                var buttondiv = $("<div></div>").css({
                    textAlign: "right",
                    paddingTop: "5px",
                    paddingRight: "5px"
                }).show();

                var clearFilterBtn = $("<button>.</button>");
                clearFilterBtn.css({ height: "22px",
                    width: "25px",
                    border: "none",
                    background: "url(../../images/ClearFilter.png) center center no-repeat",
                    cursor: "pointer"
                });
                buttondiv.append(clearFilterBtn);

                var okBtn = $("<button>OK</button>");
                okBtn.css({ height: "22px", width: "70px" });
                buttondiv.append(okBtn);

                var cancelBtn = $("<button>Cancel</button>");
                cancelBtn.css({ height: "22px", width: "70px" });
                buttondiv.append(cancelBtn);

                cancelBtn.click(function () {
                    var container = $("#divFilterList");
                    container.detach();
                });

                okBtn.click(function () {

                    var SelectedDataCollection = {};

                    var ArrSelectedColumnsInfo = new Array(); // It hold all column selected info

                    var SelectedData = {};
                    SelectedData["ColumnName"] = colName;

                    var ArrSelectedValue = new Array();
                    $('#filterChkBox table tr td input:checked').each(function () {
                        ArrSelectedValue.push($(this).val());
                    });
                    SelectedData["SelectedValue"] = ArrSelectedValue;

                    var IsExist = false;
                    var alreadySelectedValueInfo;

                    if (hiddenControlValue.length > 0) {
                        var alreadySelectedValueInfo = jQuery.parseJSON(hiddenControlValue);
                        for (var index = 0; index < alreadySelectedValueInfo.DataCollection.length; index++) {
                            if (alreadySelectedValueInfo.DataCollection[index].ColumnName == colName) {
                                alreadySelectedValueInfo.DataCollection[index] = SelectedData;
                                SelectedDataCollection = alreadySelectedValueInfo;
                                IsExist = true;
                                break;
                            }
                        }
                    }

                    if (IsExist == false) {
                        var alreadySelectedValueInfo = new Array();
                        if (hiddenControlValue.length > 0) {
                            alreadySelectedValueInfo = jQuery.parseJSON(hiddenControlValue);
                            if (SelectedData.SelectedValue.length > 0)
                                alreadySelectedValueInfo["DataCollection"].push(SelectedData);
                            SelectedDataCollection = alreadySelectedValueInfo;
                        }
                        else {
                            alreadySelectedValueInfo.push(SelectedData);
                            SelectedDataCollection["DataCollection"] = alreadySelectedValueInfo;
                        }
                    }

                    $('#<%= hndSelectedValue.ClientID %>').val(JSON.stringify(SelectedDataCollection));
                    document.getElementById('<%= Refresh.ClientID %>').click();
                });

                clearFilterBtn.click(function () {
                    if (hiddenControlValue.length > 0) {
                        var alreadySelectedValueInfo = jQuery.parseJSON(hiddenControlValue);
                        for (var index = 0; index < alreadySelectedValueInfo.DataCollection.length; index++) {
                            if (alreadySelectedValueInfo.DataCollection[index].ColumnName == colName) {
                                alreadySelectedValueInfo.DataCollection[index].selectedValue = new Array();
                                $('#<%= hndSelectedValue.ClientID %>').val(JSON.stringify(alreadySelectedValueInfo));
                                break;
                            }
                        }
                    }
                    document.getElementById('<%= Refresh.ClientID %>').click();
                });

                filterdiv.append(buttondiv);

                var tblwidth = table.outerWidth();
                valuesdiv.css({ width: tblwidth + "px" }).show();

                div.css({
                    overflow: "auto",
                    textAlign: "left",
                    paddingLeft: "5px",
                    paddingTop: "1px",
                    marginLeft: "1px",
                    marginTop: "1px",
                    backgroundColor: "White",
                    border: "1px solid gray",
                    height: "207px",
                    width: "200px"
                }).show();

                var left = 0;
                if (pos.left + width - 200 - 10 < 0)
                    left = 0;
                else
                    left = pos.left + width - 200 - 10;

                filterdiv.css({
                    position: "absolute",
                    textAlign: "left",
                    backgroundColor: "white",
                    border: "1px solid black",
                    color: "black",
                    height: "243px",
                    width: "209px",
                    top: pos.top + height + 1 + "px",
                    left: left + "px"
                }).show();
            },
            error: function (xhr, status, error) {
                alert(error);
            }
        });

    });
    $(document).mouseup(function (e) {
        var container = $("#divFilterList");

        if (container.has(e.target).length === 0) {
            container.detach();
        }
    });
});

This implementation works well with the IE 9.0, Firefox 10.0 and Chrome 21.0 (other browsers I have not tested).

The screen output as below

Note: I have used the dropdown icon little different then Excel dropdown icon. I have included another image in the project which is same as Excel dropdown icon.

So to use the same icon, you can change the style sheet as below

th.filter
{
 background: url(../../images/ShowFilter.png) right bottom no-repeat;
 cursor:pointer;
}

The output of this stylesheet will look like as below

download the working copy of the source code in C# here and in VB here.

Important Note: I call the service with localhost port number 1609 (http://localhost:1609/Service1.svc) as the service published like that in my system. If your service running in different port, please change the port number. If you are downloading the source file and running, make sure the port number is same, or change the source code as per the new port number the service running. (To know the port number, you can run the application and get to know from localhost url or check in the Web tab in project properties.)

[Update 25 Dec 2014 06:30 PM]
Addition to this, I have made two more example which avoids the service call made to the server from the Javascript to get list of values while click the dropdown icon on each column. To avoid service call, there are various ways to do, but considering only two of the methods here -

1. Using Generic Handler - HTTP handlers are used to intercept the HTTP calls to the server and respond instead of using aspx pages. For more information HTTP Handlers and HTTP Modules Overview.
   The example in the download link uses the Generic Handler to get the list of items for each column and respond the list to the Javascript.
2. But looking at the previous example, there is a call happening from Client to server. Instead, I can get the list of values for all columns and keep it in variable (here hidden control) before loading the page. When Javascript required the list, it can get from the variable and use it without calling to the server again. This method will be useful while getting limited data from the db as the page load might take time. The download link is here.

The Next post on this same topic explains more functionalities.