Python自动化:告别手动Excel数据验证,效率提升10倍!

pip install openpyxl pandas

from openpyxl import Workbookfrom openpyxl.worksheet.datavalidation import DataValidationwb = Workbook()ws = wb.activews.title = "员工信息"# 设置表头ws['A1'] = "姓名"ws['B1'] = "性别"ws['C1'] = "年龄"ws['D1'] = "入职日期"ws['E1'] = "手机号"# 创建下拉列表验证dv_list = DataValidation(type="list", formula1='"男,女"', allow_blank=True)dv_list.error = '请选择男或女'dv_list.errorTitle = '输入错误'ws.add_data_validation(dv_list)dv_list.add('B2:B100')  # 应用到B2到B100wb.save("员工信息表.xlsx")

# 年龄：18~60的整数dv_age = DataValidation(type="whole", operator="between", formula1=18, formula2=60)dv_age.error = '年龄必须是18到60之间的整数'dv_age.errorTitle = '输入错误'ws.add_data_validation(dv_age)dv_age.add('C2:C100')

from datetime import date# 入职日期：必须是日期格式，且大于2000-01-01dv_date = DataValidation(type="date", operator="greaterThan", formula1=date(2000,1,1))dv_date.error = '请输入2000年之后的日期'dv_date.errorTitle = '日期错误'ws.add_data_validation(dv_date)dv_date.add('D2:D100')

# 手机号：长度必须为11位dv_phone = DataValidation(type="textLength", operator="equal", formula1=11)dv_phone.error = '手机号必须为11位'dv_phone.errorTitle = '长度错误'ws.add_data_validation(dv_phone)dv_phone.add('E2:E100')

dv_custom = DataValidation(type="custom", formula1='=ISERROR(FIND(" ",A2))')dv_custom.error = '姓名不能包含空格'ws.add_data_validation(dv_custom)dv_custom.add('A2:A100')

import pandas as pddf = pd.read_excel("员工信息表.xlsx")# 1. 检查性别invalid_gender = df[~df['性别'].isin(['男', '女'])]# 2. 检查年龄invalid_age = df[(df['年龄'] < 18) | (df['年龄'] > 60) | (df['年龄'].isnull())]# 3. 检查手机号：长度11且全为数字invalid_phone = df[~df['手机号'].astype(str).str.match(r'^\d{11}$')]# 打印无效数据print("性别无效：\n", invalid_gender)print("年龄无效：\n", invalid_age)print("手机号无效：\n", invalid_phone)

from openpyxl import load_workbookfrom openpyxl.styles import PatternFillwb = load_workbook("员工信息表.xlsx")ws = wb.activered_fill = PatternFill(start_color="FFC7CE", end_color="FFC7CE", fill_type="solid")# 假设从第2行开始for row in range(2, ws.max_row + 1):    gender = ws.cell(row=row, column=2).value    if gender not in ['男', '女']:        ws.cell(row=row, column=2).fill = red_fill    age = ws.cell(row=row, column=3).value    if not isinstance(age, int) or age < 18 or age > 60:        ws.cell(row=row, column=3).fill = red_fillwb.save("员工信息表_检查结果.xlsx")

import osfrom openpyxl import load_workbookfrom openpyxl.worksheet.datavalidation import DataValidationfolder = "部门报表"for filename in os.listdir(folder):    if filename.endswith(".xlsx"):        filepath = os.path.join(folder, filename)        wb = load_workbook(filepath)        ws = wb.active        # 添加性别下拉        dv = DataValidation(type="list", formula1='"男,女"', allow_blank=True)        ws.add_data_validation(dv)        dv.add('B2:B1000')        # 添加年龄范围        dv_age = DataValidation(type="whole", operator="between", formula1=18, formula2=60)        ws.add_data_validation(dv_age)        dv_age.add('C2:C1000')        wb.save(filepath)        print(f"{filename} 处理完成")