Python 处理 Excel：openpyxl 与 pandas 实战指南

工作表操作

复制、删除或遍历工作表也是常见需求，openpyxl 提供了相应的方法。

# 复制工作表
source = wb['Sheet1']
target = wb.copy_worksheet(source)
target.title = "Sheet1 的副本"

# 删除工作表
del wb['Sheet1 的副本']

# 遍历所有工作表
for sheet in wb:
    print(sheet.title)

单元格操作详解

基本单元格操作

单元格是数据处理的最小单位。可以通过坐标字符串（如'A1'）或直接指定行列号来访问。

# 获取单元格
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

批量操作单元格

当需要处理大量数据时，循环单个单元格效率较低，建议使用 iter_rows 进行批量迭代。

# 批量写入数据
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

样式与格式设置

字体样式

通过 Font 类可以自定义字体名称、大小、粗细和颜色。

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'))

对齐方式

Alignment 类用于控制文本的对齐模式，包括水平、垂直和对齐换行。

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')

边框设置

Border 和 Side 类允许我们定义线条的样式，使表格结构更清晰。

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

填充颜色

PatternFill 用于纯色填充，GradientFill 支持渐变效果。

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

数字格式

通过 number_format 属性可以控制数值的显示方式，例如保留小数位或显示百分比。

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%' # 显示为百分比

公式与计算

直接在单元格中写入 Excel 公式，openpyxl 会将其识别并保留。

# 写入公式
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'

图表与图像操作

创建图表

利用 BarChart 等类可以在 Excel 中嵌入图表，直观展示数据趋势。

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")

插入图像

可以将本地图片插入到工作表中，常用于添加 Logo 或水印。

from openpyxl.drawing.image import Image

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

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

高级功能

数据验证

DataValidation 类可以限制用户输入的内容，例如下拉列表或数值范围。

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)

保护工作表

防止他人意外修改关键数据。

# 保护工作表
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 基础与核心功能

pandas 简介与安装

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

安装 pandas 及其依赖：

pip install pandas openpyxl xlrd

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

DataFrame 基础

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

创建 DataFrame

可以从字典、列表等多种结构创建 DataFrame。

import pandas as pd

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

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

# 显示 DataFrame
print(df)

基本属性

了解 DataFrame 的形状、索引和列名是数据处理的第一步。

# 查看前几行
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)

数据选择

pandas 提供了丰富的索引方式，包括基于标签的 loc 和基于位置的 iloc。

# 选择列
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)

数据导入与导出

读取 Excel 文件

read_excel 是读取 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 文件

to_excel 方法将 DataFrame 保存回 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)) # 冻结首行

其他格式支持

pandas 不仅限于 Excel，还支持 CSV、JSON 和数据库。

# 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)

数据清洗与预处理

处理缺失值

缺失值是数据质量的大敌，dropna 和 fillna 是常用工具。

# 检测缺失值
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')

字符串操作

str 访问器提供了便捷的字符串处理方法。

# 字符串方法
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

数据转换与计算

应用函数

apply 方法允许我们对每一行或每一列应用自定义逻辑。

# 应用简单函数
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

分组聚合

groupby 是进行统计分析的核心方法。

# 基本分组
grouped = df.groupby('城市')

# 聚合函数
print(grouped['年龄'].mean()) # 每个城市的平均年龄
print(grouped.agg({'年龄': ['mean', 'min', 'max'], '收入': 'sum'}))

# 多级分组
grouped = df.groupby(['城市', '性别'])
print(grouped['年龄'].mean())

数据透视表

pivot_table 可以快速生成汇总报表。

# 简单透视表
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)

合并与连接

merge 和 concat 用于组合多个数据集。

# 合并两个 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)

高级数据分析功能

时间序列分析

pandas 内置了强大的时间序列处理能力。

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['性别'])

可视化

虽然 matplotlib/seaborn 更强大，但 pandas 自带的 plot 接口适合快速预览。

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 的协同使用

结合使用的优势与场景

在实际工作中，单纯使用某一个库往往难以满足所有需求。openpyxl 和 pandas 各有优势，结合使用可以发挥更大威力：

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

DataFrame 写入 Excel 并保持格式

这是最常见的协同场景：先用 pandas 算好数据，再写入带样式的 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:
    from openpyxl import Workbook
    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

有时候我们需要读取带有格式的原始数据，然后交给 pandas 分析。

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())

复杂报表生成案例

下面是一个综合案例，展示了如何生成包含标题、数据、汇总行、图表和页脚的完整销售报告。

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}")

模板填充技术

利用现有的 Excel 模板，仅更新数据区域，是维护标准化报表的高效方式。

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}")

性能优化技巧

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

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

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

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

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

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

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

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

del large_df
del wb

实战应用案例

财务报表自动化

下面是一个完整的财务报表生成脚本，展示了从数据清洗到最终输出的全流程。

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')