sheets.rows.cells.validationObject
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.typeString
Defines the validation type.
The supported options are:
rejectwarning(default)
Example
<div id="spreadsheet"></div>
<script>
$("#spreadsheet").kendoSpreadsheet({
sheets: [{
name: "MySheet",
rows: [{
cells: [{
value: 10,
validation: {
type: "reject",
dataType: "number",
comparerType: "greaterThan",
from: 5
}
}]
}]
}]
});
</script>sheets.rows.cells.validation.comparerTypeString
Defines the comparer type that is used to validate the cell value.
Example
<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:
greaterThanlessThanbetweenequalTonotEqualTogreaterThanOrEqualTolessThanOrEqualTonotBetweencustom
sheets.rows.cells.validation.dataTypeString
Defines the data type of the cell value.
The supported values are:
datetextnumberlistcustom
Example
<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.fromString
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.
Example
<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.showButtonBoolean(default: false)
A Boolean value which indicates if a button for selecting list items will be displayed (dataType set to list).
Example
<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.toString
Defines a formula or a value that is used for the comparison process. Will be used if the comparer type requires a second argument.
Example
<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.allowNullsBoolean
Specifies whether to allow null values.
Example
<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.messageTemplateString
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: "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.titleTemplateString
Defines the hint title that will be displayed if the value is invalid.
Example
<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>In this article