FlexSheet supports filtering of column entries on the client side. It extends the FlexGrid filter, and enables you to apply Condition filters and Value filters to each column of your sheet. Condition filters and value filters are available in filter editor, which is displayed by invoking showColumnFilter() method.

In the filter editor you can use Filter by Condition to apply conditions to narrow down your search, and Filter by Value to precisely locate data corresponding to the desired column value. The Ascending and Descending buttons enable you to sort the entries in a particular column in ascending and descending order respectively. Upon clicking the Apply button the filtered values are fetched, while on clicking the Clear button filter editor is closed.

This functionality helps users in quickly fetching the desired data corresponding to the specific column entries. It eases the task of analyzing and viewing complex data in a sheet. For example, from monthly sales data of an organization operating in four countries, you want to view sales details only for two countries. This can be easily accomplished by applying filter on the column containing country names. The below example demonstrates such a scenario.

The following image shows how a data bound FlexSheet control appears after applying Filter by Value on the selected column. The control in the below example displays data generated in Sale.cs model. The end users can select a particular column in the FlexSheet to display filter editor for it on a button click, and specify conditions or provide exact values to refine their search.

The following image shows a bound FlexSheet with Condition filter.

 The following code examples demonstrate how to enable Filtering in the FlexSheet:

In Code 

Add a Sale.cs class to the Models folder.

Model

public class Sale
{
    public int ID { get; set; }
    public DateTime Date { get; set; }
    public string Country { get; set; }
    public string Product { get; set; }
    public double Amount { get; set; }
    public bool Active { get; set; }

    private static List<string> COUNTRIES = new List<string> { "US", "Germany", "UK", "Japan", "Italy", "Greece" };
    private static List<string> PRODUCTS = new List<string> { "Widget", "Gadget", "Doohickey" };

    /// <summary>
    /// Get the data.
    /// </summary>
    /// <param name="total"></param>
    /// <returns></returns>
    public static IEnumerable<Sale> GetData(int total)
    {
        var rand = new Random(0);
        var list = Enumerable.Range(0, total).Select(i =>
        {
            var country = COUNTRIES[rand.Next(0, COUNTRIES.Count - 1)];
            var product = PRODUCTS[rand.Next(0, PRODUCTS.Count - 1)];
            var date = new DateTime(2015, i % 12 + 1, 25);

            return new Sale
            {
                ID = i + 1,
                Date = date,
                Country = country,
                Product = product,
                Amount = Math.Round(rand.NextDouble() * 10000 - 5000, 2),
                Active = (i % 4 == 0)
            };
        });
        return list;
    }

    public static List<string> GetCountries()
    {
        var countries = new List<string>();
        countries.AddRange(COUNTRIES);
        return countries;
    }

    public static List<string> GetProducts()
    {
        List<string> products = new List<string>();
        products.AddRange(PRODUCTS);
        return products;
    }
}

FilteringController.cs

public class FilterController : Controller
{
    // GET: Filter
    public static List<Sale> SALES = Sale.GetData(15).ToList();
    public ActionResult FilterIndex()
    {
        return View(SALES);
    }
}

Filtering.cshtml

@using MVCFlexSheet.Models;
@model IEnumerable<Sale>

<script>
    function filterFlex() {
        var flex = wijmo.Control.getControl("#fSheet");
        flex.showColumnFilter();
    }
</script>

<div>
    <input id="filter" type="button" onclick="filterFlex()" value="Show Filter" />
    <br /><br />
    @(Html.C1().FlexSheet().CssClass("flexSheet").Id("fSheet").IsReadOnly(false)
        .AddBoundSheet(sheet =>
            sheet.Bind(cv =>
                            cv.Bind(Model)))
    )
</div>

Back to Top