sheets.rows.cells.validation Object

The validation rule that is applied to the cell.

Example - initializing the Spreadsheet with validation data by using the 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 supported options are:

• reject
• warning (default)

sheets.rows.cells.validation.comparerType String

Defines the comparer type that is used to validate the cell value.

The supported values are:

• greaterThan
• lessThan
• between
• equalTo
• notEqualTo
• greaterThanOrEqualTo
• lessThanOrEqualTo
• notBetween
• custom

sheets.rows.cells.validation.dataType String

Defines the data type of the cell value.

The supported values are:

• date
• text
• number
• list
• custom

sheets.rows.cells.validation.from String

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

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

A Boolean value which indicates if a button for selecting list items will be displayed (dataType set to list).

sheets.rows.cells.validation.to String

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

sheets.rows.cells.validation.allowNulls Boolean

Specifies whether to allow null values.

sheets.rows.cells.validation.messageTemplate String

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

The template provides access to the following variables:

• from{0}
• to{1}
• fromFormula{2}
• toFormula{3}
• dataType{4}
• type{5}
• comparerType{6}

Example - using 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 the value is invalid.