[]
Sheets.ConditionalFormatting.Condition
• 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);
Name | Type |
---|---|
conditionType |
ConditionType |
args |
Object |
▸ 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");
Name | Type | Description |
---|---|---|
value? |
LogicalOperators | GeneralComparisonOperators | TextCompareType | ColorCompareType | DateCompareType |
The rule compare type. |
any
If no value is set, returns the rule compare type; otherwise, returns the condition.
▸ evaluate(evaluator
, baseRow
, baseColumn
, actualObj
): boolean
Evaluates the condition using the specified evaluator.
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. |
boolean
▸ 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));
Name | Type | Description |
---|---|---|
value? |
any |
The expected value. |
any
If no value is set, returns the expected value; otherwise, returns the condition.
▸ 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);
Name | Type | Description |
---|---|---|
formulaOrBaseRow? |
string | number |
The expected formula or base row. |
baseColumn? |
number |
The base column. |
any
If no value is set or baseRow and baseColumn is set, returns the expected formula; otherwise, returns the condition.
▸ getExpected(evaluator
, baseRow
, baseColumn
): Object
Gets the expected value.
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. |
Object
The expected value.
▸ getValidList(evaluator
, baseRow
, baseColumn
): any
[]
Returns the list of valid data items.
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. |
any
[]
The list of valid data items.
▸ 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");
Name | Type | Description |
---|---|---|
value? |
boolean |
Whether to ignore the blank cell. |
any
If no value is set, returns whether to ignore the blank cell; otherwise, returns the condition.
▸ 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");
Name | Type | Description |
---|---|---|
value? |
boolean |
Whether to ignore case when performing the comparison. |
any
If no value is set, returns whether to ignore case when performing the comparison; otherwise, returns the condition.
▸ 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));
Name | Type | Description |
---|---|---|
value? |
Condition |
The first condition. |
If no value is set, returns the first condition; otherwise, returns the relation condition.
▸ 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));
Name | Type | Description |
---|---|---|
value? |
Condition |
The second condition. |
If no value is set, returns the second condition; otherwise, returns the relation condition.
▸ 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));
Name | Type | Description |
---|---|---|
value? |
boolean |
Indicates compare whole day or precise date time. |
any
If no value is set, returns compare whole day or precise date time; otherwise, returns the data validator.
▸ 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);
Name | Type | Description |
---|---|---|
value? |
Range [] |
The condition ranges. |
any
If no value is set, returns the condition ranges; otherwise, returns the condition.
▸ 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();
});
void
▸ 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);
Name | Type | Description |
---|---|---|
value? |
boolean |
Whether to treat the null value in a cell as zero. |
any
If no value is set, returns whether to treat the null value in a cell as zero; otherwise, returns the condition.
▸ 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");
Name | Type | Description |
---|---|---|
value? |
boolean |
Whether to compare strings using wildcards. |
any
If no value is set, returns whether to compare strings using wildcards; otherwise, returns the condition.
▸ Static
fromDay(day
): Condition
Creates a date extend condition object from the specified day.
static
Name | Type | Description |
---|---|---|
day |
number |
The day. |
A date extend condition object.
▸ Static
fromFormula(formula
): Condition
Creates the area condition from formula data.
static
Name | Type | Description |
---|---|---|
formula |
string |
The formula that specifies a range that contains data items. |
The area condition.
▸ Static
fromMonth(month
): Condition
Creates a date extend condition object from the specified month.
static
Name | Type | Description |
---|---|---|
month |
number |
The month. The first month is 0. |
A date extend condition object.
▸ Static
fromQuarter(quarter
): Condition
Creates a date extend condition object from the specified quarter.
static
Name | Type | Description |
---|---|---|
quarter |
QuarterType |
The quarter. |
A date extend condition object.
▸ Static
fromSource(expected
): Condition
Creates the area condition from source data.
static
Name | Type | Description |
---|---|---|
expected |
string |
The expected source that separates each data item with a comma (","). |
The area condition.
▸ Static
fromWeek(week
): Condition
Creates a date extend condition object from the specified week.
static
Name | Type | Description |
---|---|---|
week |
number |
The week. |
A date extend condition object.
▸ Static
fromYear(year
): Condition
Creates a date extend condition object from the specified year.
static
Name | Type | Description |
---|---|---|
year |
number |
The year. |
A date extend condition object.