Python Xlsxwriter 简明教程
Python XlsxWriter - Data Validation
Excel 中的 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.
数据验证工具在“数据”菜单中提供。第一个选项卡允许您设置验证条件。下图显示条件需要单元格包含 1 到 25 之间的整数：
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 −
在第二个选项卡中，设置当用户光标所在期望单元格时闪现的消息，在本例中为“输入 1 到 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 like to flash if the validation criteria fails.
当用户将光标置于 I10（为此设置了验证）中时，您会看到输入消息。
When the user places the cursor in I10 (for which the validation 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 Validation
您可以使用 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'
}
)data_validation() 方法接受 options 参数，该参数是一个包含以下参数的字典：
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
data_validation() 方法的以下用法导致了数据验证功能的行为，如上图所示。
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()作为另一个示例，单元格 I10 设置了一个验证条件，以强制用户从下拉列表中的字符串列表中选择其值。
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()
Example
如果您想要让用户输入长度大于 5 的字符串，请使用 >= 作为条件，并将值设置为 5。
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()