openpyxl3.0官方文档（31）—— 单元格验证

数据验证器可以应用于单元格区域，范围也不必是连续的。例如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")
    

更多自定义规则信息参见网页。