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:
public AutoFilter Filter { get; set; }

Property Value

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

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).

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;


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).
See Also