Python 深度挖掘：openpyxl 和 pandas 的使用详细指南（2026 最新版）

openpyxl 和 pandas 是 Python 数据处理领域的两大核心库，尤其在处理 Excel 文件和数据分析时常结合使用。

openpyxl：专注于 Excel 文件（.xlsx）的读写和操作，是纯 Python 实现，无需安装 Excel 软件。常用于自动化报表、数据导出等场景。
pandas：强大的数据分析库，以 DataFrame 为核心，处理结构化数据（如表格）。它可以无缝集成 openpyxl 来读写 Excel。

截至 2026 年，openpyxl 最新版为 3.2.x，pandas 为 2.3.x。两者结合使用，能高效处理大数据量的 Excel 操作。

本文从安装、基础使用、进阶功能、结合案例一步步深度讲解。所有代码基于 Python 3.10+ 测试，确保可运行。

一、安装与环境准备

pip install openpyxl pandas
# 如果需要绘图（pandas 常用）
pip install matplotlib seaborn

依赖：openpyxl 无外部依赖；pandas 依赖 NumPy（自动安装）。
测试安装：

  import openpyxl
  import pandas as pd
  print(openpyxl.__version__)  # 如 3.2.0
  print(pd.__version__)        # 如 2.3.0

二、openpyxl 详细使用（Excel 操作专家）

openpyxl 提供对 Excel 的完整控制，包括工作簿、工作表、单元格、公式、样式、图表等。

1. 基础概念

Workbook：Excel 文件（.xlsx）。
Worksheet：工作表（Sheet）。
Cell：单元格。

2. 创建与保存 Excel 文件

from openpyxl import Workbook

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

# 获取默认工作表（Sheet）
ws = wb.active
ws.title = "MySheet"  # 重命名

# 写入数据
ws['A1'] = "姓名"     # 单元格 A1
ws['B1'] = "年龄"
ws.cell(row=2, column=1, value="张三")  # 行2，列1
ws.cell(row=2, column=2, value=30)

# 追加行
ws.append(["李四", 25])

# 保存文件
wb.save("example.xlsx")

3. 读取 Excel 文件

from openpyxl import load_workbook

# 加载文件（只读模式提高性能）
wb = load_workbook("example.xlsx", read_only=True)

# 获取指定工作表
ws = wb["MySheet"]

# 读取单元格
print(ws['A1'].value)  # "姓名"

# 迭代行
for row in ws.iter_rows(min_row=2, max_row=3, min_col=1, max_col=2, values_only=True):
    print(row)  # 输出如：('张三', 30)

# 获取维度
print(ws.dimensions)  # 如 "A1:B3"

进阶读取：iter_rows(values_only=True) 返回元组，提高效率；read_only=True 适合大文件。

4. 公式与计算

ws['C1'] = "总分"
ws['C2'] = "=SUM(B2:B3)"  # 公式会自动计算

wb.save("example_with_formula.xlsx")

加载后，公式值可通过 data_only=True 获取计算结果（需先用 Excel 打开保存一次）。

5. 样式与格式

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

# 字体：加粗、红色
ws['A1'].font = Font(bold=True, color="FF0000")

# 填充：黄色背景
ws['B1'].fill = PatternFill(start_color="FFFF00", end_color="FFFF00", fill_type="solid")

# 对齐：居中
ws['C1'].alignment = Alignment(horizontal="center", vertical="center")

# 边框：细线
thin = Side(border_style="thin", color="000000")
ws['A1'].border = Border(top=thin, left=thin, right=thin, bottom=thin)

# 列宽/行高
ws.column_dimensions['A'].width = 15
ws.row_dimensions[1].height = 20

wb.save("styled.xlsx")

6. 图表插入

from openpyxl.chart import BarChart, Reference

# 数据准备
ws.append(["产品", "销量"])
ws.append(["苹果", 50])
ws.append(["香蕉", 30])

# 创建柱状图
chart = BarChart()
data = Reference(ws, min_col=2, min_row=2, max_row=3)
categories = Reference(ws, min_col=1, min_row=2, max_row=3)
chart.add_data(data)
chart.set_categories(categories)
chart.title = "销量图"

ws.add_chart(chart, "D1")  # 放置位置

wb.save("chart.xlsx")

7. 进阶功能：合并单元格、冻结窗格、过滤器

# 合并单元格
ws.merge_cells('A1:B1')

# 冻结首行
ws.freeze_panes = 'A2'

# 添加过滤器
ws.auto_filter.ref = "A1:B3"

wb.save("advanced.xlsx")

8. 处理大文件（性能优化）

用 read_only=True 和 write_only=True 模式。
批量操作：使用 ws.append() 而非逐个单元格写入。
对于超大文件（>10万行），考虑结合 pandas。

三、pandas 详细使用（数据分析神器）

pandas 以 DataFrame（表格）和 Series（列）为核心，擅长数据清洗、转换、分析、可视化。

1. 基础概念

DataFrame：二维表格，带标签的行/列。
Series：一维数组，带标签。

2. 创建 DataFrame

import pandas as pd
import numpy as np

# 从字典创建
data = {'姓名': ['张三', '李四', '王五'],
        '年龄': [30, 25, 28],
        '分数': [85.5, 92.0, 78.5]}
df = pd.DataFrame(data)

# 从列表创建
df2 = pd.DataFrame(np.random.rand(3, 2), columns=['A', 'B'], index=['row1', 'row2', 'row3'])

print(df)
# 输出：
#    姓名  年龄   分数
# 0  张三  30  85.5
# 1  李四  25  92.0
# 2  王五  28  78.5

3. 数据访问与操作

访问：

  print(df['姓名'])         # Series: 张三 李四 王五
  print(df.loc[0])          # 第一行
  print(df.iloc[:, 1:3])    # 列1到2（年龄、分数）
  print(df[df['年龄'] > 25]) # 过滤：年龄 > 25 的行

修改：

  df['奖金'] = df['分数'] * 10  # 新列：分数 * 10
  df.loc[0, '年龄'] = 31        # 修改特定值
  df = df.drop('奖金', axis=1)  # 删除列

统计：

  print(df.describe())  # 均值、标准差等
  print(df['分数'].mean())  # 平均分

4. 数据清洗与转换

缺失值：

  df.loc[1, '分数'] = np.nan
  df = df.fillna(0)            # 填 0
  df = df.dropna()             # 删含 NaN 行

分组聚合：

  grouped = df.groupby('年龄').agg({'分数': 'mean'})  # 按年龄分组，求分数平均

合并：

  df_other = pd.DataFrame({'姓名': ['张三', '赵六'], '城市': ['北京', '上海']})
  merged = pd.merge(df, df_other, on='姓名', how='left')  # 左连接

5. 读写文件（与 openpyxl 集成）

pandas 默认用 openpyxl 作为 Excel 引擎（engine=’openpyxl’）。

读 Excel：

  df = pd.read_excel("example.xlsx", sheet_name="MySheet", engine="openpyxl")

写 Excel：

  df.to_excel("output.xlsx", sheet_name="Data", index=False, engine="openpyxl")

  # 多工作表写入
  with pd.ExcelWriter("multi.xlsx", engine="openpyxl") as writer:
      df.to_excel(writer, sheet_name="Sheet1")
      df2.to_excel(writer, sheet_name="Sheet2")

大文件优化：用 pd.read_excel(usecols=['A:B'], nrows=1000) 指定列/行。

6. 可视化

df.plot(kind='bar', x='姓名', y='分数')  # 柱状图
df['分数'].hist(bins=10)                # 直方图

集成 Seaborn/Matplotlib 更美观。

7. 进阶：时间序列、透视表、多索引

时间序列：

  dates = pd.date_range('20260101', periods=5)
  ts_df = pd.DataFrame(np.random.randn(5), index=dates)
  ts_df.resample('D').mean()  # 按天重采样

透视表：

  pivot = pd.pivot_table(df, values='分数', index='姓名', aggfunc='mean')

四、openpyxl 与 pandas 结合实战案例

场景：读取 Excel 数据，用 pandas 分析后，写入新 Excel 并添加样式/图表。

import pandas as pd
from openpyxl import load_workbook
from openpyxl.chart import LineChart, Reference

# 1. 用 pandas 读入
df = pd.read_excel("input.xlsx", engine="openpyxl")

# 2. pandas 分析
df['总分'] = df['数学'] + df['英语']
summary = df.describe()

# 3. 写入新文件（pandas 先写）
df.to_excel("output.xlsx", index=False, engine="openpyxl")
summary.to_excel("output.xlsx", sheet_name="Summary", engine="openpyxl")

# 4. 用 openpyxl 添加样式和图表
wb = load_workbook("output.xlsx")
ws = wb.active  # 默认 Sheet

# 添加样式
for cell in ws["A1":"D1"]:
    for c in cell:
        c.font = Font(bold=True)

# 添加图表
chart = LineChart()
data = Reference(ws, min_col=2, min_row=1, max_col=4, max_row=len(df)+1)
chart.add_data(data, titles_from_data=True)
ws.add_chart(chart, "F1")

wb.save("output.xlsx")

优势：pandas 负责数据逻辑，openpyxl 负责 Excel 细节（如样式、图表）。

五、性能优化与常见坑

大文件：pandas 用 pd.read_excel(chunksize=10000) 分块读；openpyxl 用 write_only 模式。
常见坑：
openpyxl 不支持 .xls（旧格式），用 xlrd 或 pandas 转换。
pandas 读 Excel 时，指定 dtype 避免类型推断错误。
公式：pandas 写公式需用 =SUM(...)，但计算需加载 data_only。
资源消耗：pandas DataFrame 内存大，用 df.info() 检查；openpyxl 适合逐行处理。

六、总结与进阶建议

openpyxl：适合纯 Excel 操作，深度定制样式/图表。
pandas：数据分析主力，结合 openpyxl 读写 Excel 更强大。
结合使用：80% 场景 pandas 足够，复杂样式再用 openpyxl 后处理。

进阶资源：

官方文档：openpyxl.readthedocs.io / pandas.pydata.org
书籍：《Python 数据分析》（Wes McKinney）
社区：Stack Overflow / Pandas GitHub

Python深度挖掘:openpyxl和pandas的使用详细

Python 深度挖掘：openpyxl 和 pandas 的使用详细指南（2026 最新版）

一、安装与环境准备

二、openpyxl 详细使用（Excel 操作专家）

1. 基础概念

2. 创建与保存 Excel 文件

3. 读取 Excel 文件

4. 公式与计算

5. 样式与格式

6. 图表插入

7. 进阶功能：合并单元格、冻结窗格、过滤器

8. 处理大文件（性能优化）

三、pandas 详细使用（数据分析神器）

1. 基础概念

2. 创建 DataFrame

3. 数据访问与操作

4. 数据清洗与转换

5. 读写文件（与 openpyxl 集成）

6. 可视化

7. 进阶：时间序列、透视表、多索引

四、openpyxl 与 pandas 结合实战案例

五、性能优化与常见坑

六、总结与进阶建议

likuolei

发表回复取消回复

归档

分类

2026 年 2 月
一	二	三	四	五	六	日
						1
2	3	4	5	6	7	8
9	10	11	12	13	14	15
16	17	18	19	20	21	22
23	24	25	26	27	28

Python 深度挖掘：openpyxl 和 pandas 的使用详细指南（2026 最新版）

一、安装与环境准备

二、openpyxl 详细使用（Excel 操作专家）

1. 基础概念

2. 创建与保存 Excel 文件

3. 读取 Excel 文件

4. 公式与计算

5. 样式与格式

6. 图表插入

7. 进阶功能：合并单元格、冻结窗格、过滤器

8. 处理大文件（性能优化）

三、pandas 详细使用（数据分析神器）

1. 基础概念

2. 创建 DataFrame

3. 数据访问与操作

4. 数据清洗与转换

5. 读写文件（与 openpyxl 集成）

6. 可视化

7. 进阶：时间序列、透视表、多索引

四、openpyxl 与 pandas 结合实战案例

五、性能优化与常见坑

六、总结与进阶建议

likuolei

发表回复 取消回复

相关文章

发表回复取消回复