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 istrue
.
By default, the filter
module is injected internally into Spreadsheet to perform filtering.
In the active Spreadsheet, select a range of cells to filter by value of the cell. The filtering can be done by any of the following ways:
applyFilter()
method programmatically.Ctrl + Shift + L
keyboard shortcut to apply the filter.
- Use
Alt + Up/Down
keyboard shortcut to open the filter dialog.
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.
@Html.EJS().Spreadsheet("spreadsheet").DataBound("dataBound").AllowFiltering(true).Sheets((sheet) =>
{
sheet.Ranges((ranges) =>
{
ranges.DataSource(@ViewBag.defaultData).Add();
}).Add();
}).Render()
<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();
}
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.
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,
Clear
option in ribbon toolbar under Filter and Sort
. It clears the filters applied in the spreadsheet for all fields.clearFilter()
method programmatically, to clear the applied filters in spreadsheet for all fields.After filtering, you can clear/reset the filter for a field alone. It can be done in the following ways,
Clear Filter
option from the filter dialog.Clear Filter from <Column Name>.
option from the context menu.clearFilter(field)
method programmatically, to clear the filter in a particular column.When you want to reapply the filter after some changes happened in the rows. It can be done in the following ways,
Reapply
option in ribbon toolbar under Filter and Sort
to reapply the filtered columns again.Reapply
option from the context menu. It reapplies the filters again in the Spreadsheet for all the fields.The following errors have been handled for filtering,
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.