Documents for Excel, Java Edition Documentation
Features / Filter
In This Topic
    Filter
    In This Topic

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

    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.

    Java
    Copy 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.

    Java
    Copy 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.

    Java
    Copy 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.

    Java
    Copy 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.

    Java
    Copy 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.

    Java
    Copy 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.

    Java
    Copy 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.

    Java
    Copy 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.

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