Data validation Exceptions

When working with Data validations EPPlus can under some circumstances throw some specialiced Exceptions. These Exceptions resides in the OfficeOpenXml.DataValidation.Exceptions namespace.

DataValidationStaleException

This Exception is introduced in EPPlus 5.6.1. It inherits System.InvalidOperationException.

As you might know EPPlus saves workbooks in the Office Open XML format (which is also used by various spreadsheet applications such as Excel). In this format the formulas of data validations can only refer to the worksheet where they resides. To allow the formulas to refer to other worksheets the data validation must be moved from one part of the xml structure to another. Currently EPPlus supports this for two data validation types: List and Custom validations.

What happens behind the scenes is that when EPPlus discovers that a formula refers to another worksheet it moves the data validation to another place in the xml. When this happens the data validation you have a reference to might become stale. This should be resolved by always setting the ExcelFormula property of the data validations last. When EPPlus detects that a property is changed on a stale data validation it will throw a DataValidationStaleException.

Example

This will throw a DataValidationStaleException

var sheet1 = package.Workbook.Worksheets.Add("sheet1");
var sheet2 = package.Workbook.Worksheets.Add("sheet2");

var v = sheet1.Cells["A1"].DataValidation.AddListDataValidation();
// the following statement makes EPPlus to internally move the DataValidation
// and the variable v will from then on be in a stale state.
v.Formula.ExcelFormula = "sheet2!A1:A2";
// when the following line executes the DataValidationStaleException will be thrown
v.ShowErrorMessage = true;

This is how it should be resolved

var v = sheet1.Cells["A1"].DataValidation.AddListDataValidation();
// set ShowErrorMessage (or any other property on the validation) before you set the ExcelFormula
v.ShowErrorMessage = true;
v.Formula.ExcelFormula = "sheet2!A1:A2";

DataValidationFormulaTooLongException

This Exception was introduced in EPPlus 5.6.1, in previous versions this was an System.InvalidOperationException. The DataValidationFormulaTooLongException inherits the System.InvalidOperationException for backward compatibility.

The length of a data validation formula must not exceed 255 characters.