用 Python 操作 Excel：openpyxl 与 pandas 实战对比

两个库怎么选

Python 操作 Excel 主要用这两个库，分工不同：

	pandas	openpyxl
定位	数据分析	Excel 文件读写控制
擅长	批量数据处理、统计、筛选	单元格样式、合并、公式、图表
操作粒度	整表/整行/整列	精确到每个单元格
学习成本	低	中

经验法则：

• 「处理数据」用 pandas（读进来 → 算 → 写出去）
• 「操作格式」用 openpyxl（改样式、加批注、做模板报表）
• 两者经常配合使用：pandas 算数据，openpyxl 美化输出

pip install pandas openpyxl

pandas 读写 Excel

读取：

import pandas as pd

# 读单个工作表
df = pd.read_excel("销售数据.xlsx", sheet_name="1月")

# 读所有工作表，返回 {表名: DataFrame} 字典
all_sheets = pd.read_excel("销售数据.xlsx", sheet_name=None)

# 实用参数
pd.read_excel("data.xlsx", skiprows=2)       # 跳过前2行标题说明
pd.read_excel("data.xlsx", usecols="A:C")    # 只读A到C列
pd.read_excel("data.xlsx", dtype={"工号": str})  # 工号按字符串读，防止前导零丢失

写入：

# 单表写入
df.to_excel("结果.xlsx", index=False)

# 多表写入一个文件
with pd.ExcelWriter("汇总.xlsx", engine="openpyxl") as writer:
    df1.to_excel(writer, sheet_name="汇总", index=False)
    df2.to_excel(writer, sheet_name="明细", index=False)

openpyxl 精细控制

基本读写：

from openpyxl import Workbook, load_workbook

# 读已有文件
wb = load_workbook("模板.xlsx")
ws = wb["Sheet1"]           # 按名取工作表
ws = wb.active              # 或取活动表

print(ws["A1"].value)       # 读单元格
ws["B2"] = "hello"          # 写单元格
ws.cell(row=3, column=2, value=100)  # 按行列写

# 遍历数据区
for row in ws.iter_rows(min_row=2, max_row=100, values_only=True):
    print(row)  # 每行是一个元组

wb.save("输出.xlsx")

设置样式（做正式报表必备）：

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

ws = wb.active

# 表头样式：加粗白字深蓝底，居中
header_font = Font(bold=True, color="FFFFFF", size=12)
header_fill = PatternFill("solid", start_color="2F5597")
for cell in ws[1]:
    cell.font = header_font
    cell.fill = header_fill
    cell.alignment = Alignment(horizontal="center", vertical="center")

# 边框
thin = Side(style="thin", color="999999")
border = Border(left=thin, right=thin, top=thin, bottom=thin)
for row in ws.iter_rows(min_row=1, max_row=ws.max_row, max_col=5):
    for cell in row:
        cell.border = border

# 列宽行高
ws.column_dimensions["A"].width = 20
ws.row_dimensions[1].height = 28

# 冻结首行（滚动时表头不动）
ws.freeze_panes = "A2"

公式与合并：

ws["E10"] = "=SUM(E2:E9)"          # 写公式，Excel 打开时自动计算
ws.merge_cells("A1:E1")            # 合并单元格做标题

实战：批量合并 100 个 Excel 文件

真实场景：每月底收到 30 个分公司发来的同格式报表，要合并成一个总表。

from pathlib import Path

import pandas as pd

def merge_excels(folder: str, output: str) -> None:
    """合并目录下所有 xlsx 的第一个工作表"""
    files = list(Path(folder).glob("*.xlsx"))
    if not files:
        print("目录下没有 xlsx 文件")
        return

    frames = []
    for f in files:
        try:
            df = pd.read_excel(f)
            df["来源文件"] = f.stem  # 加一列标记数据来源，方便追溯
            frames.append(df)
            print(f"已读取：{f.name}（{len(df)} 行）")
        except Exception as e:
            print(f"读取失败 {f.name}：{e}")  # 某个文件坏了不影响整体

    result = pd.concat(frames, ignore_index=True)
    result.to_excel(output, index=False)
    print(f"\n合并完成：{len(result)} 行 → {output}")

merge_excels("./分公司报表", "./合并结果.xlsx")

实战：生成带样式的月度报表

数据分析完，输出一份「拿得出手」的报表：

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

# 第一步：pandas 算数据
df = pd.read_excel("原始数据.xlsx")
summary = df.groupby("部门").agg(
    总销售额=("销售额", "sum"),
    订单数=("订单号", "count"),
).reset_index()

# 第二步：写入文件
summary.to_excel("月度报表.xlsx", index=False, sheet_name="汇总")

# 第三步：openpyxl 美化
wb = load_workbook("月度报表.xlsx")
ws = wb.active

# 标题行样式
for cell in ws[1]:
    cell.font = Font(bold=True, color="FFFFFF")
    cell.fill = PatternFill("solid", start_color="305496")
    cell.alignment = Alignment(horizontal="center")

# 数字格式：千分位 + 保留两位小数
for row in ws.iter_rows(min_row=2, min_col=2, max_col=2):
    for cell in row:
        cell.number_format = "#,##0.00"

# 自适应列宽（按内容长度估算）
for col in ws.columns:
    max_len = max(len(str(c.value or "")) for c in col)
    ws.column_dimensions[col[0].column_letter].width = max_len * 2 + 4

wb.save("月度报表.xlsx")

常见坑

1. xls 与 xlsx 要分清

• .xlsx（2007 后的格式）→ openpyxl
• .xls（老格式）→ 需要 xlrd 库，且 xlrd 2.0+ 只支持 xls
• 简单办法：先用 Excel 把 xls 另存为 xlsx

2. 大文件内存爆炸

十万行以上的文件，openpyxl 普通模式会很吃内存，用只读模式：

wb = load_workbook("大文件.xlsx", read_only=True)

pandas 则分块读（仅限 CSV；Excel 大文件建议先转 CSV）。

3. 数字变科学计数法 / 丢失前导零

身份证号、手机号、工号这些「数字字符串」，读的时候指定 dtype=str，写的时候设置单元格为文本格式：

cell.number_format = "@"  # 文本格式，18位身份证不会被转成科学计数

4. 公式不计算

openpyxl 写的公式，只有用 Excel/WPS 打开时才会计算。如果程序里要读公式结果，先用 Excel 打开保存一次，或读取时用 data_only=True（前提是文件被 Excel 保存过）。

写在最后

办公自动化的回报是立竿见影的：昨天还花一下午的合并报表，今天一个脚本 30 秒跑完。从你身边最烦的那个重复性 Excel 工作开始——那就是你的第一个自动化脚本最好的题材。