A built-in way to validate a cell value based on Excel data validation constraints?

[tonyqus]

Discussed in #1217

^{Originally posted by toraritte November 21, 2023}
What I would like to do:

1. Set cell value.
2. Check if the cell value conforms to the data validation rules referencing that cell.

I could extract all the information needed to create a validation function, but don't want to re-invent the wheel. I didn't find the NPOI docs yet (is it the source?), and based on my experiments, there is no such function, but perhaps I was looking in the wrong places.

Apache POI's XSSFDataValidationConstraint class does have a validate method, but the docs are not much help and I don't know Java:

validate
public void validate()

---

edit: Just found Apache POI's DataValidationEvaluator class and I presume that NPOI.SS.Formula.DataValidationEvaluator is the corresponding NPOI class. Am I on the right track?

[toraritte]

These are the challenges I've found, and this is how Apache POI does it:

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][2] (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).