Class: ConditionalFormats

Sheets.ConditionalFormatting.ConditionalFormats

Table of contents

Constructors

• constructor

Methods

• add2ScaleRule
• add3ScaleRule
• addAverageRule
• addCellValueRule
• addColumnStateRule
• addDataBarRule
• addDateOccurringRule
• addDuplicateRule
• addFormulaRule
• addIconSetRule
• addRowStateRule
• addRule
• addSpecificTextRule
• addTop10Rule
• addUniqueRule
• clearRule
• containsRule
• count
• getRule
• getRules
• removeRule
• removeRuleByRange

Constructors

constructor

• new ConditionalFormats(worksheet)

Represents a format condition class.

Parameters

Name	Type	Description
worksheet	Worksheet	The sheet.

Methods

add2ScaleRule

▸ add2ScaleRule(minType, minValue, minColor, maxType, maxValue, maxColor, ranges): ConditionRuleBase

Adds the two scale rule to the rule collection.

example

//This example uses the add2ScaleRule method.
activeSheet.conditionalFormats.add2ScaleRule(GC.Spread.Sheets.ConditionalFormatting.ScaleValueType.number,10,"Red",GC.Spread.Sheets.ConditionalFormatting.ScaleValueType.number,100,"Yellow", [new GC.Spread.Sheets.Range(0,0,10,3)]);
activeSheet.setValue(0,0, 1,3);
activeSheet.setValue(1,0, 50,3);
activeSheet.setValue(2,0, 100,3);

Parameters

Name	Type	Description
minType	ScaleValueType	The minimum scale type.
minValue	number	The minimum scale value.
minColor	string	The minimum scale color.
maxType	ScaleValueType	The maximum scale type.
maxValue	number	The maximum scale value.
maxColor	string	The maximum scale color.
ranges	Range[]	The cell ranges where the rule is applied whose item type is GC.Spread.Sheets.Range.

Returns

ConditionRuleBase

The two scale rule added to the rule collection.

---

add3ScaleRule

▸ add3ScaleRule(minType, minValue, minColor, midType, midValue, midColor, maxType, maxValue, maxColor, ranges): ConditionRuleBase

Adds the three scale rule to the rule collection.

example

//This example uses the add3ScaleRule method.
activeSheet.setValue(0,0, 1,3);
activeSheet.setValue(1,0, 50,3);
activeSheet.setValue(2,0, 100,3);
activeSheet.conditionalFormats.add3ScaleRule(1, 10, "red", 0, 50, "blue",2, 100, "yellow", [new GC.Spread.Sheets.Range(0, 0, 10, 3)]);

Parameters

Name	Type	Description
minType	ScaleValueType	The minimum scale type.
minValue	number	The minimum scale value.
minColor	string	The minimum scale color.
midType	ScaleValueType	The midpoint scale type.
midValue	number	The midpoint scale value.
midColor	string	The midpoint scale color.
maxType	ScaleValueType	The maximum scale type.
maxValue	number	The maximum scale value.
maxColor	string	The maximum scale color.
ranges	Range[]	The cell ranges where the rule is applied whose item type is GC.Spread.Sheets.Range.

Returns

ConditionRuleBase

The three scale rule added to the rule collection.

---

addAverageRule

▸ addAverageRule(type, style, ranges): ConditionRuleBase

Adds an average rule to the rule collection.

example

//This example uses the addAverageRule method.
activeSheet.setValue(0,0, 1,3);
activeSheet.setValue(1,0, 50,3);
activeSheet.setValue(2,0, 100,3);
activeSheet.setValue(3,0, 2,3);
activeSheet.setValue(4,0, 60,3);
activeSheet.setValue(5,0, 90,3);
activeSheet.setValue(6,0, 3,3);
activeSheet.setValue(7,0, 40,3);
activeSheet.setValue(8,0, 70,3);
activeSheet.setValue(9,0, 5,3);
activeSheet.setValue(10,0, 35,3);
var style = new GC.Spread.Sheets.Style();
style.backColor = "red";
style.borderLeft =new GC.Spread.Sheets.LineBorder("blue",GC.Spread.Sheets.LineStyle.medium);
style.borderTop = new GC.Spread.Sheets.LineBorder("blue",GC.Spread.Sheets.LineStyle.medium);
style.borderRight = new GC.Spread.Sheets.LineBorder("blue",GC.Spread.Sheets.LineStyle.medium);
style.borderBottom = new GC.Spread.Sheets.LineBorder("blue",GC.Spread.Sheets.LineStyle.medium);
activeSheet.conditionalFormats.addAverageRule(GC.Spread.Sheets.ConditionalFormatting.AverageConditionType.above,style,[new GC.Spread.Sheets.Range(0, 0, 10, 3)]);

Parameters

Name	Type	Description
type	AverageConditionType	The average condition type.
style	Style	The style that is applied to the cell when the condition is met.
ranges	Range[]	The cell ranges where the rule is applied whose item type is GC.Spread.Sheets.Range.

Returns

ConditionRuleBase

The average rule added to the rule collection.

---

addCellValueRule

▸ addCellValueRule(comparisonOperator, value1, value2, style, ranges): ConditionRuleBase

Adds the cell value rule to the rule collection.

example

//This example uses the addCellValueRule method.
var style = new GC.Spread.Sheets.Style();
style.backColor = "red";
var ranges=[new GC.Spread.Sheets.Range(0,0,5,1)];
activeSheet.conditionalFormats.addCellValueRule(GC.Spread.Sheets.ConditionalFormatting.ComparisonOperators.between, 2, 100, style, ranges);
activeSheet.setValue(0,0,1,3);
activeSheet.setValue(1,0,45,3);

Parameters

Name	Type	Description
comparisonOperator	ComparisonOperators	The comparison operator.
value1	Object	The first value.
value2	Object	The second value.
style	Style	The style that is applied to the cell when the condition is met.
ranges	Range[]	The cell ranges where the rule is applied whose item type is GC.Spread.Sheets.Range.

Returns

ConditionRuleBase

The cell value rule added to the rule collection.

---

addColumnStateRule

▸ addColumnStateRule(state, style, ranges): ConditionRuleBase

Adds a column state rule to the rule collection.

example

// Add a hover state rule in column direction.
activeSheet.conditionalFormats.addColumnStateRule(GC.Spread.Sheets.RowColumnStates.hover, new GC.Spread.Sheets.Style("yellow"), [new GC.Spread.Sheets.Range(-1, -1, -1, -1)]);
activeSheet.conditionalFormats.addColumnStateRule(
     GC.Spread.Sheets.RowColumnStates.hover,
     [new GC.Spread.Sheets.Style("green"), new GC.Spread.Sheets.Style("red")],
     [new GC.Spread.Sheets.Range(1, 1, 10, 5), new GC.Spread.Sheets.Range(13, 1, 10, 5)]
);

Parameters

Name	Type	Description
state	RowColumnStates	The state type.
style	Style | Style[]	The row style if the columns are matched the state. It could be an array of styles.
ranges	Range[]	The ranges where the rule is applied whose item type is GC.Spread.Sheets.Range.

Returns

ConditionRuleBase

The state rule added to the rule collection.

---

addDataBarRule

▸ addDataBarRule(minType, minValue, maxType, maxValue, color, ranges): ConditionRuleBase

Adds a data bar rule to the rule collection.

example

//This example uses the addDataBarRule method.
activeSheet.setValue(0,0,1,3);
activeSheet.setValue(1,0,15,3);
activeSheet.setValue(2,0,25,3);
activeSheet.setValue(3,0,-1,3);
activeSheet.conditionalFormats.addDataBarRule(GC.Spread.Sheets.ConditionalFormatting.ScaleValueType.number, -1, GC.Spread.Sheets.ConditionalFormatting.ScaleValueType.number, 40, "orange", [new GC.Spread.Sheets.Range(0,0,4,1)]);

Parameters

Name	Type	Description
minType	ScaleValueType	The minimum scale type.
minValue	number	The minimum scale value.
maxType	ScaleValueType	The maximum scale type.
maxValue	number	The maximum scale value.
color	string	The color data bar to show on the view.
ranges	Range[]	The cell ranges where the rule is applied whose item type is GC.Spread.Sheets.Range.

Returns

ConditionRuleBase

The data bar rule added to the rule collection.

---

addDateOccurringRule

▸ addDateOccurringRule(type, style, ranges): ConditionRuleBase

Adds the date occurring rule to the rule collection.

example

//This example uses the addDateOccurringRule method.
var style = new GC.Spread.Sheets.Style();
style.backColor = "red";
var d = new Date();
activeSheet.setValue(0, 0, d);
activeSheet.setValue(1, 0, new Date(d.setDate(d.getDate()+1)));
activeSheet.setValue(2, 0, new Date(d.setDate(d.getDate()+5)));
activeSheet.setValue(3, 0,new Date(d.setDate(d.getDate()+6)));
activeSheet.setValue(4, 0,new Date(d.setDate(d.getDate()+7)));
activeSheet.setValue(5, 0, new Date(d.setDate(d.getDate()+8)));
activeSheet.conditionalFormats.addDateOccurringRule(GC.Spread.Sheets.ConditionalFormatting.DateOccurringType.nextWeek, style, [new GC.Spread.Sheets.Range(0,0,10,1)]);

Parameters

Name	Type	Description
type	DateOccurringType	The data occurring type.
style	Style	The style that is applied to the cell when the condition is met.
ranges	Range[]	The cell ranges where the rule is applied whose item type is GC.Spread.Sheets.Range.

Returns

ConditionRuleBase

The date occurring rule added to the rule collection.

---

addDuplicateRule

▸ addDuplicateRule(style, ranges): ConditionRuleBase

Adds a duplicate rule to the rule collection.

example

//This example uses the addDuplicateRule method.
var style = new GC.Spread.Sheets.Style();
style.backColor = "yellow";
var ranges=[new GC.Spread.Sheets.Range(0,0,10,1)];
activeSheet.conditionalFormats.addDuplicateRule(style, ranges);
activeSheet.setValue(0, 0, 50);
activeSheet.setValue(1, 0, 50);
activeSheet.setValue(2, 0, 11);
activeSheet.setValue(3, 0, 5);

Parameters

Name	Type	Description
style	Style	The style that is applied to the cell when the condition is met.
ranges	Range[]	The cell ranges where the rule is applied whose item type is GC.Spread.Sheets.Range.

Returns

ConditionRuleBase

The duplicate rule added to the rule collection.

---

addFormulaRule

▸ addFormulaRule(formula, style, ranges): ConditionRuleBase

Adds the formula rule to the rule collection.

example

//This example uses the addFormulaRule method.
var style = new GC.Spread.Sheets.Style();
style.backColor = "red";
var ranges = [new GC.Spread.Sheets.Range(0, 0, 2, 1)];
activeSheet.conditionalFormats.addFormulaRule("=A1=B1+C1", style, ranges);
activeSheet.setValue(0, 0, 2,3);
activeSheet.setValue(0, 1, 1,3);
activeSheet.setValue(0, 2,1,3);
activeSheet.setValue(1, 0, 1,3);

Parameters

Name	Type	Description
formula	string	The condition formula.
style	Style	The style that is applied to the cell when the condition is met.
ranges	Range[]	The cell ranges where the rule is applied whose item type is GC.Spread.Sheets.Range.

Returns

ConditionRuleBase

The formula rule added to the rule collection.

---

addIconSetRule

▸ addIconSetRule(iconSetTye, ranges): ConditionRuleBase

Adds an icon set rule to the rule collection.

example

//This example uses the addIconSetRule method.
activeSheet.setValue(0,0,1,3);
activeSheet.setValue(1,0,15,3);
activeSheet.setValue(2,0,25,3);
activeSheet.setValue(3,0,-1,3);
activeSheet.conditionalFormats.addIconSetRule(GC.Spread.Sheets.ConditionalFormatting.IconSetType.fourTrafficLights, [new GC.Spread.Sheets.Range(0,0,4,1)]);

Parameters

Name	Type	Description
iconSetTye	IconSetType	-
ranges	Range[]	The cell ranges where the rule is applied whose item type is GC.Spread.Sheets.Range.

Returns

ConditionRuleBase

The icon set rule added to the rule collection.

---

addRowStateRule

▸ addRowStateRule(state, style, ranges): ConditionRuleBase

Adds a row state rule to the rule collection.

example

// Add a hover state rule in row direction.
activeSheet.conditionalFormats.addRowStateRule(GC.Spread.Sheets.RowColumnStates.hover, new GC.Spread.Sheets.Style("yellow"), [new GC.Spread.Sheets.Range(-1, -1, -1, -1)]);
// Add a hover state rule with variable styles
activeSheet.conditionalFormats.addRowStateRule(
     GC.Spread.Sheets.RowColumnStates.hover,
     [new GC.Spread.Sheets.Style("green"), new GC.Spread.Sheets.Style("red")],
     [new GC.Spread.Sheets.Range(1, 1, 10, 5), new GC.Spread.Sheets.Range(1, 7, 10, 5)]
);

Parameters

Name	Type	Description
state	RowColumnStates	The state type.
style	Style | Style[]	The row style if the rows are matched the state. It could be an array of styles.
ranges	Range[]	The ranges where the rule is applied whose item type is GC.Spread.Sheets.Range.

Returns

ConditionRuleBase

The state rule added to the rule collection.

---

addRule

▸ addRule(rule): ConditionRuleBase

Adds the rule.

example

//This example uses the addRule method.
var scale = new GC.Spread.Sheets.ConditionalFormatting.ScaleRule();
scale.ruleType(GC.Spread.Sheets.ConditionalFormatting.RuleType.threeScaleRule);
scale.midColor("red");
scale.midType(GC.Spread.Sheets.ConditionalFormatting.ScaleValueType.number);
scale.midValue(50);
scale.maxColor("blue");
scale.maxType(GC.Spread.Sheets.ConditionalFormatting.ScaleValueType.number);
scale.maxValue(100);
scale.minColor("yellow");
scale.minType(GC.Spread.Sheets.ConditionalFormatting.ScaleValueType.number);
scale.minValue(10);
scale.ranges([new GC.Spread.Sheets.Range(0, 0, 10, 3)]);
activeSheet.conditionalFormats.addRule(scale);
activeSheet.setValue(0,0, 1,3);
activeSheet.setValue(1,0, 50,3);
activeSheet.setValue(2,0, 100,3);

Parameters

Name	Type	Description
rule	ConditionRuleBase	The rule to add.

Returns

ConditionRuleBase

---

addSpecificTextRule

▸ addSpecificTextRule(comparisonOperator, text, style, ranges): ConditionRuleBase

Adds the text rule to the rule collection.

example

//This example uses the addSpecificTextRule method.
var style = new GC.Spread.Sheets.Style();
style.backColor = "red";
var ranges=[new GC.Spread.Sheets.Range(0,0,10,1)];
activeSheet.conditionalFormats.addSpecificTextRule(GC.Spread.Sheets.ConditionalFormatting.TextComparisonOperators.contains, "test", style, ranges);
activeSheet.setValue(0, 0, "testing");
activeSheet.setValue(1, 0, "test");
activeSheet.setValue(2, 0, "a");
activeSheet.setValue(3, 0, "t");

Parameters

Name	Type	Description
comparisonOperator	TextComparisonOperators	The comparison operator.
text	string	The text for comparison.
style	Style	The style that is applied to the cell when the condition is met.
ranges	Range[]	The cell ranges where the rule is applied to items whose item type is GC.Spread.Sheets.Range.

Returns

ConditionRuleBase

The text rule added to the rule collection.

---

addTop10Rule

▸ addTop10Rule(type, rank, style, ranges): ConditionRuleBase

Adds the top 10 rule or bottom 10 rule to the collection based on the Top10ConditionType object.

example

//This example uses the addTop10Rule method.
var style = new GC.Spread.Sheets.Style();
style.backColor = "red";
var ranges=[new GC.Spread.Sheets.Range(0,0,10,1)];
activeSheet.conditionalFormats.addTop10Rule(GC.Spread.Sheets.ConditionalFormatting.Top10ConditionType.top, 2, style, ranges);
activeSheet.setValue(0, 0, 1);
activeSheet.setValue(1, 0, 50);
activeSheet.setValue(2, 0, 11);
activeSheet.setValue(3, 0, 5);

Parameters

Name	Type	Description
type	Top10ConditionType	The top 10 condition.
rank	number	The number of top or bottom items to apply the style to.
style	Style	The style that is applied to the cell when the condition is met.
ranges	Range[]	The cell ranges where the rule is applied whose item type is GC.Spread.Sheets.Range.

Returns

ConditionRuleBase

The top 10 rule added to the rule collection.

---

addUniqueRule

▸ addUniqueRule(style, ranges): ConditionRuleBase

Adds a unique rule to the rule collection.

example

//This example uses the addUniqueRule method.
var style = new GC.Spread.Sheets.Style();
style.backColor = "green";
activeSheet.setValue(0, 0, 50);
activeSheet.setValue(1, 0, 50);
activeSheet.setValue(2, 0, 11);
activeSheet.setValue(3, 0, 5);
activeSheet.conditionalFormats.addUniqueRule(style, [new GC.Spread.Sheets.Range(0,0,10,1)]);

Parameters

Name	Type	Description
style	Style	The style that is applied to the cell when the condition is met.
ranges	Range[]	The cell ranges where the rule is applied whose item type is GC.Spread.Sheets.Range.

Returns

ConditionRuleBase

The unique rule added to the rule collection.

---

clearRule

▸ clearRule(): void

Removes all rules.

example

//This example uses the clearRule method.
activeSheet.setValue(0,0, 1,3);
activeSheet.setValue(1,0, 50,3);
activeSheet.setValue(2,0, 100,3);
activeSheet.conditionalFormats.add2ScaleRule(GC.Spread.Sheets.ConditionalFormatting.ScaleValueType.number, 10, "red", GC.Spread.Sheets.ConditionalFormatting.ScaleValueType.number, 100, "yellow", [new GC.Spread.Sheets.Range(0, 0, 10, 3)]);
// Remove comment in front of method to test
//activeSheet.conditionalFormats.clearRule();

Returns

void

---

containsRule

▸ containsRule(rule, row, column): boolean

Determines whether the specified cell contains a specified rule.

example

//This example checks to see if a cell has a specified rule.
var style = new GC.Spread.Sheets.Style();
style.backColor = "red";
var rule = new GC.Spread.Sheets.ConditionalFormatting.NormalConditionRule();
rule.ruleType(GC.Spread.Sheets.ConditionalFormatting.RuleType.cellValueRule);
rule.ranges([new GC.Spread.Sheets.Range(0,0,5,1)]);
rule.operator(GC.Spread.Sheets.ConditionalFormatting.ComparisonOperators.between);
rule.style(style);
rule.value1(2);
rule.value2(100);
activeSheet.conditionalFormats.addRule(rule);
activeSheet.setValue(0,0,1,3);
activeSheet.setValue(1,0,45,3);
var ruletest = activeSheet.conditionalFormats.containsRule(rule, 0, 0);
alert(ruletest);

Parameters

Name	Type	Description
rule	ConditionRuleBase	The rule for which to check.
row	number	The row index.
column	number	The column index.

Returns

boolean

true if the specified cell contains a specified rule; otherwise, false.

---

count

▸ count(): number

Gets the number of rule objects in the collection.

example

//This example counts the rules.
var style = new GC.Spread.Sheets.Style();
style.backColor = "red";
var rule = new GC.Spread.Sheets.ConditionalFormatting.NormalConditionRule();
rule.ruleType(GC.Spread.Sheets.ConditionalFormatting.RuleType.cellValueRule);
rule.ranges([new GC.Spread.Sheets.Range(0,0,5,1)]);
rule.operator(GC.Spread.Sheets.ConditionalFormatting.ComparisonOperators.between);
rule.style(style);
rule.value1(2);
rule.value2(100);
activeSheet.conditionalFormats.addRule(rule);
activeSheet.setValue(0,0,1,3);
activeSheet.setValue(1,0,45,3);
var ruletest = activeSheet.conditionalFormats.count();
alert(ruletest);

Returns

number

The number of rule objects in the collection.

---

getRule

▸ getRule(index): ConditionRuleBase

Gets the rule using the index.

example

//This example uses the getRule method.
var style = new GC.Spread.Sheets.Style();
style.backColor = "red";
var rule = new GC.Spread.Sheets.ConditionalFormatting.NormalConditionRule();
rule.ruleType(GC.Spread.Sheets.ConditionalFormatting.RuleType.cellValueRule);
rule.ranges([new GC.Spread.Sheets.Range(0,0,5,1)]);
rule.operator(GC.Spread.Sheets.ConditionalFormatting.ComparisonOperators.between);
rule.style(style);
rule.value1(2);
rule.value2(100);
activeSheet.conditionalFormats.addRule(rule);
activeSheet.setValue(0,0,1,3);
activeSheet.setValue(1,0,45,3);
var ruletest = activeSheet.conditionalFormats.getRule(0);
alert(ruletest.value1());

Parameters

Name	Type	Description
index	number	The index from which to get the rule.

Returns

ConditionRuleBase

The rule from the index.

---

getRules

▸ getRules(row, column): ConditionRuleBase[]

Gets the conditional rules from the cell at the specified row and column.

example

//This example uses the getRules method.
var style = new GC.Spread.Sheets.Style();
style.backColor = "red";
var rule = new GC.Spread.Sheets.ConditionalFormatting.NormalConditionRule();
rule.ruleType(GC.Spread.Sheets.ConditionalFormatting.RuleType.cellValueRule);
rule.ranges([new GC.Spread.Sheets.Range(0,0,5,1)]);
rule.operator(GC.Spread.Sheets.ConditionalFormatting.ComparisonOperators.between);
rule.style(style);
rule.value1(2);
rule.value2(100);
activeSheet.conditionalFormats.addRule(rule);
activeSheet.setValue(0,0,1,3);
activeSheet.setValue(1,0,45,3);
var ruletest = activeSheet.conditionalFormats.getRules();
alert(ruletest[0].style().backColor);

Parameters

Name	Type	Description
row	number	The row index.
column	number	The column index.

Returns

ConditionRuleBase[]

The conditional rules.

---

removeRule

▸ removeRule(rule): void

Removes a rule object from the ConditionalFormats object.

example

//This example uses the removeRule method.
var style = new GC.Spread.Sheets.Style();
style.backColor = "red";
var rule = new GC.Spread.Sheets.ConditionalFormatting.NormalConditionRule();
rule.ruleType(GC.Spread.Sheets.ConditionalFormatting.RuleType.cellValueRule);
rule.ranges([new GC.Spread.Sheets.Range(0,0,5,1)]);
rule.operator(GC.Spread.Sheets.ConditionalFormatting.ComparisonOperators.between);
rule.style(style);
rule.value1(2);
rule.value2(100);
activeSheet.conditionalFormats.addRule(rule);
activeSheet.setValue(0,0,1,3);
activeSheet.setValue(1,0,45,3);
activeSheet.conditionalFormats.removeRule(rule);

Parameters

Name	Type	Description
rule	ConditionRuleBase	The rule object to remove from the ConditionalFormats object.

Returns

void

---

removeRuleByRange

▸ removeRuleByRange(row, column, rowCount, columnCount): void

Removes the rules from a specified cell range.

example

//This example uses the removeRuleByRange method.
var style = new GC.Spread.Sheets.Style();
style.backColor = "red";
var rule = new GC.Spread.Sheets.ConditionalFormatting.NormalConditionRule();
rule.ruleType(GC.Spread.Sheets.ConditionalFormatting.RuleType.cellValueRule);
rule.ranges([new GC.Spread.Sheets.Range(0,0,5,1)]);
rule.operator(GC.Spread.Sheets.ConditionalFormatting.ComparisonOperators.between);
rule.style(style);
rule.value1(2);
rule.value2(100);
activeSheet.conditionalFormats.addRule(rule);
activeSheet.setValue(0,0,1,3);
activeSheet.setValue(1,0,45,3);
activeSheet.conditionalFormats.removeRuleByRange(0, 0, 5, 1);

Parameters

Name	Type	Description
row	number	The row index of the first cell in the range.
column	number	The column index of the first cell in the range.
rowCount	number	The number of rows in the range.
columnCount	number	The number of columns in the range.

Returns

void