Validations
The validations extension in Jspreadsheet enhances data integrity by enabling users to manage data validations within their spreadsheets. This interface supports adding, updating, and removing validation rules, ensuring data entries meet predefined criteria and maintaining consistency across the data grid.
Documentation
For more information on how to set up the data grid with validations or to change them programmatically, please refer to the Validations Documentation.
Installation
Choose one of the following options:
Using NPM
$ npm install @jspreadsheet/validations Using a CDN
<script src="https://cdn.jsdelivr.net/npm/@jspreadsheet/validations/dist/index.min.js"></script> Examples
Basic Data Grid Validations
Define data grid or spreadsheet validations during initialization or through programmatic methods.
<html> <link rel="stylesheet" href="https://jspreadsheet.com/v12/jspreadsheet.css" type="text/css" /> <link rel="stylesheet" href="https://jsuites.net/v6/jsuites.css" type="text/css" /> <link rel="stylesheet" href="https://fonts.googleapis.com/css?family=Material+Icons" /> <link rel="stylesheet" href="https://cdn.jsdelivr.net/npm/@lemonadejs/studio/dist/style.min.css" type="text/css" /> <script src="https://jsuites.net/v6/jsuites.js"></script> <script src="https://jspreadsheet.com/v12/jspreadsheet.js"></script> <script src="https://cdn.jsdelivr.net/npm/lemonadejs/dist/lemonade.min.js"></script> <script src="https://cdn.jsdelivr.net/npm/@lemonadejs/studio/dist/index.min.js"></script> <script src="https://cdn.jsdelivr.net/npm/@jspreadsheet/validations/dist/index.min.js"></script> <div id="spreadsheet"></div> <script> // Set the license for both plugin and the spreadsheet jspreadsheet.setLicense('ZmIyZDYxZTcyODI2ZWJhOTJkMzU4YjQ4ZjZhMWMxZjhkNjYwMDYxOWE5MzBmNWNmNGMzM2NhODY2OWY5NTFkYzI1YjMyNGQ4MDJlZGNmYTIzZDlhZmRhMzU0ODA4NTg2OWMwMThiN2FlMDJhM2YyOTllY2U4ZjRkMjFiMDZiMWUsZXlKamJHbGxiblJKWkNJNklpSXNJbTVoYldVaU9pSktjM0J5WldGa2MyaGxaWFFpTENKa1lYUmxJam94TnpjME1UYzBNRE16TENKa2IyMWhhVzRpT2xzaWFuTndjbVZoWkhOb1pXVjBMbU52YlNJc0ltTnZaR1Z6WVc1a1ltOTRMbWx2SWl3aWFuTm9aV3hzTG01bGRDSXNJbU56WWk1aGNIQWlMQ0p6ZEdGamEySnNhWFI2TG1sdklpd2lkMlZpWTI5dWRHRnBibVZ5TG1sdklpd2liRzlqWVd4b2IzTjBJbDBzSW5Cc1lXNGlPaUl6TkNJc0luTmpiM0JsSWpwYkluWTNJaXdpZGpnaUxDSjJPU0lzSW5ZeE1DSXNJbll4TVNJc0luWXhNaUlzSW1Ob1lYSjBjeUlzSW1admNtMXpJaXdpWm05eWJYVnNZU0lzSW5CaGNuTmxjaUlzSW5KbGJtUmxjaUlzSW1OdmJXMWxiblJ6SWl3aWFXMXdiM0owWlhJaUxDSmlZWElpTENKMllXeHBaR0YwYVc5dWN5SXNJbk5sWVhKamFDSXNJbkJ5YVc1MElpd2ljMmhsWlhSeklpd2lZMnhwWlc1MElpd2ljMlZ5ZG1WeUlpd2ljMmhoY0dWeklpd2labTl5YldGMElpd2ljR2wyYjNRaVhTd2laR1Z0YnlJNmRISjFaWDA9'); // Set the extensions jspreadsheet.setExtensions({ validations }); // Create the spreadsheet const spreadsheet = jspreadsheet(document.getElementById('spreadsheet'), { toolbar: true, worksheets: [{ data: [ [10,"=A1*2"], [20,"=A2*2"], [30,"=A3*2"], [40,"=A4*2"], [50,"=A5*2"] ], minDimensions: [7, 6], }], validations: [{ range: 'Sheet1!A1:A6', action: "warning", criteria: "between", type: "number", allowBlank: false, value: [10, 30], }] }); </script> </html> import React, { useRef } from "react"; import { Spreadsheet, Worksheet, jspreadsheet } from "@jspreadsheet/react"; import validations from "@jspreadsheet/validations"; import "jsuites/dist/jsuites.css"; import "jspreadsheet/dist/jspreadsheet.css"; import "@lemonadejs/studio/dist/style.css"; // You can use the following license for quick testing on localhost, StackBlitz, or CodeSandbox. // The license is valid for one day, after which the spreadsheet will become read-only. // For a longer trial period, you can create a free account and generate a demo license with an extended expiration date. jspreadsheet.setLicense('ZmIyZDYxZTcyODI2ZWJhOTJkMzU4YjQ4ZjZhMWMxZjhkNjYwMDYxOWE5MzBmNWNmNGMzM2NhODY2OWY5NTFkYzI1YjMyNGQ4MDJlZGNmYTIzZDlhZmRhMzU0ODA4NTg2OWMwMThiN2FlMDJhM2YyOTllY2U4ZjRkMjFiMDZiMWUsZXlKamJHbGxiblJKWkNJNklpSXNJbTVoYldVaU9pSktjM0J5WldGa2MyaGxaWFFpTENKa1lYUmxJam94TnpjME1UYzBNRE16TENKa2IyMWhhVzRpT2xzaWFuTndjbVZoWkhOb1pXVjBMbU52YlNJc0ltTnZaR1Z6WVc1a1ltOTRMbWx2SWl3aWFuTm9aV3hzTG01bGRDSXNJbU56WWk1aGNIQWlMQ0p6ZEdGamEySnNhWFI2TG1sdklpd2lkMlZpWTI5dWRHRnBibVZ5TG1sdklpd2liRzlqWVd4b2IzTjBJbDBzSW5Cc1lXNGlPaUl6TkNJc0luTmpiM0JsSWpwYkluWTNJaXdpZGpnaUxDSjJPU0lzSW5ZeE1DSXNJbll4TVNJc0luWXhNaUlzSW1Ob1lYSjBjeUlzSW1admNtMXpJaXdpWm05eWJYVnNZU0lzSW5CaGNuTmxjaUlzSW5KbGJtUmxjaUlzSW1OdmJXMWxiblJ6SWl3aWFXMXdiM0owWlhJaUxDSmlZWElpTENKMllXeHBaR0YwYVc5dWN5SXNJbk5sWVhKamFDSXNJbkJ5YVc1MElpd2ljMmhsWlhSeklpd2lZMnhwWlc1MElpd2ljMlZ5ZG1WeUlpd2ljMmhoY0dWeklpd2labTl5YldGMElpd2ljR2wyYjNRaVhTd2laR1Z0YnlJNmRISjFaWDA9'); // Extensions jspreadsheet.setExtensions({ validations }) 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 rules = [{ range: 'Sheet1!A1:A6', action: "warning", criteria: "between", type: "number", allowBlank: false, value: [10, 30], }]; // Render component return ( <Spreadsheet ref={spreadsheet} validations={rules} toolbar={true}> <Worksheet data={data} /> </Spreadsheet> ); } <template> <Spreadsheet ref="spreadsheet" :validations="rules" :toolbar="true"> <Worksheet :data="data" worksheetName="Sheet1" /> </Spreadsheet> </template> <script> import { Spreadsheet, Worksheet, jspreadsheet } from "@jspreadsheet/vue"; import validations from "@jspreadsheet/validations"; import "jsuites/dist/jsuites.css"; import "jspreadsheet/dist/jspreadsheet.css"; import "@lemonadejs/studio/dist/style.css"; // You can use the following license for quick testing on localhost, StackBlitz, or CodeSandbox. // The license is valid for one day, after which the spreadsheet will become read-only. // For a longer trial period, you can create a free account and generate a demo license with an extended expiration date. jspreadsheet.setLicense('ZmIyZDYxZTcyODI2ZWJhOTJkMzU4YjQ4ZjZhMWMxZjhkNjYwMDYxOWE5MzBmNWNmNGMzM2NhODY2OWY5NTFkYzI1YjMyNGQ4MDJlZGNmYTIzZDlhZmRhMzU0ODA4NTg2OWMwMThiN2FlMDJhM2YyOTllY2U4ZjRkMjFiMDZiMWUsZXlKamJHbGxiblJKWkNJNklpSXNJbTVoYldVaU9pSktjM0J5WldGa2MyaGxaWFFpTENKa1lYUmxJam94TnpjME1UYzBNRE16TENKa2IyMWhhVzRpT2xzaWFuTndjbVZoWkhOb1pXVjBMbU52YlNJc0ltTnZaR1Z6WVc1a1ltOTRMbWx2SWl3aWFuTm9aV3hzTG01bGRDSXNJbU56WWk1aGNIQWlMQ0p6ZEdGamEySnNhWFI2TG1sdklpd2lkMlZpWTI5dWRHRnBibVZ5TG1sdklpd2liRzlqWVd4b2IzTjBJbDBzSW5Cc1lXNGlPaUl6TkNJc0luTmpiM0JsSWpwYkluWTNJaXdpZGpnaUxDSjJPU0lzSW5ZeE1DSXNJbll4TVNJc0luWXhNaUlzSW1Ob1lYSjBjeUlzSW1admNtMXpJaXdpWm05eWJYVnNZU0lzSW5CaGNuTmxjaUlzSW5KbGJtUmxjaUlzSW1OdmJXMWxiblJ6SWl3aWFXMXdiM0owWlhJaUxDSmlZWElpTENKMllXeHBaR0YwYVc5dWN5SXNJbk5sWVhKamFDSXNJbkJ5YVc1MElpd2ljMmhsWlhSeklpd2lZMnhwWlc1MElpd2ljMlZ5ZG1WeUlpd2ljMmhoY0dWeklpd2labTl5YldGMElpd2ljR2wyYjNRaVhTd2laR1Z0YnlJNmRISjFaWDA9'); // Extensions jspreadsheet.setExtensions({ validations }) export default { components: { Spreadsheet, Worksheet, }, setup() { // Data const data = [ [10, "=A1*2"], [20, "=A2*2"], [30, "=A3*2"], [40, "=A4*2"], [50, "=A5*2"], ]; // Validations const rules = [ { range: "Sheet1!A1:A6", action: "warning", criteria: "between", type: "number", allowBlank: false, value: [10, 30], }, ]; // Return object return { data, rules }; } } </script> import { Component, ViewChild, ElementRef } from "@angular/core"; import jspreadsheet from "jspreadsheet"; import validations from "@jspreadsheet/validations"; import "jsuites/dist/jsuites.css"; import "jspreadsheet/dist/jspreadsheet.css"; import "@lemonadejs/studio/dist/style.css"; // You can use the following license for quick testing on localhost, StackBlitz, or CodeSandbox. // The license is valid for one day, after which the spreadsheet will become read-only. // For a longer trial period, you can create a free account and generate a demo license with an extended expiration date. jspreadsheet.setLicense('ZmIyZDYxZTcyODI2ZWJhOTJkMzU4YjQ4ZjZhMWMxZjhkNjYwMDYxOWE5MzBmNWNmNGMzM2NhODY2OWY5NTFkYzI1YjMyNGQ4MDJlZGNmYTIzZDlhZmRhMzU0ODA4NTg2OWMwMThiN2FlMDJhM2YyOTllY2U4ZjRkMjFiMDZiMWUsZXlKamJHbGxiblJKWkNJNklpSXNJbTVoYldVaU9pSktjM0J5WldGa2MyaGxaWFFpTENKa1lYUmxJam94TnpjME1UYzBNRE16TENKa2IyMWhhVzRpT2xzaWFuTndjbVZoWkhOb1pXVjBMbU52YlNJc0ltTnZaR1Z6WVc1a1ltOTRMbWx2SWl3aWFuTm9aV3hzTG01bGRDSXNJbU56WWk1aGNIQWlMQ0p6ZEdGamEySnNhWFI2TG1sdklpd2lkMlZpWTI5dWRHRnBibVZ5TG1sdklpd2liRzlqWVd4b2IzTjBJbDBzSW5Cc1lXNGlPaUl6TkNJc0luTmpiM0JsSWpwYkluWTNJaXdpZGpnaUxDSjJPU0lzSW5ZeE1DSXNJbll4TVNJc0luWXhNaUlzSW1Ob1lYSjBjeUlzSW1admNtMXpJaXdpWm05eWJYVnNZU0lzSW5CaGNuTmxjaUlzSW5KbGJtUmxjaUlzSW1OdmJXMWxiblJ6SWl3aWFXMXdiM0owWlhJaUxDSmlZWElpTENKMllXeHBaR0YwYVc5dWN5SXNJbk5sWVhKamFDSXNJbkJ5YVc1MElpd2ljMmhsWlhSeklpd2lZMnhwWlc1MElpd2ljMlZ5ZG1WeUlpd2ljMmhoY0dWeklpd2labTl5YldGMElpd2ljR2wyYjNRaVhTd2laR1Z0YnlJNmRISjFaWDA9'); // Extensions jspreadsheet.setExtensions({ validations }); @Component({ standalone: true, selector: "app-root", template: `<div #spreadsheet></div>` }) export class AppComponent { @ViewChild("spreadsheet") spreadsheet: ElementRef; // Create a new data grid ngAfterViewInit() { // Create spreadsheet jspreadsheet(this.spreadsheet.nativeElement, { toolbar: true, 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], dropdown: true }] }); } }