How To Validate Data Entry in SpreadJS

Here are some common scenarios that may sound familiar to you:

• Only numbers 5 through 10 can be entered into this cell
• This cell can only accept date that is later than the last cell
• Entered text should be less than 5 characters length
• Value of this cell should be value of one cell in the first column
• ...

Did you know that you can do all of these in SpreadJS? SpreadJS provides data validation similar to Excel, to control the type of data or the value that users enter into a cell. There are many types of built in -data validators inside SpreadJS that you mayuse to validate data entry in the cell. First of all you need to create a data validator by static methods of DefaultDataValidator class, and set the data validator by the dataValidator method of the Cell class to the cell which need data validation. DefaultDataValidator class provide 6 type of data validator, easy to create different criteria validator for your variety requirement. They are:

• Date Validator, uses to validate date or time type value.
• Number Validator, uses to validate number type value.
• List Validator, uses to validate the entered value should be in a list.
• Text Length Validator, uses to validate length of entered text.
• Formula Validator, uses to validate result which calculated by a formula.
• Formula List Validator, uses to validate the entered value should be in a list which is result of a formula.

Here is a sample, create a number validator to check entered value should be between 5 and 10.

var dv = $.wijmo.wijspread.DefaultDataValidator.createNumberValidator($.wijmo.wijspread.ComparisonOperator.Between, 5, 10, true);
dv.errorMessage = "value should be number and between 5 and 10";
sheet.getCell(1, 1).dataValidator(dv);

The second, add code in ValidationError event to process result of validation. This event will be fired when a cell is ending the editing if the entered value is invalid. In event arguments, you can get sheet object which the cell belong to, row index and column index of the cell, validator of the cell, and validationResult this is a flag indicate how to process the invalid value, type of this flag is DataValidationResult, it has three options:

• ForceApply (default): Indicates the invalid value will be accepted, and save in the cell
• Discard: Indicates the invalid value will be discard, and original value will be restore in the cell
• Retry: Indicates the editing will not be end until user enter a valid value

spread.bind($.wijmo.wijspread.Events.ValidationError, function (event, data) {
var dv = data.validator;
if (dv) {
alert(dv.errorMessage);
if(data.row==0 && data.col==1) {
// finish editing, and restor the original value
data.validationResult=$.wijmo.wijspread.DataValidationResult.Discard;
} else if(data.row==1 && data.col==1) {
// keep editing until enter a valid value
data.validationResult=$.wijmo.wijspread.DataValidationResult.Retry;
}
}
});

You can try the sample page, to learn more about getting started with SpreadJS in a web page, please read Quick Start Guide to Using SpreadJS. For more information about how to use SpreadJS, includes its data model and API, please read our online documentation or try online demos.