Excel Like Filter

12 Jan 20245 minutes to read

You can enable Excel like filter by defining type as Excel. The excel menu contains an option such as Sorting, Clear filter, Sub menu for advanced filtering.

@Html.EJS().Grid("ExcelFilter").DataSource((IEnumerable<object>)ViewBag.DataSource).AllowFiltering().FilterSettings(Filter => Filter.Type(Syncfusion.EJ2.Grids.FilterType.Excel)).Columns(col =>
{
    col.Field("OrderID").HeaderText("Order ID").Width("120").TextAlign(Syncfusion.EJ2.Grids.TextAlign.Right).Add();
    col.Field("CustomerID").HeaderText("Customer Name").Width("150").Add();
    col.Field("OrderDate").HeaderText("Order Date").Width("130").TextAlign(Syncfusion.EJ2.Grids.TextAlign.Right).Format("yMd").Add();
    col.Field("Freight").HeaderText("Freight").Width("120").Format("C2").TextAlign(Syncfusion.EJ2.Grids.TextAlign.Right).Add();
    col.Field("ShipCountry").HeaderText("Ship Country").Width("120").Add();

}).AllowPaging().Render()
public IActionResult Index()
 {
    if (orders.Count() == 0)
        DataSource();
    ViewBag.DataSource = orders;            
    return View();
 }

 public void DataSource()
 {
    int code = 10000;
    for (int i = 1; i < 10; i++)
    {
        orders.Add(new OrderDetails(code + 1, "ALFKI", i + 0, 2.3 * i, false, new DateTime(1991, 05, 15), "Berlin", "Simons bistro", "Denmark", new DateTime(1996, 7, 16), "Kirchgasse 6"));
        orders.Add(new OrderDetails(code + 2, "ANATR", i + 2, 3.3 * i, true, new DateTime(1990, 04, 04), "Madrid", "Queen Cozinha", "Brazil", new DateTime(1996, 9, 11), "Avda. Azteca 123"));
        orders.Add(new OrderDetails(code + 3, "ANTON", i + 1, 4.3 * i, true, new DateTime(1957, 11, 30), "Cholchester", "Frankenversand", "Germany", new DateTime(1996, 10, 7), "Carrera 52 con Ave. Bolívar #65-98 Llano Largo"));
        orders.Add(new OrderDetails(code + 4, "BLONP", i + 3, 5.3 * i, false, new DateTime(1930, 10, 22), "Marseille", "Ernst Handel", "Austria", new DateTime(1996, 12, 30), "Magazinweg 7"));
        orders.Add(new OrderDetails(code + 5, "BOLID", i + 4, 6.3 * i, true, new DateTime(1953, 02, 18), "Tsawassen", "Hanari Carnes", "Switzerland", new DateTime(1997, 12, 3), "1029 - 12th Ave. S."));
        code += 5;
    }
 }

Render checkbox list data in on-demand for excel/checkbox filtering

The Excel/Checkbox filter type of Grid has a restriction where only the first 1000 unique sorted items are accessible to view in the filter dialog checkbox list content by scrolling. This limitation is in place to avoid any rendering delays when opening the filter dialog. However, the searching and filtering processes consider all unique items in that particular column.

The Excel/Checkbox filter in the Grid provides an option to load large data sets on-demand during scrolling to improve scrolling limitation functionality. This is achieved by setting the EnableInfiniteScrolling property to true. This feature proves especially beneficial for managing extensive datasets, enhancing data loading performance in the checkbox list, and allowing interactive checkbox selection with persistence for the selection based on filtering criteria.

The Excel/Checkbox filter retrieves distinct data in ascending order, governed by its ItemsCount property, with a default value of 50. As the checkbox list data scroller reaches its end, the next dataset is fetched and displayed, with the notable advantage that this process only requests new checkbox list data without redundantly fetching the existing loaded dataset.

Customize the items count for initial rendering

Based on the items count value, the Excel/Checkbox filter gets unique data and displayed in Excel/Checkbox filter content dialog. You can customize the count of on-demand data rendering for Excel/Checkbox filter by adjusting the ItemsCount property. The default value is 50.

@Html.EJS().Grid("OnDemandExcelFilter").DataSource(ViewBag.DataSource).FilterSettings(Filter => Filter.EnableInfiniteScrolling(true).ItemsCount(40))

It is recommended to keep the itemsCount below 300. Higher values will result in unwanted whitespace because of DOM maintenance performance degradation.

Customize the loading animation effect

A loading effect is presented to signify that loading is in progress when the checkbox list data scroller reaches the end, and there is a delay in receiving the data response from the server. The loading effect during on-demand data retrieval for Excel/Checkbox filter can be customized using the LoadingIndicator property. The default value is Shimmer.

@Html.EJS().Grid("OnDemandExcelFilter").DataSource(ViewBag.DataSource).FilterSettings(Filter => Filter.EnableInfiniteScrolling(true).LoadingIndicator(Syncfusion.EJ2.Grids.IndicatorType.Spinner))

In the provided example, On-Demand Excel filter has been enabled for the ASP.NET MVC Grid

@Html.EJS().Grid("OnDemandExcelFilter").DataSource(dataManger =>
{
    dataManger.Url("https://services.syncfusion.com/aspnet/production/api/UrlDataSource").CrossDomain(true).Adaptor("UrlAdaptor");

}).AllowFiltering().FilterSettings(Filter => Filter.Type(Syncfusion.EJ2.Grids.FilterType.Excel).EnableInfiniteScrolling(true)).Columns(col =>
{
    col.Field("EmployeeID").HeaderText("Employee ID").Width("120").TextAlign(Syncfusion.EJ2.Grids.TextAlign.Right).Add();
    col.Field("Employees").HeaderText("Employee Name").Width("150").Add();
    col.Field("Designation").HeaderText("Designation").Width("120").Add();
    col.Field("CurrentSalary").HeaderText("Current Salary").Width("120").Format("C2").TextAlign(Syncfusion.EJ2.Grids.TextAlign.Right).Add();
}).AllowPaging().Render()
public IActionResult Index()
 {        
    return View();
 }

See also