![Python XlsxWriter Tutorial](/python_xlsxwriter/images/python-xlsxwriter-mini-logo.jpg)
- Python XlsxWriter Tutorial
- Python XlsxWriter - Home
- Python XlsxWriter - Overview
- Python XlsxWriter - Environment Setup
- Python XlsxWriter - Hello World
- Python XlsxWriter - Important classes
- Python XlsxWriter - Cell Notation & Ranges
- Python XlsxWriter - Defined Names
- Python XlsxWriter - Formula & Function
- Python XlsxWriter - Date and Time
- Python XlsxWriter - Tables
- Python XlsxWriter - Applying Filter
- Python XlsxWriter - Fonts & Colors
- Python XlsxWriter - Number Formats
- Python XlsxWriter - Border
- Python XlsxWriter - Hyperlinks
- Python XlsxWriter - Conditional Formatting
- Python XlsxWriter - Adding Charts
- Python XlsxWriter - Chart Formatting
- Python XlsxWriter - Chart Legends
- Python XlsxWriter - Bar Chart
- Python XlsxWriter - Line Chart
- Python XlsxWriter - Pie Chart
- Python XlsxWriter - Sparklines
- Python XlsxWriter - Data Validation
- Python XlsxWriter - Outlines & Grouping
- Python XlsxWriter - Freeze & Split Panes
- Python XlsxWriter - Hide/Protect Worksheet
- Python XlsxWriter - Textbox
- Python XlsxWriter - Insert Image
- Python XlsxWriter - Page Setup
- Python XlsxWriter - Header & Footer
- Python XlsxWriter - Cell Comments
- Python XlsxWriter - Working with Pandas
- Python XlsxWriter - VBA Macro
- Python XlsxWriter Useful Resources
- Python XlsxWriter - Quick Guide
- Python XlsxWriter - Useful Resources
- Python XlsxWriter - Discussion
Python XlsxWriter - Data Validation
Data validation feature in Excel allows you to control what a user can enter into a cell. You can use it to ensure that the value in a cell is a number/date within a specified range, text with required length, or to present a dropdown menu to choose the value from.
The data validation tools are available in the Data menu. The first tab allows you to set a validation criterion. Following figure shows that criteria requires the cell should contain an integer between 1 to 25 −
![Data Validation](/python_xlsxwriter/images/data_validation.jpg)
In the second tab, set the message to be flashed when user's cursor is on the desired cell, which in this case is 'Enter any integer between 1 to 25'. You can also set the message title; in this case it is Age.
![Data Validation1](/python_xlsxwriter/images/data_validation1.jpg)
The third tab allows asks you to define any error message you would like to flash if the validation criteria fails.
![Data Validation2](/python_xlsxwriter/images/data_validation2.jpg)
When the user places the cursor in I10 (for which the validation is set), you can see the input message.
![Age1](/python_xlsxwriter/images/age1.jpg)
When the entered number is not in the range, the error message will flash.
![Age2](/python_xlsxwriter/images/age2.jpg)
Working with XlsxWriter Data Validation
You can set the validation criteria, input and error message programmatically with data_validation() method.
worksheet.data_validation( 'I10', { 'validate': 'integer','criteria': 'between', 'minimum': 1,'maximum': 25, 'input_title': 'Enter an integer:', 'input_message': 'between 1 and 25', 'error_title': 'Input value is not valid!', 'error_message': 'It should be an integer between 1 and 25' } )
The data_validation() method accepts options parameter as a dictionary with following parameters −
validate − It is used to set the type of data that you wish to validate. Allowed values are integer, decimal, list, date, time, length etc.
criteria − It is used to set the criteria for validation. It can be set to any logical operator including between/ not between, ==, !=, <, >, <=, >=, etc.
value − Sets the limiting value to which the criteria is applied. It is always required. When using the list validation, it is given as a Comma Separated Variable string.
input_title − Used to set the title of the input message when the cursor is placed in the target cell.
input_message − The message to be displayed when a cell is entered.
error_title − The title of the error message to be displayed when validation criteria is not met.
error_message − Sets the error message. The default error message is "The value you entered is not valid. A user has restricted values that can be entered into the cell."
Example
Following usage of data_validation() method results in the behavior of data validation feature as shown in the above figures.
import xlsxwriter wb = xlsxwriter.Workbook('hello.xlsx') worksheet = wb.add_worksheet() worksheet.data_validation( 'I10', { 'validate': 'integer','criteria': 'between', 'minimum': 1,'maximum': 25, 'input_title': 'Enter an integer:', 'input_message': 'between 1 and 25', 'error_title': 'Input value is not valid!', 'error_message':'It should be an integer between 1 and 25' } ) wb.close()
As another example, the cell I10 is set a validation criterion so as to force the user choose its value from a list of strings in a drop down.
worksheet.data_validation( 'I10', { 'validate': 'list', 'source': ['Mumbai', 'Delhi', 'Chennai', 'Kolkata'], 'input_title': 'Choose one:', 'input_message': 'Select a value from th list', } )
Example
The modified program for validation with the drop down list is as follows −
import xlsxwriter wb = xlsxwriter.Workbook('hello.xlsx') worksheet = wb.add_worksheet() worksheet.data_validation( 'I10', { 'validate': 'list', 'source': ['Mumbai', 'Delhi', 'Chennai', 'Kolkata'], 'input_title': 'Choose one:', 'input_message': 'Select a value from the list', } ) wb.close()
Output
The dropdown list appears when the cursor is placed in I10 cell −
![Dropdown List](/python_xlsxwriter/images/dropdown_list.jpg)
Example
If you want to make the user enter a string of length greater than 5, use >= as criteria and value set to 5.
import xlsxwriter wb = xlsxwriter.Workbook('hello.xlsx') worksheet = wb.add_worksheet() worksheet.data_validation( 'I10',{ 'validate': 'length', 'criteria': '>=','value': 5,'input_title': 'Enter name:', 'input_message': 'Minimum length 5 character', 'error_message':'Name should have at least 5 characters' } ) wb.close()
Output
If the string is having less than 5 characters, the error message pops up as follows −
![String](/python_xlsxwriter/images/string.jpg)
To Continue Learning Please Login