Click or drag to resize

Data Validation in Excel Spreadsheets with ExpertXls

The ExpertXls Excel Library for .NET offers support for validating data entered in the worksheet cells. Using this feature you can ensure that the value entered in a cell is from a predefined list of values, a number or date is in a specified range or that the length of a string is in a specified range. The data validation is implemented using the DataValidator property.

Code Samples

The code sample below shows how to add data validation to a worksheet:

Copy
// Validate data from a list

worksheet["A5:E5"].Merge();
worksheet["A5:E5"].Style = textMessageStyle;
worksheet["A5:E5"].Value = "Select a value from the list:";

// set the range to be validated
worksheet["G5"].Style = dataValidationStyle;
worksheet["G5"].ColumnWidthInChars = 25;
worksheet["G5"].AddComment("Click this cell to select a value from list.");

ExcelDataValidator listValidator = worksheet["G5"].DataValidator;
listValidator.AllowedDataType = ExcelDataValidatorDataType.List;
listValidator.AllowedValues = new string[] { "HTML to PDF Converter", "PDF Merge", "PDF Security", "Excel Library for .NET" };
listValidator.InputMessageText = "Select a value from the list";
listValidator.ShowInputMessage = true;

// Validate a whole number between 0 and 10

worksheet["A7:E7"].Merge();
worksheet["A7:E7"].Style = textMessageStyle;
worksheet["A7:E7"].Value = "Enter a whole number between 0 and 10 :";


// set the range to be validated
worksheet["G7"].Style = dataValidationStyle;
worksheet["G7"].ColumnWidthInChars = 25;
worksheet["G7"].AddComment("Click this cell to enter a whole number.");

// Data Validation for Numbers
ExcelDataValidator wholeNumberValidator = worksheet["G7"].DataValidator;
wholeNumberValidator.AllowedDataType = ExcelDataValidatorDataType.WholeNumber;
wholeNumberValidator.Operator = ExcelDataValidatorOperator.Between;
wholeNumberValidator.Value1 = 0;
wholeNumberValidator.Value2 = 10;
wholeNumberValidator.ErrorAlertText = "A number between 0 to 10 is expected";
wholeNumberValidator.ShowErrorAlert = true;
wholeNumberValidator.ErrorAlertTitle = "Whole Number Validation Error";
wholeNumberValidator.InputMessageText = "Enter a whole number between 0 and 10";
wholeNumberValidator.ShowInputMessage = true;

// Validate a date between 01/01/2000 and 12/31/2009

worksheet["A9:E9"].Merge();
worksheet["A9:E9"].Style = textMessageStyle;
worksheet["A9:E9"].Value = "Enter a date between 01/01/2000 and 12/31/2009 :";

// set the range to be validated
worksheet["G9"].Style = dataValidationStyle;
worksheet["G9"].ColumnWidthInChars = 25;
worksheet["G9"].Style.Number.NumberFormatString = "m/d/yyyy";
worksheet["G9"].Value = new DateTime(2008, 12, 15); // default value
worksheet["G9"].AddComment("Double-Click this cell to enter a date in local format.");

ExcelDataValidator dateValidator = worksheet["G9"].DataValidator;
dateValidator.AllowedDataType = ExcelDataValidatorDataType.Date;
dateValidator.Operator = ExcelDataValidatorOperator.Between;
dateValidator.Value1 = new DateTime(2000, 1, 1);
dateValidator.Value2 = new DateTime(2009, 12, 31); ;
dateValidator.ErrorAlertText = "A date between 01/01/2000 and 12/31/2009 is expected";
dateValidator.ShowErrorAlert = true;
dateValidator.ErrorAlertTitle = "Date Validation Error";
dateValidator.InputMessageText = "Enter a date between 01/01/2000 and 12/31/2009";
dateValidator.ShowInputMessage = true;

// Validate the length of a text

worksheet["A11:E11"].Merge();
worksheet["A11:E11"].Style = textMessageStyle;
worksheet["A11:E11"].Value = "Enter a text with length between 2 and 5 chars:";

// set the range to be validated
worksheet["G11"].Style = dataValidationStyle;
worksheet["G11"].ColumnWidthInChars = 25;
worksheet["G11"].AddComment("Click this cell to enter a text.");

ExcelDataValidator textLengthValidator = worksheet["G11"].DataValidator;
textLengthValidator.AllowedDataType = ExcelDataValidatorDataType.TextLength;
textLengthValidator.Operator = ExcelDataValidatorOperator.Between;
textLengthValidator.Value1 = 2;
textLengthValidator.Value2 = 5;
textLengthValidator.ErrorAlertTitle = "Text Length Validation Error";
textLengthValidator.ErrorAlertText = "A text with length between 2 and 5 chars is expected";
textLengthValidator.ShowErrorAlert = true;
textLengthValidator.InputMessageText = "Enter a text with length between 2 and 5 characters";
textLengthValidator.ShowInputMessage = true;