Working with Data Validation

Working with Data Validation

Aspose.Cells FOSS for C++ supports adding cell-level data validation rules to worksheets via the Validation and ValidationCollection classes. The WorkbookValidator class provides a pre-save consistency check.

Adding a Validation Rule

Access the validation collection for a worksheet and add a new rule using ValidationCollection::Add(). Set the type, formula, and target area on the returned Validation object:

#include "aspose/cells_foss/Workbook.h"
#include "aspose/cells_foss/Worksheet.h"
#include "aspose/cells_foss/ValidationCollection.h"
#include "aspose/cells_foss/Validation.h"
#include "aspose/cells_foss/CellArea.h"

using namespace Aspose::Cells_FOSS;

int main() {
    Workbook workbook;
    Worksheet& sheet = workbook.GetWorksheets()[0];
    ValidationCollection& validations = sheet.GetValidations();

    int idx = validations.Add();
    Validation& v = validations[idx];

    v.SetType(ValidationType::WholeNumber);
    v.SetFormula1("1");
    v.SetFormula2("100");
    v.SetIgnoreBlank(true);
    v.SetInCellDropDown(false);
    v.SetInputTitle("Enter Value");
    v.SetInputMessage("Value must be between 1 and 100.");
    v.SetErrorTitle("Invalid Input");
    v.SetErrorMessage("Value is out of range.");
    v.SetShowInput(true);
    v.SetShowError(true);

    CellArea area = CellArea::CreateCellArea("B2", "B10");
    v.AddArea(area);

    workbook.Save("validated.xlsx");
    return 0;
}

Validation Types

The ValidationType enumeration defines the kinds of constraints that can be applied:

Alert Style

The ValidationAlertType enumeration controls the behavior shown when the validation fails:

Set the alert style with Validation::SetAlertStyle(ValidationAlertType).

Querying Validation in a Cell

Use ValidationCollection::GetValidationInCell(row, col) to find which validation rule applies to a specific cell. Returns nullptr if no rule applies.

Removing Validation Rules

Remove a validation from a specific cell or range area:

CellArea area = CellArea::CreateCellArea("B2", "B10");
validations.RemoveArea(area);
// Or remove a single cell:
validations.RemoveACell(1, 1); // row=1, col=1 (0-based)

Pre-Save Workbook Validation

Call WorkbookValidator::ValidateForSave to run a consistency check on the workbook model before saving. This is called automatically by Workbook::Save but can be invoked independently during development:

WorkbookValidator validator;
validator.ValidateForSave(workbook);

Tips and Best Practices

• Always set both SetFormula1 and SetFormula2 for range-type validations (WholeNumber, Decimal, Date) to define the lower and upper bounds.
• Use SetIgnoreBlank(true) to allow empty cells to pass validation without triggering an error.
• Call ValidationCollection::AreasOverlap before adding a new rule to check for overlapping validation areas on the same sheet.
• WorkbookValidator::ValidateForSave is called internally on save; invoke it during development to catch issues early.

Common Issues

API Reference Summary

See Also

• Aspose.Cells — Enterprise Documentation