Validate Data in Spreadsheet Cells
- 6 minutes to read
Data validation ensures that a cell only accepts valid data. When users enter invalid data, a message can be displayed that explains why the value is invalid and/or how to correct the error.
The DataValidation interface contains the data validation rule information - the Range in which the rule is in effect, the ValidationType, and other options which can be used while validating data.
The DataValidationCollection collection contains all data validation rules in a worksheet. Access the collection via the Worksheet.DataValidations property.
Add New Data Validation Rule
Use the DataValidationCollection.Add method to add a new data validation rule.
The table below lists API that you can use to specify the validation type.
| Task | API Members |
|---|---|
| Restrict data to predefined items in a list. | DataValidationType.List |
| Restrict numbers outside a specified range. | DataValidationType.WholeNumber DataValidationType.Decimal DataValidationOperator.Between |
| Restrict dates and times outside a certain time frame. | DataValidationType.Time DataValidationOperator.LessThanOrEqual |
| Limit the number of text characters. | DataValidationType.TextLength DataValidationOperator.Equal |
| Validate data based on formulas or values in other cells. | DataValidationType.Custom |
| Create a message that explains what data can be entered in a cell. | InputTitle InputMessage ShowInputMessage |
| Create a message that appears when the user types incorrect data. | ErrorTitle ErrorMessage ErrorStyle ShowErrorMessage |
| Check whether the cell contains valid data. | DataValidationCollection.Validate |
| Retrieve cells that do not meet the data validation criteria. | DataValidationCollection.GetInvalidCells |
The code snippet below illustrates how to create and apply different validation criteria:
Note
The maximum length of a list used to specify the DataValidationType.List validation type or a custom validation criteria is 255 characters. When a list or criteria exceeds this value, an InvalidOperationException is thrown.
workbook.LoadDocument("Documents\\DataValidation.xlsx");
Worksheet worksheet = workbook.Worksheets[0];
worksheet["C1"].SetValue(DateTime.Now);
worksheet["C1"].NumberFormat = "mmm/d/yyyy h:mm";
// Restrict data entry to a whole number from 10 to 20.
worksheet.DataValidations.Add(worksheet["B1"], DataValidationType.WholeNumber, DataValidationOperator.Between, 10, 20);
// Restrict data entry to a number within limits.
DataValidation validation = worksheet.DataValidations.Add(worksheet["F4:F11"], DataValidationType.Decimal, DataValidationOperator.Between, 10, 40);
// Restrict data entry using criteria calculated by a worksheet formula.
worksheet.DataValidations.Add(worksheet["B4:B11"], DataValidationType.Custom, "=AND(ISNUMBER(B4),LEN(B4)=5)");
// Restrict data entry to 3 symbols.
worksheet.DataValidations.Add(worksheet["D4:D11"], DataValidationType.TextLength, DataValidationOperator.Equal, 3);
// Restrict data entry to values in a drop-down list specified in code.
// Note that the list in code should always use comma to separate entries,
// but the list in UI is displayed using culture-specific list separator.
worksheet.DataValidations.Add(worksheet["A4:A11"], DataValidationType.List, "PASS, FAIL");
// Restrict data entry to values in a drop-down list obtained from a worksheet.
worksheet.DataValidations.Add(worksheet["E4:E11"], DataValidationType.List, ValueObject.FromRange(worksheet["H4:H9"].GetRangeWithAbsoluteReference()));
// Restrict data entry to a time before the specified time.
worksheet.DataValidations.Add(worksheet["C1"], DataValidationType.Time, DataValidationOperator.LessThanOrEqual, DateTime.Now);
// Highlight data validation ranges.
worksheet["H4:H9"].FillColor = Color.LightGray;
int[] MyColorScheme = new int[] { 0xFFC4C4, 0xFFD9D9, 0xFFF6F6, 0xFFECEC, 0xE9D3D3, 0xFFDFC4, 0xFFDAE9};
for (int i = 0; i < worksheet.DataValidations.Count; i++){
worksheet.DataValidations[i].Range.FillColor = Color.FromArgb(MyColorScheme[i]);
}
Obtain Data Validation Rules
Use the DataValidationCollection.GetDataValidation method to retrieve data validation rules for the specified cell. The DataValidationCollection.GetDataValidations method obtains data validation entries for cells in the specified range or that meet certain criteria.
workbook.LoadDocument("Documents\\DataValidation.xlsx");
Worksheet worksheet = workbook.Worksheets[0];
// Add data validations.
worksheet.DataValidations.Add(worksheet["D4:D11"], DataValidationType.TextLength, DataValidationOperator.Equal, 3);
worksheet.DataValidations.Add(worksheet["E4:E11"], DataValidationType.List, ValueObject.FromRange(worksheet["H4:H9"].GetRangeWithAbsoluteReference()));
// Get data validation entry associated with a particular cell.
worksheet.DataValidations.GetDataValidation(worksheet.Cells["E4"]).Criteria = ValueObject.FromRange(worksheet["H4:H5"]);
// Get data validation entries for the specified range.
var myValidation = worksheet.DataValidations.GetDataValidations(worksheet["D4:E11"])
.Where(d => d.ValidationType == DataValidationType.TextLength).SingleOrDefault();
if (myValidation != null) myValidation.Criteria = 4;
// Get data validation entries that meet certain criteria.
foreach (var d in worksheet.DataValidations.GetDataValidations(DataValidationType.TextLength, DataValidationOperator.Equal, 4, ValueObject.Empty))
{
// Change criteria operator.
// Range D4:D11 should contain text with more than 4 characters.
d.Operator = DataValidationOperator.GreaterThan;
}
// Highlight data validation ranges.
int[] MyColorScheme = new int[] { 0xFFC4C4, 0xFFD9D9, 0xFFF6F6, 0xFFECEC, 0xE9D3D3 };
for (int i = 0; i < worksheet.DataValidations.Count; i++)
{
worksheet.DataValidations[i].Range.FillColor = Color.FromArgb(MyColorScheme[i]);
}
Remove Validation Rules
To cancel a particular rule, remove it from the collection. To remove all data validation rules, clear the collection.
workbook.LoadDocument("Documents\\DataValidation.xlsx");
Worksheet worksheet = workbook.Worksheets[0];
// Add data validations.
worksheet.DataValidations.Add(worksheet["D4:D11"], DataValidationType.TextLength, DataValidationOperator.Equal, 3);
worksheet.DataValidations.Add(worksheet["E4:E11"], DataValidationType.List, ValueObject.FromRange(worksheet["H4:H9"].GetRangeWithAbsoluteReference()));
// Remove data validation by index.
worksheet.DataValidations.RemoveAt(1);
// Highlight data validation ranges.
int[] MyColorScheme = new int[] { 0xFFC4C4, 0xFFD9D9, 0xFFF6F6, 0xFFECEC, 0xE9D3D3 };
for (int i = 0; i < worksheet.DataValidations.Count; i++)
{
worksheet.DataValidations[i].Range.FillColor = Color.FromArgb(MyColorScheme[i]);
}
Exclude Cell Range from Data Validation
Access the range in which the rule is in effect, call the CellRange.Exclude method with the range you want to exclude as a parameter, and reassign the range returned with the Exclude method to the Range property.
var validations = worksheet.DataValidations.GetDataValidations(worksheet.Selection);
foreach (DataValidation validation in validations)
{
CellRange updatedRange = validation.Range.Exclude(worksheet.Selection);
if (updatedRange != null)
validation.Range = updatedRange;
else
worksheet.DataValidations.Remove(validation);
}