Filtering
This article describes what is filtering and filters and how to work with them through the document model. It contains the following sections:
What is Filtering?
The filtering feature allows the user to hide and show certain rows of a range, based on different criteria. It provides an easy way to work with just the relevant set of data.
The information about the filtering applied to a worksheet is contained in the Worksheet's property Filter, which is of type AutoFilter. Through it, you can set and modify the current range which is filtered and add and remove filters to its columns. Each column can have only one filter applied to it. The interface implemented by all filters is IFilter.
AutoFilter
The AutoFilter class exposes the following public members:
FilterRange: Property of type CellRange. Represents the range to which a filter is currently applied. The worksheet can have only one range filtered at a time. If filtering is not applied, the filtered range is null.
void SetFilters(IEnumerable<IFilter> filters): Sets multiple filters on the filtered range and applies them.
void SetFilter(IFilter filter): Sets a single filter on the filtered range and applies it.
IFilter GetFilter(int relativeColumnIndex): Retrieves the filter applied on the column with the specified index.
bool RemoveFilter(IFilter filter): Removes the specified filter and shows all rows which were hidden by it.
bool RemoveFilter(int relativeColumnIndex): Removes the filter applied on the column with the specified index and shows all rows which were hidden by it.
void ClearFilters(): Removes all filters and shows all rows of the filtered range.
void ReapplyFilter(IFilter filter): Reapplies the specified filter.
void ReapplyFilter(int relativeColumnIndex): Reapplies the filter applied to the column with the specified index.
The column indices which are used to work with the filters are zero-based and relative to the filtered range.
IFilter
All the filters which can be applied to the filter range implement the IFilter interface. The interface exposes the following members:
RelativeColumnIndex: Gets the index of the column to which the filter is applied. The index is relative to the beginning of the filter range.
object GetValue(Cells cells, int rowIndex, int columnIndex): Gets the value of the cell at the specified index. This value is used to determine whether the row should be hidden by the filter.
bool ShouldShowValue(object value): Determines whether the row which contains the specified value should be shown.
The GetValue() method provides the value which the ShouldShowValue() method uses to evaluate whether the current row should be hidden or shown.
The diagram in Figure 1 shows the different types of filters, which inherit the IFilter interface, and the classes which implement them:
Figure 1: Filter types
ValuesCollectionFilter
The values collection filter is a filter which holds a collection of strings and date group items. If the filter encounters a date in the column it filters, it compares it to the date group items in its collection. If there isn't a date group item which matches it, the row is hidden. If the value is not a date the filter compares the formatted string representation of the cell value with the collection of string values. If it is present in the collection, the row is shown, otherwise it is hidden. If the cell is empty, the filter uses the value of the boolean property Blank to determine whether the row should be shown or hidden.
Other than the members of the IFilter interface, the ValuesCollectionFilter class exposes the following members specific to it:
StringValues: The collection of strings values.
DateItems: The collection of date group items.
Blank: The value indicating whether the blank cells will be shown or not.
Example 1 shows how to create a ValuesCollectionFilter.
Example 1: Create ValuesCollectionFilter
IEnumerable<string> stringItems = new List<string>() { "test", "1%", "1.0" };
IEnumerable<DateGroupItem> dateItems = new List<DateGroupItem>()
{
new DateGroupItem(2013),
new DateGroupItem(2014, 3)
};
ValuesCollectionFilter filter = new ValuesCollectionFilter(0, stringItems, dateItems, true);
This filter created in Example 1 will hide all rows which contain dates which are not within the year of 2013 or within March 2014. It will also hide the rows where the formatted string value of the cell does not correspond to any of the strings of the stringItems list. The blank items will be shown.
CustomFilter
The custom filter is a filter which contains one or two critera which are used to filter the column to which the filter is assigned. If the value of the cell doesn't satisfy the criteria, the respective row is hidden by the filter.
Other than the members of the IFilter interface, the CustomFilter class exposes the following members specific to it:
Criteria1: Property of type CustomFilterCriteria specisying the first criteria.
Criteria2: Property of type CustomFilterCriteria specisying the second criteria. The second critera can be null.
-
LogicalOperator: The logical operator which determines the logical relationship between the critera. It can have two values:
And
Or
The criteria is represented by the CustomFilterCriteria class. Each criteria contains the following:
FilterValue: The value to which the cell value is compared.
-
ComparisonOperator: The operator which indicates how the cell value should compare to the FilterValue. The comparison operator can be:
EqualsTo
GreaterThan
GreaterThanOrEqualsTo
LessThan
LessThanOrEqualsTo
NotEqualsTo
Example 2 shows how to create a custom filter.
Example 2: Create CustomFilter
CustomFilterCriteria critera1 = new CustomFilterCriteria(ComparisonOperator.EqualsTo, "Test string");
CustomFilterCriteria critera2 = new CustomFilterCriteria(ComparisonOperator.GreaterThan, "-5");
CustomFilter filter = new CustomFilter(0, critera1, LogicalOperator.Or, critera2);
Note that even though the FilterValue is of type string, internally the filter will attempt to parse it. This is the opposite behavior to the ValuesCollectionFilter which compares only the string representations of the values. In this case, the filter will display all rows which contain a number value greater than -5 or a text value equal to "Test string".
TopFilter
The top filter is a filter which displays a given number or percent of the total values in the column it filters, taking the first top or bottom values. It hides all other rows.
Other than the members of the IFilter interface, the TopFilter class exposes the following members specific to it:
-
TopFilterType: The value indicating whether the filter should display the top or bottom values and whether the number of values will be indicated as a number of items or as percent of the total number of items. The top filter type can be:
TopNumber
BottomNumber
TopPercent
BottomPercent
Value: The number of items or the percent of the total number of items which will be displayed by the filter.
Example 3 shows how to create a top filter.
Example 3: Create TopFilter
TopFilter filter = new TopFilter(0, TopFilterType.BottomPercent, 30);
The filter will show the top 30 percent of all values in the filtered column. Note that the filter includes only number values both in its estimate how many values to show and which values to show. If the filtered column includes for example a text value, it will be hidden, even if the filter is supposed to show the top 100 percent of values.
DynamicFilter
The dynamic filter is a filter which shows or hides the rows in the column it filters based on a condition chosen from a set of predetermined conditions.
Other than the members of the IFilter interface, the DynamicFilter class exposes only one property specific to it:
- DynamicFilterType: The type of the dynamic filter, which determines what condition the filter should use to filter the column it is assigned to. The dynamic filter type can be used from the values of the DynamicFilterType enumaration.
Example 4 demonstrates how to create a dynamic filter.
Example 4: Create DynamicFilter
DynamicFilter filter = new DynamicFilter(0, DynamicFilterType.LastWeek);
The filter will show only the values which are dates and which fall within the week prior to the application of the filter.
ForeColorFilter
The fore color filter hides or displays the cells in the column it filters based on the color of the text in it.
Other than the members of the IFilter interface, the ForeColorFilter class exposes only one property specific to it:
- Color: A ThemableColor object representing the color which should be set on the text of the cell in order for it to be displayed. All other cells of the column are hidden.
Example 5 demonstrates how to create a fore color filter.
Example 5: Create ForeColorFilter
ThemableColor color = new ThemableColor(Colors.Red);
ForeColorFilter filter = new ForeColorFilter(0, color);
This filter will hide all cells whose text color is not red.
FillColorFilter
The fill color filter hides or displays the cells in the column it filters based on their fill.
Other than the members of the IFilter interface, the FillColorFilter class exposes only one property specific to it:
- Fill: The fill the cell needs to have in order for it to be displayed. All other cells of the column are hidden.
Example 6 shows hot to create a fill color filter.
Example 6: Create FillColorFilter
IFill fill = new PatternFill(PatternType.Solid, Colors.Red, Colors.Red);
FillColorFilter filter = new FillColorFilter(0, fill);
This filter will hide all cells whose fill is not solid red.
Setting a Filter
In order to set a filter on a range, you need to follow the steps below:
-
Set the filter range.
Example 7: Set FilterRange
Worksheet worksheet = workbook.ActiveWorksheet; CellRange filterRange = new CellRange(0, 1, 5, 2); worksheet.Filter.FilterRange = filterRange; -
Create a filter.
Example 8: Create DynamicFilter
The relative index specified in the constructor is 1, which means that the filter will be set on the second column of the range, that is, column C.DynamicFilter filter = new DynamicFilter(1, DynamicFilterType.AboveAverage); -
Set the filter on the necessary column.
Example 9: Set Filter
worksheet.Filter.SetFilter(filter);Figure 2 demonstrates the result of the filtering when applied on the values 1-9 in column B and 11-19 in column C.
Figure 2: Result of the filtering
Alternatively, you can set the filter through the cell selection like in Example 10. This approach will automatically set the filter range anew.
Example 10: Set filter through selection
worksheet.Cells[filterRange].Filter(filter);
Keep in mind that the first row of the FilterRange is reserved for column headers and will not be included in the actual filtering.
Note that the filter cannot be set before the range. Attempting to do so will cause an exception.
Reapplying a Filter
When a filter is set it is automatically applied. The application of a filter happens only once and if the values or properties of the filtered column change afterwards, the filter needs to be reapplied. This is done by using the overloads of the ReapplyFilter() method. The first overload allows reapplying a filter by the relative index of the column it is applied to. The second - by a IFilter instance.
Example 11: Set FilterRange
worksheet.Filter.ReapplyFilter(1);
Note that attempting to reapply filter on a column which is not filtered causes an exception.
Removing and Clearing Filters
Removing and clearing filters is done using the following methods exposed by the AutoFilter class:
RemoveFilter(IFilter filter): Removes the specified filter and shows all rows which were hidden by it. Returns true if successful.
RemoveFilter(int relativeColumnIndex): Removes the filter applied on the column with the specified index and shows all rows which were hidden by it. Returns true if successful.
ClearFilters(): Removes all filters and shows all rows of the filtered range.
As is the case with the ReapplyFilter() method, you can remove a filter by instance and by relative index of the column it is applied to.
Example 12: Remove filter
bool success = worksheet.Filter.RemoveFilter(1);
In order to remove all applied filters at once use the ClearFilters() method. ClearFilters() will display all rows which were hidden by filtering on the worksheet. However, it will not remove the filtering itself. In order to do this, you need to set the FilteredRange property to null.
Setting the FilteredRange property to null without removing the filters beforehand will automatically remove them.