Add or Remove Data Validation in Excel with Python
Data validation in Excel is a feature that allows you to set certain criteria or rules for the data entered into specific cells. It helps ensure the accuracy and integrity of data by restricting input to predefined values or conditions. When a user enters data that violates the defined validation rules, Excel displays an error message and prevents the data from being entered or modified until it meets the specified criteria. Data validation is beneficial for maintaining data consistency, preventing input errors, and ensuring data accuracy in Excel spreadsheets. In this article, we will explore how to add and remove data validation in Excel using Python.
We’ll discuss the following topics:
- Add (Dropdown) List Data Validation to Excel with Python
- Add Whole Number Data Validation to Excel with Python
- Add Decimal Data Validation to Excel with Python
- Add Date Data Validation to Excel with Python
- Add Time Data Validation to Excel with Python
- Add Text Length Data Validation to Excel with Python
- Remove Data Validation from Excel with Python
Python Library to Add and Remove Data Validation in Excel
To add and remove data validation in Excel in Python, we can use the Spire.XLS for Python library. It is an easy-to-use and feature-rich library for creating, reading, editing, and converting Excel files within Python applications. With this library, you can work with a lot of spreadsheet formats, such as XLS, XLSX, XLSB, XLSM, and ODS. Moreover, you are also able to render Excel files to other types of file formats, such as PDF, HTML, CSV, Text, Image, XML, SVG, ODS, PostScript, and XPS.
You can install Spire.XLS for Python from PyPI by running the following commands in your terminal:
pip install Spire.XlsFor more detailed information about the installation, you can check this official documentation: How to Install Spire.XLS for Python in VS Code.
Add (Dropdown) List Data Validation to Excel with Python
Adding (Dropdown) List Data Validation to Excel allows you to create a dropdown list of predefined options, ensuring that the data entered in a specific cell matches one of the available choices. By restricting input to valid values from the list, list data validation effectively maintains data integrity and accuracy.
Here is a simple example that shows how to add (dropdown) list data validation to an Excel file using Python and Spire.XLS for Python:
from spire.xls import *
from spire.xls.common import *
# Create a Workbook object
workbook = Workbook()
# Get the first worksheet
sheet = workbook.Worksheets[0]
# Add text to cell B2
sheet.Range["B2"].Text = "List Data Validation:"
# Add list data validation to cell C2
range = sheet.Range["C2"]
listValidation = range.DataValidation
listValidation.Values = ["Red", "Blue", "Green", "Yellow"]
listValidation.IsSuppressDropDownArrow = False
listValidation.InputMessage = "Select a color from the list"
listValidation.AlertStyle = AlertStyleType.Stop
listValidation.ShowError = True
listValidation.ErrorTitle = "Error"
listValidation.ErrorMessage = "Please select a color from the list"
# Set column width
sheet.SetColumnWidth(2, 20)
sheet.SetColumnWidth(3, 20)
# Save the result file
workbook.SaveToFile("ListDataValidation.xlsx", ExcelVersion.Version2016)
workbook.Dispose()
Add Whole Number Data Validation to Excel with Python
Adding Whole Number Data Validation to Excel enables you to set restrictions on the type of data that can be entered into a cell. With this validation, you can define specific criteria for whole numbers, such as minimum and maximum values, ensuring that only valid whole numbers are accepted.
Here is a simple example that shows how to add whole number data validation to an Excel file using Python and Spire.XLS for Python:
from spire.xls import *
from spire.xls.common import *
# Create a Workbook object
workbook = Workbook()
# Get the first worksheet
sheet = workbook.Worksheets[0]
# Add text to cell B2
sheet.Range["B2"].Text = "Whole Number Data Validation:"
# Add whole number data validation to cell C2
range = sheet.Range["C2"]
wholeNumberValidation = range.DataValidation
wholeNumberValidation.AllowType = CellDataType.Integer
wholeNumberValidation.CompareOperator = ValidationComparisonOperator.Between
wholeNumberValidation.Formula1 = "1"
wholeNumberValidation.Formula2 = "100"
wholeNumberValidation.InputMessage = "Enter a number between 1 and 100"
wholeNumberValidation.AlertStyle = AlertStyleType.Stop
wholeNumberValidation.ShowError = True
wholeNumberValidation.ErrorTitle = "Error"
wholeNumberValidation.ErrorMessage = "Please enter a number between 1 and 100"
# Set column width
sheet.SetColumnWidth(2, 27)
sheet.SetColumnWidth(3, 20)
# Save the result file
workbook.SaveToFile("WholeNumberDataValidation.xlsx", ExcelVersion.Version2016)
workbook.Dispose()
Add Decimal Data Validation to Excel with Python
Adding Decimal Data Validation to Excel allows you to set constraints on decimal values entered into a cell. You can specify the number of decimal places, minimum and maximum values, or even enforce a specific format. This validation ensures that only accurate and acceptable decimal data is entered.
Here is a simple example that shows how to add decimal data validation to an Excel file using Python and Spire.XLS for Python:
from spire.xls import *
from spire.xls.common import *
# Create a Workbook object
workbook = Workbook()
# Get the first worksheet
sheet = workbook.Worksheets[0]
# Add text to cell B2
sheet.Range["B2"].Text = "Decimal Data Validation:"
# Add decimal data validation to cell C2
range = sheet.Range["C2"]
wholeNumberValidation = range.DataValidation
wholeNumberValidation.AllowType = CellDataType.Decimal
wholeNumberValidation.CompareOperator = ValidationComparisonOperator.Between
wholeNumberValidation.Formula1 = "-10.50"
wholeNumberValidation.Formula2 = "10.50"
wholeNumberValidation.InputMessage = "Enter a decimal between -10.50 and 10.50"
wholeNumberValidation.AlertStyle = AlertStyleType.Stop
wholeNumberValidation.ShowError = True
wholeNumberValidation.ErrorTitle = "Error"
wholeNumberValidation.ErrorMessage = "Please enter a decimal between -10.50 and 10.50"
# Set column width
sheet.SetColumnWidth(2, 23)
sheet.SetColumnWidth(3, 20)
# Save the result file
workbook.SaveToFile("DecimalDataValidation.xlsx", ExcelVersion.Version2016)
workbook.Dispose()
Add Date Data Validation to Excel with Python
Adding Date Data Validation to Excel enables you to control and validate dates entered into cells. You can set specific date formats, define date ranges, and even limit input to weekdays or specific days of the week. This validation helps maintain consistency and accuracy in date-based data.
Here is a simple example that shows how to add date data validation to an Excel file using Python and Spire.XLS for Python:
from spire.xls import *
from spire.xls.common import *
# Create a Workbook object
workbook = Workbook()
# Get the first worksheet
sheet = workbook.Worksheets[0]
# Add text to cell B2
sheet.Range["B2"].Text = "Date Data Validation:"
# Add date data validation to cell C2
range = sheet.Range["C2"]
dateValidation = range.DataValidation
dateValidation.AllowType = CellDataType.Date
dateValidation.CompareOperator = ValidationComparisonOperator.Between
dateValidation.Formula1 = "01/01/2023"
dateValidation.Formula2 = "12/31/2023"
dateValidation.InputMessage = "Enter a date between 01/01/2023 and 12/31/2023"
dateValidation.AlertStyle = AlertStyleType.Stop
dateValidation.ShowError = True
dateValidation.ErrorTitle = "Error"
dateValidation.ErrorMessage = "Please enter a date between 01/01/2023 and 12/31/2023"
# Set column width
sheet.SetColumnWidth(2, 20)
sheet.SetColumnWidth(3, 20)
# Save the result file
workbook.SaveToFile("DateDataValidation.xlsx", ExcelVersion.Version2016)
workbook.Dispose()
Add Time Data Validation to Excel with Python
Adding Time Data Validation to Excel allows you to validate and control time values entered into cells. You can define specific time formats, minimum and maximum values, or even restrict input to specific time intervals. This validation ensures that the time data entered is accurate and within the desired parameters.
Here is a simple example that shows how to add time data validation to an Excel file using Python and Spire.XLS for Python:
from spire.xls import *
from spire.xls.common import *
# Create a Workbook object
workbook = Workbook()
# Get the first worksheet
sheet = workbook.Worksheets[0]
# Add text to cell B2
sheet.Range["B2"].Text = "Time Data Validation:"
# Add time data validation to cell C2
range = sheet.Range["C2"]
timeValidation = range.DataValidation
timeValidation.AllowType = CellDataType.Time
timeValidation.CompareOperator = ValidationComparisonOperator.Between
timeValidation.Formula1 = "8:30"
timeValidation.Formula2 = "11:30"
timeValidation.InputMessage = "Enter a time between 8:30 and 11:30"
timeValidation.AlertStyle = AlertStyleType.Stop
timeValidation.ShowError = True
timeValidation.ErrorTitle = "Error"
timeValidation.ErrorMessage = "Please enter a time between 8:30 and 11:30"
# Set column width
sheet.SetColumnWidth(2, 20)
sheet.SetColumnWidth(3, 20)
# Save the result file
workbook.SaveToFile("TimeDataValidation.xlsx", ExcelVersion.Version2016)
workbook.Dispose()
Add Text Length Data Validation to Excel with Python
Adding Text Length Data Validation to Excel allows you to set restrictions on the length of text entered into cells. You can define maximum and minimum character limits, ensuring that the entered text meets the desired criteria. This validation helps prevent data entry errors and ensures consistent text length.
Here is a simple example that shows how to add text length data validation to an Excel file using Python and Spire.XLS for Python:
from spire.xls import *
from spire.xls.common import *
# Create a Workbook object
workbook = Workbook()
# Get the first worksheet
sheet = workbook.Worksheets[0]
# Add text to cell B2
sheet.Range["B2"].Text = "Text Length Data Validation:"
# Add text length data validation to cell C2
range = sheet.Range["C2"]
textLengthValidation = range.DataValidation
textLengthValidation.AllowType = CellDataType.TextLength
textLengthValidation.CompareOperator = ValidationComparisonOperator.Greater
textLengthValidation.Formula1 = "8"
textLengthValidation.InputMessage = "Enter a text greater than 8 characters"
textLengthValidation.AlertStyle = AlertStyleType.Stop
textLengthValidation.ShowError = True
textLengthValidation.ErrorTitle = "Error"
textLengthValidation.ErrorMessage = "Please enter a text greater than 8 characters"
# Set column width
sheet.SetColumnWidth(2, 25)
sheet.SetColumnWidth(3, 20)
# Save the result file
workbook.SaveToFile("TextLengthDataValidation.xlsx", ExcelVersion.Version2016)
workbook.Dispose()
Remove Data Validation from Excel with Python
Removing Data Validation from Excel allows you to eliminate any previously set validation rules from a cell or range of cells. This can be useful when you no longer need the restrictions imposed by the validation or when you want to change the validation criteria.
Here is a simple example that shows how to remove data validation from an Excel file using Python and Spire.XLS for Python:
from spire.xls import *
from spire.xls.common import *
# Create a Workbook object
workbook = Workbook()
# Load an Excel file containing data validations
workbook.LoadFromFile("ListDataValidation.xlsx")
# Get the first worksheet
worksheet = workbook.Worksheets[0]
# Create a list of rectangles to specify the cells or cell ranges where the data validation will be removed
rects= []
rects.append(Rectangle.FromLTRB(0, 0, 3, 2))
# Remove the data validation from the selected cells
worksheet.DVTable.Remove(rects)
# Save the result file
workbook.SaveToFile("RemoveDataValidation.xlsx", ExcelVersion.Version2016)
workbook.Dispose()
Conclusion
This article demonstrated how to add various types of data validations to an Excel file using Python. Furthermore, it also explained how to remove data validations from an Excel file using Python. We hope you find it helpful.
