Spread Windows Forms 13.0 Product Documentation
Spread Windows Forms 13.0 Product Documentation / Developer's Guide / Customizing Interaction in Cells / Managing Cell Range Filtering
In This Topic
    Managing Cell Range Filtering
    In This Topic

    Spread for WinForms allows users to manage range filtering operations without any hassle via applying filters on a specific cell range in a worksheet. This makes it easier and quicker to find and view records matching to a specific filter condition in a cell range while working with bulk data in spreadsheets.

    Managing cell range filtering includes the following tasks:

    1. Apply Range Filtering
    2. Access Filter Settings

    Apply Range Filtering

    You can apply filter on a cell range by using the AutoFilter method of IRange interface. If no filter is applied to a cell range, invoking the AutoFilter method will create a new empty filter. However, if a filter is already applied, invoking this method in the filtered range will remove the existing filter.

    After applying filters on a cell range in a worksheet, a drop-down arrow indicator appears on the right side of each cell at the topmost row in the filtered range. Upon clicking the indicator, a drop-down list containing all the unique values available in the column will be displayed. Users can select the required filter options from the list in order to arrange the data.

    Using Code

    In order to filter a range of cells, you can use the AutoFilter method of IRange interface.

    Example

    Refer to the following example code to apply filter on a range of cells in a spreadsheet.

    C#
    Copy Code
    // Apply range filter in cells B2:C5 using the AutoFilter method
    fpSpread1.AsWorkbook().ActiveSheet.Cells["B2:C5"].AutoFilter();
    
    // Apply range filter with custom filter condition
    // Create a range filter - B2:E10 and filter Column C with AboveAverage filter condition
    fpSpread1.AsWorkbook().ActiveSheet.Cells["B2:E10"].AutoFilter(1,
    GrapeCity.Spreadsheet.DynamicFilterType.AboveAverage);
    
    VB
    Copy Code
    'Apply range filter in cells B2:C5 using the AutoFilter method
    FpSpread1.AsWorkbook().ActiveSheet.Cells("B2:C5").AutoFilter()
    
    'Apply range filter with custom filter condition
    'Create a range filter - B2:E10 and filter Column C with AboveAverage filter condition
    FpSpread1.AsWorkbook().ActiveSheet.Cells("B2:C10").AutoFilter(1, GrapeCity.Spreadsheet.DynamicFilterType.AboveAverage)
    

    Access Filter Settings

    While working with range filters in a worksheet, you can access the current filter settings along with the existing table filter settings. This includes accessing information such as the cell range on which the filter is applied, the specified filter criteria and the filter mode.

    Using Code

    In order to access the existing filter settings and table filter settings, you can use the AutoFilter property.

    Example

    Refer to the following example code to access the current filter settings and the table filter settings in a worksheet.

    C#
    Copy Code
    // Access current filter settings
    IAutoFilter worksheetAutoFilter = fpSpread1.AsWorkbook().ActiveSheet.AutoFilter;
    IFilter columnCFilter = worksheetAutoFilter.Filters[1];
    
    // Access table filter settings 
    IAutoFilter tableAutoFilter = fpSpread1.AsWorkbook().ActiveSheet.Tables["table1"].AutoFilter;
    IFilter columnFilter = tableAutoFilter.Filters[0];
    
    VB
    Copy Code
    'Access current filter settings
     Dim worksheetAutoFilter As GrapeCity.Spreadsheet.IAutoFilter
     worksheetAutoFilter = FpSpread1.AsWorkbook().ActiveSheet.AutoFilter
     Dim columnCFilter As IFilter = worksheetAutoFilter.Filters(1)
    'Access table filter settings
     Dim tableAutoFilter As IAutoFilter = FpSpread1.AsWorkbook().ActiveSheet.Tables("table1").AutoFilter
     Dim columnFilter As IFilter = tableAutoFilter.Filters(0)
    

    Note: Users cannot use both column filters and range filters simultaneously in a worksheet at the same time. Also, users can filter only one cell range in a worksheet.