How does Validate method of EPPlus work?

Question

I'm using EPPlus as a calculations server. Here is my code:

using (var xlp = new ExcelPackage(stream))
{
OfficeOpenXml.ExcelWorksheet Sheet = xlp.Workbook.Worksheets["sheet1"];
//Some code for feeding user data to excel sheet
//...

//We first invoke calculate method to let contraints of data validation get updated.
xlp.Workbook.Calculate();   

var v = Sheet.DataValidations["A1"];
if (v != null)
{
    switch (v.ValidationType.Type)
    {
        case OfficeOpenXml.DataValidation.eDataValidationType.DateTime:     
            OfficeOpenXml.DataValidation.ExcelDataValidationDateTime V1 = (OfficeOpenXml.DataValidation.ExcelDataValidationDateTime)v;
            try
            {
                //this line doesn't do any thing
                V1.Validate();
            }
            catch
            {
            }           
        break;
        case ...
    }
}
}

I had read somewhere that Validate() method throws exception for invalid data. It doesn't. My question: How to use the Validate() method?

Answer 1

That would depend on what the content of the cell and the settings of the validator's Operator:

http://epplus.codeplex.com/SourceControl/latest#EPPlus/DataValidation/ExcelDataValidationOperator.cs

/// <summary>
/// Operator for comparison between Formula and Formula2 in a validation.
/// </summary>
public enum ExcelDataValidationOperator
{
    any,
    equal,
    notEqual,
    lessThan,
    lessThanOrEqual,
    greaterThan,
    greaterThanOrEqual,
    between,
    notBetween
}

The ExcelDataValidationDateTime (eventually) derives from ExcelDataValidationWithFormula<IExcelDataValidationFormulaDateTime> which contains the implemenation of Validate():

http://epplus.codeplex.com/SourceControl/latest#EPPlus/DataValidation/ExcelDataValidationWithFormula.cs

public override void Validate()
{
    base.Validate();
    if (Operator == ExcelDataValidationOperator.between || Operator == ExcelDataValidationOperator.notBetween)
    {
        if (string.IsNullOrEmpty(Formula2Internal))
        {
            throw new InvalidOperationException("Validation of " + Address.Address + " failed: Formula2 must be set if operator is 'between' or 'notBetween'");
        }
    }
}

So it will throw an exception (invalidate) when the validation operation is either ExcelDataValidationOperator.between or ExcelDataValidationOperator.notBetween and Forumla2 is not set (not to be confused with the primary Formula). In other words, it considers the validator invalid when you are using an operation which requires TWO values/formulas to compare but only one is set.