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.

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.

Figure 1: Filter types

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.

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.

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:

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

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.

[C#] 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.

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.

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.

[C#] 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 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.

[C#] 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.