Filter in EJ2 JavaScript Spreadsheet control

4 Jul 202415 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.

  • 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 sheet, select a range of cells to filter by value of the cell. The filtering can be done by any of the following ways:

  • Select the filter item in the Ribbon toolbar.

  • Right-click the sheet, select the filter item in the context menu.

  • Use the applyFilter() method programmatically.

  • Use Ctrl + Shift + L keyboard shortcut to apply the filter.
  • Use Alt + Up/Down keyboard shortcut to open the filter dialog.

Filter by criteria

The applyFilter() method will apply the filter UI, based on the predicate and range given in the arguments.

  • 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.

// Initialize the Spreadsheet component.

var sheet = [{
                ranges: [{ dataSource: tradeData }],
                columns: [{ width: 100 }, { width: 130 },{ width: 96},
                    { width: 130 }, { width: 130 },{ width: 96},
                    { width: 100 }, { width: 100 },{ width: 110}, { width: 100 }, { width: 130 },{ width: 150}]
                }];

var spreadsheet = new ej.spreadsheet.Spreadsheet({
    sheets: sheet,
    allowFiltering: true,
    dataBound: function () {
        if (spreadsheet.activeSheetIndex === 0) {
            var departments = ['Sweden', 'Canada', 'UK'];
            var predicateList = []
            departments.forEach((department) => { predicateList.push({ field: 'D', predicate: 'or', operator: 'equal', value: department }); })
            spreadsheet.applyFilter(predicateList);
        }
    }
    });

// Render initialized Spreadsheet.
spreadsheet.appendTo('#spreadsheet');
<!DOCTYPE html><html lang="en"><head>
        <title>EJ2 SpreadSheet</title>
        <meta charset="utf-8">
        <meta name="viewport" content="width=device-width, initial-scale=1.0">
        <meta name="description" content="Typescript UI Controls">
        <meta name="author" content="Syncfusion">
        <link rel="shortcut icon" href="resources/favicon.ico">
        <link href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.7/css/bootstrap.min.css" rel="stylesheet">
        <link href="https://cdn.syncfusion.com/ej2/27.2.2/ej2-base/styles/material.css" rel="stylesheet">
        <link href="https://cdn.syncfusion.com/ej2/27.2.2/ej2-inputs/styles/material.css" rel="stylesheet">
        <link href="https://cdn.syncfusion.com/ej2/27.2.2/ej2-buttons/styles/material.css" rel="stylesheet">
        <link href="https://cdn.syncfusion.com/ej2/27.2.2/ej2-splitbuttons/styles/material.css" rel="stylesheet">
        <link href="https://cdn.syncfusion.com/ej2/27.2.2/ej2-lists/styles/material.css" rel="stylesheet">
        <link href="https://cdn.syncfusion.com/ej2/27.2.2/ej2-navigations/styles/material.css" rel="stylesheet">
        <link href="https://cdn.syncfusion.com/ej2/27.2.2/ej2-popups/styles/material.css" rel="stylesheet">
        <link href="https://cdn.syncfusion.com/ej2/27.2.2/ej2-dropdowns/styles/material.css" rel="stylesheet">
        <link href="https://cdn.syncfusion.com/ej2/27.2.2/ej2-grids/styles/material.css" rel="stylesheet">
        <link href="https://cdn.syncfusion.com/ej2/27.2.2/ej2-spreadsheet/styles/material.css" rel="stylesheet">
        <link href="styles.css" rel="stylesheet">
        
        <script src="https://cdnjs.cloudflare.com/ajax/libs/core-js/2.4.1/shim.min.js"></script>
        <script src="es5-datasource.js" type="text/javascript"></script>
<script src="https://cdn.syncfusion.com/ej2/27.2.2/dist/ej2.min.js" type="text/javascript"></script>
<script src="es5-datasource.js" type="text/javascript"></script>
<script src="https://cdn.syncfusion.com/ej2/syncfusion-helper.js" type ="text/javascript"></script>
</head>

<body>
        <!--Element which is going to render-->
        
       <div id="container">
       <div id="spreadsheet"></div>
       </div>


<script>
var ele = document.getElementById('container');
if(ele) {
  ele.style.visibility = "visible";
}   
      </script>
<script src="index.js" type="text/javascript"></script>
</body></html>

Filter by cell value

To apply a filter for a cell value, right-click the cell and choose filter -> Filter By Selected Cell's Value option from the menu. It applies the filter based on the value of the selected cell in the current sheet.

Clear filter

After applying filter to a certain column, you may want to clear it to make all filtered rows visible again. It can be done in 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

After filtering, you can clear/reset the filter for a field alone. It can be done in the following ways,

  • Click filter icon in the column’s header and then choose Clear Filter option from the filter dialog.

  • You can right-click on a filtered column cell and choose Clear Filter from <Column Name>. option from the context menu.

  • Use the clearFilter(field) method programmatically, to clear the filter in a particular column.

Reapply filter

When you want to reapply the filter after some changes happened in the rows. It can be done in the following ways,

  • You can choose Reapply option in ribbon toolbar under Filter and Sort to reapply the filtered columns again.
  • You can right-click on a filtered cell and choose Reapply option from the context menu. It reapplies the filters again in the Spreadsheet for all the fields.

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.

let sheet = [{
    ranges: [{ dataSource: defaultData }],
    columns: [
        { width: 180 }, { width: 130 }, { width: 130 }, { width: 180 },
        { width: 130 }, { width: 120 }
    ]
}];

let spreadsheet = new ej.spreadsheet.Spreadsheet({
    sheets: sheet,
    created: function () {
        // Applies cell formatting to specified range of the active sheet
        spreadsheet.cellFormat({ fontWeight: 'bold', textAlign: 'center', verticalAlign: 'middle' }, 'A1:F1');
        // Construct the predicate model to be updated to the data.
        let predicates = [{
            field: 'C',
            operator: 'equal',
            value: 'Pink',
            matchCase: false
        }];
        // Apply filter to the specified range.
        spreadsheet.applyFilter(predicates, 'A1:C7');
    }
});

spreadsheet.appendTo('#spreadsheet');

document.getElementById("getFilterData").onclick = () => {
    let activeSheet = spreadsheet.getActiveSheet();
    let usedRange = activeSheet.usedRange;
    for (let i = 0; i <= usedRange.rowIndex; i++) {
        // Get the filtered row using isFiltered property.
        let filteredRow = (activeSheet.rows[i]).isFiltered;
        if (!filteredRow) {
            let rowData = spreadsheet.getRowData(i);
            console.log("Row:", i + 1, "Cells", rowData);
        }
    }
};
<!DOCTYPE html>
<html lang="en">

<head>
        <title>EJ2 SpreadSheet</title>
        <meta charset="utf-8">
        <meta name="viewport" content="width=device-width, initial-scale=1.0">
        <meta name="description" content="Typescript UI Controls">
        <meta name="author" content="Syncfusion">
        <link rel="shortcut icon" href="resources/favicon.ico">
        <link href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.7/css/bootstrap.min.css" rel="stylesheet">
        <link href="https://cdn.syncfusion.com/ej2/27.2.2/ej2-base/styles/material.css" rel="stylesheet">
        <link href="https://cdn.syncfusion.com/ej2/27.2.2/ej2-inputs/styles/material.css" rel="stylesheet">
        <link href="https://cdn.syncfusion.com/ej2/27.2.2/ej2-buttons/styles/material.css" rel="stylesheet">
        <link href="https://cdn.syncfusion.com/ej2/27.2.2/ej2-splitbuttons/styles/material.css" rel="stylesheet">
        <link href="https://cdn.syncfusion.com/ej2/27.2.2/ej2-lists/styles/material.css" rel="stylesheet">
        <link href="https://cdn.syncfusion.com/ej2/27.2.2/ej2-navigations/styles/material.css" rel="stylesheet">
        <link href="https://cdn.syncfusion.com/ej2/27.2.2/ej2-popups/styles/material.css" rel="stylesheet">
        <link href="https://cdn.syncfusion.com/ej2/27.2.2/ej2-dropdowns/styles/material.css" rel="stylesheet">
        <link href="https://cdn.syncfusion.com/ej2/27.2.2/ej2-grids/styles/material.css" rel="stylesheet">
        <link href="https://cdn.syncfusion.com/ej2/27.2.2/ej2-spreadsheet/styles/material.css" rel="stylesheet">
        <link href="styles.css" rel="stylesheet">

        <script src="https://cdnjs.cloudflare.com/ajax/libs/core-js/2.4.1/shim.min.js"></script>
        <script src="https://cdn.syncfusion.com/ej2/27.2.2/dist/ej2.min.js" type="text/javascript"></script>
        <script src="es5-datasource.js" type="text/javascript"></script>
<script src="https://cdn.syncfusion.com/ej2/syncfusion-helper.js" type ="text/javascript"></script>
</head>

<body>
        <!--Element which is going to render-->

        <div id="container">
                <button class="e-btn custom-btn" id="getFilterData">Get Filtered Data</button>
                <div id="spreadsheet"></div>
        </div>

        <script>
                var ele = document.getElementById('container');
                if (ele) {
                        ele.style.visibility = "visible";
                }   
        </script>
        <script src="index.js" type="text/javascript"></script>
</body>

</html>

Limitations

The following features have some limitations in Filter:

  • Insert/delete row/column between the filter applied cells.
  • Merge cells with filter.
  • Copy/cut paste the filter applied cells.

See Also