Python办公自动化：使用 openpyxl 进行高级 Excel 报表自动化生成(实战项目)

项目背景

假设你是某公司财务部门的一员，需要定期生成公司各部门的月度财务报表。为了提高工作效率，你决定使用 Python 脚本自动生成这些报表。每个部门的数据都存储在不同的 Excel 文件中，我们需要将它们汇总到一个总报表中，并生成图表进行可视化分析。

项目步骤

1. 读取各部门的 Excel 文件
2. 合并数据到一个总报表中
3. 生成各部门的财务数据图表
4. 应用条件格式突出显示特定数据
5. 保存最终报表

第一步：读取各部门的 Excel 文件

import openpyxl
from openpyxl.utils import get_column_letter

# 定义部门名称及其对应的文件路径
departments = {
    '销售部': 'sales.xlsx',
    '市场部': 'marketing.xlsx',
    '研发部': 'rnd.xlsx',
    '人力资源部': 'hr.xlsx'
}

# 创建一个字典来存储每个部门的工作表
department_data = {}

# 逐个读取部门的 Excel 文件
for dept, file_path in departments.items():
    # 打开工作簿并获取第一个工作表
    workbook = openpyxl.load_workbook(file_path)
    sheet = workbook.active
    department_data[dept] = sheet

第二步：合并数据到一个总报表中

# 创建一个新的工作簿用于存储总报表
summary_workbook = openpyxl.Workbook()
summary_sheet = summary_workbook.active
summary_sheet.title = '总报表'

# 写入表头
headers = ['部门', '月份', '收入', '支出', '利润']
summary_sheet.append(headers)

# 合并各部门数据
for dept, sheet in department_data.items():
    for row in sheet.iter_rows(min_row=2, values_only=True):
        # 添加部门名称
        summary_sheet.append([dept] + list(row))

# 调整列宽
for col in range(1, len(headers) + 1):
    summary_sheet.column_dimensions[get_column_letter(col)].width = 15

第三步：生成各部门的财务数据图表

from openpyxl.chart import BarChart, Reference

# 为每个部门创建图表
for dept in department_data:
    dept_data = []
    
    # 提取部门的数据
    for row in summary_sheet.iter_rows(values_only=True):
        if row[0] == dept:
            dept_data.append(row)
    
    # 在新工作表中添加数据
    dept_sheet = summary_workbook.create_sheet(dept + '财务图表')
    for row in dept_data:
        dept_sheet.append(row)
    
    # 创建条形图
    chart = BarChart()
    data = Reference(dept_sheet, min_col=3, min_row=1, max_col=5, max_row=len(dept_data) + 1)
    chart.add_data(data, titles_from_data=True)
    dept_sheet.add_chart(chart, "H10")

第四步：应用条件格式突出显示特定数据

from openpyxl.styles import PatternFill
from openpyxl.formatting.rule import CellIsRule

# 定义条件格式 - 高亮显示利润为负的行
red_fill = PatternFill(start_color="FFC7CE", end_color="FFC7CE", fill_type="solid")
rule = CellIsRule(operator='lessThan', formula=['0'], fill=red_fill)

# 将条件格式应用于利润列
summary_sheet.conditional_formatting.add('E2:E100', rule)

第五步：保存最终报表

# 保存总报表
summary_workbook.save('公司月度财务总报表.xlsx')

通过这个实战项目，我们综合运用了 openpyxl 的多种功能，包括 Excel 文件的读取与合并、图表的生成、条件格式的应用等。这种自动化解决方案大大提高了财务报表的生成效率，也减少了人工操作可能带来的错误。