Add Validations

Spread.Services Documentation
Add Validations
Collapse All Expand All

Spread.Services Documentation > Developer's Guide > Apply Data Validations > Add Validations

You can use the Add method of the IValidation interface to apply data validation to individual cells or a range of cells in a spreadsheet. A single cell can have only one validation rule and if you try to apply validation on a cell that already possesses a validation rule, it will throw an exception.

Validation rule instance for a range is represented with the Validation property of the IRange interface. If you want to know whether a cell range already contains the validation rule, you can use the HasValidation property of the IRange interface. If all the cells in a range possess the same validation rule applied to them, it is represented with the ValidationIsSame property of the IRange interface.

Shared below is a list of data validations operations that can be implemented in Spread.Services.

- Add Whole Number Validation
- Add Decimal Validation
- Add List Validation
- Add Date Validation
- Add Time Validation
- Add Text Length Validation
- Add Custom Validation

### Add whole number validation

You can validate your data and ensure users add only whole numbers in cells or a range of cells by applying the whole number validation in a worksheet.

Refer to the following example code to add whole number validation.

C# | Copy Code
--- | ---

    //Add whole number validation
    worksheet.Range["A1:A3"].Validation.Add(ValidationType.Whole, ValidationAlertStyle.Stop, ValidationOperator.Between, 1, 8);
    IValidation validation = worksheet.Range["A1:A3"].Validation;
    validation.IgnoreBlank = true;
    validation.InputTitle = "Tips";
    validation.InputMessage = "Input a value between 1 and 8, please";
    validation.ErrorTitle = "Error";
    validation.ErrorMessage = "input value does not between 1 and 8";
    validation.ShowInputMessage = true;
    validation.ShowError = true;

### Add decimal validation

You can validate your data and ensure users add only decimal numbers in cells or a range of cells by applying the decimal validation in a worksheet.

Refer to the following example code to add decimal validation.

C# | Copy Code
--- | ---

    //Add Decimal validation
    worksheet.Range["B1:B3"].Validation.Add(ValidationType.Decimal, ValidationAlertStyle.Stop, ValidationOperator.Between, 3.4, 102.8);

### Add list validation

You can also validate lists inserted in cells or a range of cells by applying the list validation in your worksheet .

Refer to the following example code to add list validation.

C# | Copy Code
--- | ---

    //Add List Validation
    worksheet.Range["C4"].Value = "aaa";
    worksheet.Range["C5"].Value = "bbb";
    worksheet.Range["C6"].Value = "ccc";

    //Use cell reference.
    worksheet.Range["C1:C3"].Validation.Add(ValidationType.List, ValidationAlertStyle.Stop, ValidationOperator.Between, "=c4:c6");

    //Or use string.
    //this._worksheet.Range["C2:E4"].Validation.Add(ValidationType.List, ValidationAlertStyle.Stop, ValidationOperator.Between, "aaa, bbb, ccc");

    //Display list dropdown
    IValidation dvalidation = worksheet.Range["C1:C3"].Validation;
    dvalidation.InCellDropdown = true;

### Add date validation

You can validate data entered in date format in cells or a range of cells by applying the date validation in a worksheet.

Refer to the following example code to add date validation.

C# | Copy Code
--- | ---

    //Add Date validation
    worksheet.Range["D1:D3"].Validation.Add(ValidationType.Date, ValidationAlertStyle.Stop, ValidationOperator.Between, new DateTime(2015, 12, 13), new DateTime(2015, 12, 18));

### Add time validation

You can validate the time entered in cells or a range of cells by applying the time validation in a worksheet.

Refer to the following example code to add time validation.

C# | Copy Code
--- | ---

    //Add Time Validation
    worksheet.Range["E1:E3"].Validation.Add(ValidationType.Time, ValidationAlertStyle.Stop, ValidationOperator.Between, new TimeSpan(13, 30, 0), new TimeSpan(18, 30, 0));

### Add text length validation

You can validate the length of the text entered in cells or a range of cells by applying the text length validation in a worksheet.

Refer to the following example code to add text length validation.

C# | Copy Code
--- | ---

    //Add Text Length Validation
    worksheet.Range["C2:E4"].Validation.Add(ValidationType.TextLength, ValidationAlertStyle.Stop, ValidationOperator.Between, 2, 3);

### Add custom validation

You can add a custom validation rule to validate data in a worksheet by applying custom validation.

Refer to the following example code to add custom validation.

C# | Copy Code
--- | ---

    //Add custom validation
    worksheet.Range["A2"].Value = 1;
    worksheet.Range["A3"].Value = 2;
    worksheet.Range["C2"].Value = 1;
    //when use custom validation, validationOperator and formula2 parameters will be ignored even if you have given.
    worksheet.Range["A2:A3"].Validation.Add(ValidationType.Custom, ValidationAlertStyle.Information, formula1: "=C2");

Copyright © GrapeCity, inc. All rights reserved.