如何使用 openpyxl 验证 Excel 数据？DataValidation 对象介绍

openpyxl Excel 数据验证对象 DataValidation

openpyxl包的DataValidation类，属于模块openpyxl.worksheet.datavalidation，用于表示与 Excel 单元格相关的数据验证。一个数据验证可以针对一个单元格，也可以针对一个区域，工作表所包含的数据验证，可通过Worksheet对象的data_validations变量进行查看，具体可以查看获取和设置工作表中的数据验证一段。

创建 Excel 数据验证

你可以使用DataValidation对象的构造器，创建新的 Excel 数据验证，然后通过Worksheet对象将其添加到工作表中，具体可以查看为工作表添加数据验证一段。

DataValidation(type=None, formula1=None, formula2=None, showErrorMessage=False, showInputMessage=False, showDropDown=False, allowBlank=False, sqref=(), promptTitle=None, errorStyle=None, error=None, prompt=None, errorTitle=None, imeMode=None, operator=None, allow_blank=None)

type 参数

type参数是一个字符串，用于指示将单元格中的内容作为哪种数据类型进行验证，可以是以下取值之一，'whole'（整数），'decimal'（小数），'list'（内容需要存在于列表中），'date'（日期），'time'（时间），'textLength'（内容的长度），'custom'（根据formula1参数给出的公式进行验证）。如果type参数为None，则允许输入任何类型的数据。

formula1，formula2 参数

formula1参数和formula2参数是两个表示公式的字符串，这些公式被用于数据验证，比如，要判断数字是否在3和10之间，那么formula1应该为'3'，formula2应该为'10'。这里需要指出，一些数据验证仅会用到参数formula1所表示的公式。

如果type参数为'list'，那么formula1表示的是列表中的项，两个项之间需要使用逗号进行分隔，所有项需要使用双引号括住，比如'"是,否,不确定"'。

showErrorMessage 参数

showErrorMessage参数用于设置在单元格中的内容未通过数据验证时，是否显示相关的错误信息。默认值为False，不显示错误信息。

showInputMessage 参数

showInputMessage参数用于设置在选中单元格时，是否显示相关提示信息以提示用户如何进行输入。默认值为False，不显示提示信息。

showDropDown 参数

showDropDown参数用于设置在选中单元格时，是否显示列表框，该参数在type参数为'list'时有效。在一些 Office 软件中，showDropDown参数为False表示显示列表框。

allowBlank，allow_blank 参数

allowBlank参数和allow_blank参数用于设置是否允许单元格为空。默认值为False，不允许为空。

sqref 参数

sqref参数用于设置对哪些单元格进行数据验证，该参数可以是某个单元格或区域的地址，也可以是一个MultiCellRange对象或包含CellRange对象的 Python 元组。

promptTitle 参数

promptTitle参数是在选中单元格时，显示的提示信息的标题。

errorStyle 参数

errorStyle参数表示在单元格中的内容未通过数据验证时执行的操作，可以是以下取值之一，'stop'（输入的内容会被撤销），'warning'（显示警告），'information'（显示信息）。

error 参数

error参数是在单元格中的内容未通过数据验证时，显示的错误信息的内容。

prompt 参数

prompt参数是在选中单元格时，显示的提示信息的内容。

errorTitle 参数

errorTitle参数是在单元格中的内容未通过数据验证时，显示的错误信息的标题。

imeMode 参数

imeMode参数是一个表示输入法模式的字符串，他将在用户编辑单元格时自动切换输入法，imeMode可以是以下取值之一，'noControl'（不对 IME 进行任何控制），'off'（关闭），'on'（打开），'disabled'（禁用），'hiragana'（平假名），'fullKatakana'（全角片假名），'halfKatakana'（半角片假名），'fullAlpha'（全角字母数字），'halfAlpha'（半角字母数字），'fullHangul'（全角韩语），'halfHangul'（半角韩语）。

需要指出的是，并非所有的 Office 都会支持输入法的自动切换功能，该功能是否有效可能会与其他设置相关。

operator 参数

operator参数是一个表示运算方式的字符串，他与formula1，formula2两个参数一起完成数据的验证，可以是以下取值之一，'between'（在某个区域之间），'notBetween'（不在某个区域之间），'equal'（等于），'notEqual'（不等于），'lessThan'（小于），'lessThanOrEqual'（小于等于），'greaterThan'（大于），'greaterThanOrEqual'（大于等于）。

获取和设置 Excel 工作表中的数据验证

Worksheet对象的data_validations变量是一个DataValidationList对象，该对象的dataValidation变量是一个包含了 Excel 工作表中所有数据验证的 Python 列表，通过此 Python 列表可以获取或设置某个数据验证。

# 打开 Data.xlsx 中的工作表 Students
from openpyxl import load_workbook
workbook = load_workbook('Data.xlsx')
worksheet = workbook['Students']


dvs = worksheet.data_validations.dataValidation
# 显示 Excel 工作表中所有的数据验证
for dv in dvs:
	print(dv)


# 修改第二个数据验证
dv = dvs[1]
dv.errorTitle = '请选择一项'
dv.error = '只能选择男或女'
workbook.save('Save.xlsx')

<openpyxl.worksheet.datavalidation.DataValidation object>
Parameters:
sqref=<MultiCellRange [A1:A1002]>, showDropDown=False, showInputMessage=False, showErrorMessage=True, allowBlank=True, errorTitle=None, error=None, promptTitle=None, prompt=None, type='textLength', errorStyle='stop', imeMode=None, operator='between', formula1='2', formula2='4'
<openpyxl.worksheet.datavalidation.DataValidation object>
Parameters:
sqref=<MultiCellRange [B1:B1002]>, showDropDown=False, showInputMessage=False, showErrorMessage=True, allowBlank=True, errorTitle=None, error=None, promptTitle=None, prompt=None, type='list', errorStyle='stop', imeMode=None, operator='equal', formula1='"男,女"', formula2='0'

为 Excel 工作表添加数据验证

通过Worksheet对象的add_data_validation方法，或者Worksheet对象的data_validations变量的append方法，可以为 Excel 工作表添加一个数据验证。

worksheet.add_data_validation(data_validation)
worksheet.data_validations.append(data_validation)

data_validation 参数

data_validation参数是一个DataValidation对象，表示被添加的数据验证。

# …
# 打开 Data.xlsx 中的工作表 Students
from openpyxl import load_workbook
wb = load_workbook('Data.xlsx')
ws = wb['Students']


from openpyxl.worksheet.datavalidation import DataValidation
from openpyxl.worksheet.cell_range import MultiCellRange, CellRange


# 为 C1:C1001 添加数据验证，只能输入在 0 之间 100 的整数
newDV = DataValidation(
	'whole', 0, 100,
	sqref='C1:C1001', imeMode='off', operator='between'
	)
ws.add_data_validation(newDV)


# 为 D1:D2，D4:D5 两个区域添加数据验证，内容长度需要小于等于 5
ws.data_validations.append(DataValidation(
	'textLength', 5,
	sqref=(CellRange('D1:D2'), CellRange('D4:D5')), operator='lessThanOrEqual'
	))


# 为 E1:E2，E4:E5 两个区域添加数据验证，小数需要大于 1.5
ws.add_data_validation(DataValidation(
	'decimal', 1.5,
	sqref=MultiCellRange(['E1:E2', 'E4:E5']), operator='greaterThan'
	))


wb.save('Add.xlsx')

源码

src/zh/openpyxl/data/validations·codebeatme/office-programming·GitHub