验证单元格¶
数据验证器可以应用于单元格区域,但不能强制或计算。范围不必是连续的:例如,“a1 b2:b5”包含a1,单元格b2到b5,但不包含a2或b2。
实例¶
>>> from openpyxl import Workbook
>>> from openpyxl.worksheet.datavalidation import DataValidation
>>>
>>> # Create the workbook and worksheet we'll be working with
>>> wb = Workbook()
>>> ws = wb.active
>>>
>>> # Create a data-validation object with list validation
>>> dv = DataValidation(type="list", formula1='"Dog,Cat,Bat"', allow_blank=True)
>>>
>>> # Optionally set a custom error message
>>> dv.error ='Your entry is not in the list'
>>> dv.errorTitle = 'Invalid Entry'
>>>
>>> # Optionally set a custom prompt message
>>> dv.prompt = 'Please select from the list'
>>> dv.promptTitle = 'List Selection'
>>>
>>> # Add the data-validation object to the worksheet
>>> ws.add_data_validation(dv)
>>> # Create some cells, and add them to the data-validation object
>>> c1 = ws["A1"]
>>> c1.value = "Dog"
>>> dv.add(c1)
>>> c2 = ws["A2"]
>>> c2.value = "An invalid value"
>>> dv.add(c2)
>>>
>>> # Or, apply the validation to a range of cells
>>> dv.add('B1:B1048576') # This is the same as for the whole of column B
>>>
>>> # Check with a cell is in the validator
>>> "B4" in dv
True
注解
保存工作簿时,将忽略没有任何单元格范围的验证。
其他验证示例¶
任意整数::
dv = DataValidation(type="whole")
大于100的整数:
dv = DataValidation(type="whole",
operator="greaterThan",
formula1=100)
任何十进制数:::
dv = DataValidation(type="decimal")
0到1之间的任何十进制数:::
dv = DataValidation(type="decimal",
operator="between",
formula1=0,
formula2=1)
任何日期::
dv = DataValidation(type="date")
或时间::
dv = DataValidation(type="time")
最多15个字符的任何字符串:::
dv = DataValidation(type="textLength",
operator="lessThanOrEqual"),
formula1=15)
单元格范围验证:::
from openpyxl.utils import quote_sheetname
dv = DataValidation(type="list",
formula1="{0}!$B$1:$B$10".format(quote_sheetname(sheetname))
)
习惯规则::
dv = DataValidation(type="custom",
formula1"=SOMEFORMULA")
注解
有关自定义规则,请参阅http://www.contextures.com/xldataval07.html。