Filter in EJ2 JavaScript Spreadsheet control

27 Jul 202614 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 in any of the following ways:

  • Select the Filter item in the Ribbon toolbar.

  • Right-click the sheet and select the Filter item from 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 is triggered before filtering the specified range.
  • The filterComplete event is triggered after the filter action completes successfully.

The following code example shows the 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(function (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/33.1.44/ej2-base/styles/tailwind3.css" rel="stylesheet">
        <link href="https://cdn.syncfusion.com/ej2/33.1.44/ej2-inputs/styles/tailwind3.css" rel="stylesheet">
        <link href="https://cdn.syncfusion.com/ej2/33.1.44/ej2-buttons/styles/tailwind3.css" rel="stylesheet">
        <link href="https://cdn.syncfusion.com/ej2/33.1.44/ej2-splitbuttons/styles/tailwind3.css" rel="stylesheet">
        <link href="https://cdn.syncfusion.com/ej2/33.1.44/ej2-lists/styles/tailwind3.css" rel="stylesheet">
        <link href="https://cdn.syncfusion.com/ej2/33.1.44/ej2-navigations/styles/tailwind3.css" rel="stylesheet">
        <link href="https://cdn.syncfusion.com/ej2/33.1.44/ej2-popups/styles/tailwind3.css" rel="stylesheet">
        <link href="https://cdn.syncfusion.com/ej2/33.1.44/ej2-dropdowns/styles/tailwind3.css" rel="stylesheet">
        <link href="https://cdn.syncfusion.com/ej2/33.1.44/ej2-grids/styles/tailwind3.css" rel="stylesheet">
        <link href="https://cdn.syncfusion.com/ej2/33.1.44/ej2-spreadsheet/styles/tailwind3.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/33.1.44/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 context menu. It applies the filter based on the value of the selected cell in the current sheet.

Clear filter

After applying filter to one or more columns, you may want to clear it to make all filtered rows visible again. This can be done in the following ways:

  • Choose the Clear option in the ribbon toolbar under Filter and Sort. This clears the filters applied in the spreadsheet for all fields.
  • Use the clearFilter() method programmatically to clear the applied filters in the spreadsheet for all fields.

Clear filter on a field

After filtering, you can clear/reset the filter for a single field. This can be done in the following ways:

  • Click the filter icon in the column’s header and then choose the Clear Filter option from the filter dialog.
  • Right-click a filtered column cell and choose Clear Filter from <Column Name> from the context menu.
  • Use the clearFilter(field) method programmatically to clear the filter on a particular column.

Reapply filter

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

  • You can choose Reapply option in the 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.

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

var 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.
        var 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 = function () {
    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);
        }
    }
};
<!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/33.1.44/ej2-base/styles/tailwind3.css" rel="stylesheet">
        <link href="https://cdn.syncfusion.com/ej2/33.1.44/ej2-inputs/styles/tailwind3.css" rel="stylesheet">
        <link href="https://cdn.syncfusion.com/ej2/33.1.44/ej2-buttons/styles/tailwind3.css" rel="stylesheet">
        <link href="https://cdn.syncfusion.com/ej2/33.1.44/ej2-splitbuttons/styles/tailwind3.css" rel="stylesheet">
        <link href="https://cdn.syncfusion.com/ej2/33.1.44/ej2-lists/styles/tailwind3.css" rel="stylesheet">
        <link href="https://cdn.syncfusion.com/ej2/33.1.44/ej2-navigations/styles/tailwind3.css" rel="stylesheet">
        <link href="https://cdn.syncfusion.com/ej2/33.1.44/ej2-popups/styles/tailwind3.css" rel="stylesheet">
        <link href="https://cdn.syncfusion.com/ej2/33.1.44/ej2-dropdowns/styles/tailwind3.css" rel="stylesheet">
        <link href="https://cdn.syncfusion.com/ej2/33.1.44/ej2-grids/styles/tailwind3.css" rel="stylesheet">
        <link href="https://cdn.syncfusion.com/ej2/33.1.44/ej2-spreadsheet/styles/tailwind3.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/33.1.44/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