openpyxl条件格式详解 - 自动高亮颜色渐变数据条教程

from openpyxl.formatting.rule import CellIsRule, ColorScaleRule, DataBarRule

ws.conditional_formatting.add(range_string, rule)

from openpyxl import Workbook
from openpyxl.formatting.rule import CellIsRule
from openpyxl.styles import Font, PatternFill

wb = Workbook()
ws = wb.active

# 写入数据
ws.append(['姓名', '成绩'])
scores = [['张三', 85], ['李四', 62], ['王五', 91],
          ['赵六', 45], ['钱七', 78], ['孙八', 55]]
for s in scores:
    ws.append(s)

# 条件格式1：成绩>=90，绿色背景
green_fill = PatternFill(start_color='C8E6C9', end_color='C8E6C9', fill_type='solid')
ws.conditional_formatting.add('B2:B7',
    CellIsRule(operator='greaterThanOrEqual', formula=['90'], fill=green_fill))

# 条件格式2：成绩<60，红色背景
red_fill = PatternFill(start_color='FFCDD2', end_color='FFCDD2', fill_type='solid')
red_font = Font(color='FF0000', bold=True)
ws.conditional_formatting.add('B2:B7',
    CellIsRule(operator='lessThan', formula=['60'], fill=red_fill, font=red_font))

# 条件格式3：成绩60-80，黄色背景
yellow_fill = PatternFill(start_color='FFF9C4', end_color='FFF9C4', fill_type='solid')
ws.conditional_formatting.add('B2:B7',
    CellIsRule(operator='between', formula=['60', '80'], fill=yellow_fill))

print(f"条件格式规则数: {len(ws.conditional_formatting._cf_rules)}")
wb.save('cond_format_cell.xlsx')
print("单元格条件格式文件已保存")

条件格式规则数: 3
单元格条件格式文件已保存

from openpyxl import Workbook
from openpyxl.formatting.rule import ColorScaleRule, DataBarRule

wb = Workbook()
ws = wb.active

# 写入数据
ws.append(['城市', '温度'])
temps = [['北京', 32], ['上海', 35], ['广州', 38],
         ['成都', 30], ['哈尔滨', 25], ['武汉', 37],
         ['深圳', 36], ['昆明', 22]]
for t in temps:
    ws.append(t)

# 颜色渐变：低温蓝色 -> 中温黄色 -> 高温红色
ws.conditional_formatting.add('B2:B9',
    ColorScaleRule(
        start_type='min', start_color='2196F3',
        mid_type='percentile', mid_value=50, mid_color='FFF176',
        end_type='max', end_color='F44336'
    ))

# 数据条
ws2 = wb.create_sheet('数据条')
ws2.append(['产品', '销量'])
sales = [['A', 100], ['B', 200], ['C', 150],
         ['D', 300], ['E', 250]]
for s in sales:
    ws2.append(s)

ws2.conditional_formatting.add('B2:B6',
    DataBarRule(
        start_type='min', end_type='max',
        color='2196F3'
    ))

print("颜色渐变与数据条设置完成")
wb.save('cond_format_colorscale.xlsx')
print("颜色渐变文件已保存")

颜色渐变与数据条设置完成
颜色渐变文件已保存

from openpyxl import Workbook
from openpyxl.formatting.rule import FormulaRule
from openpyxl.styles import Font, PatternFill

wb = Workbook()
ws = wb.active

# 写入数据
ws.append(['姓名', '部门', '工资'])
employees = [
    ['张三', '技术部', 15000],
    ['李四', '市场部', 12000],
    ['王五', '技术部', 18000],
    ['赵六', '财务部', 13000],
    ['钱七', '技术部', 16000],
]
for e in employees:
    ws.append(e)

# 公式条件格式：技术部整行高亮
tech_fill = PatternFill(start_color='E3F2FD', end_color='E3F2FD', fill_type='solid')
ws.conditional_formatting.add('A2:C6',
    FormulaRule(formula=['$B2="技术部"'], fill=tech_fill))

# 公式条件格式：工资>15000整行高亮
high_fill = PatternFill(start_color='FFF3E0', end_color='FFF3E0', fill_type='solid')
ws.conditional_formatting.add('A2:C6',
    FormulaRule(formula=['$C2>15000'], fill=high_fill))

print(f"条件格式规则数: {len(ws.conditional_formatting._cf_rules)}")
wb.save('cond_format_formula.xlsx')
print("公式条件格式文件已保存")

条件格式规则数: 2
公式条件格式文件已保存