类: Condition

Sheets.ConditionalFormatting.Condition

Table of contents

构造方法

• constructor

方法

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

构造方法

constructor

• new Condition(conditionType, args)

使用参数对象表示条件项

代码示例

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);

参数

属性名	类型
conditionType	ConditionType
args	Object

方法

compareType

▸ compareType(value?): any

获取或设置规则比较类型

代码示例

 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;

参数

属性名	类型	说明
value?	LogicalOperators | GeneralComparisonOperators | TextCompareType | ColorCompareType | DateCompareType	规则比较类型

返回值

any

如果未设置任何值，则返回规则比较类型否则，返回条件

---

evaluate

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

使用指定的计算器计算条件

代码示例

 //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);
 }

参数

属性名	类型	说明
evaluator	Object	可以计算表达式或函数的计算器
baseRow	number	用于计算的基行索引
baseColumn	number	用于计算的基列索引
actualObj	Object	用于计算的对象的实际值

返回值

boolean

如果结果成功，返回true;否则返回false

---

expected

▸ expected(value?): any

获取或设置期望值

代码示例

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));

参数

属性名	类型	说明
value?	any	期望值

返回值

any

如果未设置任何值，则返回期望值否则，返回条件

---

formula

▸ formula(formulaOrBaseRow?, baseColumn?): any

获取或设置期望的公式

代码示例

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"); // 公式用于计算数字
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");
//将值3设置为$C$1,此代码之后，单元格(0,0)中的值有效
activeSheet.setValue(0, 2, 3);
//将值5设置为$C$1,此代码之后，单元格(0,0)中的值无效
// activeSheet.setValue(0, 2, 5);

参数

属性名	类型	说明
formulaOrBaseRow?	string | number	预期的公式或基行
baseColumn?	number	基列

返回值

any

如果未设置任何值或设置基行和基列，则返回期望公式；否则，返回条件

---

getExpected

▸ getExpected(evaluator, baseRow, baseColumn): Object

获取期望值

参数

属性名	类型	说明
evaluator	Object	可以计算表达式或函数的计算器
baseRow	number	用于计算的基行索引
baseColumn	number	用于计算的基列索引

返回值

Object

期望值

---

getValidList

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

返回有效数据项的列表

参数

属性名	类型	说明
evaluator	Object	可以计算表达式或函数的计算器
baseRow	number	用于计算的基行索引
baseColumn	number	用于计算的基列索引

返回值

any[]

有效数据项的列表

---

ignoreBlank

▸ ignoreBlank(value?): any

获取或设置是否忽略空白单元格

代码示例

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");

参数

属性名	类型	说明
value?	boolean	是否忽略空白单元格

返回值

any

如果未设置任何值，则返回是否忽略空白单元格否则，返回条件

---

ignoreCase

▸ ignoreCase(value?): any

获取或设置执行比较时是否忽略大小写

代码示例

//本示例创建一个文本条件
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");

参数

属性名	类型	说明
value?	boolean	执行比较时是否忽略大小写

返回值

any

如果未设置任何值，则返回执行比较时是否忽略大小写；否则，返回条件

---

item1

▸ item1(value?): Condition

获取或设置第一个条件

代码示例

//本示例验证日期
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));

参数

属性名	类型	说明
value?	Condition	第一个条件

返回值

Condition

如果未设置任何值，则返回第一个条件否则，返回关系条件

---

item2

▸ item2(value?): Condition

获取或设置第二个条件

代码示例

//本示例验证日期
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));

参数

属性名	类型	说明
value?	Condition	第二个条件

返回值

Condition

如果未设置任何值，则返回第二个条件否则，返回关系条件

---

preciseCompareDate

▸ preciseCompareDate(value?): any

获取或设置是比较整天还是精确日期时间

代码示例

//本示例使用preciseCompareDate方法
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));
//当该选项为false时，验证会按天比较，并且它们是相同的，因此验证失败并显示红色警报
//如果该选项为true,则日期时间7点大于6点，因此结果是成功的
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));

参数

属性名	类型	说明
value?	boolean	比较整天或精确日期时间

返回值

any

如果未设置值，则返回比较全天或精确日期时间；否则，返回数据验证

---

ranges

▸ ranges(value?): any

获取或设置条件区域

代码示例

//本示例创建一个唯一条件
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);

参数

属性名	类型	说明
value?	Range[]	条件区域

返回值

any

如果未设置任何值，则返回条件区域否则，返回条件

---

reset

▸ reset(): void

重置此实例

代码示例

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);
//创建一个按钮
$("#button1").click(function () {
activeSheet.suspendPaint();
nCondition.reset();
activeSheet.resumePaint();
    });

返回值

void

---

treatNullValueAsZero

▸ treatNullValueAsZero(value?): any

获取或设置是否将单元格中的空值视为零

代码示例

//本示例设置treatNullValueAsZero方法
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);
//当选项为false时，验证失败并显示红色警报
//当选项为true时，将把空白单元格视为零，验证成功
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);

参数

属性名	类型	说明
value?	boolean	是否将单元格中的空值视为零

返回值

any

如果未设置任何值，则返回是否将单元格中的空值视为零；否则，返回条件

---

useWildCards

▸ useWildCards(value?): any

获取或设置是否使用通配符比较字符串

代码示例

//本示例允许使用通配符
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");

参数

属性名	类型	说明
value?	boolean	是否使用通配符比较字符串

返回值

any

如果未设置任何值，则返回是否使用通配符比较字符串；否则，返回条件

---

fromDay

▸ Static fromDay(day): Condition

从指定日期创建日期扩展条件对象

static

参数

属性名	类型	说明
day	number	日期

返回值

Condition

日期扩展条件对象

---

fromFormula

▸ Static fromFormula(formula): Condition

根据公式数据创建区域条件

static

参数

属性名	类型	说明
formula	string	包含数据项的区域的公式

返回值

Condition

区域条件

---

fromMonth

▸ Static fromMonth(month): Condition

从指定的月份创建日期扩展条件对象

static

参数

属性名	类型	说明
month	number	月份第一个月是0

返回值

Condition

日期扩展条件对象

---

fromQuarter

▸ Static fromQuarter(quarter): Condition

从指定的季度创建日期扩展条件对象

static

参数

属性名	类型	说明
quarter	QuarterType	季度

返回值

Condition

日期扩展条件对象

---

fromSource

▸ Static fromSource(expected): Condition

根据数据源创建区域条件

static

参数

属性名	类型	说明
expected	string	用逗号(",")分隔每个数据项的预期源

返回值

Condition

区域条件

---

fromWeek

▸ Static fromWeek(week): Condition

从指定的周创建日期扩展条件对象

static

参数

属性名	类型	说明
week	number	周

返回值

Condition

日期扩展条件对象

---

fromYear

▸ Static fromYear(year): Condition

从指定年份创建日期扩展条件对象

static

参数

属性名	类型	说明
year	number	年份

返回值

Condition

日期扩展条件对象