Edit this page

sheets.rows.cells.validation Object

The validation rule applied to the cell.

Initialize Spreadsheet with validation data, using sheets.rows configuration option

<div id="spreadsheet"></div>

<script type="text/javascript" charset="utf-8">
    $("#spreadsheet").kendoSpreadsheet({
        sheets: [
            {
                name: "Populated" ,
                rows: [
                    {
                        index: 2,
                        cells: [
                            { index: 3, background: "red", color: "green", value: "D3" },
                            { index: 10, color: "blue", value: "a value" }
                        ]
                    },
                    {
                        index: 5,
                        cells: [
                            {
                                index: 0,
                                color: "red",
                                value: "A6",
                                validation: {
                                    from: "1",
                                    to: "2",
                                    comparerType: "between",
                                    dataType: "number",
                                    messageTemplate: "Number should match the validation."
                                }
                            }
                        ]
                    }
                ]
            }
        ]
    });
</script>

sheets.rows.cells.validation.type String

Defines the validation type. The acceptable options are reject or warning. Defaults to warning.

sheets.rows.cells.validation.comparerType String

Defines the comparer type used to validate the cell value. Can be "greaterThan", "lessThan", "between", "equalTo", "notEqualTo", "greaterThanOrEqualTo", "lessThanOrEqualTo", "notBetween" or "custom".

sheets.rows.cells.validation.dataType String

Defines the data type of the cell value. Can be "date", "text", "number", "list" or "custom".

sheets.rows.cells.validation.from String

Defines a formula or value used for the comparison process. Used as the only compare value if comparer type does not require second argument. Mandatory for validation to work.

sheets.rows.cells.validation.showButton Boolean (default: false)

A boolean value indicating if a button for selecting list items (dataType set to list) should be displayed.

sheets.rows.cells.validation.to String

Defines a formula or value used for the comparison process. Will be used if comparer type requires second argument.

sheets.rows.cells.validation.allowNulls Boolean

Specifies whether to allow nulls.

sheets.rows.cells.validation.messageTemplate String

Defines the hint message that will be displayed if value is invalid.

The template is giving an access to the following variables: from{0}, to{1}, fromFormula{2}, toFormula{3}, dataType{4}, type{5} and comparerType{6}.

Example - use validation template variables

<div id="example">
    <div id="spreadsheet" style="width: 100%;"></div>
    <script>
        $(function() {
        $("#spreadsheet").kendoSpreadsheet({
            columns: 26,
            rows: 30,
            sheetsbar: false,
            excel: {
            // Required to enable Excel Export in some browsers
            proxyURL: "//demos.telerik.com/kendo-ui/service/export"
            },
            sheets: [
            {
                name: "Validation Template",

                rows: [
                {
                    height: 25,
                    cells: [
                    {
                        value: "15",
                        validation: {
                        dataType: "number",
                        from: "B1",
                        to:"C1",
                        allowNulls: true,
                        comparerType:"between" ,
                        type: "reject",
                        titleTemplate: "Number validation error",
                        messageTemplate: "The number have to be between {0} and {1}"
                        }

                    },
                    {
                        value: "10",

                    },
                    {
                        value: "20",

                    },

                    ]
                },
                ],
                columns: [
                {
                    width: 100
                },
                {
                    width: 215
                },
                {
                    width: 115
                },
                {
                    width: 115
                },
                {
                    width: 115
                }
                ]
            },
            {
                name: "ListValues",
                rows: [ //A1:B1
                {
                    cells: [
                    {
                        value: true
                    },
                    {
                        value: false
                    }
                    ]
                }
                ]
            }
            ]
        });
        });
    </script>
</div>

sheets.rows.cells.validation.titleTemplate String

Defines the hint title that will be displayed if value is invalid.

Is this article helpful? Yes / No
Thank you for your feedback!

Give article feedback

Tell us how we can improve this article

close
Dummy