Python入门第10课：用Python操作Excel，openpyxl与pandas实用技巧

Python入门第10课：用Python操作Excel，openpyxl与pandas实用技巧

作者： 蛋皮
标签： Python, Excel, openpyxl, pandas, 数据处理, 自动化

欢迎回到Python入门系列！在前几节课中，我们掌握了文件操作、异常处理、模块与包的使用。今天，我们将这些知识应用到一个非常实用的场景——自动化处理Excel文件。无论你是数据分析师、财务人员、行政助理还是开发人员，Excel都是日常工作中不可或缺的工具。而Python，凭借其强大的库，可以让你从繁琐的手动操作中解放出来，实现高效、准确的Excel数据处理自动化。

我们将重点学习两个最流行的Python库：openpyxl 和 pandas。它们各有侧重，配合使用能发挥巨大威力。

为什么选择Python处理Excel？

• 自动化: 一键完成重复性任务（如合并多个工作簿、批量重命名、格式化）。
• 准确性: 避免人为操作失误。
• 高效性: 处理大量数据远超手动操作速度。
• 集成性: 轻松与其他数据源（数据库、API、CSV文件）交互。
• 可复用性: 写好的脚本可以反复使用。

准备工作：安装必要的库

首先，确保你已安装了所需的第三方库。打开命令行（终端），执行：

pip install openpyxl pandas

• openpyxl: 专门用于读写 .xlsx (Excel 2007+) 文件。它能精确控制单元格、样式、图表等。
• pandas: 强大的数据分析库。它提供了 DataFrame 数据结构，能高效地处理表格数据，并通过 openpyxl 引擎读写Excel文件。

第一部分：openpyxl - 精细控制Excel

openpyxl 让你能够像使用Excel软件一样，精确地操作工作簿、工作表、单元格、行、列、样式等。

1. 读取Excel文件

from openpyxl import load_workbook

# 加载现有的Excel工作簿
workbook = load_workbook('example.xlsx')

# 获取工作表
# 方法1: 通过名称获取
worksheet = workbook['Sheet1']
# 方法2: 获取活动工作表 (通常是打开时显示的)
# worksheet = workbook.active

# 读取单元格数据
cell_A1 = worksheet['A1']  # 获取单元格对象
print(f"A1单元格的值: {cell_A1.value}")
print(f"A1单元格的行: {cell_A1.row}, 列: {cell_A1.column}, 坐标: {cell_A1.coordinate}")

# 直接通过坐标读取值
value_B2 = worksheet['B2'].value
print(f"B2的值: {value_B2}")

# 读取特定区域的数据
print("\n读取A1到C3区域:")
for row in worksheet['A1:C3']:
    for cell in row:
        print(f"{cell.coordinate}: {cell.value}", end=" | ")
    print()  # 换行

# 获取工作表的维度 (包含数据的最小矩形区域)
print(f"数据范围: {worksheet.dimensions}")  # 如 'A1:C10'

# 遍历行或列
print("\n遍历第2行:")
for cell in worksheet[2]:  # 第2行的所有单元格
    print(cell.value, end=" ")
print()

# 关闭工作簿 (虽然load_workbook通常不需要手动close，但好习惯)
workbook.close()

2. 创建和写入Excel文件

from openpyxl import Workbook
from openpyxl.styles import Font, PatternFill

# 创建一个新的工作簿
workbook = Workbook()

# 获取活动工作表 (新工作簿默认有一个)
worksheet = workbook.active
worksheet.title = "销售数据"  # 重命名工作表

# 写入数据
worksheet['A1'] = "产品"
worksheet['B1'] = "销量"
worksheet['C1'] = "销售额"

products = ["苹果", "香蕉", "橙子"]
sales = [150, 200, 120]
revenue = [3000, 2400, 1800]

for i, (product, sale, rev) in enumerate(zip(products, sales, revenue), start=2):
    worksheet[f'A{i}'] = product
    worksheet[f'B{i}'] = sale
    worksheet[f'C{i}'] = rev

# 应用样式
# 设置标题行加粗和背景色
header_font = Font(bold=True)
header_fill = PatternFill(start_color="D3D3D3", end_color="D3D3D3", fill_type="solid") # 浅灰色

for cell in worksheet[1]:  # 第1行
    cell.font = header_font
    cell.fill = header_fill

# 调整列宽
worksheet.column_dimensions['A'].width = 15
worksheet.column_dimensions['B'].width = 10
worksheet.column_dimensions['C'].width = 12

# 保存文件
workbook.save('sales_report.xlsx')
print("销售报告已生成: sales_report.xlsx")

# 关闭工作簿
workbook.close()

3. openpyxl 实用技巧

• 处理大文件: 对于非常大的文件，使用 read_only=True 模式加载可以节省内存。

  workbook = load_workbook('huge_file.xlsx', read_only=True)
  

• 创建新工作表:

  new_sheet = workbook.create_sheet("新工作表")
  # 或者指定位置
  # new_sheet = workbook.create_sheet("汇总", 0) # 插入到第一个位置
  

• 删除工作表:

  workbook.remove(worksheet)  # 删除指定工作表
  # 或 workbook.remove(workbook['SheetName'])
  

• 公式: 可以写入Excel公式。

  worksheet['D2'] = '=B2*C2'  # 假设D列是销售额=销量*单价
  

第二部分：pandas - 高效处理数据

pandas 的核心是 DataFrame，一个类似电子表格或SQL表的二维数据结构。它在数据清洗、转换、分析方面极其强大。对于Excel操作，pandas 通常用于读取数据进行分析处理，然后将结果写回Excel。

1. 用 pandas 读取Excel

import pandas as pd

# 读取Excel文件的第一个工作表
df = pd.read_excel('sales_report.xlsx')
print("DataFrame内容:")
print(df)
print(f"\n数据形状: {df.shape}")  # (行数, 列数)
print(f"列名: {df.columns.tolist()}")

# 读取特定工作表
# df_sheet2 = pd.read_excel('file.xlsx', sheet_name='Sheet2')

# 读取多个工作表到一个字典
# dfs = pd.read_excel('file.xlsx', sheet_name=['Sheet1', 'Sheet2']) # 或 sheet_name=None 读取所有
# print(dfs['Sheet1'])

# 指定列作为索引
# df = pd.read_excel('file.xlsx', index_col='产品')

# 跳过行或读取特定区域
# df = pd.read_excel('file.xlsx', skiprows=1, usecols='B:D') # 跳过第一行，只读B到D列

2. 用 pandas 写入Excel

# 假设我们有一个处理后的DataFrame
data = {
    '姓名': ['张三', '李四', '王五'],
    '部门': ['技术', '销售', '人事'],
    '工资': [8000, 6000, 5500]
}
df_new = pd.DataFrame(data)

# 写入Excel (会覆盖同名文件)
df_new.to_excel('employees.xlsx', index=False, sheet_name='员工信息')
print("员工信息已写入: employees.xlsx")

# 写入多个工作表 (需要使用 ExcelWriter)
with pd.ExcelWriter('combined_report.xlsx', engine='openpyxl') as writer:
    df.to_excel(writer, sheet_name='销售数据', index=False)
    df_new.to_excel(writer, sheet_name='员工信息', index=False)
print("综合报告已生成: combined_report.xlsx")

3. pandas 实用技巧

• 数据清洗:

  # 处理缺失值
  df.dropna(inplace=True)  # 删除含空值的行
  # df.fillna(0, inplace=True) # 用0填充空值
  
  # 数据类型转换
  df['销量'] = df['销量'].astype(int)
  df['销售额'] = pd.to_numeric(df['销售额'], errors='coerce') # 转换为数字，错误的转为NaN
  
  # 字符串操作
  df['产品'] = df['产品'].str.strip()  # 去除空格
  df['部门'] = df['部门'].str.upper()  # 转为大写
  

• 数据筛选与查询:

  # 筛选销量大于100的产品
  high_sales = df[df['销量'] > 100]
  print("高销量产品:")
  print(high_sales)
  
  # 多条件筛选
  # result = df[(df['销量'] > 100) & (df['销售额'] < 2500)]
  

• 数据聚合与分析:

  # 计算总和、平均值等
  total_sales = df['销量'].sum()
  avg_revenue = df['销售额'].mean()
  print(f"总销量: {total_sales}, 平均销售额: {avg_revenue:.2f}")
  
  # 分组聚合
  # sales_by_dept = df.groupby('部门')['销售额'].sum()
  

• 样式化 (Styling): pandas 可以在写入Excel时应用基本样式。

  # 创建样式器
  styled_df = df.style.highlight_max(axis=0, color='lightgreen') \
                     .highlight_min(axis=0, color='lightcoral') \
                     .format({'销售额': '{:.0f}'})  # 格式化数字
  
  # 写入Excel
  with pd.ExcelWriter('styled_report.xlsx', engine='openpyxl') as writer:
      styled_df.to_excel(writer, sheet_name='销售数据', index=False)
  

openpyxl vs pandas：如何选择？

结论： 它们不是替代关系，而是互补关系！一个常见的工作流是：

1. 用 pandas 读取多个数据源（Excel, CSV, 数据库）并进行清洗、合并、计算。
2. 将处理好的结果用 pandas 写入一个Excel文件的多个工作表。
3. 再用 openpyxl 打开这个文件，对最终报告进行精细的样式美化（如设置标题、调整列宽、添加边框、插入图表）。

综合示例：生成一份销售分析报告

import pandas as pd
from openpyxl import load_workbook
from openpyxl.styles import Font, Alignment, Border, Side

# 1. 使用pandas处理数据
print("1. 使用pandas处理数据...")
# 假设有两个数据源
sales_data = pd.DataFrame({
    '日期': pd.date_range('2025-01-01', periods=3),
    '产品': ['苹果', '香蕉', '橙子'],
    '销量': [150, 200, 120],
    '单价': [20, 12, 15]
})
sales_data['销售额'] = sales_data['销量'] * sales_data['单价']

# 计算汇总
summary = sales_data[['销量', '销售额']].sum()
summary.name = '总计'
sales_data_with_summary = pd.concat([sales_data, pd.DataFrame([summary])], ignore_index=True)

# 2. 用pandas写入Excel (作为基础)
print("2. 写入基础数据...")
with pd.ExcelWriter('final_report.xlsx', engine='openpyxl') as writer:
    sales_data.to_excel(writer, sheet_name='详细数据', index=False)
    # 也可以写入其他分析结果
print("基础数据写入完成。")

# 3. 使用openpyxl进行美化
print("3. 使用openpyxl进行美化...")
wb = load_workbook('final_report.xlsx')
ws = wb['详细数据']

# 设置标题行样式
header_font = Font(bold=True, color="FFFFFF")  # 白色加粗
header_fill = PatternFill(start_color="366092", end_color="366092", fill_type="solid") # 深蓝色
thin_border = Border(left=Side(style='thin'), 
                     right=Side(style='thin'), 
                     top=Side(style='thin'), 
                     bottom=Side(style='thin'))

for cell in ws[1]:
    cell.font = header_font
    cell.fill = header_fill
    cell.alignment = Alignment(horizontal='center')  # 居中
    cell.border = thin_border

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

# 为总计行设置样式 (假设总计在最后一行)
last_row = ws.max_row
for cell in ws[last_row]:
    cell.font = Font(bold=True)
    cell.fill = PatternFill(start_color="D9D9D9", end_color="D9D9D9", fill_type="solid") # 灰色背景
    cell.border = thin_border

# 保存最终报告
wb.save('final_report.xlsx')
wb.close()
print("最终销售分析报告已生成: final_report.xlsx")

最佳实践与总结

1. 明确需求: 先想清楚任务是侧重数据处理还是格式排版，选择合适的工具或组合使用。
2. 处理大文件: 对于超大Excel文件，考虑使用 pandas 的 chunksize 参数分块读取，或 openpyxl 的 read_only 模式。
3. 异常处理: 在生产脚本中，务必使用 try-except 处理文件不存在、权限不足、数据格式错误等异常。
4. 虚拟环境: 在项目中使用虚拟环境管理依赖。
5. 备份原文件: 在修改重要Excel文件前，先备份。

总结

通过学习 openpyxl 和 pandas，你已经掌握了用Python自动化Excel操作的强大技能。openpyxl 赋予你“外科手术”般的精确控制力，而 pandas 则提供了“数据处理引擎”的澎湃动力。将它们结合起来，你就能轻松应对从简单的数据录入到复杂的报表生成等各种任务，大幅提升工作效率。

练习建议：

1. 找一个你工作中的Excel文件，尝试用 pandas 读取它，进行一些简单的数据筛选或计算，然后保存为新文件。
2. 使用 openpyxl 创建一个包含多行多列数据的新Excel文件，并为其添加标题、边框和背景色。
3. 模仿“综合示例”，尝试生成一个包含数据和汇总的简单报告。