AutoFilter
- interface ClosedXML.Excel.IXLAutoFilter
Autofilter can sort and filter (hide) values in a non-empty area of a sheet. Each table can have autofilter and each worksheet can have at most one range with an autofilter. First row of the area contains headers, remaining rows contain sorted and filtered data.
Sorting of rows is done Sort method, using the passed parameters. The sort properties (SortColumn and SortOrder) are updated from properties passed to the Sort method. Sorting can be done only on values of one column.
Autofilter can filter rows through Reapply method. The filter evaluates conditions of the autofilter and leaves visible only rows that satisfy the conditions. Rows that don’t satisfy filter conditions are marked as hidden. Filter conditions can be specified for each column (accessible through Column(string) methods), e.g.
sheet.AutoFilter.Column(1).Top(10, XLTopBottomType.Percent)creates a filter that displays only rows with values in top 10% percentile.Subclassed by XLAutoFilter
Public Functions
- IXLAutoFilter Clear ()
Disable autofilter, remove all filters and unhide all rows of the Range.
- IXLFilterColumn Column (String columnLetter)
Get filter configuration for a column.
- Param columnLetter
Column letter that determines number in the range, from A as the first column of a Range.
- Throws ArgumentOutOfRangeException
Invalid column.
- Return
Filter configuration for the column.
- IXLFilterColumn Column (Int32 columnNumber)
Get filter configuration for a column.
- Param columnNumber
Column number in the range, from 1 as the first column of a Range.
- Return
Filter configuration for the column.
- IXLAutoFilter Reapply ()
Apply autofilter filters to the range and show every row that satisfies the conditions and hide the ones that don’t satisfy conditions.
Filter is generally automatically applied on a filter change. This method could be called after a cell value change or row deletion.
- IXLAutoFilter Sort (Int32 columnToSortBy = 1, XLSortOrder sortOrder = XLSortOrder.Ascending, Boolean matchCase = false, Boolean ignoreBlanks = true)
Sort rows of the range using data of one column.
This method sets Sorted, SortColumn and SortOrder properties.
- Param columnToSortBy
Column number in the range, from 1 to width of the Range.
- Param sortOrder
Should rows be sorted in ascending or descending order?
- Param matchCase
Should XLDataType.Text values on the column be matched case sensitive.
- Param ignoreBlanks
true- rows with blank value in the column will always at the end, regardless of sorting order.false- blank will be treated as empty string and sorted accordingly.
Properties
- IXLRangeRow > HiddenRows { get; set; }
Get rows of Range that were hidden because they didn’t satisfy filter conditions during last filtering.
Visibility is automatically updated on filter change.
- Boolean IsEnabled { get; set; }
Is autofilter enabled? When autofilter is enabled, it shows the arrow buttons and might contain some filter that hide some rows. Disabled autofilter doesn’t show arrow buttons and all rows are visible.
- IXLRange Range { get; set; }
Range of the autofilter. It consists of a header in first row, followed by data rows. It doesn’t include totals row for tables.
- Int32 SortColumn { get; set; }
What column was used during last Sort. Contains undefined value for not yet Sorted autofilter.
- Boolean Sorted { get; set; }
Are values in the autofilter range sorted? I.e. the values were either already loaded sorted or Sort has been called at least once.
If
true, SortColumn and SortOrder contain valid values.
- XLSortOrder SortOrder { get; set; }
What sorting order was used during last Sort. Contains undefined value for not yet Sorted autofilter.