SpreadJS Validation

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