aspose.cells

aspose.cells

Class AutoFilter

Represents autofiltering for the specified worksheet.

Example:

//Instantiating a Workbook object
$workbook = new cells\Workbook("Book1.xls");
//Accessing the first worksheet in the Excel file
$worksheet = $workbook->getWorksheets()->get(0);
//Creating AutoFilter by giving the cells range of the heading row
$worksheet->getAutoFilter()->setRange("A1:B1");
//Filtering columns with specified values
$worksheet->getAutoFilter()->filter(1, "Bananas");

Property Getters/Setters Summary
functiongetFilterColumns()
Gets the collection of the filter columns.
functiongetRange()
function
setRange(value)
           Represents the range to which the specified AutoFilter applies.
functiongetShowFilterButton()
function
setShowFilterButton(value)
           Indicates whether the AutoFilter button for this column is visible.
functiongetSorter()
Gets the data sorter.
 
Method Summary
functionaddDateFilter(fieldIndex, dateTimeGroupingType, year, month, day, hour, minute, second)
Adds a date filter.
functionaddFillColorFilter(fieldIndex, pattern, foregroundColor, backgroundColor)
Adds a fill color filter.
functionaddFilter(fieldIndex, criteria)
Adds a filter for a filter column.
functionaddFontColorFilter(fieldIndex, color)
Adds a font color filter.
functionaddIconFilter(fieldIndex, iconSetType, iconId)
Adds an icon filter.
functioncustom(fieldIndex, operatorType1, criteria1)
Filters a list with a custom criteria.
functioncustom(fieldIndex, operatorType1, criteria1, isAnd, operatorType2, criteria2)
Filters a list with custom criteria.
functiondynamicFilter(fieldIndex, dynamicFilterType)
Adds a dynamic filter.
functionfilter(fieldIndex, criteria)
Filters a list with specified criteria.
functionfilterTop10(fieldIndex, isTop, isPercent, itemCount)
Filter the top 10 item in the list
functiongetCellArea()
Gets the CellArea where the specified AutoFilter applies to.
functionmatchBlanks(fieldIndex)
Match all blank cell in the list.
functionmatchNonBlanks(fieldIndex)
Match all not blank cell in the list.
functionrefresh()
Refresh auto filters to hide or unhide the rows.
functionrefresh(hideRows)
Gets all hidden rows' indexes.
functionremoveDateFilter(fieldIndex, dateTimeGroupingType, year, month, day, hour, minute, second)
Removes a date filter.
functionremoveFilter(fieldIndex)
Remove the specific filter.
functionremoveFilter(fieldIndex, criteria)
Removes a filter for a filter column.
functionsetRange(row, startColumn, endColumn)
Sets the range to which the specified AutoFilter applies.
functionshowAll()
Unhide all rows.
 

    • Property Getters/Setters Detail

      • getSorter : DataSorter 

        function getSorter()
        Gets the data sorter.

      • getRange/setRange : String 

        function getRange() / function setRange(value)
        Represents the range to which the specified AutoFilter applies.

      • getShowFilterButton/setShowFilterButton : boolean 

        function getShowFilterButton() / function setShowFilterButton(value)
        Indicates whether the AutoFilter button for this column is visible.

      • getFilterColumns : FilterColumnCollection 

        function getFilterColumns()
        Gets the collection of the filter columns.

    • Method Detail

      • setRange

        function setRange(row, startColumn, endColumn)
        Sets the range to which the specified AutoFilter applies.
        Parameters:
        row: Number - Row index.
        startColumn: Number - Start column index.
        endColumn: Number - End column Index.

      • getCellArea

        function getCellArea()
        Gets the CellArea where the specified AutoFilter applies to.
        Returns:

      • addFilter

        function addFilter(fieldIndex, criteria)
        Adds a filter for a filter column. MS Excel 2007 supports multiple selection in a filter column.
        Parameters:
        fieldIndex: Number - The integer offset of the field on which you want to base the filter (from the left of the list; the leftmost field is field 0).
        criteria: String - The specified criteria (a string; for example, "101"). It only can be null or be one of the cells' value in this column.

      • addDateFilter

        function addDateFilter(fieldIndex, dateTimeGroupingType, year, month, day, hour, minute, second)
        Adds a date filter. If DateTimeGroupingType is Year, only the param year effects. If DateTiemGroupingType is Month, only the param year and month effect.
        Parameters:
        fieldIndex: Number - The integer offset of the field on which you want to base the filter (from the left of the list; the leftmost field is field 0).
        dateTimeGroupingType: Number - A DateTimeGroupingType value. The grouping type
        year: Number - The year.
        month: Number - The month.
        day: Number - The day.
        hour: Number - The hour.
        minute: Number - The minute.
        second: Number - The second.

      • removeDateFilter

        function removeDateFilter(fieldIndex, dateTimeGroupingType, year, month, day, hour, minute, second)
        Removes a date filter. If DateTimeGroupingType is Year, only the param year effects. If DateTiemGroupingType is Month, only the param year and month effect.
        Parameters:
        fieldIndex: Number - The integer offset of the field on which you want to base the filter (from the left of the list; the leftmost field is field 0).
        dateTimeGroupingType: Number - A DateTimeGroupingType value. The grouping type
        year: Number - The year.
        month: Number - The month.
        day: Number - The day.
        hour: Number - The hour.
        minute: Number - The minute.
        second: Number - The second.

      • removeFilter

        function removeFilter(fieldIndex, criteria)
        Removes a filter for a filter column.
        Parameters:
        fieldIndex: Number - The integer offset of the field on which you want to base the filter (from the left of the list; the leftmost field is field 0).
        criteria: String - The specified criteria (a string; for example, "101"). It only can be null or be one of the cells' value in this column.

      • filter

        function filter(fieldIndex, criteria)
        Filters a list with specified criteria. Aspose.Cells will remove all other filter setting on this field as Ms Excel 97-2003.
        Parameters:
        fieldIndex: Number - The integer offset of the field on which you want to base the filter (from the left of the list; the leftmost field is field 0).
        criteria: String - The specified criteria (a string; for example, "101").

      • filterTop10

        function filterTop10(fieldIndex, isTop, isPercent, itemCount)
        Filter the top 10 item in the list
        Parameters:
        fieldIndex: Number - The integer offset of the field on which you want to base the filter (from the left of the list; the leftmost field is field 0).
        isTop: boolean - Indicates whether filter from top or bottom
        isPercent: boolean - Indicates whether the items is percent or count
        itemCount: Number - The item count

      • dynamicFilter

        function dynamicFilter(fieldIndex, dynamicFilterType)
        Adds a dynamic filter.
        Parameters:
        fieldIndex: Number - The integer offset of the field on which you want to base the filter (from the left of the list; the leftmost field is field 0).
        dynamicFilterType: Number - A DynamicFilterType value. Dynamic filter type.

      • addFontColorFilter

        function addFontColorFilter(fieldIndex, color)
        Adds a font color filter.
        Parameters:
        fieldIndex: Number - The integer offset of the field on which you want to base the filter (from the left of the list; the leftmost field is field 0).
        color: CellsColor - The CellsColor object.

      • addFillColorFilter

        function addFillColorFilter(fieldIndex, pattern, foregroundColor, backgroundColor)
        Adds a fill color filter.
        Parameters:
        fieldIndex: Number - The integer offset of the field on which you want to base the filter (from the left of the list; the leftmost field is field 0).
        pattern: Number - A BackgroundType value. The background pattern type.
        foregroundColor: CellsColor - The foreground color.
        backgroundColor: CellsColor - The background color.

      • addIconFilter

        function addIconFilter(fieldIndex, iconSetType, iconId)
        Adds an icon filter. Only supports to add the icon filter. Not supports checking which row is visible if the filter is icon filter.
        Parameters:
        fieldIndex: Number - The integer offset of the field on which you want to base the filter (from the left of the list; the leftmost field is field 0).
        iconSetType: Number - A IconSetType value. The icon set type.
        iconId: Number - The icon id.

      • matchBlanks

        function matchBlanks(fieldIndex)
        Match all blank cell in the list.
        Parameters:
        fieldIndex: Number - The integer offset of the field on which you want to base the filter (from the left of the list; the leftmost field is field 0).

      • matchNonBlanks

        function matchNonBlanks(fieldIndex)
        Match all not blank cell in the list.
        Parameters:
        fieldIndex: Number - The integer offset of the field on which you want to base the filter (from the left of the list; the leftmost field is field 0).

      • custom

        function custom(fieldIndex, operatorType1, criteria1)
        Filters a list with a custom criteria.
        Parameters:
        fieldIndex: Number - The integer offset of the field on which you want to base the filter (from the left of the list; the leftmost field is field 0).
        operatorType1: Number - A FilterOperatorType value. The filter operator type
        criteria1: Object - The custom criteria

      • custom

        function custom(fieldIndex, operatorType1, criteria1, isAnd, operatorType2, criteria2)
        Filters a list with custom criteria.
        Parameters:
        fieldIndex: Number - The integer offset of the field on which you want to base the filter (from the left of the list; the leftmost field is field 0).
        operatorType1: Number - A FilterOperatorType value. The filter operator type
        criteria1: Object - The custom criteria
        isAnd: boolean -
        operatorType2: Number - A FilterOperatorType value. The filter operator type
        criteria2: Object - The custom criteria

      • showAll

        function showAll()
        Unhide all rows.

      • removeFilter

        function removeFilter(fieldIndex)
        Remove the specific filter.
        Parameters:
        fieldIndex: Number - The specific filter index

      • refresh

        function refresh()
        Refresh auto filters to hide or unhide the rows.
        Returns:
        Returns all hidden rows' indexes.

      • refresh

        function refresh(hideRows)
        Gets all hidden rows' indexes.
        Parameters:
        hideRows: boolean - If true, hide the filtered rows.
        Returns:
        Returns all hidden rows indexes.