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

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

The third tab allows asks you to define any error message you would like to flash if the validation criteria fails.

Data Validation2

When the user places the cursor in I10 (for which the validation is set), you can see the input message.

Age1

When the entered number is not in the range, the error message will flash.

Age2

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

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
Advertisements