Using Data Validation

You can 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. Set the highlightInvalidData property to use the red ellipse as an invalid data image. The following image displays a red ellipse since the cell value is invalid.

You can also show an input tip for the user as in the following image.

Select the drop-down button to display the list of valid values for a list type validator.

You can use any of several types of validator methods to create the validation criteria.

The formula validator is valid if the formula condition returns true. The formula list validator uses a range of cells to create the list of valid values.

The ValidationError event occurs when the applied cell value is invalid.

Using Code

This example creates a list of valid values, displays an input tip, and displays an invalid data image if the incorrect value is entered.

JavaScript	Copy Code
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));

Using Code

This example uses a number validator, displays an input tip, and displays an invalid data image if the incorrect value is entered.

JavaScript	Copy Code
spread.options.highlightInvalidData = true; var dv = GC.Spread.Sheets.DataValidation.createNumberValidator(GC.Spread.Sheets.ConditionalFormatting.ComparisonOperators.between, "5", "20", true); dv.showInputMessage(true); dv.inputMessage("Value must be between 5 and 20."); dv.inputTitle("tip"); activeSheet.setDataValidator(1, 1, 1, 1, dv, GC.Spread.Sheets.SheetArea.viewport);

Using Code

This example uses a text validator, displays an input tip, and displays an invalid data image if the incorrect value is entered.

JavaScript	Copy Code
spread.options.highlightInvalidData = true; var dv = GC.Spread.Sheets.DataValidation.createTextLengthValidator(GC.Spread.Sheets.ConditionalFormatting.ComparisonOperators.greaterThan, "4", "20"); dv.showInputMessage(true); dv.inputMessage("Number of characters must be greater than 4."); dv.inputTitle("tip"); activeSheet.setDataValidator(1, 1, 1, 1, dv, GC.Spread.Sheets.SheetArea.viewport);

Using Code

This example creates a list of valid values based on a range of cells specified by a formula.

JavaScript	Copy Code
activeSheet.setValue(0, 2, 5); activeSheet.setValue(1, 2, 4); activeSheet.setValue(2, 2, 5); spread.options.highlightInvalidData = true; var dv = GC.Spread.Sheets.DataValidation.createFormulaListValidator("$C$1:$C$3"); dv.showInputMessage(true); dv.inputMessage("Pick a value from the list."); dv.inputTitle("tip"); activeSheet.setDataValidator(1, 1, 1, 1, dv, GC.Spread.Sheets.SheetArea.viewport); var validList = activeSheet.getDataValidator(1, 1).getValidList(sheet, 1, 1);

JavaScript

Copy Code

activeSheet.setValue(0, 2, 5);
activeSheet.setValue(1, 2, 4);
activeSheet.setValue(2, 2, 5);

spread.options.highlightInvalidData = true;
var dv = GC.Spread.Sheets.DataValidation.createFormulaListValidator("$C$1:$C$3");
dv.showInputMessage(true);
dv.inputMessage("Pick a value from the list.");
dv.inputTitle("tip");
activeSheet.setDataValidator(1, 1, 1, 1, dv, GC.Spread.Sheets.SheetArea.viewport);
var validList = activeSheet.getDataValidator(1, 1).getValidList(sheet, 1, 1);

Using Code

This example creates a date validator.

JavaScript	Copy Code
spread.options.highlightInvalidData = true; var dv = GC.Spread.Sheets.DataValidation.createDateValidator(GC.Spread.Sheets.ConditionalFormatting.ComparisonOperators.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, 1, 1, dv, GC.Spread.Sheets.SheetArea.viewport);

JavaScript

Copy Code

spread.options.highlightInvalidData = true;
var dv = GC.Spread.Sheets.DataValidation.createDateValidator(GC.Spread.Sheets.ConditionalFormatting.ComparisonOperators.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, 1, 1, dv, GC.Spread.Sheets.SheetArea.viewport);

Using Code

This example creates a formula validator.

JavaScript	Copy Code
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"); activeSheet.setDataValidator(0, 0, 1, 1, dv, GC.Spread.Sheets.SheetArea.viewport);