[]
Sheets.ConditionalFormatting.ConditionalFormats
• new ConditionalFormats(worksheet
)
Represents a format condition class.
Name | Type | Description |
---|---|---|
worksheet |
Worksheet |
The sheet. |
▸ 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);
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. |
The two scale rule added to the rule collection.
▸ 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)]);
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. |
The three scale rule added to the rule collection.
▸ 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)]);
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. |
The average rule added to the rule collection.
▸ 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);
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. |
The cell value rule added to the rule collection.
▸ 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)]
);
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. |
The state rule added to the rule collection.
▸ 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)]);
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. |
The data bar rule added to the rule collection.
▸ 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)]);
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. |
The date occurring rule added to the rule collection.
▸ 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);
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. |
The duplicate rule added to the rule collection.
▸ 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);
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. |
The formula rule added to the rule collection.
▸ 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)]);
Name | Type | Description |
---|---|---|
iconSetTye |
IconSetType |
- |
ranges |
Range [] |
The cell ranges where the rule is applied whose item type is GC.Spread.Sheets.Range. |
The icon set rule added to the rule collection.
▸ 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)]
);
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. |
The state rule added to the rule collection.
▸ 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);
Name | Type | Description |
---|---|---|
rule |
ConditionRuleBase |
The rule to add. |
▸ 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");
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. |
The text rule added to the rule collection.
▸ 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);
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. |
The top 10 rule added to the rule collection.
▸ 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)]);
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. |
The unique rule added to the rule collection.
▸ 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();
void
▸ 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);
Name | Type | Description |
---|---|---|
rule |
ConditionRuleBase |
The rule for which to check. |
row |
number |
The row index. |
column |
number |
The column index. |
boolean
true
if the specified cell contains a specified rule; otherwise, false
.
▸ 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);
number
The number of rule objects in the collection.
▸ 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());
Name | Type | Description |
---|---|---|
index |
number |
The index from which to get the rule. |
The rule from the index.
▸ 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);
Name | Type | Description |
---|---|---|
row |
number |
The row index. |
column |
number |
The column index. |
The conditional rules.
▸ 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);
Name | Type | Description |
---|---|---|
rule |
ConditionRuleBase |
The rule object to remove from the ConditionalFormats object. |
void
▸ 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);
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. |
void