数据源接入（一）：Excel文件读取与写入

做报告自动化，Excel 是绕不开的第一道坎。我见过太多人把时间花在手动复制粘贴上，其实用 Python 处理 Excel 比你想象的要简单得多。今天我们就来聊聊怎么用 openpyxl 和 pandas 搞定 Excel 文件的读写，以及多 Sheet 场景下的处理技巧。

为什么选这两个库？

说实话，Python 处理 Excel 的库不少，但我个人最常用的是这两个：

• openpyxl：纯 Python 实现，不依赖 Office 环境。能读写 .xlsx 文件，支持样式、公式、图表。适合需要精细控制单元格格式的场景。
• pandas：数据分析利器。读取 Excel 后直接返回 DataFrame，后续清洗、聚合、透视一气呵成。适合批量数据处理。

我一般这样分工：数据量大、逻辑复杂时用 pandas；需要保留格式、写回样式时用 openpyxl。两者配合，基本能覆盖 90% 的 Excel 自动化需求。

openpyxl 实战：从零开始读写 Excel

先看一个最简单的例子。假设你有一个销售报表，需要读取里面的数据。

from openpyxl import load_workbook

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

# 获取当前活动工作表
ws = wb.active

# 读取单元格
print(ws['A1'].value)  # 第一行第一列
print(ws.cell(row=2, column=3).value)  # 第二行第三列

# 遍历所有行
for row in ws.iter_rows(min_row=2, values_only=True):
    print(row)  # 每行是一个元组

嗯，这里要注意：values_only=True 这个参数很关键。不加的话，返回的是 Cell 对象，不是值。我刚开始用的时候就踩过这个坑，调试了半天才发现。

写入数据并保留格式

写入的时候，openpyxl 的优势就体现出来了。你可以控制字体、颜色、对齐方式，甚至合并单元格。

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

wb = Workbook()
ws = wb.active
ws.title = "月度汇总"

# 设置表头样式
header_font = Font(name='微软雅黑', bold=True, size=12, color='FFFFFF')
header_fill = PatternFill(start_color='3498DB', end_color='3498DB', fill_type='solid')
header_align = Alignment(horizontal='center', vertical='center')

headers = ['月份', '销售额', '成本', '利润']
for col, header in enumerate(headers, 1):
    cell = ws.cell(row=1, column=col, value=header)
    cell.font = header_font
    cell.fill = header_fill
    cell.alignment = header_align

# 写入数据
data = [
    ['1月', 120000, 80000, 40000],
    ['2月', 135000, 85000, 50000],
    ['3月', 110000, 72000, 38000]
]
for row_idx, row_data in enumerate(data, 2):
    for col_idx, value in enumerate(row_data, 1):
        ws.cell(row=row_idx, column=col_idx, value=value)

wb.save('formatted_report.xlsx')

pandas 实战：一行代码搞定读取

pandas 读取 Excel 就简单多了。我敢说，90% 的数据读取场景，用 pandas 就够了。

import pandas as pd

# 读取整个工作表
df = pd.read_excel('sales_report.xlsx', sheet_name='Sheet1')

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

# 基本统计
print(df.describe())

# 写入 Excel
df.to_excel('output.xlsx', index=False, sheet_name='汇总')

你看，三行代码就完成了读取、分析和写入。但这里有个细节：index=False 一定要加，否则 pandas 会把行索引也写进去，生成一个多余的列。我曾经因为这个被领导批评过，说报表里多了个莫名其妙的数字列。

pandas 读取时的常用参数

参数	作用	示例
sheet_name	指定工作表名称或索引	sheet_name='Sheet1' 或 sheet_name=0
header	指定表头行号	header=0（默认第一行）
usecols	只读取指定列	usecols='A:C' 或 usecols=['月份','销售额']
dtype	指定列数据类型	dtype={'销售额': float}
skiprows	跳过前N行	skiprows=2

多 Sheet 处理：这才是实战场景

真实项目里，一个 Excel 文件往往有多个 Sheet。比如财务月报，可能有「收入明细」「成本明细」「汇总表」三个 Sheet。怎么高效处理？

方案一：pandas 批量读取

# 读取所有 Sheet，返回字典
all_sheets = pd.read_excel('monthly_report.xlsx', sheet_name=None)

# 遍历处理
for sheet_name, df in all_sheets.items():
    print(f"正在处理: {sheet_name}")
    # 对每个 DataFrame 做处理
    df['数据来源'] = sheet_name  # 标记来源
    df.to_excel(f'processed_{sheet_name}.xlsx', index=False)

sheet_name=None 这个参数很实用。它返回一个字典，键是 Sheet 名称，值是 DataFrame。你想想看，一个循环就能处理所有 Sheet，多省事。

方案二：openpyxl 精细控制

如果需要在不同 Sheet 之间复制数据、保留格式，那就得上 openpyxl 了。

from openpyxl import load_workbook

wb = load_workbook('template.xlsx')

# 获取所有 Sheet 名称
sheet_names = wb.sheetnames
print(f"共有 {len(sheet_names)} 个 Sheet: {sheet_names}")

# 在指定位置创建新 Sheet
ws_new = wb.create_sheet('汇总', 0)  # 插入到最前面

# 复制数据：从 Sheet1 复制到汇总
ws_source = wb['Sheet1']
for row in ws_source.iter_rows(min_row=1, max_row=ws_source.max_row, values_only=True):
    ws_new.append(row)

wb.save('multi_sheet_output.xlsx')

知识体系总览

下面这张图总结了本章的核心知识点，你可以对照着梳理自己的学习路径。

实战案例：合并多个 Sheet 的销售数据

最后，我们用一个完整的例子来收尾。假设你有一个 Excel 文件，里面每个 Sheet 代表一个门店的销售数据，需要合并成一个总表。

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

# 第一步：用 pandas 读取所有 Sheet
file_path = 'stores_sales.xlsx'
all_sheets = pd.read_excel(file_path, sheet_name=None)

# 第二步：合并数据
combined_df = pd.DataFrame()
for sheet_name, df in all_sheets.items():
    df['门店'] = sheet_name  # 添加门店标识
    combined_df = pd.concat([combined_df, df], ignore_index=True)

# 第三步：用 openpyxl 写入并设置样式
with pd.ExcelWriter('merged_report.xlsx', engine='openpyxl') as writer:
    combined_df.to_excel(writer, sheet_name='汇总', index=False)
    
    # 获取 workbook 和 worksheet
    workbook = writer.book
    worksheet = writer.sheets['汇总']
    
    # 设置表头样式
    header_font = Font(bold=True, color='FFFFFF', size=11)
    header_fill = PatternFill(start_color='2C3E50', end_color='2C3E50', fill_type='solid')
    header_align = Alignment(horizontal='center', vertical='center')
    
    for cell in worksheet[1]:
        cell.font = header_font
        cell.fill = header_fill
        cell.alignment = header_align

print(f"合并完成！共 {len(combined_df)} 条记录，来自 {len(all_sheets)} 个门店。")

这个模式我用了很多次。pandas 负责数据合并，openpyxl 负责最终呈现。各司其职，代码清晰，维护起来也方便。