openpyxl高级：样式美化、条件格式与模板

Python 办公自动化专题 · 打造专业级Excel报表的样式艺术

专题：Python 自动化办公系统学习

关键词：Python, 自动化办公, openpyxl, Excel样式, 条件格式, 模板, 报表美化, NamedStyle, Python自动化

一、样式系统概述

openpyxl 的样式体系是构建专业 Excel 报表的基石。与单纯写入数据不同，样式赋予了工作表视觉层次和信息传达能力。整个样式系统围绕几个核心对象展开：Font（字体）、Border（边框）、PatternFill（填充）、Alignment（对齐）以及将它们封装在一起的 NamedStyle（命名样式）。用户通过调整这些对象的属性，可以实现单元格级别的精细化控制。

每个单元格对象（Cell）都持有一个 Style 属性，这个属性内部包含了对上述对象的引用。当设置单元格样式时，实际上是在修改这些子对象的字段。openpyxl 采用"写时复制"（copy-on-write）的机制来管理样式，这意味着直接修改一个单元格的样式不会影响其他单元格，即便它们之前共享同一个样式对象。

理解样式优先级非常重要：行样式 < 列样式 < 单元格直接样式。当同时设置了行样式和单元格样式时，单元格样式会覆盖行样式。同样，NamedStyle 属于较底层的样式类型，如果在单元格上直接设置 Font 或 Fill 等属性，这些直接属性会覆盖 NamedStyle 中的对应部分。掌握这一套优先级规则，可以避免样式冲突和意外覆盖。

核心要点：openpyxl 的样式对象（Font/Border/Fill/Alignment）通过 Cell 的各个属性暴露给用户。修改样式就是修改这些对象的属性值。命名样式（NamedStyle）提供了一套可复用的样式集合，适合在批量场景下统一单元格外观。样式优先级为：单元格直接样式 > 列样式 > 行样式。

样式对象体系一览

样式对象	所属模块	核心属性
Font	openpyxl.styles	name, size, bold, italic, color, underline
Border / Side	openpyxl.styles	left, right, top, bottom, diagonal, outline
PatternFill / GradientFill	openpyxl.styles	fgColor, bgColor, fill_type / gradient_type, stop
Alignment	openpyxl.styles	horizontal, vertical, wrap_text, indent, text_rotation
NumberFormat	openpyxl.styles	format_code（如 '#,##0.00'）
NamedStyle	openpyxl.styles	name, font, border, fill, alignment, number_format

from openpyxl.styles import Font, Border, Side, PatternFill, Alignment, NamedStyle

# 创建各个样式对象
title_font = Font(name='微软雅黑', size=14, bold=True, color='1F4E79')
thin_border = Border(
    left=Side(style='thin', color='999999'),
    right=Side(style='thin', color='999999'),
    top=Side(style='thin', color='999999'),
    bottom=Side(style='thin', color='999999')
)
header_fill = PatternFill(start_color='4472C4', end_color='4472C4', fill_type='solid')
center_align = Alignment(horizontal='center', vertical='center', wrap_text=True)

# 将样式应用到单元格
ws['A1'].font = title_font
ws['A1'].border = thin_border
ws['A1'].fill = header_fill
ws['A1'].alignment = center_align

from openpyxl import Workbook
from openpyxl.styles import Font, PatternFill, Alignment

wb = Workbook()
ws = wb.active
ws.title = '样式示例'

# 写入表头并应用样式
headers = ['姓名', '部门', '销售额', '业绩评级']
header_font = Font(bold=True, color='FFFFFF', size=11)
header_fill = PatternFill(start_color='2F5496', end_color='2F5496', fill_type='solid')
header_align = Alignment(horizontal='center', vertical='center')

for col, header in enumerate(headers, 1):
    cell = ws.cell(row=1, column=col, value=header)
    cell.font = header_font
    cell.fill = header_fill
    cell.alignment = header_align

wb.save('style_demo.xlsx')

二、字体与数字格式

Font 对象是样式系统中最常用的组件之一。它控制着文本的外观：字体名称（name）、字号（size）、加粗（bold）、斜体（italic）、下划线（underline）、删除线（strikethrough）和颜色（color）。字体颜色支持三种指定方式：RGB 十六进制字符串（如 'FF0000'）、主题色（theme 属性搭配 tint 属性）和索引色（indexed 属性）。在实际应用中，建议优先使用 RGB 字符串，因为它最直观且跨平台一致性最好。

数字格式（NumberFormat）是经常被忽略但极为强大的样式能力。通过设置单元格的 number_format 属性，可以控制数字、日期、货币等数据类型在 Excel 中的显示方式。openpyxl 支持所有 Excel 原生的格式代码，常见的如 'YYYY-MM-DD' 用于日期显示、'#,##0.00' 用于千分位数字、'¥#,##0' 用于人民币格式。自定义格式代码还可以实现条件显示，例如 '[红色]0.00;[蓝色]-0.00' 让正数显示为红色、负数显示为蓝色。

字体和数字格式的组合使用，可以让报表中的数据一目了然。例如，将关键指标的大数字加粗显示，同时将日期格式统一为易于阅读的形式。需要注意的是，数字格式只改变显示方式，不改变单元格的实际值，这对后续的数据分析和公式计算非常重要。

核心要点：Font 的 color 属性接受 Color 对象，可以通过 rgb（如 'FF0000'）、theme（配合 tint）或 indexed 三种方式指定。NumberFormat 的 format_code 遵循 Excel 的本土格式代码语法，例如 #,##0.00 表示千分位保留两位小数，YYYY-MM-DD 表示日期格式。数字格式不改变底层数值。

from openpyxl.styles import Font, numbers

# Font 对象的各种用法
font1 = Font(name='Arial', size=12, bold=True, italic=False, color='FF0000')
font2 = Font(name='微软雅黑', size=10, bold=False, color='2F5496', underline='single')
font3 = Font(name='Consolas', size=11, color='333333', strike=None)

# 颜色专题：RGB / 主题色 / 索引色
from openpyxl.styles import Color
rgb_color = Color(rgb='FF0000')            # 纯红色
theme_color = Color(theme=0, tint=-0.25)   # 主题色 + 色调微调
indexed_color = Color(indexed=3)            # 索引色 3 号

# 应用字体
cell = ws['A1']
cell.value = 'Hello World'
cell.font = Font(name='Arial', size=14, bold=True, color='1F4E79')

from openpyxl.styles import Font
from datetime import datetime, date

# 数字格式实战
ws['A1'] = 1234567.89
ws['A1'].number_format = '#,##0.00'          # 显示为 1,234,567.89

ws['A2'] = 0.85
ws['A2'].number_format = '0.00%'             # 显示为 85.00%

ws['A3'] = 987654
ws['A3'].number_format = '¥#,##0'           # 显示为 ¥987,654

ws['A4'] = date(2025, 12, 31)
ws['A4'].number_format = 'YYYY-MM-DD'       # 显示为 2025-12-31

ws['A5'] = 0.5
ws['A5'].number_format = '[红色]0%↑;[绿色]-0%↓;0%'  # 条件格式显示

# 同时设置字体和数字格式
header_cell = ws['B1']
header_cell.value = '同比增长率'
header_cell.font = Font(bold=True, size=11, color='333333')
header_cell.number_format = '0.0%'

wb.save('number_format_demo.xlsx')

from openpyxl.styles import Font
from openpyxl.utils import get_column_letter

# 批量设置字体样式
def apply_header_style(ws, row=1, font_size=11):
    """给工作表表头行统一设置字体样式"""
    header_font = Font(name='微软雅黑', size=font_size, bold=True, color='FFFFFF')
    for col in range(1, ws.max_column + 1):
        cell = ws.cell(row=row, column=col)
        cell.font = header_font

# 数据区域交替行字体颜色
data_font = Font(name='微软雅黑', size=10, color='333333')
alt_font = Font(name='微软雅黑', size=10, color='555555')
for row in range(2, ws.max_row + 1):
    row_font = data_font if row % 2 == 0 else alt_font
    for col in range(1, ws.max_column + 1):
        ws.cell(row=row, column=col).font = row_font

三、边框与填充

边框（Border）由四个 Side（边）对象组成：left、right、top、bottom，以及可选的 diagonal（对角线）。每个 Side 对象有三个属性：style（线型）、color（颜色）和 border_style（原生样式名）。常用的线型包括 'thin'（细线）、'medium'（中等线）、'thick'（粗线）、'dashed'（虚线）、'dotted'（点线）、'double'（双线）等。边框可以单独设置每一条边，这在制作自定义表格效果时非常有用。

填充（Fill）分为两类：PatternFill（图案填充）和 GradientFill（渐变填充）。PatternFill 是最常用的类型，通过设置 fill_type='solid' 并指定 start_color 和 end_color（实心填充时设为相同颜色）来实现背景色。此外还支持 'gray125'、'lightDown'、'lightGrid' 等图案类型。GradientFill 支持线性渐变和路径渐变，通过定义多个色标（stop）实现丰富的渐变效果。

边框与填充的组合是美化报表的核心手段。表头通常使用深色填充配合白色字体，数据区域使用细边框界定单元格，汇总行使用顶部粗边框进行视觉分隔。合理运用双线边框可以模拟会计表格的下划线效果，而浅色交替行填充（斑马线）则可以大幅提升长表格的可读性。

核心要点：Border 接受四个 Side 参数，每个 Side 的 style 属性决定线型。PatternFill 的 fill_type='solid' 可实现纯色背景。GradientFill 通过 stop 列表定义渐变色标，每个色标是一个 (position, color) 元组。边框和填充配合使用可以制作出接近专业设计水平的报表。

from openpyxl.styles import Border, Side, PatternFill, GradientFill

# 各种边框样式
thin_border = Border(
    left=Side(style='thin', color='C0C0C0'),
    right=Side(style='thin', color='C0C0C0'),
    top=Side(style='thin', color='C0C0C0'),
    bottom=Side(style='thin', color='C0C0C0')
)

# 会计风格：底部双线
accounting_border = Border(
    bottom=Side(style='double', color='000000'),
    top=Side(style='thin', color='000000')
)

# 外粗内细的经典表格框线
outer_border = Border(
    left=Side(style='medium', color='2F5496'),
    right=Side(style='medium', color='2F5496'),
    top=Side(style='medium', color='2F5496'),
    bottom=Side(style='medium', color='2F5496')
)

# 应用边框
ws['A1'].border = thin_border
ws['A10'].border = accounting_border

from openpyxl.styles import PatternFill, GradientFill

# 实用填充样式
# 1. 实心填充 - 表头深蓝
header_fill = PatternFill(start_color='2F5496', end_color='2F5496', fill_type='solid')

# 2. 实心填充 - 交替行浅灰
even_fill = PatternFill(start_color='F2F2F2', end_color='F2F2F2', fill_type='solid')
odd_fill = PatternFill(start_color='FFFFFF', end_color='FFFFFF', fill_type='solid')

# 3. 实心填充 - 警告色
warn_fill = PatternFill(start_color='FFF2CC', end_color='FFF2CC', fill_type='solid')  # 浅黄
error_fill = PatternFill(start_color='FCE4EC', end_color='FCE4EC', fill_type='solid') # 浅红

# 4. 渐变填充
grad_fill = GradientFill(
    gradient_type='linear',
    degree=90,
    stop=[('FF6B35', 0), ('F7C948', 0.5), ('FFFFFF', 1)]
)

# 斑马线效果
for row in range(2, 100):
    fill = even_fill if row % 2 == 0 else odd_fill
    for col in range(1, 10):
        ws.cell(row=row, column=col).fill = fill

# 条件性填充：销售额低于目标标红
for row in range(2, ws.max_row + 1):
    sales = ws.cell(row=row, column=3).value
    if sales is not None and sales < 10000:
        ws.cell(row=row, column=3).fill = error_fill

# 边框和填充的批量工具函数

def apply_table_style(ws, header_rows=1, border_color='C0C0C0'):
    """快速给工作表添加规范化表格样式"""
    from openpyxl.styles import Border, Side, PatternFill
    thin_side = Side(style='thin', color=border_color)
    table_border = Border(left=thin_side, right=thin_side, top=thin_side, bottom=thin_side)

    max_row = ws.max_row
    max_col = ws.max_column

    # 表头样式
    header_fill = PatternFill(start_color='2F5496', end_color='2F5496', fill_type='solid')
    for r in range(1, header_rows + 1):
        for c in range(1, max_col + 1):
            cell = ws.cell(row=r, column=c)
            cell.border = table_border
            cell.fill = header_fill

    # 数据区域
    data_fill_even = PatternFill(start_color='F5F5F5', end_color='F5F5F5', fill_type='solid')
    for r in range(header_rows + 1, max_row + 1):
        for c in range(1, max_col + 1):
            ws.cell(row=r, column=c).border = table_border
            if (r - header_rows) % 2 == 0:
                ws.cell(row=r, column=c).fill = data_fill_even

四、对齐与缩进

Alignment 对象控制单元格内文本的排列方式。水平对齐（horizontal）支持 'general'（常规）、'left'（左对齐）、'center'（居中）、'right'（右对齐）、'justify'（两端对齐）和 'centerContinuous'（跨列居中）。垂直对齐（vertical）支持 'top'（顶端）、'center'（居中）、'bottom'（底端）和 'justify'（两端对齐）。wrap_text 属性控制是否自动换行，当单元格内容较长时非常实用。

缩进（indent）和文本旋转（text_rotation）是相对不那么常用但非常重要的排版功能。缩进用于在单元格内创建层次感，例如在树形结构的数据中，通过不同缩进量来表示层级关系。text_rotation 接受 -90 到 90 之间的整数，用于旋转文本方向，常在窄列表头中使用以节省空间。合并单元格的对齐需要特别处理，必须在合并后对合并区域左上角单元格设置 alignment。

另一种常见需求是垂直居中和对齐合并单元格的内容。当使用 merge_cells 合并多个单元格后，只有左上角的单元格保留了值和对齐属性，其余被合并的单元格会变为空。因此，对齐设置必须在 merge_cells 之后进行，且只设置左上角单元格即可。

核心要点：Alignment 的 horizontal 和 vertical 控制基本对齐方式，wrap_text=True 启用自动换行，indent 控制缩进量（以字符宽度为单位），text_rotation 控制旋转角度。合并单元格的对齐必须在 merge_cells 之后只对左上角单元格设置。centerContinuous 可以实现不合并单元格的跨列居中效果。

from openpyxl.styles import Alignment

# 各种对齐方式
align_center = Alignment(horizontal='center', vertical='center')
align_left_top = Alignment(horizontal='left', vertical='top')
align_right_bottom = Alignment(horizontal='right', vertical='bottom')
align_wrap = Alignment(horizontal='left', vertical='top', wrap_text=True)

# 缩进应用 - 模拟层级
align_level0 = Alignment(horizontal='left', vertical='center', indent=0)
align_level1 = Alignment(horizontal='left', vertical='center', indent=2)
align_level2 = Alignment(horizontal='left', vertical='center', indent=4)

# 文本旋转 - 窄列表头
align_rotate = Alignment(horizontal='center', vertical='bottom', text_rotation=90)

# 应用到单元格
ws['A1'].alignment = align_center
ws['B2'].alignment = align_wrap
ws['C3'].alignment = align_rotate

# 合并单元格对齐
ws.merge_cells('A1:D1')
ws['A1'] = '2025年度销售汇总报告'
ws['A1'].alignment = Alignment(horizontal='center', vertical='center')
ws['A1'].font = Font(bold=True, size=16, color='1F4E79')

# 多行合并垂直居中
ws.merge_cells('A2:A10')
ws['A2'] = '分类：电子产品'
ws['A2'].alignment = Alignment(horizontal='center', vertical='center', wrap_text=True)

# 自动换行与行高自适应
ws['B5'] = '这是一段非常长的文本内容，需要自动换行来完整显示'
ws['B5'].alignment = Alignment(wrap_text=True, vertical='top')
# 设置行高以容纳多行文本
ws.row_dimensions[5].height = 60

# 批量设置对齐函数

def format_column_headers(ws, row=1):
    """统一格式化表头行：居中、加粗、自动换行"""
    from openpyxl.styles import Alignment, Font
    align = Alignment(horizontal='center', vertical='center', wrap_text=True)
    font = Font(bold=True, size=10)
    for col in range(1, ws.max_column + 1):
        cell = ws.cell(row=row, column=col)
        cell.alignment = align
        cell.font = font

def auto_adjust_row_height(ws, min_height=20, max_height=100):
    """根据内容自动调整行高（近似算法）"""
    for row in ws.iter_rows(min_row=1, max_row=ws.max_row):
        max_lines = 1
        for cell in row:
            if cell.alignment.wrap_text and cell.value:
                text = str(cell.value)
                # 估算行数：字符数 / 每行字符数
                col_width = ws.column_dimensions[cell.column_letter].width or 10
                lines = len(text) / max(col_width * 0.8, 1) + text.count('\n')
                max_lines = max(max_lines, int(lines) + 1)
        height = min(max(min_height * max_lines, min_height), max_height)
        ws.row_dimensions[row[0].row].height = height

五、命名样式

NamedStyle（命名样式）是 openpyxl 提供的一种样式复用机制，类似于 Excel 中原生的"单元格样式"功能。通过创建 NamedStyle 对象并将其注册到工作簿，可以在多个单元格之间共享同一套样式定义。当需要修改这些单元格的样式时，只需修改 NamedStyle 对象的属性，所有引用了该样式的单元格会自动更新。

创建 NamedStyle 的流程很简单：实例化 NamedStyle 并指定名称，然后设置其 font、border、fill、alignment、number_format 等子对象的属性。最后通过工作簿的 add_named_style 方法将其注册。使用命名样式时，直接将样式名称赋值给单元格的 style 属性即可。命名样式还可以作为模板，通过 copy() 方法生成变体，在基础样式上微调。

openpyxl 内置了一些预定义样式，例如 'Normal'、'Title'、'Headline 1' 等。但这些内置样式的可用性取决于 Excel 版本和语言设置，因此在实际开发中，更推荐自定义命名样式。需要注意的是，命名样式的名称在整个工作簿中必须唯一，且不能超过 255 个字符。

核心要点：NamedStyle 通过 wb.add_named_style(style) 注册到工作簿，然后在单元格上通过 cell.style = 'style_name' 引用。修改 NamedStyle 的属性会同步更新所有引用它的单元格。NamedStyle.copy() 可创建包含所有属性的深拷贝变体。命名样式名称在工作簿内必须唯一。

from openpyxl.styles import NamedStyle, Font, Border, Side, PatternFill, Alignment

# 创建命名样式 - 表头
header_style = NamedStyle(name='CustomHeader')
header_style.font = Font(name='微软雅黑', size=11, bold=True, color='FFFFFF')
header_style.fill = PatternFill(start_color='2F5496', end_color='2F5496', fill_type='solid')
header_style.alignment = Alignment(horizontal='center', vertical='center')
header_style.border = Border(
    left=Side(style='thin'), right=Side(style='thin'),
    top=Side(style='medium'), bottom=Side(style='medium')
)
# 注册到工作簿
wb.add_named_style(header_style)

# 创建命名样式 - 数据单元格
data_style = NamedStyle(name='CustomData')
data_style.font = Font(name='微软雅黑', size=10)
data_style.alignment = Alignment(horizontal='left', vertical='center')
data_style.border = Border(
    left=Side(style='thin', color='D9D9D9'),
    right=Side(style='thin', color='D9D9D9'),
    top=Side(style='thin', color='D9D9D9'),
    bottom=Side(style='thin', color='D9D9D9')
)
wb.add_named_style(data_style)

# 应用命名样式
ws['A1'].style = 'CustomHeader'
ws['A2'].style = 'CustomData'

# 命名样式的复制与变体
from copy import copy

# 基于已有样式创建变体
danger_style = NamedStyle(name='DangerCell')
danger_style.font = Font(bold=True, color='B71C1C')
danger_style.fill = PatternFill(start_color='FFCDD2', end_color='FFCDD2', fill_type='solid')
danger_style.alignment = Alignment(horizontal='center', vertical='center')
danger_style.border = copy(data_style.border)  # 复用数据样式的边框
wb.add_named_style(danger_style)

# 修改命名样式后自动全局生效
header_style.font.color = 'FFD700'  # 将所有表头文字改为金色
# 所有设置了 style='CustomHeader' 的单元格自动更新

# 遍历所有命名样式
for style in wb.named_styles:
    print(f'样式名称: {style.name}, 字体: {style.font.name}')

# 完整的样式管理系统
class ExcelStyleManager:
    """Excel 命名样式管理器"""

    def __init__(self, workbook):
        self.wb = workbook
        self._register_default_styles()

    def _register_default_styles(self):
        """注册默认样式集"""
        styles = {
            'title': NamedStyle(name='Report_Title'),
            'header': NamedStyle(name='Report_Header'),
            'data': NamedStyle(name='Report_Data'),
            'total': NamedStyle(name='Report_Total'),
        }
        styles['title'].font = Font(name='微软雅黑', size=16, bold=True, color='1F4E79')
        styles['header'].font = Font(name='微软雅黑', size=11, bold=True, color='FFFFFF')
        styles['header'].fill = PatternFill(start_color='2F5496', end_color='2F5496', fill_type='solid')
        styles['data'].font = Font(name='微软雅黑', size=10)
        styles['total'].font = Font(name='微软雅黑', size=11, bold=True)
        styles['total'].fill = PatternFill(start_color='D6E4F0', end_color='D6E4F0', fill_type='solid')

        for style in styles.values():
            style.alignment = Alignment(horizontal='center', vertical='center')
            style.border = Border(
                left=Side(style='thin', color='BFBFBF'),
                right=Side(style='thin', color='BFBFBF'),
                top=Side(style='thin', color='BFBFBF'),
                bottom=Side(style='thin', color='BFBFBF')
            )
            self.wb.add_named_style(style)

    def apply(self, cell, style_name):
        """给单元格应用命名样式"""
        cell.style = style_name

六、条件格式基础

条件格式是 Excel 数据分析的核心功能之一，它允许根据单元格的值动态改变其外观。openpyxl 通过 openpyxl.formatting 模块提供了完整的条件格式支持。条件格式的核心思想是：定义一个或多个规则，每个规则包含一个条件判断和一组样式（字体、填充、边框），当条件满足时，对应的样式自动应用到目标单元格区域。

最基础的条件格式是"单元格值规则"（CellIsRule），它通过与一个固定值比较（大于、小于、等于、介于等）来决定是否应用样式。例如，为所有销售额大于 10000 的单元格设置绿色填充。此外还有"顶部/底部规则"（Top10Rule），用于标记排名靠前或靠后的单元格；以及"平均值规则"（AverageRule），用于标记高于或低于平均值的单元格。

使用条件格式的基本流程是：首先创建规则对象（如 CellIsRule），指定条件和样式；然后获取工作表的 conditional_formatting 集合；最后调用 add(cell_range, rule) 方法将规则应用到指定区域。一个区域可以叠加多个规则，Excel 会按照规则添加的顺序依次评估，优先级数字越小越先评估。

核心要点：条件格式通过 conditional_formatting.add(range, rule) 添加到工作表。CellIsRule 支持 operator='greaterThan'/'lessThan'/'equal'/'between' 等操作符。Top10Rule 可筛选排名前/后 N 项或百分比。AverageRule 支持 aboveAverage 和 aboveEqual 等类型。多个规则叠加时按添加顺序评估。

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

# 单元格值规则
highlight_fill = PatternFill(start_color='C6EFCE', end_color='C6EFCE', fill_type='solid')
highlight_font = Font(bold=True, color='006100')

# 标记销售额大于 50000 的单元格
ws.conditional_formatting.add(
    'C2:C100',
    CellIsRule(
        operator='greaterThan',
        formula=['50000'],
        fill=highlight_fill,
        font=highlight_font
    )
)

# 标记介于范围的单元格
light_red_fill = PatternFill(start_color='FFC7CE', end_color='FFC7CE', fill_type='solid')
ws.conditional_formatting.add(
    'C2:C100',
    CellIsRule(
        operator='between',
        formula=['30000', '50000'],
        fill=light_red_fill
    )
)

from openpyxl.formatting.rule import Top10Rule, AverageRule

# Top 10 规则：标记业绩前 5 名
ws.conditional_formatting.add(
    'D2:D100',
    Top10Rule(
        rank=5,
        percent=False,
        top=True,   # True=前N名, False=后N名
        fill=PatternFill(start_color='C6EFCE', end_color='C6EFCE', fill_type='solid'),
        font=Font(bold=True, color='006100')
    )
)

# 高于平均值规则
ws.conditional_formatting.add(
    'E2:E100',
    AverageRule(
        aboveAverage=True,
        fill=PatternFill(start_color='D9E2F3', end_color='D9E2F3', fill_type='solid')
    )
)

# 低于平均值规则
ws.conditional_formatting.add(
    'E2:E100',
    AverageRule(
        aboveAverage=False,
        fill=PatternFill(start_color='FCE4EC', end_color='FCE4EC', fill_type='solid')
    )
)

# 唯一值/重复值规则
from openpyxl.formatting.rule import CellIsRule, FormulaRule

# 标记重复值（使用 COUNTIF 公式）
ws.conditional_formatting.add(
    'A2:A100',
    FormulaRule(
        formula=['COUNTIF(A$2:A$100, A2) > 1'],
        fill=PatternFill(start_color='FFC7CE', end_color='FFC7CE', fill_type='solid'),
        font=Font(color='9C0006')
    )
)

# 标记空单元格
ws.conditional_formatting.add(
    'B2:B100',
    FormulaRule(
        formula=['ISBLANK(B2)'],
        fill=PatternFill(start_color='FFFFCC', end_color='FFFFCC', fill_type='solid')
    )
)

# 管理条件格式：查看和删除
print(f'条件格式规则数: {len(ws.conditional_formatting._cf_rules)}')
# 删除指定区域的所有条件格式
# del ws.conditional_formatting['C2:C100']

七、条件格式高级

除了基础规则外，openpyxl 还支持三类更高级的条件格式：色阶（ColorScale）、数据条（DataBar）和图标集（IconSet）。这三类规则通过颜色的渐变、条形的长短和图标的变化来直观展示数据的相对大小，非常适合制作仪表盘和看板类的报表。

色阶规则（ColorScaleRule）通过两种或三种颜色的渐变来映射数据范围。例如，双色阶从最小值（红色）渐变到最大值（绿色），中间值呈现过渡色。用户可以自定义颜色和对应的数值类型（最低值/百分位/数字/公式）。数据条规则（DataBarRule）在每个单元格内绘制彩色条形图，条形的长度与该单元格值在整个范围内的相对大小成正比。数据条支持实心填充和渐变填充两种模式。

图标集规则（IconSetRule）根据数据的分位点或阈值，为每个单元格分配一个图标（如箭头、交通灯、信号柱、星星等）。例如，"三色箭头"图标集可以将数据分为三组：增长（绿色上箭头）、持平（黄色横箭头）和下降（红色下箭头）。图标集非常直观，特别适合在报告摘要中展示趋势和状态。

核心要点：ColorScaleRule 接受 2~3 个颜色点定义（type/value/color）。DataBarRule 接受颜色、填充类型和是否显示值等参数。IconSetRule 通过 icon_style 选择图标类型（如 '3Arrows'/'3TrafficLights'/'5Quarters'），通过 type 和 values 定义阈值分界。公式条件格式（FormulaRule）使用 Excel 公式作为条件，灵活性最高。

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

# 色阶规则：双色阶（红→绿）
ws.conditional_formatting.add(
    'C2:C100',
    ColorScaleRule(
        start_type='min', start_color='F8696B',
        end_type='max', end_color='63BE7B'
    )
)

# 色阶规则：三色阶（红→黄→绿）
ws.conditional_formatting.add(
    'D2:D100',
    ColorScaleRule(
        start_type='percentile', start_value=0, start_color='F8696B',
        mid_type='percentile', mid_value=50, mid_color='FFEB84',
        end_type='percentile', end_value=100, end_color='63BE7B'
    )
)

# 数据条规则
ws.conditional_formatting.add(
    'E2:E100',
    DataBarRule(
        start_type='min', end_type='max',
        color='5B9BD5',            # 条形颜色
        showValue=True,            # 是否显示数值
        minLength=None,            # 最小条形长度
        maxLength=None             # 最大条形长度
    )
)

# 渐变色数据条
ws.conditional_formatting.add(
    'F2:F100',
    DataBarRule(
        start_type='min', end_type='max',
        color='70AD47',
        showValue=True,
        gradient=True              # 渐变色填充
    )
)

# 图标集规则：三色箭头
ws.conditional_formatting.add(
    'G2:G100',
    IconSetRule(
        icon_style='3Arrows',      # 三色箭头
        type='percent',
        values=[0, 33, 67],       # 阈值百分比
        showValue=True
    )
)

# 图标集：三色交通灯
ws.conditional_formatting.add(
    'H2:H100',
    IconSetRule(
        icon_style='3TrafficLights',
        type='num',
        values=[0, 50, 80],        # 以具体数值为阈值
        showValue=True
    )
)

# 公式条件格式高级用法
from openpyxl.formatting.rule import FormulaRule

# 跨列判断：标记整行
# 当 B 列值为 "销售部" 时，标记该行全部单元格
ws.conditional_formatting.add(
    'A2:H100',
    FormulaRule(
        formula=['$B2="销售部"'],
        fill=PatternFill(start_color='E2EFDA', end_color='E2EFDA', fill_type='solid')
    )
)

# 基于日期条件：标记即将到期项
# 假设 E 列为截止日期，距今天不足 7 天时标黄
ws.conditional_formatting.add(
    'A2:H100',
    FormulaRule(
        formula=['AND($E2>TODAY(), $E2-TODAY()<=7)'],
        fill=PatternFill(start_color='FFF2CC', end_color='FFF2CC', fill_type='solid'),
        font=Font(bold=True, color='9C6500')
    )
)

# 标记已过期项
ws.conditional_formatting.add(
    'A2:H100',
    FormulaRule(
        formula=['$E2
        

八、模板化报表

在实际的企业报表开发中，直接在每个脚本中硬编码样式逻辑会导致代码冗余严重、维护成本高。模板化报表的思想是将样式定义、布局结构和数据填充逻辑分离，形成可复用的报表生成框架。一个成熟的模板系统通常包含三层：样式模板层（定义共用样式集）、布局模板层（定义标题/表头/表尾等结构）、和数据填充层（定义数据来源和映射规则）。

函数式报表生成是模板化的一种轻量级实现。通过编写一系列报表生成函数，如 make_header()、make_table()、make_summary() 和 make_footer()，将不同类型报表共用的逻辑抽取为可组合的函数。每个函数接收工作表对象和参数，返回修改后的工作表。这种模式在单一报表类型场景下极为高效。

进一步抽象，可以走向配置驱动报表生成。将报表布局、样式引用、数据映射等信息写入 JSON 或 YAML 配置文件，通用的生成引擎读取配置后自动渲染报表。这意味着新增一种报表类型时，只需要新增配置文件，无需编写 Python 代码。批量报表生成则通过循环遍历多个数据源和配置组合，一次性输出整套报表。

核心要点：模板化报表的核心思路是"样式-布局-数据"三层分离。函数式封装提供轻量级复用；配置驱动模式提供最大灵活性；命名样式（NamedStyle）是样式复用的基础设施。对于批量和定时报表任务，建议使用配置驱动 + 模板引擎的组合方案。

# 函数式报表生成
from openpyxl import Workbook
from openpyxl.styles import NamedStyle, Font, PatternFill, Alignment, Border, Side
from openpyxl.utils import get_column_letter

def make_header(ws, title, row=1, col=1):
    """生成报表标题"""
    ws.merge_cells(start_row=row, start_column=col, end_row=row, end_column=8)
    cell = ws.cell(row=row, column=col, value=title)
    cell.font = Font(name='微软雅黑', size=16, bold=True, color='1F4E79')
    cell.alignment = Alignment(horizontal='center', vertical='center')
    ws.row_dimensions[row].height = 40
    return ws

def make_table_header(ws, headers, row=2):
    """生成报表表头"""
    header_style = NamedStyle(name='ReportHeader')
    header_style.font = Font(name='微软雅黑', size=11, bold=True, color='FFFFFF')
    header_style.fill = PatternFill(start_color='2F5496', end_color='2F5496', fill_type='solid')
    header_style.alignment = Alignment(horizontal='center', vertical='center')
    ws.add_named_style(header_style)

    for col, header in enumerate(headers, 1):
        cell = ws.cell(row=row, column=col, value=header)
        cell.style = 'ReportHeader'
    ws.row_dimensions[row].height = 30
    return ws

# 配置驱动报表生成
import json

report_config = {
    "title": "2025年Q2销售区域报表",
    "headers": ["区域", "负责人", "目标额", "完成额", "完成率", "排名", "状态"],
    "column_widths": [12, 10, 14, 14, 10, 8, 10],
    "column_formats": {
        "C": "¥#,##0",
        "D": "¥#,##0",
        "E": "0.0%"
    },
    "style_ref": {
        "title": "Report_Title",
        "header": "Report_Header",
        "data": "Report_Data",
        "total": "Report_Total"
    },
    "conditional_rules": [
        {"range": "E2:E100", "type": "data_bar", "color": "5B9BD5"},
        {"range": "F2:F100", "type": "icon_set", "icon_style": "3Arrows"}
    ]
}

def render_report(ws, config, data):
    """根据配置渲染报表"""
    # 标题
    ws.merge_cells(start_row=1, start_column=1, end_row=1, end_column=len(config['headers']))
    ws['A1'] = config['title']
    ws['A1'].style = config['style_ref']['title']

    # 表头
    for col, header in enumerate(config['headers'], 1):
        cell = ws.cell(row=2, column=col, value=header)
        cell.style = config['style_ref']['header']
        ws.column_dimensions[get_column_letter(col)].width = config['column_widths'][col - 1]

    # 数据
    for row_idx, row_data in enumerate(data, 3):
        for col_idx, value in enumerate(row_data, 1):
            cell = ws.cell(row=row_idx, column=col_idx, value=value)
            cell.style = config['style_ref']['data']
            # 数字格式
            col_letter = get_column_letter(col_idx)
            if col_letter in config.get('column_formats', {}):
                cell.number_format = config['column_formats'][col_letter]

    # 条件格式
    for rule in config.get('conditional_rules', []):
        if rule['type'] == 'data_bar':
            ws.conditional_formatting.add(rule['range'], DataBarRule(
                start_type='min', end_type='max', color=rule['color']
            ))

# 批量报表生成
import os

def batch_generate_reports(configs, data_sources, output_dir):
    """批量生成报表"""
    os.makedirs(output_dir, exist_ok=True)
    for cfg, data in zip(configs, data_sources):
        wb = Workbook()
        ws = wb.active
        render_report(ws, cfg, data)
        filename = f"{cfg['title'].replace(' ', '_')}.xlsx"
        filepath = os.path.join(output_dir, filename)
        wb.save(filepath)
        print(f'生成报表: {filepath}')

# 生成月度报表序列
monthly_configs = []
for month in range(1, 13):
    config = report_config.copy()
    config['title'] = f'2025年{month}月销售报表'
    monthly_configs.append(config)

# 假设有 12 个月的数据
# batch_generate_reports(monthly_configs, monthly_data, './reports/2025')

九、实战案例

理论最终要落地到实战。本章通过三个完整的案例展示如何将前面学到的样式、条件格式和模板技术组合应用于真实的业务场景。每个案例都从需求分析出发，经过代码实现，最终生成可直接交付的 Excel 报表文件。

第一个案例是财务报表专业美化。财务人员对报表的格式要求极为严格：千分位分隔符、两位小数、货币符号、负数红色显示、双线下划线等。我们将创建一个完整的利润表模板，包含规范的会计格式、层次分明的缩进结构（营业收入→营业成本→毛利→费用→净利润）和恰当的分隔线。

第二个案例是销售看板制作。使用色阶显示各地区销售额热度，数据条对比各产品的完成进度，图标集标识同比增长方向。看板需要在一张工作表内整合汇总表和明细表，通过合并单元格和样式突出关键信息。第三个案例是绩效考核表，综合运用条件格式标记达标/超标/未达标项，自动计算综合评分和评级。

核心要点：实战的关键在于"组合运用"——一个报表中往往同时需要命名样式（统一风格）、条件格式（数据可视化）、数字格式（专业显示）和对齐排布（结构清晰）。建议从简单的报表开始，逐步添加样式层，每次只验证一个目标效果，避免样式冲突难以排查。

# 案例一：财务报表专业美化
def create_financial_report(wb, data):
    """生成专业利润表"""
    ws = wb.active
    ws.title = '利润表'

    # 样式定义
    title_font = Font(name='微软雅黑', size=18, bold=True, color='1F4E79')
    header_fill = PatternFill(start_color='1F4E79', end_color='1F4E79', fill_type='solid')
    header_font = Font(name='微软雅黑', size=11, bold=True, color='FFFFFF')
    item_font = Font(name='微软雅黑', size=10)
    total_font = Font(name='微软雅黑', size=10, bold=True)
    double_bottom = Border(bottom=Side(style='double', color='000000'))

    # 标题
    ws.merge_cells('A1:C1')
    ws['A1'] = '利润表（2025年1-6月）'
    ws['A1'].font = title_font
    ws['A1'].alignment = Alignment(horizontal='center', vertical='center')
    ws.row_dimensions[1].height = 45

    # 表头
    headers = ['项目', '本期金额', '上期金额']
    for col, h in enumerate(headers, 1):
        cell = ws.cell(row=2, column=col, value=h)
        cell.font = header_font
        cell.fill = header_fill
        cell.alignment = Alignment(horizontal='center', vertical='center')

    # 数据行 - 带层次缩进
    for idx, (item_name, curr_val, prev_val) in enumerate(data, 3):
        ws.cell(row=idx, column=1, value=item_name).font = item_font
        ws.cell(row=idx, column=1).alignment = Alignment(indent=1)
        ws.cell(row=idx, column=2, value=curr_val).number_format = '¥#,##0.00'
        ws.cell(row=idx, column=3, value=prev_val).number_format = '¥#,##0.00'
        if item_name == '净利润':
            ws.cell(row=idx, column=1).font = total_font
            ws.cell(row=idx, column=2).border = double_bottom
            ws.cell(row=idx, column=3).border = double_bottom

    # 列宽
    ws.column_dimensions['A'].width = 30
    ws.column_dimensions['B'].width = 18
    ws.column_dimensions['C'].width = 18
    return ws

# 案例二：销售看板
def create_sales_dashboard(wb, sales_data):
    """生成销售业绩看板"""
    ws = wb.active
    ws.title = '销售看板'

    # 标题
    ws.merge_cells('A1:H1')
    ws['A1'] = '2025年Q2销售业绩看板'
    ws['A1'].font = Font(name='微软雅黑', size=20, bold=True, color='1F4E79')
    ws['A1'].alignment = Alignment(horizontal='center', vertical='center')
    ws.row_dimensions[1].height = 50

    # 表头
    headers = ['销售代表', '区域', '目标(万)', '完成(万)', '完成率', '环比增长', '客户数', '评级']
    for col, h in enumerate(headers, 1):
        cell = ws.cell(row=3, column=col, value=h)
        cell.font = Font(name='微软雅黑', size=11, bold=True, color='FFFFFF')
        cell.fill = PatternFill(start_color='2F5496', end_color='2F5496', fill_type='solid')
        cell.alignment = Alignment(horizontal='center', vertical='center')

    # 数据
    for i, row_data in enumerate(sales_data, 4):
        for j, val in enumerate(row_data, 1):
            ws.cell(row=i, column=j, value=val)
        # 完成率列使用数据条
        ws.cell(row=i, column=5).number_format = '0.0%'
        # 环比率格式化
        ws.cell(row=i, column=6).number_format = '+0.0%;-0.0%'

    # 条件格式：完成率 -> 色阶
    ws.conditional_formatting.add('E4:E100', ColorScaleRule(
        start_type='min', start_color='F8696B',
        mid_type='percentile', mid_value=50, mid_color='FFEB84',
        end_type='max', end_color='63BE7B'
    ))

    # 条件格式：环比增长 -> 三色箭头
    ws.conditional_formatting.add('F4:F100', IconSetRule(
        icon_style='3Arrows', type='percent', values=[0, 33, 67]
    ))

    # 评级自动标注（假设评级列有字母）
    ws.conditional_formatting.add('H4:H100', FormulaRule(
        formula=['$H4="A"'],
        fill=PatternFill(start_color='C6EFCE', end_color='C6EFCE', fill_type='solid'),
        font=Font(bold=True, color='006100')
    ))

    return ws

# 案例三：绩效考核表
def create_performance_report(wb, emp_data):
    """生成员工考核表"""
    ws = wb.active
    ws.title = '绩效考核'

    # 标题和表头
    ws.merge_cells('A1:G1')
    ws['A1'] = '2025年Q2员工绩效考核表'
    ws['A1'].font = Font(name='微软雅黑', size=16, bold=True, color='1F4E79')
    ws['A1'].alignment = Alignment(horizontal='center')

    headers = ['姓名', 'KPI得分(40%)', '360评分(30%)', '出勤(10%)', '项目成果(20%)', '综合得分', '评级']
    for col, h in enumerate(headers, 1):
        cell = ws.cell(row=2, column=col, value=h)
        cell.font = Font(bold=True, color='FFFFFF', size=10)
        cell.fill = PatternFill(start_color='2F5496', end_color='2F5496', fill_type='solid')
        cell.alignment = Alignment(horizontal='center', vertical='center', wrap_text=True)

    # 数据写入 + 自动计算综合得分
    for i, (name, kpi, score360, attend, project) in enumerate(emp_data, 3):
        ws.cell(row=i, column=1, value=name)
        ws.cell(row=i, column=2, value=kpi)
        ws.cell(row=i, column=3, value=score360)
        ws.cell(row=i, column=4, value=attend)
        ws.cell(row=i, column=5, value=project)
        # 综合得分 = 各加权项之和
        total = kpi * 0.4 + score360 * 0.3 + attend * 0.1 + project * 0.2
        ws.cell(row=i, column=6, value=round(total, 2))
        # 评级
        if total >= 90: grade = 'A'
        elif total >= 80: grade = 'B'
        elif total >= 70: grade = 'C'
        else: grade = 'D'
        ws.cell(row=i, column=7, value=grade)

    # 条件格式：综合得分 >= 90 标记为绿色
    ws.conditional_formatting.add('F3:F100', CellIsRule(
        operator='greaterThanOrEqual', formula=['90'],
        fill=PatternFill(start_color='C6EFCE', end_color='C6EFCE', fill_type='solid'),
        font=Font(bold=True, color='006100')
    ))
    # 综合得分 < 60 标记为红色
    ws.conditional_formatting.add('F3:F100', CellIsRule(
        operator='lessThan', formula=['60'],
        fill=PatternFill(start_color='FCE4EC', end_color='FCE4EC', fill_type='solid'),
        font=Font(bold=True, color='B71C1C')
    ))
    return ws

十、常见问题与调试

使用 openpyxl 处理样式时，开发者经常会遇到一些陷阱和性能问题。最常见的样式冲突发生在同时使用 NamedStyle 和直接属性设置时。比如通过 cell.style = 'MyStyle' 设置了命名样式，然后又通过 cell.font = Font(bold=True) 设置字体，后者会覆盖前者中的字体设置，但边框和填充等不受影响——这种"部分覆盖"行为常常让人困惑。

性能优化是批量报表生成中的核心问题。openpyxl 在写入大量单元格（超过数万）时会变得缓慢，尤其是每个单元格都应用了独立的样式时。优化策略包括：尽可能使用 NamedStyle 而非逐单元格设置属性；尽量逐列（列优先）而非逐行写入数据；对于超大数据集考虑使用 write_only 模式；在单独的工作表中做样式定义再复制。

兼容性方面，不同 Excel 版本和不同操作系统之间存在若干差异。自定义颜色在不同版本的 Excel 中可能略有偏差；某些字体（特别是中文字体）在 macOS 和 Windows 上表现不同；openpyxl 不支持 Excel Online 的所有样式特性。建议在报表生成后进行跨平台验证，并使用常用的字体（如微软雅黑、Arial、Calibri）以确保兼容性。

核心要点：样式冲突时，直接属性设置会覆盖 NamedStyle 的同名属性。性能优化三原则：复用 NamedStyle、列优先写入、避免过多独立样式。兼容性注意：中文字体跨平台问题、颜色微小偏差、Excel Online 功能限制。条件格式过多（超过 1000 条）可能导致 Excel 打开缓慢。

# 样式冲突排查示例
from openpyxl import Workbook
from openpyxl.styles import NamedStyle, Font, PatternFill

wb = Workbook()
ws = wb.active

# 创建命名样式
style = NamedStyle(name='TestStyle')
style.font = Font(name='Arial', size=12, bold=True, color='2F5496')
style.fill = PatternFill(start_color='D6E4F0', end_color='D6E4F0', fill_type='solid')
wb.add_named_style(style)

# 应用命名样式
ws['A1'] = '测试冲突'
ws['A1'].style = 'TestStyle'   # font + fill 均有效

# 部分覆盖——仅覆盖字体，填充不受影响
ws['A1'].font = Font(bold=True, color='FF0000')  # 只改颜色和加粗，字号和字体名称不变
# 👆 注意！这里创建了新 Font 对象替换了原有的，而不是修改原有 Font

# 正确的做法：获取现有样式再修改
# cell = ws['A1']
# cell.font = Font(name=cell.font.name, size=cell.font.size, bold=True, color='FF0000')

# 性能优化：批量写入 vs 逐单元格写入

# ❌ 低效方式：逐单元格写入 + 独立样式
for row in range(1, 1001):
    for col in range(1, 21):
        cell = ws.cell(row=row, column=col, value=row * col)
        cell.font = Font(name='Arial', size=10)
        cell.border = Border(
            left=Side(style='thin'),
            right=Side(style='thin'),
            top=Side(style='thin'),
            bottom=Side(style='thin')
        )

# ✅ 高效方式：NamedStyle 复用 + 列优先写入
from openpyxl.styles import NamedStyle, Font, Border, Side

data_style = NamedStyle(name='FastData')
data_style.font = Font(name='Arial', size=10)
data_style.border = Border(
    left=Side(style='thin'), right=Side(style='thin'),
    top=Side(style='thin'), bottom=Side(style='thin')
)
wb.add_named_style(data_style)

# 先写数据（列优先，减少行切换）
for col in range(1, 21):
    for row in range(1, 1001):
        ws.cell(row=row, column=col, value=row * col)

# 再统一应用样式
for row in range(1, 1001):
    for col in range(1, 21):
        ws.cell(row=row, column=col).style = 'FastData'

# 调试技巧

# 1. 检查单元格当前样式
def debug_cell_style(cell):
    """打印单元格的完整样式信息"""
    info = {
        '值': cell.value,
        '字体名称': cell.font.name,
        '字体大小': cell.font.size,
        '字体颜色': cell.font.color.rgb if cell.font.color else None,
        '加粗': cell.font.bold,
        '填充颜色': cell.fill.start_color.rgb if cell.fill else None,
        '边框': f"L:{cell.border.left.style} R:{cell.border.right.style}",
        '对齐': f"H:{cell.alignment.horizontal} V:{cell.alignment.vertical}",
        '数字格式': cell.number_format,
    }
    for k, v in info.items():
        print(f'  {k}: {v}')

# 2. 列出工作簿所有命名样式
def list_named_styles(wb):
    for style in wb.named_styles:
        print(f'[{style.name}] 字体={style.font.name}, 字号={style.font.size}')

# 3. 清除所有条件格式
def clear_all_conditional_formatting(ws):
    ws.conditional_formatting = type(ws.conditional_formatting)()

# 4. 检查 style 属性（是否为字符串引用）
cell = ws['A1']
print(f'Style 引用: {cell.style}')          # 样式名称字符串
print(f'Has Style: {cell.has_style}')       # 布尔值