You can use data validation to control the type of data and values that users are allowed to enter in a cell. This can be useful if you only want to allow correct values in a cell based on your data. SpreadJS supports data validation. Create validators to validate the user data. You can display a list of valid values for the user and display an invalid data image if the user types invalid data. Use the highlightInvalidData method to specify a red ellipse as an invalid data image. You can use any of several types of validator methods to create the validation criteria.
- createDateValidator
- createFormulaValidator
- createFormulaListValidator
- createListValidator
- createNumberValidator
- createTextLengthValidator
The ValidationError event occurs when the applied cell value is invalid. You can also use the isValid method to check that a cell value is valid based on the validation. This example creates a list of valid values for the Category column. List Data JavaScript
var datasource = [
{ Name: "Apple", Category: "Fruit" },
{ Name: "Orange", Category: "Fruit" },
{ Name: "Broccoli", Category: "Vegetable" },
{ Name: "Kiwi", Category: "Fruit" },
{ Name: "Strawberry", Category: "Fruit" },
{ Name: "Yogurt", Category: "Dairy" },
{ Name: "Plum", Category: "Fruit" },
{ Name: "Pear", Category: "Cereal" },
{ Name: "Carrot", Category: "Vegetable" },
{ Name: "Cheese", Category: "Dairy" },
];
activeSheet.setDataSource(datasource);
activeSheet.getColumn(0).width(75);
activeSheet.getColumn(1).width(75);
spread.highlightInvalidData(true);
var dv = GcSpread.Sheets.DefaultDataValidator.createListValidator("Fruit,Vegetable,Dairy");
dv.showInputMessage = true;
dv.inputMessage = "Value must be Fruit, Vegetable, or Dairy.";
dv.inputTitle = "tip";
activeSheet.setDataValidator(-1, 1, dv);
This example uses a text validator, displays an input tip, and displays an invalid data image if the incorrect value is entered. Text Validator JavaScript
spread.highlightInvalidData(true);
var dv = GcSpread.Sheets.DefaultDataValidator.createTextLengthValidator(GcSpread.Sheets.ComparisonOperator.GreaterThan, "4", "20");
dv.showInputMessage = true;
dv.inputMessage = "Number of characters must be greater than 4.";
dv.inputTitle = "tip";
activeSheet.setDataValidator(1, 1, dv);
This example uses a number validator, displays an input tip, and displays an invalid data image if the incorrect value is entered. Number Validator JavaScript
spread.highlightInvalidData(true);
var dv = GcSpread.Sheets.DefaultDataValidator.createNumberValidator(GcSpread.Sheets.ComparisonOperator.Between, "5", "20", true);
dv.showInputMessage = true;
dv.inputMessage = "Value must be between 5 and 20.";
dv.inputTitle = "tip";
activeSheet.setDataValidator(1, 1, dv);
This example creates a date validator. Date Validator JavaScript
spread.highlightInvalidData(true);
var dv = GcSpread.Sheets.DefaultDataValidator.createDateValidator(GcSpread.Sheets.ComparisonOperator.Between, new Date(2012, 11, 31), new Date(2013, 11, 31));
dv.showInputMessage = true;
dv.inputMessage = "Enter a date between 12/31/2012 and 12/31/2013.";
dv.inputTitle = "Tip";
activeSheet.setDataValidator(1, 1, dv);
This example creates a formula validator. Formula Validator JavaScript
spread.highlightInvalidData(true);
//The formula validator is valid if the formula condition returns true.
var dv = GcSpread.Sheets.DefaultDataValidator.createFormulaValidator("A1>0");
dv.showInputMessage = true;
dv.inputMessage = "Enter a value greater than 0 in A1.";
dv.inputTitle = "Tip";
activeSheet.setDataValidator(0, 0, dv);
This example uses the isValid method. JavaScript
var nCondition = new GcSpread.Sheets.CellValueCondition();
nCondition.compareType = GcSpread.Sheets.GeneralCompareType.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 GcSpread.Sheets.DefaultDataValidator(nCondition)
validator.IgnoreBlank(false);
activeSheet.getCell(0, 0, GcSpread.Sheets.SheetArea.viewport).dataValidator(validator);
spread.highlightInvalidData(true);
activeSheet.setValue(0, 0, null);
alert(activeSheet.isValid(0, 0, 0));
This example uses the ValidationError event. JavaScript
var nCondition = new GcSpread.Sheets.CellValueCondition();
nCondition.compareType = GcSpread.Sheets.GeneralCompareType.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 GcSpread.Sheets.DefaultDataValidator(nCondition)
validator.IgnoreBlank(false);
activeSheet.getCell(0, 0, GcSpread.Sheets.SheetArea.viewport).dataValidator(validator);
spread.highlightInvalidData(true);
activeSheet.setValue(0, 0, null);
//Type different values in cell (0,0). This event fires if the user types an invalid value.
activeSheet.bind("ValidationError", vError);
function vError(sender, args) {
alert("error");
}