影刀RPA Excel数据验证下拉列表与输入限制署名林焱什么情况用什么RPA流程生成了一份Excel模板需要让填表人只能从下拉列表选择值或者限制某些单元格只能填数字、日期。如果填错了会导致后续自动化流程报错。用openpyxl的数据验证功能可以给Excel添加下拉列表、数值范围限制等规则从源头防止错误数据。验证类型适用场景下拉列表固定选项是/否、状态码、分类数值范围限制数字范围0-100、0日期范围限制日期选择文本长度限制字符数自定义公式复杂验证逻辑怎么做一下拉列表importopenpyxlfromopenpyxl.worksheet.datavalidationimportDataValidation wbopenpyxl.Workbook()wswb.active ws.title录入表# 表头headers[姓名,部门,状态,优先级,完成度(%)]forcol,hinenumerate(headers,1):ws.cell(row1,columncol).valueh# 下拉列表1部门dept_dvDataValidation(typelist,formula1研发部,市场部,财务部,人事部,运营部,allow_blankTrue,showErrorMessageTrue,errorTitle输入错误,error请从下拉列表中选择部门)dept_dv.add(B2:B100)ws.add_data_validation(dept_dv)[video(video-82fgGAp2-1783501648805)(type-csdn)(url-https://live.csdn.net/v/embed/526818)(image-https://v-blog.csdnimg.cn/asset/582d14c3bd0451c5399cd990b56e2a0d/cover/Cover0.jpg)(title-拼多多店群自动化报活动上架)]# 下拉列表2状态status_dvDataValidation(typelist,formula1待开始,进行中,已完成,已取消,allow_blankTrue)status_dv.add(C2:C100)ws.add_data_validation(status_dv)# 下拉列表3优先级priority_dvDataValidation(typelist,formula1P0,P1,P2,P3,allow_blankTrue)priority_dv.add(D2:D100)ws.add_data_validation(priority_dv)wb.save(rD:\模板\任务录入表.xlsx)二数值范围验证# 完成度0-100的整数percent_dvDataValidation(typewhole,# whole整数, decimal小数operatorbetween,formula10,formula2100,allow_blankTrue,showErrorMessageTrue,errorTitle数值超出范围,error请输入0-100之间的整数)percent_dv.add(E2:E100)ws.add_data_validation(percent_dv)# 年龄必须大于0age_dvDataValidation(typewhole,operatorgreaterThan,formula10,allow_blankTrue,errorTitle输入错误,error年龄必须大于0)age_dv.add(F2:F100)ws.add_data_validation(age_dv)三日期范围验证# 日期不能晚于今天fromdatetimeimportdatetime date_dvDataValidation(typedate,operatorlessThanOrEqual,formula1datetime.now().strftime(%Y/%m/%d),allow_blankTrue,errorTitle日期错误,error日期不能晚于今天)date_dv.add(G2:G100)ws.add_data_validation(date_dv)四引用其他Sheet的列表# 当下拉选项很多时放在另一个Sheet里引用ws_listwb.create_sheet(下拉选项)ws_list[A1]部门列表departments[研发部,市场部,财务部,人事部,运营部,客服部,法务部]fori,deptinenumerate(departments,2):ws_list.cell(rowi,column1).valuedept# 引用其他Sheet的列表dept_ref_dvDataValidation(typelist,formula1下拉选项!$A$2:$A$8,allow_blankTrue)dept_ref_dv.add(B2:B100)ws.add_data_validation(dept_ref_dv)完整流程录入模板生成importopenpyxlfromopenpyxl.worksheet.datavalidationimportDataValidationfromopenpyxl.stylesimportFont,PatternFill,Alignment,Border,Sidefromdatetimeimportdatetime# yd_input: output_pathoutput_pathyd_input.get(output_path,rD:\模板\数据录入模板.xlsx)wbopenpyxl.Workbook()wswb.active ws.title录入表# 表头样式 header_fontFont(boldTrue,colorFFFFFF,size11)header_fillPatternFill(start_color4472C4,end_color4472C4,fill_typesolid)header_alignAlignment(horizontalcenter,verticalcenter)thin_borderBorder(leftSide(stylethin),rightSide(stylethin),topSide(stylethin),bottomSide(stylethin))# 表头 headers[序号,项目名称,负责人,部门,状态,优先级,开始日期,结束日期,完成度(%),备注]forcol,hinenumerate(headers,1):cellws.cell(row1,columncol)cell.valueh cell.fontheader_font cell.fillheader_fill cell.alignmentheader_align cell.borderthin_border# 数据验证 # 部门下拉dept_dvDataValidation(typelist,formula1研发部,市场部,财务部,人事部,运营部,allow_blankTrue)dept_dv.add(fD2:D200)ws.add_data_validation(dept_dv)# 状态下拉status_dvDataValidation(typelist,formula1待开始,进行中,已完成,已暂停,已取消,allow_blankTrue)status_dv.add(fE2:E200)ws.add_data_validation(status_dv)# 优先级下拉pri_dvDataValidation(typelist,formula1P0,P1,P2,P3,allow_blankTrue)pri_dv.add(fF2:F200)ws.add_data_validation(pri_dv)# 开始日期不晚于今天start_dvDataValidation(typedate,operatorlessThanOrEqual,formula1datetime.now().strftime(%Y/%m/%d),allow_blankTrue)start_dv.add(fG2:G200)ws.add_data_validation(start_dv)# 结束日期不早于开始日期end_dvDataValidation(typedate,operatorgreaterThanOrEqual,formula1G2,allow_blankTrue)end_dv.add(fH2:H200)ws.add_data_validation(end_dv)# 完成度0-100整数pct_dvDataValidation(typewhole,operatorbetween,formula10,formula2100,allow_blankTrue)pct_dv.add(fI2:I200)ws.add_data_validation(pct_dv)# 列宽 col_widths[6,25,12,12,12,10,14,14,12,30]forcol,widthinenumerate(col_widths,1):ws.column_dimensions[openpyxl.utils.get_column_letter(col)].widthwidth# 冻结首行 ws.freeze_panesA2wb.save(output_path)yd_output{status:ok,path:output_path}有什么坑坑一下拉列表选项超255字符限制现象下拉列表选项特别多生成后打开Excel看不到下拉箭头。原因Excel的内联下拉列表formula1选项1,选项2,...有255字符长度限制。解决选项多时放到单独Sheet引用# 错误选项超255字符# long_list 项目A,项目B,项目C,...超过255字符# dv DataValidation(typelist, formula1long_list) ❌# 正确选项放Sheet引用ws_listwb.create_sheet(选项数据)options[项目A,项目B,项目C,...]# 可以很长fori,optinenumerate(options,1):ws_list.cell(rowi,column1).valueopt dvDataValidation(typelist,formula1f选项数据!$A$1:$A${len(options)})坑二formula1的引号格式导致下拉列表为空现象生成了下拉列表但点开是空的。原因openpyxl的formula1对内联列表的格式要求严格——必须用英文双引号包裹整个列表选项之间用英文逗号分隔。解决TEMU店群矩阵自动化运营核价报活动# 正确英文双引号 英文逗号dvDataValidation(typelist,formula1选项A,选项B,选项C)# 错误中文逗号# dv DataValidation(typelist, formula1选项A选项B选项C) ❌# 错误少了外层双引号# dv DataValidation(typelist, formula1选项A,选项B,选项C) ❌# 错误用了单引号# dv DataValidation(typelist, formula1选项A,选项B,选项C) ❌坑三数据验证范围超出了实际数据区域现象给B2:B1000加了验证但数据只有50行打开Excel时提示修复。原因范围过大不是问题本身但如果同时设置了很多验证规则Excel的XML文件会膨胀。解决根据实际数据行数设置范围max_rowws.max_rowifws.max_row1else100# 范围设为实际行数一定余量dv.add(fB2:B{max_row50})坑四DataValidation对象不能复用现象想给两列设置相同的下拉列表复用了同一个DataValidation对象结果只有一列生效。原因openpyxl的DataValidation对象被add到一个范围后再次add另一个范围不会创建新规则而是修改原范围。同时一个DataValidation只能关联到一个worksheet。解决为每列创建独立的DataValidation对象# 错误复用对象# dv DataValidation(typelist, formula1A,B,C)# dv.add(B2:B100)# dv.add(D2:D100) ❌ 可能只有最后一个生效# ws.add_data_validation(dv)# 正确创建两个对象dv1DataValidation(typelist,formula1A,B,C)dv1.add(B2:B100)ws.add_data_validation(dv1)dv2DataValidation(typelist,formula1A,B,C)dv2.add(D2:D100)ws.add_data_validation(dv2)
)
