How to programmatically evaluate Excel data validations using .NET?

0. State of the art (as 11/22/2023)

The NPOI.SS.Formula.DataValidationEvaluator class seems to mirror Apache POI's DataValidationEvaluator class, but NPOI's version only implemented the IsType method thus far. (For reference, here's Apache POI's isType.)

There also seems to be no .NET Excel project that natively supports this yet.

1. Challenges

1.1 Many validation types

There are at least 7 data validation types in Excel¹, each with its own requirements.

^{[1]: From the Open XML SDK for Microsoft Office: DocumentFormat.OpenXml.Spreadsheet.DataValidation class' Type property values. NPOI provides the int GetValidationType method(?) and the ValidationType "enum" to check int against a specific type.}

Personally, I mostly have to deal with the List type (which is usually used to implement drop-downs), where the possible values can be defined by

• listing the values explicitly (e.g., 'yes', 'no')

  NPOI.XSSF.UserModel.XSSFDataValidationConstraint
    {ExplicitListValues = [|"Yes"; "No"|];
     Formula1 = ""Yes, No"";
     Formula2 = null;
     Operator = 0;}
  

• a cell range reference (e.g., =$E$2:$H$2)

  NPOI.XSSF.UserModel.XSSFDataValidationConstraint 
    {ExplicitListValues = null;
     Formula1 = "$Q$2:$T$2";
     Formula2 = null;
     Operator = 0;}
  

• a named range

  NPOI.XSSF.UserModel.XSSFDataValidationConstraint 
    {ExplicitListValues = null;
     Formula1 = "Status";
     Formula2 = null;
     Operator = 0;}
  

• an Excel function / formula

  NPOI.XSSF.UserModel.XSSFDataValidationConstraint 
    {ExplicitListValues = null;
     Formula1 = "INDIRECT(V7)";
     Formula2 = null;
     Operator = 0;}
  

^{The sample outputs are from dotnet fsi (the F# REPL) using NPOI.}

Here's an example where a drop-down list is created using a named range in column A, and column B's drop-downs are conditionally defined (using INDIRECT) by a list data validation that also uses named ranges.

1.2 Named ranges, built-in and custom functions / formulas

1.2.1 The invocation of formulas / functions and named ranges look the same.

Can you tell just by looking if the invocation =status is a named range or a function? There is no native STATUS function in Excel, so it may be either a custom VBA script or a named range. (At least, NPOI's XSSFWorkbook class has a GetAllNames method, so named ranges can be eliminated fairly easily.)

1.2.2 No way discriminate between custom scripts and built-in functions.

Open XML SDK for Microsoft Office does not provide a way to list built-in functions. As far as I know, most popular .NET Excel projects build on Open XML - and they also have no such feature. (NPOI has no way to list VBA scripts, but one can dig them up using the Open XML SDK by looking for VBAProjectPart instances.)

1.2.3 How to evaluate Excel formulas / functions?

As mentioned above, built-in Excel functions are not part of the Open XML SDK and so one would have to re-implement them in the framework itself. At least, this is how Apache POI's DataValidationEvaluator (source) works: after jumping through a bunch of hoops², eventually FormulaParser.parse (source) will get called, and the validation evaluator will apply the subset of Excel functions implemented in Java or throw an error.

^{[2]: DataValidationEvaluator [ isValidCell -> isValid -> isValidValue -> getValidationValuesForConstraint]
-> WorkbookEvaluator [ evaluateList -> evaluate ]
-> FormulaParser [ parse ]}

The same applies to custom VBA scripts as well. They can be set using Open XML SDK, but that's it.

NPOI has a way to re-calculate formulas, (F# again, sorry)

let recalculateFormulas (workbook: XSSFWorkbook) =
    let creationHelper = workbook.GetCreationHelper()
    let formulaEvaluator = creationHelper.CreateFormulaEvaluator()
    formulaEvaluator.EvaluateAll()

but this will only evaluate formula-type cells, and not the formulas embedded into data validations (see IDataValidationConstraint).

posted 12 months ago

CC0

12mo ago

toraritte‭

640 reputation 32 16 99 53