How to Work with Data Validation in .NET

Aspose.Cells FOSS for .NET enables you to add data validation rules to cell ranges through Worksheet.Validations (a ValidationCollection). Call Add(CellArea) to create a Validation object and set its Type using ValidationType. Supported types include List, Decimal, and Custom. Install with dotnet add package Aspose.Cells_FOSS.

Step-by-Step Guide

Step 1: Install the Package

dotnet add package Aspose.Cells_FOSS

Step 2: Import the Namespace

using Aspose.Cells_FOSS;

Step 3: Add a List Dropdown Validation

Create a CellArea with CellArea.CreateCellArea(), add it to Worksheet.Validations, and set ValidationType.List with a comma-separated list in Formula1.

using Aspose.Cells_FOSS;

var wb = new Workbook();
var ws = wb.Worksheets[0];

// Dropdown list for A1:A10
var listVal = ws.Validations[ws.Validations.Add(CellArea.CreateCellArea("A1", "A10"))];
listVal.Type = ValidationType.List;
listVal.Formula1 = "\"Open,In Progress,Closed\"";
listVal.InCellDropDown = true;
listVal.IgnoreBlank = true;
listVal.InputTitle = "Status";
listVal.InputMessage = "Choose a status from the dropdown";

wb.Save("list-validation.xlsx");
Console.WriteLine("List validation added.");

Step 4: Add a Numeric Range Validation

Use ValidationType.Decimal with OperatorType.Between and Formula1/Formula2 to constrain numeric input.

using Aspose.Cells_FOSS;

var wb = new Workbook();
var ws = wb.Worksheets[0];

// Decimal range validation for B1:B10 — allow 0 to 100
var numVal = ws.Validations[ws.Validations.Add(CellArea.CreateCellArea("B1", "B10"))];
numVal.Type = ValidationType.Decimal;
numVal.Operator = OperatorType.Between;
numVal.Formula1 = "0";
numVal.Formula2 = "100";
numVal.ErrorTitle = "Out of Range";
numVal.ErrorMessage = "Please enter a number between 0 and 100.";
numVal.ShowError = true;
numVal.IgnoreBlank = true;

wb.Save("decimal-validation.xlsx");
Console.WriteLine("Decimal validation added.");

Step 5: Combine Multiple Validations on the Same Sheet

You can add any number of validations to the same sheet by calling Validations.Add() multiple times, each with a different CellArea.

using Aspose.Cells_FOSS;

var wb = new Workbook();
var ws = wb.Worksheets[0];

// List validation for column A (status)
var statusVal = ws.Validations[ws.Validations.Add(CellArea.CreateCellArea("A2", "A100"))];
statusVal.Type = ValidationType.List;
statusVal.Formula1 = "\"New,Open,Closed\"";
statusVal.InCellDropDown = true;

// Decimal validation for column B (score)
var scoreVal = ws.Validations[ws.Validations.Add(CellArea.CreateCellArea("B2", "B100"))];
scoreVal.Type = ValidationType.Decimal;
scoreVal.Operator = OperatorType.Between;
scoreVal.Formula1 = "0";
scoreVal.Formula2 = "10";
scoreVal.ShowError = true;

wb.Save("combined-validation.xlsx");
Console.WriteLine("Combined validations added to sheet.");

Common Issues and Fixes

The dropdown does not appear in Excel after save. Ensure InCellDropDown = true is set on the Validation object before saving. The dropdown is only visible in Excel when this flag is true and ValidationType.List is used.

Validation allows values outside the defined range. The validation is enforced by Excel, not by the library. Setting validation rules does not prevent the library itself from writing any value to the cell. The validation is applied when a user enters data in Excel.

Formula1 for a list validation shows as #NAME? in Excel. For an inline comma-separated list, wrap the string in escaped quotes: Formula1 = "\"Option1,Option2,Option3\"". Without the inner quotes, Excel treats the value as a cell reference formula.

OperatorType.Between not applying correctly. Ensure both Formula1 (minimum) and Formula2 (maximum) are set as strings. Missing Formula2 for Between leaves the upper bound undefined.

Frequently Asked Questions

Can I validate against a cell range (e.g. a named range)?

Set Formula1 to a cell range reference such as "Sheet2!$A$1:$A$10". The values in that range will be used as the dropdown list source.

Does validation prevent the library from writing invalid values?

No. Validation rules are enforced by Excel when a user enters data. The Aspose.Cells FOSS library does not enforce validation when you call Cell.PutValue() programmatically.

How do I remove a validation rule?

Access the rule through Worksheet.Validations[index] and use ValidationCollection operations to manage rules. Alternatively, create a new workbook model without the unwanted rules.

What is the maximum number of validations per sheet?

The XLSX format supports up to 65,530 validation rules per worksheet.