Python XlsxWriter - Data Vapdation

Data vapdation 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 vapdation tools are available in the Data menu. The first tab allows you to set a vapdation criterion. Following figure shows that criteria requires the cell should contain an integer between 1 to 25 −

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.

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

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

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

Working with XlsxWriter Data Vapdation

You can set the vapdation criteria, input and error message programmatically with data_vapdation() method.

worksheet.data_vapdation(
    I10 ,
   {
       vapdate :  integer , criteria :  between ,
       minimum : 1, maximum : 25,
       input_title :  Enter an integer: ,
       input_message :  between 1 and 25 ,
       error_title :  Input value is not vapd! ,
       error_message :  It  should be an integer between 1 and 25 
   }
)

The data_vapdation() method accepts options parameter as a dictionary with following parameters −

vapdate − It is used to set the type of data that you wish to vapdate. Allowed values are integer, decimal, pst, date, time, length etc.

criteria − It is used to set the criteria for vapdation. It can be set to any logical operator including between/ not between, ==, !=, <, >, <=, >=, etc.

value − Sets the pmiting value to which the criteria is appped. It is always required. When using the pst vapdation, 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 vapdation criteria is not met.

error_message − Sets the error message. The default error message is "The value you entered is not vapd. A user has restricted values that can be entered into the cell."

Example

Following usage of data_vapdation() method results in the behavior of data vapdation feature as shown in the above figures.

import xlsxwriter

wb = xlsxwriter.Workbook( hello.xlsx )
worksheet = wb.add_worksheet()

worksheet.data_vapdation(
    I10 ,
   {
       vapdate :  integer , criteria :  between ,
       minimum : 1, maximum : 25,
       input_title :  Enter an integer: ,
       input_message :  between 1 and 25 ,
       error_title :  Input value is not vapd! ,
       error_message : It should be an integer between 1 and 25 
   }
)
wb.close()

As another example, the cell I10 is set a vapdation criterion so as to force the user choose its value from a pst of strings in a drop down.

worksheet.data_vapdation(
    I10 ,
   {
       vapdate :  pst ,
       source : [ Mumbai ,  Delhi ,  Chennai ,  Kolkata ],
       input_title :  Choose one: ,
       input_message :  Select a value from th pst ,
   }
)

Example

The modified program for vapdation with the drop down pst is as follows −

import xlsxwriter

wb = xlsxwriter.Workbook( hello.xlsx )
worksheet = wb.add_worksheet()

worksheet.data_vapdation(
    I10 ,
   {
       vapdate :  pst ,
       source : [ Mumbai ,  Delhi ,  Chennai ,  Kolkata ],
       input_title :  Choose one: ,
       input_message :  Select a value from the pst ,
   }
)
wb.close()

Output

The dropdown pst appears when the cursor is placed in I10 cell −

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_vapdation(
    I10 ,{
       vapdate :  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 −