Data Validation in Excel Using Python

data-validation-in-excel-using-python.md

Learn how to implement data validation in Excel using Python

data-validation-in-excel-using-python_date.py

import aspose.cells as cells

# Create a new workbook
workbook = cells.Workbook()
worksheet = workbook.worksheets.get(0)

# Access validations collection
validations = worksheet.validations

# Add a new validation
index = validations.add()
validation = validations[index]

# Set the validation type to Date
validation.type = cells.ValidationType.DATE
validation.operator = cells.OperatorType.BETWEEN

# Set the start and end dates (use formula strings)
validation.formula1 = '"2024-01-01"'
validation.formula2 = '"2024-12-31"'

# Define the area where validation will apply (e.g., C1:C10)
area = cells.CellArea()
area.start_row = 0
area.end_row = 9
area.start_column = 2  # Column C (A=0, B=1, C=2)
area.end_column = 2

validation.add_area(area)

# Save the workbook
workbook.save("output_date_validation.xlsx")

data-validation-in-excel-using-python_dropdown.py

import aspose.cells as cells

# Create a new workbook
workbook = cells.Workbook()
worksheet = workbook.worksheets.get(0)

# Access the validations collection
validations = worksheet.validations

# Add a new validation
index = validations.add()
validation = validations[index]

# Set the validation type to List
validation.type = cells.ValidationType.LIST
validation.operator = cells.OperatorType.NONE

# Set the values for the dropdown list
validation.formula1 = 'Apple,Banana,Cherry'

# Define the area where validation will be applied
area = cells.CellArea()
area.create_cell_area("B1", "B1")

validation.add_area(area)

# Save the workbook
workbook.save("output_dropdown.xlsx")

data-validation-in-excel-using-python_errors.py

import aspose.cells as cells

# Create a new workbook
workbook = cells.Workbook()
worksheet = workbook.worksheets.get(0)

# Add number range validation
idx = worksheet.validations.add()
validation = worksheet.validations[idx]
validation.type = cells.ValidationType.CUSTOM
validation.formula1 = "=NOT(ISBLANK(B1))"

# Define the area where validation will be applied
area = cells.CellArea()
area.create_cell_area("C1", "C1")

# Configure the error message and input tip
validation.alert_style = cells.ValidationAlertType.STOP
validation.error_title = "Missing Required Field"
validation.error_message = "Please fill in cell B1 before entering data in C1."
validation.input_title = "Dependent Field"
validation.input_message = "You must complete B1 before filling this field."
validation.show_error = True
validation.show_input = True

# Save the workbook
workbook.save("validated_with_alerts.xlsx")

data-validation-in-excel-using-python_formula.py

import aspose.cells as cells

# Create a new workbook
workbook = cells.Workbook()
worksheet = workbook.worksheets.get(0)

# Add validation
idx = worksheet.validations.add()
validation = worksheet.validations[idx]
validation.type = cells.ValidationType.CUSTOM
validation.formula1 = "=B1>A1"

# Define the area where validation will be applied
area = cells.CellArea()
area.create_cell_area("B1", "B1")

validation.add_area(area)

# Save the workbook
workbook.save("formula_validation.xlsx")

data-validation-in-excel-using-python_numbers.py

import aspose.cells as cells

# Create a new workbook
workbook = cells.Workbook()
worksheet = workbook.worksheets.get(0)

# Add validation to cell A1
idx = worksheet.validations.add()
validation = worksheet.validations[idx]
validation.type = cells.ValidationType.WHOLE_NUMBER
validation.operator = cells.OperatorType.BETWEEN
validation.formula1 = "1"
validation.formula2 = "100"

# Define the area where validation will be applied
area = cells.CellArea()
area.create_cell_area("A1", "A1")

validation.add_area(area)

# Save the workbook
workbook.save("WholeNumber_output_dropdown.xlsx")