Communities

Writing
Writing
Codidact Meta
Codidact Meta
The Great Outdoors
The Great Outdoors
Photography & Video
Photography & Video
Scientific Speculation
Scientific Speculation
Cooking
Cooking
Electrical Engineering
Electrical Engineering
Judaism
Judaism
Languages & Linguistics
Languages & Linguistics
Software Development
Software Development
Mathematics
Mathematics
Christianity
Christianity
Code Golf
Code Golf
Music
Music
Physics
Physics
Linux Systems
Linux Systems
Power Users
Power Users
Tabletop RPGs
Tabletop RPGs
Community Proposals
Community Proposals
tag:snake search within a tag
answers:0 unanswered questions
user:xxxx search by author id
score:0.5 posts with 0.5+ score
"snake oil" exact phrase
votes:4 posts with 4+ votes
created:<1w created < 1 week ago
post_type:xxxx type of post
Search help
Notifications
Mark all as read See all your notifications »
Q&A

Welcome to Software Development on Codidact!

Will you help us build our independent community of developers helping developers? We're small and trying to grow. We welcome questions about all aspects of software development, from design to code to QA and more. Got questions? Got answers? Got code you'd like someone to review? Please join us.

How to programmatically evaluate Excel data validations using .NET?

+2
−0

What I would like to do:

  1. Set a cell value.
  2. Check with a program if cell values conform to data validation rules.

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

What I have in mind is along the lines of how NPOI allows recalculating all formulas to reflect changes in a workbook:

let recalculateFormulas (workbook: XSSFWorkbook) =
    let creationHelper = workbook.GetCreationHelper()
    let formulaEvaluator = creationHelper.CreateFormulaEvaluator()
    formulaEvaluator.EvaluateAll()
History
Why does this post require moderator attention?
You might want to add some details to your flag.
Why should this post be closed?

0 comment threads

1 answer

+1
−0

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 Excel1, 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 hoops2, 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).

History
Why does this post require moderator attention?
You might want to add some details to your flag.

0 comment threads

Sign up to answer this question »