Aspose.Cells FOSS
C++
How to Add Data Validation to a Spreadsheet in C++

How to Add Data Validation to a Spreadsheet in C++

Aspose.Cells FOSS for C++ supports cell-level data validation rules that constrain what values users can enter in specific cells or ranges. The Validation, ValidationCollection, and WorkbookValidator classes make up the validation API.

Step-by-Step Guide

Step 1: Set Up the Build 

cmake_minimum_required(VERSION 3.15)
project(DataValidation CXX)
set(CMAKE_CXX_STANDARD 17)

add_subdirectory(path/to/Aspose.Cells-FOSS-for-Cpp)
add_executable(DataValidation main.cpp)
target_link_libraries(DataValidation PRIVATE Aspose.Cells.Foss.Cpp)

Step 2: Include Required Headers 

#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;

Step 3: Access the Validation Collection

Retrieve the ValidationCollection from the worksheet:

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

Step 4: Add a Validation Rule

Call ValidationCollection::Add() and configure the returned Validation object:

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

v.SetType(ValidationType::WholeNumber);
v.SetFormula1("1");
v.SetFormula2("100");
v.SetIgnoreBlank(true);
v.SetInputTitle("Enter Quantity");
v.SetInputMessage("Enter a whole number between 1 and 100.");
v.SetErrorTitle("Invalid Input");
v.SetErrorMessage("Value must be between 1 and 100.");
v.SetShowInput(true);
v.SetShowError(true);
v.SetAlertStyle(ValidationAlertType::Stop);

Step 5: Apply the Rule to a Range

Attach the validation to a CellArea using Validation::AddArea:

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

Step 6: Save the Workbook 

workbook.Save("validated.xlsx");
workbook.Dispose();

Step 7: Run Pre-Save Validation (Optional)

Call WorkbookValidator::ValidateForSave during development to catch model issues before saving:

WorkbookValidator validator;
validator.ValidateForSave(workbook);
workbook.Save("validated.xlsx");

Common Issues and Fixes

Validation not visible in saved file Ensure Validation::AddArea(area) is called after configuring the rule. A rule with no associated area is not serialized.

Drop-down list not showing Set v.SetInCellDropDown(true) and set v.SetType(ValidationType::List) with SetFormula1 pointing to a range or comma-separated values.

Overlapping validation areas Use ValidationCollection::AreasOverlap to check for conflicts before adding a new rule. Overlapping rules may produce unexpected behavior in Excel.

Input message not appearing Ensure both SetShowInput(true) and SetInputMessage("...") are set. An empty message string suppresses the input prompt even if SetShowInput(true) is called.

ValidateForSave reports errors Review the reported inconsistency. Common causes include missing formula bounds for range-type validations and invalid cell area coordinates.

Frequently Asked Questions

What validation types are supported?

The ValidationType enumeration includes: WholeNumber, Decimal, List, Date, TextLength, Custom, and Any.

Can I remove a validation rule from a range?

Yes. Use ValidationCollection::RemoveArea(area) to remove the rule from a range, or RemoveACell(row, col) for a single cell.

Can I apply the same validation rule to multiple ranges?

Yes. Call Validation::AddArea(area) multiple times on the same Validation object to apply it to additional ranges.

What alert styles are available?

ValidationAlertType includes: Stop (blocks invalid entry), Warning (warns but allows entry), and Information (informational tooltip only).

Does validation work at runtime, or only in Excel?

Data validation rules are stored in the XLSX file and enforced by Excel or a compatible reader. The library does not evaluate validation constraints at runtime when values are set programmatically.

See Also