Document Solutions for Excel, .NET Edition | Document Solutions
Features / Filter
In This Topic
    Filter
    In This Topic

    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 DsExcel, 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.

    Create Filter without Condition

    DsExcel allows you to create a filter without condition by using the 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.

    C#
    Copy Code
    //Create filters without condition.
    worksheet.Range["A1:F7"].AutoFilter();

    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.FromArgb(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);