[]
        
(Showing Draft Content)

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 GcExcel .NET.

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.

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

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

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

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

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

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

//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");