New to Kendo UI for jQuery? Start a free 30-day trial

Sheets.rows.cells.validation

configuration

The validation rule that is applied to the cell.

sheets.rows.cells.validation

Object

<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.allowNulls

Boolean

Specifies whether to allow null values.

<div id="spreadsheet"></div>
<script>
$("#spreadsheet").kendoSpreadsheet({
    sheets: [{
        name: "MySheet",
        rows: [{
            cells: [{
                validation: {
                    dataType: "number",
                    comparerType: "greaterThan",
                    from: "0",
                    allowNulls: true
                }
            }]
        }]
    }]
});
</script>

sheets.rows.cells.validation.comparerType

String

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

<div id="spreadsheet"></div>
<script>
$("#spreadsheet").kendoSpreadsheet({
    sheets: [{
        name: "MySheet",
        rows: [{
            cells: [{
                value: 15,
                validation: {
                    dataType: "number",
                    comparerType: "between",
                    from: 10,
                    to: 20
                }
            }]
        }]
    }]
});
</script>

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

<div id="spreadsheet"></div>
<script>
$("#spreadsheet").kendoSpreadsheet({
    sheets: [{
        name: "MySheet",
        rows: [{
            cells: [{
                value: "test@example.com",
                validation: {
                    dataType: "custom",
                    from: "AND(EXACT(FIND(\"@\",A1),FIND(\"@\",SUBSTITUTE(A1,\"@\",\"\",ROW(INDIRECT(\"1:\"&LEN(A1)-LEN(SUBSTITUTE(A1,\"@\",\"\"))))))),ISERROR(FIND(\" \",A1)))"
                }
            }]
        }]
    }]
});
</script>

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.

<div id="spreadsheet"></div>
<script>
$("#spreadsheet").kendoSpreadsheet({
    sheets: [{
        name: "MySheet",
        rows: [{
            cells: [{
                value: 8,
                validation: {
                    dataType: "number",
                    comparerType: "greaterThan",
                    from: "5"
                }
            }]
        }]
    }]
});
</script>

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}

<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: "https://demos.telerik.com/service/v2/core/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.showButton

Boolean

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

Default: false

<div id="spreadsheet"></div>
<script>
$("#spreadsheet").kendoSpreadsheet({
    sheets: [{
        name: "MySheet",
        rows: [{
            cells: [{
                validation: {
                    dataType: "list",
                    from: "A,B,C",
                    showButton: true
                }
            }]
        }]
    }]
});
</script>

sheets.rows.cells.validation.titleTemplate

String

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

<div id="spreadsheet"></div>
<script>
$("#spreadsheet").kendoSpreadsheet({
    sheets: [{
        name: "MySheet",
        rows: [{
            cells: [{
                value: 5,
                validation: {
                    dataType: "number",
                    comparerType: "greaterThan",
                    from: "10",
                    titleTemplate: "Value must be greater than {0}"
                }
            }]
        }]
    }]
});
</script>

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.

<div id="spreadsheet"></div>
<script>
$("#spreadsheet").kendoSpreadsheet({
    sheets: [{
        name: "MySheet",
        rows: [{
            cells: [{
                value: 15,
                validation: {
                    dataType: "number",
                    comparerType: "between",
                    from: "10",
                    to: "20"
                }
            }]
        }]
    }]
});
</script>

sheets.rows.cells.validation.type

String

Defines the validation type.

The supported options are:

reject
warning (default)

<div id="spreadsheet"></div>
<script>
$("#spreadsheet").kendoSpreadsheet({
    sheets: [{
        name: "MySheet",
        rows: [{
            cells: [{
                value: 10,
                validation: {
                    type: "reject",
                    dataType: "number",
                    comparerType: "greaterThan",
                    from: 5
                }
            }]
        }]
    }]
});
</script>