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.

// 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);

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

You can get an array of filtered out row indexes that are filtered by applying an AutoFilter in a column or a cell range. You can do this by using the IAutoFilter.GetFilteredOutRows method which returns an array of row indexes that does not meet the filter criteria.

The following code is used to get an array of filtered rows using the GetFilteredOutRows method.

IWorksheet sheet = fpSpread1.AsWorkbook().ActiveSheet;
IAutoFilter autoFilter = sheet.AutoFilter;
int[] result = autoFilter.GetFilteredOutRows(1);

Dim sheet As IWorksheet = fpSpread1.AsWorkbook().ActiveSheet
Dim autoFilter As IAutoFilter = sheet.AutoFilter
Dim result As Integer() = autoFilter.GetFilteredOutRows(1)

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.

// 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];

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