Data validation Exceptions

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

DataValidationStaleException

NB! Below is only relevant for EPPlus versions below 6.2. The DataValidationStaleException will not be thrown in newer version.

This Exception is introduced in EPPlus 5.6.2. 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 5 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";

You can also use the data validations IsStale property and reload it from the worksheet if needed:

var v = sheet1.Cells["A1"].DataValidation.AddListDataValidation();
v.Formula.ExcelFormula = "extlist_sheet2!A1:A2";
if(v.IsStale)
{
    // reload the data validation from the workbook
    v = sheet1.DataValidations.Find(x => x.Uid == v.Uid).As.ListValidation;
}
v.ShowErrorMessage = true;

Please note that the above only is relevant if your formula changes from refering within the same worksheet to refering to another worksheet or vice versa. But we recommend to always set the ExcelFormula after any other property to avoid this side effect.

DataValidationFormulaTooLongException

This Exception was introduced in EPPlus 5.6.2, in previous versions this was a System.InvalidOperationException. The DataValidationFormulaTooLongException inherits the System.InvalidOperationException.

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

See also

• Data validation