# Data Validation Types

There are 8 types of data validation 1. [Any](<#any-validation>) 2. [Whole](<#whole-validation>) 3. [Decimal](<#decimal-validation>) 4. [List](<#list-validation>) 5. [Date](<#date-validation>) 6. [Time](<#time-validation>) 7. [TextLength](<#text-length-validation>) 8. [Custom](<#custom-validation>)

## Any Validation

Lacks any type of formula but allows for adding tooltips to cells (As do all other . Can be used like this for example:

```C#
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");
}
```

## Whole Validation

Validation of whole(int) values as defined by Operator and Formula and in some cases Formula2

```C#
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

```C#
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");
}
```

## Decimal Validation

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")`

## List Validation

Defines a list of values with optional dropdown.

The following code adds a dropdown list of valid options based on an Excel range.

```c#
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 the range
    validation.Formula.ExcelFormula = "$B$2:$B$4";

}
```

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.

```c#
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");

}
```

## Date Validation

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.

```c#
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");
}
```

## Time Validation

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.

```c#
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");
}
```

## Text Length Validation

Same as [Whole Validation](<#whole-validation>) but excel will count characters of the text instead with the provided values.

Created with `ws.DataValidations.AddTextLengthValidation("A1)`

## Custom Validation

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

```c#
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");
}
```

### See also

- [Data validations](<https://epplussoftware.com/docs/worksheet-and-ranges/data-validation/index.md>)