Data validation¶
Data validations is accessed via the DataValidation property of the ExcelWorksheet class or via the ExcelRange class. The following types of data validations are supported: Integer ("whole" in Excel), Decimal, List, Date, Time, Any and Custom.
Add Data validations¶
Here are some examples on how to add data validation to a worksheet. The following code adds a data validation to a range that only allows integer values between 1 and 5.
private static void AddIntegerValidation(ExcelPackage package)
{
var sheet = package.Workbook.Worksheets.Add("integer");
// add a validation and set values
var validation = sheet.DataValidations.AddIntegerValidation("A1:A2");
// Alternatively:
//var validation = sheet.Cells["A1:A2"].DataValidation.AddIntegerDataValidation();
validation.ErrorStyle = ExcelDataValidationWarningStyle.stop;
validation.PromptTitle = "Enter a integer value here";
validation.Prompt = "Value should be between 1 and 5";
validation.ShowInputMessage = true;
validation.ErrorTitle = "An invalid value was entered";
validation.Error = "Value must be between 1 and 5";
validation.ShowErrorMessage = true;
validation.Operator = ExcelDataValidationOperator.between;
validation.Formula.Value = 1;
validation.Formula2.Value = 5;
}
Private Shared Sub AddIntegerValidation(package As ExcelPackage)
Dim sheet = package.Workbook.Worksheets.Add("integer")
' add a validation and set values
Dim validation = sheet.DataValidations.AddIntegerValidation("A1:A2")
' Alternatively:
'Dim validation = sheet.Cells("A1:A2").DataValidation.AddIntegerDataValidation()
validation.ErrorStyle = ExcelDataValidationWarningStyle.stop
validation.PromptTitle = "Enter a integer value here"
validation.Prompt = "Value should be between 1 and 5"
validation.ShowInputMessage = True
validation.ErrorTitle = "An invalid value was entered"
validation.Error = "Value must be between 1 and 5"
validation.ShowErrorMessage = True
validation.Operator = ExcelDataValidationOperator.between
validation.Formula.Value = 1
validation.Formula2.Value = 5
End Sub
The following code adds a dropdown list of valid options based on an Excel range.
private static void AddListValidationFormula(ExcelPackage package)
{
var sheet = package.Workbook.Worksheets.Add("list formula");
sheet.Cells["B1"].Style.Font.Bold = true;
sheet.Cells["B1"].Value = "Source values";
sheet.Cells["B2"].Value = 1;
sheet.Cells["B3"].Value = 2;
sheet.Cells["B4"].Value = 3;
// add a validation and set values
var validation = sheet.DataValidations.AddListValidation("A1");
// Alternatively:
// var validation = sheet.Cells["A1"].DataValidation.AddListDataValidation();
validation.ShowErrorMessage = true;
validation.ErrorStyle = ExcelDataValidationWarningStyle.warning;
validation.ErrorTitle = "An invalid value was entered";
validation.Error = "Select a value from the list";
//Keep in mind that this is a formula and as such you usually want to use absolute values so it does not change.
validation.Formula.ExcelFormula = "$B$2:$B$4";
}
Private Shared Sub AddListValidationFormula(package As ExcelPackage)
Dim sheet = package.Workbook.Worksheets.Add("list formula")
sheet.Cells("B1").Style.Font.Bold = True
sheet.Cells("B1").Value = "Source values"
sheet.Cells("B2").Value = 1
sheet.Cells("B3").Value = 2
sheet.Cells("B4").Value = 3
' add a validation and set values
Dim validation = sheet.DataValidations.AddListValidation("A1")
' Alternatively:
' Dim validation = sheet.Cells("A1").DataValidation.AddListDataValidation()
validation.ShowErrorMessage = True
validation.ErrorStyle = ExcelDataValidationWarningStyle.warning
validation.ErrorTitle = "An invalid value was entered"
validation.Error = "Select a value from the list"
'Keep in mind that this is a formula and as such you usually want to use absolute values so it does not change.
validation.Formula.ExcelFormula = "$B$2:$B$4"
End Sub
The following code adds a dropdown list of valid options based on a set of values. Note that this option is only for a small amount of values, since Excel has a built in limitation on the length of the field containing the values. If you want to create a data validation with a larger number of options, reference a formula instead as demonstrated above.
private static void AddListValidationValues(ExcelPackage package)
{
var sheet = package.Workbook.Worksheets.Add("list values");
// add a validation and set values
var validation = sheet.DataValidations.AddListValidation("A1");
validation.ShowErrorMessage = true;
validation.ErrorStyle = ExcelDataValidationWarningStyle.warning;
validation.ErrorTitle = "An invalid value was entered";
validation.Error = "Select a value from the list";
for (var i = 1; i <= 5; i++)
{
validation.Formula.Values.Add(i.ToString());
}
Console.WriteLine("Added sheet for list validation with values");
}
Private Shared Sub AddListValidationValues(package As ExcelPackage)
Dim sheet = package.Workbook.Worksheets.Add("list values")
' add a validation and set values
Dim validation = sheet.DataValidations.AddListValidation("A1")
validation.ShowErrorMessage = True
validation.ErrorStyle = ExcelDataValidationWarningStyle.warning
validation.ErrorTitle = "An invalid value was entered"
validation.Error = "Select a value from the list"
For i As Integer = 1 To 5
validation.Formula.Values.Add(i.ToString())
Next
Console.WriteLine("Added sheet for list validation with values")
End Sub
Read existing Data validations from a worksheet¶
foreach (var dataValidation in worksheet.DataValidations)
{
switch(dataValidation.ValidationType.Type)
{
case eDataValidationType.Whole:
var intValidation = dataValidation.As.IntegerValidation;
break;
case eDataValidationType.List:
var listValidation = dataValidation.As.ListValidation;
break;
default:
break;
}
}
Search/Modify¶
The following functions exposed as members of the worksheet.DataValidations property are useful for searching/modifying data validations:
- FindAll(Predicate<IExcelDataValidation> match) - returns all matches
- Find(Predicate<IExcelDataValidation> match) - returns the first match
- Clear() - removes all data validations from the worksheet
- RemoveAll(Predicate<IExcelDataValidation> match) - Removes the validations that matches the predicate
Performance¶
Due to how Data validations are implemented in Excel/Office Open Xml it is always better to use a shared range rather than adding the validations on each row. For example: if you have 50 000 cells and wants to add a data validation on each one of them try - if feasible - to define a common validation for the cells (i.e. Worksheet.AddListValidation("C1:C50000")) rather than setting them per row.
The Validate() Method¶
The design-intent of the .Validate() method is not to Enforce the rule but rather to ensure that the Configuration or "State" of the Validation is correct and to avoid corrupt workbooks,
I.e. A Decimal Validation with the operator "Between" has a value in Formula1 but not in Formula2 Image
This is an Invalid configuration for the dataValidation. There is no way to enforce this rule for a spreadsheet application like Excel because it's not possible to have a value between 1 and "blank". Excel therefore does not allow the data validation to be created.
Validate() is meant to catch these kinds of impossible statements as it could lead to corrupt workbooks.
Why Do We Not Support Enforcing¶
"Enforcing" AKA "Throwing an exception if a user of the API tries to set a value that break a data validation" is not Supported. Currently Epplus does not Enforce data validation rules. There's two main reasons: 1. Data Validation in Excel is a UI operation. As an API we do not have a UI. and as such can Configure the xml the UI uses but we do not directly implement UI logic.
- Performance. To check all Data Validations and Enforce their rules each time a value is set within their range becomes a huge performance drain. Furthermore if the value was set before the datavalidation was applied it is allowed to exist in excel. Which would require the API to track dirty and clean cells. This in worst-case might double the performance cost.
The idea is that Data Validation is a UI-tool to stop an End-User from applying certain values in certain cases. A developer configures the UI-tool(data-validation) using the library. The end-application, reading the xml-file then has the responsibility of Enforcing the configured rule.
It is likely easier to set some kind of restriction in your own application Before you set a value.
While not recommended, Should you wish to implement your own enforcing rules you may have use of the Workbook.FormulaParserManager. Since a Data Validation CAN contain some extremely advanced Excel formulas (though they are often simple addresses) that way you can Calculate() the formulas on the data validations.