ConditionalFormatCollection Object (JavaScript API for Excel)

Represents a collection of all the conditional formats that are overlap the range.

Properties

Property	Type	Description	Req. Set
items	ConditionalFormat[]	A collection of conditionalFormat objects. Read-only.	1.6

Relationships

None

Methods

Method	Return Type	Description	Req. Set
add(type: string)	ConditionalFormat	Adds a new conditional format to the collection at the firsttop priority.	1.6
clearAll()	void	Clears all conditional formats active on the current specified range.	1.6
getCount()	int	Returns the number of conditional formats in the workbook. Read-only.	1.6
getItemAt(index: number)	ConditionalFormat	Returns a conditional format at the given index.	1.6

Method Details

add(type: string)

Adds a new conditional format to the collection at the firsttop priority.

Syntax

conditionalFormatCollectionObject.add(type);

Parameters

Parameter	Type	Description
type	string	The type of conditional format being added. Possible values are: Custom, DataBar, ColorScale, IconSet

Returns

ConditionalFormat

Examples

Excel.run(function (ctx) {
    var sheetName = "Sheet1";
    var rangeAddress = "A1:C3";
    var range = ctx.workbook.worksheets.getItem(sheetName).getRange(rangeAddress);
    var conditionalFormat = range.conditionalFormats.add(Excel.ConditionalFormatType.iconSet);
    conditionalFormat.iconOrNull.style = "YellowThreeArrows";
    return ctx.sync().then(function () {
        console.log("Added new yellow three arrow icon set.");
    });
}).catch(function (error) {
    console.log("Error: " + error);
    if (error instanceof OfficeExtension.Error) {
        console.log("Debug info: " + JSON.stringify(error.debugInfo));
    }
    });

clearAll()

Clears all conditional formats active on the current specified range.

Syntax

conditionalFormatCollectionObject.clearAll();

Parameters

None

Returns

void

Examples

Excel.run(function (ctx) {
    var sheetName = "Sheet1";
    var rangeAddress = "A1:C3";
    var range = ctx.workbook.worksheets.getItem(sheetName).getRange(rangeAddress);
    var conditionalFormats = range.conditionalFormats;
    var conditionalFormat = conditionalFormats.clearAll();
    return ctx.sync().then(function () {
        console.log("Cleared all conditional formats from this range.");
    });
}).catch(function (error) {
    console.log("Error: " + error);
    if (error instanceof OfficeExtension.Error) {
        console.log("Debug info: " + JSON.stringify(error.debugInfo));
    }
    });

getCount()

Returns the number of conditional formats in the workbook. Read-only.

Syntax

conditionalFormatCollectionObject.getCount();

Parameters

None

Returns

int

Examples

Excel.run(function (ctx) {
    var sheetName = "Sheet1";
    var rangeAddress = "A1:C3";
    var range = ctx.workbook.worksheets.getItem(sheetName).getRange(rangeAddress);
    var conditionalFormat = range.conditionalFormats.add(Excel.ConditionalFormatType.iconSet);
    conditionalFormat.iconOrNull.style = Excel.IconSet.fourTrafficLights;
    var cfCount = range.conditionalFormats.getCount(); 

    return ctx.sync().then(function () {
        console.log("Count: " + cfCount.value);
    });
}).catch(function (error) {
    console.log("Error: " + error);
    if (error instanceof OfficeExtension.Error) {
        console.log("Debug info: " + JSON.stringify(error.debugInfo));
    }
});

getItemAt(index: number)

Returns a conditional format at the given index.

Syntax

conditionalFormatCollectionObject.getItemAt(index);

Parameters

Parameter	Type	Description
index	number	Index of the conditional formats to be retrieved.

Returns

ConditionalFormat

Examples

Excel.run(function (ctx) {
    var sheetName = "Sheet1";
    var rangeAddress = "A1:C3";
    var range = ctx.workbook.worksheets.getItem(sheetName).getRange(rangeAddress);
    var conditionalFormats = range.conditionalFormats;
    var conditionalFormat = conditionalFormats.getItemAt(3);
    return ctx.sync().then(function () {
        console.log("Conditional Format at Item 3 Loaded");
    });
}).catch(function (error) {
    console.log("Error: " + error);
    if (error instanceof OfficeExtension.Error) {
        console.log("Debug info: " + JSON.stringify(error.debugInfo));
    }
    });