FormatCondition

FormatCondition class

Represents conditional formatting condition.

class FormatCondition;

Example

const { Workbook, CellArea, FormatConditionType, OperatorType, Color } = require("aspose.cells.node");

//Instantiating a Workbook object
var workbook = new Workbook();
var sheet = workbook.worksheets.get(0);

//Adds an empty conditional formatting
var index = sheet.conditionalFormattings.add();
var fcs = sheet.conditionalFormattings.get(index);

//Sets the conditional format range.
var ca = new CellArea();
ca.startRow = 0;
ca.endRow = 0;
ca.startColumn = 0;
ca.endColumn = 0;
fcs.addArea(ca);

ca = new CellArea();
ca.startRow = 1;
ca.endRow = 1;
ca.startColumn = 1;
ca.endColumn = 1;
fcs.addArea(ca);

//Adds condition.
var conditionIndex = fcs.addCondition(FormatConditionType.CellValue, OperatorType.Between, "=A2", "100");
//Adds condition.
var conditionIndex2 = fcs.addCondition(FormatConditionType.CellValue, OperatorType.Between, "50", "100");

//Sets the background color.
var fc = fcs.get(conditionIndex);
fc.style.backgroundColor = Color.Red;

//Saving the Excel file
workbook.save("output/FormatCondition.xls");

Properties

PropertyTypeDescription
formula1stringGets and sets the value or expression associated with conditional formatting.
formula2stringGets and sets the value or expression associated with conditional formatting.
operatorOperatorTypeGets and sets the conditional format operator type.
stopIfTruebooleanTrue, no rules with lower priority may be applied over this rule, when this rule evaluates to true. Only applies for Excel 2007;
prioritynumberThe priority of this conditional formatting rule. This value is used to determine which format should be evaluated and rendered. Lower numeric values are higher priority than higher numeric values, where ‘1’ is the highest priority.
styleStyleGets or setts style of conditional formatted cell ranges.
typeFormatConditionTypeGets and sets whether the conditional format Type.
iconSetIconSetReadonly. Get the conditional formatting’s “IconSet” instance. The default instance’s IconSetType is TrafficLights31. Valid only for type = IconSet.
dataBarDataBarReadonly. Get the conditional formatting’s “DataBar” instance. The default instance’s color is blue. Valid only for type is DataBar.
colorScaleColorScaleReadonly. Get the conditional formatting’s “ColorScale” instance. The default instance is a “green-yellow-red” 3ColorScale . Valid only for type = ColorScale.
top10Top10Readonly. Get the conditional formatting’s “Top10” instance. The default instance’s rule highlights cells whose values fall in the top 10 bracket. Valid only for type is Top10.
aboveAverageAboveAverageReadonly. Get the conditional formatting’s “AboveAverage” instance. The default instance’s rule highlights cells that are above the average for all values in the range. Valid only for type = AboveAverage.
textstringThe text value in a “text contains” conditional formatting rule. Valid only for type = containsText, notContainsText, beginsWith and endsWith. The default value is null.
timePeriodTimePeriodTypeThe applicable time period in a “date occurring…” conditional formatting rule. Valid only for type = timePeriod. The default value is TimePeriodType.Today.

Methods

MethodDescription
getFormula1()@deprecated. Please use the ‘formula1’ property instead. Gets and sets the value or expression associated with conditional formatting.
setFormula1(string)@deprecated. Please use the ‘formula1’ property instead. Gets and sets the value or expression associated with conditional formatting.
getFormula2()@deprecated. Please use the ‘formula2’ property instead. Gets and sets the value or expression associated with conditional formatting.
setFormula2(string)@deprecated. Please use the ‘formula2’ property instead. Gets and sets the value or expression associated with conditional formatting.
getOperator()@deprecated. Please use the ‘operator’ property instead. Gets and sets the conditional format operator type.
setOperator(OperatorType)@deprecated. Please use the ‘operator’ property instead. Gets and sets the conditional format operator type.
getStopIfTrue()@deprecated. Please use the ‘stopIfTrue’ property instead. True, no rules with lower priority may be applied over this rule, when this rule evaluates to true. Only applies for Excel 2007;
setStopIfTrue(boolean)@deprecated. Please use the ‘stopIfTrue’ property instead. True, no rules with lower priority may be applied over this rule, when this rule evaluates to true. Only applies for Excel 2007;
getPriority()@deprecated. Please use the ‘priority’ property instead. The priority of this conditional formatting rule. This value is used to determine which format should be evaluated and rendered. Lower numeric values are higher priority than higher numeric values, where ‘1’ is the highest priority.
setPriority(number)@deprecated. Please use the ‘priority’ property instead. The priority of this conditional formatting rule. This value is used to determine which format should be evaluated and rendered. Lower numeric values are higher priority than higher numeric values, where ‘1’ is the highest priority.
getStyle()@deprecated. Please use the ‘style’ property instead. Gets or setts style of conditional formatted cell ranges.
setStyle(Style)@deprecated. Please use the ‘style’ property instead. Gets or setts style of conditional formatted cell ranges.
getType()@deprecated. Please use the ’type’ property instead. Gets and sets whether the conditional format Type.
setType(FormatConditionType)@deprecated. Please use the ’type’ property instead. Gets and sets whether the conditional format Type.
getIconSet()@deprecated. Please use the ‘iconSet’ property instead. Get the conditional formatting’s “IconSet” instance. The default instance’s IconSetType is TrafficLights31. Valid only for type = IconSet.
getDataBar()@deprecated. Please use the ‘dataBar’ property instead. Get the conditional formatting’s “DataBar” instance. The default instance’s color is blue. Valid only for type is DataBar.
getColorScale()@deprecated. Please use the ‘colorScale’ property instead. Get the conditional formatting’s “ColorScale” instance. The default instance is a “green-yellow-red” 3ColorScale . Valid only for type = ColorScale.
getTop10()@deprecated. Please use the ’top10’ property instead. Get the conditional formatting’s “Top10” instance. The default instance’s rule highlights cells whose values fall in the top 10 bracket. Valid only for type is Top10.
getAboveAverage()@deprecated. Please use the ‘aboveAverage’ property instead. Get the conditional formatting’s “AboveAverage” instance. The default instance’s rule highlights cells that are above the average for all values in the range. Valid only for type = AboveAverage.
getText()@deprecated. Please use the ’text’ property instead. The text value in a “text contains” conditional formatting rule. Valid only for type = containsText, notContainsText, beginsWith and endsWith. The default value is null.
setText(string)@deprecated. Please use the ’text’ property instead. The text value in a “text contains” conditional formatting rule. Valid only for type = containsText, notContainsText, beginsWith and endsWith. The default value is null.
getTimePeriod()@deprecated. Please use the ’timePeriod’ property instead. The applicable time period in a “date occurring…” conditional formatting rule. Valid only for type = timePeriod. The default value is TimePeriodType.Today.
setTimePeriod(TimePeriodType)@deprecated. Please use the ’timePeriod’ property instead. The applicable time period in a “date occurring…” conditional formatting rule. Valid only for type = timePeriod. The default value is TimePeriodType.Today.
getFormula1(boolean, boolean)Gets the value or expression associated with this format condition.
getFormula1(boolean, boolean, number, number)Gets the value or expression of the conditional formatting of the cell.
getFormula1(number, number)Gets the formula of the conditional formatting of the cell.
getFormula2(boolean, boolean)Gets the value or expression associated with this format condition.
getFormula2(boolean, boolean, number, number)Gets the value or expression of the conditional formatting of the cell.
getFormula2(number, number)Gets the formula of the conditional formatting of the cell.
setFormulas(string, string, boolean, boolean)Sets the value or expression associated with this format condition.
setFormula1(string, boolean, boolean)Sets the value or expression associated with this format condition.
setFormula2(string, boolean, boolean)Sets the value or expression associated with this format condition.
isNull()Checks whether the implementation object is null.

formula1

Gets and sets the value or expression associated with conditional formatting.

formula1 : string;

Remarks

Please add all areas before setting formula. For setting formula for this condition, if the input value starts with ‘=’, then it will be taken as formula. Otherwise it will be taken as plain value(text, number, bool). For text value that starts with ‘=’, user may input it as formula in format: “="=…"”.

formula2

Gets and sets the value or expression associated with conditional formatting.

formula2 : string;

Remarks

Please add all areas before setting formula. For setting formula for this condition, if the input value starts with ‘=’, then it will be taken as formula. Otherwise it will be taken as plain value(text, number, bool). For text value that starts with ‘=’, user may input it as formula in format: “="=…"”.

operator

Gets and sets the conditional format operator type.

operator : OperatorType;

stopIfTrue

True, no rules with lower priority may be applied over this rule, when this rule evaluates to true. Only applies for Excel 2007;

stopIfTrue : boolean;

priority

The priority of this conditional formatting rule. This value is used to determine which format should be evaluated and rendered. Lower numeric values are higher priority than higher numeric values, where ‘1’ is the highest priority.

priority : number;

style

Gets or setts style of conditional formatted cell ranges.

style : Style;

type

Gets and sets whether the conditional format Type.

type : FormatConditionType;

iconSet

Readonly. Get the conditional formatting’s “IconSet” instance. The default instance’s IconSetType is TrafficLights31. Valid only for type = IconSet.

iconSet : IconSet;

dataBar

Readonly. Get the conditional formatting’s “DataBar” instance. The default instance’s color is blue. Valid only for type is DataBar.

dataBar : DataBar;

colorScale

Readonly. Get the conditional formatting’s “ColorScale” instance. The default instance is a “green-yellow-red” 3ColorScale . Valid only for type = ColorScale.

colorScale : ColorScale;

top10

Readonly. Get the conditional formatting’s “Top10” instance. The default instance’s rule highlights cells whose values fall in the top 10 bracket. Valid only for type is Top10.

top10 : Top10;

aboveAverage

Readonly. Get the conditional formatting’s “AboveAverage” instance. The default instance’s rule highlights cells that are above the average for all values in the range. Valid only for type = AboveAverage.

aboveAverage : AboveAverage;

text

The text value in a “text contains” conditional formatting rule. Valid only for type = containsText, notContainsText, beginsWith and endsWith. The default value is null.

text : string;

timePeriod

The applicable time period in a “date occurring…” conditional formatting rule. Valid only for type = timePeriod. The default value is TimePeriodType.Today.

timePeriod : TimePeriodType;

getFormula1()

@deprecated. Please use the ‘formula1’ property instead. Gets and sets the value or expression associated with conditional formatting.

getFormula1() : string;

Remarks

Please add all areas before setting formula. For setting formula for this condition, if the input value starts with ‘=’, then it will be taken as formula. Otherwise it will be taken as plain value(text, number, bool). For text value that starts with ‘=’, user may input it as formula in format: “="=…"”.

setFormula1(string)

@deprecated. Please use the ‘formula1’ property instead. Gets and sets the value or expression associated with conditional formatting.

setFormula1(value: string) : void;

Parameters:

ParameterTypeDescription
valuestringThe value to set.

Remarks

Please add all areas before setting formula. For setting formula for this condition, if the input value starts with ‘=’, then it will be taken as formula. Otherwise it will be taken as plain value(text, number, bool). For text value that starts with ‘=’, user may input it as formula in format: “="=…"”.

getFormula2()

@deprecated. Please use the ‘formula2’ property instead. Gets and sets the value or expression associated with conditional formatting.

getFormula2() : string;

Remarks

Please add all areas before setting formula. For setting formula for this condition, if the input value starts with ‘=’, then it will be taken as formula. Otherwise it will be taken as plain value(text, number, bool). For text value that starts with ‘=’, user may input it as formula in format: “="=…"”.

setFormula2(string)

@deprecated. Please use the ‘formula2’ property instead. Gets and sets the value or expression associated with conditional formatting.

setFormula2(value: string) : void;

Parameters:

ParameterTypeDescription
valuestringThe value to set.

Remarks

Please add all areas before setting formula. For setting formula for this condition, if the input value starts with ‘=’, then it will be taken as formula. Otherwise it will be taken as plain value(text, number, bool). For text value that starts with ‘=’, user may input it as formula in format: “="=…"”.

getOperator()

@deprecated. Please use the ‘operator’ property instead. Gets and sets the conditional format operator type.

getOperator() : OperatorType;

Returns

OperatorType

setOperator(OperatorType)

@deprecated. Please use the ‘operator’ property instead. Gets and sets the conditional format operator type.

setOperator(value: OperatorType) : void;

Parameters:

ParameterTypeDescription
valueOperatorTypeThe value to set.

getStopIfTrue()

@deprecated. Please use the ‘stopIfTrue’ property instead. True, no rules with lower priority may be applied over this rule, when this rule evaluates to true. Only applies for Excel 2007;

getStopIfTrue() : boolean;

setStopIfTrue(boolean)

@deprecated. Please use the ‘stopIfTrue’ property instead. True, no rules with lower priority may be applied over this rule, when this rule evaluates to true. Only applies for Excel 2007;

setStopIfTrue(value: boolean) : void;

Parameters:

ParameterTypeDescription
valuebooleanThe value to set.

getPriority()

@deprecated. Please use the ‘priority’ property instead. The priority of this conditional formatting rule. This value is used to determine which format should be evaluated and rendered. Lower numeric values are higher priority than higher numeric values, where ‘1’ is the highest priority.

getPriority() : number;

setPriority(number)

@deprecated. Please use the ‘priority’ property instead. The priority of this conditional formatting rule. This value is used to determine which format should be evaluated and rendered. Lower numeric values are higher priority than higher numeric values, where ‘1’ is the highest priority.

setPriority(value: number) : void;

Parameters:

ParameterTypeDescription
valuenumberThe value to set.

getStyle()

@deprecated. Please use the ‘style’ property instead. Gets or setts style of conditional formatted cell ranges.

getStyle() : Style;

Returns

Style

setStyle(Style)

@deprecated. Please use the ‘style’ property instead. Gets or setts style of conditional formatted cell ranges.

setStyle(value: Style) : void;

Parameters:

ParameterTypeDescription
valueStyleThe value to set.

getType()

@deprecated. Please use the ’type’ property instead. Gets and sets whether the conditional format Type.

getType() : FormatConditionType;

Returns

FormatConditionType

setType(FormatConditionType)

@deprecated. Please use the ’type’ property instead. Gets and sets whether the conditional format Type.

setType(value: FormatConditionType) : void;

Parameters:

ParameterTypeDescription
valueFormatConditionTypeThe value to set.

getIconSet()

@deprecated. Please use the ‘iconSet’ property instead. Get the conditional formatting’s “IconSet” instance. The default instance’s IconSetType is TrafficLights31. Valid only for type = IconSet.

getIconSet() : IconSet;

Returns

IconSet

getDataBar()

@deprecated. Please use the ‘dataBar’ property instead. Get the conditional formatting’s “DataBar” instance. The default instance’s color is blue. Valid only for type is DataBar.

getDataBar() : DataBar;

Returns

DataBar

getColorScale()

@deprecated. Please use the ‘colorScale’ property instead. Get the conditional formatting’s “ColorScale” instance. The default instance is a “green-yellow-red” 3ColorScale . Valid only for type = ColorScale.

getColorScale() : ColorScale;

Returns

ColorScale

getTop10()

@deprecated. Please use the ’top10’ property instead. Get the conditional formatting’s “Top10” instance. The default instance’s rule highlights cells whose values fall in the top 10 bracket. Valid only for type is Top10.

getTop10() : Top10;

Returns

Top10

getAboveAverage()

@deprecated. Please use the ‘aboveAverage’ property instead. Get the conditional formatting’s “AboveAverage” instance. The default instance’s rule highlights cells that are above the average for all values in the range. Valid only for type = AboveAverage.

getAboveAverage() : AboveAverage;

Returns

AboveAverage

getText()

@deprecated. Please use the ’text’ property instead. The text value in a “text contains” conditional formatting rule. Valid only for type = containsText, notContainsText, beginsWith and endsWith. The default value is null.

getText() : string;

setText(string)

@deprecated. Please use the ’text’ property instead. The text value in a “text contains” conditional formatting rule. Valid only for type = containsText, notContainsText, beginsWith and endsWith. The default value is null.

setText(value: string) : void;

Parameters:

ParameterTypeDescription
valuestringThe value to set.

getTimePeriod()

@deprecated. Please use the ’timePeriod’ property instead. The applicable time period in a “date occurring…” conditional formatting rule. Valid only for type = timePeriod. The default value is TimePeriodType.Today.

getTimePeriod() : TimePeriodType;

Returns

TimePeriodType

setTimePeriod(TimePeriodType)

@deprecated. Please use the ’timePeriod’ property instead. The applicable time period in a “date occurring…” conditional formatting rule. Valid only for type = timePeriod. The default value is TimePeriodType.Today.

setTimePeriod(value: TimePeriodType) : void;

Parameters:

ParameterTypeDescription
valueTimePeriodTypeThe value to set.

getFormula1(boolean, boolean)

Gets the value or expression associated with this format condition.

getFormula1(isR1C1: boolean, isLocal: boolean) : string;

Parameters:

ParameterTypeDescription
isR1C1booleanWhether the formula needs to be formatted as R1C1.
isLocalbooleanWhether the formula needs to be formatted by locale.

Returns

The value or expression associated with this format condition.

getFormula1(boolean, boolean, number, number)

Gets the value or expression of the conditional formatting of the cell.

getFormula1(isR1C1: boolean, isLocal: boolean, row: number, column: number) : string;

Parameters:

ParameterTypeDescription
isR1C1booleanWhether the formula needs to be formatted as R1C1.
isLocalbooleanWhether the formula needs to be formatted by locale.
rownumberThe row index.
columnnumberThe column index.

Returns

The value or expression associated with the conditional formatting of the cell.

Remarks

The given cell must be contained by this conditional formatting, otherwise null will be returned.

getFormula1(number, number)

Gets the formula of the conditional formatting of the cell.

getFormula1(row: number, column: number) : string;

Parameters:

ParameterTypeDescription
rownumberThe row index.
columnnumberThe column index.

Returns

The formula.

getFormula2(boolean, boolean)

Gets the value or expression associated with this format condition.

getFormula2(isR1C1: boolean, isLocal: boolean) : string;

Parameters:

ParameterTypeDescription
isR1C1booleanWhether the formula needs to be formatted as R1C1.
isLocalbooleanWhether the formula needs to be formatted by locale.

Returns

The value or expression associated with this format condition.

getFormula2(boolean, boolean, number, number)

Gets the value or expression of the conditional formatting of the cell.

getFormula2(isR1C1: boolean, isLocal: boolean, row: number, column: number) : string;

Parameters:

ParameterTypeDescription
isR1C1booleanWhether the formula needs to be formatted as R1C1.
isLocalbooleanWhether the formula needs to be formatted by locale.
rownumberThe row index.
columnnumberThe column index.

Returns

The value or expression associated with the conditional formatting of the cell.

Remarks

The given cell must be contained by this conditional formatting, otherwise null will be returned.

getFormula2(number, number)

Gets the formula of the conditional formatting of the cell.

getFormula2(row: number, column: number) : string;

Parameters:

ParameterTypeDescription
rownumberThe row index.
columnnumberThe column index.

Returns

The formula.

setFormulas(string, string, boolean, boolean)

Sets the value or expression associated with this format condition.

setFormulas(formula1: string, formula2: string, isR1C1: boolean, isLocal: boolean) : void;

Parameters:

ParameterTypeDescription
formula1stringThe value or expression associated with this format condition. /// If the input value starts with ‘=’, then it will be taken as formula. Otherwise it will be taken as plain value(text, number, bool). /// For text value that starts with ‘=’, user may input it as formula in format: “="=…"”.
formula2stringThe value or expression associated with this format condition. The input format is same with formula1
isR1C1booleanWhether the formula is R1C1 formula.
isLocalbooleanWhether the formula is locale formatted.

setFormula1(string, boolean, boolean)

Sets the value or expression associated with this format condition.

setFormula1(formula: string, isR1C1: boolean, isLocal: boolean) : void;

Parameters:

ParameterTypeDescription
formulastringThe value or expression associated with this format condition. /// If the input value starts with ‘=’, then it will be taken as formula. Otherwise it will be taken as plain value(text, number, bool). /// For text value that starts with ‘=’, user may input it as formula in format: “="=…"”.
isR1C1booleanWhether the formula is R1C1 formula.
isLocalbooleanWhether the formula is locale formatted.

setFormula2(string, boolean, boolean)

Sets the value or expression associated with this format condition.

setFormula2(formula: string, isR1C1: boolean, isLocal: boolean) : void;

Parameters:

ParameterTypeDescription
formulastringThe value or expression associated with this format condition. /// If the input value starts with ‘=’, then it will be taken as formula. Otherwise it will be taken as plain value(text, number, bool). /// For text value that starts with ‘=’, user may input it as formula in format: “="=…"”.
isR1C1booleanWhether the formula is R1C1 formula.
isLocalbooleanWhether the formula is locale formatted.

isNull()

Checks whether the implementation object is null.

isNull() : boolean;