Data Validation in Excel Spreadsheets with ExpertXls |
Data validation restricts the values a user can type in cells when the workbook is edited in Excel. The DataValidator property of a range returns an ExcelDataValidator. Set the allowed data type (ExcelDataValidatorDataType): a list of values, whole numbers, decimal numbers, dates, times or a text length; the operator and the limits; the message Excel shows when a cell is selected; and the error alert Excel shows when the value is not valid.
The error alert style is Stop (the value is rejected), Warning or Information (the user can keep the value).
ExcelDataValidator listValidator = worksheet["G5"].DataValidator; listValidator.AllowedDataType = ExcelDataValidatorDataType.List; listValidator.AllowedValues = new string[] { "HTML to PDF Converter", "PDF Merge", "PDF Security", "Excel Library" }; listValidator.InCellDropDown = true; listValidator.InputMessageTitle = "Product"; listValidator.InputMessageText = "Select a product from the list"; listValidator.ShowInputMessage = true;
// a whole number between 0 and 10; other values are rejected ExcelDataValidator numberValidator = worksheet["G7"].DataValidator; numberValidator.AllowedDataType = ExcelDataValidatorDataType.WholeNumber; numberValidator.Operator = ExcelDataValidatorOperator.Between; numberValidator.Value1 = 0; numberValidator.Value2 = 10; numberValidator.ErrorAlertTitle = "Whole number expected"; numberValidator.ErrorAlertText = "A whole number between 0 and 10 is expected"; numberValidator.ErrorAlertStyle = ExcelDataValidatorErrorStyle.Stop; numberValidator.ShowErrorAlert = true; // a date in 2026 ExcelDataValidator dateValidator = worksheet["G9"].DataValidator; dateValidator.AllowedDataType = ExcelDataValidatorDataType.Date; dateValidator.Operator = ExcelDataValidatorOperator.Between; dateValidator.Value1 = new DateTime(2026, 1, 1); dateValidator.Value2 = new DateTime(2026, 12, 31); dateValidator.ErrorAlertText = "A date in 2026 is expected"; dateValidator.ShowErrorAlert = true; // a text of 2 to 5 characters; the warning lets the user keep a longer text ExcelDataValidator textValidator = worksheet["G11"].DataValidator; textValidator.AllowedDataType = ExcelDataValidatorDataType.TextLength; textValidator.Operator = ExcelDataValidatorOperator.Between; textValidator.Value1 = 2; textValidator.Value2 = 5; textValidator.ErrorAlertStyle = ExcelDataValidatorErrorStyle.Warning; textValidator.ErrorAlertText = "The text should have 2 to 5 characters. Keep it anyway?"; textValidator.ShowErrorAlert = true; // decimal numbers greater than 0 in a range of cells ExcelDataValidator priceValidator = worksheet["G13:G16"].DataValidator; priceValidator.AllowedDataType = ExcelDataValidatorDataType.Decimal; priceValidator.Operator = ExcelDataValidatorOperator.GreaterThan; priceValidator.Value1 = 0; priceValidator.ShowErrorAlert = true;