close
Products
Validations and conditional formatting
This extensions allow the implementation of cell validations and conditional formatting on cells.Documentation
Methods
The following methods can be used to get or set comments in one or multiple cells.Method | Description |
---|---|
getValidations | Get validations by index.getValidations(index: Number) => Object @Param number - validation index. |
setValidations | Add or edit a new validation.setValidations(validations: Object|Array) => void
@param {array} - Array with the validations. |
Settings
All available properties to define a validationProperty | Description |
---|---|
range: string | A cell or a range of cells affect by the validation rules. Example: Sheet1!A1:A8 |
type: string | number | text | date | list | textLength |
Action: string | warning | reject | format |
criteria: string | Selected criteria. '=' | '!=' | '>=' | '>' | '<=' | '<' | 'between | 'not between' | 'valid date' | 'valid email' | 'valid url' | 'contains' | 'not contains' |
text: string | Define the warning or reject message. |
allowBlank: boolean | Allow blank values. |
format: object | color, background-color, font-weight. |
className: string | Class name to be added to the cell when the condition is match. |
Author
Jspreadsheet Pro Team.License
Available on the premium edition only.Installation
Please choose one of the following options// From NPM npm install @jspreadsheet/validations // From CDN https://cdn.jsdelivr.net/npm/@jspreadsheet/validations/dist/index.min.js
Example
A basic example for creating a validation on the spreadsheet during initialization and adding a new one programmatically after initialization.Browser
NPM
<html> <script src="https://jspreadsheet.com/v9/jspreadsheet.js"></script> <link rel="stylesheet" href="https://jspreadsheet.com/v9/jspreadsheet.css" type="text/css" /> <script src="https://jsuites.net/v4/jsuites.js"></script> <link rel="stylesheet" href="https://jsuites.net/v4/jsuites.css" type="text/css" /> <link rel="stylesheet" href="https://fonts.googleapis.com/css?family=Material+Icons" /> <script src="https://cdn.jsdelivr.net/npm/lemonadejs/dist/lemonade.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('MmYxNWM3OTQ5MzEyNjllNzIxNjM4YzU0ODk0ODJjZmNhOTBlZjBhYmY2OWQ0YjQyOTA4ZmNjYTM2YmYzNjIzMDk0OTdkYTNiM2M3NGVlYzA5MmJlMWMxMWQ4YzBiNjJjYmZhYWE0ZDc3YjljYTRmM2Y4NDJjMDg2ZjQxMWZjOTIsZXlKdVlXMWxJam9pU25Od2NtVmhaSE5vWldWMElpd2laR0YwWlNJNk1UWTJNREk0TmpnNU5Dd2laRzl0WVdsdUlqcGJJbXB6Y0hKbFlXUnphR1ZsZEM1amIyMGlMQ0pqYjJSbGMyRnVaR0p2ZUM1cGJ5SXNJbXB6YUdWc2JDNXVaWFFpTENKamMySXVZWEJ3SWl3aWJHOWpZV3hvYjNOMElsMHNJbkJzWVc0aU9pSXpJaXdpYzJOdmNHVWlPbHNpZGpjaUxDSjJPQ0lzSW5ZNUlpd2lZMmhoY25Seklpd2labTl5YlhNaUxDSm1iM0p0ZFd4aElpd2ljR0Z5YzJWeUlpd2ljbVZ1WkdWeUlpd2lZMjl0YldWdWRITWlMQ0pwYlhCdmNuUWlMQ0ppWVhJaUxDSjJZV3hwWkdGMGFXOXVjeUlzSW5ObFlYSmphQ0pkTENKa1pXMXZJanAwY25WbGZRPT0='); // Set the extensions jspreadsheet.setExtensions({ validations }); // Create the spreadsheet var 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: [6, 6], }], validations: [{ range: 'Sheet1!A1:A6', action: "warning", criteria: "between", type: "number", allowBlank: false, value: [10, 30], }] }); var create = function() { spreadsheet[0].setValidations([{ index: 1, value: { range: 'Sheet1!B1:B3', action: "format", criteria: "<", type: "number", value: [500], format: { color: '#ff0000' }, } }]); } var remove = function() { // Remove the validation by the index of the array spreadsheet[0].parent.config.validations spreadsheet[0].resetValidations([1]); } </script> </html>
// Import the JSS library import jspreadsheet from 'jspreadsheet'; // Import the JSS validation extension import validations from '@jspreadsheet/validations'; // Set the license for both plugin and the spreadsheet jspreadsheet.setLicense('MmYxNWM3OTQ5MzEyNjllNzIxNjM4YzU0ODk0ODJjZmNhOTBlZjBhYmY2OWQ0YjQyOTA4ZmNjYTM2YmYzNjIzMDk0OTdkYTNiM2M3NGVlYzA5MmJlMWMxMWQ4YzBiNjJjYmZhYWE0ZDc3YjljYTRmM2Y4NDJjMDg2ZjQxMWZjOTIsZXlKdVlXMWxJam9pU25Od2NtVmhaSE5vWldWMElpd2laR0YwWlNJNk1UWTJNREk0TmpnNU5Dd2laRzl0WVdsdUlqcGJJbXB6Y0hKbFlXUnphR1ZsZEM1amIyMGlMQ0pqYjJSbGMyRnVaR0p2ZUM1cGJ5SXNJbXB6YUdWc2JDNXVaWFFpTENKamMySXVZWEJ3SWl3aWJHOWpZV3hvYjNOMElsMHNJbkJzWVc0aU9pSXpJaXdpYzJOdmNHVWlPbHNpZGpjaUxDSjJPQ0lzSW5ZNUlpd2lZMmhoY25Seklpd2labTl5YlhNaUxDSm1iM0p0ZFd4aElpd2ljR0Z5YzJWeUlpd2ljbVZ1WkdWeUlpd2lZMjl0YldWdWRITWlMQ0pwYlhCdmNuUWlMQ0ppWVhJaUxDSjJZV3hwWkdGMGFXOXVjeUlzSW5ObFlYSmphQ0pkTENKa1pXMXZJanAwY25WbGZRPT0='); // Set the extensions jspreadsheet.setExtensions({ validations }); // Create the spreadsheet let 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: [6, 6], }], validations: [{ range: 'Sheet1!A1:A6', action: "warning", criteria: "between", type: "number", allowBlank: false, value: [10, 30], }] }); var create = function() { spreadsheet[0].setValidations([{ index: 1, value: { range: 'Sheet1!B1:B3', action: "format", criteria: "<", type: "number", value: [500], format: { color: '#ff0000' }, } }]); } var remove = function() { // Remove the validation by the index of the array spreadsheet[0].parent.config.validations spreadsheet[0].resetValidations([1]); }