Class: Condition

Sheets.ConditionalFormatting.Condition

Table of contents

Constructors

• constructor

Methods

• compareType
• evaluate
• expected
• formula
• getExpected
• getValidList
• ignoreBlank
• ignoreCase
• item1
• item2
• preciseCompareDate
• ranges
• reset
• treatNullValueAsZero
• useWildCards
• fromDay
• fromFormula
• fromMonth
• fromQuarter
• fromSource
• fromWeek
• fromYear

Constructors

constructor

• new Condition(conditionType, args)

Represents a conditional item using the parameter object.

example

var nCondition = new GC.Spread.Sheets.ConditionalFormatting.Condition(GC.Spread.Sheets.ConditionalFormatting.ConditionType.averageCondition, {compareType: GC.Spread.Sheets.ConditionalFormatting.AverageConditionType.above});
nCondition.ranges([new GC.Spread.Sheets.Range(0, 0, 10, 3)]);
var validator = new GC.Spread.Sheets.DataValidation.DefaultDataValidator(nCondition);
validator.type(GC.Spread.Sheets.DataValidation.CriteriaType.custom);
activeSheet.getCell(0, 0, GC.Spread.Sheets.SheetArea.viewport).validator(validator);
spread.options.highlightInvalidData = true;
activeSheet.setValue(0, 0, 5);
activeSheet.setValue(1, 0, 15);

Parameters

Name	Type
conditionType	ConditionType
args	Object

Methods

compareType

▸ compareType(value?): any

Gets or sets the rule compare type.

example

 var data = ["testing", 'est', 'testtest', 'tesla', 'taste'];
 activeSheet.setArray(0,0,data);
 //this example will highlight show the cell which text not contain 'test'
 var nCondition = new GC.Spread.Sheets.ConditionalFormatting.Condition(GC.Spread.Sheets.ConditionalFormatting.ConditionType.textCondition);
 nCondition.compareType(GC.Spread.Sheets.ConditionalFormatting.TextCompareType.contains);
 nCondition.expected("test");
 var validator = new GC.Spread.Sheets.DataValidation.DefaultDataValidator(nCondition);
 validator.type(GC.Spread.Sheets.DataValidation.CriteriaType.custom);
 activeSheet.getRange(0, 0, 5,1).validator(validator);
 spread.options.highlightInvalidData = true;

Parameters

Name	Type	Description
value?	LogicalOperators | GeneralComparisonOperators | TextCompareType | ColorCompareType | DateCompareType	The rule compare type.

Returns

any

If no value is set, returns the rule compare type; otherwise, returns the condition.

---

evaluate

▸ evaluate(evaluator, baseRow, baseColumn, actualObj): boolean

Evaluates the condition using the specified evaluator.

example

 //this example will highlight show the cell which text not contain 'test'
 var data = ["testing", 'est', 'testtest', 'tesla', 'taste'];
 activeSheet.setArray(0, 0, data);
 var nCondition = new GC.Spread.Sheets.ConditionalFormatting.Condition(GC.Spread.Sheets.ConditionalFormatting.ConditionType.textCondition);
 nCondition.compareType(GC.Spread.Sheets.ConditionalFormatting.TextCompareType.contains);
 nCondition.expected("test");
 var validator = new GC.Spread.Sheets.DataValidation.DefaultDataValidator(nCondition);
 validator.type(GC.Spread.Sheets.DataValidation.CriteriaType.custom);
 activeSheet.getRange(0, 0, 5, 1).validator(validator);
 spread.options.highlightInvalidData = true;
 for (var i = 0; i < 10; i++) {
     var result = nCondition.evaluate(activeSheet, i, 0, data[i]);
     console.log(result);
 }

Parameters

Name	Type	Description
evaluator	Object	The evaluator that can evaluate an expression or a function.
baseRow	number	The base row index for evaluation.
baseColumn	number	The base column index for evaluation.
actualObj	Object	The actual value of object1 for evaluation.

Returns

boolean

true if the result is successful; otherwise, false.

---

expected

▸ expected(value?): any

Gets or sets the expected value.

example

var nCondition = new GC.Spread.Sheets.ConditionalFormatting.Condition(GC.Spread.Sheets.ConditionalFormatting.ConditionType.dateCondition);
nCondition.compareType(GC.Spread.Sheets.ConditionalFormatting.DateCompareType.before);
nCondition.expected(new Date(2012, 11, 31));
var validator = new GC.Spread.Sheets.DataValidation.DefaultDataValidator(nCondition);
validator.type(GC.Spread.Sheets.DataValidation.CriteriaType.custom);
activeSheet.getCell(0, 0, GC.Spread.Sheets.SheetArea.viewport).validator(validator);
spread.options.highlightInvalidData = true;
activeSheet.setValue(0, 0, new Date(2012, 12, 12));

Parameters

Name	Type	Description
value?	any	The expected value.

Returns

any

If no value is set, returns the expected value; otherwise, returns the condition.

---

formula

▸ formula(formulaOrBaseRow?, baseColumn?): any

Gets or sets the expected formula.

example

var textLengthCondition = new GC.Spread.Sheets.ConditionalFormatting.Condition(GC.Spread.Sheets.ConditionalFormatting.ConditionType.textLengthCondition);
textLengthCondition.compareType(GC.Spread.Sheets.ConditionalFormatting.GeneralComparisonOperators.greaterThan);
textLengthCondition.formula("$C$1"); // formula used to calculate a number.
var validator = new GC.Spread.Sheets.DataValidation.DefaultDataValidator(textLengthCondition);
validator.type(GC.Spread.Sheets.DataValidation.CriteriaType.custom);
activeSheet.getCell(0, 0, GC.Spread.Sheets.SheetArea.viewport).validator(validator);
spread.options.highlightInvalidData = true;
activeSheet.setValue(0, 0, "abcf");
//Set value 3 to $C$1, after this code, the value in Cell(0,0) is valid.
activeSheet.setValue(0, 2, 3);
//Set value 5 to $C$1, after this code, the value in Cel(0,0) is invalid.
// activeSheet.setValue(0, 2, 5);

Parameters

Name	Type	Description
formulaOrBaseRow?	string | number	The expected formula or base row.
baseColumn?	number	The base column.

Returns

any

If no value is set or baseRow and baseColumn is set, returns the expected formula; otherwise, returns the condition.

---

getExpected

▸ getExpected(evaluator, baseRow, baseColumn): Object

Gets the expected value.

Parameters

Name	Type	Description
evaluator	Object	The evaluator that can evaluate an expression or a function.
baseRow	number	The base row index for evaluation.
baseColumn	number	The base column index for evaluation.

Returns

Object

The expected value.

---

getValidList

▸ getValidList(evaluator, baseRow, baseColumn): any[]

Returns the list of valid data items.

Parameters

Name	Type	Description
evaluator	Object	The evaluator that can evaluate an expression or a function.
baseRow	number	The base row index for evaluation.
baseColumn	number	The base column index for evaluation.

Returns

any[]

The list of valid data items.

---

ignoreBlank

▸ ignoreBlank(value?): any

Gets or sets whether to ignore the blank cell.

example

var nCondition = new GC.Spread.Sheets.ConditionalFormatting.Condition(GC.Spread.Sheets.ConditionalFormatting.ConditionType.textCondition);
nCondition.compareType(GC.Spread.Sheets.ConditionalFormatting.TextCompareType.contains);
nCondition.expected("te?t");
nCondition.ignoreBlank(true);
nCondition.ignoreCase(true);
nCondition.useWildCards(true);
var validator = new GC.Spread.Sheets.DataValidation.DefaultDataValidator(nCondition);
validator.type(GC.Spread.Sheets.DataValidation.CriteriaType.custom);
activeSheet.getCell(0, 0, GC.Spread.Sheets.SheetArea.viewport).validator(validator);
spread.options.highlightInvalidData = true;
activeSheet.setValue(0, 0, "testing");

Parameters

Name	Type	Description
value?	boolean	Whether to ignore the blank cell.

Returns

any

If no value is set, returns whether to ignore the blank cell; otherwise, returns the condition.

---

ignoreCase

▸ ignoreCase(value?): any

Gets or sets whether to ignore case when performing the comparison.

example

//This example creates a text condition.
var nCondition = new GC.Spread.Sheets.ConditionalFormatting.Condition(GC.Spread.Sheets.ConditionalFormatting.ConditionType.textCondition);
nCondition.compareType(GC.Spread.Sheets.ConditionalFormatting.TextCompareType.contains);
nCondition.expected("te?t");
nCondition.ignoreBlank(true);
nCondition.ignoreCase(true);
nCondition.useWildCards(true);
var validator = new GC.Spread.Sheets.DataValidation.DefaultDataValidator(nCondition);
validator.type(GC.Spread.Sheets.DataValidation.CriteriaType.custom);
activeSheet.getCell(0, 0, GC.Spread.Sheets.SheetArea.viewport).validator(validator);
spread.options.highlightInvalidData = true;
activeSheet.setValue(0, 0, "testing");

Parameters

Name	Type	Description
value?	boolean	Whether to ignore case when performing the comparison.

Returns

any

If no value is set, returns whether to ignore case when performing the comparison; otherwise, returns the condition.

---

item1

▸ item1(value?): Condition

Gets or sets the first condition.

example

//This example validates a date.
var condition1 = new GC.Spread.Sheets.ConditionalFormatting.Condition(GC.Spread.Sheets.ConditionalFormatting.ConditionType.dateCondition, {compareType: GC.Spread.Sheets.ConditionalFormatting.DateCompareType.afterEqualsTo, expected: new Date(2012, 11, 31)});
var condition2 = new GC.Spread.Sheets.ConditionalFormatting.Condition(GC.Spread.Sheets.ConditionalFormatting.ConditionType.dateCondition, {compareType: GC.Spread.Sheets.ConditionalFormatting.DateCompareType.beforeEqualsTo, expected: new Date(2013, 11, 31)});
var nCondition = new GC.Spread.Sheets.ConditionalFormatting.Condition(GC.Spread.Sheets.ConditionalFormatting.ConditionType.relationCondition);
nCondition.compareType(GC.Spread.Sheets.ConditionalFormatting.LogicalOperators.and);
nCondition.item1(condition1);
nCondition.item2(condition2);
var validator = new GC.Spread.Sheets.DataValidation.DefaultDataValidator(nCondition);
validator.type(GC.Spread.Sheets.DataValidation.CriteriaType.custom);
activeSheet.getCell(0, 0, GC.Spread.Sheets.SheetArea.viewport).validator(validator);
spread.options.highlightInvalidData = true;
activeSheet.setValue(0, 0, new Date(2012, 11, 25));

Parameters

Name	Type	Description
value?	Condition	The first condition.

Returns

Condition

If no value is set, returns the first condition; otherwise, returns the relation condition.

---

item2

▸ item2(value?): Condition

Gets or sets the second condition.

example

//This example validates a date.
var condition1 = new GC.Spread.Sheets.ConditionalFormatting.Condition(GC.Spread.Sheets.ConditionalFormatting.ConditionType.dateCondition, {compareType: GC.Spread.Sheets.ConditionalFormatting.DateCompareType.afterEqualsTo, expected: new Date(2012, 11, 31)});
var condition2 = new GC.Spread.Sheets.ConditionalFormatting.Condition(GC.Spread.Sheets.ConditionalFormatting.ConditionType.dateCondition, {compareType: GC.Spread.Sheets.ConditionalFormatting.DateCompareType.beforeEqualsTo, expected: new Date(2013, 11, 31)});
var nCondition = new GC.Spread.Sheets.ConditionalFormatting.Condition(GC.Spread.Sheets.ConditionalFormatting.ConditionType.relationCondition);
nCondition.compareType(GC.Spread.Sheets.ConditionalFormatting.LogicalOperators.and);
nCondition.item1(condition1);
nCondition.item2(condition2);
var validator = new GC.Spread.Sheets.DataValidation.DefaultDataValidator(nCondition);
validator.type(GC.Spread.Sheets.DataValidation.CriteriaType.custom);
activeSheet.getCell(0, 0, GC.Spread.Sheets.SheetArea.viewport).validator(validator);
spread.options.highlightInvalidData = true;
activeSheet.setValue(0, 0, new Date(2012, 11, 25));

Parameters

Name	Type	Description
value?	Condition	The second condition.

Returns

Condition

If no value is set, returns the second condition; otherwise, returns the relation condition.

---

preciseCompareDate

▸ preciseCompareDate(value?): any

Gets or sets whether to compare whole day or precise date time.

example

//This example uses the preciseCompareDate method.
var dateCondition = new GC.Spread.Sheets.ConditionalFormatting.Condition(GC.Spread.Sheets.ConditionalFormatting.ConditionType.dateCondition);
dateCondition.compareType(GC.Spread.Sheets.ConditionalFormatting.DateCompareType.after);
dateCondition.expected(new Date(2020, 4, 22, 6));
//When the option is false, the validator compares the whole day, and they are the same, so validation fails and the red alert is displayed.
//When the option is true, the date time 7 o'clock is greater than 6 o'clock, so the result is successful.
var validator = new GC.Spread.Sheets.DataValidation.DefaultDataValidator(dateCondition);
validator.type(GC.Spread.Sheets.DataValidation.CriteriaType.date);
validator.preciseCompareDate(true);
activeSheet.getCell(0, 0, GC.Spread.Sheets.SheetArea.viewport).validator(validator);
spread.options.highlightInvalidData = true;
activeSheet.setValue(0, 0, new Date(2020, 4, 22, 7));

Parameters

Name	Type	Description
value?	boolean	Indicates compare whole day or precise date time.

Returns

any

If no value is set, returns compare whole day or precise date time; otherwise, returns the data validator.

---

ranges

▸ ranges(value?): any

Gets or sets the condition ranges.

example

//This example creates a unique condition.
var nCondition = new GC.Spread.Sheets.ConditionalFormatting.Condition(GC.Spread.Sheets.ConditionalFormatting.ConditionType.uniqueCondition);
nCondition.expected(true);
nCondition.ranges([new GC.Spread.Sheets.Range(0, 0, 5, 1)]);
var validator = new GC.Spread.Sheets.DataValidation.DefaultDataValidator(nCondition);
validator.type(GC.Spread.Sheets.DataValidation.CriteriaType.custom);
activeSheet.getCell(0, 0, GC.Spread.Sheets.SheetArea.viewport).validator(validator);
spread.options.highlightInvalidData = true;
activeSheet.setValue(0, 0, 5);

Parameters

Name	Type	Description
value?	Range[]	The condition ranges.

Returns

any

If no value is set, returns the condition ranges; otherwise, returns the condition.

---

reset

▸ reset(): void

Resets this instance.

example

var nCondition = new GC.Spread.Sheets.ConditionalFormatting.Condition(GC.Spread.Sheets.ConditionalFormatting.ConditionType.numberCondition);
nCondition.compareType(GC.Spread.Sheets.ConditionalFormatting.ComparisonOperators.greaterThan);
nCondition.expected(5);
var validator = new GC.Spread.Sheets.DataValidation.DefaultDataValidator(nCondition);
validator.type(GC.Spread.Sheets.DataValidation.CriteriaType.custom);
activeSheet.getCell(0, 0, GC.Spread.Sheets.SheetArea.viewport).validator(validator);
spread.options.highlightInvalidData = true;
activeSheet.setValue(0, 0, 5);
//Create a button
$("#button1").click(function () {
activeSheet.suspendPaint();
nCondition.reset();
activeSheet.resumePaint();
    });

Returns

void

---

treatNullValueAsZero

▸ treatNullValueAsZero(value?): any

Gets or sets whether to treat the null value in a cell as zero.

example

//This example sets the treatNullValueAsZero method.
var nCondition = new GC.Spread.Sheets.ConditionalFormatting.Condition(GC.Spread.Sheets.ConditionalFormatting.ConditionType.cellValueCondition);
nCondition.compareType(GC.Spread.Sheets.ConditionalFormatting.GeneralComparisonOperators.equalsTo);
nCondition.expected(0);
//When the option is false, the validation fails and the red alert is displayed.
//When the option is true, the blank cell is treated as zero and the validation is successful.
nCondition.treatNullValueAsZero(false);
var validator = new GC.Spread.Sheets.DataValidation.DefaultDataValidator(nCondition);
validator.type(GC.Spread.Sheets.DataValidation.CriteriaType.custom);
validator.ignoreBlank(false);
activeSheet.getCell(0, 0, GC.Spread.Sheets.SheetArea.viewport).validator(validator);
spread.options.highlightInvalidData = true;
activeSheet.setValue(0, 0, null);

Parameters

Name	Type	Description
value?	boolean	Whether to treat the null value in a cell as zero.

Returns

any

If no value is set, returns whether to treat the null value in a cell as zero; otherwise, returns the condition.

---

useWildCards

▸ useWildCards(value?): any

Gets or sets whether to compare strings using wildcards.

example

//This example allows wildcards.
var nCondition = new GC.Spread.Sheets.ConditionalFormatting.Condition(GC.Spread.Sheets.ConditionalFormatting.ConditionType.textCondition);
nCondition.compareType(GC.Spread.Sheets.ConditionalFormatting.TextCompareType.contains);
nCondition.expected("te?t");
nCondition.ignoreBlank(true);
nCondition.ignoreCase(true);
nCondition.useWildCards(true);
var validator = new GC.Spread.Sheets.DataValidation.DefaultDataValidator(nCondition);
validator.type(GC.Spread.Sheets.DataValidation.CriteriaType.custom);
activeSheet.getCell(0, 0, GC.Spread.Sheets.SheetArea.viewport).validator(validator);
spread.options.highlightInvalidData = true;
activeSheet.setValue(0, 0, "testing");

Parameters

Name	Type	Description
value?	boolean	Whether to compare strings using wildcards.

Returns

any

If no value is set, returns whether to compare strings using wildcards; otherwise, returns the condition.

---

fromDay

▸ Static fromDay(day): Condition

Creates a date extend condition object from the specified day.

static

Parameters

Name	Type	Description
day	number	The day.

Returns

Condition

A date extend condition object.

---

fromFormula

▸ Static fromFormula(formula): Condition

Creates the area condition from formula data.

static

Parameters

Name	Type	Description
formula	string	The formula that specifies a range that contains data items.

Returns

Condition

The area condition.

---

fromMonth

▸ Static fromMonth(month): Condition

Creates a date extend condition object from the specified month.

static

Parameters

Name	Type	Description
month	number	The month. The first month is 0.

Returns

Condition

A date extend condition object.

---

fromQuarter

▸ Static fromQuarter(quarter): Condition

Creates a date extend condition object from the specified quarter.

static

Parameters

Name	Type	Description
quarter	QuarterType	The quarter.

Returns

Condition

A date extend condition object.

---

fromSource

▸ Static fromSource(expected): Condition

Creates the area condition from source data.

static

Parameters

Name	Type	Description
expected	string	The expected source that separates each data item with a comma (",").

Returns

Condition

The area condition.

---

fromWeek

▸ Static fromWeek(week): Condition

Creates a date extend condition object from the specified week.

static

Parameters

Name	Type	Description
week	number	The week.

Returns

Condition

A date extend condition object.

---

fromYear

▸ Static fromYear(year): Condition

Creates a date extend condition object from the specified year.

static

Parameters

Name	Type	Description
year	number	The year.

Returns

Condition

A date extend condition object.