AutoFilter

AutoFilter class

Represents autofiltering for the specified worksheet.

public class AutoFilter

Properties

Name Description
FilterColumns { get; } Gets the collection of the filter columns.
Range { get; set; } Represents the range to which the specified AutoFilter applies.
ShowFilterButton { get; set; } Indicates whether the AutoFilter button for this column is visible.
Sorter { get; } Gets the data sorter.

Methods

Name Description
AddDateFilter(int, DateTimeGroupingType, int, int, int, int, int, int) Adds a date filter.
AddFillColorFilter(int, BackgroundType, CellsColor, CellsColor) Adds a fill color filter.
AddFilter(int, string) Adds a filter for a filter column.
AddFontColorFilter(int, CellsColor) Adds a font color filter.
AddIconFilter(int, IconSetType, int) Adds an icon filter.
Custom(int, FilterOperatorType, object) Filters a list with a custom criteria.
Custom(int, FilterOperatorType, object, bool, FilterOperatorType, object) Filters a list with custom criteria.
DynamicFilter(int, DynamicFilterType) Adds a dynamic filter.
Filter(int, string) Filters a list with specified criteria.
FilterTop10(int, bool, bool, int) Filter the top 10 item in the list
GetCellArea() Gets the CellArea where the specified AutoFilter applies to.
MatchBlanks(int) Match all blank cell in the list.
MatchNonBlanks(int) Match all not blank cell in the list.
Refresh() Refresh auto filters to hide or unhide the rows.
Refresh(bool) Gets all hidden rows’ indexes.
RemoveDateFilter(int, DateTimeGroupingType, int, int, int, int, int, int) Removes a date filter.
RemoveFilter(int) Remove the specific filter.
RemoveFilter(int, string) Removes a filter for a filter column.
SetRange(int, int, int) Sets the range to which the specified AutoFilter applies.
ShowAll() Unhide all rows.

Examples


[C#]

//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");

[Visual Basic]
   
'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")

See Also