[]
Sheets.DataValidation.DefaultDataValidator
• new DefaultDataValidator(condition?
)
Represents a data validator.
example
//This example validates the cell data.
spread.options.highlightInvalidData = true;
var dv = GC.Spread.Sheets.DataValidation.createTextLengthValidator(GC.Spread.Sheets.ConditionalFormatting.ComparisonOperators.greaterThan, 5);
activeSheet.setDataValidator(0, 0, 1, 1, dv, GC.Spread.Sheets.SheetArea.viewport);
activeSheet.setValue(0, 0, "abcf");
Name | Type |
---|---|
condition? |
Condition |
▸ comparisonOperator(value?
): any
Gets or sets the comparison operator.
Name | Type |
---|---|
value? |
ComparisonOperators |
any
If no value is set, returns the comparison operator; otherwise, returns the data validator.
▸ condition(value?
): any
Gets or sets the condition to validate.
Name | Type |
---|---|
value? |
Condition |
any
If no value is set, returns the condition to validate; otherwise, returns the data validator.
▸ errorMessage(value?
): any
Gets or sets the error message.
Name | Type |
---|---|
value? |
string |
any
If no value is set, returns the error message; otherwise, returns the data validator.
▸ errorStyle(value?
): any
Gets or sets the error style to display.
Name | Type |
---|---|
value? |
ErrorStyle |
any
If no value is set, returns the error style to display; otherwise, returns the data validator.
▸ errorTitle(value?
): any
Gets or sets the error title.
Name | Type |
---|---|
value? |
string |
any
If no value is set, returns the error title; otherwise, returns the data validator.
▸ getValidList(evaluator
, baseRow
, baseColumn
): any
[]
Returns the valid data lists if the Data validation type is list; otherwise, returns null.
Name | Type | Description |
---|---|---|
evaluator |
Object |
The object that can evaluate a condition. |
baseRow |
number |
The base row. |
baseColumn |
number |
The base column. |
any
[]
The valid data lists or null.
▸ highlightStyle(style?
): any
Get or Sets the invalid data cell highlight style.
example
//This example uses the highlightStyle method.
sheet.setValue(1, 1, "sss");
var dv = GC.Spread.Sheets.DataValidation.createListValidator('Fruit,Vegetable,Food');
dv.highlightStyle({
type:GC.Spread.Sheets.DataValidation.HighlightType.dogEar,
color:'blue',
position:GC.Spread.Sheets.DataValidation.HighlightPosition.topLeft
});
sheet.setDataValidator(1,1, dv);
spread.options.highlightInvalidData = true;
Name | Type |
---|---|
style? |
IHighLightStyle |
any
If no value is set, returns the hignlight style object; otherwise, returns the data validator.
▸ ignoreBlank(value?
): any
Gets or sets whether to ignore an empty value.
example
//This example uses the IgnoreBlank 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 |
---|---|
value? |
boolean |
any
If no value is set, returns whether to ignore the empty value; otherwise, returns the data validator.
▸ inCellDropdown(value?
): any
Gets or sets whether to display a drop-down button.
example
//This example uses the inCellDropdown method.
spread.options.highlightInvalidData = true;
var dv = GC.Spread.Sheets.DataValidation.createListValidator("1,2,3");
dv.showInputMessage(true);
dv.inputMessage("Value must be 1,2 or 3");
dv.inputTitle("tip");
dv.inCellDropdown(true);
activeSheet.setDataValidator(1,1,1,1,dv,GC.Spread.Sheets.SheetArea.viewport);
var validList = activeSheet.getDataValidator(1, 1).getValidList(activeSheet, 1, 1);
alert(validList);
Name | Type |
---|---|
value? |
boolean |
any
If no value is set, returns whether to display a drop-down button; otherwise, returns the data validator.
▸ inputMessage(value?
): any
Gets or sets the input message.
example
spread.options.highlightInvalidData = true;
var dv = GC.Spread.Sheets.DataValidation.createListValidator("1,2,3");
dv.showInputMessage(true);
dv.inputMessage("Value must be 1,2 or 3");
dv.inputTitle("tip");
activeSheet.setDataValidator(1,1,1,1,dv,GC.Spread.Sheets.SheetArea.viewport);
alert(activeSheet.getDataValidator(1,1).getValidList(activeSheet,1,1));
Name | Type |
---|---|
value? |
string |
any
If no value is set, returns the input message; otherwise, returns the data validator.
▸ inputTitle(value?
): any
Gets or sets the input title.
example
spread.options.highlightInvalidData = true;
var dv = GC.Spread.Sheets.DataValidation.createListValidator("1,2,3");
dv.showInputMessage(true);
dv.inputMessage("Value must be 1,2 or 3");
dv.inputTitle("tip");
activeSheet.setDataValidator(1,1,1,1,dv,GC.Spread.Sheets.SheetArea.viewport);
alert(activeSheet.getDataValidator(1,1).getValidList(activeSheet,1,1));
Name | Type |
---|---|
value? |
string |
any
If no value is set, returns the input title; otherwise, returns the data validator.
▸ isValid(evaluator
, baseRow
, baseColumn
, actual
): boolean
Determines whether the current value is valid.
example
sheet.setArray(0, 0,
[
[ 3.4 ],
[ 102.8 ]
]);
var expression1 = 1.1;
var expression2 = 101.2;
var dv = GC.Spread.Sheets.DataValidation.createNumberValidator(GC.Spread.Sheets.ConditionalFormatting.ComparisonOperators.between, expression1, expression2, false);
sheet.getCell(0, 0, GC.Spread.Sheets.SheetArea.viewport).validator(dv);
dv = sheet.getCell(0, 0, GC.Spread.Sheets.SheetArea.viewport).validator(); // Limitation of SDM, the dv is copied when set to style.
console.log(dv.isValid(sheet, 0, 0, 3)); // true
console.log(dv.isValid(sheet, 0, 0, 1)); // false
console.log(dv.isValid(sheet, 0, 0, 101)); // true
console.log(dv.isValid(sheet, 0, 0, 0)); // false
console.log(dv.isValid(sheet, 0, 0, 120.0)); // false
Name | Type | Description |
---|---|---|
evaluator |
Object |
The evaluator. |
baseRow |
number |
The base row. |
baseColumn |
number |
The base column. |
actual |
Object |
The current value. |
boolean
true
if the value is valid; otherwise, false
.
▸ 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));
Name | Type |
---|---|
value? |
boolean |
any
If no value is set, returns compare whole day or precise date time; otherwise, returns the data validator.
▸ reset(): void
Resets the data validator.
example
//This example uses the reset 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);
validator.reset();
void
▸ showErrorMessage(value?
): any
Gets or sets whether to display an error message.
example
spread.options.highlightInvalidData = true;
//The formula validator is valid if the formula condition returns true.
var dv = GC.Spread.Sheets.DataValidation.createFormulaValidator("A1>0");
dv.showInputMessage(true);
dv.inputMessage("Enter a value greater than 0 in A1.");
dv.inputTitle("Tip");
dv.showErrorMessage(true);
dv.errorMessage("Incorrect Value");
activeSheet.setDataValidator(0, 0, 1, 1, dv, GC.Spread.Sheets.SheetArea.viewport);
//bind
activeSheet.bind(GC.Spread.Sheets.Events.ValidationError, function (sender, args) {
if (args.validator.showErrorMessage()) {
if (confirm(args.validator.errorMessage())) {
args.validationResult = GC.Spread.Sheets.DataValidation.DataValidationResult.retry;
} else {
args.validationResult = GC.Spread.Sheets.DataValidation.DataValidationResult.forceApply;
}
}
});
Name | Type |
---|---|
value? |
boolean |
any
If no value is set, returns whether to display an error message; otherwise, returns the data validator.
▸ showInputMessage(value?
): any
Gets or sets whether to display the input title and input message.
example
spread.options.highlightInvalidData = true;
var dv = GC.Spread.Sheets.DataValidation.createListValidator("1,2,3");
dv.showInputMessage(true);
dv.inputMessage("Value must be 1,2 or 3");
dv.inputTitle("tip");
activeSheet.setDataValidator(1,1,1,1,dv,GC.Spread.Sheets.SheetArea.viewport);
alert(activeSheet.getDataValidator(1,1).getValidList(activeSheet,1,1));
Name | Type |
---|---|
value? |
boolean |
any
If no value is set, returns whether to display the input title and input message; otherwise, returns the data validator.
▸ type(value?
): any
Gets or sets the criteria type of this data validator.
Name | Type |
---|---|
value? |
CriteriaType |
any
If no value is set, returns the criteria type of this data validator; otherwise, returns the data validator.
▸ value1(baseRow?
, baseColumn?
): any
Gets the first value of the data validation.
example
//This example validates a cell value.
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);
alert(validator.value1());
Name | Type |
---|---|
baseRow? |
number |
baseColumn? |
number |
any
The first value.
▸ value2(baseRow?
, baseColumn?
): any
Gets the second value of the data validation.
example
//This example validates a cell value.
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);
alert(validator.value2());
Name | Type |
---|---|
baseRow? |
number |
baseColumn? |
number |
any
The second value.