Which .NET project can programmatically validate a cell's value out of the box based on Excel data validation constraints?

Here's a list of popular Excel .NET projects that don't have this functionality:

^{* Non-authoritative answer (i.e., post author couldn't find proof for support)}

1. Open XML SDK for Microsoft Office

Can only be used to get or set data validation rules. (See DataValidation class.)

2. NPOI

Couldn't find anything except NPOI.SS.Formula.DataValidationEvaluator (see NOTE below).

NOTE
NPOI was inspired Apache POI, and NPOI.SS.Formula.DataValidationEvaluator seems to mirror Apache POI's DataValidationEvaluator class. However, as of 11/20/2023, NPOI's version only has one method, IsType (whose specs are the same as isType's though).

Also, Apache POIDataValidationEvaluator class' isValidCell method:

public boolean isValidCell(CellReference cellRef)

Use the validation returned by getValidationForCell(CellReference) if you want the error display details. This is the validation checked by this method, which attempts to replicate Excel's data validation rules.

Note that to properly apply some validations, care must be taken to offset the base validation formula by the relative position of the current cell, or the wrong value is checked.

Parameters:
cellRef - The reference of the cell to evaluate

Returns:
true if the cell has no validation or the cell value passes the defined validation, false if it fails

QUESTION
Apache POI's XSSFDataValidationConstraint class also has a validate method, but can't decide based on the docs whether it is germane to this discussion.

3. EPPlus

Found the InternalValidationEnabled property (see below), but I think it is only meant to check the consistency of the data validation rules themselves, and not if the cells conform to them.

class ExcelDataValidationCollection

InternalValidationEnabled
Epplus validates that all data validations are consistent and valid when they are added and when a workbook is saved. Since this takes some resources, it can be disabled for improve performance.

Declaration
public bool InternalValidationEnabled { get; set; }

4. ClosedXML

Based on the sources below, it does not have built-in cell validation.

• Data Validation · ClosedXML/ClosedXML Wiki

• GitHub code search results for "validat"

• Cell — ClosedXML 0.102.0 documentation

posted about 1 year ago

CC0

1y ago

toraritte‭

640 reputation 33 16 99 54