最好的报表不是"好看"，而是"会自动告诉你哪里有问题"。
今天教你用数据验证和条件格式，打造一份会"自动报警"的Excel报表。
👇 看完本文你能学到什么？
1. 下拉列表、数据验证规则全面掌握
2. 条件格式自动高亮异常值和关键数据
3. 打造"会自动报警"的Excel报表
💡 文末有彩蛋：附赠数据验证模板代码
一、数据验证（Data Validation）
from openpyxl import Workbook from openpyxl.worksheet.datavalidation import DataValidation wb = Workbook() ws = wb.active # 1. 下拉列表 dv_list = DataValidation(type='list', formula1='"电子,服装,食品,家居"', allow_blank=True) dv_list.error = '请从列表中选择品类' dv_list.errorTitle = '输入错误' ws.add_data_validation(dv_list) dv_list.add('A2:A1000') # 应用到A列 # 2. 整数范围 dv_int = DataValidation(type='whole', operator='between', formula1=1, formula2=1000) dv_int.error = '请输入1-1000之间的整数' ws.add_data_validation(dv_int) dv_int.add('B2:B1000') # 3. 日期范围 dv_date = DataValidation(type='date', operator='between', formula1='2024-01-01', formula2='2024-12-31') dv_date.error = '日期必须在2024年范围内' ws.add_data_validation(dv_date) dv_date.add('C2:C1000') # 4. 小数范围 dv_decimal = DataValidation(type='decimal', operator='greaterThan', formula1=0) dv_decimal.error = '金额必须大于0' ws.add_data_validation(dv_decimal) dv_decimal.add('D2:D1000') # 5. 自定义公式验证 dv_formula = DataValidation(type='custom', formula1='=LEN(A2)<=20') dv_formula.error = '名称不能超过20个字符' ws.add_data_validation(dv_formula) dv_formula.add('A2:A1000')
二、条件格式实战
from openpyxl.formatting.rule import CellIsRule, FormulaRule, DataBarRule, ColorScaleRule # 1. 金额>10万高亮 ws.conditional_formatting.add('E2:E100', CellIsRule(operator='greaterThan', formula=['100000'], fill=PatternFill(start_color='FFD700', end_color='FFD700', fill_type='solid'), font=Font(bold=True, color='CC0000'))) # 2. 金额<0标红（负数） ws.conditional_formatting.add('E2:E100', CellIsRule(operator='lessThan', formula=['0'], fill=PatternFill(start_color='FF4444', end_color='FF4444', fill_type='solid'), font=Font(color='FFFFFF'))) # 3. 重复值标记 ws.conditional_formatting.add('A2:A1000', FormulaRule(formula=['COUNTIF($A$2:$A$1000,A2)>1'], fill=PatternFill(start_color='FFCCCC', end_color='FFCCCC', fill_type='solid'))) # 4. 数据条 ws.conditional_formatting.add('D2:D100', DataBarRule(start_type='min', end_type='max', color='4472C4')) # 5. 色阶 ws.conditional_formatting.add('C2:C100', ColorScaleRule(start_type='min', start_color='FF0000', mid_type='percentile', mid_value=50, mid_color='FFFF00', end_type='max', end_color='00FF00'))
三、智能报表——自动报警
def create_smart_report(df, output_path): '''创建带自动报警功能的报表''' wb = Workbook() ws = wb.active ws.title = '监控报表' # 写数据... for r_idx, row in enumerate(data, 2): for c_idx, val in enumerate(row, 1): ws.cell(r_idx, c_idx, val) last_row = len(data) + 1 # 自动报警规则 # 1. 金额为负数 → 红色 ws.conditional_formatting.add(f'E2:E{last_row}', CellIsRule(operator='lessThan', formula=['0'], fill=PatternFill(start_color='FF0000', end_color='FF0000', fill_type='solid'))) # 2. 金额超过平均值2倍 → 黄色（可能异常） ws.conditional_formatting.add(f'E2:E{last_row}', FormulaRule(formula=[f'E2>AVERAGE($E$2:$E${last_row})*2'], fill=PatternFill(start_color='FFD700', end_color='FFD700', fill_type='solid'))) # 3. 数据条可视化 ws.conditional_formatting.add(f'D2:D{last_row}', DataBarRule(start_type='min', end_type='max', color='4472C4')) wb.save(output_path) print(f"智能报表已生成: {output_path}")
今日小结
数据验证5种类型：列表、整数、日期、小数、自定义公式
条件格式5种：CellIsRule（值判断）、FormulaRule（公式）、DataBarRule（数据条）、ColorScaleRule（色阶）、IconSetRule（图标）
今日小练习
1. 给你的一份数据表加上数据验证：品类下拉列表、金额必须>0。
2. 给金额列加上数据条和负值红色标记。
3. 写一条公式条件格式：自动标记重复的订单号。
📌 往期精选：
👉 Excel图表编程：柱状图、折线图、饼图完整指南
🔔 关注公众号，回复「数据验证」领取：数据验证学习手册
（点击公众号底部菜单 → 领取资料）
