openpyxl数据验证详解 - 下拉列表输入限制与自定义公式教程

from openpyxl.worksheet.datavalidation import DataValidation

dv = DataValidation(type='whole', operator='between', formula1=1, formula2=100)
dv.error = '请输入1-100之间的整数'
dv.errorTitle = '输入错误'
dv.prompt = '请输入1-100的整数'
dv.promptTitle = '输入提示'

ws.add_data_validation(dv)
dv.add(cell)
dv.add('A2:A10')

from openpyxl import Workbook
from openpyxl.worksheet.datavalidation import DataValidation

wb = Workbook()
ws = wb.active

ws['A1'] = '年龄'
ws['B1'] = '部门'

# 整数验证：年龄1-150
dv_age = DataValidation(type='whole', operator='between', formula1=1, formula2=150)
dv_age.error = '年龄必须在1-150之间'
dv_age.errorTitle = '年龄错误'
dv_age.prompt = '请输入1-150之间的整数'
dv_age.promptTitle = '年龄'
ws.add_data_validation(dv_age)
dv_age.add('A2:A10')

# 列表验证：部门选择
dv_dept = DataValidation(type='list', formula1='"技术部,市场部,财务部,人事部"')
dv_dept.error = '请从列表中选择部门'
dv_dept.errorTitle = '部门错误'
dv_dept.prompt = '请选择部门'
dv_dept.promptTitle = '部门'
ws.add_data_validation(dv_dept)
dv_dept.add('B2:B10')

print(f"数据验证规则数: {len(ws.data_validations.dataValidation)}")
wb.save('validation_basic.xlsx')
print("基本验证文件已保存")

数据验证规则数: 2
基本验证文件已保存

from openpyxl import Workbook
from openpyxl.worksheet.datavalidation import DataValidation

wb = Workbook()
ws = wb.active

ws['A1'] = '入职日期'
ws['B1'] = '手机号'

# 日期验证：2020-01-01到2025-12-31
dv_date = DataValidation(type='date', operator='between',
                         formula1='2020-01-01', formula2='2025-12-31')
dv_date.error = '日期必须在2020-2025之间'
dv_date.errorTitle = '日期错误'
dv_date.prompt = '请输入2020-2025之间的日期'
ws.add_data_validation(dv_date)
dv_date.add('A2:A10')

# 文本长度验证：手机号11位
dv_phone = DataValidation(type='textLength', operator='equal', formula1='11')
dv_phone.error = '手机号必须是11位'
dv_phone.errorTitle = '手机号错误'
dv_phone.prompt = '请输入11位手机号'
ws.add_data_validation(dv_phone)
dv_phone.add('B2:B10')

print(f"数据验证规则数: {len(ws.data_validations.dataValidation)}")
wb.save('validation_date_text.xlsx')
print("日期文本验证文件已保存")

数据验证规则数: 2
日期文本验证文件已保存

from openpyxl import Workbook
from openpyxl.worksheet.datavalidation import DataValidation

wb = Workbook()
ws = wb.active

# 创建数据源工作表
ws_source = wb.create_sheet('数据源')
ws_source['A1'] = '城市'
cities = ['北京', '上海', '广州', '深圳', '杭州', '成都']
for i, city in enumerate(cities, 2):
    ws_source.cell(row=i, column=1, value=city)

# 主工作表
ws['A1'] = '姓名'
ws['B1'] = '城市'
ws['C1'] = '分数'

# 下拉列表验证（引用其他工作表数据）
dv_city = DataValidation(type='list', formula1='=数据源!$A$2:$A$7')
dv_city.error = '请从列表中选择城市'
dv_city.prompt = '请选择城市'
ws.add_data_validation(dv_city)
dv_city.add('B2:B20')

# 自定义公式验证：分数>=0且<=100
dv_score = DataValidation(type='custom', formula1='AND(C2>=0,C2<=100)')
dv_score.error = '分数必须在0-100之间'
dv_score.errorTitle = '分数错误'
dv_score.prompt = '请输入0-100之间的分数'
ws.add_data_validation(dv_score)
dv_score.add('C2:C20')

print(f"数据验证规则数: {len(ws.data_validations.dataValidation)}")
wb.save('validation_custom.xlsx')
print("自定义验证文件已保存")

数据验证规则数: 2
自定义验证文件已保存