AutoFilter Class

Namespace:
GemBox.Spreadsheet
Assembly:
GemBox.Spreadsheet.dll

Represents filter settings and provides filter functionality.

public sealed class AutoFilter
Public NotInheritable Class AutoFilter
Inheritance:
System.Object
AutoFilter
Remarks

Use Filter() methods to create or get an AutoFilter instance.

Active AutoFilter is stored in the parent worksheet (Filter) and is loaded from and saved to XLSX file.

Properties

Columns

Gets the filter columns.

public FilterColumnCollection Columns { get; }
Public ReadOnly Property Columns As FilterColumnCollection
Property Value
FilterColumnCollection

The filter columns.

Range

Gets the range to filter.

public CellRange Range { get; }
Public ReadOnly Property Range As CellRange
Property Value
CellRange

The range to filter.

Sort

Gets the AutoFilter sort state.

public SortState Sort { get; }
Public ReadOnly Property Sort As SortState
Property Value
SortState

The AutoFilter sort state.

Methods

Apply()

Performs the filtering and sorting of this Range based on this AutoFilter settings.

Rows which cells contained in Range do not satisfy filtering conditions will be hidden (Hidden will be set to true).

public void Apply()
Public Sub Apply
Remarks

This method has the same affect as Apply(Boolean) with parameter set to true.

Exceptions
System.InvalidOperationException

Sort range contains merged cells.

Apply(Boolean)

Performs the filtering and (optionally) sorting of this Range based on this AutoFilter settings.

Rows which cells contained in Range do not satisfy filtering conditions will be hidden (Hidden will be set to true).

public void Apply(bool applySort)
Public Sub Apply(applySort As Boolean)
Parameters
applySort
System.Boolean

if set to true perform sorting in addition to filtering.

Exceptions
System.InvalidOperationException

Sort range contains merged cells.

ByCustom(Int32, FilterOperator, Object)

Sets a CustomFilter to a column with the specified index (relative to the Range).

CustomFilter filters by a one or two conditions (operators and values).

public AutoFilter ByCustom(int columnIndex, FilterOperator operator, object value)
Public Function ByCustom(columnIndex As Integer, operator As FilterOperator, value As Object) As AutoFilter
Parameters
columnIndex
System.Int32

The index of a column relative to the Range that a set Top10Filter applies to.

operator
FilterOperator

The operator used by the filter condition.

value
System.Object

The value used by the filter condition.

Returns
AutoFilter

This AutoFilter to specify additional filters or to apply filter.

Remarks

If FilterColumn already specifies a filter (Filter is not equal to null), it will be overridden.

Exceptions
System.ArgumentOutOfRangeException

columnIndex is less than zero or is equal to or greater than Width of the Range.

System.NotSupportedException

Value's type is not supported.

ByCustom(Int32, FilterOperator, Object, Boolean, FilterOperator, Object)

Sets a CustomFilter to a column with the specified index (relative to the Range).

CustomFilter filters by a one or two conditions (operators and values).

public AutoFilter ByCustom(int columnIndex, FilterOperator operator, object value, bool and, FilterOperator operator2, object value2)
Public Function ByCustom(columnIndex As Integer, operator As FilterOperator, value As Object, and As Boolean, operator2 As FilterOperator, value2 As Object) As AutoFilter
Parameters
columnIndex
System.Int32

The index of a column relative to the Range that a set CustomFilter applies to.

operator
FilterOperator

The operator used by the (first) filter condition.

value
System.Object

The value used by the (first) filter condition.

and
System.Boolean

The value indicating whether the two conditions have an "and" relationship. true indicates "and" and false indicates "or".

operator2
FilterOperator

The operator used by the second filter condition.

value2
System.Object

The value used by the second filter condition.

Returns
AutoFilter

This AutoFilter to specify additional filters or to apply filter.

Remarks

If FilterColumn already specifies a filter (Filter is not equal to null), it will be overridden.

Exceptions
System.ArgumentOutOfRangeException

columnIndex is less than zero or is equal to or greater than Width of the Range.

System.NotSupportedException

Value's type is not supported.

ByDynamic(Int32, DynamicFilterType)

Sets a DynamicFilter to a column with the specified index (relative to the Range).

DynamicFilter filters by a dynamic criteria that can change, either with the data itself (e.g., "above average") or with the current system date (e.g., show values for "today").

public AutoFilter ByDynamic(int columnIndex, DynamicFilterType dynamicFilterType)
Public Function ByDynamic(columnIndex As Integer, dynamicFilterType As DynamicFilterType) As AutoFilter
Parameters
columnIndex
System.Int32

The index of a column relative to the Range that a set DynamicFilter applies to.

dynamicFilterType
DynamicFilterType

The dynamic filter type.

Returns
AutoFilter

This AutoFilter to specify additional filters or to apply filter.

Remarks

If FilterColumn already specifies a filter (Filter is not equal to null), it will be overridden.

Exceptions
System.ArgumentOutOfRangeException

columnIndex is less than zero or is equal to or greater than Width of the Range.

ByPredicate(Int32, Predicate<ExcelCell>)

Sets a custom filter method to a column with the specified index (relative to the Range).

Use this method to specify a custom filter behavior which is not provided by any of the FilterTypes.

public AutoFilter ByPredicate(int columnIndex, Predicate<ExcelCell> match)
Public Function ByPredicate(columnIndex As Integer, match As Predicate(Of ExcelCell)) As AutoFilter
Parameters
columnIndex
System.Int32

The index of a column relative to the Range that a set custom filter predicate applies to.

match
System.Predicate<ExcelCell>

A custom filter method.

Returns
AutoFilter

This AutoFilter to specify additional filters or to apply filter.

Remarks

If FilterColumn already specifies a filter (Filter is not equal to null), it won't be removed.

Exceptions
System.ArgumentOutOfRangeException

columnIndex is less than zero or is equal to or greater than Width of the Range.

ByTop10(Int32, Boolean, Boolean, Double)

Sets a Top10Filter to a column with the specified index (relative to the Range).

Top10Filter filters by a top N (percent or number of items).

public AutoFilter ByTop10(int columnIndex, bool top, bool percent, double value)
Public Function ByTop10(columnIndex As Integer, top As Boolean, percent As Boolean, value As Double) As AutoFilter
Parameters
columnIndex
System.Int32

The index of a column relative to the Range that a set Top10Filter applies to.

top
System.Boolean

a value indicating whether or not to filter by top order. A false value filters by bottom order.

percent
System.Boolean

A value indicating whether or not to filter by percent value of the column. A false value filters by number of items.

value
System.Double

The top or bottom value to use as the filter criteria. For example "Filter by Top 10 Percent" or "Filter by Top 5 Items".

Returns
AutoFilter

This AutoFilter to specify additional filters or to apply filter.

Remarks

If FilterColumn already specifies a filter (Filter is not equal to null), it will be overridden.

Exceptions
System.ArgumentOutOfRangeException

columnIndex is less than zero or is equal to or greater than Width of the Range.

System.ArgumentOutOfRangeException

value must be greater than zero.

ByValues(Int32, IEnumerable)

Sets a ValuesFilter to a column with the specified index (relative to the Range).

ValuesFilter filters by a list of values.

The list can contain null, System.String and DateTimeGroup values.

public AutoFilter ByValues(int columnIndex, IEnumerable values)
Public Function ByValues(columnIndex As Integer, values As IEnumerable) As AutoFilter
Parameters
columnIndex
System.Int32

The index of a column relative to the Range that a set ValuesFilter applies to.

values
System.Collections.IEnumerable

The list of values to filter by.

Returns
AutoFilter

This AutoFilter to specify additional filters or to apply filter.

Remarks

If FilterColumn already specifies a filter (Filter is not equal to null), it will be overridden.

Exceptions
System.ArgumentOutOfRangeException

columnIndex is less than zero or is equal to or greater than Width of the Range.

System.NotSupportedException

values contains an item which is not null, System.String or DateTimeGroup.

ByValues(Int32, Object[])

Sets a ValuesFilter to a column with the specified index (relative to the Range).

ValuesFilter filters by a list of values.

The list can contain null, System.String and DateTimeGroup values.

public AutoFilter ByValues(int columnIndex, params object[] values)
Public Function ByValues(columnIndex As Integer, ParamArray values As Object()) As AutoFilter
Parameters
columnIndex
System.Int32

The index of a column relative to the Range that a set ValuesFilter applies to.

values
System.Object[]

The list of values to filter by.

Returns
AutoFilter

This AutoFilter to specify additional filters or to apply filter.

Remarks

If FilterColumn already specifies a filter (Filter is not equal to null), it will be overridden.

Exceptions
System.ArgumentOutOfRangeException

columnIndex is less than zero or is equal to or greater than Width of the Range.

System.NotSupportedException

values contains an item which is not null, System.String or DateTimeGroup.

ShowAll()

Shows all rows (sets Hidden to false) to which Range belongs.

public void ShowAll()
Public Sub ShowAll

SortBy(Int32)

Adds a SortLevel to a column with the specified index (relative to the Range).

public AutoFilter SortBy(int columnIndex)
Public Function SortBy(columnIndex As Integer) As AutoFilter
Parameters
columnIndex
System.Int32

The index of a column relative to the Range that an added SortLevel applies to.

Returns
AutoFilter

This AutoFilter to specify additional filters / sort levels or to apply filter / sort.

Remarks

If FilterColumn already specifies a sort level (Sort is not equal to null), it will be overridden.

Exceptions
System.ArgumentOutOfRangeException

columnIndex is less than zero or is equal to or greater than Width of the Range.

SortBy(Int32, Boolean)

Adds a SortLevel to a column with the specified index (relative to the Range) and with the specified order to sort by.

public AutoFilter SortBy(int columnIndex, bool descending)
Public Function SortBy(columnIndex As Integer, descending As Boolean) As AutoFilter
Parameters
columnIndex
System.Int32

The index of a column relative to the Range that an added SortLevel applies to.

descending
System.Boolean

true to sort the values in the descending order; otherwise, false.

Returns
AutoFilter

This AutoFilter to specify additional filters / sort levels or to apply filter / sort.

Remarks

If FilterColumn already specifies a sort level (Sort is not equal to null), it will be overridden.

Exceptions
System.ArgumentOutOfRangeException

columnIndex is less than zero or is equal to or greater than Width of the Range.

SortBy(Int32, Boolean, IEnumerable<String>)

Adds a SortLevel to a column with the specified index (relative to the Range) and with the specified order and custom list by which order of items to sort by.

public AutoFilter SortBy(int columnIndex, bool descending, IEnumerable<string> customList)
Public Function SortBy(columnIndex As Integer, descending As Boolean, customList As IEnumerable(Of String)) As AutoFilter
Parameters
columnIndex
System.Int32

The index of a column relative to the Range that an added SortLevel applies to.

descending
System.Boolean

true to sort the values in the descending order; otherwise, false.

customList
System.Collections.Generic.IEnumerable<System.String>

The custom list by which order of items to sort by.

Returns
AutoFilter

This AutoFilter to specify additional filters / sort levels or to apply filter / sort.

Remarks

If FilterColumn already specifies a sort level (Sort is not equal to null), it will be overridden.

Exceptions
System.ArgumentOutOfRangeException

columnIndex is less than zero or is equal to or greater than Width of the Range.

SortBy(Int32, Boolean, String[])

Adds a SortLevel to a column with the specified index (relative to the Range) and with the specified order and custom list by which order of items to sort by.

public AutoFilter SortBy(int columnIndex, bool descending, params string[] customList)
Public Function SortBy(columnIndex As Integer, descending As Boolean, ParamArray customList As String()) As AutoFilter
Parameters
columnIndex
System.Int32

The index of a column relative to the Range that an added SortLevel applies to.

descending
System.Boolean

true to sort the values in the descending order; otherwise, false.

customList
System.String[]

The custom list by which order of items to sort by.

Returns
AutoFilter

This AutoFilter to specify additional filters / sort levels or to apply filter / sort.

Remarks

If FilterColumn already specifies a sort level (Sort is not equal to null), it will be overridden.

Exceptions
System.ArgumentOutOfRangeException

columnIndex is less than zero or is equal to or greater than Width of the Range.

SortBy(Int32, IEnumerable<String>)

Adds a SortLevel to a column with the specified index (relative to the Range) and with the specified custom list by which order of items to sort by.

public AutoFilter SortBy(int columnIndex, IEnumerable<string> customList)
Public Function SortBy(columnIndex As Integer, customList As IEnumerable(Of String)) As AutoFilter
Parameters
columnIndex
System.Int32

The index of a column relative to the Range that an added SortLevel applies to.

customList
System.Collections.Generic.IEnumerable<System.String>

The custom list by which order of items to sort by.

Returns
AutoFilter

This AutoFilter to specify additional filters / sort levels or to apply filter / sort.

Remarks

If FilterColumn already specifies a sort level (Sort is not equal to null), it will be overridden.

Exceptions
System.ArgumentOutOfRangeException

columnIndex is less than zero or is equal to or greater than Width of the Range.

SortBy(Int32, Comparison<ExcelCell>)

Adds a SortLevel to a column with the specified index (relative to the Range) and with the specified comparison method to sort by.

public AutoFilter SortBy(int columnIndex, Comparison<ExcelCell> comparison)
Public Function SortBy(columnIndex As Integer, comparison As Comparison(Of ExcelCell)) As AutoFilter
Parameters
columnIndex
System.Int32

The index of a column relative to the Range that an added SortLevel applies to.

comparison
System.Comparison<ExcelCell>

The comparison method to sort by.

Returns
AutoFilter

This AutoFilter to specify additional filters / sort levels or to apply filter / sort.

Remarks

If FilterColumn already specifies a sort level (Sort is not equal to null), it will be overridden.

Exceptions
System.ArgumentOutOfRangeException

columnIndex is less than zero or is equal to or greater than Width of the Range.

SortBy(Int32, String[])

Adds a SortLevel to a column with the specified index (relative to the Range) and with the specified custom list by which order of items to sort by.

public AutoFilter SortBy(int columnIndex, params string[] customList)
Public Function SortBy(columnIndex As Integer, ParamArray customList As String()) As AutoFilter
Parameters
columnIndex
System.Int32

The index of a column relative to the Range that an added SortLevel applies to.

customList
System.String[]

The custom list by which order of items to sort by.

Returns
AutoFilter

This AutoFilter to specify additional filters / sort levels or to apply filter / sort.

Remarks

If FilterColumn already specifies a sort level (Sort is not equal to null), it will be overridden.

Exceptions
System.ArgumentOutOfRangeException

columnIndex is less than zero or is equal to or greater than Width of the Range.

ToString()

Returns a System.String that represents this AutoFilter instance.

public override string ToString()
Public Overrides Function ToString As String
Returns
System.String

A System.String that represents this AutoFilter instance.

Overrides
System.Object.ToString()
Remarks

This method should be used primarily for debugging purposes and should be considered volatile (format of its return value might change in future versions).

Examples