Class AutoFilter
Namespace: Aspose.Cells
Assembly: Aspose.Cells.dll (25.2.0)
Represents autofiltering for the specified worksheet.
public class AutoFilterInheritance
Inherited Members
object.GetType(), object.MemberwiseClone(), object.ToString(), object.Equals(object?), object.Equals(object?, object?), object.ReferenceEquals(object?, object?), object.GetHashCode()
Examples
//Creating a file stream containing the Excel file to be opened
//Instantiating a Workbook object
Workbook workbook = new Workbook("template.xlsx");
//Accessing the first worksheet in the Excel file
Worksheet worksheet = workbook.Worksheets[0];
//Creating AutoFilter by giving the cells range of the heading row
worksheet.AutoFilter.Range = "A1:B1";
//Filtering columns with specified values
worksheet.AutoFilter.Filter(1, "Bananas");
//Saving the modified Excel file.
workbook.Save("output.xls");'Creating a file stream containing the Excel file to be opened
'Instantiating a Workbook object
Dim workbook As Workbook = New Workbook("template.xlsx")
'Accessing the first worksheet in the Excel file
Dim worksheet As Worksheet = workbook.Worksheets(0)
'Creating AutoFilter by giving the cells range of the heading row
worksheet.AutoFilter.Range = "A1:B1"
'Filtering columns with specified values
Worksheet.AutoFilter.Filter(1, "Bananas")
'Saving the modified Excel file
workbook.Save("output.xls")Properties
FilterColumns
Gets the collection of the filter columns.
public FilterColumnCollection FilterColumns { get; }Property Value
Range
Represents the range to which the specified AutoFilter applies.
public string Range { get; set; }Property Value
ShowFilterButton
Indicates whether the AutoFilter button for this column is visible.
public bool ShowFilterButton { get; set; }Property Value
Sorter
Gets the data sorter.
public DataSorter Sorter { get; }Property Value
Methods
AddDateFilter(int, DateTimeGroupingType, int, int, int, int, int, int)
Adds a date filter.
public void AddDateFilter(int fieldIndex, DateTimeGroupingType dateTimeGroupingType, int year, int month, int day, int hour, int minute, int second)Parameters
fieldIndex int
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 int
The year.
month int
The month.
day int
The day.
hour int
The hour.
minute int
The minute.
second int
The second.
Remarks
If DateTimeGroupingType is Year, only the param year effects. If DateTiemGroupingType is Month, only the param year and month effect.
AddFillColorFilter(int, BackgroundType, CellsColor, CellsColor)
Adds a fill color filter.
public void AddFillColorFilter(int fieldIndex, BackgroundType pattern, CellsColor foregroundColor, CellsColor backgroundColor)Parameters
fieldIndex int
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.
AddFilter(int, string)
Adds a filter for a filter column.
public void AddFilter(int fieldIndex, string criteria)Parameters
fieldIndex int
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.
AddFontColorFilter(int, CellsColor)
Adds a font color filter.
public void AddFontColorFilter(int fieldIndex, CellsColor color)Parameters
fieldIndex int
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 Aspose.Cells.CellsColor object.
AddIconFilter(int, IconSetType, int)
Adds an icon filter.
public void AddIconFilter(int fieldIndex, IconSetType iconSetType, int iconId)Parameters
fieldIndex int
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 int
The icon id.
Remarks
Only supports to add the icon filter. Not supports checking which row is visible if the filter is icon filter.
Custom(int, FilterOperatorType, object)
Filters a list with a custom criteria.
public void Custom(int fieldIndex, FilterOperatorType operatorType1, object criteria1)Parameters
fieldIndex int
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(int, FilterOperatorType, object, bool, FilterOperatorType, object)
Filters a list with custom criteria.
public void Custom(int fieldIndex, FilterOperatorType operatorType1, object criteria1, bool isAnd, FilterOperatorType operatorType2, object criteria2)Parameters
fieldIndex int
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 bool
operatorType2 FilterOperatorType
The filter operator type
criteria2 object
The custom criteria
DynamicFilter(int, DynamicFilterType)
Adds a dynamic filter.
public void DynamicFilter(int fieldIndex, DynamicFilterType dynamicFilterType)Parameters
fieldIndex int
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.
Filter(int, string)
Filters a list with specified criteria.
public void Filter(int fieldIndex, string criteria)Parameters
fieldIndex int
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(int, bool, bool, int)
Filter the top 10 item in the list
public void FilterTop10(int fieldIndex, bool isTop, bool isPercent, int itemCount)Parameters
fieldIndex int
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 bool
Indicates whether filter from top or bottom
isPercent bool
Indicates whether the items is percent or count
itemCount int
The item count
GetCellArea()
Gets the Aspose.Cells.CellArea where the this AutoFilter applies to.
public CellArea GetCellArea()Returns
the area this filter applies to
GetCellArea(bool)
Gets the Aspose.Cells.CellArea where the specified AutoFilter applies to.
public CellArea GetCellArea(bool refreshAppliedRange)Parameters
refreshAppliedRange bool
Whether refresh the applied range. For the applied range of auto filter, the last row may change when cells data changes. If this flag is true, then the last row of the range will be re-calculated according to current cells data.
Returns
the area this filter applies to
MatchBlanks(int)
Match all blank cell in the list.
public void MatchBlanks(int fieldIndex)Parameters
fieldIndex int
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(int)
Match all not blank cell in the list.
public void MatchNonBlanks(int fieldIndex)Parameters
fieldIndex int
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).
Refresh()
Refresh auto filters to hide or unhide the rows.
public int[] Refresh()Returns
int[]
Returns all hidden rows’ indexes.
Refresh(bool)
Gets all hidden rows’ indexes.
public int[] Refresh(bool hideRows)Parameters
hideRows bool
If true, hide the filtered rows.
Returns
int[]
Returns all hidden rows indexes.
RemoveDateFilter(int, DateTimeGroupingType, int, int, int, int, int, int)
Removes a date filter.
public void RemoveDateFilter(int fieldIndex, DateTimeGroupingType dateTimeGroupingType, int year, int month, int day, int hour, int minute, int second)Parameters
fieldIndex int
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 int
The year.
month int
The month.
day int
The day.
hour int
The hour.
minute int
The minute.
second int
The second.
Remarks
If DateTimeGroupingType is Year, only the param year effects. If DateTiemGroupingType is Month, only the param year and month effect.
RemoveFilter(int, string)
Removes a filter for a filter column.
public void RemoveFilter(int fieldIndex, string criteria)Parameters
fieldIndex int
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(int)
Remove the specific filter.
public void RemoveFilter(int fieldIndex)Parameters
fieldIndex int
The specific filter index
SetRange(int, int, int)
Sets the range to which the specified AutoFilter applies.
public void SetRange(int row, int startColumn, int endColumn)Parameters
row int
Row index.
startColumn int
Start column index.
endColumn int
End column Index.
ShowAll()
Unhide all rows.
public void ShowAll()