使用Python在Excel工作表中设置数据验证_Python

引言

在企业数据管理和报表系统中，数据的准确性和规范性至关重要。无论是员工信息录入、财务数据填报还是库存信息维护，都需要确保用户输入的数据符合业务规则。手动在 excel 中设置数据验证虽然可行，但当需要批量处理多个文件或统一验证规则时，效率低下且容易遗漏。通过 python 程序自动化设置 excel 数据验证，不仅可以快速批量处理，还能保证规则的一致性和可追溯性。

本文将使用 free spire.xls for python 演示如何在 excel 工作表中设置多种类型的数据验证，包括下拉列表、整数范围、小数范围、日期区间、文本长度和时间范围等，并结合实际业务场景帮助你理解数据验证的应用价值。

本文使用的方法需要用到 free spire.xls for python，可通过 pip 安装：

pip install spire.xls.free

1. 初始化工作簿和工作表

首先创建一个新的 excel 工作簿，并获取第一个工作表用于设置数据验证：

from spire.xls import *
from spire.xls.common import *
workbook = workbook()
sheet = workbook.worksheets[0]
sheet.name = "员工信息录入"
sheet.range["a1"].text = "所属部门"
sheet.range["b1"].text = "员工年龄"
sheet.range["c1"].text = "绩效得分"
sheet.range["d1"].text = "入职日期"
sheet.range["e1"].text = "员工工号"
sheet.range["f1"].text = "上班时间"

操作说明：
这里新建了一个 excel 工作簿并获取第一个工作表，命名为"员工信息录入"。我们在第一行设置了六个字段的表头，后续将针对每个字段设置不同的数据验证规则，确保录入数据的规范性。

2. 下拉列表验证（部门选择）

在实际业务中，员工所属部门通常是固定的几个选项，例如"人事部""财务部""技术部""市场部"。通过下拉列表验证，可以避免用户输入错误的部门名称，确保数据的统一性。

sheet.range["a2"].text = "可选部门："
sheet.range["a3"].text = "人事部"
sheet.range["a4"].text = "财务部"
sheet.range["a5"].text = "技术部"
sheet.range["a6"].text = "市场部"
dept_cell = sheet.range["b2"]
dept_cell.datavalidation.datarange = sheet.range["a3:a6"]
dept_cell.datavalidation.showerror = true
dept_cell.datavalidation.alertstyle = alertstyletype.stop
dept_cell.datavalidation.errortitle = "输入错误"
dept_cell.datavalidation.errormessage = "请从下拉列表中选择部门！"
dept_cell.datavalidation.showinput = true
dept_cell.datavalidation.inputtitle = "选择部门"
dept_cell.datavalidation.inputmessage = "请从固定部门列表中选择。"

使用场景：避免部门名称不统一（如"技术""技术部"混用），确保人事系统中的部门数据标准化。

保存文件后效果：

3. 整数验证（员工年龄）

员工年龄一般处于合理范围内，例如 18 到 60 岁。通过整数验证可以限制用户只能输入该范围内的整数值，避免出现异常数据。

sheet.range["b1"].text = "员工年龄 (18-60)"

age_cell = sheet.range["b3"]
age_cell.datavalidation.allowtype = celldatatype.integer
age_cell.datavalidation.compareoperator = validationcomparisonoperator.between
age_cell.datavalidation.formula1 = "18"
age_cell.datavalidation.formula2 = "60"
age_cell.datavalidation.alertstyle = alertstyletype.warning
age_cell.datavalidation.showerror = true
age_cell.datavalidation.errortitle = "年龄错误"
age_cell.datavalidation.errormessage = "请输入 18 到 60 之间的整数！"
age_cell.datavalidation.inputmessage = "员工年龄验证"
age_cell.datavalidation.ignoreblank = true
age_cell.datavalidation.showinput = true

使用场景：保证录入的年龄数据合理，不会出现"5 岁员工"或"100 岁员工"的异常数据，确保人事数据的真实性和合规性。

保存文件后效果：

4. 小数验证（绩效得分）

员工的绩效考核得分通常是带小数的数值，例如 0 到 100 分之间的小数。通过小数验证可以确保绩效数据的精确性和合理性。

sheet.range["c1"].text = "绩效得分 (0-100)"

score_cell = sheet.range["c2"]
score_cell.datavalidation.allowtype = celldatatype.decimal
score_cell.datavalidation.compareoperator = validationcomparisonoperator.between
score_cell.datavalidation.formula1 = "0"
score_cell.datavalidation.formula2 = "100"
score_cell.datavalidation.showerror = true
score_cell.datavalidation.errormessage = "绩效得分必须在 0 到 100 之间！"
score_cell.datavalidation.alertstyle = alertstyletype.stop

使用场景：适用于绩效考核、评分统计等需要小数精度的场景，避免输入超出范围的分数或无效数值。

5. 日期验证（入职日期）

企业通常要求员工入职日期在某一合理区间内。例如，数据录入系统只允许选择 2023 年内的入职日期，以防止录入历史错误数据。

sheet.range["d1"].text = "入职日期 (2023年)"

hire_date_cell = sheet.range["d2"]
hire_date_cell.datavalidation.allowtype = celldatatype.date
hire_date_cell.datavalidation.compareoperator = validationcomparisonoperator.between
hire_date_cell.datavalidation.formula1 = "2023-01-01"
hire_date_cell.datavalidation.formula2 = "2023-12-31"
hire_date_cell.datavalidation.showerror = true
hire_date_cell.datavalidation.errormessage = "请输入 2023 年的有效日期！"
hire_date_cell.datavalidation.alertstyle = alertstyletype.warning

使用场景：确保入职时间不会超出考勤和人事系统设定范围，避免录入未来日期或过于久远的历史日期。

保存文件后效果：

6. 文本长度验证（员工工号）

工号通常有固定的位数规则，例如必须是 6 位字符。通过文本长度验证可以保证工号录入规范，便于后续系统识别和处理。

sheet.range["e1"].text = "员工工号 (6位)"

id_cell = sheet.range["e2"]
id_cell.datavalidation.allowtype = celldatatype.textlength
id_cell.datavalidation.compareoperator = validationcomparisonoperator.equal
id_cell.datavalidation.formula1 = "6"
id_cell.datavalidation.showerror = true
id_cell.datavalidation.errormessage = "工号必须为 6 位字符！"
id_cell.datavalidation.alertstyle = alertstyletype.stop

使用场景：避免工号录入长度不一导致系统识别异常，确保所有工号格式统一，便于数据库存储和查询。

7. 时间验证（上班时间）

在考勤管理中，员工的上班时间通常需要在合理的时间范围内，例如上午 7:00 到 9:00 之间。通过时间验证可以规范考勤数据的录入。

sheet.range["f1"].text = "上班时间 (07:00-09:00)"

time_cell = sheet.range["f2"]
time_cell.datavalidation.allowtype = celldatatype.time
time_cell.datavalidation.compareoperator = validationcomparisonoperator.between
time_cell.datavalidation.formula1 = "07:00"
time_cell.datavalidation.formula2 = "09:00"
time_cell.datavalidation.alertstyle = alertstyletype.info
time_cell.datavalidation.showerror = true
time_cell.datavalidation.errortitle = "时间错误"
time_cell.datavalidation.errormessage = "上班时间应在 07:00 到 09:00 之间！"
time_cell.datavalidation.inputmessage = "上班时间验证"
time_cell.datavalidation.ignoreblank = true
time_cell.datavalidation.showinput = true

使用场景：适用于考勤系统、排班管理等场景，确保时间数据的合理性，便于统计分析和薪资计算。

8. 保存文件并调整格式

完成所有验证规则设置后，调整工作表的格式并保存为 excel 文件：

for col in range(1, 7):
    sheet.autofitcolumn(col)
workbook.savetofile("datavalidation.xlsx", excelversion.version2016)
workbook.dispose()

说明：
使用 autofitcolumn 方法自动调整列宽，使数据显示更加美观。最后将工作簿保存为 excel 2016 格式的文件，并释放资源。

关键类与属性总结

数据验证设置流程

获取单元格范围：通过 sheet.range["单元格地址"] 获取需要设置验证的单元格对象。
设置验证类型：通过 datavalidation.allowtype 指定验证类型（整数、小数、日期、时间、文本长度等）。
设置比较运算符：通过 datavalidation.compareoperator 指定比较方式（between、equal、lessorequal 等）。
设置验证条件：通过 formula1 和 formula2 设置验证参数值。
配置提示信息：设置 showerror、errormessage、showinput、inputmessage 等属性，提供用户友好的提示。
保存文件：使用 savetofile 方法保存工作簿。

关键类与属性对照表

类 / 属性	说明
`workbook`	表示 excel 工作簿，用于创建和保存文件
`worksheet`	表示 excel 工作表，所有操作都基于该对象
`cellrange`	表示单元格或单元格区域
`datavalidation`	用于设置单元格数据验证规则
`allowtype`	指定验证类型（整数、小数、日期、时间、文本长度等）
`compareoperator`	指定比较运算符（between、equal、lessorequal 等）
`formula1` / `formula2`	用于设置验证条件的参数值
`datarange`	用于设置下拉列表的数据源范围
`alertstyle`	错误提示样式（stop、warning、info）
`showerror`	是否显示错误提示
`errortitle`	错误提示标题
`errormessage`	错误提示信息
`showinput`	是否显示输入提示
`inputtitle`	输入提示标题
`inputmessage`	输入提示信息
`ignoreblank`	是否允许空值

总结

通过本文示例，你已经了解如何使用 free spire.xls for python 在 excel 工作表中设置多种类型的数据验证，包括下拉列表、整数范围、小数范围、日期区间、文本长度和时间范围。从初始化工作簿到设置各类验证规则，整个过程高度自动化，特别适用于批量生成带有数据验证规则的 excel 模板文件。

相比手动设置验证规则，代码方式具有以下优势：可以批量处理多个文件，保证规则一致性；可以轻松修改和扩展验证规则；可以与数据处理流程无缝集成。你可以在此基础上扩展更多能力，例如自定义公式验证、条件格式设置、批量数据导入等。

如果你正在处理员工信息录入、财务数据填报、库存管理等需要数据规范化验证的需求，这种基于 python 的 excel 数据验证方案将为你的工作带来显著提升。

以上就是使用python在excel工作表中设置数据验证的详细内容，更多关于python excel设置数据验证的资料请关注代码网其它相关文章！