Spreadsheet validations
Data validations in Jspreadsheet enable the creation of rules for the data entered in the grid, ensuring that users input valid or expected data types.Upgrading from version 9
Starting from JSS version 10, the validations feature can be used without the validation extension. The extension is now limited to providing a no-code modal where users can create their own rules. Also, you can use column range such as Sheet1!E:E
Documentation
Methods
Below are the methods available to set or retrieve validations for one or multiple cells in Jspreadsheet:Method | Description |
---|---|
getValidations | Get validations by index.
@param {number} index - validation index. getValidations(index: Number) => Object
|
setValidations | Add or edit a new validation.
@param {object} - Array with the validations. setValidations(validations: Validations[]) => void
|
resetValidations | Add or edit a new validation.
@param {array} - Array with the validation indexes to be reset. Null to delete all validations. resetValidations(validations?: Array) => void
|
loadValidations | Get all validation rules applicable for one cell based on its coordinates.
@param {number} - x @param {number} - y loadValidations(x: Number, y: Number) => Object[]
|
hasErrors | This method returns true if a cell fails to meet all the defined validation criteria. If invoked without arguments, this method will scan the entire worksheet and return true if it detects validation errors.
@param {number?|string?} - x | column name | undefined @param {number?} - y | undefined hasErrors(col?: Number, row?: Number) => Boolean
|
Events
Events related to validations.Method | Description |
---|---|
onvalidation | onvalidation(worksheet: worksheetInstance, records: Validations[]) => void |
Validations[]
All available properties to define a validationProperty | Description |
---|---|
index: number | Index of an array of validations. |
value: Validation[] | Array of validation objects |
Validation object
Property | Description |
---|---|
range: string | A cell or a range of cells affect by the validation rules. Example: Sheet1!A1:A8 or a whole column as Sheet1!E:E |
type: string | number | text | date | list | textLength | empty | notEmpty |
Action: string | warning | reject | format |
criteria: string | '=' | '!=' | '>=' | '>' | '<=' | '<' | 'between | 'not between' | 'valid date' | 'valid email' | 'valid url' | 'contains' | 'not contains' | 'begins with' | 'ends with' |
text: string | Define the warning or reject message. |
allowBlank: boolean | Allow blank values. Only valid for warning messages |
format: object | color, background-color, font-weight, font-style. |
className: string | Class name to be added to the cell when the condition is match. |
Examples
Basic data grid with validations
You can define the data grid or spreadsheet validations during initialization or through programmatic methods.JavaScript example
<html> <script src="https://jspreadsheet.com/v10/jspreadsheet.js"></script> <link rel="stylesheet" href="https://jspreadsheet.com/v10/jspreadsheet.css" type="text/css" /> <script src="https://jsuites.net/v5/jsuites.js"></script> <link rel="stylesheet" href="https://jsuites.net/v5/jsuites.css" type="text/css" /> <link rel="stylesheet" href="https://fonts.googleapis.com/css?family=Material+Icons" /> <div id="spreadsheet"></div> <input type="button" value="Add new validation" onclick="create()"> <input type="button" value="Remove validation" onclick="remove()"> <script> // Set the license for both plugin and the spreadsheet jspreadsheet.setLicense('Y2UxNGVhMWJlM2QwNWVkNmM2MmM3ZTBhNTkwMjNiNWY4Y2E5ZDZhMTFjNDc3MWEwMGM4NjI3OTM2Y2FlZWQxMTZhMzE2YWZiN2E1OTRmZTVlM2FmY2ZmZjQ5OTAxMmZkMTU2OGE0YzViYjk2MGE1NzIzMjNlNTE3ODI4YjEyY2MsZXlKdVlXMWxJam9pU25Od2NtVmhaSE5vWldWMElpd2laR0YwWlNJNk1UWTROVGczT0RRMU1Td2laRzl0WVdsdUlqcGJJbXB6Y0hKbFlXUnphR1ZsZEM1amIyMGlMQ0pqYjJSbGMyRnVaR0p2ZUM1cGJ5SXNJbXB6YUdWc2JDNXVaWFFpTENKamMySXVZWEJ3SWl3aWQyVmlJaXdpYkc5allXeG9iM04wSWwwc0luQnNZVzRpT2lJek5DSXNJbk5qYjNCbElqcGJJblkzSWl3aWRqZ2lMQ0oyT1NJc0luWXhNQ0lzSW1Ob1lYSjBjeUlzSW1admNtMXpJaXdpWm05eWJYVnNZU0lzSW5CaGNuTmxjaUlzSW5KbGJtUmxjaUlzSW1OdmJXMWxiblJ6SWl3aWFXMXdiM0owSWl3aVltRnlJaXdpZG1Gc2FXUmhkR2x2Ym5NaUxDSnpaV0Z5WTJnaUxDSndjbWx1ZENJc0luTm9aV1YwY3lKZExDSmtaVzF2SWpwMGNuVmxmUT09'); // Create the spreadsheet let spreadsheet = jspreadsheet(document.getElementById('spreadsheet'), { worksheets: [{ data: [ [10,"=A1*2"], [20,"=A2*2"], [30,"=A3*2"], [40,"=A4*2"], [50,"=A5*2"] ], minDimensions: [6, 6], }], validations: [{ range: 'Sheet1!A1:A6', action: "warning", criteria: "between", type: "number", allowBlank: false, value: [10, 30], }] }); const create = function() { spreadsheet[0].setValidations([{ index: 1, value: { range: 'Sheet1!B1:B3', action: "format", criteria: "<", type: "number", value: [500], format: { color: '#ff0000' }, } }]); } const remove = function() { // Remove the validation by the index of the array spreadsheet[0].parent.config.validations spreadsheet[0].resetValidations([1]); } </script> </html>
React code example
import React, { useRef } from "react"; import { Spreadsheet, Worksheet } from "@jspreadsheet/react"; import jspreadsheet from "jspreadsheet"; const license = 'Y2UxNGVhMWJlM2QwNWVkNmM2MmM3ZTBhNTkwMjNiNWY4Y2E5ZDZhMTFjNDc3MWEwMGM4NjI3OTM2Y2FlZWQxMTZhMzE2YWZiN2E1OTRmZTVlM2FmY2ZmZjQ5OTAxMmZkMTU2OGE0YzViYjk2MGE1NzIzMjNlNTE3ODI4YjEyY2MsZXlKdVlXMWxJam9pU25Od2NtVmhaSE5vWldWMElpd2laR0YwWlNJNk1UWTROVGczT0RRMU1Td2laRzl0WVdsdUlqcGJJbXB6Y0hKbFlXUnphR1ZsZEM1amIyMGlMQ0pqYjJSbGMyRnVaR0p2ZUM1cGJ5SXNJbXB6YUdWc2JDNXVaWFFpTENKamMySXVZWEJ3SWl3aWQyVmlJaXdpYkc5allXeG9iM04wSWwwc0luQnNZVzRpT2lJek5DSXNJbk5qYjNCbElqcGJJblkzSWl3aWRqZ2lMQ0oyT1NJc0luWXhNQ0lzSW1Ob1lYSjBjeUlzSW1admNtMXpJaXdpWm05eWJYVnNZU0lzSW5CaGNuTmxjaUlzSW5KbGJtUmxjaUlzSW1OdmJXMWxiblJ6SWl3aWFXMXdiM0owSWl3aVltRnlJaXdpZG1Gc2FXUmhkR2x2Ym5NaUxDSnpaV0Z5WTJnaUxDSndjbWx1ZENJc0luTm9aV1YwY3lKZExDSmtaVzF2SWpwMGNuVmxmUT09'; // Example on how to create a new validation on React const create = function(worksheet) { worksheet.setValidations([{ index: 1, value: { range: 'Sheet1!B1:B3', action: "format", criteria: "<", type: "number", value: [500], format: { color: '#ff0000' }, } }]); } const remove = function(worksheet) { // Remove the validation by the index of the array spreadsheet[0].parent.config.validations worksheet.resetValidations([1]); } export default function App() { // Spreadsheet array of worksheets const spreadsheet = useRef(); // Data const data = [ [10,"=A1*2"], [20,"=A2*2"], [30,"=A3*2"], [40,"=A4*2"], [50,"=A5*2"] ] // Validations const validations: [ { range: 'Sheet1!A1:A6', action: "warning", criteria: "between", type: "number", allowBlank: false, value: [10, 30], } ] // Render component return ( <> <Spreadsheet ref={spreadsheet} license={license} validations={validations}> <Worksheet data={data} minDimensions={[6,6]} /> </Spreadsheet> <input type="button" value="Add new validation" onClick={() => create(spreadsheet.current[0])} /> <input type="button" value="Remove validation" onClick={() => remove(spreadsheet.current[0])} /> </> ); }
Vue example
<template> <Spreadsheet ref="spreadsheet" :license="license" :validations="validations"> <Worksheet :data="data" /> </Spreadsheet> <input type="button" value="Add new validation" @click="create" /> <input type="button" value="Remove validation" @click="remove" /> </template> <script> import { Spreadsheet, Worksheet } from "@jspreadsheet/vue"; const license = 'Y2UxNGVhMWJlM2QwNWVkNmM2MmM3ZTBhNTkwMjNiNWY4Y2E5ZDZhMTFjNDc3MWEwMGM4NjI3OTM2Y2FlZWQxMTZhMzE2YWZiN2E1OTRmZTVlM2FmY2ZmZjQ5OTAxMmZkMTU2OGE0YzViYjk2MGE1NzIzMjNlNTE3ODI4YjEyY2MsZXlKdVlXMWxJam9pU25Od2NtVmhaSE5vWldWMElpd2laR0YwWlNJNk1UWTROVGczT0RRMU1Td2laRzl0WVdsdUlqcGJJbXB6Y0hKbFlXUnphR1ZsZEM1amIyMGlMQ0pqYjJSbGMyRnVaR0p2ZUM1cGJ5SXNJbXB6YUdWc2JDNXVaWFFpTENKamMySXVZWEJ3SWl3aWQyVmlJaXdpYkc5allXeG9iM04wSWwwc0luQnNZVzRpT2lJek5DSXNJbk5qYjNCbElqcGJJblkzSWl3aWRqZ2lMQ0oyT1NJc0luWXhNQ0lzSW1Ob1lYSjBjeUlzSW1admNtMXpJaXdpWm05eWJYVnNZU0lzSW5CaGNuTmxjaUlzSW5KbGJtUmxjaUlzSW1OdmJXMWxiblJ6SWl3aWFXMXdiM0owSWl3aVltRnlJaXdpZG1Gc2FXUmhkR2x2Ym5NaUxDSnpaV0Z5WTJnaUxDSndjbWx1ZENJc0luTm9aV1YwY3lKZExDSmtaVzF2SWpwMGNuVmxmUT09'; export default { components: { Spreadsheet, Worksheet, }, methods: { create() { // Get the first worksheet instance let worksheet = this.$refs.spreadsheet.current[0]; // Add a new validation to the Sheet1 worksheet.setValidations([{ index: 1, value: { range: 'Sheet1!B1:B3', action: "format", criteria: "<", type: "number", value: [500], format: { color: '#ff0000' }, } }]); }, remove() { // Get the first worksheet instance let worksheet = this.$refs.spreadsheet.current[0]; // Destroy the validations rules index one. worksheet.resetValidations([1]); }, }, data() { // Data const data = [ [10,"=A1*2"], [20,"=A2*2"], [30,"=A3*2"], [40,"=A4*2"], [50,"=A5*2"] ]; // Validations const validations: [ { range: 'Sheet1!A1:A6', action: "warning", criteria: "between", type: "number", allowBlank: false, value: [10, 30], } ]; return { data, validations, license, }; } } </script>
Angular example
import { Component, ViewChild, ElementRef } from "@angular/core"; import * as jspreadsheet from "jspreadsheet"; // Set your JSS license key (The following key only works for one day) jspreadsheet.setLicense('Y2UxNGVhMWJlM2QwNWVkNmM2MmM3ZTBhNTkwMjNiNWY4Y2E5ZDZhMTFjNDc3MWEwMGM4NjI3OTM2Y2FlZWQxMTZhMzE2YWZiN2E1OTRmZTVlM2FmY2ZmZjQ5OTAxMmZkMTU2OGE0YzViYjk2MGE1NzIzMjNlNTE3ODI4YjEyY2MsZXlKdVlXMWxJam9pU25Od2NtVmhaSE5vWldWMElpd2laR0YwWlNJNk1UWTROVGczT0RRMU1Td2laRzl0WVdsdUlqcGJJbXB6Y0hKbFlXUnphR1ZsZEM1amIyMGlMQ0pqYjJSbGMyRnVaR0p2ZUM1cGJ5SXNJbXB6YUdWc2JDNXVaWFFpTENKamMySXVZWEJ3SWl3aWQyVmlJaXdpYkc5allXeG9iM04wSWwwc0luQnNZVzRpT2lJek5DSXNJbk5qYjNCbElqcGJJblkzSWl3aWRqZ2lMQ0oyT1NJc0luWXhNQ0lzSW1Ob1lYSjBjeUlzSW1admNtMXpJaXdpWm05eWJYVnNZU0lzSW5CaGNuTmxjaUlzSW5KbGJtUmxjaUlzSW1OdmJXMWxiblJ6SWl3aWFXMXdiM0owSWl3aVltRnlJaXdpZG1Gc2FXUmhkR2x2Ym5NaUxDSnpaV0Z5WTJnaUxDSndjbWx1ZENJc0luTm9aV1YwY3lKZExDSmtaVzF2SWpwMGNuVmxmUT09'); @Component({ selector: "app-root", template: ` <div #spreadsheet></div> <input type="button" value="Add new validation" (click)="create()" /> <input type="button" value="Remove validation" (click)="remove()" />` }) export class AppComponent { @ViewChild("spreadsheet") spreadsheet: ElementRef; // Worksheets worksheets: jspreadsheet.worksheetInstance[]; // Create a new data grid ngOnInit() { // Create spreadsheet this.worksheets = jspreadsheet(this.spreadsheet.nativeElement, { worksheets: [{ data: [ [10,"=A1*2"], [20,"=A2*2"], [30,"=A3*2"], [40,"=A4*2"], [50,"=A5*2"] ], minDimensions: [6, 6], }], validations: [{ range: 'Sheet1!A1:A6', action: "warning", criteria: "between", type: "number", allowBlank: false, value: [10, 30], }] }); } create() { // Create or update the validation on position one in the array of validations this.worksheets[0].setValidations([{ index: 1, value: { range: 'Sheet1!B1:B3', action: "format", criteria: "<", type: "number", value: [500], format: { color: '#ff0000' }, } }]); } remove() { // Remove the validation by the index this.worksheets[0].resetValidations([1]); } }