
Validations
The validations extension in Jspreadsheet enhances data integrity by allowing 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
Please 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
You can define the data grid or spreadsheet validations during initialization or through programmatic methods.
<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" />
<link rel="stylesheet" href="https://cdn.jsdelivr.net/npm/@lemonadejs/studio/dist/style.min.css" type="text/css" />
<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>
jspreadsheet.setLicense('NGY2Mjc2M2RkMGE0OThhY2EyMjI0MmQ2NGJlYWYxZTJhNDk0NzZiYjdmOGJlMzg3MzYxMGNjOGQ5NWZmZmRjYzU0MTU4NjdhODQyMzE5NDQ0ODMxM2RiNDQ3NWI2NjA0MDU5YjYzMTkzMWFlNTc1ZmMwYWFiNzU4NWE1OWEzMTEsZXlKamJHbGxiblJKWkNJNklpSXNJbTVoYldVaU9pSktjM0J5WldGa2MyaGxaWFFpTENKa1lYUmxJam94TnpRME9UWXpORFkxTENKa2IyMWhhVzRpT2xzaWFuTndjbVZoWkhOb1pXVjBMbU52YlNJc0ltTnZaR1Z6WVc1a1ltOTRMbWx2SWl3aWFuTm9aV3hzTG01bGRDSXNJbU56WWk1aGNIQWlMQ0ozWldJaUxDSnNiMk5oYkdodmMzUWlYU3dpY0d4aGJpSTZJak0wSWl3aWMyTnZjR1VpT2xzaWRqY2lMQ0oyT0NJc0luWTVJaXdpZGpFd0lpd2lkakV4SWl3aVkyaGhjblJ6SWl3aVptOXliWE1pTENKbWIzSnRkV3hoSWl3aWNHRnljMlZ5SWl3aWNtVnVaR1Z5SWl3aVkyOXRiV1Z1ZEhNaUxDSnBiWEJ2Y25SbGNpSXNJbUpoY2lJc0luWmhiR2xrWVhScGIyNXpJaXdpYzJWaGNtTm9JaXdpY0hKcGJuUWlMQ0p6YUdWbGRITWlMQ0pqYkdsbGJuUWlMQ0p6WlhKMlpYSWlMQ0p6YUdGd1pYTWlYU3dpWkdWdGJ5STZkSEoxWlgwPQ==');
jspreadsheet.setExtensions({ validations });
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";
jspreadsheet.setLicense('NGY2Mjc2M2RkMGE0OThhY2EyMjI0MmQ2NGJlYWYxZTJhNDk0NzZiYjdmOGJlMzg3MzYxMGNjOGQ5NWZmZmRjYzU0MTU4NjdhODQyMzE5NDQ0ODMxM2RiNDQ3NWI2NjA0MDU5YjYzMTkzMWFlNTc1ZmMwYWFiNzU4NWE1OWEzMTEsZXlKamJHbGxiblJKWkNJNklpSXNJbTVoYldVaU9pSktjM0J5WldGa2MyaGxaWFFpTENKa1lYUmxJam94TnpRME9UWXpORFkxTENKa2IyMWhhVzRpT2xzaWFuTndjbVZoWkhOb1pXVjBMbU52YlNJc0ltTnZaR1Z6WVc1a1ltOTRMbWx2SWl3aWFuTm9aV3hzTG01bGRDSXNJbU56WWk1aGNIQWlMQ0ozWldJaUxDSnNiMk5oYkdodmMzUWlYU3dpY0d4aGJpSTZJak0wSWl3aWMyTnZjR1VpT2xzaWRqY2lMQ0oyT0NJc0luWTVJaXdpZGpFd0lpd2lkakV4SWl3aVkyaGhjblJ6SWl3aVptOXliWE1pTENKbWIzSnRkV3hoSWl3aWNHRnljMlZ5SWl3aWNtVnVaR1Z5SWl3aVkyOXRiV1Z1ZEhNaUxDSnBiWEJ2Y25SbGNpSXNJbUpoY2lJc0luWmhiR2xrWVhScGIyNXpJaXdpYzJWaGNtTm9JaXdpY0hKcGJuUWlMQ0p6YUdWbGRITWlMQ0pqYkdsbGJuUWlMQ0p6WlhKMlpYSWlMQ0p6YUdGd1pYTWlYU3dpWkdWdGJ5STZkSEoxWlgwPQ==');
jspreadsheet.setExtensions({ validations })
export default function App() {
const spreadsheet = useRef();
const data = [
[10,"=A1*2"],
[20,"=A2*2"],
[30,"=A3*2"],
[40,"=A4*2"],
[50,"=A5*2"]
];
const rules = [{
range: 'Sheet1!A1:A6',
action: "warning",
criteria: "between",
type: "number",
allowBlank: false,
value: [10, 30],
}];
return (
<Spreadsheet ref={spreadsheet} validations={rules} toolbar>
<Worksheet data={data} />
</Spreadsheet>
);
}
<template>
<Spreadsheet ref="spreadsheet" :validations="rules" :toolbars="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";
jspreadsheet.setLicense('NGY2Mjc2M2RkMGE0OThhY2EyMjI0MmQ2NGJlYWYxZTJhNDk0NzZiYjdmOGJlMzg3MzYxMGNjOGQ5NWZmZmRjYzU0MTU4NjdhODQyMzE5NDQ0ODMxM2RiNDQ3NWI2NjA0MDU5YjYzMTkzMWFlNTc1ZmMwYWFiNzU4NWE1OWEzMTEsZXlKamJHbGxiblJKWkNJNklpSXNJbTVoYldVaU9pSktjM0J5WldGa2MyaGxaWFFpTENKa1lYUmxJam94TnpRME9UWXpORFkxTENKa2IyMWhhVzRpT2xzaWFuTndjbVZoWkhOb1pXVjBMbU52YlNJc0ltTnZaR1Z6WVc1a1ltOTRMbWx2SWl3aWFuTm9aV3hzTG01bGRDSXNJbU56WWk1aGNIQWlMQ0ozWldJaUxDSnNiMk5oYkdodmMzUWlYU3dpY0d4aGJpSTZJak0wSWl3aWMyTnZjR1VpT2xzaWRqY2lMQ0oyT0NJc0luWTVJaXdpZGpFd0lpd2lkakV4SWl3aVkyaGhjblJ6SWl3aVptOXliWE1pTENKbWIzSnRkV3hoSWl3aWNHRnljMlZ5SWl3aWNtVnVaR1Z5SWl3aVkyOXRiV1Z1ZEhNaUxDSnBiWEJ2Y25SbGNpSXNJbUpoY2lJc0luWmhiR2xrWVhScGIyNXpJaXdpYzJWaGNtTm9JaXdpY0hKcGJuUWlMQ0p6YUdWbGRITWlMQ0pqYkdsbGJuUWlMQ0p6WlhKMlpYSWlMQ0p6YUdGd1pYTWlYU3dpWkdWdGJ5STZkSEoxWlgwPQ==');
jspreadsheet.setExtensions({ validations })
export default {
components: {
Spreadsheet,
Worksheet,
},
setup() {
const data = [
[10, "=A1*2"],
[20, "=A2*2"],
[30, "=A3*2"],
[40, "=A4*2"],
[50, "=A5*2"],
];
const rules = [
{
range: "Sheet1!A1:A6",
action: "warning",
criteria: "between",
type: "number",
allowBlank: false,
value: [10, 30],
},
];
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";
jspreadsheet.setLicense('NGY2Mjc2M2RkMGE0OThhY2EyMjI0MmQ2NGJlYWYxZTJhNDk0NzZiYjdmOGJlMzg3MzYxMGNjOGQ5NWZmZmRjYzU0MTU4NjdhODQyMzE5NDQ0ODMxM2RiNDQ3NWI2NjA0MDU5YjYzMTkzMWFlNTc1ZmMwYWFiNzU4NWE1OWEzMTEsZXlKamJHbGxiblJKWkNJNklpSXNJbTVoYldVaU9pSktjM0J5WldGa2MyaGxaWFFpTENKa1lYUmxJam94TnpRME9UWXpORFkxTENKa2IyMWhhVzRpT2xzaWFuTndjbVZoWkhOb1pXVjBMbU52YlNJc0ltTnZaR1Z6WVc1a1ltOTRMbWx2SWl3aWFuTm9aV3hzTG01bGRDSXNJbU56WWk1aGNIQWlMQ0ozWldJaUxDSnNiMk5oYkdodmMzUWlYU3dpY0d4aGJpSTZJak0wSWl3aWMyTnZjR1VpT2xzaWRqY2lMQ0oyT0NJc0luWTVJaXdpZGpFd0lpd2lkakV4SWl3aVkyaGhjblJ6SWl3aVptOXliWE1pTENKbWIzSnRkV3hoSWl3aWNHRnljMlZ5SWl3aWNtVnVaR1Z5SWl3aVkyOXRiV1Z1ZEhNaUxDSnBiWEJ2Y25SbGNpSXNJbUpoY2lJc0luWmhiR2xrWVhScGIyNXpJaXdpYzJWaGNtTm9JaXdpY0hKcGJuUWlMQ0p6YUdWbGRITWlMQ0pqYkdsbGJuUWlMQ0p6WlhKMlpYSWlMQ0p6YUdGd1pYTWlYU3dpWkdWdGJ5STZkSEoxWlgwPQ==');
jspreadsheet.setExtensions({ validations });
@Component({
standalone: true,
selector: "app-root",
template: `<div #spreadsheet></div>`
})
export class AppComponent {
@ViewChild("spreadsheet") spreadsheet: ElementRef;
ngAfterViewInit() {
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],
}]
});
}
}