Python操作Excel入门：Pandas 与 Openpyxl 使用指南

在数字化办公场景中，Excel作为核心数据处理工具，其操作效率直接影响工作质量。Python通过Pandas和Openpyxl库构建了强大的Excel自动化处理体系：Pandas擅长结构化数据的高效分析，Openpyxl提供精细化的单元格级控制。以某电商公司为例，使用Python自动化处理10万行销售数据时，人工处理需40小时的工作可缩短至8分钟完成，且错误率从3.2%降至0.05%。本文ZHANID工具网将系统讲解这两个库的核心功能，通过真实案例演示从基础读写到复杂数据处理的完整流程。

一、环境准备与基础概念

1.1 库安装与版本选择

# 推荐安装方式（确保版本兼容性）
pip install pandas==2.1.4 openpyxl==3.1.2 xlrd==2.0.1

• Pandas：最新稳定版（2.1+）支持Excel读写，需依赖openpyxl或xlrd作为引擎

• Openpyxl：3.0+版本支持.xlsx格式，2.6+版本兼容旧格式

• xlrd：2.0+版本仅支持.xls读取（Pandas默认引擎）

1.2 文件格式对比

二、Pandas快速入门

2.1 基础读写操作

读取Excel文件：

import pandas as pd

# 读取单个sheet（默认第一个）
df = pd.read_excel('sales_data.xlsx', sheet_name='Sheet1')

# 读取所有sheet（返回字典）
all_sheets = pd.read_excel('sales_data.xlsx', sheet_name=None)
print(all_sheets.keys()) # 查看所有sheet名

写入Excel文件：

# 创建DataFrame
data = {
  'Product': ['A', 'B', 'C'],
  'Price': [100, 200, 150],
  'Stock': [50, 30, 40]
}
df = pd.DataFrame(data)

# 写入新文件
df.to_excel('output.xlsx', index=False, sheet_name='Products')

# 追加多个sheet（需ExcelWriter）
with pd.ExcelWriter('multi_sheet.xlsx') as writer:
  df.to_excel(writer, sheet_name='Sheet1', index=False)
  df.head(2).to_excel(writer, sheet_name='Preview', index=False)

2.2 数据清洗与转换

处理缺失值：

# 读取含缺失值的数据
df = pd.read_excel('dirty_data.xlsx')

# 填充缺失值
df.fillna({'Price': 0, 'Stock': 10}, inplace=True)

# 删除空行
df.dropna(subset=['Product'], inplace=True)

数据类型转换：

# 字符串转数值
df['Price'] = pd.to_numeric(df['Price'], errors='coerce')

# 日期处理
df['OrderDate'] = pd.to_datetime(df['OrderDate'], format='%Y-%m-%d')

2.3 数据分析实战

销售统计案例：

# 读取销售数据
sales = pd.read_excel('monthly_sales.xlsx')

# 按产品分组统计
product_stats = sales.groupby('Product').agg({
  'Quantity': 'sum',
  'Revenue': ['sum', 'mean']
})

# 计算环比增长率
product_stats['Revenue_MoM'] = product_stats['Revenue']['sum'].pct_change() * 100

# 导出结果
product_stats.to_excel('sales_analysis.xlsx')

三、Openpyxl深度应用

3.1 单元格级操作

基础读写：

from openpyxl import load_workbook

# 加载工作簿
wb = load_workbook('template.xlsx')

# 选择工作表
ws = wb['Sheet1'] # 或 wb.active 获取活动表

# 读写单元格
ws['A1'] = "Total Sales" # 写入
print(ws['B2'].value)   # 读取

# 遍历单元格
for row in ws.iter_rows(min_row=2, max_col=3, values_only=True):
  print(row)

批量操作优化：

# 高效写入10万行数据
data = [(f"Product_{i}", i*100) for i in range(1, 100001)]

for row_idx, row_data in enumerate(data, start=2):
  for col_idx, value in enumerate(row_data, start=1):
    ws.cell(row=row_idx, column=col_idx, value=value)

# 更高效的方式（避免频繁IO）
for i, (name, price) in enumerate(data, start=2):
  ws.cell(row=i, column=1, value=name)
  ws.cell(row=i, column=2, value=price)

3.2 格式与样式控制

样式设置：

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

# 定义样式
header_font = Font(name='Arial', size=12, bold=True, color='FFFFFF')
header_fill = PatternFill(start_color='4F81BD', end_color='4F81BD', fill_type='solid')
thin_border = Border(left=Side(style='thin'), 
          right=Side(style='thin'), 
          top=Side(style='thin'), 
          bottom=Side(style='thin'))
center_align = Alignment(horizontal='center', vertical='center')

# 应用样式
for cell in ws[1]: # 表头行
  cell.font = header_font
  cell.fill = header_fill
  cell.border = thin_border
  cell.alignment = center_align

# 设置列宽
ws.column_dimensions['A'].width = 20
ws.column_dimensions['B'].width = 15

条件格式：

from openpyxl.formatting.rule import CellIsRule
from openpyxl.styles import PatternFill

# 高亮显示库存不足的产品
red_fill = PatternFill(start_color='FFC7CE', end_color='FFC7CE', fill_type='solid')
ws.conditional_formatting.add('C2:C101',
  CellIsRule(operator='lessThan', formula=['10'], fill=red_fill))

3.3 公式与图表

公式计算：

# 设置公式
ws['D1'] = "Total"
ws['D2'] = "=SUM(B2:B101)" # 总销售额
ws['D3'] = "=AVERAGE(B2:B101)" # 平均单价

# 公式刷新（需保存后重新打开）
wb.save('with_formulas.xlsx')

创建图表：

from openpyxl.chart import BarChart, Reference

# 创建柱状图
chart = BarChart()
chart.title = "Monthly Sales Trend"
chart.x_axis.title = "Month"
chart.y_axis.title = "Revenue"

# 设置数据范围
data = Reference(ws, min_col=2, min_row=1, max_row=13, max_col=3)
categories = Reference(ws, min_col=1, min_row=2, max_row=13)

chart.add_data(data, titles_from_data=True)
chart.set_categories(categories)

# 插入图表到工作表
ws.add_chart(chart, "F2")

四、Pandas与Openpyxl协同工作

4.1 混合处理流程

典型场景：用Pandas处理数据，Openpyxl调整格式

import pandas as pd
from openpyxl import load_workbook
from openpyxl.utils.dataframe import dataframe_to_rows

# Pandas数据处理
df = pd.read_excel('raw_data.xlsx')
df['Profit'] = df['Revenue'] - df['Cost']
df_sorted = df.sort_values('Profit', ascending=False)

# Openpyxl格式调整
wb = load_workbook('template.xlsx')
ws = wb.active

# 写入处理后的数据（保留原格式）
for r_idx, row in enumerate(dataframe_to_rows(df_sorted, index=False, header=True), 1):
  for c_idx, value in enumerate(row, 1):
    ws.cell(row=r_idx, column=c_idx, value=value)

# 添加条件格式
from openpyxl.formatting.rule import ColorScaleRule
ws.conditional_formatting.add('B2:D101',
  ColorScaleRule(start_type='percentile', start_value=0, start_color='FF0000',
         mid_type='percentile', mid_value=50, mid_color='FFFF00',
         end_type='percentile', end_value=100, end_color='00FF00'))

wb.save('final_report.xlsx')

4.2 性能优化技巧

大数据处理方案：

1. 分块读写：

# Pandas分块读取
chunk_size = 50000
chunks = pd.read_excel('large_file.xlsx', chunksize=chunk_size)

# Openpyxl分块写入
from openpyxl.workbook import Workbook
wb = Workbook()
ws = wb.active

for i, chunk in enumerate(chunks):
  for r_idx, row in enumerate(dataframe_to_rows(chunk, index=False, header=(i==0)), 1):
    for c_idx, value in enumerate(row, 1):
      ws.cell(row=r_idx, column=c_idx, value=value)
  # 每处理完一个块保存一次
  if i % 3 == 0:
    wb.save(f'output_part_{i}.xlsx')

2. 使用缓存：

# 启用Openpyxl的只读模式处理大文件
from openpyxl import load_workbook
wb = load_workbook('large_file.xlsx', read_only=True)

# 处理完成后转换为可写模式
from openpyxl.writer.excel import save_virtual_workbook
virtual_wb = save_virtual_workbook(wb)

五、常见问题解决方案

5.1 编码与格式错误

问题：中文乱码或公式不计算
解决方案：

# 指定编码读取CSV
pd.read_csv('chinese_data.csv', encoding='gbk')

# 强制公式重新计算（Openpyxl）
wb = load_workbook('formulas.xlsx', data_only=False) # 确保不使用data_only

5.2 性能瓶颈优化

问题：处理10万行数据耗时过长
解决方案：

• 使用openpyxl.utils.dataframe.dataframe_to_rows替代逐单元格写入

• 关闭自动公式计算：wb.app.auto_calculate = False

• 使用xlwings库（需安装Excel）处理超大数据集

5.3 版本兼容问题

问题：新版本Excel无法打开生成的文件
解决方案：

# 明确指定引擎版本
pd.read_excel('file.xlsx', engine='openpyxl') # 强制使用openpyxl

# 保存为兼容格式
wb.save('legacy_format.xls', write_only=True) # 需安装xlwt（仅支持.xls）

结语

通过掌握Pandas的结构化数据处理能力和Openpyxl的精细化控制能力，开发者可以构建完整的Excel自动化解决方案。在实际项目中，建议根据数据规模和处理需求选择合适工具：中小型数据集（<10万行）优先使用Pandas，需要复杂格式或图表时结合Openpyxl；超大数据集建议考虑数据库中间层或专业ETL工具。掌握这些技术后，可将80%的Excel手工操作转化为自动化脚本，显著提升工作效率和数据准确性。