[]
DEMOS DOC DESIGNER API
DOWNLOAD
(Showing Draft Content)

GC.Spread.Sheets.DataValidation.DefaultDataValidator

Class: DefaultDataValidator

Sheets.DataValidation.DefaultDataValidator

Table of contents

Constructors

Methods

Constructors

constructor

• new DefaultDataValidator(condition?)

Represents a data validator that defines rules for cell data validation. The DefaultDataValidator class allows you to specify validation criteria that restrict the type of data or the values that users can enter into a cell or range of cells. It supports various validation types including whole numbers, decimals, lists, dates, times, text length restrictions, and custom formulas.

example

// Example 1: 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, 2, 1, dv, GC.Spread.Sheets.SheetArea.viewport);
activeSheet.setValue(0, 0, "abc");
activeSheet.setValue(1, 0, "abcdef");

example

// Example 2: Create a whole number validator
var numberValidator = GC.Spread.Sheets.DataValidation.createNumberValidator(
    GC.Spread.Sheets.ConditionalFormatting.ComparisonOperators.between, 1, 100, true);
numberValidator.inputMessage("Enter a whole number between 1 and 100");
numberValidator.errorMessage("Value must be a whole number between 1 and 100");
activeSheet.setDataValidator(1, 0, 1, 1, numberValidator);

example

// Example 3: Create a list validator
var listValidator = GC.Spread.Sheets.DataValidation.createListValidator("Apple,Banana,Orange");
listValidator.showInputMessage(true);
listValidator.inputTitle("Fruit Selection");
listValidator.inputMessage("Please select a fruit from the dropdown list");
activeSheet.setDataValidator(2, 0, 1, 1, listValidator);

Parameters

Name Type
condition? Condition

Methods

comparisonOperator

▸ comparisonOperator(value?): any

Gets or sets the comparison operator.

example

// Example: Setting comparison operator for a number validator
var validator = GC.Spread.Sheets.DataValidation.createNumberValidator(
    GC.Spread.Sheets.ConditionalFormatting.ComparisonOperators.between, 1, 100);

// Get the current comparison operator
console.log(validator.comparisonOperator()); // Outputs: 6 (between)
// Apply to a cell
activeSheet.setDataValidator(0, 0, validator);

Parameters

Name Type
value? ComparisonOperators

Returns

any

If no value is set, returns the comparison operator; otherwise, returns the data validator.

condition

▸ condition(value?): any

Gets or sets the condition to validate.

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();
validator.type(GC.Spread.Sheets.DataValidation.CriteriaType.custom);
validator.condition(nCondition);
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
value? Condition

Returns

any

If no value is set, returns the condition to validate; otherwise, returns the data validator.

errorMessage

▸ errorMessage(value?): any

Gets or sets the error message.

example

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);
var validator = new GC.Spread.Sheets.DataValidation.DefaultDataValidator(nCondition);
validator.type(GC.Spread.Sheets.DataValidation.CriteriaType.custom);
validator.errorMessage('Incorrect input, please input a positive number');
activeSheet.getCell(0, 0, GC.Spread.Sheets.SheetArea.viewport).validator(validator);

Parameters

Name Type
value? string

Returns

any

If no value is set, returns the error message; otherwise, returns the data validator.

errorStyle

▸ errorStyle(value?): any

Gets or sets the error style to display.

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);
validator.errorStyle(GC.Spread.Sheets.DataValidation.ErrorStyle.warning);
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
value? ErrorStyle

Returns

any

If no value is set, returns the error style to display; otherwise, returns the data validator.

errorTitle

▸ errorTitle(value?): any

Gets or sets the error title.

example

spread.options.highlightInvalidData = true;
var dv = GC.Spread.Sheets.DataValidation.createListValidator("1,2,3");
dv.showErrorMessage(true);
dv.errorMessage("Value must be 1,2 or 3");
dv.errorTitle("Custom Error Title");
activeSheet.setDataValidator(1,1,1,1,dv,GC.Spread.Sheets.SheetArea.viewport);

Parameters

Name Type
value? string

Returns

any

If no value is set, returns the error title; otherwise, returns the data validator.

getValidList

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

Returns the valid data lists if the Data validation type is list; otherwise, returns null.

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

Parameters

Name Type Description
evaluator Object The object that can evaluate a condition.
baseRow number The base row.
baseColumn number The base column.

Returns

any[]

The valid data lists or null.

highlightStyle

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

Parameters

Name Type
style? IHighLightStyle

Returns

any

If no value is set, returns the hignlight style object; otherwise, returns the data validator.

ignoreBlank

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

Parameters

Name Type
value? boolean

Returns

any

If no value is set, returns whether to ignore the empty value; otherwise, returns the data validator.

inCellDropdown

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

Parameters

Name Type
value? boolean

Returns

any

If no value is set, returns whether to display a drop-down button; otherwise, returns the data validator.

inputMessage

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

Parameters

Name Type
value? string

Returns

any

If no value is set, returns the input message; otherwise, returns the data validator.

inputTitle

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

Parameters

Name Type
value? string

Returns

any

If no value is set, returns the input title; otherwise, returns the data validator.

isValid

▸ 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

Parameters

Name Type Description
evaluator Object The evaluator.
baseRow number The base row.
baseColumn number The base column.
actual Object The current value.

Returns

boolean

true if the value is valid; otherwise, false.

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
value? boolean

Returns

any

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

reset

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

Returns

void

showErrorMessage

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

Parameters

Name Type
value? boolean

Returns

any

If no value is set, returns whether to display an error message; otherwise, returns the data validator.

showInputMessage

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

Parameters

Name Type
value? boolean

Returns

any

If no value is set, returns whether to display the input title and input message; otherwise, returns the data validator.

type

▸ type(value?): any

Gets or sets the criteria type of this data validator.

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
value? CriteriaType

Returns

any

If no value is set, returns the criteria type of this data validator; otherwise, returns the data validator.

value1

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

Parameters

Name Type
baseRow? number
baseColumn? number

Returns

any

The first value.

value2

▸ value2(baseRow?, baseColumn?): any

Gets the second value of the data validation.

example

//This example demonstrates how to get the second value of a data validation.
spread.options.highlightInvalidData = true;
// Create a number validator with between operator (10 and 100)
var validator = GC.Spread.Sheets.DataValidation.createNumberValidator(
    GC.Spread.Sheets.ConditionalFormatting.ComparisonOperators.between,
    10,  // First value: minimum
    100  // Second value: maximum
);
activeSheet.getCell(0, 0, GC.Spread.Sheets.SheetArea.viewport).validator(validator);
activeSheet.setValue(0,0,5);
// Now value1() and value2() will return different values
console.log(validator.value2()); // Output: 100 (maximum value)

Parameters

Name Type
baseRow? number
baseColumn? number

Returns

any

The second value.