Apply Filters

Spread.Services Documentation
Apply Filters
Collapse All Expand All

Spread.Services Documentation > Developer's Guide > Customize User Interaction > Apply Filters

Worksheets with bulk data can be difficult to manage. In such a scenario, applying filters can be a useful feature to view only the required information while hiding rest of the data. Filters are used to display only the relevant records that match to a certain criteria in a particular column.

In Spread.Services, you can apply filters to a selected range of data. For example, you can apply date type filter from C4 to C7 range. To filter data in a range of cells or a table, you need to set the auto filter mode for the worksheet to boolean true or false using AutoFilterMode property of the IWorksheet interface.

There are several types of range filters responsible for executing distinct filter operations in a worksheet.

- Apply number filters
- Apply multi select filters
- Apply text filters
- Apply date filters
- Apply dynamic date filters
- Apply filters by cell color
- Apply filters by no fill
- Apply filters by icon
- Apply filters by no icon

### Apply number filters

Refer to the following example code to see how you can apply number filters to display data that meets the specified criteria applied on a column containing numeric cell values.

C# | Copy Code
--- | ---

    // Apply number filter
    worksheet.Range["D3:I6"].AutoFilter(0, "<>2");

### Apply multi select filters

Refer to the following example code to see how multi select filters can be applied to quickly filter data based on cell values with multiple selections.

C# | Copy Code
--- | ---

    //filter condition is "multi select".
    worksheet.Range["A1:E5"].AutoFilter(0, new object[] { "$2", "$4" }, AutoFilterOperator.Values);

### Apply text filters

Refer to the following example code to see how text filters are applied to display rows with cell values that either match to the specified text or regular expression value in the column on which the filter is applied.

C# | Copy Code
--- | ---

    //begin with "a".
    worksheet.Range["D3:I9"].AutoFilter(1, "a*");

### Apply date filters

Refer to the following example code to see how date filters can be applied to a range to display only those results that are falling within the specified dates.

Apply date filters | Copy Code
--- | ---

    //Apply filter using Date criteria
    var criteria1 = new DateTime(2008, 1, 1).ToString();
    var criteria2 = new DateTime(2008, 8, 1).ToString();
    worksheet.Range["D20:F29"].AutoFilter(2, ">=" + criteria1, AutoFilterOperator.And, "<=" + criteria2);

### Apply dynamic date filters

Refer to the following example code to see how dynamic date filters can be applied to display results that match the specified date criteria taking into account the current system date that automatically gets updated everyday.

C# | Copy Code
--- | ---

    //filter in yersterday.
    worksheet.Range["D7:F18"].AutoFilter(2, DynamicFilterType.Yesterday, AutoFilterOperator.Dynamic);

### Apply filters by cell colors

Refer to the following example code to see how you can apply filters by cell colors on a column to display results containing cells with distinct fill shades.

C# | Copy Code
--- | ---

    worksheet.Range["A1:A6"].AutoFilter(0, Color.FromRGB(255, 255, 0), AutoFilterOperator.CellColor);

### Apply filters by no fill

Refer to the following example code to see how you can apply filters by no fill on a column to display results containing cells with no fill color.

C# | Copy Code
--- | ---

    worksheet.Range["A1:A6"].AutoFilter(0, null, AutoFilterOperator.NoFill);

### Apply filters by icon

Refer to the following example code to see how you can apply filters by icon to display results that contain a specific icon in the cells.

C# | Copy Code
--- | ---

    worksheet.Range["A1:A10"].AutoFilter(0, workbook.IconSets[IconSetType.Icon5ArrowsGray][0], AutoFilterOperator.Icon);

### Apply filters by no icon

Refer to the following example code to see how you can apply filters by no icon to display results where cells do not possess an icon.

C# | Copy Code
--- | ---

    worksheet.Range["A1:A10"].AutoFilter(0, null, AutoFilterOperator.NoIcon);

Copyright © GrapeCity, inc. All rights reserved.