Excel.AutoFilter class

Represents the AutoFilter object. AutoFilter turns the values in Excel column into specific filters based on the cell contents.

Extends

Remarks

[ API set: ExcelApi 1.9 ]

Properties

context

The request context associated with the object. This connects the add-in's process to the Office host application's process.

criteria

An array that holds all the filter criteria in the autofiltered range.

enabled

Specifies if the AutoFilter is enabled.

isDataFiltered

Specifies if the AutoFilter has filter criteria.

Methods

apply(range, columnIndex, criteria)

Applies the AutoFilter to a range. This filters the column if column index and filter criteria are specified.

clearColumnCriteria(columnIndex)

Clears the column filter criteria of the AutoFilter.

clearCriteria()

Clears the filter criteria and sort state of the AutoFilter.

getRange()

Returns the Range object that represents the range to which the AutoFilter applies.

getRangeOrNullObject()

Returns the Range object that represents the range to which the AutoFilter applies. If there is no Range object associated with the AutoFilter, then this method returns an object with its isNullObject property set to true. For further information, see *OrNullObject methods and properties.

load(options)

Queues up a command to load the specified properties of the object. You must call context.sync() before reading the properties.

load(propertyNames)

Queues up a command to load the specified properties of the object. You must call context.sync() before reading the properties.

load(propertyNamesAndPaths)

Queues up a command to load the specified properties of the object. You must call context.sync() before reading the properties.

reapply()

Applies the specified AutoFilter object currently on the range.

remove()

Removes the AutoFilter for the range.

toJSON()

Overrides the JavaScript toJSON() method in order to provide more useful output when an API object is passed to JSON.stringify(). (JSON.stringify, in turn, calls the toJSON method of the object that is passed to it.) Whereas the original Excel.AutoFilter object is an API object, the toJSON method returns a plain JavaScript object (typed as Excel.Interfaces.AutoFilterData) that contains shallow copies of any loaded child properties from the original object.

Property Details

context

The request context associated with the object. This connects the add-in's process to the Office host application's process.

context: RequestContext;

Property Value

criteria

An array that holds all the filter criteria in the autofiltered range.

readonly criteria: Excel.FilterCriteria[];

Property Value

Remarks

[ API set: ExcelApi 1.9 ]

enabled

Specifies if the AutoFilter is enabled.

readonly enabled: boolean;

Property Value

boolean

Remarks

[ API set: ExcelApi 1.9 ]

isDataFiltered

Specifies if the AutoFilter has filter criteria.

readonly isDataFiltered: boolean;

Property Value

boolean

Remarks

[ API set: ExcelApi 1.9 ]

Method Details

apply(range, columnIndex, criteria)

Applies the AutoFilter to a range. This filters the column if column index and filter criteria are specified.

apply(range: Range | string, columnIndex?: number, criteria?: Excel.FilterCriteria): void;

Parameters

range

Excel.Range | string

The range on which the AutoFilter will apply.

columnIndex

number

The zero-based column index to which the AutoFilter is applied.

criteria
Excel.FilterCriteria

The filter criteria.

Returns

void

Remarks

[ API set: ExcelApi 1.9 ]

Examples

// Link to full sample: https://raw.githubusercontent.com/OfficeDev/office-js-snippets/prod/samples/excel/54-worksheet/worksheet-auto-filter.yaml

// This function adds a percentage AutoFilter to the active worksheet 
// and applies the filter to a column of the used range.
await Excel.run(async (context) => {
    // Retrieve the active worksheet and the used range on that worksheet.
    const sheet = context.workbook.worksheets.getActiveWorksheet();
    const farmData = sheet.getUsedRange();

    // Add a filter that will only show the rows with the top 50% of values in column 3.
    sheet.autoFilter.apply(farmData, 3, {
        criterion1: "50",
        filterOn: Excel.FilterOn.topPercent
    });

    await context.sync();
});

clearColumnCriteria(columnIndex)

Clears the column filter criteria of the AutoFilter.

clearColumnCriteria(columnIndex: number): void;

Parameters

columnIndex

number

The zero-based column index, which represents which column filter needs to be cleared. If the index value is not supported (for example, if the value is a negative number, or if the value is greater than the number of available columns in the range), then an InvalidArgument error will be thrown.

Returns

void

Remarks

[ API set: ExcelApi 1.14 ]

Examples

// Link to full sample: https://raw.githubusercontent.com/OfficeDev/office-js-snippets/prod/samples/excel/54-worksheet/worksheet-auto-filter.yaml

// This function clears the AutoFilter setting from one column.
await Excel.run(async (context) => {
    // Retrieve the active worksheet.
    const sheet = context.workbook.worksheets.getActiveWorksheet();

    // Clear the filter from only column 3.
    sheet.autoFilter.clearColumnCriteria(3);
    await context.sync();
});

clearCriteria()

Clears the filter criteria and sort state of the AutoFilter.

clearCriteria(): void;

Returns

void

Remarks

[ API set: ExcelApi 1.9 ]

getRange()

Returns the Range object that represents the range to which the AutoFilter applies.

getRange(): Excel.Range;

Returns

Remarks

[ API set: ExcelApi 1.9 ]

getRangeOrNullObject()

Returns the Range object that represents the range to which the AutoFilter applies. If there is no Range object associated with the AutoFilter, then this method returns an object with its isNullObject property set to true. For further information, see *OrNullObject methods and properties.

getRangeOrNullObject(): Excel.Range;

Returns

Remarks

[ API set: ExcelApi 1.9 ]

load(options)

Queues up a command to load the specified properties of the object. You must call context.sync() before reading the properties.

load(options?: Excel.Interfaces.AutoFilterLoadOptions): Excel.AutoFilter;

Parameters

options
Excel.Interfaces.AutoFilterLoadOptions

Provides options for which properties of the object to load.

Returns

load(propertyNames)

Queues up a command to load the specified properties of the object. You must call context.sync() before reading the properties.

load(propertyNames?: string | string[]): Excel.AutoFilter;

Parameters

propertyNames

string | string[]

A comma-delimited string or an array of strings that specify the properties to load.

Returns

load(propertyNamesAndPaths)

Queues up a command to load the specified properties of the object. You must call context.sync() before reading the properties.

load(propertyNamesAndPaths?: {
            select?: string;
            expand?: string;
        }): Excel.AutoFilter;

Parameters

propertyNamesAndPaths

{ select?: string; expand?: string; }

propertyNamesAndPaths.select is a comma-delimited string that specifies the properties to load, and propertyNamesAndPaths.expand is a comma-delimited string that specifies the navigation properties to load.

Returns

reapply()

Applies the specified AutoFilter object currently on the range.

reapply(): void;

Returns

void

Remarks

[ API set: ExcelApi 1.9 ]

Examples

// Link to full sample: https://raw.githubusercontent.com/OfficeDev/office-js-snippets/prod/samples/excel/54-worksheet/worksheet-auto-filter.yaml

// This function refreshes the AutoFilter to ensure that changes are captured.
await Excel.run(async (context) => {
    // Retrieve the active worksheet.
    const sheet = context.workbook.worksheets.getActiveWorksheet();

    // Reapply the filter to capture changes.
    sheet.autoFilter.reapply();
    await context.sync();
});

remove()

Removes the AutoFilter for the range.

remove(): void;

Returns

void

Remarks

[ API set: ExcelApi 1.9 ]

Examples

// Link to full sample: https://raw.githubusercontent.com/OfficeDev/office-js-snippets/prod/samples/excel/54-worksheet/worksheet-auto-filter.yaml

// This function removes all AutoFilters from the active worksheet.
await Excel.run(async (context) => {
    // Retrieve the active worksheet.
    const sheet = context.workbook.worksheets.getActiveWorksheet();

    // Remove all filters.
    sheet.autoFilter.remove();
    await context.sync();
});

toJSON()

Overrides the JavaScript toJSON() method in order to provide more useful output when an API object is passed to JSON.stringify(). (JSON.stringify, in turn, calls the toJSON method of the object that is passed to it.) Whereas the original Excel.AutoFilter object is an API object, the toJSON method returns a plain JavaScript object (typed as Excel.Interfaces.AutoFilterData) that contains shallow copies of any loaded child properties from the original object.

toJSON(): Excel.Interfaces.AutoFilterData;

Returns