Post History

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

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

There also seems to be [no .NET Excel project that natively supports this yet](https://stackoverflow.com/a/77518267/1498178).</sup>

### 1. Challenges

#### 1.1 Many validation types

There are at least [7 data validation types][4] in Excel<sup><b>1</b></sup>, each with its own requirements.

<sup>\[1]: From the Open XML SDK for Microsoft Office: [`DocumentFormat.OpenXml.Spreadsheet.DataValidation`][5] class' [`Type`][6] property [values][7].</sup>

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][8]

  ```
  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;}
  ```

<sup>The sample outputs are from `dotnet fsi` (the F# REPL) using NPOI.</sup>

[Here's an example][9] 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`][10]) 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][8]** 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`][11] method, so named ranges can be eliminated fairly easily.)

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

[Open XML SDK for Microsoft Office][12] 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][12] by looking for [`VBAProjectPart`][13] instances.)

##### 1.2.3 How to evaluate Excel formulas / function?

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

<sup>\[2]: [`DataValidationEvaluator`][14] [ `isValidCell` -> `isValid` -> `isValidValue` -> `getValidationValuesForConstraint`]<br> -> [`WorkbookEvaluator`][18] [ `evaluateList` -> `evaluate` ]<br> -> [`FormulaParser`][16] [ `parse` ]</sup>

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

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

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

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


  [1]: https://github.com/nissl-lab/npoi/blob/master/main/SS/Formula/DataValidationEvaluator.cs
  [2]: https://poi.apache.org/apidocs/dev/org/apache/poi/ss/formula/DataValidationEvaluator.html
  [3]: https://poi.apache.org/apidocs/dev/org/apache/poi/ss/formula/DataValidationEvaluator.html#isType-org.apache.poi.ss.usermodel.Cell-org.apache.poi.ss.usermodel.CellType-
  [4]: https://support.microsoft.com/en-us/office/apply-data-validation-to-cells-29fecbcc-d1b9-42c1-9d76-eff3ce5f7249
  [5]: https://learn.microsoft.com/en-us/dotnet/api/documentformat.openxml.spreadsheet.datavalidation?view=openxml-2.8.1
  [6]: https://learn.microsoft.com/en-us/dotnet/api/documentformat.openxml.spreadsheet.datavalidation.type?view=openxml-2.8.1
  [7]: https://learn.microsoft.com/en-us/dotnet/api/documentformat.openxml.spreadsheet.datavalidationvalues?view=openxml-2.8.1
  [8]: https://support.microsoft.com/en-us/office/define-and-use-names-in-formulas-4d0f13ac-53b7-422e-afd2-abd7ff379c64
  [9]: https://stackoverflow.com/questions/77524422/drop-down-values-in-excel-cells-are-not-specified-in-data-validation-rules-and-g
  [10]: https://support.microsoft.com/en-us/office/indirect-function-474b3a3a-8a26-4f44-b491-92b6306fa261
  [11]: https://github.com/nissl-lab/npoi/blob/f1ecc1ebf13ce37599ca9e29720e60f7ea60f2d3/ooxml/XSSF/UserModel/XSSFWorkbook.cs#L1080C37-L1080C37
  [12]: https://learn.microsoft.com/en-us/office/open-xml/open-xml-sdk?view=openxml-2.8.1
  [13]: https://learn.microsoft.com/en-us/dotnet/api/documentformat.openxml.packaging.vbaprojectpart?view=openxml-2.8.1
  [14]: https://svn.apache.org/repos/asf/poi/trunk/poi/src/main/java/org/apache/poi/ss/formula/DataValidationEvaluator.java
  [15]: https://poi.apache.org/apidocs/dev/org/apache/poi/ss/formula/FormulaParser.html
  [16]: https://svn.apache.org/repos/asf/poi/trunk/poi/src/main/java/org/apache/poi/ss/formula/FormulaParser.java
  [17]: https://svn.apache.org/repos/asf/poi/trunk/poi/src/main/java/org/apache/poi/ss/formula/functions/
  [18]: https://svn.apache.org/repos/asf/poi/trunk/poi/src/main/java/org/apache/poi/ss/formula/WorkbookEvaluator.java
  [19]: https://github.com/nissl-lab/npoi/blob/f1ecc1ebf13ce37599ca9e29720e60f7ea60f2d3/main/SS/UserModel/Cell.cs
  [20]: https://github.com/nissl-lab/npoi/blob/f1ecc1ebf13ce37599ca9e29720e60f7ea60f2d3/main/SS/UserModel/DataValidationConstraint.cs