Openpyxl 实战：Python 帮你做"排版美工"——自动化生成格式化 Excel 报表

Pandas 擅长数据处理，但在格式美化方面力不从心——它无法设置字体颜色、合并单元格、调整列宽、添加边框等。

这时候就需要 Openpyxl 登场了。

如果说 Pandas 是财务人的"大脑"（负责算数），那 Openpyxl 就是"双手"（负责把结果排版成一份漂亮的报表）。

---

一、 Openpyxl 是什么？

Openpyxl 是 Python 中专门用于读写 Excel 2010+ xlsx/xlsm 文件 的库。它的核心能力：

• • ✅ 读取/创建/修改 .xlsx 文件
• • ✅ 设置单元格格式（字体、颜色、对齐方式）
• • ✅ 合并单元格、冻结窗格
• • ✅ 插入图表
• • ✅ 设置条件格式
• • ✅ 调整行高列宽

注意： Openpyxl 只支持 .xlsx 格式，不支持旧版的 .xls。

---

二、 安装与基础操作

pip install openpyxl

创建一个新的 Excel 文件

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

# 创建工作簿
wb = Workbook()
ws = wb.active
ws.title = "费用报表"

# 写入标题
ws["A1"] = "2024年3月 部门费用汇总报表"
ws.merge_cells("A1:E1")  # 合并标题单元格

# 设置标题样式
ws["A1"].font = Font(name="微软雅黑", size=16, bold=True, color="FFFFFF")
ws["A1"].fill = PatternFill(start_color="2F5496", end_color="2F5496", fill_type="solid")
ws["A1"].alignment = Alignment(horizontal="center", vertical="center")
ws.row_dimensions[1].height = 35# 标题行高度

# 写入表头
headers = ["部门", "费用类型", "金额(元)", "占比", "备注"]
for col, header inenumerate(headers, 1):
    cell = ws.cell(row=3, column=col, value=header)
    cell.font = Font(bold=True, color="FFFFFF")
    cell.fill = PatternFill(start_color="4472C4", end_color="4472C4", fill_type="solid")
    cell.alignment = Alignment(horizontal="center", vertical="center")

# 写入数据
data = [
    ["销售部", "差旅费", 7700, "29.5%", "含招待费"],
    ["研发部", "设备采购", 20900, "80.1%", "服务器升级"],
    ["市场部", "广告费", 11500, "44.1%", "Q1推广"],
    ["财务部", "培训费", 1500, "5.8%", "外部培训"],
    ["行政部", "办公耗材", 1280, "4.9%", "日常采购"],
]

for row_idx, row_data inenumerate(data, 4):
for col_idx, value inenumerate(row_data, 1):
        cell = ws.cell(row=row_idx, column=col_idx, value=value)
        cell.alignment = Alignment(horizontal="center", vertical="center")
# 金额列右对齐并添加千分位格式
if col_idx == 3:
            cell.number_format = '#,##0.00'
            cell.alignment = Alignment(horizontal="right", vertical="center")

# 添加合计行
total_row = len(data) + 4
ws.cell(row=total_row, column=1, value="合计").font = Font(bold=True)
ws.cell(row=total_row, column=3, value=sum([d[2] for d in data]))
ws.cell(row=total_row, column=3).number_format = '#,##0.00'
ws.cell(row=total_row, column=3).font = Font(bold=True)

# 设置边框
thin_border = Border(
    left=Side(style='thin'),
    right=Side(style='thin'),
    top=Side(style='thin'),
    bottom=Side(style='thin')
)

for row in ws.iter_rows(min_row=3, max_row=total_row, max_col=5):
for cell in row:
        cell.border = thin_border

# 调整列宽
ws.column_dimensions['A'].width = 12
ws.column_dimensions['B'].width = 14
ws.column_dimensions['C'].width = 15
ws.column_dimensions['D'].width = 10
ws.column_dimensions['E'].width = 18

# 冻结首行（滚动时表头不动）
ws.freeze_panes = "A4"

# 保存文件
output_path = "格式化_费用报表.xlsx"
wb.save(output_path)
print(f"✅ 报表已生成: {output_path}")

运行后打开 格式化_费用报表.xlsx，你会看到一份排版精美的专业报表。

三、 条件格式：自动标记异常数据

条件格式是财务报表中非常实用的功能——比如自动标红超预算的费用：

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

wb = Workbook()
ws = wb.active

# 写入示例数据
data = [
    ["项目", "预算", "实际", "差异"],
    ["差旅费", 10000, 13500, None],
    ["办公费", 5000, 4200, None],
    ["招待费", 8000, 9200, None],
    ["培训费", 3000, 2800, None],
    ["设备费", 20000, 25000, None],
]

for row_idx, row inenumerate(data, 1):
for col_idx, val inenumerate(row, 1):
        ws.cell(row=row_idx, column=col_idx, value=val)

# 计算差异列
for row inrange(2, len(data) + 1):
    budget = ws.cell(row=row, column=2).value
    actual = ws.cell(row=row, column=3).value
    diff_cell = ws.cell(row=row, column=4)
if budget and actual:
        diff_cell.value = actual - budget
        diff_cell.number_format = '+#,##0;-#,##0;0'

# ===== 条件格式规则 =====

# 规则1：超支金额 > 0 → 红色填充
red_fill = PatternFill(start_color="FFCCCC", end_color="FFCCCC", fill_type="solid")
red_font = Font(color="CC0000", bold=True)
ws.conditional_formatting.add(
"D2:D6",
    CellIsRule(operator="greaterThan", formula=["0"], fill=red_fill, font=red_font)
)

# 规则2：节约金额 < 0 → 绿色填充
green_fill = PatternFill(start_color="C6EFCE", end_color="C6EFCE", fill_type="solid")
green_font = Font(color="006100")
ws.conditional_formatting.add(
"D2:D6",
    CellIsRule(operator="lessThan", formula=["0"], fill=green_fill, font=green_font)
)

# 规则3：实际/预算 > 120% → 黄色警告
yellow_fill = PatternFill(start_color="FFEB9C", end_color="FFEB9C", fill_type="solid")
ws.conditional_formatting.add(
"C2:C6",
    FormulaRule(formula=["C2/B2>1.2"], fill=yellow_fill)
)

wb.save("条件格式_预算对比.xlsx")
print("✅ 条件格式报表已生成!")

四、 完整实战：从 Pandas 数据到精美报表的工作流

这是最实用的场景——先用 Pandas 处理数据，再用 Openpyxl 排版输出：

import pandas as pd
from openpyxl import Workbook
from openpyxl.styles import Font, Alignment, PatternFill, Border, Side
from openpyxl.utils.dataframe import dataframe_to_rows
from datetime import datetime

defcreate_formatted_report(df, output_file, title="财务分析报告"):
"""
    将 Pandas DataFrame 转换为格式化的 Excel 报表
    :param df: Pandas DataFrame
    :param output_file: 输出文件名
    :param title: 报表标题
    """

    wb = Workbook()
    ws = wb.active
    ws.title = "报表"

# ========== 1. 标题区域 ==========
    ws["A1"] = title
    ws.merge_cells(start_row=1, start_column=1,
                    end_row=1, end_column=len(df.columns))
    ws["A1"].font = Font(name="微软雅黑", size=18, bold=True, color="FFFFFF")
    ws["A1"].fill = PatternFill("1F4E79", fill_type="solid")
    ws["A1"].alignment = Alignment(horizontal="center", vertical="center")
    ws.row_dimensions[1].height = 45

# ========== 2. 生成时间标注 ==========
    ws[f"{chr(65 + len(df.columns))}2"] = f"生成时间: {datetime.now().strftime('%Y-%m-%d %H:%M')}"
    ws[f"{chr(65 + len(df.columns))}2"].font = Font(size=9, italic=True, color="888888")
    ws[f"{chr(65 + len(df.columns))}2"].alignment = Alignment(horizontal="right")

# ========== 3. 表头 ==========
    header_fill = PatternFill("2F5496", fill_type="solid")
    header_font = Font(bold=True, color="FFFFFF", size=11)
    header_alignment = Alignment(horizontal="center", vertical="center")

for col_idx, column_name inenumerate(df.columns, 1):
        cell = ws.cell(row=4, column=col_idx, value=column_name)
        cell.font = header_font
        cell.fill = header_fill
        cell.alignment = header_alignment
        cell.border = Border(
            left=Side(style="thin"),
            right=Side(style="thin"),
            top=Side(style="medium"),
            bottom=Side(style="medium")
        )

# ========== 4. 数据行 ==========
    alt_fill = PatternFill("D6DCE4", fill_type="solid")  # 斑马纹交替色
    data_border = Border(
        left=Side(style="thin"),
        right=Side(style="thin"),
        top=Side(style="thin"),
        bottom=Side(style="thin")
    )

for row_idx, row_data inenumerate(df.values, 5):
for col_idx, value inenumerate(row_data, 1):
            cell = ws.cell(row=row_idx, column=col_idx, value=value)
            cell.alignment = Alignment(vertical="center")
            cell.border = data_border

# 数值类列：右对齐 + 千分位
ifisinstance(value, (int, float)):
                cell.alignment = Alignment(horizontal="right", vertical="center")
                cell.number_format = '#,##0.00'ifisinstance(value, float) else'#,##0'

# 斑马纹效果
if row_idx % 2 == 0:
                cell.fill = alt_fill

# ========== 5. 合计行 ==========
    total_row = len(df) + 5
    ws.cell(row=total_row, column=1, value="合计").font = Font(bold=True)
    ws.cell(row=total_row, column=1).fill = PatternFill("FFF2CC", fill_type="solid")

for col_idx inrange(2, len(df.columns) + 1):
        col_name = df.columns[col_idx - 1]
# 对数值列求和
if pd.api.types.is_numeric_dtype(df[col_name]):
            total_val = df[col_name].sum()
            cell = ws.cell(row=total_row, column=col_idx, value=total_val)
            cell.font = Font(bold=True)
            cell.fill = PatternFill("FFF2CC", fill_type="solid")
ifisinstance(total_val, float):
                cell.number_format = '#,##0.00'
else:
                cell.number_format = '#,##0'
else:
            ws.cell(row=total_row, column=col_idx, value="-").fill = PatternFill("FFF2CC", fill_type="solid")

# ========== 6. 列宽自适应 ==========
for col_idx, column_name inenumerate(df.columns, 1):
        max_length = len(str(column_name))
for value in df[column_name]:
try:
                max_length = max(max_length, len(str(value)))
except:
pass
        adjusted_width = min(max_length * 1.5 + 4, 40)  # 最大宽度限制
        ws.column_dimensions[chr(64 + col_idx)].width = adjusted_width

# ========== 7. 打印设置 ==========
    ws.print_title_rows = "1:4"# 打印时重复显示标题和表头
    ws.page_setup.orientation = "landscape"# 横向打印
    ws.page_setup.fitToPage = True
    ws.page_setup.fitToWidth = 1
    ws.page_setup.fitToHeight = 0# 按宽度适配，页数不限

    wb.save(output_file)
print(f"✅ 格式化报表已保存: {output_file}")
return output_file


# ===== 使用示例 =====
sample_df = pd.DataFrame({
"部门": ["销售部", "研发部", "市场部", "财务部", "行政部"],
"预算(万)": [50, 80, 30, 20, 15],
"实际(万)": [48.5, 92.3, 28.7, 18.2, 13.8],
"执行率%": [97.0, 115.4, 95.7, 91.0, 92.0],
"负责人": ["张三", "李四", "王五", "赵六", "钱七"]
})

create_formatted_report(sample_df, "部门预算执行情况.xlsx",
                         title="2024年Q1 各部门预算执行情况")

五、 高级功能：插入图表

from openpyxl.chart import BarChart, Reference, LineChart
from openpyxl.chart.label import DataLabelList

# 在上面的工作簿基础上继续...

# 创建柱状图：各部门预算 vs 实际
chart = BarChart()
chart.type = "col"
chart.grouping = "clustered"
chart.title = "各部门预算执行对比"
chart.y_axis.title = "金额（万元）"
chart.x_axis.title = "部门"
chart.style = 10

# 数据范围
data_ref = Reference(ws, min_col=2, min_row=4, max_col=3, max_row=9)
cats_ref = Reference(ws, min_col=1, min_row=5, max_row=9)

chart.add_data(data_ref, titles_from_data=True)
chart.set_categories(cats_ref)
chart.shape = 4
chart.width = 15
chart.height = 10

# 放置图表
ws.add_chart(chart, "G4")

# 显示数据标签
chart.dataLabels = DataLabelList()
chart.dataLabels.showVal = True

wb.save("带图表的报表.xlsx")
print("✅ 含图表的报表已生成!")

六、 避坑指南

七、 总结

最佳实践：Pandas 处理数据 → Openpyxl 排版输出。 两者配合使用，覆盖了财务报表生成的全流程。

下一篇文章，我们将学习 数据可视化——如何用 Matplotlib 和 Seaborn 把枯燥的数据变成直观的图表。