AutoFilter

AutoFilter class

Represents autofiltering for the specified worksheet.

class AutoFilter;

Example

const { Workbook } = require("aspose.cells.node");

//Instantiating a Workbook object
var workbook = new Workbook("input/AutoFilter.xlsx");
//Accessing the first worksheet in the Excel file
var worksheet = workbook.getWorksheets().get(0);
//Creating AutoFilter by giving the cells range of the heading row
worksheet.getAutoFilter().setRange("A1:C1");
//Filtering columns with specified values
worksheet.getAutoFilter().filter(2, "present");
// Refreshing auto filters to hide or unhide the rows.
worksheet.getAutoFilter().refresh();
//Saving the modified Excel file.
workbook.save("output/AutoFilter.xlsx");

Methods

Method	Description
getSorter()	Gets the data sorter.
getRange()	Represents the range to which the specified AutoFilter applies.
setRange(string)	Represents the range to which the specified AutoFilter applies.
getShowFilterButton()	Indicates whether the AutoFilter button for this column is visible.
setShowFilterButton(boolean)	Indicates whether the AutoFilter button for this column is visible.
getFilterColumns()	Gets the collection of the filter columns.
setRange(number, number, number)	Sets the range to which the specified AutoFilter applies.
getCellArea()	Gets the CellArea where the specified AutoFilter applies to.
addFilter(number, string)	Adds a filter for a filter column.
addDateFilter(number, DateTimeGroupingType, number, number, number, number, number, number)	Adds a date filter.
removeDateFilter(number, DateTimeGroupingType, number, number, number, number, number, number)	Removes a date filter.
removeFilter(number, string)	Removes a filter for a filter column.
removeFilter(number)	Remove the specific filter.
filter(number, string)	Filters a list with specified criteria.
filterTop10(number, boolean, boolean, number)	Filter the top 10 item in the list
dynamic_Filter(number, DynamicFilterType)	Adds a dynamic filter.
addFontColorFilter(number, CellsColor)	Adds a font color filter.
addFillColorFilter(number, BackgroundType, CellsColor, CellsColor)	Adds a fill color filter.
addIconFilter(number, IconSetType, number)	Adds an icon filter.
matchBlanks(number)	Match all blank cell in the list.
matchNonBlanks(number)	Match all not blank cell in the list.
custom(number, FilterOperatorType, object)	Filters a list with a custom criteria.
custom(number, FilterOperatorType, object, boolean, FilterOperatorType, object)	Filters a list with custom criteria.
showAll()	Unhide all rows.
refresh()	Refresh auto filters to hide or unhide the rows.
refresh(boolean)	Gets all hidden rows’ indexes.
isNull()	Checks whether the implementation object is null.

getSorter()

Gets the data sorter.

getSorter() : DataSorter;

Returns

DataSorter

getRange()

Represents the range to which the specified AutoFilter applies.

getRange() : string;

setRange(string)

Represents the range to which the specified AutoFilter applies.

setRange(value: string) : void;

Parameters:

Parameter	Type	Description
value	string	The value to set.

getShowFilterButton()

Indicates whether the AutoFilter button for this column is visible.

getShowFilterButton() : boolean;

setShowFilterButton(boolean)

Indicates whether the AutoFilter button for this column is visible.

setShowFilterButton(value: boolean) : void;

Parameters:

Parameter	Type	Description
value	boolean	The value to set.

getFilterColumns()

Gets the collection of the filter columns.

getFilterColumns() : FilterColumnCollection;

Returns

FilterColumnCollection

setRange(number, number, number)

Sets the range to which the specified AutoFilter applies.

setRange(row: number, startColumn: number, endColumn: number) : void;

Parameters:

Parameter	Type	Description
row	number	Row index.
startColumn	number	Start column index.
endColumn	number	End column Index.

getCellArea()

Gets the CellArea where the specified AutoFilter applies to.

getCellArea() : CellArea;

Returns

CellArea

addFilter(number, string)

Adds a filter for a filter column.

addFilter(fieldIndex: number, criteria: string) : void;

Parameters:

Parameter	Type	Description
fieldIndex	number	The integer offset of the field on which you want to base the filter /// (from the left of the list; the leftmost field is field 0).
criteria	string	The specified criteria (a string; for example, “101”). /// It only can be null or be one of the cells’ value in this column.

Remarks

MS Excel 2007 supports multiple selection in a filter column.

addDateFilter(number, DateTimeGroupingType, number, number, number, number, number, number)

Adds a date filter.

addDateFilter(fieldIndex: number, dateTimeGroupingType: DateTimeGroupingType, year: number, month: number, day: number, hour: number, minute: number, second: number) : void;

Parameters:

Parameter	Type	Description
fieldIndex	number	The integer offset of the field on which you want to base the filter /// (from the left of the list; the leftmost field is field 0).
dateTimeGroupingType	DateTimeGroupingType	The grouping type
year	number	The year.
month	number	The month.
day	number	The day.
hour	number	The hour.
minute	number	The minute.
second	number	The second.

Remarks

If DateTimeGroupingType is Year, only the param year effects. If DateTiemGroupingType is Month, only the param year and month effect.

removeDateFilter(number, DateTimeGroupingType, number, number, number, number, number, number)

Removes a date filter.

removeDateFilter(fieldIndex: number, dateTimeGroupingType: DateTimeGroupingType, year: number, month: number, day: number, hour: number, minute: number, second: number) : void;

Parameters:

Parameter	Type	Description
fieldIndex	number	The integer offset of the field on which you want to base the filter /// (from the left of the list; the leftmost field is field 0).
dateTimeGroupingType	DateTimeGroupingType	The grouping type
year	number	The year.
month	number	The month.
day	number	The day.
hour	number	The hour.
minute	number	The minute.
second	number	The second.

Remarks

If DateTimeGroupingType is Year, only the param year effects. If DateTiemGroupingType is Month, only the param year and month effect.

removeFilter(number, string)

Removes a filter for a filter column.

removeFilter(fieldIndex: number, criteria: string) : void;

Parameters:

Parameter	Type	Description
fieldIndex	number	The integer offset of the field on which you want to base the filter /// (from the left of the list; the leftmost field is field 0).
criteria	string	The specified criteria (a string; for example, “101”). /// It only can be null or be one of the cells’ value in this column.

removeFilter(number)

Remove the specific filter.

removeFilter(fieldIndex: number) : void;

Parameters:

Parameter	Type	Description
fieldIndex	number	The specific filter index

filter(number, string)

Filters a list with specified criteria.

filter(fieldIndex: number, criteria: string) : void;

Parameters:

Parameter	Type	Description
fieldIndex	number	The integer offset of the field on which you want to base the filter /// (from the left of the list; the leftmost field is field 0).
criteria	string	The specified criteria (a string; for example, “101”).

Remarks

Aspose.Cells will remove all other filter setting on this field as Ms Excel 97-2003.

filterTop10(number, boolean, boolean, number)

Filter the top 10 item in the list

filterTop10(fieldIndex: number, isTop: boolean, isPercent: boolean, itemCount: number) : void;

Parameters:

Parameter	Type	Description
fieldIndex	number	The integer offset of the field on which you want to base the filter /// (from the left of the list; the leftmost field is field 0).
isTop	boolean	Indicates whether filter from top or bottom
isPercent	boolean	Indicates whether the items is percent or count
itemCount	number	The item count

dynamic_Filter(number, DynamicFilterType)

Adds a dynamic filter.

dynamic_Filter(fieldIndex: number, dynamicFilterType: DynamicFilterType) : void;

Parameters:

Parameter	Type	Description
fieldIndex	number	The integer offset of the field on which you want to base the filter /// (from the left of the list; the leftmost field is field 0).
dynamicFilterType	DynamicFilterType	Dynamic filter type.

addFontColorFilter(number, CellsColor)

Adds a font color filter.

addFontColorFilter(fieldIndex: number, color: CellsColor) : void;

Parameters:

Parameter	Type	Description
fieldIndex	number	The integer offset of the field on which you want to base the filter /// (from the left of the list; the leftmost field is field 0).
color	CellsColor	The CellsColor object.

addFillColorFilter(number, BackgroundType, CellsColor, CellsColor)

Adds a fill color filter.

addFillColorFilter(fieldIndex: number, pattern: BackgroundType, foregroundColor: CellsColor, backgroundColor: CellsColor) : void;

Parameters:

Parameter	Type	Description
fieldIndex	number	The integer offset of the field on which you want to base the filter /// (from the left of the list; the leftmost field is field 0).
pattern	BackgroundType	The background pattern type.
foregroundColor	CellsColor	The foreground color.
backgroundColor	CellsColor	The background color.

addIconFilter(number, IconSetType, number)

Adds an icon filter.

addIconFilter(fieldIndex: number, iconSetType: IconSetType, iconId: number) : void;

Parameters:

Parameter	Type	Description
fieldIndex	number	The integer offset of the field on which you want to base the filter /// (from the left of the list; the leftmost field is field 0).
iconSetType	IconSetType	The icon set type.
iconId	number	The icon id.

Remarks

Only supports to add the icon filter. Not supports checking which row is visible if the filter is icon filter.

matchBlanks(number)

Match all blank cell in the list.

matchBlanks(fieldIndex: number) : void;

Parameters:

Parameter	Type	Description
fieldIndex	number	The integer offset of the field on which you want to base the filter /// (from the left of the list; the leftmost field is field 0).

matchNonBlanks(number)

Match all not blank cell in the list.

matchNonBlanks(fieldIndex: number) : void;

Parameters:

Parameter	Type	Description
fieldIndex	number	The integer offset of the field on which you want to base the filter /// (from the left of the list; the leftmost field is field 0).

custom(number, FilterOperatorType, object)

Filters a list with a custom criteria.

custom(fieldIndex: number, operatorType1: FilterOperatorType, criteria1: object) : void;

Parameters:

Parameter	Type	Description
fieldIndex	number	The integer offset of the field on which you want to base the filter /// (from the left of the list; the leftmost field is field 0).
operatorType1	FilterOperatorType	The filter operator type
criteria1	object	The custom criteria

custom(number, FilterOperatorType, object, boolean, FilterOperatorType, object)

Filters a list with custom criteria.

custom(fieldIndex: number, operatorType1: FilterOperatorType, criteria1: object, isAnd: boolean, operatorType2: FilterOperatorType, criteria2: object) : void;

Parameters:

Parameter	Type	Description
fieldIndex	number	The integer offset of the field on which you want to base the filter /// (from the left of the list; the leftmost field is field 0).
operatorType1	FilterOperatorType	The filter operator type
criteria1	object	The custom criteria
isAnd	boolean
operatorType2	FilterOperatorType	The filter operator type
criteria2	object	The custom criteria

showAll()

Unhide all rows.

showAll() : void;

refresh()

Refresh auto filters to hide or unhide the rows.

refresh() : number[];

Returns

Returns all hidden rows’ indexes.

refresh(boolean)

Gets all hidden rows’ indexes.

refresh(hideRows: boolean) : number[];

Parameters:

Parameter	Type	Description
hideRows	boolean	If true, hide the filtered rows.

Returns

Returns all hidden rows indexes.

isNull()

Checks whether the implementation object is null.

isNull() : boolean;