Python Excel 文件处理：openpyxl 与 pandas 库完全指南

在数据处理和分析过程中，Excel 文件是最常见的数据存储格式之一。Python 提供了多个库来处理 Excel 文件，其中 openpyxl 和 pandas 是最常用的两个库。它们各自有独特的优势，适用于不同的需求。本文将详细介绍如何使用这两个库来处理 Excel 文件。

一、使用 openpyxl 处理 Excel 文件

openpyxl 是一个纯 Python 编写的库，用于读取、写入、修改 Excel 文件（.xlsx 格式）。它支持更多 Excel 文件特性，如合并单元格、格式化、绘制图表等。

1. 安装 openpyxl

在开始之前，需要先安装 openpyxl 库：

pip install openpyxl

2. 读取 Excel 文件

openpyxl 可以用于打开并读取 Excel 文件的内容。下面是如何读取 Excel 文件的基本示例：

import openpyxl

# 加载工作簿
workbook = openpyxl.load_workbook('example.xlsx')

# 选择活动工作表
sheet = workbook.active

# 读取特定单元格的值
print(sheet['A1'].value)

# 或者通过行列索引访问
print(sheet.cell(row=1, column=1).value)

3. 写入数据到 Excel 文件

我们可以在 Excel 文件中写入数据或更新现有数据：

# 写入数据到特定单元格
sheet['A2'] = 'Hello, Openpyxl!'

# 保存更改到新的 Excel 文件
workbook.save('output.xlsx')

4. 遍历所有行和列

你可以遍历 Excel 工作表的所有行和列：

for row in sheet.iter_rows(min_row=1, max_row=3, min_col=1, max_col=3):
    for cell in row:
        print(cell.value, end=" ")
    print()

5. 创建新的工作表

你可以创建新的工作表，并添加数据：

# 创建一个新的工作表
new_sheet = workbook.create_sheet('NewSheet')

# 向新工作表写入数据
new_sheet['A1'] = 'New Data'

# 保存工作簿
workbook.save('output_with_new_sheet.xlsx')

6. 格式化和样式设置

openpyxl 还支持对单元格进行样式设置，例如字体、颜色和对齐方式：

from openpyxl.styles import Font, Color, Alignment

# 设置字体加粗和斜体
sheet['A2'].font = Font(bold=True, italic=True)

# 设置单元格背景颜色
sheet['A2'].fill = openpyxl.styles.PatternFill(start_color="FFFF00", end_color="FFFF00", fill_type="solid")

# 设置文本居中对齐
sheet['A2'].alignment = Alignment(horizontal='center', vertical='center')

# 保存更改
workbook.save('styled_output.xlsx')

7. 合并单元格

你可以合并单元格来创建跨越多个列或行的标题或数据块：

# 合并 A1 到 D1 的单元格
sheet.merge_cells('A1:D1')

# 设置合并单元格的内容
sheet['A1'] = 'Merged Cells'

# 保存更改
workbook.save('merged_cells_output.xlsx')

二、使用 pandas 处理 Excel 文件

pandas 是一个强大的数据分析库，具有极好的处理表格数据的能力。它能够处理 .xls 和 .xlsx 格式的文件，并且在读取和写入 Excel 数据时表现非常高效。

1. 安装 pandas 和 openpyxl

在使用 pandas 读取和写入 Excel 文件时，推荐安装 openpyxl 作为 Excel 文件的支持引擎：

pip install pandas openpyxl

2. 读取 Excel 文件

pandas 提供了 read_excel() 函数来读取 Excel 文件并将其转换为 DataFrame（数据框）。DataFrame 是 pandas中的核心数据结构，方便进行数据分析。

import pandas as pd

# 读取 Excel 文件
df = pd.read_excel('example.xlsx')

# 查看 DataFrame
print(df.head())  # 显示前 5 行

3. 读取特定工作表

如果 Excel 文件包含多个工作表，你可以指定要读取的工作表：

# 读取指定工作表
df = pd.read_excel('example.xlsx', sheet_name='Sheet1')

# 或者按索引指定工作表
df = pd.read_excel('example.xlsx', sheet_name=0)  # 读取第一个工作表

4. 写入数据到 Excel 文件

将数据框写入 Excel 文件时，可以使用 to_excel() 方法。你可以选择是否保存为新的工作表或覆盖现有工作表。

# 创建一个新的 DataFrame
df_new = pd.DataFrame({
    'A': [1, 2, 3],
    'B': ['X', 'Y', 'Z']
})

# 将 DataFrame 写入 Excel 文件
df_new.to_excel('output.xlsx', index=False)  # `index=False` 防止保存索引列

5. 数据筛选与处理

pandas 提供了强大的数据处理能力，可以很容易地对 Excel 数据进行筛选、排序、分组等操作：

# 筛选数据
filtered_df = df[df['ColumnA'] > 50]

# 排序
sorted_df = df.sort_values(by='ColumnB', ascending=False)

# 分组并聚合
grouped_df = df.groupby('Category').agg({'Value': 'sum'})

6. 修改数据

你可以直接修改 DataFrame 中的值：

# 修改特定单元格的值
df.loc[0, 'ColumnA'] = 100

# 修改整列
df['ColumnB'] = df['ColumnB'] * 2

7. 保存 DataFrame 到多个工作表

pandas 允许你将多个 DataFrame 写入同一个 Excel 文件中的多个工作表：

# 创建 ExcelWriter 对象
with pd.ExcelWriter('output_multiple_sheets.xlsx') as writer:
    df.to_excel(writer, sheet_name='Sheet1', index=False)
    df_new.to_excel(writer, sheet_name='Sheet2', index=False)

三、openpyxl 和 pandas 的比较与使用场景

• openpyxl：适用于更复杂的 Excel 文件操作，如格式化、图表生成、单元格合并等。它能精确控制 Excel 文件的结构和格式。

  • 优势：可以进行细粒度的操作，如样式、合并单元格、图表等。

  • 场景：生成或修改带有格式、样式或其他 Excel 特性（如图表、过滤器）的文件。

• pandas：主要用于数据处理和分析，能够高效地读取、操作和写入表格数据。它的 DataFrame 结构使得数据操作变得非常简便，尤其适合进行批量数据处理和分析。

  • 优势：高效的数据操作、支持大量数据操作、与 numpy 配合使用、简洁的语法。

  • 场景：数据分析、批量数据处理、数据清洗等。

总结

• openpyxl 适合处理 Excel 文件中的格式化操作和结构性操作（如合并单元格、图表、单元格样式等），而 pandas 适合快速的数据分析和表格数据处理。

• 如果你需要对 Excel 文件的内容进行复杂的分析、过滤、聚合等操作，建议使用 pandas。

• 如果你需要格式化 Excel 文件、设置样式或创建图表等，建议使用 openpyxl。

通过这两个库的结合使用，你可以高效地处理和分析 Excel 数据，从而提高工作效率。

Python 使用总结之：Python 文本转语音引擎 – pyttsx3 完全指南 – 菜鸟-创作你的创作