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:
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.
In order to filter a range of cells, you can use the AutoFilter method of IRange interface.
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) |
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.
In order to access the existing filter settings and table filter settings, you can use the AutoFilter property.
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) |