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" />

<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('Nzc4MDJhM2UxNDBkN2U5YjEyN2Y4ODgxOTNjMDhiMDU1YTRlYjM2ZWRjNjQ2NWQwYmFhYTQwYjY3NmM0NjVjYzRiZTBmNzgwM2NjYmQ0YzgzYTY3Mzk4ZWRjZmJhMGIxMjM0MThhYWM0NTdhMzNkNTFlYmUxZTk0NTgyNTlhNmQsZXlKamJHbGxiblJKWkNJNklpSXNJbTVoYldVaU9pSktjM0J5WldGa2MyaGxaWFFpTENKa1lYUmxJam94TnpFME1URXdOVEkwTENKa2IyMWhhVzRpT2xzaWFuTndjbVZoWkhOb1pXVjBMbU52YlNJc0ltTnZaR1Z6WVc1a1ltOTRMbWx2SWl3aWFuTm9aV3hzTG01bGRDSXNJbU56WWk1aGNIQWlMQ0ozWldJaUxDSnNiMk5oYkdodmMzUWlYU3dpY0d4aGJpSTZJak0wSWl3aWMyTnZjR1VpT2xzaWRqY2lMQ0oyT0NJc0luWTVJaXdpZGpFd0lpd2lkakV4SWl3aVkyaGhjblJ6SWl3aVptOXliWE1pTENKbWIzSnRkV3hoSWl3aWNHRnljMlZ5SWl3aWNtVnVaR1Z5SWl3aVkyOXRiV1Z1ZEhNaUxDSnBiWEJ2Y25SbGNpSXNJbUpoY2lJc0luWmhiR2xrWVhScGIyNXpJaXdpYzJWaGNtTm9JaXdpY0hKcGJuUWlMQ0p6YUdWbGRITWlMQ0pqYkdsbGJuUWlMQ0p6WlhKMlpYSWlMQ0p6YUdGd1pYTWlYU3dpWkdWdGJ5STZkSEoxWlgwPQ==');
// 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";

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

// Set your JSS license key (The following key only works for one day)
jspreadsheet.setLicense('Nzc4MDJhM2UxNDBkN2U5YjEyN2Y4ODgxOTNjMDhiMDU1YTRlYjM2ZWRjNjQ2NWQwYmFhYTQwYjY3NmM0NjVjYzRiZTBmNzgwM2NjYmQ0YzgzYTY3Mzk4ZWRjZmJhMGIxMjM0MThhYWM0NTdhMzNkNTFlYmUxZTk0NTgyNTlhNmQsZXlKamJHbGxiblJKWkNJNklpSXNJbTVoYldVaU9pSktjM0J5WldGa2MyaGxaWFFpTENKa1lYUmxJam94TnpFME1URXdOVEkwTENKa2IyMWhhVzRpT2xzaWFuTndjbVZoWkhOb1pXVjBMbU52YlNJc0ltTnZaR1Z6WVc1a1ltOTRMbWx2SWl3aWFuTm9aV3hzTG01bGRDSXNJbU56WWk1aGNIQWlMQ0ozWldJaUxDSnNiMk5oYkdodmMzUWlYU3dpY0d4aGJpSTZJak0wSWl3aWMyTnZjR1VpT2xzaWRqY2lMQ0oyT0NJc0luWTVJaXdpZGpFd0lpd2lkakV4SWl3aVkyaGhjblJ6SWl3aVptOXliWE1pTENKbWIzSnRkV3hoSWl3aWNHRnljMlZ5SWl3aWNtVnVaR1Z5SWl3aVkyOXRiV1Z1ZEhNaUxDSnBiWEJ2Y25SbGNpSXNJbUpoY2lJc0luWmhiR2xrWVhScGIyNXpJaXdpYzJWaGNtTm9JaXdpY0hKcGJuUWlMQ0p6YUdWbGRITWlMQ0pqYkdsbGJuUWlMQ0p6WlhKMlpYSWlMQ0p6YUdGd1pYTWlYU3dpWkdWdGJ5STZkSEoxWlgwPQ==');
// 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 * as jspreadsheet from "jspreadsheet";
import * as comments from "@jspreadsheet/comments";

import "jsuites/dist/jsuites.css";
import "jspreadsheet/dist/jspreadsheet.css";

// Set your JSS license key (The following key only works for one day)
jspreadsheet.setLicense('Nzc4MDJhM2UxNDBkN2U5YjEyN2Y4ODgxOTNjMDhiMDU1YTRlYjM2ZWRjNjQ2NWQwYmFhYTQwYjY3NmM0NjVjYzRiZTBmNzgwM2NjYmQ0YzgzYTY3Mzk4ZWRjZmJhMGIxMjM0MThhYWM0NTdhMzNkNTFlYmUxZTk0NTgyNTlhNmQsZXlKamJHbGxiblJKWkNJNklpSXNJbTVoYldVaU9pSktjM0J5WldGa2MyaGxaWFFpTENKa1lYUmxJam94TnpFME1URXdOVEkwTENKa2IyMWhhVzRpT2xzaWFuTndjbVZoWkhOb1pXVjBMbU52YlNJc0ltTnZaR1Z6WVc1a1ltOTRMbWx2SWl3aWFuTm9aV3hzTG01bGRDSXNJbU56WWk1aGNIQWlMQ0ozWldJaUxDSnNiMk5oYkdodmMzUWlYU3dpY0d4aGJpSTZJak0wSWl3aWMyTnZjR1VpT2xzaWRqY2lMQ0oyT0NJc0luWTVJaXdpZGpFd0lpd2lkakV4SWl3aVkyaGhjblJ6SWl3aVptOXliWE1pTENKbWIzSnRkV3hoSWl3aWNHRnljMlZ5SWl3aWNtVnVaR1Z5SWl3aVkyOXRiV1Z1ZEhNaUxDSnBiWEJ2Y25SbGNpSXNJbUpoY2lJc0luWmhiR2xrWVhScGIyNXpJaXdpYzJWaGNtTm9JaXdpY0hKcGJuUWlMQ0p6YUdWbGRITWlMQ0pqYkdsbGJuUWlMQ0p6WlhKMlpYSWlMQ0p6YUdGd1pYTWlYU3dpWkdWdGJ5STZkSEoxWlgwPQ==');
// Extensions
jspreadsheet.setExtensions({ validations });

@Component({
    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],
            }]
        });
    }
}

Edit this page on GitHub