-
Notifications
You must be signed in to change notification settings - Fork 275
Data Validation Examples
There are 8 types of data validation
Lacks any type of formula but allows for adding tooltips to cells (As do all other . Can be used like this for example:
using (ExcelPackage package = new ExcelPackage())
{
var ws = package.Workbook.Worksheets.Add("test");
var anyValidation = ws.DataValidations.AddAnyValidation("A1");
anyValidation.ShowInputMessage = true;
anyValidation.PromptTitle = "A Popup";
anyValidation.Prompt = "Put info about what's in this field or fields here without restricting it.";
package.SaveAs("C:/temp/AnyExample.xlsx");
}
Validation of whole(int) values as defined by Operator and Formula and in some cases Formula2
using (ExcelPackage package = new ExcelPackage())
{
var ws = package.Workbook.Worksheets.Add("test");
var intValidation = ws.DataValidations.AddIntegerValidation("A1");
intValidation.Operator = ExcelDataValidationOperator.lessThanOrEqual;
intValidation.Formula.Value = 5;
intValidation.ShowErrorMessage = true;
intValidation.ErrorTitle = "Input invalid in Data Validation";
intValidation.Error = "Value must be 5 or less!";
package.SaveAs("C:/temp/WholeExampleSingle.xlsx");
}
With Operators Between or NotBetween you have to specify a second value via formula2
using (ExcelPackage package = new ExcelPackage())
{
var ws = package.Workbook.Worksheets.Add("test");
var intValidation = ws.DataValidations.AddIntegerValidation("A1");
intValidation.Operator = ExcelDataValidationOperator.ExcelDataValidationOperator.between;
intValidation.Formula.Value = 5;
intValidation.Formula2.Value = 10;
intValidation.ShowErrorMessage = true;
intValidation.ErrorTitle = "Input invalid in Data Validation";
intValidation.Error = "Value must between 5 and 10!";
package.SaveAs("C:/temp/WholeExampleBetween.xlsx");
}
Same as Whole but with decimal values e.g "1.5" note that data type is double. To add one same as above except:
ws.DataValidations.AddDecimalValidation("A1")
Defines a list of values with optional dropdown.
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";
validation.Formula.ExcelFormula = "B2:B4";
}
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");
}
Used to limit dates. Datatype is DateTime.
Following example shows how to add a dateTime with greaterThan operator. DateTime like other validations can also have Between and NotBetween operators which requires two values.
using (ExcelPackage package = new ExcelPackage())
{
var ws = package.Workbook.Worksheets.Add("test");
var dateValidation = ws.DataValidations.AddDateTimeValidation("B1");
dateValidation.Operator = ExcelDataValidationOperator.greaterThan;
DateTime date = new DateTime(1997, 05, 01);
dateValidation.Formula.Value = date;
dateValidation.ShowErrorMessage = true;
dateValidation.ErrorTitle = "Input invalid in Data Validation";
dateValidation.Error = $"Date must be after {date}";
package.SaveAs("C:/temp/DateTimeExample.xlsx");
}
Used to add time restrictions. The example below shows how to add a validation that ensures entered time must be later than 14:15. Note that the value of Formula can have properties changed directly here unlike most other validations.
using (ExcelPackage package = new ExcelPackage())
{
var ws = package.Workbook.Worksheets.Add("test");
var timeValidation = ws.DataValidations.AddTimeValidation("C1");
timeValidation.Operator = ExcelDataValidationOperator.greaterThan;
timeValidation.Formula.Value.Hour = 14;
timeValidation.Formula.Value.Minute = 15;
timeValidation.ShowErrorMessage = true;
timeValidation.ErrorTitle = "Input invalid in Data Validation";
timeValidation.Error = $"Time must be after {timeValidation.Formula.Value}";
package.SaveAs("C:/temp/TimeExample.xlsx");
}
Same as Whole Validation but excel will count characters of the text instead with the provided values.
Created with ws.DataValidations.AddTextLengthValidation("A1)
Allows for custom defined formulas for data validation.
The following example shows an example of adding a custom validation to cells A1 to A5 that only accepts numbers
using (ExcelPackage package = new ExcelPackage())
{
var ws = package.Workbook.Worksheets.Add("test");
var customValidation = ws.DataValidations.AddCustomValidation("A1:A5");
customValidation.Formula.ExcelFormula = "ISNUMBER(A1)";
customValidation.ShowErrorMessage = true;
customValidation.ErrorTitle = "Input invalid in Data Validation";
customValidation.Error = $"Value in cell must be number!!";
package.SaveAs("C:/temp/CustomExample.xlsx");
}
EPPlus Software AB - https://epplussoftware.com
- Getting Started
- The Sample Project
- EPPlus and Docker
- EPPlus and Blazor
- EPPlus and Linux
- Security
- Configuration
- Releases/versioning
- What is new in EPPlus 5+
- Breaking Changes in EPPlus 5
- Breaking Changes in EPPlus 6
- Breaking Changes in EPPlus 7
- Addressing a worksheet
- Dimension/Used range
- Copying ranges/sheets
- Insert/Delete
- Filling ranges
- Sorting ranges
- Taking and skipping columns/rows
- Data validation
- Comments
- Freeze and Split Panes
- Header and Footer
- Autofit columns
- Grouping and Ungrouping Rows and Columns
- Formatting and styling
- Conditional formatting
- Using Themes
- Working with custom named table- or slicer- styles