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

引言

数据准确性和规范性，这事儿做数据管理和报表的朋友应该都深有体会。不管是员工信息录入、财务数据填报，还是库存信息维护，最怕的就是用户输入的数据五花八门，不符合业务规则。手动在Excel里挨个设置数据验证当然可行，但一旦涉及批量处理多个文件，或者要统一验证规则，那效率真是硬伤，还容易遗漏。而通过Python程序来自动化做这件事，不仅速度快，能批量处理几十上百个文件，关键是规则统一、过程可追溯——这对企业级的报表流程来说，价值非常高。

下面，我们用Free Spire.XLS for Python这个库，来演示如何在Excel工作表中设置多种类型的数据验证，包括下拉列表、整数范围、小数范围、日期区间、文本长度和时间范围等。每个验证都会结合一个实际的业务场景，方便理解其应用价值。

开始之前，先通过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岁。通过整数验证，可以限制用户只能输入这个范围内的整数值，避免出现“5岁员工”或“100岁员工”这种异常数据。

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

保证录入的年龄数据合理，让人事数据的真实性和合规性有据可依。

保存文件后效果：

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.Sa veToFile("DataValidation.xlsx", ExcelVersion.Version2016)
workbook.Dispose()

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

关键类与属性总结

数据验证设置流程

• 获取单元格范围：通过sheet.Range["单元格地址"]获取需要设置验证的单元格对象。
• 设置验证类型：通过DataValidation.AllowType指定验证类型（整数、小数、日期、时间、文本长度等）。
• 设置比较运算符：通过DataValidation.CompareOperator指定比较方式（Between、Equal、LessOrEqual等）。
• 设置验证条件：通过Formula1和Formula2设置验证参数值。
• 配置提示信息：设置ShowError、ErrorMessage、ShowInput、InputMessage等属性，提供用户友好的提示。
• 保存文件：使用Sa veToFile方法保存工作簿。

关键类与属性对照表

总结

通过本文的示例，我们用Free Spire.XLS for Python在Excel工作表中设置了多种类型的数据验证，包括下拉列表、整数范围、小数范围、日期区间、文本长度和时间范围。从初始化工作簿到设置各类验证规则，整个过程高度自动化，特别适用于批量生成带有数据验证规则的Excel模板文件。

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

如果正在处理员工信息录入、财务数据填报、库存管理等需要数据规范化验证的需求，这个基于Python的Excel数据验证方案，会是一个很实用的选择。