Python 深度挖掘：openpyxl 和 pandas 的使用详细教程
在处理 Excel 文件时，Python 的 openpyxl 和 pandas 是两个非常流行的库，它们各自有不同的优势。openpyxl更侧重于对 Excel 文件的精细控制，如格式设置、图表处理等，而 pandas 更侧重于数据处理和分析，尤其是大规模数据操作。
这篇教程将详细介绍如何使用这两个库来处理 Excel 文件。我们将学习如何用它们进行以下操作：
- 打开和读取 Excel 文件
- 数据操作：修改、插入、删除数据
- 处理多个工作表
- 格式设置
- 数据分析和导出
- 如何结合
openpyxl和pandas使用
1. openpyxl 与 pandas 安装
首先，确保你安装了这两个库。你可以使用 pip 来安装它们：
pip install openpyxl pandas
2. 打开和读取 Excel 文件
使用 pandas 读取 Excel 文件
pandas 提供了非常便捷的方式来读取 Excel 文件。其 read_excel() 函数允许直接读取 Excel 文件，并返回一个 DataFrame。
import pandas as pd
# 读取 Excel 文件
df = pd.read_excel('example.xlsx')
# 显示前几行数据
print(df.head())
pandas 也支持从指定的工作表读取数据，可以使用 sheet_name 参数指定工作表名或索引。
# 读取指定工作表
df = pd.read_excel('example.xlsx', sheet_name='Sheet1')
# 读取多个工作表
dfs = pd.read_excel('example.xlsx', sheet_name=['Sheet1', 'Sheet2'])
使用 openpyxl 读取 Excel 文件
openpyxl 允许你更细致地访问 Excel 文件中的内容，包括单元格的值、样式、公式等。
from openpyxl import load_workbook
# 加载 Excel 文件
wb = load_workbook('example.xlsx')
# 选择工作表
sheet = wb['Sheet1']
# 读取单元格内容
cell_value = sheet['A1'].value
print(cell_value)
# 读取一个单元格范围
for row in sheet.iter_rows(min_row=1, max_row=5, min_col=1, max_col=3):
for cell in row:
print(cell.value)
3. 数据操作：修改、插入、删除数据
使用 pandas 操作数据
pandas 提供了强大的数据操作功能，如修改、删除和插入数据。
修改数据
# 修改某个单元格的值
df.at[0, 'Column1'] = 'New Value'
# 修改整个列的数据
df['Column2'] = df['Column2'] * 2
删除数据
# 删除某一列
df = df.drop(columns=['Column1'])
# 删除某一行
df = df.drop(index=0)
插入数据
# 插入一列数据
df['NewColumn'] = df['Column2'] + df['Column3']
# 插入一行数据
new_row = {'Column1': 10, 'Column2': 20, 'Column3': 30}
df = df.append(new_row, ignore_index=True)
使用 openpyxl 操作数据
openpyxl 可以对单元格进行修改、插入新行或列等操作。
修改数据
# 修改单元格的值
sheet['A1'] = 'New Value'
插入数据
# 插入一行
sheet.insert_rows(2)
# 插入一列
sheet.insert_cols(2)
# 插入数据
sheet['B2'] = 'New Data'
删除数据
# 删除行
sheet.delete_rows(2)
# 删除列
sheet.delete_cols(2)
4. 处理多个工作表
使用 pandas 处理多个工作表
pandas 可以直接读取多个工作表，并将它们返回为一个字典，字典的键是工作表的名称，值是对应的 DataFrame。
# 读取多个工作表
dfs = pd.read_excel('example.xlsx', sheet_name=None)
# 获取特定工作表的 DataFrame
df_sheet1 = dfs['Sheet1']
df_sheet2 = dfs['Sheet2']
使用 openpyxl 处理多个工作表
你可以通过 openpyxl 访问多个工作表，方法是通过 workbook.sheetnames 获取所有工作表的名称，然后选择不同的工作表。
# 获取所有工作表名称
sheet_names = wb.sheetnames
print(sheet_names)
# 访问工作表
sheet1 = wb['Sheet1']
sheet2 = wb['Sheet2']
5. 格式设置
使用 openpyxl 设置格式
openpyxl 允许你自定义单元格的格式，包括字体、颜色、边框等。
设置字体和颜色
from openpyxl.styles import Font, Color, PatternFill
# 设置字体
font = Font(name='Calibri', size=12, bold=True, color='FF0000')
sheet['A1'].font = font
# 设置背景颜色
fill = PatternFill(start_color='FFFF00', end_color='FFFF00', fill_type='solid')
sheet['A1'].fill = fill
设置边框
from openpyxl.styles import Border, Side
# 创建边框样式
border = Border(left=Side(style='thin'), right=Side(style='thin'),
top=Side(style='thin'), bottom=Side(style='thin'))
sheet['A1'].border = border
使用 pandas 设置格式
pandas 主要用于数据处理和分析，因此它对格式化的支持较弱。不过你可以通过 Styler 对 DataFrame 进行简单的样式设置，或者结合 openpyxl 来做更复杂的格式化。
# 使用 pandas 的 Styler 设置样式
styled_df = df.style.highlight_max(color='yellow').highlight_min(color='red')
# 将样式应用到 Excel 中
styled_df.to_excel('styled_example.xlsx', engine='openpyxl')
6. 数据分析与导出
使用 pandas 进行数据分析
pandas 提供了强大的数据分析工具，可以进行汇总、分组、聚合、筛选等操作。
数据汇总
# 获取数据的描述性统计
print(df.describe())
# 获取某一列的最大值和最小值
print(df['Column2'].max(), df['Column2'].min())
分组与聚合
# 按某列分组并计算聚合统计
grouped = df.groupby('Column1').agg({'Column2': 'sum'})
print(grouped)
导出数据到 Excel 文件
你可以使用 pandas 导出 DataFrame 到 Excel 文件，甚至可以同时导出多个工作表。
# 导出为 Excel 文件
df.to_excel('output.xlsx', index=False)
# 导出多个工作表
with pd.ExcelWriter('multiple_sheets.xlsx', engine='openpyxl') as writer:
df_sheet1.to_excel(writer, sheet_name='Sheet1')
df_sheet2.to_excel(writer, sheet_name='Sheet2')
7. 如何结合 openpyxl 和 pandas 使用
有时你需要在数据处理时同时使用 pandas 和 openpyxl，比如用 pandas 处理数据，然后用 openpyxl 设置格式。
例子：处理数据并设置格式
import pandas as pd
from openpyxl import load_workbook
from openpyxl.styles import Font
# 使用 pandas 读取数据
df = pd.read_excel('example.xlsx')
# 修改数据
df['Column1'] = df['Column1'] * 2
# 使用 openpyxl 设置格式
wb = load_workbook('example.xlsx')
sheet = wb.active
# 设置字体
font = Font(name='Arial', size=14, bold=True)
sheet['A1'].font = font
# 将修改后的数据写回 Excel
df.to_excel('modified_example.xlsx', index=False, engine='openpyxl')
总结
openpyxl是一个强大的库，适合做精细的 Excel 操作，如格式设置、插入图表等。pandas是用于数据分析和处理的库，提供了强大的数据操作功能，尤其适合大规模数据操作。- 结合使用这两个库可以让你更高效地处理和分析 Excel 数据。
这篇教程涵盖了如何使用这两个库来读取、操作、分析 Excel 数据，并进行格式设置。根据你的需求，选择合适
的库或者结合使用它们来提高你的工作效率。
发表回复