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.

// 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.

//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.

//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 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.

//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.

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.

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.

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.

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