Validations

JSS validations offer an interface that enables users to add, update, and delete validations directly on the data grid cells.

What's new?
Jspreadsheet version 10 includes the validation methods natively, making the validations extension unnecessary if an interface for managing validations is not required.

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.

Source code example

<html>
<script src="https://jspreadsheet.com/v10/jspreadsheet.js"></script>
<link rel="stylesheet" href="https://jspreadsheet.com/v10/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('YWFhNmIyM2IzODJhNzc2YzVlYmU5NGE5NWNlNTJiYjY5N2FkYmNhNDY0MTM4NmFmMTY0OWQ4MGQwOTdmNmQ5ZjkzODA4YjE5YjJkNGI2NzlhNDliN2Q0MjhiYTQ1YWE5ODBkYzliMDA3Y2QyYjVjODNiMGY3ODRlMzQyOTBjNmQsZXlKdVlXMWxJam9pU25Od2NtVmhaSE5vWldWMElpd2laR0YwWlNJNk1UWTROVGd6T0RVek1Td2laRzl0WVdsdUlqcGJJbXB6Y0hKbFlXUnphR1ZsZEM1amIyMGlMQ0pqYjJSbGMyRnVaR0p2ZUM1cGJ5SXNJbXB6YUdWc2JDNXVaWFFpTENKamMySXVZWEJ3SWl3aWQyVmlJaXdpYkc5allXeG9iM04wSWwwc0luQnNZVzRpT2lJek5DSXNJbk5qYjNCbElqcGJJblkzSWl3aWRqZ2lMQ0oyT1NJc0luWXhNQ0lzSW1Ob1lYSjBjeUlzSW1admNtMXpJaXdpWm05eWJYVnNZU0lzSW5CaGNuTmxjaUlzSW5KbGJtUmxjaUlzSW1OdmJXMWxiblJ6SWl3aWFXMXdiM0owSWl3aVltRnlJaXdpZG1Gc2FXUmhkR2x2Ym5NaUxDSnpaV0Z5WTJnaUxDSndjbWx1ZENJc0luTm9aV1YwY3lKZExDSmtaVzF2SWpwMGNuVmxmUT09');

// 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: [6, 6],
    }],
    validations: [{
        range: 'Sheet1!A1:A6',
        action: "warning",
        criteria: "between",
        type: "number",
        allowBlank: false,
        value: [10, 30],
    }]
});
</script>
</html>

React code example

import React, { useRef } from "react";
import { Spreadsheet, Worksheet } from "@jspreadsheet/react";
import jspreadsheet from "jspreadsheet";
import validations from "@jspreadsheet/validations";

// License
const license = 'YWFhNmIyM2IzODJhNzc2YzVlYmU5NGE5NWNlNTJiYjY5N2FkYmNhNDY0MTM4NmFmMTY0OWQ4MGQwOTdmNmQ5ZjkzODA4YjE5YjJkNGI2NzlhNDliN2Q0MjhiYTQ1YWE5ODBkYzliMDA3Y2QyYjVjODNiMGY3ODRlMzQyOTBjNmQsZXlKdVlXMWxJam9pU25Od2NtVmhaSE5vWldWMElpd2laR0YwWlNJNk1UWTROVGd6T0RVek1Td2laRzl0WVdsdUlqcGJJbXB6Y0hKbFlXUnphR1ZsZEM1amIyMGlMQ0pqYjJSbGMyRnVaR0p2ZUM1cGJ5SXNJbXB6YUdWc2JDNXVaWFFpTENKamMySXVZWEJ3SWl3aWQyVmlJaXdpYkc5allXeG9iM04wSWwwc0luQnNZVzRpT2lJek5DSXNJbk5qYjNCbElqcGJJblkzSWl3aWRqZ2lMQ0oyT1NJc0luWXhNQ0lzSW1Ob1lYSjBjeUlzSW1admNtMXpJaXdpWm05eWJYVnNZU0lzSW5CaGNuTmxjaUlzSW5KbGJtUmxjaUlzSW1OdmJXMWxiblJ6SWl3aWFXMXdiM0owSWl3aVltRnlJaXdpZG1Gc2FXUmhkR2x2Ym5NaUxDSnpaV0Z5WTJnaUxDSndjbWx1ZENJc0luTm9aV1YwY3lKZExDSmtaVzF2SWpwMGNuVmxmUT09';

// Extensions
const extensions = { 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} license={license} validations={rules} extensions={extensions} toolbar>
          <Worksheet data={data} />
      </Spreadsheet>
    );
}

See this example on codesandbox

VueJS example

<template>
    <Spreadsheet ref="spreadsheet" :license="license" :validations="rules" :extensions="extensions" :toolbars="true">
        <Worksheet :data="data" worksheetName="Sheet1" />
    </Spreadsheet>
</template>

<script>
import { Spreadsheet, Worksheet } from "@jspreadsheet/vue";
import validations from "@jspreadsheet/validations";

// License
const license = 'YWFhNmIyM2IzODJhNzc2YzVlYmU5NGE5NWNlNTJiYjY5N2FkYmNhNDY0MTM4NmFmMTY0OWQ4MGQwOTdmNmQ5ZjkzODA4YjE5YjJkNGI2NzlhNDliN2Q0MjhiYTQ1YWE5ODBkYzliMDA3Y2QyYjVjODNiMGY3ODRlMzQyOTBjNmQsZXlKdVlXMWxJam9pU25Od2NtVmhaSE5vWldWMElpd2laR0YwWlNJNk1UWTROVGd6T0RVek1Td2laRzl0WVdsdUlqcGJJbXB6Y0hKbFlXUnphR1ZsZEM1amIyMGlMQ0pqYjJSbGMyRnVaR0p2ZUM1cGJ5SXNJbXB6YUdWc2JDNXVaWFFpTENKamMySXVZWEJ3SWl3aWQyVmlJaXdpYkc5allXeG9iM04wSWwwc0luQnNZVzRpT2lJek5DSXNJbk5qYjNCbElqcGJJblkzSWl3aWRqZ2lMQ0oyT1NJc0luWXhNQ0lzSW1Ob1lYSjBjeUlzSW1admNtMXpJaXdpWm05eWJYVnNZU0lzSW5CaGNuTmxjaUlzSW5KbGJtUmxjaUlzSW1OdmJXMWxiblJ6SWl3aWFXMXdiM0owSWl3aVltRnlJaXdpZG1Gc2FXUmhkR2x2Ym5NaUxDSnpaV0Z5WTJnaUxDSndjbWx1ZENJc0luTm9aV1YwY3lKZExDSmtaVzF2SWpwMGNuVmxmUT09';

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],
            },
        ];

        // Extensions
        const extensions = {
            validations,
        };

        // Return object
        return {
            data,
            rules,
            license,
            extensions,
        };
    }
}
</script>

See this example on Codesandbox

Angular example

import { Component, ViewChild, ElementRef } from "@angular/core";
import * as jspreadsheet from "jspreadsheet";
import * as comments from "@jspreadsheet/comments";

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

// Set your JSS license key (The following key only works for one day)
jspreadsheet.setLicense('YWFhNmIyM2IzODJhNzc2YzVlYmU5NGE5NWNlNTJiYjY5N2FkYmNhNDY0MTM4NmFmMTY0OWQ4MGQwOTdmNmQ5ZjkzODA4YjE5YjJkNGI2NzlhNDliN2Q0MjhiYTQ1YWE5ODBkYzliMDA3Y2QyYjVjODNiMGY3ODRlMzQyOTBjNmQsZXlKdVlXMWxJam9pU25Od2NtVmhaSE5vWldWMElpd2laR0YwWlNJNk1UWTROVGd6T0RVek1Td2laRzl0WVdsdUlqcGJJbXB6Y0hKbFlXUnphR1ZsZEM1amIyMGlMQ0pqYjJSbGMyRnVaR0p2ZUM1cGJ5SXNJbXB6YUdWc2JDNXVaWFFpTENKamMySXVZWEJ3SWl3aWQyVmlJaXdpYkc5allXeG9iM04wSWwwc0luQnNZVzRpT2lJek5DSXNJbk5qYjNCbElqcGJJblkzSWl3aWRqZ2lMQ0oyT1NJc0luWXhNQ0lzSW1Ob1lYSjBjeUlzSW1admNtMXpJaXdpWm05eWJYVnNZU0lzSW5CaGNuTmxjaUlzSW5KbGJtUmxjaUlzSW1OdmJXMWxiblJ6SWl3aWFXMXdiM0owSWl3aVltRnlJaXdpZG1Gc2FXUmhkR2x2Ym5NaUxDSnpaV0Z5WTJnaUxDSndjbWx1ZENJc0luTm9aV1YwY3lKZExDSmtaVzF2SWpwMGNuVmxmUT09');

// 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],
            }]
        });
    }
}