Python 使用 openpyxl 和 pandas 处理 Excel 详解

一、Excel 处理在数据分析中的重要性

在现代数据分析工作中，Excel 文件几乎无处不在。作为最广泛使用的电子表格工具，Excel 在企业数据存储、报表生成和初步数据分析中扮演着重要角色。根据最新调查，超过 80% 的企业在日常运营中使用 Excel 作为主要的数据管理工具之一。因此，掌握 Python 处理 Excel 文件的能力对于数据分析师、财务人员和科研工作者来说至关重要。

Python 提供了多种处理 Excel 文件的库，其中最常用的是 openpyxl 和 pandas。openpyxl 专注于直接操作 Excel 文件（特别是.xlsx 格式），提供了单元格级别的精细控制；而 pandas 则是一个强大的数据分析库，可以方便地将 Excel 数据读入 DataFrame 进行复杂的数据处理和分析。

本文将深入探讨这两个库的使用方法，从基础操作到高级技巧，帮助读者全面掌握 Python 处理 Excel 文件的能力。我们将通过大量实际示例展示如何结合使用这两个库来完成各种复杂的 Excel 处理任务。

二、openpyxl 基础与核心功能

2.1 openpyxl 简介与安装

openpyxl 是一个专门用于读写 Excel 2010 xlsx/xlsm/xltx/xltm 文件的 Python 库。它不依赖于 Excel 软件本身，可以直接操作 Excel 文件，非常适合自动化处理 Excel 报表。

安装 openpyxl 非常简单，使用 pip 命令即可：

pip install openpyxl

如果需要处理图表，还需要安装额外的依赖：

pip install openpyxl[charts]

2.2 工作簿与工作表的基本操作

创建新工作簿

from openpyxl import Workbook

# 创建一个新工作簿
wb = Workbook()
# 获取活动的工作表
ws = wb.active
# 设置工作表标题
ws.title = "第一个工作表"
# 创建新的工作表
ws1 = wb.create_sheet("第二个工作表")
# 默认插入到最后
ws2 = wb.create_sheet("第三个工作表", 0)
# 插入到第一个位置
# 保存工作簿
wb.save("新工作簿.xlsx")

打开已有工作簿

from openpyxl import load_workbook

# 打开一个已存在的工作簿
wb = load_workbook('example.xlsx')
# 获取所有工作表名称
print(wb.sheetnames)
# 通过名称获取工作表
ws = wb['Sheet1']
# 检查工作表是否存在
if 'Sheet1' in wb.sheetnames:
    print("Sheet1 存在")

工作表操作

# 复制工作表
source = wb['Sheet1']
target = wb.copy_worksheet(source)
target.title = "Sheet1 的副本"
# 删除工作表
del wb['Sheet1 的副本']
# 遍历所有工作表
for sheet in wb:
    print(sheet.title)

2.3 单元格操作详解

基本单元格操作

# 获取单元格
cell = ws['A1']
# 写入值
ws['A1'] = "Hello"
ws['B1'] = "World"
# 使用单元格坐标
ws.cell(row=1, column=3, value="!")
# 读取值
print(ws['A1'].value)  # 输出：Hello
# 单元格坐标
print(cell.row, cell.column)  # 输出：1, 1
print(cell.coordinate)  # 输出：A1

批量操作单元格

# 批量写入数据
for row in range(1, 6):
    for col in range(1, 5):
        ws.cell(row=row, column=col, value=f"R{row}C{col}")

# 批量读取数据
for row in ws.iter_rows(min_row=1, max_row=3, min_col=1, max_col=3):
    for cell in row:
        print(cell.value, end="\t")
    print()

# 使用 values_only 参数只获取值
for row in ws.iter_rows(values_only=True):
    print(row)

特殊单元格操作

# 合并单元格
ws.merge_cells('A1:D1')
ws['A1'] = "合并的标题"
# 取消合并
ws.unmerge_cells('A1:D1')
# 设置行高和列宽
ws.row_dimensions[1].height = 30
ws.column_dimensions['A'].width = 20
# 隐藏行列
ws.column_dimensions['B'].hidden = True
ws.row_dimensions[2].hidden = True

2.4 样式与格式设置

字体样式

from openpyxl.styles import Font, Color

# 设置字体
bold_font = Font(name='Arial', size=12, bold=True, italic=False, color='FF0000')
ws['A1'].font = bold_font

# 修改现有单元格字体
for row in ws.iter_rows(min_row=2, max_row=5):
    for cell in row:
        cell.font = Font(size=10, color=Color(rgb='0000FF'))

对齐方式

from openpyxl.styles import Alignment

# 设置对齐方式
center_aligned = Alignment(horizontal='center', vertical='center', wrap_text=True)
ws['A1'].alignment = center_aligned

# 应用到范围
for row in ws.iter_rows(min_row=1, max_row=10):
    for cell in row:
        cell.alignment = Alignment(horizontal='center')

边框设置

from openpyxl.styles import Border, Side

# 定义边框样式
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=1, max_row=5):
    for cell in row:
        cell.border = thin_border

填充颜色

from openpyxl.styles import PatternFill, GradientFill

# 纯色填充
yellow_fill = PatternFill(start_color='FFFF00', end_color='FFFF00', fill_type='solid')
ws['A1'].fill = yellow_fill

# 渐变填充
gradient_fill = GradientFill(stop=('FFFFFF','0000FF'))
ws['B1'].fill = gradient_fill

数字格式

from openpyxl.styles import numbers

# 设置数字格式
ws['C1'].value = 3.1415926
ws['C1'].number_format = numbers.FORMAT_NUMBER_00  # 显示两位小数

# 自定义格式
ws['D1'].value = 0.85
ws['D1'].number_format = '0.00%'  # 显示为百分比

2.5 公式与计算

# 写入公式
ws['E1'] = '=SUM(A1:D1)'
ws['E2'] = '=AVERAGE(A2:D2)'
ws['E3'] = '=IF(A3>B3, "A 大", "B 大")'

# 读取公式
print(ws['E1'].value)  # 输出：=SUM(A1:D1)

# 计算数据
ws['F1'] = '结果'
ws['F2'] = '=E2*100'

2.6 图表与图像操作

创建图表

from openpyxl.chart import BarChart, Reference

# 准备数据
for i in range(1, 6):
    ws[f'A{i}'] = i
    ws[f'B{i}'] = i * i

# 创建柱状图
chart = BarChart()
data = Reference(ws, min_col=2, min_row=1, max_row=5)
categories = Reference(ws, min_col=1, min_row=2, max_row=5)
chart.add_data(data, titles_from_data=True)
chart.set_categories(categories)

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

插入图像

from openpyxl.drawing.image import Image

# 插入图像
img = Image('logo.png')
ws.add_image(img, 'A10')

# 调整图像大小
img.width = 100
img.height = 100

2.7 高级功能

数据验证

from openpyxl.worksheet.datavalidation import DataValidation

# 创建数据验证
dv = DataValidation(type="list", formula1='"男，女"', allow_blank=True)
dv.add('A1:A10')  # 应用到 A1:A10 范围
ws.add_data_validation(dv)

# 数字范围验证
dv_num = DataValidation(type="whole", operator="between", formula1=1, formula2=100)
dv_num.error = "输入必须在 1 到 100 之间"
dv_num.add('B1:B10')
ws.add_data_validation(dv_num)

条件格式

from openpyxl.formatting.rule import ColorScaleRule, FormulaRule

# 色阶条件格式
color_scale_rule = ColorScaleRule(start_type='min', start_color='FF0000', mid_type='percentile', mid_value=50, mid_color='FFFF00', end_type='max', end_color='00FF00')
ws.conditional_formatting.add('C1:C10', color_scale_rule)

# 公式条件格式
formula_rule = FormulaRule(formula=['ISBLANK(C1)'], stopIfTrue=True, font=Font(color='FF0000'))
ws.conditional_formatting.add('C1:C10', formula_rule)

保护工作表

from openpyxl.styles import Protection

# 保护工作表
ws.protection.sheet = True
ws.protection.password = 'password'
ws.protection.enable()

# 解锁特定单元格
for row in ws.iter_rows(min_row=1, max_row=5):
    for cell in row:
        cell.protection = Protection(locked=False)

冻结窗格

# 冻结第一行和第一列
ws.freeze_panes = 'B2'
# 解冻
ws.freeze_panes = None

三、pandas 基础与核心功能

3.1 pandas 简介与安装

pandas 是一个强大的开源数据分析和操作库，提供了高性能、易用的数据结构和数据分析工具。它特别适合处理表格数据（如 Excel 文件）和时间序列数据。

安装 pandas 及其依赖：

pip install pandas openpyxl xlrd

注意：xlrd 库用于读取旧版 Excel 文件 (.xls)，而 openpyxl 则用于处理.xlsx 文件。

3.2 DataFrame 基础

DataFrame 是 pandas 的核心数据结构，可以看作是一个二维表格，类似于 Excel 工作表。

创建 DataFrame

import pandas as pd

# 从字典创建
data = {'姓名': ['张三', '李四', '王五'], '年龄': [25, 30, 35], '城市': ['北京', '上海', '广州']}
df = pd.DataFrame(data)

# 从列表创建
data = [{'姓名': '张三', '年龄': 25, '城市': '北京'}, {'姓名': '李四', '年龄': 30, '城市': '上海'}, {'姓名': '王五', '年龄': 35, '城市': '广州'}]
df = pd.DataFrame(data)

# 显示 DataFrame
print(df)

基本属性

# 查看前几行
print(df.head(2))
# 查看后几行
print(df.tail(1))
# 查看形状
print(df.shape)  # 输出：(3, 3)
# 查看列名
print(df.columns)  # 输出：Index(['姓名', '年龄', '城市'], dtype='object')
# 查看索引
print(df.index)  # 输出：RangeIndex(start=0, stop=3, step=1)
# 查看数据类型
print(df.dtypes)

数据选择

# 选择列
print(df['姓名'])
# 选择单列
print(df[['姓名', '年龄']])
# 选择多列
# 选择行
print(df.iloc[0])  # 通过位置选择
print(df.loc[0])   # 通过索引选择
print(df[1:3])     # 切片选择
# 条件选择
print(df[df['年龄'] > 28])
print(df[(df['年龄'] > 25) & (df['城市'] == '上海')])

数据修改

# 添加列
df['性别'] = ['男', '女', '男']
# 修改值
df.loc[0, '年龄'] = 26
df['年龄'] = df['年龄'] + 1
# 删除列
df = df.drop('性别', axis=1)
# 删除行
df = df.drop(0, axis=0)

3.3 数据导入与导出

读取 Excel 文件

# 读取整个 Excel 文件
df = pd.read_excel('data.xlsx', sheet_name='Sheet1')
# 读取指定范围
df = pd.read_excel('data.xlsx', sheet_name='Sheet1', usecols='A:C', nrows=10)
# 读取多个工作表
with pd.ExcelFile('data.xlsx') as xls:
    df1 = pd.read_excel(xls, 'Sheet1')
    df2 = pd.read_excel(xls, 'Sheet2')
# 处理缺失值
df = pd.read_excel('data.xlsx', na_values=['NA', 'N/A', '缺失'])

写入 Excel 文件

# 写入单个 DataFrame
df.to_excel('output.xlsx', sheet_name='数据', index=False)
# 写入多个 DataFrame
with pd.ExcelWriter('output.xlsx') as writer:
    df1.to_excel(writer, sheet_name='Sheet1')
    df2.to_excel(writer, sheet_name='Sheet2')
# 追加模式写入
with pd.ExcelWriter('output.xlsx', mode='a') as writer:
    df3.to_excel(writer, sheet_name='Sheet3')
# 设置格式
df.to_excel('output.xlsx', sheet_name='数据', index=False, float_format="%.2f", freeze_panes=(1, 0))  # 冻结首行

其他格式支持

# CSV 文件
df.to_csv('data.csv', index=False)
df = pd.read_csv('data.csv')
# JSON
df.to_json('data.json', orient='records')
df = pd.read_json('data.json')
# SQL 数据库
from sqlalchemy import create_engine
engine = create_engine('sqlite:///data.db')
df.to_sql('table_name', engine, if_exists='replace')
df = pd.read_sql('SELECT * FROM table_name', engine)

3.4 数据清洗与预处理

处理缺失值

# 检测缺失值
print(df.isnull().sum())
# 删除缺失值
df_cleaned = df.dropna()  # 删除任何包含缺失值的行
df_cleaned = df.dropna(subset=['年龄'])  # 只删除年龄列有缺失的行
# 填充缺失值
df_filled = df.fillna(0)  # 用 0 填充
df_filled = df.fillna(df.mean())  # 用均值填充数值列
df_filled = df.fillna(method='ffill')  # 用前一个值填充

处理重复值

# 检测重复行
print(df.duplicated().sum())
# 删除重复行
df_unique = df.drop_duplicates()
# 基于某些列删除重复
df_unique = df.drop_duplicates(subset=['姓名', '城市'])

数据类型转换

# 查看数据类型
print(df.dtypes)
# 转换数据类型
df['年龄'] = df['年龄'].astype('float64')
df['日期'] = pd.to_datetime(df['日期'])
# 分类数据
df['城市'] = df['城市'].astype('category')

字符串操作

# 字符串方法
df['姓名'] = df['姓名'].str.upper()  # 转为大写
df['城市'] = df['城市'].str.replace('京', '都')  # 替换
# 提取信息
df['姓氏'] = df['姓名'].str[0]  # 提取第一个字符
df['名字'] = df['姓名'].str[1:]  # 提取第二个字符之后
# 拆分列
df[['姓', '名']] = df['姓名'].str.split(expand=True)

日期处理

# 解析日期
df['日期'] = pd.to_datetime(df['日期'], format='%Y-%m-%d')
# 提取日期部分
df['年'] = df['日期'].dt.year
df['月'] = df['日期'].dt.month
df['日'] = df['日期'].dt.day
df['星期'] = df['日期'].dt.day_name()
# 日期运算
df['年龄天数'] = (pd.to_datetime('today') - df['出生日期']).dt.days
df['年龄'] = df['年龄天数'] // 365

3.5 数据转换与计算

应用函数

# 应用简单函数
df['年龄加 10'] = df['年龄'].apply(lambda x: x + 10)

# 应用复杂函数
def age_group(age):
    if age < 20:
        return '少年'
    elif age < 40:
        return '青年'
    else:
        return '中年'

df['年龄段'] = df['年龄'].apply(age_group)

# 向量化操作
df['BMI'] = df['体重'] / (df['身高'] / 100) ** 2

分组聚合

# 基本分组
grouped = df.groupby('城市')
# 聚合函数
print(grouped['年龄'].mean())  # 每个城市的平均年龄
print(grouped.agg({'年龄': ['mean', 'min', 'max'], '收入': 'sum'}))
# 多级分组
grouped = df.groupby(['城市', '性别'])
print(grouped['年龄'].mean())

数据透视表

# 简单透视表
pivot = pd.pivot_table(df, values='年龄', index='城市', aggfunc='mean')
# 复杂透视表
pivot = pd.pivot_table(df, values=['年龄', '收入'], index=['城市', '性别'], columns=['教育程度'], aggfunc={'年龄': 'mean', '收入': ['sum', 'count']}, fill_value=0, margins=True)

合并与连接

# 合并两个 DataFrame
df1 = pd.DataFrame({'key': ['A', 'B', 'C'], 'value': [1, 2, 3]})
df2 = pd.DataFrame({'key': ['A', 'B', 'D'], 'value': [4, 5, 6]})
# 内连接
pd.merge(df1, df2, on='key', how='inner')
# 左连接
pd.merge(df1, df2, on='key', how='left')
# 外连接
pd.merge(df1, df2, on='key', how='outer')
# 纵向合并
pd.concat([df1, df2], axis=0)

3.6 高级数据分析功能

时间序列分析

import numpy as np

# 创建时间序列
date_rng = pd.date_range(start='1/1/2020', end='1/10/2020', freq='D')
df = pd.DataFrame(date_rng, columns=['date'])
df['data'] = np.random.randint(0, 100, size=(len(date_rng)))

# 设置为索引
df = df.set_index('date')
# 重采样
df.resample('W').mean()  # 按周平均
df.resample('M').sum()   # 按月求和
# 滚动窗口
df.rolling(window=3).mean()  # 3 天移动平均

统计函数

# 描述性统计
print(df.describe())
# 相关性
print(df.corr())
# 协方差
print(df.cov())
# 唯一值计数
print(df['城市'].value_counts())
# 交叉表
pd.crosstab(df['城市'], df['性别'])

可视化

import matplotlib.pyplot as plt

# 线图
df.plot.line()
# 柱状图
df.plot.bar()
# 直方图
df['年龄'].plot.hist(bins=20)
# 箱线图
df.plot.box()
# 散点图
df.plot.scatter(x='年龄', y='收入')
plt.show()

四、openpyxl 与 pandas 的协同使用

4.1 结合使用的优势与场景

openpyxl 和 pandas 各有优势，在实际工作中结合使用可以发挥更大威力：

数据流转：pandas 适合数据处理和分析，openpyxl 适合精细控制 Excel 格式，两者结合可以实现"pandas 处理数据→openpyxl 调整格式"的完整流程。
复杂报表：对于需要复杂格式的报表，可以先用 pandas 生成数据，再用 openpyxl 添加图表、条件格式等高级功能。
模板填充：使用 Excel 文件作为模板，pandas 填充数据，openpyxl 保持原有格式和公式。
大数据处理：当 Excel 文件很大时，可以先用 pandas 高效读取和处理数据，再用 openpyxl 进行必要的格式调整。

4.2 pandas DataFrame 与 openpyxl 的转换

DataFrame 写入 Excel 并保持格式

from openpyxl import load_workbook
from openpyxl.utils.dataframe import dataframe_to_rows
import pandas as pd

# 创建 DataFrame
df = pd.DataFrame({'产品': ['A', 'B', 'C', 'D'], '销量': [120, 150, 90, 200], '单价': [25.5, 32.0, 18.0, 40.5]})

# 加载现有工作簿或创建新工作簿
try:
    wb = load_workbook('report.xlsx')
except FileNotFoundError:
    wb = Workbook()

# 选择或创建工作表
if '销售报告' in wb.sheetnames:
    ws = wb['销售报告']
else:
    ws = wb.create_sheet('销售报告')

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

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

# 添加公式
ws['E1'] = '销售额'
for row in range(2, ws.max_row + 1):
    ws[f'E{row}'] = f'=B{row}*C{row}'

# 设置格式
header_font = Font(bold=True, color='FFFFFF')
header_fill = PatternFill(start_color='4F81BD', end_color='4F81BD', fill_type='solid')
for cell in ws[1]:
    cell.font = header_font
    cell.fill = header_fill

# 保存工作簿
wb.save('report.xlsx')

从 openpyxl 读取数据到 DataFrame

from openpyxl import load_workbook

# 加载工作簿
wb = load_workbook('data.xlsx')
ws = wb.active

# 将工作表数据转换为列表
data = []
for row in ws.iter_rows(values_only=True):
    data.append(row)

# 转换为 DataFrame
df = pd.DataFrame(data[1:], columns=data[0])

# 处理数据
df['日期'] = pd.to_datetime(df['日期'])
df['销售额'] = df['数量'] * df['单价']
print(df.head())

4.3 复杂报表生成案例

import pandas as pd
from openpyxl import Workbook
from openpyxl.styles import Font, Alignment, Border, Side, PatternFill
from openpyxl.chart import BarChart, Reference
from openpyxl.drawing.image import Image
from datetime import datetime

# 1. 准备数据
sales_data = {'月份': ['1 月', '2 月', '3 月', '4 月', '5 月', '6 月'], '产品 A': [1200, 1500, 1800, 2100, 2400, 2700], '产品 B': [800, 950, 1100, 1250, 1400, 1550], '产品 C': [500, 600, 700, 800, 900, 1000]}
df = pd.DataFrame(sales_data)

# 2. 创建 Excel 工作簿
wb = Workbook()
ws = wb.active
ws.title = "销售报告"

# 3. 写入标题
ws['A1'] = "2023 年上半年销售报告"
ws['A1'].font = Font(size=16, bold=True, name='微软雅黑')
ws.merge_cells('A1:D1')

# 4. 写入数据
# 写入列标题
columns = list(df.columns)
for col_num, column_title in enumerate(columns, 1):
    cell = ws.cell(row=3, column=col_num, value=column_title)
    cell.font = Font(bold=True)
    cell.alignment = Alignment(horizontal='center')

# 写入数据
for row_num, row_data in enumerate(df.values, 4):
    for col_num, cell_value in enumerate(row_data, 1):
        ws.cell(row=row_num, column=col_num, value=cell_value)

# 5. 添加汇总行
last_row = ws.max_row + 1
ws.cell(row=last_row, column=1, value="总计").font = Font(bold=True)
for col_num in range(2, 5):
    col_letter = chr(64 + col_num)
    ws.cell(row=last_row, column=col_num, value=f"=SUM({col_letter}4:{col_letter}{last_row-1})")

# 6. 设置格式
# 设置边框
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=last_row, min_col=1, max_col=4):
    for cell in row:
        cell.border = thin_border
        if cell.row > 3 and cell.column > 1:  # 数据单元格
            cell.number_format = '#,##0'

# 设置对齐
for row in ws.iter_rows(min_row=3, max_row=last_row, min_col=1, max_col=4):
    for cell in row:
        cell.alignment = Alignment(horizontal='center')

# 7. 创建图表
chart = BarChart()
chart.type = "col"
chart.style = 10
chart.title = "产品销售趋势"
chart.y_axis.title = "销售额"
chart.x_axis.title = "月份"
data = Reference(ws, min_col=2, max_col=4, min_row=3, max_row=last_row-1)
categories = Reference(ws, min_col=1, min_row=4, max_row=last_row-1)
chart.add_data(data, titles_from_data=True)
chart.set_categories(categories)
ws.add_chart(chart, "F3")

# 8. 添加页脚
footer_row = last_row + 2
ws.cell(row=footer_row, column=1, value=f"报告生成时间：{datetime.now().strftime('%Y-%m-%d %H:%M:%S')}")

# 9. 调整列宽
ws.column_dimensions['A'].width = 12
ws.column_dimensions['B'].width = 12
ws.column_dimensions['C'].width = 12
ws.column_dimensions['D'].width = 12

# 10. 保存文件
filename = f"销售报告_{datetime.now().strftime('%Y%m%d')}.xlsx"
wb.save(filename)
print(f"报表已生成：{filename}")

4.4 模板填充技术

from openpyxl import load_workbook
from openpyxl.utils.dataframe import dataframe_to_rows
import pandas as pd

# 1. 加载模板文件
template_path = 'report_template.xlsx'
wb = load_workbook(template_path)
ws = wb['Data']

# 2. 准备数据
data = {'Region': ['North', 'South', 'East', 'West'], 'Q1': [1200, 1500, 1800, 900], 'Q2': [1300, 1600, 1900, 950], 'Q3': [1400, 1700, 2000, 1000], 'Q4': [1500, 1800, 2100, 1050]}
df = pd.DataFrame(data)

# 3. 清空模板中的数据区域 (保留格式)
for row in ws.iter_rows(min_row=3, max_row=100, min_col=1, max_col=6):
    for cell in row:
        cell.value = None

# 4. 写入新数据
for r_idx, row in enumerate(dataframe_to_rows(df, index=False, header=False), 3):
    for c_idx, value in enumerate(row, 1):
        ws.cell(row=r_idx, column=c_idx, value=value)

# 5. 更新公式 (假设模板中已有公式)
last_data_row = 3 + len(df) - 1
for row in range(3, last_data_row + 1):
    ws[f'F{row}'] = f'=SUM(B{row}:E{row})'

# 6. 更新汇总公式
ws['B20'] = f'=SUM(B3:B{last_data_row})'
ws['C20'] = f'=SUM(C3:C{last_data_row})'
ws['D20'] = f'=SUM(D3:D{last_data_row})'
ws['E20'] = f'=SUM(E3:E{last_data_row})'
ws['F20'] = f'=SUM(F3:F{last_data_row})'

# 7. 更新报告日期
ws['A1'] = f"销售报告 - {pd.Timestamp.today().strftime('%Y-%m-%d')}"

# 8. 保存新文件
output_path = 'quarterly_report.xlsx'
wb.save(output_path)
print(f"报告已生成：{output_path}")

4.5 性能优化技巧

当处理大型 Excel 文件时，性能可能成为问题。以下是一些优化技巧：

只读模式：如果只需要读取数据而不修改文件，使用只读模式可以显著提高速度。

wb = load_workbook('large_file.xlsx', read_only=True)

只写模式：如果只需要写入大量数据而不读取现有内容，使用只写模式。

wb = Workbook(write_only=True)
ws = wb.create_sheet()
for row in data:
    ws.append(row)

批量操作：尽量减少单个单元格操作，使用批量写入方法。
禁用计算：在写入大量公式时，临时禁用自动计算。

wb = load_workbook('file.xlsx', data_only=False)
wb.calculation = False  # ... 写入公式 ...
wb.calculation = True
wb.save('file.xlsx')

使用 pandas 处理大数据：对于数据操作，优先使用 pandas，它比 openpyxl 的单元格操作高效得多。
内存优化：处理完数据后及时删除不需要的变量，特别是大型 DataFrame 和工作簿对象。

del large_df
del wb

五、实战应用案例

5.1 财务报表自动化

import pandas as pd
from openpyxl import load_workbook
from openpyxl.styles import Font, Alignment, numbers
from datetime import datetime

def generate_financial_report(input_path, output_path):
    # 1. 使用 pandas 读取和处理数据
    df = pd.read_excel(input_path, sheet_name='Transactions')
    # 数据清洗
    df = df.dropna(subset=['Amount'])
    df['Date'] = pd.to_datetime(df['Date'])
    df['Month'] = df['Date'].dt.to_period('M')
    
    # 分类汇总
    income = df[df['Type'] == 'Income'].groupby('Month')['Amount'].sum()
    expense = df[df['Type'] == 'Expense'].groupby('Month')['Amount'].sum()
    profit = income - expense
    
    # 2. 准备报表数据
    report_data = pd.DataFrame({
        'Month': income.index.astype(str),
        'Income': income.values,
        'Expense': expense.values,
        'Profit': profit.values
    })
    
    # 3. 加载模板文件
    wb = load_workbook('financial_report_template.xlsx')
    ws = wb['Report']
    
    # 4. 清空旧数据 (保留格式)
    for row in ws.iter_rows(min_row=5, max_row=100, min_col=1, max_col=4):
        for cell in row:
            cell.value = None
    
    # 5. 写入新数据
    for i, row in report_data.iterrows():
        ws.cell(row=5+i, column=1, value=row['Month'])
        ws.cell(row=5+i, column=2, value=row['Income'])
        ws.cell(row=5+i, column=3, value=row['Expense'])
        ws.cell(row=5+i, column=4, value=row['Profit'])
    
    # 6. 设置数字格式
    for row in ws.iter_rows(min_row=5, max_row=5+len(report_data), min_col=2, max_col=4):
        for cell in row:
            cell.number_format = numbers.FORMAT_CURRENCY_USD_SIMPLE
    
    # 7. 更新汇总信息
    last_row = 5 + len(report_data) - 1
    ws['B1'] = f"财务报告 - {datetime.now().strftime('%Y-%m-%d')}"
    ws['B2'] = f"数据期间：{report_data['Month'].iloc[0]} 至 {report_data['Month'].iloc[-1]}"
    ws[f'B{last_row+2}'] = "总计:"
    ws[f'C{last_row+2}'] = f"=SUM(C5:C{last_row})"
    ws[f'D{last_row+2}'] = f"=SUM(D5:D{last_row})"
    ws[f'E{last_row+2}'] = f"=SUM(E5:E{last_row})"
    
    # 8. 设置汇总行格式
    for cell in ws[f'B{last_row+2}':f'E{last_row+2}'][0]:
        cell.font = Font(bold=True)
        if cell.column in [3, 4, 5]:
            cell.number_format = numbers.FORMAT_CURRENCY_USD_SIMPLE
    
    # 9. 保存报告
    wb.save(output_path)
    print(f"财务报表已生成：{output_path}")

# 使用示例
generate_financial_report('transactions.xlsx', 'financial_report_Q1.xlsx')

一、Excel 处理在数据分析中的重要性

二、openpyxl 基础与核心功能

2.1 openpyxl 简介与安装

安装 openpyxl 非常简单，使用 pip 命令即可：

pip install openpyxl

如果需要处理图表，还需要安装额外的依赖：

pip install openpyxl[charts]

2.2 工作簿与工作表的基本操作

创建新工作簿

from openpyxl import Workbook

# 创建一个新工作簿
wb = Workbook()
# 获取活动的工作表
ws = wb.active
# 设置工作表标题
ws.title = "第一个工作表"
# 创建新的工作表
ws1 = wb.create_sheet("第二个工作表")
# 默认插入到最后
ws2 = wb.create_sheet("第三个工作表", 0)
# 插入到第一个位置
# 保存工作簿
wb.save("新工作簿.xlsx")

打开已有工作簿

from openpyxl import load_workbook

# 打开一个已存在的工作簿
wb = load_workbook('example.xlsx')
# 获取所有工作表名称
print(wb.sheetnames)
# 通过名称获取工作表
ws = wb['Sheet1']
# 检查工作表是否存在
if 'Sheet1' in wb.sheetnames:
    print("Sheet1 存在")

工作表操作

# 复制工作表
source = wb['Sheet1']
target = wb.copy_worksheet(source)
target.title = "Sheet1 的副本"
# 删除工作表
del wb['Sheet1 的副本']
# 遍历所有工作表
for sheet in wb:
    print(sheet.title)

2.3 单元格操作详解

基本单元格操作

# 获取单元格
cell = ws['A1']
# 写入值
ws['A1'] = "Hello"
ws['B1'] = "World"
# 使用单元格坐标
ws.cell(row=1, column=3, value="!")
# 读取值
print(ws['A1'].value)  # 输出：Hello
# 单元格坐标
print(cell.row, cell.column)  # 输出：1, 1
print(cell.coordinate)  # 输出：A1

批量操作单元格

# 批量写入数据
for row in range(1, 6):
    for col in range(1, 5):
        ws.cell(row=row, column=col, value=f"R{row}C{col}")

# 批量读取数据
for row in ws.iter_rows(min_row=1, max_row=3, min_col=1, max_col=3):
    for cell in row:
        print(cell.value, end="\t")
    print()

# 使用 values_only 参数只获取值
for row in ws.iter_rows(values_only=True):
    print(row)

特殊单元格操作

# 合并单元格
ws.merge_cells('A1:D1')
ws['A1'] = "合并的标题"
# 取消合并
ws.unmerge_cells('A1:D1')
# 设置行高和列宽
ws.row_dimensions[1].height = 30
ws.column_dimensions['A'].width = 20
# 隐藏行列
ws.column_dimensions['B'].hidden = True
ws.row_dimensions[2].hidden = True

2.4 样式与格式设置

字体样式

from openpyxl.styles import Font, Color

# 设置字体
bold_font = Font(name='Arial', size=12, bold=True, italic=False, color='FF0000')
ws['A1'].font = bold_font

# 修改现有单元格字体
for row in ws.iter_rows(min_row=2, max_row=5):
    for cell in row:
        cell.font = Font(size=10, color=Color(rgb='0000FF'))

对齐方式

from openpyxl.styles import Alignment

# 设置对齐方式
center_aligned = Alignment(horizontal='center', vertical='center', wrap_text=True)
ws['A1'].alignment = center_aligned

# 应用到范围
for row in ws.iter_rows(min_row=1, max_row=10):
    for cell in row:
        cell.alignment = Alignment(horizontal='center')

边框设置

from openpyxl.styles import Border, Side

# 定义边框样式
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=1, max_row=5):
    for cell in row:
        cell.border = thin_border

填充颜色

from openpyxl.styles import PatternFill, GradientFill

# 纯色填充
yellow_fill = PatternFill(start_color='FFFF00', end_color='FFFF00', fill_type='solid')
ws['A1'].fill = yellow_fill

# 渐变填充
gradient_fill = GradientFill(stop=('FFFFFF','0000FF'))
ws['B1'].fill = gradient_fill

数字格式

from openpyxl.styles import numbers

# 设置数字格式
ws['C1'].value = 3.1415926
ws['C1'].number_format = numbers.FORMAT_NUMBER_00  # 显示两位小数

# 自定义格式
ws['D1'].value = 0.85
ws['D1'].number_format = '0.00%'  # 显示为百分比

2.5 公式与计算

# 写入公式
ws['E1'] = '=SUM(A1:D1)'
ws['E2'] = '=AVERAGE(A2:D2)'
ws['E3'] = '=IF(A3>B3, "A 大", "B 大")'

# 读取公式
print(ws['E1'].value)  # 输出：=SUM(A1:D1)

# 计算数据
ws['F1'] = '结果'
ws['F2'] = '=E2*100'

2.6 图表与图像操作

创建图表

from openpyxl.chart import BarChart, Reference

# 准备数据
for i in range(1, 6):
    ws[f'A{i}'] = i
    ws[f'B{i}'] = i * i

# 创建柱状图
chart = BarChart()
data = Reference(ws, min_col=2, min_row=1, max_row=5)
categories = Reference(ws, min_col=1, min_row=2, max_row=5)
chart.add_data(data, titles_from_data=True)
chart.set_categories(categories)

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

插入图像

from openpyxl.drawing.image import Image

# 插入图像
img = Image('logo.png')
ws.add_image(img, 'A10')

# 调整图像大小
img.width = 100
img.height = 100

2.7 高级功能

数据验证

from openpyxl.worksheet.datavalidation import DataValidation

# 创建数据验证
dv = DataValidation(type="list", formula1='"男，女"', allow_blank=True)
dv.add('A1:A10')  # 应用到 A1:A10 范围
ws.add_data_validation(dv)

# 数字范围验证
dv_num = DataValidation(type="whole", operator="between", formula1=1, formula2=100)
dv_num.error = "输入必须在 1 到 100 之间"
dv_num.add('B1:B10')
ws.add_data_validation(dv_num)

条件格式

from openpyxl.formatting.rule import ColorScaleRule, FormulaRule

# 色阶条件格式
color_scale_rule = ColorScaleRule(start_type='min', start_color='FF0000', mid_type='percentile', mid_value=50, mid_color='FFFF00', end_type='max', end_color='00FF00')
ws.conditional_formatting.add('C1:C10', color_scale_rule)

# 公式条件格式
formula_rule = FormulaRule(formula=['ISBLANK(C1)'], stopIfTrue=True, font=Font(color='FF0000'))
ws.conditional_formatting.add('C1:C10', formula_rule)

保护工作表

from openpyxl.styles import Protection

# 保护工作表
ws.protection.sheet = True
ws.protection.password = 'password'
ws.protection.enable()

# 解锁特定单元格
for row in ws.iter_rows(min_row=1, max_row=5):
    for cell in row:
        cell.protection = Protection(locked=False)

冻结窗格

# 冻结第一行和第一列
ws.freeze_panes = 'B2'
# 解冻
ws.freeze_panes = None

三、pandas 基础与核心功能

3.1 pandas 简介与安装

安装 pandas 及其依赖：

pip install pandas openpyxl xlrd

注意：xlrd 库用于读取旧版 Excel 文件 (.xls)，而 openpyxl 则用于处理.xlsx 文件。

3.2 DataFrame 基础

DataFrame 是 pandas 的核心数据结构，可以看作是一个二维表格，类似于 Excel 工作表。

创建 DataFrame

import pandas as pd

# 从字典创建
data = {'姓名': ['张三', '李四', '王五'], '年龄': [25, 30, 35], '城市': ['北京', '上海', '广州']}
df = pd.DataFrame(data)

# 从列表创建
data = [{'姓名': '张三', '年龄': 25, '城市': '北京'}, {'姓名': '李四', '年龄': 30, '城市': '上海'}, {'姓名': '王五', '年龄': 35, '城市': '广州'}]
df = pd.DataFrame(data)

# 显示 DataFrame
print(df)

基本属性

# 查看前几行
print(df.head(2))
# 查看后几行
print(df.tail(1))
# 查看形状
print(df.shape)  # 输出：(3, 3)
# 查看列名
print(df.columns)  # 输出：Index(['姓名', '年龄', '城市'], dtype='object')
# 查看索引
print(df.index)  # 输出：RangeIndex(start=0, stop=3, step=1)
# 查看数据类型
print(df.dtypes)

数据选择

# 选择列
print(df['姓名'])
# 选择单列
print(df[['姓名', '年龄']])
# 选择多列
# 选择行
print(df.iloc[0])  # 通过位置选择
print(df.loc[0])   # 通过索引选择
print(df[1:3])     # 切片选择
# 条件选择
print(df[df['年龄'] > 28])
print(df[(df['年龄'] > 25) & (df['城市'] == '上海')])

数据修改

# 添加列
df['性别'] = ['男', '女', '男']
# 修改值
df.loc[0, '年龄'] = 26
df['年龄'] = df['年龄'] + 1
# 删除列
df = df.drop('性别', axis=1)
# 删除行
df = df.drop(0, axis=0)

3.3 数据导入与导出

读取 Excel 文件

# 读取整个 Excel 文件
df = pd.read_excel('data.xlsx', sheet_name='Sheet1')
# 读取指定范围
df = pd.read_excel('data.xlsx', sheet_name='Sheet1', usecols='A:C', nrows=10)
# 读取多个工作表
with pd.ExcelFile('data.xlsx') as xls:
    df1 = pd.read_excel(xls, 'Sheet1')
    df2 = pd.read_excel(xls, 'Sheet2')
# 处理缺失值
df = pd.read_excel('data.xlsx', na_values=['NA', 'N/A', '缺失'])

写入 Excel 文件

# 写入单个 DataFrame
df.to_excel('output.xlsx', sheet_name='数据', index=False)
# 写入多个 DataFrame
with pd.ExcelWriter('output.xlsx') as writer:
    df1.to_excel(writer, sheet_name='Sheet1')
    df2.to_excel(writer, sheet_name='Sheet2')
# 追加模式写入
with pd.ExcelWriter('output.xlsx', mode='a') as writer:
    df3.to_excel(writer, sheet_name='Sheet3')
# 设置格式
df.to_excel('output.xlsx', sheet_name='数据', index=False, float_format="%.2f", freeze_panes=(1, 0))  # 冻结首行

其他格式支持

# CSV 文件
df.to_csv('data.csv', index=False)
df = pd.read_csv('data.csv')
# JSON
df.to_json('data.json', orient='records')
df = pd.read_json('data.json')
# SQL 数据库
from sqlalchemy import create_engine
engine = create_engine('sqlite:///data.db')
df.to_sql('table_name', engine, if_exists='replace')
df = pd.read_sql('SELECT * FROM table_name', engine)

3.4 数据清洗与预处理

处理缺失值

# 检测缺失值
print(df.isnull().sum())
# 删除缺失值
df_cleaned = df.dropna()  # 删除任何包含缺失值的行
df_cleaned = df.dropna(subset=['年龄'])  # 只删除年龄列有缺失的行
# 填充缺失值
df_filled = df.fillna(0)  # 用 0 填充
df_filled = df.fillna(df.mean())  # 用均值填充数值列
df_filled = df.fillna(method='ffill')  # 用前一个值填充

处理重复值

# 检测重复行
print(df.duplicated().sum())
# 删除重复行
df_unique = df.drop_duplicates()
# 基于某些列删除重复
df_unique = df.drop_duplicates(subset=['姓名', '城市'])

数据类型转换

# 查看数据类型
print(df.dtypes)
# 转换数据类型
df['年龄'] = df['年龄'].astype('float64')
df['日期'] = pd.to_datetime(df['日期'])
# 分类数据
df['城市'] = df['城市'].astype('category')

字符串操作

# 字符串方法
df['姓名'] = df['姓名'].str.upper()  # 转为大写
df['城市'] = df['城市'].str.replace('京', '都')  # 替换
# 提取信息
df['姓氏'] = df['姓名'].str[0]  # 提取第一个字符
df['名字'] = df['姓名'].str[1:]  # 提取第二个字符之后
# 拆分列
df[['姓', '名']] = df['姓名'].str.split(expand=True)

日期处理

# 解析日期
df['日期'] = pd.to_datetime(df['日期'], format='%Y-%m-%d')
# 提取日期部分
df['年'] = df['日期'].dt.year
df['月'] = df['日期'].dt.month
df['日'] = df['日期'].dt.day
df['星期'] = df['日期'].dt.day_name()
# 日期运算
df['年龄天数'] = (pd.to_datetime('today') - df['出生日期']).dt.days
df['年龄'] = df['年龄天数'] // 365

3.5 数据转换与计算

应用函数

# 应用简单函数
df['年龄加 10'] = df['年龄'].apply(lambda x: x + 10)

# 应用复杂函数
def age_group(age):
    if age < 20:
        return '少年'
    elif age < 40:
        return '青年'
    else:
        return '中年'

df['年龄段'] = df['年龄'].apply(age_group)

# 向量化操作
df['BMI'] = df['体重'] / (df['身高'] / 100) ** 2

分组聚合

# 基本分组
grouped = df.groupby('城市')
# 聚合函数
print(grouped['年龄'].mean())  # 每个城市的平均年龄
print(grouped.agg({'年龄': ['mean', 'min', 'max'], '收入': 'sum'}))
# 多级分组
grouped = df.groupby(['城市', '性别'])
print(grouped['年龄'].mean())

数据透视表

# 简单透视表
pivot = pd.pivot_table(df, values='年龄', index='城市', aggfunc='mean')
# 复杂透视表
pivot = pd.pivot_table(df, values=['年龄', '收入'], index=['城市', '性别'], columns=['教育程度'], aggfunc={'年龄': 'mean', '收入': ['sum', 'count']}, fill_value=0, margins=True)

合并与连接

# 合并两个 DataFrame
df1 = pd.DataFrame({'key': ['A', 'B', 'C'], 'value': [1, 2, 3]})
df2 = pd.DataFrame({'key': ['A', 'B', 'D'], 'value': [4, 5, 6]})
# 内连接
pd.merge(df1, df2, on='key', how='inner')
# 左连接
pd.merge(df1, df2, on='key', how='left')
# 外连接
pd.merge(df1, df2, on='key', how='outer')
# 纵向合并
pd.concat([df1, df2], axis=0)

3.6 高级数据分析功能

时间序列分析

import numpy as np

# 创建时间序列
date_rng = pd.date_range(start='1/1/2020', end='1/10/2020', freq='D')
df = pd.DataFrame(date_rng, columns=['date'])
df['data'] = np.random.randint(0, 100, size=(len(date_rng)))

# 设置为索引
df = df.set_index('date')
# 重采样
df.resample('W').mean()  # 按周平均
df.resample('M').sum()   # 按月求和
# 滚动窗口
df.rolling(window=3).mean()  # 3 天移动平均

统计函数

# 描述性统计
print(df.describe())
# 相关性
print(df.corr())
# 协方差
print(df.cov())
# 唯一值计数
print(df['城市'].value_counts())
# 交叉表
pd.crosstab(df['城市'], df['性别'])

可视化

import matplotlib.pyplot as plt

# 线图
df.plot.line()
# 柱状图
df.plot.bar()
# 直方图
df['年龄'].plot.hist(bins=20)
# 箱线图
df.plot.box()
# 散点图
df.plot.scatter(x='年龄', y='收入')
plt.show()

四、openpyxl 与 pandas 的协同使用

4.1 结合使用的优势与场景

openpyxl 和 pandas 各有优势，在实际工作中结合使用可以发挥更大威力：

数据流转：pandas 适合数据处理和分析，openpyxl 适合精细控制 Excel 格式，两者结合可以实现"pandas 处理数据→openpyxl 调整格式"的完整流程。
复杂报表：对于需要复杂格式的报表，可以先用 pandas 生成数据，再用 openpyxl 添加图表、条件格式等高级功能。
模板填充：使用 Excel 文件作为模板，pandas 填充数据，openpyxl 保持原有格式和公式。
大数据处理：当 Excel 文件很大时，可以先用 pandas 高效读取和处理数据，再用 openpyxl 进行必要的格式调整。

4.2 pandas DataFrame 与 openpyxl 的转换

DataFrame 写入 Excel 并保持格式

from openpyxl import load_workbook
from openpyxl.utils.dataframe import dataframe_to_rows
import pandas as pd

# 创建 DataFrame
df = pd.DataFrame({'产品': ['A', 'B', 'C', 'D'], '销量': [120, 150, 90, 200], '单价': [25.5, 32.0, 18.0, 40.5]})

# 加载现有工作簿或创建新工作簿
try:
    wb = load_workbook('report.xlsx')
except FileNotFoundError:
    wb = Workbook()

# 选择或创建工作表
if '销售报告' in wb.sheetnames:
    ws = wb['销售报告']
else:
    ws = wb.create_sheet('销售报告')

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

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

# 添加公式
ws['E1'] = '销售额'
for row in range(2, ws.max_row + 1):
    ws[f'E{row}'] = f'=B{row}*C{row}'

# 设置格式
header_font = Font(bold=True, color='FFFFFF')
header_fill = PatternFill(start_color='4F81BD', end_color='4F81BD', fill_type='solid')
for cell in ws[1]:
    cell.font = header_font
    cell.fill = header_fill

# 保存工作簿
wb.save('report.xlsx')

从 openpyxl 读取数据到 DataFrame

from openpyxl import load_workbook

# 加载工作簿
wb = load_workbook('data.xlsx')
ws = wb.active

# 将工作表数据转换为列表
data = []
for row in ws.iter_rows(values_only=True):
    data.append(row)

# 转换为 DataFrame
df = pd.DataFrame(data[1:], columns=data[0])

# 处理数据
df['日期'] = pd.to_datetime(df['日期'])
df['销售额'] = df['数量'] * df['单价']
print(df.head())

4.3 复杂报表生成案例

import pandas as pd
from openpyxl import Workbook
from openpyxl.styles import Font, Alignment, Border, Side, PatternFill
from openpyxl.chart import BarChart, Reference
from openpyxl.drawing.image import Image
from datetime import datetime

# 1. 准备数据
sales_data = {'月份': ['1 月', '2 月', '3 月', '4 月', '5 月', '6 月'], '产品 A': [1200, 1500, 1800, 2100, 2400, 2700], '产品 B': [800, 950, 1100, 1250, 1400, 1550], '产品 C': [500, 600, 700, 800, 900, 1000]}
df = pd.DataFrame(sales_data)

# 2. 创建 Excel 工作簿
wb = Workbook()
ws = wb.active
ws.title = "销售报告"

# 3. 写入标题
ws['A1'] = "2023 年上半年销售报告"
ws['A1'].font = Font(size=16, bold=True, name='微软雅黑')
ws.merge_cells('A1:D1')

# 4. 写入数据
# 写入列标题
columns = list(df.columns)
for col_num, column_title in enumerate(columns, 1):
    cell = ws.cell(row=3, column=col_num, value=column_title)
    cell.font = Font(bold=True)
    cell.alignment = Alignment(horizontal='center')

# 写入数据
for row_num, row_data in enumerate(df.values, 4):
    for col_num, cell_value in enumerate(row_data, 1):
        ws.cell(row=row_num, column=col_num, value=cell_value)

# 5. 添加汇总行
last_row = ws.max_row + 1
ws.cell(row=last_row, column=1, value="总计").font = Font(bold=True)
for col_num in range(2, 5):
    col_letter = chr(64 + col_num)
    ws.cell(row=last_row, column=col_num, value=f"=SUM({col_letter}4:{col_letter}{last_row-1})")

# 6. 设置格式
# 设置边框
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=last_row, min_col=1, max_col=4):
    for cell in row:
        cell.border = thin_border
        if cell.row > 3 and cell.column > 1:  # 数据单元格
            cell.number_format = '#,##0'

# 设置对齐
for row in ws.iter_rows(min_row=3, max_row=last_row, min_col=1, max_col=4):
    for cell in row:
        cell.alignment = Alignment(horizontal='center')

# 7. 创建图表
chart = BarChart()
chart.type = "col"
chart.style = 10
chart.title = "产品销售趋势"
chart.y_axis.title = "销售额"
chart.x_axis.title = "月份"
data = Reference(ws, min_col=2, max_col=4, min_row=3, max_row=last_row-1)
categories = Reference(ws, min_col=1, min_row=4, max_row=last_row-1)
chart.add_data(data, titles_from_data=True)
chart.set_categories(categories)
ws.add_chart(chart, "F3")

# 8. 添加页脚
footer_row = last_row + 2
ws.cell(row=footer_row, column=1, value=f"报告生成时间：{datetime.now().strftime('%Y-%m-%d %H:%M:%S')}")

# 9. 调整列宽
ws.column_dimensions['A'].width = 12
ws.column_dimensions['B'].width = 12
ws.column_dimensions['C'].width = 12
ws.column_dimensions['D'].width = 12

# 10. 保存文件
filename = f"销售报告_{datetime.now().strftime('%Y%m%d')}.xlsx"
wb.save(filename)
print(f"报表已生成：{filename}")

4.4 模板填充技术

from openpyxl import load_workbook
from openpyxl.utils.dataframe import dataframe_to_rows
import pandas as pd

# 1. 加载模板文件
template_path = 'report_template.xlsx'
wb = load_workbook(template_path)
ws = wb['Data']

# 2. 准备数据
data = {'Region': ['North', 'South', 'East', 'West'], 'Q1': [1200, 1500, 1800, 900], 'Q2': [1300, 1600, 1900, 950], 'Q3': [1400, 1700, 2000, 1000], 'Q4': [1500, 1800, 2100, 1050]}
df = pd.DataFrame(data)

# 3. 清空模板中的数据区域 (保留格式)
for row in ws.iter_rows(min_row=3, max_row=100, min_col=1, max_col=6):
    for cell in row:
        cell.value = None

# 4. 写入新数据
for r_idx, row in enumerate(dataframe_to_rows(df, index=False, header=False), 3):
    for c_idx, value in enumerate(row, 1):
        ws.cell(row=r_idx, column=c_idx, value=value)

# 5. 更新公式 (假设模板中已有公式)
last_data_row = 3 + len(df) - 1
for row in range(3, last_data_row + 1):
    ws[f'F{row}'] = f'=SUM(B{row}:E{row})'

# 6. 更新汇总公式
ws['B20'] = f'=SUM(B3:B{last_data_row})'
ws['C20'] = f'=SUM(C3:C{last_data_row})'
ws['D20'] = f'=SUM(D3:D{last_data_row})'
ws['E20'] = f'=SUM(E3:E{last_data_row})'
ws['F20'] = f'=SUM(F3:F{last_data_row})'

# 7. 更新报告日期
ws['A1'] = f"销售报告 - {pd.Timestamp.today().strftime('%Y-%m-%d')}"

# 8. 保存新文件
output_path = 'quarterly_report.xlsx'
wb.save(output_path)
print(f"报告已生成：{output_path}")

4.5 性能优化技巧

当处理大型 Excel 文件时，性能可能成为问题。以下是一些优化技巧：

只读模式：如果只需要读取数据而不修改文件，使用只读模式可以显著提高速度。

wb = load_workbook('large_file.xlsx', read_only=True)

只写模式：如果只需要写入大量数据而不读取现有内容，使用只写模式。

wb = Workbook(write_only=True)
ws = wb.create_sheet()
for row in data:
    ws.append(row)

批量操作：尽量减少单个单元格操作，使用批量写入方法。
禁用计算：在写入大量公式时，临时禁用自动计算。

wb = load_workbook('file.xlsx', data_only=False)
wb.calculation = False  # ... 写入公式 ...
wb.calculation = True
wb.save('file.xlsx')

使用 pandas 处理大数据：对于数据操作，优先使用 pandas，它比 openpyxl 的单元格操作高效得多。
内存优化：处理完数据后及时删除不需要的变量，特别是大型 DataFrame 和工作簿对象。

del large_df
del wb

五、实战应用案例

5.1 财务报表自动化

import pandas as pd
from openpyxl import load_workbook
from openpyxl.styles import Font, Alignment, numbers
from datetime import datetime

def generate_financial_report(input_path, output_path):
    # 1. 使用 pandas 读取和处理数据
    df = pd.read_excel(input_path, sheet_name='Transactions')
    # 数据清洗
    df = df.dropna(subset=['Amount'])
    df['Date'] = pd.to_datetime(df['Date'])
    df['Month'] = df['Date'].dt.to_period('M')
    
    # 分类汇总
    income = df[df['Type'] == 'Income'].groupby('Month')['Amount'].sum()
    expense = df[df['Type'] == 'Expense'].groupby('Month')['Amount'].sum()
    profit = income - expense
    
    # 2. 准备报表数据
    report_data = pd.DataFrame({
        'Month': income.index.astype(str),
        'Income': income.values,
        'Expense': expense.values,
        'Profit': profit.values
    })
    
    # 3. 加载模板文件
    wb = load_workbook('financial_report_template.xlsx')
    ws = wb['Report']
    
    # 4. 清空旧数据 (保留格式)
    for row in ws.iter_rows(min_row=5, max_row=100, min_col=1, max_col=4):
        for cell in row:
            cell.value = None
    
    # 5. 写入新数据
    for i, row in report_data.iterrows():
        ws.cell(row=5+i, column=1, value=row['Month'])
        ws.cell(row=5+i, column=2, value=row['Income'])
        ws.cell(row=5+i, column=3, value=row['Expense'])
        ws.cell(row=5+i, column=4, value=row['Profit'])
    
    # 6. 设置数字格式
    for row in ws.iter_rows(min_row=5, max_row=5+len(report_data), min_col=2, max_col=4):
        for cell in row:
            cell.number_format = numbers.FORMAT_CURRENCY_USD_SIMPLE
    
    # 7. 更新汇总信息
    last_row = 5 + len(report_data) - 1
    ws['B1'] = f"财务报告 - {datetime.now().strftime('%Y-%m-%d')}"
    ws['B2'] = f"数据期间：{report_data['Month'].iloc[0]} 至 {report_data['Month'].iloc[-1]}"
    ws[f'B{last_row+2}'] = "总计:"
    ws[f'C{last_row+2}'] = f"=SUM(C5:C{last_row})"
    ws[f'D{last_row+2}'] = f"=SUM(D5:D{last_row})"
    ws[f'E{last_row+2}'] = f"=SUM(E5:E{last_row})"
    
    # 8. 设置汇总行格式
    for cell in ws[f'B{last_row+2}':f'E{last_row+2}'][0]:
        cell.font = Font(bold=True)
        if cell.column in [3, 4, 5]:
            cell.number_format = numbers.FORMAT_CURRENCY_USD_SIMPLE
    
    # 9. 保存报告
    wb.save(output_path)
    print(f"财务报表已生成：{output_path}")

# 使用示例
generate_financial_report('transactions.xlsx', 'financial_report_Q1.xlsx')