Click or drag to resize

ExcelWorksheetFilter Property

Gets or sets the active AutoFilter. Returns if no AutoFilter is active.

AutoFilter is used to store filter settings and to apply filter on a range of cells.

Active AutoFilter is loaded from and saved to XLSX file.

Namespace:  GemBox.Spreadsheet
Assembly:  GemBox.Spreadsheet (in GemBox.Spreadsheet.dll) Version: 41.3.30.1132
Syntax
public AutoFilter Filter { get; set; }

Property Value

Type: AutoFilter
The active AutoFilter or if no AutoFilter is active.
Exceptions
ExceptionCondition
ArgumentExceptionFilter range doesn't belong to this worksheet.
Remarks
Use Filter Overload methods to create or get an AutoFilter instance.
Examples

Following methods shows various ways to specify and apply an AutoFilter to a range of cells.

static void Filter1(ExcelWorksheet worksheet)
{
    // Specify and apply Filter using less-verbose, fluent, but less understandable interface.
    worksheet.Cells.GetSubrange("A1", "D20").Filter().
        ByValues(0, null, "$5,400.00", "John Doe", new DateTimeGroup(2015, 5)).
        ByTop10(1, true, true, 20).
        ByCustom(2, FilterOperator.GreaterThanOrEqual, 2000, true, FilterOperator.LessThanOrEqual, 8000).
        ByDynamic(3, DynamicFilterType.AboveAverage).
        Apply();
}

static void Filter2(ExcelWorksheet worksheet)
{
    // Specify and apply Filter using more verbose, but more understandable interface.
    var filter = worksheet.Cells.GetSubrange("A1", "D20").Filter();

    var valuesFilter = filter.Columns[0].SetValuesFilter();
    valuesFilter.SetValues(null, "$5,400.00", "John Doe", new DateTimeGroup(2015, 5));

    var top10Filter = filter.Columns[1].SetTop10Filter();
    top10Filter.Top = true;
    top10Filter.Percent = true;
    top10Filter.Value = 20;

    var customFilter = filter.Columns[2].SetCustomFilter();
    customFilter.Operator = FilterOperator.GreaterThanOrEqual;
    customFilter.Value = 2000;
    customFilter.And = true;
    customFilter.Operator2 = FilterOperator.LessThanOrEqual;
    customFilter.Value2 = 8000;

    var dynamicFilter = filter.Columns[3].SetDynamicFilter();
    dynamicFilter.DynamicFilterType = DynamicFilterType.AboveAverage;

    filter.Apply();
}

static void Filter3(ExcelWorksheet worksheet)
{
    // Specify and apply Filter using less-verbose, fluent and understandable interface (with help of C# 4.0 named arguments).
    worksheet.Cells.GetSubrange("A1", "D20").Filter().
        ByValues(columnIndex: 0, values: new object[] { null, "$5,400.00", "John Doe", new DateTimeGroup(2015, 5) }).
        ByTop10(columnIndex: 1, top: true, percent: true, value: 20).
        ByCustom(columnIndex: 2, @operator: FilterOperator.GreaterThanOrEqual, value: 2000, and: true, operator2: FilterOperator.LessThanOrEqual, value2: 8000).
        ByDynamic(columnIndex: 3, dynamicFilterType: DynamicFilterType.AboveAverage).
        Apply();
}
See Also