[]
        
(Showing Draft Content)

GC.Spread.Sheets.ConditionalFormatting.Condition

Class: Condition

Sheets.ConditionalFormatting.Condition

Table of contents

Constructors

Methods

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 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.getCell(0, 0, GC.Spread.Sheets.SheetArea.viewport).validator(validator);
spread.options.highlightInvalidData = true;
activeSheet.setValue(0, 0, "testing");

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.

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.GeneralComparisonOperators.greaterThan);
dateCondition.expected(new Date(2020, 4, 22, 06));
//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, 07));

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.