SpreadJS allows users to create validators in order to validate the data input and restrict the invalid information. You can display a list of valid values for the user and display an invalid data image as soon as a user enters invalid data in the cell.

You can validate the information entered in the speadsheets in the following ways -

• Using Different Types of Data Validation
• Using Different Highlight Styles for Data Validation

Using Different Types of Data Validation

SpreadJS provides support for the following types of data validation - 

• Date Validation
• Formula Validation
• Formula List Validation
• List Validation
• Number Validation
• Text Length Validation

Date Validation

The createDateValidator method can be used to validate a specific date criteria for a cell.

Date validation restricts users from entering invalid date information (that doesn't meet the specified date criteria for a particular cell) in the spreadsheet. For example, let's say you create a date validator to allow users to enter any date between 31st December,2017 and 31st December 2018. Any date entry outside this range will be considered as an invalid data entry.

Using Code

This example creates a date validator.

Formula Validation

The createFormulaValidator method can be used to validate the formula entered in a cell.

Using Code

This example creates a formula validator.

Formula List Validation

The createFormulaListValidator method can be used to validate the list of formulas in the spreadsheet. The formula list validator uses a range of cells to create the list of valid values.

The formula validator is valid if the formula condition returns true. The ValidationError event occurs when the applied cell value is invalid.

Using Code

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

List Validation

The createListValidator method creates a validator based on a list.

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.

SpreadJS also provides support for inserting commas (via escape characters) for each paragraph in the list as shown in the below image. The cell B2 represents a list of numbers and the cell D2 represents a list of operators.

 

Using Code

This example shows list validation using two different lists - one is a list of numbers and the other is a list of operators separated by commas. The two list validation criteria in different cells (B2 and D2) restrict users to choose from the values listed in the drop-down list while entering data in the cells.

Number Validation

The createNumberValidator method can be used to validate the number entered in a cell.

Number validation restricts users from entering the numeric values that fall outside the validation criteria specified for the cell.

Using Code

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

Text Length Validation

The createTextLengthValidator method can be used to validate the length of the text entered in a cell.

You can restrict the number of characters entered in a cell using the text length validation.

Using Code

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

Using Different Highlight Styles for Data Validation

You can indicate invalid information in a cell using different highlight styles including the highlight type, position, color and image. SpreadJS supports three different types of highlight types (circle, dog-ear and icon) and six different types of highlight positions (topLeft, topRight, bottomRight, bottomLeft, outsideLeft and outsideRight) in order to allow users to indicate invalid data in the spreadsheet. By default, the highlight type is "circle", highlight position is "topRight" and the highlight color is "red". For the image in the icon highlight style, you can either specify the image URL or image base64 data. 

Along with the different highlight styles, you can also show an input tip for the user and display a drop-down button that displays the list of valid values, as shown in the following images.

         

The highlightInvalidData property must be set to true in order to highlight the invalid information. The highlightStyle method can be used to control the style of the highlighted error with each type possessing a different style attribute. 

In the image shown below, the highlight type in cell B2 represents the circle style, cell D2 represents the dog-ear style and the cell F2 represents the icon style. Also, the highlight position for cell D2 is set to topLeft and cell F2 is set to outsideLeft.

 

Using Code

This example shows three different type of highlight styles - circle, dog-ear and icon for validating data in a spreadsheet.