Click or drag to resize

CellRangeFilter Method

Gets the AutoFilter active on the parent worksheet (Filter) if active Range is equal to this CellRange; otherwise, a new AutoFilter instance, which is set as active on the parent worksheet.

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

Namespace:  GemBox.Spreadsheet
Assembly:  GemBox.Spreadsheet (in GemBox.Spreadsheet.dll) Version: 43.0.35.1020
Syntax
public AutoFilter Filter()

Return Value

Type: AutoFilter
The AutoFilter active on the parent worksheet (Filter) if active Range is equal to this CellRange; otherwise, a new AutoFilter instance, which is set as active on the parent worksheet.
Remarks
This method has the same affect as Filter(Boolean) with parameter set to .
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