Spreadsheet Validations
Jspreadsheet validations enforce data entry rules, flagging cells for corrections and ensuring inputs match specific requirements. This boosts data integrity by minimizing errors and streamlining data management.
Documentation
Methods
Below are the methods available to manage validations in Jspreadsheet.
Method | Description |
---|---|
getValidations |
Retrieves validation rules for a given index.getValidations(index: Number) => Object |
setValidations |
Create new or change exiting validation rules.setValidations(validations: Object[]) => void |
resetValidations |
Resets specific validation rules by index or reset all if no parameters are provided.resetValidations(indexes?: Number[]) => void |
loadValidations |
Retrieves all validation rules for a specific cell given its coordinates.loadValidations(x: Number, y: Number) => Object[] |
hasErrors |
Checks if a specific worksheet cell fails the validation rules.hasErrors(col?: Number|String, row?: Number) => Boolean |
Events
Events related to validations.
Method | Description |
---|---|
onvalidation | onvalidation(worksheet: worksheetInstance, records: Validations[]) => void |
Validations[]
All available properties to define a validation
Property | 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
Validations in Jspreadsheet ensure data integrity by enforcing rules either initially or programmatically.
<html>
<script src="https://jspreadsheet.com/v11/jspreadsheet.js"></script>
<link rel="stylesheet" href="https://jspreadsheet.com/v11/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><br><br>
<input type="button" value="Add new validation" id="btn1">
<input type="button" value="Remove validation" id="btn2">
<script>
// Set the license for both plugin and the spreadsheet
jspreadsheet.setLicense('OWE4MjA0NTVhZjQ2YjlkMmZiMWFlYzVlNzhhZWE2Yzk3MmM4NjBiZTRjOTMxYTNiOTVlYmM4OGE2MmQ0NzQ3NDhiYTM4ODgzYWY3OTA5OWZhMGU2NmQ0NThhOWM4N2NiN2Q2Yzg3MjFhNWM5ZmJhZGY3Mzc2MDE0NDU3ODEzY2MsZXlKamJHbGxiblJKWkNJNklpSXNJbTVoYldVaU9pSktjM0J5WldGa2MyaGxaWFFpTENKa1lYUmxJam94TnpFMU1qTTVNelF3TENKa2IyMWhhVzRpT2xzaWFuTndjbVZoWkhOb1pXVjBMbU52YlNJc0ltTnZaR1Z6WVc1a1ltOTRMbWx2SWl3aWFuTm9aV3hzTG01bGRDSXNJbU56WWk1aGNIQWlMQ0ozWldJaUxDSnNiMk5oYkdodmMzUWlYU3dpY0d4aGJpSTZJak0wSWl3aWMyTnZjR1VpT2xzaWRqY2lMQ0oyT0NJc0luWTVJaXdpZGpFd0lpd2lkakV4SWl3aVkyaGhjblJ6SWl3aVptOXliWE1pTENKbWIzSnRkV3hoSWl3aWNHRnljMlZ5SWl3aWNtVnVaR1Z5SWl3aVkyOXRiV1Z1ZEhNaUxDSnBiWEJ2Y25SbGNpSXNJbUpoY2lJc0luWmhiR2xrWVhScGIyNXpJaXdpYzJWaGNtTm9JaXdpY0hKcGJuUWlMQ0p6YUdWbGRITWlMQ0pqYkdsbGJuUWlMQ0p6WlhKMlpYSWlMQ0p6YUdGd1pYTWlYU3dpWkdWdGJ5STZkSEoxWlgwPQ==');
// Create the spreadsheet
const grid = 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() {
grid[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
grid[0].resetValidations([1]);
}
document.getElementById("btn1").onclick = create
document.getElementById("btn2").onclick = remove
</script>
</html>
import React, { useRef } from "react";
import { Spreadsheet, Worksheet } from "@jspreadsheet/react";
import jspreadsheet from "jspreadsheet";
import "jsuites/dist/jsuites.css";
import "jspreadsheet/dist/jspreadsheet.css";
const license = 'OWE4MjA0NTVhZjQ2YjlkMmZiMWFlYzVlNzhhZWE2Yzk3MmM4NjBiZTRjOTMxYTNiOTVlYmM4OGE2MmQ0NzQ3NDhiYTM4ODgzYWY3OTA5OWZhMGU2NmQ0NThhOWM4N2NiN2Q2Yzg3MjFhNWM5ZmJhZGY3Mzc2MDE0NDU3ODEzY2MsZXlKamJHbGxiblJKWkNJNklpSXNJbTVoYldVaU9pSktjM0J5WldGa2MyaGxaWFFpTENKa1lYUmxJam94TnpFMU1qTTVNelF3TENKa2IyMWhhVzRpT2xzaWFuTndjbVZoWkhOb1pXVjBMbU52YlNJc0ltTnZaR1Z6WVc1a1ltOTRMbWx2SWl3aWFuTm9aV3hzTG01bGRDSXNJbU56WWk1aGNIQWlMQ0ozWldJaUxDSnNiMk5oYkdodmMzUWlYU3dpY0d4aGJpSTZJak0wSWl3aWMyTnZjR1VpT2xzaWRqY2lMQ0oyT0NJc0luWTVJaXdpZGpFd0lpd2lkakV4SWl3aVkyaGhjblJ6SWl3aVptOXliWE1pTENKbWIzSnRkV3hoSWl3aWNHRnljMlZ5SWl3aWNtVnVaR1Z5SWl3aVkyOXRiV1Z1ZEhNaUxDSnBiWEJ2Y25SbGNpSXNJbUpoY2lJc0luWmhiR2xrWVhScGIyNXpJaXdpYzJWaGNtTm9JaXdpY0hKcGJuUWlMQ0p6YUdWbGRITWlMQ0pqYkdsbGJuUWlMQ0p6WlhKMlpYSWlMQ0p6YUdGd1pYTWlYU3dpWkdWdGJ5STZkSEoxWlgwPQ==';
// 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])} />
</>
);
}
<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";
import "jsuites/dist/jsuites.css";
import "jspreadsheet/dist/jspreadsheet.css";
const license = 'OWE4MjA0NTVhZjQ2YjlkMmZiMWFlYzVlNzhhZWE2Yzk3MmM4NjBiZTRjOTMxYTNiOTVlYmM4OGE2MmQ0NzQ3NDhiYTM4ODgzYWY3OTA5OWZhMGU2NmQ0NThhOWM4N2NiN2Q2Yzg3MjFhNWM5ZmJhZGY3Mzc2MDE0NDU3ODEzY2MsZXlKamJHbGxiblJKWkNJNklpSXNJbTVoYldVaU9pSktjM0J5WldGa2MyaGxaWFFpTENKa1lYUmxJam94TnpFMU1qTTVNelF3TENKa2IyMWhhVzRpT2xzaWFuTndjbVZoWkhOb1pXVjBMbU52YlNJc0ltTnZaR1Z6WVc1a1ltOTRMbWx2SWl3aWFuTm9aV3hzTG01bGRDSXNJbU56WWk1aGNIQWlMQ0ozWldJaUxDSnNiMk5oYkdodmMzUWlYU3dpY0d4aGJpSTZJak0wSWl3aWMyTnZjR1VpT2xzaWRqY2lMQ0oyT0NJc0luWTVJaXdpZGpFd0lpd2lkakV4SWl3aVkyaGhjblJ6SWl3aVptOXliWE1pTENKbWIzSnRkV3hoSWl3aWNHRnljMlZ5SWl3aWNtVnVaR1Z5SWl3aVkyOXRiV1Z1ZEhNaUxDSnBiWEJ2Y25SbGNpSXNJbUpoY2lJc0luWmhiR2xrWVhScGIyNXpJaXdpYzJWaGNtTm9JaXdpY0hKcGJuUWlMQ0p6YUdWbGRITWlMQ0pqYkdsbGJuUWlMQ0p6WlhKMlpYSWlMQ0p6YUdGd1pYTWlYU3dpWkdWdGJ5STZkSEoxWlgwPQ==';
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>
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('OWE4MjA0NTVhZjQ2YjlkMmZiMWFlYzVlNzhhZWE2Yzk3MmM4NjBiZTRjOTMxYTNiOTVlYmM4OGE2MmQ0NzQ3NDhiYTM4ODgzYWY3OTA5OWZhMGU2NmQ0NThhOWM4N2NiN2Q2Yzg3MjFhNWM5ZmJhZGY3Mzc2MDE0NDU3ODEzY2MsZXlKamJHbGxiblJKWkNJNklpSXNJbTVoYldVaU9pSktjM0J5WldGa2MyaGxaWFFpTENKa1lYUmxJam94TnpFMU1qTTVNelF3TENKa2IyMWhhVzRpT2xzaWFuTndjbVZoWkhOb1pXVjBMbU52YlNJc0ltTnZaR1Z6WVc1a1ltOTRMbWx2SWl3aWFuTm9aV3hzTG01bGRDSXNJbU56WWk1aGNIQWlMQ0ozWldJaUxDSnNiMk5oYkdodmMzUWlYU3dpY0d4aGJpSTZJak0wSWl3aWMyTnZjR1VpT2xzaWRqY2lMQ0oyT0NJc0luWTVJaXdpZGpFd0lpd2lkakV4SWl3aVkyaGhjblJ6SWl3aVptOXliWE1pTENKbWIzSnRkV3hoSWl3aWNHRnljMlZ5SWl3aWNtVnVaR1Z5SWl3aVkyOXRiV1Z1ZEhNaUxDSnBiWEJ2Y25SbGNpSXNJbUpoY2lJc0luWmhiR2xrWVhScGIyNXpJaXdpYzJWaGNtTm9JaXdpY0hKcGJuUWlMQ0p6YUdWbGRITWlMQ0pqYkdsbGJuUWlMQ0p6WlhKMlpYSWlMQ0p6YUdGd1pYTWlYU3dpWkdWdGJ5STZkSEoxWlgwPQ==');
@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]);
}
}
Validations Extension
The Validations Extension allows end-users to oversee cell validations within the data grid. It enables the creation of custom rules through an intuitive interface accessible via a toolbar icon.