Data Grid Validations

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.

rule
Validations

Warning message
Reject entry
Format style
Number
Text
Date
List
Text length
Cell is empty
Cell is not empty
Formula
between
is not between
less than
less than or equal to
bigger than
bigger than or equal to
equal to
different to
S
M
T
W
T
F
S
30
31
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
1
2
3
4
5
6
7
8
9
10
S
M
T
W
T
F
S
30
31
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
1
2
3
4
5
6
7
8
9
10

Formatting Options

add
add

Back

 ABCDEFG
11020
22040
33060
44080
550100
6
  • See this example in JavaScript
  • See this example in React
  • See this example in VueJS
  • See this example in Angular
<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>
// Set the license for both plugin and the spreadsheet
jspreadsheet.setLicense('NGY2Mjc2M2RkMGE0OThhY2EyMjI0MmQ2NGJlYWYxZTJhNDk0NzZiYjdmOGJlMzg3MzYxMGNjOGQ5NWZmZmRjYzU0MTU4NjdhODQyMzE5NDQ0ODMxM2RiNDQ3NWI2NjA0MDU5YjYzMTkzMWFlNTc1ZmMwYWFiNzU4NWE1OWEzMTEsZXlKamJHbGxiblJKWkNJNklpSXNJbTVoYldVaU9pSktjM0J5WldGa2MyaGxaWFFpTENKa1lYUmxJam94TnpRME9UWXpORFkxTENKa2IyMWhhVzRpT2xzaWFuTndjbVZoWkhOb1pXVjBMbU52YlNJc0ltTnZaR1Z6WVc1a1ltOTRMbWx2SWl3aWFuTm9aV3hzTG01bGRDSXNJbU56WWk1aGNIQWlMQ0ozWldJaUxDSnNiMk5oYkdodmMzUWlYU3dpY0d4aGJpSTZJak0wSWl3aWMyTnZjR1VpT2xzaWRqY2lMQ0oyT0NJc0luWTVJaXdpZGpFd0lpd2lkakV4SWl3aVkyaGhjblJ6SWl3aVptOXliWE1pTENKbWIzSnRkV3hoSWl3aWNHRnljMlZ5SWl3aWNtVnVaR1Z5SWl3aVkyOXRiV1Z1ZEhNaUxDSnBiWEJ2Y25SbGNpSXNJbUpoY2lJc0luWmhiR2xrWVhScGIyNXpJaXdpYzJWaGNtTm9JaXdpY0hKcGJuUWlMQ0p6YUdWbGRITWlMQ0pqYkdsbGJuUWlMQ0p6WlhKMlpYSWlMQ0p6YUdGd1pYTWlYU3dpWkdWdGJ5STZkSEoxWlgwPQ==');
// 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";

// Set your JSS license key (The following key only works for one day)
jspreadsheet.setLicense('NGY2Mjc2M2RkMGE0OThhY2EyMjI0MmQ2NGJlYWYxZTJhNDk0NzZiYjdmOGJlMzg3MzYxMGNjOGQ5NWZmZmRjYzU0MTU4NjdhODQyMzE5NDQ0ODMxM2RiNDQ3NWI2NjA0MDU5YjYzMTkzMWFlNTc1ZmMwYWFiNzU4NWE1OWEzMTEsZXlKamJHbGxiblJKWkNJNklpSXNJbTVoYldVaU9pSktjM0J5WldGa2MyaGxaWFFpTENKa1lYUmxJam94TnpRME9UWXpORFkxTENKa2IyMWhhVzRpT2xzaWFuTndjbVZoWkhOb1pXVjBMbU52YlNJc0ltTnZaR1Z6WVc1a1ltOTRMbWx2SWl3aWFuTm9aV3hzTG01bGRDSXNJbU56WWk1aGNIQWlMQ0ozWldJaUxDSnNiMk5oYkdodmMzUWlYU3dpY0d4aGJpSTZJak0wSWl3aWMyTnZjR1VpT2xzaWRqY2lMQ0oyT0NJc0luWTVJaXdpZGpFd0lpd2lkakV4SWl3aVkyaGhjblJ6SWl3aVptOXliWE1pTENKbWIzSnRkV3hoSWl3aWNHRnljMlZ5SWl3aWNtVnVaR1Z5SWl3aVkyOXRiV1Z1ZEhNaUxDSnBiWEJ2Y25SbGNpSXNJbUpoY2lJc0luWmhiR2xrWVhScGIyNXpJaXdpYzJWaGNtTm9JaXdpY0hKcGJuUWlMQ0p6YUdWbGRITWlMQ0pqYkdsbGJuUWlMQ0p6WlhKMlpYSWlMQ0p6YUdGd1pYTWlYU3dpWkdWdGJ5STZkSEoxWlgwPQ==');
// 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>
          <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";

// Set your JSS license key (The following key only works for one day)
jspreadsheet.setLicense('NGY2Mjc2M2RkMGE0OThhY2EyMjI0MmQ2NGJlYWYxZTJhNDk0NzZiYjdmOGJlMzg3MzYxMGNjOGQ5NWZmZmRjYzU0MTU4NjdhODQyMzE5NDQ0ODMxM2RiNDQ3NWI2NjA0MDU5YjYzMTkzMWFlNTc1ZmMwYWFiNzU4NWE1OWEzMTEsZXlKamJHbGxiblJKWkNJNklpSXNJbTVoYldVaU9pSktjM0J5WldGa2MyaGxaWFFpTENKa1lYUmxJam94TnpRME9UWXpORFkxTENKa2IyMWhhVzRpT2xzaWFuTndjbVZoWkhOb1pXVjBMbU52YlNJc0ltTnZaR1Z6WVc1a1ltOTRMbWx2SWl3aWFuTm9aV3hzTG01bGRDSXNJbU56WWk1aGNIQWlMQ0ozWldJaUxDSnNiMk5oYkdodmMzUWlYU3dpY0d4aGJpSTZJak0wSWl3aWMyTnZjR1VpT2xzaWRqY2lMQ0oyT0NJc0luWTVJaXdpZGpFd0lpd2lkakV4SWl3aVkyaGhjblJ6SWl3aVptOXliWE1pTENKbWIzSnRkV3hoSWl3aWNHRnljMlZ5SWl3aWNtVnVaR1Z5SWl3aVkyOXRiV1Z1ZEhNaUxDSnBiWEJ2Y25SbGNpSXNJbUpoY2lJc0luWmhiR2xrWVhScGIyNXpJaXdpYzJWaGNtTm9JaXdpY0hKcGJuUWlMQ0p6YUdWbGRITWlMQ0pqYkdsbGJuUWlMQ0p6WlhKMlpYSWlMQ0p6YUdGd1pYTWlYU3dpWkdWdGJ5STZkSEoxWlgwPQ==');
// 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";

// Set your JSS license key (The following key only works for one day)
jspreadsheet.setLicense('NGY2Mjc2M2RkMGE0OThhY2EyMjI0MmQ2NGJlYWYxZTJhNDk0NzZiYjdmOGJlMzg3MzYxMGNjOGQ5NWZmZmRjYzU0MTU4NjdhODQyMzE5NDQ0ODMxM2RiNDQ3NWI2NjA0MDU5YjYzMTkzMWFlNTc1ZmMwYWFiNzU4NWE1OWEzMTEsZXlKamJHbGxiblJKWkNJNklpSXNJbTVoYldVaU9pSktjM0J5WldGa2MyaGxaWFFpTENKa1lYUmxJam94TnpRME9UWXpORFkxTENKa2IyMWhhVzRpT2xzaWFuTndjbVZoWkhOb1pXVjBMbU52YlNJc0ltTnZaR1Z6WVc1a1ltOTRMbWx2SWl3aWFuTm9aV3hzTG01bGRDSXNJbU56WWk1aGNIQWlMQ0ozWldJaUxDSnNiMk5oYkdodmMzUWlYU3dpY0d4aGJpSTZJak0wSWl3aWMyTnZjR1VpT2xzaWRqY2lMQ0oyT0NJc0luWTVJaXdpZGpFd0lpd2lkakV4SWl3aVkyaGhjblJ6SWl3aVptOXliWE1pTENKbWIzSnRkV3hoSWl3aWNHRnljMlZ5SWl3aWNtVnVaR1Z5SWl3aVkyOXRiV1Z1ZEhNaUxDSnBiWEJ2Y25SbGNpSXNJbUpoY2lJc0luWmhiR2xrWVhScGIyNXpJaXdpYzJWaGNtTm9JaXdpY0hKcGJuUWlMQ0p6YUdWbGRITWlMQ0pqYkdsbGJuUWlMQ0p6WlhKMlpYSWlMQ0p6YUdGd1pYTWlYU3dpWkdWdGJ5STZkSEoxWlgwPQ==');
// 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],
            }]
        });
    }
}