Search code examples
excelxlsxepplus-4

Excel & EPPlus .NET library: Advanced DropDown list validation

In Epplus, when we create a DropDown list for some cells in excel file, then user put a value which is not part of the list, the cell show a message says: value must match one of the listed items.

Instead of this message, Is it possible to prevent the user to put a value which is not part of the drop down list?

Thanks in advance,

Solution

  • I did it with the following code: 

    //ExcelWorksheet ws
var validation = ws.DataValidations.AddListValidation(cell.Address);
//Error handling
validation.ShowErrorMessage = true;
validation.ErrorStyle = ExcelDataValidationWarningStyle.stop;
validation.ErrorTitle = "Error";
validation.Error = "Error Text";
// sheet with a name : DropDownLists 
// from DropDownLists sheet, get values from cells: !$A$1:$A$10
var formula = "=DropDownLists!$A$1:$A$10";
//Applying Formula to the range
validation.Formula.ExcelFormula = formula;