While working with spreadsheets, it often becomes challenging to analyse, manipulate and manage tons of data quickly and efficiently.

Applying filters not just helps you in viewing only the necessary information but also lets you hide the rest of the data. When you set a specific filter condition on a worksheet, only the most relevant records (rows) will display that match to a certain criteria in a particular column.

In DsExcel Java, filters can be applied to a selected range of data. For instance, you can apply number filter from range D3 to I6 to display only those rows where the columns contain numeric data.

There are several types of range filters that can be used while performing different filter operations in a worksheet.

• Create filter without condition
• 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

Create filter without condition

DsExcel allows you to create a filter without condition by using IRange.AutoFilter class method. This method creates an empty filter if no condition is set in the worksheet already. You can also create filter for a specific field by passing the optional field parameter to the method.

//Create filters without condition
worksheet.getRange("A1:F7").autoFilter();  

Apply number filters

In order to apply number filters displaying data that meets the specified criteria set on a column containing numeric data, refer to the following example code.

// Apply number filter
worksheet.getRange("D3:I6").autoFilter(0, "<>2");

Apply multi select filters

In order to apply multi select filters to filter data based on cell values with multiple selections, refer to the following example code.

// Apply filter condition - multi select
worksheet.getRange("A1:E5").autoFilter(0, new Object[] { "$2", "$4" }, AutoFilterOperator.Values);

Apply text filters

In order to apply text filters displaying rows with cell values that either match to the specified text or regular expression value on which the filter is applied, refer to the following example code.

// Apply filter condition - begin with "a".
worksheet.getRange("D3:I9").autoFilter(1, "a*");

Apply date filters

In order to apply date filter condition to a range of cells displaying only the specific results falling within the given dates, refer to the following example code.

// Apply filter using Date criteria
String criteria1 = new GregorianCalendar(1972, 6, 3).getTime().toString();
String criteria2 = new GregorianCalendar(1993, 1, 15).getTime().toString();
// Filter date between 1972.7.3 and 1993.2.15
worksheet.getRange("A1:F7").autoFilter(2, ">=" + criteria1, AutoFilterOperator.And, "<=" + criteria2);

Apply dynamic date filters

In order to apply dynamic date filters displaying results that match the specified date criteria based on the current system date that automatically gets updated everyday, refer to the following example code.

// Apply filter condition - filter by yesterday
worksheet.getRange("D7:F18").autoFilter(2, DynamicFilterType.Yesterday, AutoFilterOperator.Dynamic);

Apply filters by cell colors

In order to apply filters by cell colors on a column showing results with cells having different fill shades, refer to the following example code.

// Apply filter by cell color
worksheet.getRange("A1:A6").autoFilter(0, Color.FromArgb(255, 255, 0), AutoFilterOperator.CellColor);

Apply filters by no fill

In order to apply filters by no fill on a column in order to filter results based on cells having no fill color, refer to the following example code.

// Apply filter by no fill
worksheet.getRange("A1:A6").autoFilter(0, null, AutoFilterOperator.NoFill);

Apply filters by icon

In order to apply filters by icon that filters results via possessing a specific icon in the cells, refer to the following example code.

// Apply filter by icon
worksheet.getRange("A1:A10").autoFilter(0, workbook.getIconSets().get(IconSetType.Icon5ArrowsGray).get(0),AutoFilterOperator.Icon);

Apply filters by no icon

In order to apply filters by no icon that displays results where cells do not have an icon, refer to the following example code.

// Apply filter by no icon 
worksheet.getRange("A2:A10").autoFilter(0, null, AutoFilterOperator.NoIcon);