Filter Data in Spreadsheet Control for WPF
- 4 minutes to read
The SpreadsheetControl allows users to handle large amounts of data. To quickly access required records, a user can apply a filtering criteria and hide all records that don’t match the condition.
Manage Filters in the User Interface
Users can specify a filter, reapply filters, or remove all filters in a worksheet through filtering commands located on the Data tab in the Sort & Filter group.
When filtering is active, a drop-down arrow 
appears on the right side of each column header in the range. Users can click the arrow of the required column and select a predefined filter type from the AutoFilter drop-down menu. The menu content depends on the type of data in the filtered column (text, numeric values, color, or dates).
If cells in the target column use different styles, the Filter by Color option is available. Choose Cell colors… to apply a background color or pattern filter, and Font colors… to apply a font filter.
When you select the required option, a dialog with available colors appears. Select the target color and click OK.
Filter Data in Code
Obtain Filter Settings for a Cell Range and a Table
Use the Worksheet.AutoFilter property to get the SheetAutoFilter object. This object implements filtering functionality for a worksheet.
For a Table, filtering functionality activates by default. Use the Table.AutoFilter property to get the TableAutoFilter object, which specifies filtering options for a table.
Both SheetAutoFilter and TableAutoFilter implement the AutoFilterBase interface so that you can filter table data similarly to worksheet ranges.
Refer to the following article for an example on how to activate filtering functionality for a worksheet range or table: How to: Enable Filtering. Use the Worksheet.AutoFilter property to get access to the SheetAutoFilter object, implementing the filtering functionality for a worksheet. The SheetAutoFilter object inherits from the AutoFilterBase base interface that contains basic methods and properties used to specify different types of filters in the API. To turn on filtering for the specified cell range in a worksheet, call the SheetAutoFilter.Apply method.
Filter a Column
To filter values in a specific column, obtain a collection of columns in the filtered range through the SheetAutoFilter.Columns or TableAutoFilter.Columns property. Get the column by its index in the AutoFilterColumnCollection. Each AutoFilterColumn object in AutoFilterColumnCollection contains methods to filter data in a column.
Call one of these methods based on the desired filter type:
| Method | Description | Example |
|---|---|---|
| AutoFilterColumn.ApplyCustomFilter | Applies a custom filter based on one or two filter criteria using the comparison operator(s) to construct the filter expression. | How to: Apply a Custom Text Filter How to: Apply a Custom Number Filter How to: Apply a Custom Date Filter |
| AutoFilterColumn.ApplyFilterCriteria | Filters column data by a list of cell values and/or date and time values. | How to: Filter by Cell Values How to: Filter by Date Values |
| AutoFilterColumn.ApplyDynamicFilter | Applies a dynamic filter to display values that are above or below the average or to show dates that fall within a specified time period. | How to: Apply a Dynamic Filter |
| AutoFilterColumn.ApplyTop10Filter | Applies the Top 10 filter that allows you to display top/bottom ranked values. | How to: Filter Top or Bottom Ranked Values |
| AutoFilterColumn.ApplyFillColorFilter | Applies the background color filter. | How to: Filter Cells by Color |
| AutoFilterColumn.ApplyFontColorFilter | Applies the font color filter. | How to: Filter Cells by Color |
| AutoFilterColumn.ApplyFillFilter | Applies a filter by cell fill pattern. | How to: Filter Cells by Color |
Tip
Filter data using multiple columns. Filters are additive, meaning each new filter applies in addition to existing filters, further refining your data.
Apply, Re-apply, or Clear Filter
Turn on filtering for a specified cell range in a worksheet by the SheetAutoFilter.Apply method call.
Call the AutoFilterBase.ReApply method to reapply a filter if data in the filtered range changes. Refer to the following example for details: How to: Reapply a Filter
To remove a filter from a specific column, use the AutoFilterColumn.Clear method. To clear all the filters specified in a worksheet, call the AutoFilterBase.Clear method. For details, refer to the How to: Clear a Filter example.