public final class AutoFilter extends Object
Use CellRange
filter methods to create or get an AutoFilter
instance.
Active AutoFilter
is stored in the parent worksheet (ExcelWorksheet.getFilter()
) and is loaded from and saved to XLSX file.
public static void filter1(ExcelWorksheet worksheet) {
// Specify and apply Filter using less-verbose, fluent, but less understandable interface.
worksheet.getCells().getSubrange("A1", "D20").filter().
byValues(0, null, "$5,400.00", "John Doe", new DateTimeGroup(2015, 5)).
byTop10(1, true, true, 20).
byCustom(2, FilterOperator.GREATER_THAN_OR_EQUAL, 2000, true, FilterOperator.LESS_THAN_OR_EQUAL, 8000).
byDynamic(3, DynamicFilterType.ABOVE_AVERAGE).
apply();
}
public static void filter2(ExcelWorksheet worksheet) {
// Specify and apply Filter using more verbose, but more understandable interface.
AutoFilter filter = worksheet.getCells().getSubrange("A1", "D20").filter();
ValuesFilter valuesFilter = filter.getColumns().get(0).setValuesFilter();
valuesFilter.setValues(null, "$5,400.00", "John Doe", new DateTimeGroup(2015, 5));
Top10Filter top10Filter = filter.getColumns().get(1).setTop10Filter();
top10Filter.setTop(true);
top10Filter.setPercent(true);
top10Filter.setValue(20);
CustomFilter customFilter = filter.getColumns().get(2).setCustomFilter();
customFilter.setOperator(FilterOperator.GREATER_THAN_OR_EQUAL);
customFilter.setValue(2000);
customFilter.setAnd(Optional.of(true));
customFilter.setOperator2(FilterOperator.LESS_THAN_OR_EQUAL);
customFilter.setValue2(8000);
DynamicFilter dynamicFilter = filter.getColumns().get(3).setDynamicFilter();
dynamicFilter.setDynamicFilterType(DynamicFilterType.ABOVE_AVERAGE);
filter.apply();
}
Modifier and Type | Method and Description |
---|---|
void |
apply()
Performs the filtering and sorting of this
Range based on this AutoFilter settings. |
void |
apply(boolean applySort)
Performs the filtering and (optionally) sorting of this
Range based on this AutoFilter settings. |
AutoFilter |
byCustom(int columnIndex,
FilterOperator operator,
Object value)
Sets a
CustomFilter to a column with the specified index (relative to the Range ). |
AutoFilter |
byCustom(int columnIndex,
FilterOperator operator,
Object value,
boolean and,
FilterOperator operator2,
Object value2)
Sets a
CustomFilter to a column with the specified index (relative to the Range ). |
AutoFilter |
byDynamic(int columnIndex,
DynamicFilterType dynamicFilterType)
Sets a
DynamicFilter to a column with the specified index (relative to the Range ). |
AutoFilter |
byPredicate(int columnIndex,
Predicate<ExcelCell> match)
Sets a custom filter method to a column with the specified index (relative to the
Range ). |
AutoFilter |
byTop10(int columnIndex,
boolean top,
boolean percent,
double value)
Sets a
Top10Filter to a column with the specified index (relative to the Range ). |
AutoFilter |
byValues(int columnIndex,
Iterable values)
Sets a
ValuesFilter to a column with the specified index (relative to the Range ). |
AutoFilter |
byValues(int columnIndex,
Object... values)
Sets a
ValuesFilter to a column with the specified index (relative to the Range ). |
FilterColumnCollection |
getColumns()
Gets the filter columns.
|
CellRange |
getRange()
Gets the range to filter.
|
SortState |
getSort()
Gets the
AutoFilter sort state. |
void |
showAll()
|
AutoFilter |
sortBy(int columnIndex)
|
AutoFilter |
sortBy(int columnIndex,
boolean descending)
|
AutoFilter |
sortBy(int columnIndex,
boolean descending,
Iterable<String> customList)
|
AutoFilter |
sortBy(int columnIndex,
boolean descending,
String... customList)
|
AutoFilter |
sortBy(int columnIndex,
Comparator<ExcelCell> comparison)
|
AutoFilter |
sortBy(int columnIndex,
Iterable<String> customList)
|
AutoFilter |
sortBy(int columnIndex,
String... customList)
|
String |
toString()
Returns a
String that represents this AutoFilter instance. |
public void 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
).
This method has the same affect as apply(boolean)
with parameter set to true
.
IllegalStateException
- Sort range contains merged cells.public void apply(boolean applySort)
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
).
applySort
- if set to true
perform sorting in addition to filtering.IllegalStateException
- Sort range contains merged cells.public AutoFilter byCustom(int columnIndex, FilterOperator operator, Object value)
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).
If FilterColumn
already specifies a filter (FilterColumn.getFilter()
is not equal to null
), it will be overridden.
columnIndex
- The index of a column relative to the Range
that a set Top10Filter
applies to.operator
- The operator used by the filter condition.value
- The value used by the filter condition.AutoFilter
to specify additional filters or to apply filter.IndexOutOfBoundsException
- columnIndex
is less than zero or is equal to or greater than CellRange.getWidth()
of the Range
.UnsupportedOperationException
- Value's type is not supported.public AutoFilter byCustom(int columnIndex, FilterOperator operator, Object value, boolean and, FilterOperator operator2, Object value2)
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).
If FilterColumn
already specifies a filter (FilterColumn.getFilter()
is not equal to null
), it will be overridden.
columnIndex
- The index of a column relative to the Range
that a set CustomFilter
applies to.operator
- The operator used by the (first) filter condition.value
- The value used by the (first) filter condition.and
- The value indicating whether the two conditions have an "and" relationship. true
indicates "and" and false
indicates "or".operator2
- The operator used by the second filter condition.value2
- The value used by the second filter condition.AutoFilter
to specify additional filters or to apply filter.IndexOutOfBoundsException
- columnIndex
is less than zero or is equal to or greater than CellRange.getWidth()
of the Range
.UnsupportedOperationException
- Value's type is not supported.public AutoFilter byDynamic(int columnIndex, DynamicFilterType 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").
If FilterColumn
already specifies a filter (FilterColumn.getFilter()
is not equal to null
), it will be overridden.
columnIndex
- The index of a column relative to the Range
that a set DynamicFilter
applies to.dynamicFilterType
- The dynamic filter type.AutoFilter
to specify additional filters or to apply filter.IndexOutOfBoundsException
- columnIndex
is less than zero or is equal to or greater than CellRange.getWidth()
of the Range
.public AutoFilter byPredicate(int columnIndex, Predicate<ExcelCell> match)
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 FilterType
s.
If FilterColumn
already specifies a filter (FilterColumn.getFilter()
is not equal to null
), it won't be removed.
columnIndex
- The index of a column relative to the Range
that a set custom filter predicate applies to.match
- A custom filter method.AutoFilter
to specify additional filters or to apply filter.IndexOutOfBoundsException
- columnIndex
is less than zero or is equal to or greater than CellRange.getWidth()
of the Range
.public AutoFilter byTop10(int columnIndex, boolean top, boolean percent, double value)
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).
If FilterColumn
already specifies a filter (FilterColumn.getFilter()
is not equal to null
), it will be overridden.
columnIndex
- The index of a column relative to the Range
that a set Top10Filter
applies to.top
- a value indicating whether or not to filter by top order. A false
value filters by bottom order.percent
- A value indicating whether or not to filter by percent value of the column. A false
value filters by number of items.value
- The top or bottom value to use as the filter criteria. For example "Filter by Top 10 Percent" or "Filter by Top 5 Items".AutoFilter
to specify additional filters or to apply filter.IndexOutOfBoundsException
- columnIndex
is less than zero or is equal to or greater than CellRange.getWidth()
of the getRange()
.IndexOutOfBoundsException
- value
must be greater than zero.public AutoFilter byValues(int columnIndex, Iterable values)
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
, String
and DateTimeGroup
values.
If FilterColumn
already specifies a filter (Filter
is not equal to null
), it will be overridden.
columnIndex
- The index of a column relative to the Range
that a set ValuesFilter
applies to.values
- The list of values to filter by.AutoFilter
to specify additional filters or to apply filter.IndexOutOfBoundsException
- columnIndex
is less than zero or is equal to or greater than CellRange.getWidth()
of the getRange()
.UnsupportedOperationException
- values
contains an item which is not null
, String
or DateTimeGroup
.public AutoFilter byValues(int columnIndex, Object... values)
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
, String
and DateTimeGroup
values.
If FilterColumn
already specifies a filter (FilterColumn.getFilter()
is not equal to null
), it will be overridden.
columnIndex
- The index of a column relative to the range
that a set ValuesFilter
applies to.values
- The list of values to filter by.AutoFilter
to specify additional filters or to apply filter.IndexOutOfBoundsException
- columnIndex
is less than zero or is equal to or greater than width
of the getRange()
.UnsupportedOperationException
- values
contains an item which is not null
, String
or DateTimeGroup
.public FilterColumnCollection getColumns()
public CellRange getRange()
public SortState getSort()
AutoFilter
sort state.AutoFilter
sort state.public void showAll()
public AutoFilter sortBy(int columnIndex)
SortLevel
to a column with the specified index (relative to the Range
).
If FilterColumn
already specifies a sort level (FilterColumn.getSort()
is not equal to null
), it will be overridden.
columnIndex
- The index of a column relative to the Range
that an added SortLevel
applies to.AutoFilter
to specify additional filters / sort levels or to apply filter / sort.IndexOutOfBoundsException
- columnIndex
is less than zero or is equal to or greater than CellRange.getWidth()
of the Range
.public AutoFilter sortBy(int columnIndex, boolean descending)
SortLevel
to a column with the specified index (relative to the Range
) and with the specified order to sort by.
If FilterColumn
already specifies a sort level (FilterColumn.getSort()
is not equal to null
), it will be overridden.
columnIndex
- The index of a column relative to the Range
that an added SortLevel
applies to.descending
- true
to sort the values in the descending order; otherwise, false
.AutoFilter
to specify additional filters / sort levels or to apply filter / sort.IndexOutOfBoundsException
- columnIndex
is less than zero or is equal to or greater than CellRange.getWidth()
of the Range
.public AutoFilter sortBy(int columnIndex, boolean descending, Iterable<String> customList)
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.
If FilterColumn
already specifies a sort level (FilterColumn.getSort()
is not equal to null
), it will be overridden.
columnIndex
- The index of a column relative to the Range
that an added SortLevel
applies to.descending
- true
to sort the values in the descending order; otherwise, false
.customList
- The custom list by which order of items to sort by.AutoFilter
to specify additional filters / sort levels or to apply filter / sort.IndexOutOfBoundsException
- columnIndex
is less than zero or is equal to or greater than CellRange.getWidth()
of the Range
.public AutoFilter sortBy(int columnIndex, boolean descending, String... customList)
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.
If FilterColumn
already specifies a sort level (FilterColumn.getSort()
is not equal to null
), it will be overridden.
columnIndex
- The index of a column relative to the Range
that an added SortLevel
applies to.descending
- true
to sort the values in the descending order; otherwise, false
.customList
- The custom list by which order of items to sort by.AutoFilter
to specify additional filters / sort levels or to apply filter / sort.IndexOutOfBoundsException
- columnIndex
is less than zero or is equal to or greater than CellRange.getWidth()
of the Range
.public AutoFilter sortBy(int columnIndex, Comparator<ExcelCell> comparison)
SortLevel
to a column with the specified index (relative to the Range
) and with the specified comparison method to sort by.
If FilterColumn
already specifies a sort level (FilterColumn.getSort()
is not equal to null
), it will be overridden.
columnIndex
- The index of a column relative to the Range
that an added SortLevel
applies to.comparison
- The comparison method to sort by.AutoFilter
to specify additional filters / sort levels or to apply filter / sort.IndexOutOfBoundsException
- columnIndex
is less than zero or is equal to or greater than CellRange.getWidth()
of the Range
.public AutoFilter sortBy(int columnIndex, Iterable<String> customList)
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.
If FilterColumn
already specifies a sort level (FilterColumn.getSort()
is not equal to null
), it will be overridden.
columnIndex
- The index of a column relative to the Range
that an added SortLevel
applies to.customList
- The custom list by which order of items to sort by.AutoFilter
to specify additional filters / sort levels or to apply filter / sort.IndexOutOfBoundsException
- columnIndex
is less than zero or is equal to or greater than CellRange.getWidth()
of the Range
.public AutoFilter sortBy(int columnIndex, String... customList)
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.
If FilterColumn
already specifies a sort level (FilterColumn.getSort()
is not equal to null
), it will be overridden.
columnIndex
- The index of a column relative to the Range
that an added SortLevel
applies to.customList
- The custom list by which order of items to sort by.AutoFilter
to specify additional filters / sort levels or to apply filter / sort.IndexOutOfBoundsException
- columnIndex
is less than zero or is equal to or greater than CellRange.getWidth()
of the Range
.public String toString()
String
that represents this AutoFilter
instance.
This method should be used primarily for debugging purposes and should be considered volatile (format of its return value might change in future versions).
toString
in class Object
String
that represents this AutoFilter
instance.© GemBox Ltd. — All rights reserved.