4.5 Python 中的 Excel 文件操作（openpyxl 与 pandas）

达文汐

1236人浏览 · 2025-09-23 08:00:00

达文汐 · 2025-09-23 08:00:00 发布

引言：企业数据处理的基石

在商业和数据分析领域，Microsoft Excel无疑是使用最广泛的数据处理工具。无论是财务报告、销售数据、客户信息还是项目计划，Excel文件（.xlsx, .xls）无处不在。作为Python开发者，能够程序化地读取、处理和写入Excel文件是一项极其重要的技能。

Python生态系统提供了多个强大的库来处理Excel文件，其中最常用的是openpyxl和pandas。openpyxl专门用于读写Excel 2010 xlsx/xlsm/xltx/xltm文件，提供精细的单元格级别控制；而pandas作为数据分析库，提供了更高级的抽象，能够轻松处理整个工作表和数据表。

本章将深入探讨这两个库的使用，从基础操作到高级功能，并通过实战项目展示如何在实际工作中高效处理Excel数据。

第一部分：openpyxl库详解

openpyxl是一个专门用于读写Excel 2010+文件的Python库，提供了对Excel文件的细粒度控制。

1.1 安装与基本概念

pip install openpyxl

Excel文件的基本结构：

工作簿(Workbook)：整个Excel文件
工作表(Worksheet)：工作簿中的单个表格
单元格(Cell)：表格中的单个格子，由行和列定位

1.2 创建工作簿和工作表

from openpyxl import Workbook
from openpyxl.utils import get_column_letter

# 创建新工作簿
wb = Workbook()

# 获取默认激活的工作表
ws = wb.active
ws.title = "员工信息"  # 设置工作表标题

# 创建新的工作表
ws1 = wb.create_sheet("部门信息")  # 在末尾插入
ws2 = wb.create_sheet("薪资数据", 0)  # 在第一个位置插入

# 查看所有工作表名称
print(wb.sheetnames)  # ['薪资数据', '员工信息', '部门信息']

# 保存工作簿
wb.save("公司数据.xlsx")

1.3 单元格操作

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

# 创建工作簿
wb = Workbook()
ws = wb.active

# 方法1: 通过单元格地址直接赋值
ws['A1'] = "员工ID"
ws['B1'] = "姓名"
ws['C1'] = "部门"

# 方法2: 使用cell()方法指定行列
ws.cell(row=2, column=1, value=1001)
ws.cell(row=2, column=2, value="张三")
ws.cell(row=2, column=3, value="技术部")

# 方法3: 批量赋值
data = [
    [1002, "李四", "市场部"],
    [1003, "王五", "财务部"],
    [1004, "赵六", "人事部"]
]

for row_idx, row_data in enumerate(data, start=3):  # 从第3行开始
    for col_idx, cell_data in enumerate(row_data, start=1):  # 从第1列开始
        ws.cell(row=row_idx, column=col_idx, value=cell_data)

# 读取单元格值
cell_value = ws['A2'].value  # 1001
cell_value = ws.cell(row=2, column=2).value  # "张三"

# 遍历单元格
for row in ws.iter_rows(min_row=1, max_row=4, min_col=1, max_col=3):
    for cell in row:
        print(cell.value, end="\t")
    print()

# 单元格样式设置
header_font = Font(bold=True, color="FFFFFF", size=12)
header_fill = PatternFill(start_color="366092", end_color="366092", fill_type="solid")
center_aligned = Alignment(horizontal="center")
thin_border = Border(
    left=Side(style="thin"),
    right=Side(style="thin"),
    top=Side(style="thin"),
    bottom=Side(style="thin")
)

# 应用样式到表头
for cell in ws[1]:  # 第一行
    cell.font = header_font
    cell.fill = header_fill
    cell.alignment = center_aligned
    cell.border = thin_border

# 调整列宽
ws.column_dimensions['A'].width = 10
ws.column_dimensions['B'].width = 15
ws.column_dimensions['C'].width = 15

wb.save("公司数据.xlsx")

1.4 公式与函数

from openpyxl import Workbook
from openpyxl.utils import get_column_letter

wb = Workbook()
ws = wb.active

# 添加数据
data = [
    ["产品", "一月", "二月", "三月", "总计"],
    ["产品A", 100, 150, 200],
    ["产品B", 80, 120, 160],
    ["产品C", 200, 250, 300]
]

for r_idx, row in enumerate(data, 1):
    for c_idx, value in enumerate(row, 1):
        ws.cell(row=r_idx, column=c_idx, value=value)

# 添加公式计算总计
for r_idx in range(2, 5):  # 第2到第4行
    # 公式: =SUM(B2:D2)
    formula = f"=SUM(B{r_idx}:D{r_idx})"
    ws.cell(row=r_idx, column=5, value=formula)

# 添加平均值行
ws.cell(row=5, column=1, value="平均值")
for c_idx in range(2, 6):  # B到E列
    col_letter = get_column_letter(c_idx)
    formula = f"=AVERAGE({col_letter}2:{col_letter}4)"
    ws.cell(row=5, column=c_idx, value=formula)

wb.save("销售数据.xlsx")

1.5 高级功能：图表创建

from openpyxl import Workbook
from openpyxl.chart import BarChart, Reference

wb = Workbook()
ws = wb.active

# 添加数据
data = [
    ["产品", "一月", "二月", "三月"],
    ["产品A", 100, 150, 200],
    ["产品B", 80, 120, 160],
    ["产品C", 200, 250, 300]
]

for r_idx, row in enumerate(data, 1):
    for c_idx, value in enumerate(row, 1):
        ws.cell(row=r_idx, column=c_idx, value=value)

# 创建柱状图
chart = BarChart()
chart.type = "col"
chart.style = 10
chart.title = "产品销售情况"
chart.y_axis.title = "销售额"
chart.x_axis.title = "产品"

# 设置数据范围
data_ref = Reference(ws, min_col=2, min_row=1, max_col=4, max_row=4)
categories_ref = Reference(ws, min_col=1, min_row=2, max_row=4)

# 添加数据和类别
chart.add_data(data_ref, titles_from_data=True)
chart.set_categories(categories_ref)

# 将图表添加到工作表
ws.add_chart(chart, "A7")

wb.save("销售图表.xlsx")

第二部分：pandas库Excel处理

pandas提供了更高级的Excel文件处理功能，特别适合数据分析和处理。

2.1 读取Excel文件

import pandas as pd

# 读取整个Excel文件
excel_file = pd.ExcelFile("公司数据.xlsx")

# 查看所有工作表
print(excel_file.sheet_names)

# 读取特定工作表
df = pd.read_excel("公司数据.xlsx", sheet_name="员工信息")
print(df.head())

# 读取多个工作表
dfs = pd.read_excel("公司数据.xlsx", sheet_name=["员工信息", "部门信息"])
employees_df = dfs["员工信息"]
departments_df = dfs["部门信息"]

# 读取特定范围
df_range = pd.read_excel("公司数据.xlsx", sheet_name="员工信息", 
                         usecols="A:C", nrows=10)  # 只读取A-C列，前10行

# 处理缺失值
df = pd.read_excel("公司数据.xlsx", sheet_name="员工信息", 
                   na_values=["", "NULL", "N/A"])

# 指定数据类型
df = pd.read_excel("公司数据.xlsx", sheet_name="员工信息",
                   dtype={"员工ID": str, "部门": "category"})  # 指定列的数据类型

2.2 写入Excel文件

import pandas as pd
import numpy as np

# 创建示例数据
np.random.seed(42)
dates = pd.date_range("20230101", periods=100)
data = {
    "日期": dates,
    "销售额": np.random.randint(1000, 5000, 100),
    "成本": np.random.randint(500, 2500, 100),
    "产品线": np.random.choice(["A", "B", "C"], 100),
    "地区": np.random.choice(["东", "南", "西", "北"], 100)
}

df = pd.DataFrame(data)
df["利润"] = df["销售额"] - df["成本"]

# 基本写入
df.to_excel("销售数据.xlsx", index=False)

# 写入多个工作表
with pd.ExcelWriter("多工作表数据.xlsx") as writer:
    df.to_excel(writer, sheet_name="原始数据", index=False)
    
    # 按产品线分组汇总
    product_summary = df.groupby("产品线").agg({
        "销售额": "sum",
        "成本": "sum",
        "利润": "sum"
    }).reset_index()
    product_summary.to_excel(writer, sheet_name="产品汇总", index=False)
    
    # 按地区分组汇总
    region_summary = df.groupby("地区").agg({
        "销售额": "mean",
        "成本": "mean",
        "利润": "mean"
    }).reset_index()
    region_summary.to_excel(writer, sheet_name="地区汇总", index=False)

# 使用ExcelWriter设置格式
with pd.ExcelWriter("格式化数据.xlsx", engine="openpyxl") as writer:
    df.to_excel(writer, sheet_name="数据", index=False)
    
    # 获取工作簿和工作表对象
    workbook = writer.book
    worksheet = writer.sheets["数据"]
    
    # 设置列宽
    for column in worksheet.columns:
        max_length = 0
        column_letter = column[0].column_letter
        for cell in column:
            try:
                if len(str(cell.value)) > max_length:
                    max_length = len(str(cell.value))
            except:
                pass
        adjusted_width = min(max_length + 2, 50)
        worksheet.column_dimensions[column_letter].width = adjusted_width
    
    # 设置表头样式
    from openpyxl.styles import Font, PatternFill
    header_fill = PatternFill(start_color="366092", end_color="366092", fill_type="solid")
    header_font = Font(bold=True, color="FFFFFF")
    
    for cell in worksheet[1]:  # 第一行是表头
        cell.fill = header_fill
        cell.font = header_font

2.3 高级数据处理与Excel集成

import pandas as pd
import numpy as np

# 创建复杂数据报告
def create_sales_report():
    # 生成模拟数据
    np.random.seed(42)
    months = ["一月", "二月", "三月", "四月", "五月", "六月"]
    products = ["产品A", "产品B", "产品C", "产品D"]
    regions = ["东部", "西部", "南部", "北部"]
    
    data = []
    for month in months:
        for product in products:
            for region in regions:
                sales = np.random.randint(1000, 10000)
                cost = sales * np.random.uniform(0.3, 0.7)
                profit = sales - cost
                data.append([month, product, region, sales, cost, profit])
    
    df = pd.DataFrame(data, columns=["月份", "产品", "地区", "销售额", "成本", "利润"])
    
    # 创建数据透视表
    sales_pivot = pd.pivot_table(df, values="销售额", index="产品", 
                                columns="月份", aggfunc="sum", fill_value=0)
    
    profit_pivot = pd.pivot_table(df, values="利润", index="产品", 
                                 columns="月份", aggfunc="mean", fill_value=0)
    
    region_pivot = pd.pivot_table(df, values="销售额", index="地区", 
                                 columns="月份", aggfunc="sum", fill_value=0)
    
    # 创建Excel报告
    with pd.ExcelWriter("销售分析报告.xlsx", engine="openpyxl") as writer:
        # 原始数据
        df.to_excel(writer, sheet_name="原始数据", index=False)
        
        # 数据透视表
        sales_pivot.to_excel(writer, sheet_name="销售额透视")
        profit_pivot.to_excel(writer, sheet_name="利润透视")
        region_pivot.to_excel(writer, sheet_name="地区透视")
        
        # 获取工作簿对象
        workbook = writer.book
        
        # 添加图表
        from openpyxl.chart import BarChart, Reference, LineChart
        from openpyxl.chart.series import Series
        
        # 在销售额透视表上添加图表
        ws_sales = writer.sheets["销售额透视"]
        chart1 = BarChart()
        chart1.type = "col"
        chart1.style = 10
        chart1.title = "各产品销售额趋势"
        chart1.y_axis.title = "销售额"
        chart1.x_axis.title = "产品"
        
        data_ref = Reference(ws_sales, min_col=2, min_row=1, 
                           max_col=len(months)+1, max_row=len(products)+1)
        categories_ref = Reference(ws_sales, min_col=1, min_row=2, 
                                 max_row=len(products)+1)
        
        chart1.add_data(data_ref, titles_from_data=True)
        chart1.set_categories(categories_ref)
        ws_sales.add_chart(chart1, "A10")
        
        # 在地区透视表上添加折线图
        ws_region = writer.sheets["地区透视"]
        chart2 = LineChart()
        chart2.title = "各地区销售额趋势"
        chart2.y_axis.title = "销售额"
        chart2.x_axis.title = "月份"
        
        data_ref = Reference(ws_region, min_col=2, min_row=1, 
                           max_col=len(months)+1, max_row=len(regions)+1)
        categories_ref = Reference(ws_region, min_col=2, min_row=1, 
                                 max_col=len(months)+1, max_row=1)
        
        chart2.add_data(data_ref, titles_from_data=True)
        chart2.set_categories(categories_ref)
        ws_region.add_chart(chart2, "A10")
    
    print("销售分析报告已生成")

create_sales_report()

第三部分：openpyxl与pandas结合使用

结合使用openpyxl和pandas可以发挥两者的优势：使用pandas进行数据处理，使用openpyxl进行精细的格式控制。

3.1 使用pandas处理数据，openpyxl设置格式

import pandas as pd
from openpyxl import load_workbook
from openpyxl.styles import Font, PatternFill, Alignment
from openpyxl.utils.dataframe import dataframe_to_rows

# 创建示例数据
data = {
    "产品": ["产品A", "产品B", "产品C", "产品D", "产品E"],
    "销售额": [10000, 15000, 8000, 12000, 20000],
    "成本": [6000, 9000, 5000, 8000, 12000],
    "利润率": [0.4, 0.4, 0.375, 0.333, 0.4]
}

df = pd.DataFrame(data)
df["利润"] = df["销售额"] - df["成本"]

# 先用pandas计算数据
summary = df.agg({
    "销售额": ["sum", "mean", "max"],
    "成本": ["sum", "mean", "max"],
    "利润": ["sum", "mean", "max"]
}).round(2)

# 使用openpyxl创建格式化的Excel文件
wb = load_workbook("销售数据.xlsx") if os.path.exists("销售数据.xlsx") else Workbook()
if "分析报告" in wb.sheetnames:
    ws = wb["分析报告"]
else:
    ws = wb.create_sheet("分析报告")

# 清空现有内容
ws.delete_rows(1, ws.max_row)

# 写入数据
for r in dataframe_to_rows(df, index=False, header=True):
    ws.append(r)

# 跳过一行
ws.append([])

# 写入汇总数据
ws.append(["汇总指标", "销售额", "成本", "利润"])
for idx, (metric, values) in enumerate(summary.iterrows(), start=ws.max_row + 1):
    ws.cell(row=idx, column=1, value=metric)
    for col_idx, value in enumerate(values, start=2):
        ws.cell(row=idx, column=col_idx, value=value)

# 设置格式
header_fill = PatternFill(start_color="366092", end_color="366092", fill_type="solid")
header_font = Font(bold=True, color="FFFFFF")
money_format = '"¥"#,##0.00'
percent_format = "0.00%"

# 格式化表头
for cell in ws[1]:
    cell.fill = header_fill
    cell.font = header_font

# 格式化金额列
for row in ws.iter_rows(min_row=2, max_row=df.shape[0]+1, min_col=2, max_col=4):
    for cell in row:
        cell.number_format = money_format

# 格式化利润率列
for row in ws.iter_rows(min_row=2, max_row=df.shape[0]+1, min_col=5, max_col=5):
    for cell in row:
        cell.number_format = percent_format

# 格式化汇总行
for row in ws.iter_rows(min_row=df.shape[0]+3, max_row=ws.max_row, min_col=2, max_col=4):
    for cell in row:
        cell.number_format = money_format
        cell.font = Font(bold=True)

wb.save("销售数据.xlsx")

3.2 读取现有Excel文件并修改

from openpyxl import load_workbook
import pandas as pd

def update_excel_template(template_path, output_path, data):
    """
    使用数据更新Excel模板
    """
    # 加载模板
    wb = load_workbook(template_path)
    ws = wb["数据输入"]
    
    # 清空现有数据（保留表头）
    if ws.max_row > 1:
        ws.delete_rows(2, ws.max_row - 1)
    
    # 添加新数据
    for row_data in data:
        ws.append(row_data)
    
    # 更新公式单元格（假设模板中有公式）
    for row in range(2, ws.max_row + 1):
        # 更新利润列公式
        ws[f"E{row}"] = f"=C{row}-D{row}"
        # 更新利润率列公式
        ws[f"F{row}"] = f"=E{row}/C{row}"
    
    # 保存更新后的文件
    wb.save(output_path)
    print(f"文件已更新并保存为: {output_path}")

# 示例数据
new_data = [
    ["产品F", "东部", 15000, 9000],
    ["产品G", "西部", 18000, 11000],
    ["产品H", "南部", 12000, 7500]
]

update_excel_template("销售模板.xlsx", "更新后的销售数据.xlsx", new_data)

第四部分：综合实战项目——财务报表生成器

让我们构建一个完整的财务报表生成系统，该系统将：

从多个数据源收集数据
使用pandas进行数据清洗和分析
使用openpyxl创建格式化的财务报表
自动生成图表和摘要

代码实现：

# financial_report_generator.py
import pandas as pd
import numpy as np
from openpyxl import Workbook, load_workbook
from openpyxl.styles import Font, PatternFill, Alignment, Border, Side
from openpyxl.chart import BarChart, Reference, LineChart, PieChart
from openpyxl.utils import get_column_letter
from datetime import datetime
import os

class FinancialReportGenerator:
    def __init__(self):
        self.wb = None
        self.styles = self._define_styles()
    
    def _define_styles(self):
        """定义样式"""
        return {
            "header": {
                "font": Font(bold=True, color="FFFFFF", size=12),
                "fill": PatternFill(start_color="366092", end_color="366092", fill_type="solid"),
                "alignment": Alignment(horizontal="center", vertical="center"),
                "border": Border(
                    left=Side(style="thin"),
                    right=Side(style="thin"),
                    top=Side(style="thin"),
                    bottom=Side(style="thin")
                )
            },
            "money": {
                "number_format": '"¥"#,##0.00'
            },
            "percent": {
                "number_format": "0.00%"
            },
            "total": {
                "font": Font(bold=True),
                "fill": PatternFill(start_color="F2F2F2", end_color="F2F2F2", fill_type="solid")
            }
        }
    
    def generate_sample_data(self):
        """生成示例财务数据"""
        np.random.seed(42)
        months = ["一月", "二月", "三月", "四月", "五月", "六月"]
        categories = {
            "收入": ["产品A销售", "产品B销售", "服务收入", "其他收入"],
            "成本": ["原材料", "人工成本", "营销费用", "管理费用", "研发费用"]
        }
        
        data = []
        for month in months:
            # 收入
            for category in categories["收入"]:
                amount = np.random.randint(50000, 200000)
                data.append([month, "收入", category, amount])
            
            # 成本
            for category in categories["成本"]:
                amount = np.random.randint(10000, 100000)
                data.append([month, "成本", category, -amount])  # 成本为负值
        
        df = pd.DataFrame(data, columns=["月份", "类型", "类别", "金额"])
        return df
    
    def create_pivot_tables(self, df):
        """创建数据透视表"""
        # 月度汇总
        monthly_summary = pd.pivot_table(
            df, values="金额", index="月份", 
            columns="类型", aggfunc="sum", fill_value=0
        )
        monthly_summary["利润"] = monthly_summary.get("收入", 0) + monthly_summary.get("成本", 0)
        monthly_summary["利润率"] = monthly_summary["利润"] / monthly_summary.get("收入", 1)
        
        # 收入分类汇总
        income_by_category = pd.pivot_table(
            df[df["类型"] == "收入"], 
            values="金额", index="月份", 
            columns="类别", aggfunc="sum", fill_value=0
        )
        
        # 成本分类汇总
        cost_by_category = pd.pivot_table(
            df[df["类型"] == "成本"], 
            values="金额", index="月份", 
            columns="类别", aggfunc="sum", fill_value=0
        )
        
        return {
            "monthly_summary": monthly_summary,
            "income_by_category": income_by_category,
            "cost_by_category": cost_by_category
        }
    
    def create_financial_report(self, output_path="财务报表.xlsx"):
        """创建财务报表"""
        # 生成数据
        df = self.generate_sample_data()
        pivots = self.create_pivot_tables(df)
        
        # 创建工作簿
        self.wb = Workbook()
        
        # 添加原始数据表
        self._add_raw_data_sheet(df)
        
        # 添加月度汇总表
        self._add_monthly_summary_sheet(pivots["monthly_summary"])
        
        # 添加收入分析表
        self._add_income_analysis_sheet(pivots["income_by_category"])
        
        # 添加成本分析表
        self._add_cost_analysis_sheet(pivots["cost_by_category"])
        
        # 添加仪表板
        self._add_dashboard_sheet(pivots["monthly_summary"])
        
        # 删除默认工作表
        if "Sheet" in self.wb.sheetnames:
            self.wb.remove(self.wb["Sheet"])
        
        # 保存文件
        self.wb.save(output_path)
        print(f"财务报表已生成: {output_path}")
    
    def _add_raw_data_sheet(self, df):
        """添加原始数据表"""
        ws = self.wb.create_sheet("原始数据")
        
        # 写入数据
        headers = list(df.columns)
        ws.append(headers)
        
        for _, row in df.iterrows():
            ws.append(list(row))
        
        # 应用样式
        for col_idx, header in enumerate(headers, 1):
            cell = ws.cell(row=1, column=col_idx)
            for attr, value in self.styles["header"].items():
                setattr(cell, attr, value)
        
        # 设置列宽
        for col_idx in range(1, len(headers) + 1):
            ws.column_dimensions[get_column_letter(col_idx)].width = 15
        
        # 设置金额格式
        for row in ws.iter_rows(min_row=2, max_row=ws.max_row, min_col=4, max_col=4):
            for cell in row:
                cell.number_format = self.styles["money"]["number_format"]
    
    def _add_monthly_summary_sheet(self, df):
        """添加月度汇总表"""
        ws = self.wb.create_sheet("月度汇总")
        
        # 写入数据
        headers = ["月份"] + list(df.columns)
        ws.append(headers)
        
        for month, row in df.iterrows():
            ws.append([month] + list(row))
        
        # 添加总计行
        total_row = ["总计"]
        for col in df.columns:
            if col == "利润率":
                total = df["利润"].sum() / df["收入"].sum()
            else:
                total = df[col].sum()
            total_row.append(total)
        
        ws.append(total_row)
        
        # 应用样式
        for col_idx, header in enumerate(headers, 1):
            cell = ws.cell(row=1, column=col_idx)
            for attr, value in self.styles["header"].items():
                setattr(cell, attr, value)
        
        # 设置数字格式
        money_cols = [i for i, col in enumerate(headers, 1) if col != "月份" and col != "利润率"]
        percent_cols = [i for i, col in enumerate(headers, 1) if col == "利润率"]
        
        for row in ws.iter_rows(min_row=2, max_row=ws.max_row):
            for cell in row:
                if cell.column in money_cols:
                    cell.number_format = self.styles["money"]["number_format"]
                elif cell.column in percent_cols:
                    cell.number_format = self.styles["percent"]["number_format"]
        
        # 设置总计行样式
        for cell in ws[ws.max_row]:
            for attr, value in self.styles["total"].items():
                setattr(cell, attr, value)
        
        # 设置列宽
        for col_idx in range(1, len(headers) + 1):
            ws.column_dimensions[get_column_letter(col_idx)].width = 15
        
        # 添加图表
        self._add_monthly_charts(ws, df)
    
    def _add_monthly_charts(self, ws, df):
        """添加月度图表"""
        # 收入成本利润柱状图
        chart1 = BarChart()
        chart1.type = "col"
        chart1.style = 10
        chart1.title = "月度收入、成本与利润"
        chart1.y_axis.title = "金额"
        chart1.x_axis.title = "月份"
        
        data_ref = Reference(ws, min_col=2, min_row=1, max_col=4, max_row=len(df)+1)
        categories_ref = Reference(ws, min_col=1, min_row=2, max_row=len(df)+1)
        
        chart1.add_data(data_ref, titles_from_data=True)
        chart1.set_categories(categories_ref)
        ws.add_chart(chart1, "A15")
        
        # 利润率折线图
        chart2 = LineChart()
        chart2.title = "月度利润率趋势"
        chart2.y_axis.title = "利润率"
        chart2.x_axis.title = "月份"
        
        data_ref = Reference(ws, min_col=5, min_row=1, max_col=5, max_row=len(df)+1)
        categories_ref = Reference(ws, min_col=1, min_row=2, max_row=len(df)+1)
        
        chart2.add_data(data_ref, titles_from_data=True)
        chart2.set_categories(categories_ref)
        ws.add_chart(chart2, "J15")
    
    def _add_income_analysis_sheet(self, df):
        """添加收入分析表"""
        ws = self.wb.create_sheet("收入分析")
        
        # 写入数据
        headers = ["月份"] + list(df.columns)
        ws.append(headers)
        
        for month, row in df.iterrows():
            ws.append([month] + list(row))
        
        # 添加总计行
        total_row = ["总计"] + [df[col].sum() for col in df.columns]
        ws.append(total_row)
        
        # 添加百分比行
        percent_row = ["占比"]
        total_income = sum(total_row[1:])
        for value in total_row[1:]:
            percent = value / total_income
            percent_row.append(percent)
        
        ws.append(percent_row)
        
        # 应用样式
        for col_idx, header in enumerate(headers, 1):
            cell = ws.cell(row=1, column=col_idx)
            for attr, value in self.styles["header"].items():
                setattr(cell, attr, value)
        
        # 设置数字格式
        for row in ws.iter_rows(min_row=2, max_row=ws.max_row - 2):  # 数据行
            for cell in row:
                if cell.column > 1:  # 跳过月份列
                    cell.number_format = self.styles["money"]["number_format"]
        
        # 设置百分比行格式
        for cell in ws[ws.max_row]:
            if cell.column > 1:  # 跳过"占比"列
                cell.number_format = self.styles["percent"]["number_format"]
        
        # 设置总计行样式
        for cell in ws[ws.max_row - 1]:
            for attr, value in self.styles["total"].items():
                setattr(cell, attr, value)
        
        # 设置列宽
        for col_idx in range(1, len(headers) + 1):
            ws.column_dimensions[get_column_letter(col_idx)].width = 15
        
        # 添加饼图
        chart = PieChart()
        chart.title = "收入构成"
        
        labels_ref = Reference(ws, min_col=1, min_row=1, max_row=1, min_col=2, max_col=len(headers))
        data_ref = Reference(ws, min_col=2, min_row=ws.max_row - 1, max_col=len(headers), max_row=ws.max_row - 1)
        
        chart.add_data(data_ref, titles_from_data=True)
        chart.set_categories(labels_ref)
        ws.add_chart(chart, "A15")
    
    def _add_cost_analysis_sheet(self, df):
        """添加成本分析表"""
        # 与收入分析类似，省略详细实现
        pass
    
    def _add_dashboard_sheet(self, monthly_summary):
        """添加仪表板"""
        ws = self.wb.create_sheet("仪表板")
        
        # 添加关键指标
        total_income = monthly_summary["收入"].sum()
        total_cost = monthly_summary["成本"].sum()
        total_profit = monthly_summary["利润"].sum()
        avg_profit_margin = monthly_summary["利润率"].mean()
        
        metrics = [
            ["关键指标", "值"],
            ["总收入", total_income],
            ["总成本", total_cost],
            ["总利润", total_profit],
            ["平均利润率", avg_profit_margin],
            ["最佳月份", monthly_summary["利润"].idxmax()],
            ["最佳月利润", monthly_summary["利润"].max()]
        ]
        
        for row in metrics:
            ws.append(row)
        
        # 应用样式
        for col_idx in range(1, 3):
            cell = ws.cell(row=1, column=col_idx)
            for attr, value in self.styles["header"].items():
                setattr(cell, attr, value)
        
        # 设置数字格式
        for row in range(2, len(metrics) + 1):
            if row in [2, 3, 4, 7]:  # 金额行
                ws.cell(row=row, column=2).number_format = self.styles["money"]["number_format"]
            elif row == 5:  # 利润率行
                ws.cell(row=row, column=2).number_format = self.styles["percent"]["number_format"]
        
        # 设置列宽
        ws.column_dimensions['A'].width = 15
        ws.column_dimensions['B'].width = 20

# 使用示例
if __name__ == "__main__":
    generator = FinancialReportGenerator()
    generator.create_financial_report("公司财务报表.xlsx")

项目扩展思路：

数据源集成：从数据库或API获取真实数据，而不是生成模拟数据
自动化报告：使用APScheduler或Celery实现定期自动生成报告
邮件发送：集成邮件功能，自动将报告发送给相关人员
Web界面：使用Flask或Streamlit创建Web界面，允许用户上传数据和自定义报告
多公司支持：扩展支持为多个公司生成财务报表
高级分析：集成机器学习算法，提供预测和异常检测功能

总结

通过本章的学习，你已经全面掌握了使用openpyxl和pandas处理Excel文件的各个方面：

openpyxl基础：掌握了工作簿、工作表和单元格的基本操作，以及样式设置和图表创建
pandas Excel集成：学会了使用pandas读写Excel文件，处理多工作表和数据透视表
高级技巧：掌握了两个库的结合使用，实现数据处理与格式控制的完美结合
实战应用：构建了完整的财务报表生成系统，综合运用了所学知识

最佳实践总结：

对于简单的数据读写，优先使用pandas，它更简洁高效
对于需要精细格式控制的场景，使用openpyxl
结合两者优势：使用pandas处理数据，使用openpyxl设置格式
处理大型Excel文件时，考虑使用openpyxl的只读模式优化内存使用
始终处理可能出现的异常，如文件不存在、格式错误等
为复杂的Excel操作创建可重用的函数和类

Excel文件处理是Python在企业环境中最重要的应用之一。通过掌握openpyxl和pandas，你能够自动化繁琐的Excel操作任务，提高工作效率，并为更复杂的数据处理和分析工作奠定坚实基础。

亚马逊云科技技术品牌专区

更多推荐

图解人工智能（86）人工智能前沿-和数学家做朋友

亚马逊云科技技术品牌专区

图解人工智能（87）人工智能前沿-和数学家做朋友

开普勒猜想是关于三维空间球体最密堆积的数学问题，认为面心立方和六方最密堆积的74.048%密度是最优解。该猜想由17世纪天文学家开普勒提出，直到1998年数学家托马斯·黑尔斯借助计算机辅助证明才最终解决。证明过程涉及大量复杂计算，计算机帮助验证了数千种特殊情况。这种堆积方式类似水果摊上橘子的自然堆叠，证实了日常直觉背后的数学原理。