Aspose.Cells FOSS
Python
DataValidation

DataValidation — Aspose.Cells FOSS for Python API Reference

aspose-cells-foss supports adding data validation rules to cell ranges. Validation rules are managed through ws.data_validations, a DataValidationCollection attached to each Worksheet.

Package: aspose.cells_foss

from aspose.cells_foss import (
    DataValidationType, DataValidationOperator,
    DataValidationAlertStyle, DataValidationImeMode,
    DataValidation, DataValidationCollection,
)

DataValidationType

DataValidationType is an IntEnum identifying the type of constraint to apply.

MemberValueDescription
NONE0No validation constraint.
WHOLE_NUMBER1Accepts only integers.
DECIMAL2Accepts decimal numbers.
LIST3Accepts values from a dropdown list.
DATE4Accepts only date values.
TIME5Accepts only time values.
TEXT_LENGTH6Validates by text length.
CUSTOM7Validates using a custom formula.

DataValidationOperator

DataValidationOperator is an IntEnum specifying the comparison operator used with numeric/date/time/length constraints.

MemberValue
BETWEEN0
NOT_BETWEEN1
EQUAL2
NOT_EQUAL3
GREATER_THAN4
LESS_THAN5
GREATER_THAN_OR_EQUAL6
LESS_THAN_OR_EQUAL7

DataValidationAlertStyle

DataValidationAlertStyle is an IntEnum controlling how Excel reacts when invalid data is entered.

MemberValueBehavior
STOP0Rejects the entry and shows an error dialog (default).
WARNING1Shows a warning but allows the entry.
INFORMATION2Shows an informational message but always allows the entry.

DataValidationImeMode

DataValidationImeMode is an IntEnum controlling the IME (Input Method Editor) mode for East Asian text entry.

MemberValue
NO_CONTROL0
OFF1
ON2
DISABLED3
HIRAGANA4
FULL_KATAKANA5
HALF_KATAKANA6
FULL_ALPHA7
HALF_ALPHA8
FULL_HANGUL9
HALF_HANGUL10

DataValidation

A DataValidation object describes a single validation rule and the cell range (sqref) it applies to.

Constructor 

DataValidation(sqref=None)
ParameterTypeDescription
sqrefstr | NoneThe cell range this rule applies to (e.g., "A1:A10", "B2"). Can be set later via the sqref property.

Properties

PropertyTypeDescription
sqrefstrThe cell range for this rule (e.g., "B2:B100").
typeDataValidationTypeValidation type (whole number, decimal, list, date, time, text length, or custom).
operatorDataValidationOperatorComparison operator applied to formula1 (and formula2 for BETWEEN/NOT_BETWEEN).
formula1strLower bound, list source (e.g., '"Apple,Banana,Cherry"'), or custom formula.
formula2strUpper bound for BETWEEN and NOT_BETWEEN operators.
alert_styleDataValidationAlertStyleHow Excel responds to invalid input (STOP, WARNING, or INFORMATION).
show_error_messageboolTrue to display the error message dialog on invalid input.
error_titlestrTitle of the error dialog (max 32 characters).
error_messagestrBody of the error dialog (max 225 characters).
show_input_messageboolTrue to display an input prompt message when the cell is selected.
input_titlestrTitle of the input prompt (max 32 characters).
input_messagestrBody of the input prompt (max 255 characters).
allow_blankboolTrue to allow blank (empty) cells without triggering the error.
show_dropdownboolTrue to show a dropdown arrow for list validations.
ime_modeDataValidationImeModeIME mode for East Asian text input.

Methods

copy 

validation.copy() -> DataValidation

Returns a deep copy of this validation rule.

DataValidationCollection

DataValidationCollection is accessed as ws.data_validations. It manages all validation rules on a worksheet.

Methods

add 

ws.data_validations.add(
    sqref,
    validation_type=None,
    operator=None,
    formula1=None,
    formula2=None
) -> DataValidation

Creates and registers a new validation rule in one call. Returns the created DataValidation object.

add_validation 

ws.data_validations.add_validation(validation: DataValidation) -> DataValidation

Registers a pre-configured DataValidation object. Returns the same object.

remove 

ws.data_validations.remove(validation: DataValidation) -> bool

Removes a validation rule by reference. Returns True if the rule was found and removed.

remove_at 

ws.data_validations.remove_at(index: int)

Removes the validation rule at the given zero-based index.

remove_by_range 

ws.data_validations.remove_by_range(sqref: str) -> int

Removes all validations whose sqref overlaps the given range. Returns the number of rules removed.

clear 

ws.data_validations.clear()

Removes all validation rules from the worksheet.

get_validation 

ws.data_validations.get_validation(cell_ref: str) -> DataValidation | None

Returns the validation rule that applies to the given cell reference (e.g., "B5"), or None if none applies.

Properties and Iteration

MemberDescription
countNumber of validation rules on this worksheet (read-only).
disable_promptsbool — Set to True to suppress all input-message prompts globally.
__len__()Returns count.
__iter__()Iterates over all DataValidation objects.
__getitem__(index)Returns the DataValidation at zero-based index.

Examples

Dropdown List Validation 

from aspose.cells_foss import Workbook, DataValidationType, DataValidationAlertStyle

wb = Workbook()
ws = wb.worksheets[0]

# Write a label
ws.cells["A1"].put_value("Status")

# Add a dropdown list to A2:A20
dv = ws.data_validations.add(
    sqref="A2:A20",
    validation_type=DataValidationType.LIST,
    formula1='"Open,In Progress,Closed,Cancelled"',
)
dv.show_dropdown = True
dv.allow_blank = True
dv.alert_style = DataValidationAlertStyle.STOP
dv.show_error_message = True
dv.error_title = "Invalid Status"
dv.error_message = "Please select a value from the list."
dv.show_input_message = True
dv.input_title = "Status"
dv.input_message = "Choose a status from the dropdown."

wb.save("dropdown_validation.xlsx")

Numeric Range Validation 

from aspose.cells_foss import (
    Workbook, DataValidationType, DataValidationOperator,
    DataValidationAlertStyle,
)

wb = Workbook()
ws = wb.worksheets[0]

ws.cells["B1"].put_value("Score (0–100)")

# Whole number between 0 and 100
dv = ws.data_validations.add(
    sqref="B2:B50",
    validation_type=DataValidationType.WHOLE_NUMBER,
    operator=DataValidationOperator.BETWEEN,
    formula1="0",
    formula2="100",
)
dv.allow_blank = False
dv.alert_style = DataValidationAlertStyle.WARNING
dv.show_error_message = True
dv.error_title = "Out of Range"
dv.error_message = "Score must be between 0 and 100."

wb.save("range_validation.xlsx")

See Also