Filtering in ASP.NET Core Spreadsheet control

30 Jul 202615 minutes to read

Filtering helps you to view specific rows in the Spreadsheet by hiding the other rows. You can use the allowFiltering property to enable or disable filtering functionality.

NOTE

  • The default value for allowFiltering property is true.

By default, the filter module is injected internally into Spreadsheet to perform filtering.

Apply filter on UI

In the active Spreadsheet, select a range of cells to filter by their values. You can apply filtering in one of the following ways:

  • Choose Filter from the Ribbon.
  • Right-click the selected range and choose Filter from the context menu.
  • Use the applyFilter() method programmatically.
  • Press Ctrl + Shift + L to apply the filter.

NOTE

  • Press Alt + Up/Down to open the filter dialog.

Filter by criteria

Use the applyFilter() method to apply a filter based on the specified predicate and cell range. The predicate defines the filter condition, and the range specifies the cells to which the filter is applied.

  • The beforeFilter event will be triggered before filtering the specified range.
  • The filterComplete event will be triggered after the filter action is completed successfully.

The following code example shows filter functionality in the Spreadsheet control.

After running the sample, verify that only the rows matching the specified filter criteria are displayed.

<ejs-spreadsheet id="spreadsheet" allowFiltering="true" dataBound="dataBound">
    <e-spreadsheet-sheets>
        <e-spreadsheet-sheet>
            <e-spreadsheet-ranges>
                <e-spreadsheet-range dataSource="ViewBag.DefaultData"></e-spreadsheet-range>
            </e-spreadsheet-ranges>
        </e-spreadsheet-sheet>
    </e-spreadsheet-sheets>
</ejs-spreadsheet>

<script>

function dataBound() {
    var spreadsheetObj = ej.base.getComponent(document.getElementById('spreadsheet'), 'spreadsheet');
    if (spreadsheetObj.activeSheetIndex === 0) {
        var colors = ['Pink', 'Aquamarine', 'Blue'];
        var predicateList = []
        colors.forEach((color) => { predicateList.push({ field: 'C', predicate: 'or', operator: 'equal', value: color }); })
        spreadsheetObj.applyFilter(predicateList);
    }
}

</script>
public IActionResult Index()
{
    List<object> data = new List<object>()
    {
        new { CustomerName= "Romona Heaslip",  Model= "Taurus",  Color= "Aquamarine",  PaymentMode= "Debit Card",  DeliveryDate= "07/11/2015",  Amount= "8529.22" },
        new { CustomerName= "Clare Batterton",  Model= "Sparrow",  Color= "Pink",  PaymentMode= "Cash On Delivery",  DeliveryDate= "7/13/2016",  Amount= "17866.19" },
        new { CustomerName= "Eamon Traise",  Model= "Grand Cherokee",  Color= "Blue",  PaymentMode= "Net Banking",  DeliveryDate= "09/04/2015",  Amount= "13853.09" },
        new { CustomerName= "Julius Gorner",  Model= "GTO",  Color= "Aquamarine",  PaymentMode= "Credit Card",  DeliveryDate= "12/15/2017",  Amount= "2338.74" },
        new { CustomerName= "Jenna Schoolfield",  Model= "LX",  Color= "Yellow",  PaymentMode= "Credit Card",  DeliveryDate= "10/08/2014",  Amount= "9578.45" },
        new { CustomerName= "Marylynne Harring",  Model= "Catera",  Color= "Green",  PaymentMode= "Cash On Delivery",  DeliveryDate= "7/01/2017",  Amount= "19141.62" },
        new { CustomerName= "Vilhelmina Leipelt",  Model= "7 Series",  Color= "Goldenrod",  PaymentMode= "Credit Card",  DeliveryDate= "12/20/2015",  Amount= "6543.30" },
        new { CustomerName= "Barby Heisler",  Model= "Corvette",  Color= "Red",  PaymentMode= "Credit Card",  DeliveryDate= "11/24/2014",  Amount= "13035.06" },
        new { CustomerName= "Karyn Boik",  Model= "Regal",  Color= "Indigo",  PaymentMode= "Debit Card",  DeliveryDate= "05/12/2014",  Amount= "18488.80" },
        new { CustomerName= "Jeanette Pamplin",  Model= "S4",  Color= "Fuscia",  PaymentMode= "Net Banking",  DeliveryDate= "12/30/2014",  Amount= "12317.04" },
        new { CustomerName= "Cristi Espinos",  Model= "TL",  Color= "Aquamarine",  PaymentMode= "Credit Card",  DeliveryDate= "12/18/2013",  Amount= "6230.13" },
        new { CustomerName= "Issy Humm",  Model= "Club Wagon",  Color= "Pink",  PaymentMode= "Cash On Delivery",  DeliveryDate= "02/02/2015",  Amount= "9709.49" },
        new { CustomerName= "Tuesday Fautly",  Model= "V8 Vantage",  Color= "Crimson",  PaymentMode= "Debit Card",  DeliveryDate= "11/19/2014",  Amount= "9766.10" },
        new { CustomerName= "Rosemaria Thomann",  Model= "Caravan",  Color= "Violet",  PaymentMode= "Net Banking",  DeliveryDate= "02/08/2014",  Amount= "7685.49" },
    };
    ViewBag.DefaultData = data;
    return View();
}

Filter by cell value

To filter by a cell value, right-click the cell and choose Filter -> Filter By Selected Cell's Value from the context menu. The filter is applied based on the value of the selected cell in the active sheet.

Clear filter

After applying a filter, you can clear all filters to display every row again. You can clear all filters in one of the following ways:

  • Choose Clear option in ribbon toolbar under Filter and Sort. It clears the filters applied in the spreadsheet for all fields.

  • Use the clearFilter() method programmatically, to clear the applied filters in spreadsheet for all fields.

Clear filter on a field

You can clear or reset the filter applied to a specific field in one of the following ways:

  • Click the filter icon in the column header and choose Clear Filter from the filter dialog.
  • Right-click a cell in the filtered column and choose the corresponding Clear Filter option from the context menu.
  • Use the clearFilter(field) method to programmatically clear the filter from a specific field.

Reapply filter

After modifying the data in filtered rows, you can reapply the existing filter in one of the following ways:

  • Choose Reapply from Filter and Sort in the Ribbon.
  • Right-click a filtered cell and choose Reapply from the context menu.

Reapplying updates the filtered results based on the current data.

Known error validations

The following errors have been handled for filtering,

  • Out of range validation: When the selected range is not a used range of the active sheet, it is considered as invalid and the out of range alert with the message Select a cell or range inside the used range and try again will be displayed. No filter will be performed if the range is invalid.

Get data from filtered rows

Filtering allows you to view specific rows in a spreadsheet while hiding the others. The allowFiltering property allows you to enable or disable filtering functionality through the UI. You can also use the allowFiltering property and applyFilter method combination to filter data via code behind. The filtered rows can be identified by iterating through the row collection on the sheet and using the isFiltered property available in each row object.

The following code example shows how to get the filtered rows.

<ejs-button id="getFilterData" content="Get Filtered Data"></ejs-button>
<ejs-spreadsheet id="spreadsheet" allowFiltering="true">
    <e-spreadsheet-sheets>
        <e-spreadsheet-sheet>
            <e-spreadsheet-ranges>
                <e-spreadsheet-range dataSource="ViewBag.DefaultData"></e-spreadsheet-range>
            </e-spreadsheet-ranges>
        </e-spreadsheet-sheet>
    </e-spreadsheet-sheets>
</ejs-spreadsheet>

<script>


    document.getElementById("getFilterData").addEventListener('click', function () {
        var spreadsheet = document.getElementById("spreadsheet").ej2_instances[0];
        var activeSheet = spreadsheet.getActiveSheet();
        var usedRange = activeSheet.usedRange;
        for (var i = 0; i <= usedRange.rowIndex; i++) {
            // Get the filtered row using isFiltered property.
            var filteredRow = (activeSheet.rows[i]).isFiltered;
            if (!filteredRow) {
                var rowData = spreadsheet.getRowData(i);
                console.log("Row:", i + 1, "Cells", rowData);
            }
        }
    });

</script>
public IActionResult Index()
{
    List<object> data = new List<object>()
    {
        new { CustomerName= "Romona Heaslip",  Model= "Taurus",  Color= "Aquamarine",  PaymentMode= "Debit Card",  DeliveryDate= "07/11/2015",  Amount= "8529.22" },
        new { CustomerName= "Clare Batterton",  Model= "Sparrow",  Color= "Pink",  PaymentMode= "Cash On Delivery",  DeliveryDate= "7/13/2016",  Amount= "17866.19" },
        new { CustomerName= "Eamon Traise",  Model= "Grand Cherokee",  Color= "Blue",  PaymentMode= "Net Banking",  DeliveryDate= "09/04/2015",  Amount= "13853.09" },
        new { CustomerName= "Julius Gorner",  Model= "GTO",  Color= "Aquamarine",  PaymentMode= "Credit Card",  DeliveryDate= "12/15/2017",  Amount= "2338.74" },
        new { CustomerName= "Jenna Schoolfield",  Model= "LX",  Color= "Yellow",  PaymentMode= "Credit Card",  DeliveryDate= "10/08/2014",  Amount= "9578.45" },
        new { CustomerName= "Marylynne Harring",  Model= "Catera",  Color= "Pink",  PaymentMode= "Cash On Delivery",  DeliveryDate= "7/01/2017",  Amount= "19141.62" },
        new { CustomerName= "Vilhelmina Leipelt",  Model= "7 Series",  Color= "Goldenrod",  PaymentMode= "Credit Card",  DeliveryDate= "12/20/2015",  Amount= "6543.30" },
        new { CustomerName= "Barby Heisler",  Model= "Corvette",  Color= "Red",  PaymentMode= "Credit Card",  DeliveryDate= "11/24/2014",  Amount= "13035.06" },
        new { CustomerName= "Karyn Boik",  Model= "Regal",  Color= "Pink",  PaymentMode= "Debit Card",  DeliveryDate= "05/12/2014",  Amount= "18488.80" },
        new { CustomerName= "Jeanette Pamplin",  Model= "S4",  Color= "Fuscia",  PaymentMode= "Net Banking",  DeliveryDate= "12/30/2014",  Amount= "12317.04" },
        new { CustomerName= "Cristi Espinos",  Model= "TL",  Color= "Aquamarine",  PaymentMode= "Credit Card",  DeliveryDate= "12/18/2013",  Amount= "6230.13" },
        new { CustomerName= "Issy Humm",  Model= "Club Wagon",  Color= "Pink",  PaymentMode= "Cash On Delivery",  DeliveryDate= "02/02/2015",  Amount= "9709.49" },
        new { CustomerName= "Tuesday Fautly",  Model= "V8 Vantage",  Color= "Crimson",  PaymentMode= "Debit Card",  DeliveryDate= "11/19/2014",  Amount= "9766.10" },
        new { CustomerName= "Rosemaria Thomann",  Model= "Caravan",  Color= "Violet",  PaymentMode= "Net Banking",  DeliveryDate= "02/08/2014",  Amount= "7685.49" },
    };
    ViewBag.DefaultData = data;
    return View();
}

Limitations

The following operations have limitations when filtering is applied:

  • Inserting or deleting rows and columns within the filtered range.
  • Merging cells within the filtered range.
  • Copying, cutting, or pasting cells within the filtered range.

See Also