Filter Object (JavaScript API for Excel)

Manages the filtering of a table's column.

Properties

None

Relationships

Relationship Type Description Req. Set
criteria FilterCriteria The currently applied filter on the given column. Read-only. 1.2

Methods

Method Return Type Description Req. Set
apply(criteria: FilterCriteria) void Apply the given filter criteria on the given column. 1.2
applyBottomItemsFilter(count: number) void Apply a "Bottom Item" filter to the column for the given number of elements. 1.2
applyBottomPercentFilter(percent: number) void Apply a "Bottom Percent" filter to the column for the given percentage of elements. 1.2
applyCellColorFilter(color: string) void Apply a "Cell Color" filter to the column for the given color. 1.2
applyCustomFilter(criteria1: string, criteria2: string, oper: string) void Apply a "Icon" filter to the column for the given criteria strings. 1.2
applyDynamicFilter(criteria: string) void Apply a "Dynamic" filter to the column. 1.2
applyFontColorFilter(color: string) void Apply a "Font Color" filter to the column for the given color. 1.2
applyIconFilter(icon: Icon) void Apply a "Icon" filter to the column for the given icon. 1.2
applyTopItemsFilter(count: number) void Apply a "Top Item" filter to the column for the given number of elements. 1.2
applyTopPercentFilter(percent: number) void Apply a "Top Percent" filter to the column for the given percentage of elements. 1.2
applyValuesFilter(values: object[]) void Apply a "Values" filter to the column for the given values. 1.2
clear() void Clear the filter on the given column. 1.2

Method Details

apply(criteria: FilterCriteria)

Apply the given filter criteria on the given column.

Syntax

filterObject.apply(criteria);

Parameters

Parameter Type Description
criteria FilterCriteria The criteria to apply.

Returns

void

applyBottomItemsFilter(count: number)

Apply a "Bottom Item" filter to the column for the given number of elements.

Syntax

filterObject.applyBottomItemsFilter(count);

Parameters

Parameter Type Description
count number The number of elements from the bottom to show.

Returns

void

applyBottomPercentFilter(percent: number)

Apply a "Bottom Percent" filter to the column for the given percentage of elements.

Syntax

filterObject.applyBottomPercentFilter(percent);

Parameters

Parameter Type Description
percent number The percentage of elements from the bottom to show.

Returns

void

applyCellColorFilter(color: string)

Apply a "Cell Color" filter to the column for the given color.

Syntax

filterObject.applyCellColorFilter(color);

Parameters

Parameter Type Description
color string The background color of the cells to show.

Returns

void

applyCustomFilter(criteria1: string, criteria2: string, oper: string)

Apply a "Icon" filter to the column for the given criteria strings.

Syntax

filterObject.applyCustomFilter(criteria1, criteria2, oper);

Parameters

Parameter Type Description
criteria1 string The first criteria string.
criteria2 string Optional. The second criteria string.
oper string Optional. The operator that describes how the two criteria are joined. Possible values are: And, Or

Returns

void

applyDynamicFilter(criteria: string)

Apply a "Dynamic" filter to the column.

Syntax

filterObject.applyDynamicFilter(criteria);

Parameters

Parameter Type Description
criteria string The dynamic criteria to apply. Possible values are: Unknown, AboveAverage, AllDatesInPeriodApril, AllDatesInPeriodAugust, AllDatesInPeriodDecember, AllDatesInPeriodFebruray, AllDatesInPeriodJanuary, AllDatesInPeriodJuly, AllDatesInPeriodJune, AllDatesInPeriodMarch, AllDatesInPeriodMay, AllDatesInPeriodNovember, AllDatesInPeriodOctober, AllDatesInPeriodQuarter1, AllDatesInPeriodQuarter2, AllDatesInPeriodQuarter3, AllDatesInPeriodQuarter4, AllDatesInPeriodSeptember, BelowAverage, LastMonth, LastQuarter, LastWeek, LastYear, NextMonth, NextQuarter, NextWeek, NextYear, ThisMonth, ThisQuarter, ThisWeek, ThisYear, Today, Tomorrow, YearToDate, Yesterday

Returns

void

applyFontColorFilter(color: string)

Apply a "Font Color" filter to the column for the given color.

Syntax

filterObject.applyFontColorFilter(color);

Parameters

Parameter Type Description
color string The font color of the cells to show.

Returns

void

applyIconFilter(icon: Icon)

Apply a "Icon" filter to the column for the given icon.

Syntax

filterObject.applyIconFilter(icon);

Parameters

Parameter Type Description
icon Icon The icons of the cells to show.

Returns

void

applyTopItemsFilter(count: number)

Apply a "Top Item" filter to the column for the given number of elements.

Syntax

filterObject.applyTopItemsFilter(count);

Parameters

Parameter Type Description
count number The number of elements from the top to show.

Returns

void

applyTopPercentFilter(percent: number)

Apply a "Top Percent" filter to the column for the given percentage of elements.

Syntax

filterObject.applyTopPercentFilter(percent);

Parameters

Parameter Type Description
percent number The percentage of elements from the top to show.

Returns

void

applyValuesFilter(values: object[])

Apply a "Values" filter to the column for the given values.

Syntax

filterObject.applyValuesFilter(values);

Parameters

Parameter Type Description
values object[] The list of values to show.

Returns

void

clear()

Clear the filter on the given column.

Syntax

filterObject.clear();

Parameters

None

Returns

void

er Type Description   values ()[] The list of values to show.  

Returns

void

clear()

Clear the filter on the given column.

Syntax

filterObject.clear();

Parameters

None

Returns

void

r()

Clear the filter on the given column.

Syntax

filterObject.clear();

Parameters

None

Returns

void

load(param: object)

Fills the proxy object created in JavaScript layer with property and object values specified in the parameter.

Syntax

object.load(param);

Parameters

Parameter Type Description  
param object Optional. Accepts parameter and relationship names as delimited string or an array. Or, provide loadOption object.  

Returns

void

xample-8">Example
Excel.run(function (ctx) { 
    var column = ctx.workbook.tables.getItem("Table1").columns.getItemAt(0);
    column.filter.applyTopItemsFilter(3);
    return ctx.sync(); 
}).catch(function(error) {
        console.log("Error: " + error);
        if (error instanceof OfficeExtension.Error) {
            console.log("Debug info: " + JSON.stringify(error.debugInfo));
        }
});

applyTopPercentFilter(percent: number)

Apply a "Top Percent" filter to the column for the given percentage of elements.

Syntax

filterObject.applyTopPercentFilter(percent);

Parameters

Parameter Type Description
percent number The percentage of elements from the top to show.

Returns

void

Example

Excel.run(function (ctx) { 
    var column = ctx.workbook.tables.getItem("Table1").columns.getItemAt(0);
    column.filter.applyTopPercentFilter(30);
    return ctx.sync(); 
}).catch(function(error) {
        console.log("Error: " + error);
        if (error instanceof OfficeExtension.Error) {
            console.log("Debug info: " + JSON.stringify(error.debugInfo));
        }
});

applyValuesFilter(values: ()[])

Apply a "Values" filter to the column for the given values.

Syntax

filterObject.applyValuesFilter(values);

Parameters

Parameter Type Description
values ()[] The list of values to show.

Returns

void

Example

Excel.run(function (ctx) { 
    var column = ctx.workbook.tables.getItem("Table1").columns.getItemAt(0);
    column.filter.applyValuesFilter(['a','b']);
    return ctx.sync(); 
}).catch(function(error) {
        console.log("Error: " + error);
        if (error instanceof OfficeExtension.Error) {
            console.log("Debug info: " + JSON.stringify(error.debugInfo));
        }
});

clear()

Clear the filter on the given column.

Syntax

filterObject.clear();

Parameters

None

Returns

void

Example

Excel.run(function (ctx) { 
    var column = ctx.workbook.tables.getItem("Table1").columns.getItemAt(0);
    column.filter.clear();
    return ctx.sync(); 
}).catch(function(error) {
        console.log("Error: " + error);
        if (error instanceof OfficeExtension.Error) {
            console.log("Debug info: " + JSON.stringify(error.debugInfo));
        }
});

load(param: object)

Fills the proxy object created in the JavaScript layer, with property and object values specified in the parameter.

Syntax

object.load(param);

Parameters

Parameter Type Description
param object Optional. Accepts parameter and relationship names as a delimited string or an array. Or, provide loadOption object.

Returns

void