# Validation

The validation functionality of the Telerik UI Spreadsheet component for ASP.NET Core is achieved by applying data validation.

The supported data validation settings include:

* [Date validation](https://www.telerik.com/aspnet-core-ui/documentation/html-helpers/data-management/spreadsheet/validation#date-validation)
* [List validation](https://www.telerik.com/aspnet-core-ui/documentation/html-helpers/data-management/spreadsheet/validation#list-validation)
* [Text validation](https://www.telerik.com/aspnet-core-ui/documentation/html-helpers/data-management/spreadsheet/validation#text-validation)
* [Number validation](https://www.telerik.com/aspnet-core-ui/documentation/html-helpers/data-management/spreadsheet/validation#number-validation)
* [Custom validation](https://www.telerik.com/aspnet-core-ui/documentation/html-helpers/data-management/spreadsheet/validation#custom-validation)

## Date Validation

To configure date validation for a cell, set the Validation.DataType() configuration to `date`. Set the additional configuration options per your requirement. This allows the user to enter a date in a cell by clicking a calendar icon and selecting the date from a calendar rather than typing it. The following example demonstrates how to set up date validation by using the built-in calendar:

```Razor HtmlHelper
    @(Html.Kendo().Spreadsheet()
            .Name("spreadsheet")
            .Sheets(sheets =>
            {
                sheets.Add()
                    .Rows(rows =>
                    {
                        rows.Add().Cells(cells =>
                        {
                            cells.Add()
                                .Format("MMMM d, yyyy")
                                .Validation(validation => validation
                                    .DataType("date")
                                    .ShowButton(true)
                                    .ComparerType("between")
                                    .From("DATEVALUE(\"1/1/1900\")")
                                    .To("DATEVALUE(\"1/1/2100\")")
                                    .AllowNulls(true)
                                    .Type("reject")
                                    .TitleTemplate("Selected Date validation error")
                                    .MessageTemplate("Selected Date should be between year 1900 and 2100.")
                                    );
                        });
                    });
            })
    )
```

```Razor TagHelper
    @{
        var from = "DATEVALUE(\"1/1/1900\")";
        var to = "DATEVALUE(\"1/1/2100\")";
    }
    <kendo-spreadsheet name="spreadsheet">
        <sheets>
            <sheet>
                <rows>
                    <sheet-row>
                        <cells>
                            <cell>
                                <validation datatype="date"
                                    show-button="true"
                                    comparer-type="between"
                                    from="@from" to="@to"
                                    allow-nulls="true"
                                    message-template="Selected Date should be between year 1900 and 2100."
                                    title-template="Selected Date validation error"
                                    type="reject"/>
                            </cell> 
                        </cells>
                    </sheet-row>
                </rows>
            </sheet>
        </sheets>
    </kendo-spreadsheet>
```

## List Validation

To configure list validation for a cell, set the Validation.DataType() configuration to `list`. This way, you enable the user to select an option from a list of predefined options. The following example demonstrates how to set up list validation:

```Razor HtmlHelper
    @(Html.Kendo().Spreadsheet()
            .Name("spreadsheet")
            .Sheets(sheets =>
            {
                sheets.Add()
                    .Rows(rows =>
                    {
                        rows.Add().Cells(cells =>
                        {
                            cells.Add()
                                .Format("MMMM d, yyyy")
                                .Validation(validation => validation
                                    .DataType("list")
                                    .ShowButton(true)
                                    .ComparerType("list")
                                    .From("\"Option 1,Option 2,Option 3\"")
                                    .AllowNulls(true)
                                    .Type("reject")
                                    );
                        });
                    });
            })
    )
```

```Razor TagHelper
    @{
        var from = "\"Option 1,Option 2,Option 3\"";
    }
    <kendo-spreadsheet name="spreadsheet">
        <sheets>
            <sheet>
                <rows>
                    <sheet-row>
                        <cells>
                            <cell>
                                <validation datatype="list" show-button="true" comparer-type="list" from="@from" allow-nulls="true"
                                type="reject"/>
                            </cell> 
                        </cells>
                    </sheet-row>
                </rows>
            </sheet>
        </sheets>
    </kendo-spreadsheet>
```

## Text Validation

To configure text validation for a cell, set the Validation.DataType() configuration to `text`. This way, you can ensure that specific text is entered in a cell. The following example demonstrates how to set-up text validation:

```Razor HtmlHelper
    @(Html.Kendo().Spreadsheet()
            .Name("spreadsheet")
            .Sheets(sheets =>
            {
                sheets.Add()
                    .Rows(rows =>
                    {
                        rows.Add().Cells(cells =>
                        {
                            cells.Add()
                                .Validation(validation => validation
                                    .DataType("text")
                                    .ShowButton(true)
                                    .ComparerType("equalTo")
                                    .From("\"YES\"")
                                    .AllowNulls(false)
                                    .Type("reject")
                                    .MessageTemplate("Enter YES (capital letters)")
                                    );
                        });
                    });
            })
    )
```

```Razor TagHelper
    @{
        var from = "\"Yes\"";
    }
    <kendo-spreadsheet name="spreadsheet">
        <sheets>
            <sheet>
                <rows>
                    <sheet-row>
                        <cells>
                            <cell>
                                <validation datatype="text" show-button="true" comparer-type="equalTo" from="@from" allow-nulls="false"
                                    message-template="Enter YES (capital letters)" type="reject"/>
                            </cell> 
                        </cells>
                    </sheet-row>
                </rows>
            </sheet>
        </sheets>
    </kendo-spreadsheet>
```

> When setting the text to compare to, always add the text in quotes and escape the internal quotes, for example, `.From("\"YES\"")`.

## Number Validation

To configure number validation for a cell, set the Validation.DataType() configuration to `number`. This way, you can ensure that a numeric value is entered in a cell and it matches the provided requirements. The following example demonstrates how to set-up number validation for a number in a specified range:

```Razor HtmlHelper
    @(Html.Kendo().Spreadsheet()
            .Name("spreadsheet")
            .Sheets(sheets =>
            {
                sheets.Add()
                    .Rows(rows =>
                    {
                        rows.Add().Cells(cells =>
                        {
                            cells.Add()
                                .Validation(validation => validation
                                    .DataType("number")
                                    .ShowButton(true)
                                    .ComparerType("between")
                                    .From("1")
                                    .To("10")
                                    .AllowNulls(false)
                                    .Type("reject")
                                    .MessageTemplate("Only Numbers between 1 and 10 are allowed"));
                        });
                    });
            })
    )
```

```Razor TagHelper
    <kendo-spreadsheet name="spreadsheet">
        <sheets>
            <sheet>
                <rows>
                    <sheet-row>
                        <cells>
                            <cell>
                                <validation datatype="number" show-button="true" comparer-type="between" from="1" to="10" allow-nulls="false"
                                    message-template="Only Numbers between 1 and 10 are allowed" type="reject"/>
                            </cell> 
                        </cells>
                    </sheet-row>
                </rows>
            </sheet>
        </sheets>
    </kendo-spreadsheet>
```

## Custom Validation

To configure validation for a cell based on a formula, set the Validation.DataType() configuration to `custom`. This way, you can provide the formula based on which validation will be performed. The following example demonstrates how to set up custom validation for a cell:

```Razor HtmlHelper
    @(Html.Kendo().Spreadsheet()
            .Name("spreadsheet")
            .Sheets(sheets =>
            {
                sheets.Add()
                    .Rows(rows =>
                    {
                        rows.Add().Cells(cells =>
                        {
                            cells.Add()
                                .Validation(validation => validation
                                    .DataType("custom")
                                    .From("AND(LEN(A3)>3, LEN(A3)<200)")
                                    .AllowNulls(true)
                                    .Type("reject")
                                    .TitleTemplate("Full Name validation error")
                                    .MessageTemplate("The full name should be longer than 3 letters and shorter than 200."));
                        });
                    });
            })
    )
```

```Razor TagHelper
    @{
        var from = "AND(LEN(A3)>3, LEN(A3)<200)";
    }
    <kendo-spreadsheet name="spreadsheet">
        <sheets>
            <sheet>
                <rows>
                    <sheet-row>
                        <cells>
                            <cell>
                                <validation datatype="custom"
                                    from="@from"
                                    allow-nulls="true"
                                    message-template="Full Name validation error"
                                    title-template="The full name should be longer than 3 letters and shorter than 200."
                                    type="reject"/>
                            </cell> 
                        </cells>
                    </sheet-row>
                </rows>
            </sheet>
        </sheets>
    </kendo-spreadsheet>
```

## See Also

* [Validation of the Spreadsheet HtmlHelper for ASP.NET Core (Demo)](https://demos.telerik.com/aspnet-core/spreadsheet/validation)
* [Server-Side API](https://www.telerik.com/aspnet-core-ui/documentation/api/spreadsheet)