Read Excel’s drop-down and numeric rules, including x14 extensions. Reading rules does not validate cell assignments.
dotnet add package RecursiveAI.OneDocument.SpreadsheetsRequires .NET 10 (net10.0); .NET 9 and earlier aren’t supported.
using RecursiveAI.OneDocument.Spreadsheets; using var book = Workbook.Open("validation.xlsx"); foreach (var rule in book.Sheets[0].Validations) Console.WriteLine( $"{rule.Type}: {rule.Formula1}");
validation.xlsx is in the example. The snippet prints:
List: "Yes,No" WholeNumber: 1 List: Choices!$A$1:$A$2
Unzip the example, then dotnet run.
The full program
The self-made fixture has an inline Yes/No list on A2:A5, whole numbers between 1 and 10 on B2:B5, and an x14 list on C2:C5 pointing to Choices!A1:A2. Those source cells contain Red and Blue. The numeric rule uses Warning error style; the list rules use Stop.
using RecursiveAI.OneDocument.Spreadsheets; using (var book = Workbook.Open("validation.xlsx")) { foreach (var rule in book.Sheets[0].Validations) { Console.WriteLine($"{string.Join(" ", rule.Ranges)}: {rule.Type}, x14={rule.IsExtended}"); Console.WriteLine($" {rule.Operator}: {rule.Formula1} | {rule.Formula2}"); Console.WriteLine($" {rule.ErrorStyle}; blank={rule.AllowBlank}; dropdown={rule.InCellDropDown}"); } book.SaveAs("preserved.xlsx"); } using var reopened = Workbook.Open("preserved.xlsx"); Console.WriteLine($"Reopened: {reopened.Sheets[0].Validations.Count} rules");
A2:A5: List, x14=False between: "Yes,No" | Stop; blank=True; dropdown=True B2:B5: WholeNumber, x14=False between: 1 | 10 Warning; blank=True; dropdown=True C2:C5: List, x14=True between: Choices!$A$1:$A$2 | Stop; blank=True; dropdown=True Reopened: 3 rules
Opening a nonexistent input raises FileNotFoundException; use the supplied fixture or an existing XLSX path.
Variations
Read bounds and the error style. Run this against validation.xlsx from the zip:
using RecursiveAI.OneDocument.Spreadsheets; using var book = Workbook.Open("validation.xlsx"); var rule = book.Sheets[0].Validations .Single(v => v.Type == DataValidationType.WholeNumber); Console.WriteLine($"{rule.Formula1} to {rule.Formula2}: {rule.ErrorStyle}");
1 to 10: WarningRelated recipes
The short answer
Open the workbook and enumerate Worksheet.Validations. Each item exposes its target ranges, type, operator, both formulas, error style, blank handling and messages. IsExtended distinguishes an x14 extension rule from a standard rule. The collection includes both, so a caller does not need to walk the worksheet’s extension markup separately.
The validation documentation describes the supported read properties and authoring operations. Formulas are returned as text: a list formula that points to cells is not itself the list of allowed values.
How do you view all data validation rules in Excel?
For programmatic inspection, enumerate the validations on every worksheet you want to inspect. The snippet at the top selects the first worksheet; the full program prints all rules on that sheet. A rule may apply to more than one range, so inspect the complete Ranges collection rather than assuming one cell or rectangle per rule.
The fixture deliberately includes an extended rule. Its Formula1 is Choices!$A$1:$A$2, while its target range is C2:C5. These mean different things: the target is where the rule applies, and the formula identifies its source list. Looking only at the target cells would not tell you which choices appear in the list.
Inspect Type before interpreting the formula strings. For the inline list, the first formula is the quoted text "Yes,No". For the whole-number rule, it is the lower bound, and Formula2 holds the upper bound. Other operators can use those fields differently. The full output prints the operator alongside the formulas so the numeric limits are not mistaken for two unrelated expressions.
How do you read data from Excel in C#?
Reading a cell value and reading its validation are separate operations. Use the worksheet’s cell accessors for stored cell values and the Validations collection for rules. The x14 rule in this fixture returns a reference to the Choices worksheet; it does not resolve the reference into Red and Blue for you. If your application needs the actual list items, read the referenced cells as a separate step after identifying the source reference.
Do not treat the presence of a rule as evidence that every existing cell satisfies it. The example reports metadata and preserves it on save; it does not audit the workbook’s values or execute Excel’s input UI. An application that accepts spreadsheet data must decide its own validation policy, including how to handle blank cells, warning-style rules and formula-based conditions.
InCellDropDown exposes the positive sense of the setting even though the file stores the inverse showDropDown flag. Error style also matters: Stop, Warning and Information are different stored policies, not interchangeable error labels. Inspect error and prompt messages when reporting the rule to a user.
How do you copy data validation rules in Excel?
If the goal is to save the same workbook without losing its rules, open it and call SaveAs, as the full program does. Reopening preserved.xlsx shows all three rules, including the x14 rule. This is preservation, not a cross-workbook copy operation.
When changing a rule, the documented operation is to remove the existing validation and add a new one with DataValidationSpec. The authoring helpers include inline lists, range lists, whole-number and decimal bounds, and custom formulas. Adding checks Excel’s documented field lengths as implemented by the library: titles are limited to 32 characters, messages to 255, and inline lists to 255. Keep the source reference and target ranges distinct when rebuilding a rule; copying just its visible drop-down text would discard the original relationship to the source cells.