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));
    }
    });