How to programmatically validate a cell’s value based on Excel data validation constraints?

by

What I would like to do:

  1. Set a cell value.
  2. Check with a program if the cell value conforms to the data validation rules set for that cell.

I could extract all the information needed to create a validation function, but I don’t want to re-invent the wheel.

This question may be perceived as a duplicate of Is there a way to programmatically evaluate Excel data validations using npoi in .NET?, but I tried to cast a wider net while also attempting to provide an answer.

TL;DR

Has built-in cell validation?
Open XML SDK no*
NPOI no*
EPPlus no? (see section 3. below)
ClosedXML no*

* 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.

Leave a Comment