用 Python 读 Excel 的四个必踩坑：合并单元格、日期序列值、前导零、公式与值的取舍

从系统导出、同事发来、爬虫抓到的 Excel 文件，用 Python 读出来经常"看着对、用起来不对"：合并单元格只读到左上角、日期变成整数、编号 00123 变成 123、公式列全是 None。这四个坑不是库的 bug，是 Excel 文件格式本身的设计。本文用 openpyxl 3.1.5 和 pandas 3.1.0 实测，把每个坑的成因、表现、解法一次讲清。

信息核验时点：2026-11-03（openpyxl 3.1.5，发布于 2024-05-29；pandas 3.1.0，发布于 2026 年）

合并单元格为什么只读到左上角？

Excel 的合并单元格在文件里只存一个值（左上角那个），其余位置在 XML 里是空的。openpyxl 和 pandas 都如实反映这个结构——读出来就是 None 或 NaN。这不是 bug，是"所见即所得"：你在 Excel 里看到合并区域只有一个值，读出来也应该只有一个值。

造一份 A1:B1 合并、A1 写"合并标题"的 xlsx，5,119 B。openpyxl 读取：

from openpyxl import load_workbook
wb = load_workbook('four_pitfalls.xlsx')
ws = wb.active
print(ws['A1'].value)  # '合并标题'
print(ws['B1'].value)  # None
print(list(ws.merged_cells.ranges))  # [<MergedCellRange A1:B1>]

pandas 更彻底——read_excel 默认把合并区域的第一行当表头，第二行起全是 NaN：

import pandas as pd
df = pd.read_excel('four_pitfalls.xlsx', header=None)
print(df.iloc[0, 0])  # '合并标题'
print(df.iloc[0, 1])  # NaN

读完后需要手动填充。openpyxl 可以遍历 merged_cells.ranges，把左上角的值复制到区域内每个单元格；pandas 用 fillna(method='ffill') 沿列向下填充（前提是合并方向是纵向的）。

# openpyxl 填充合并单元格
for merged_range in ws.merged_cells.ranges:
    top_left = ws.cell(row=merged_range.min_row, column=merged_range.min_col).value
    for row in ws.iter_rows(min_row=merged_range.min_row, max_row=merged_range.max_row,
                            min_col=merged_range.min_col, max_col=merged_range.max_col):
        for cell in row:
            if cell.value is None:
                cell.value = top_left

如果合并是横向的（如 A1:B1），填充方向要改成沿行；如果表格有多处合并且方向不一，只能逐个区域判断。

日期序列值是什么？为什么读出来是整数？

Excel 没有"日期类型"——它存的是一个整数（从 1899-12-30 起的天数）加上一个 number_format 掩码（如 YYYY-MM-DD）。1899-12-30 这个起点是历史遗留：Lotus 1-2-3 把 1900-01-01 当作第 1 天，但错误地认为 1900 年是闰年（实际不是），Excel 为了兼容保留了这个 bug，所以起点往前挪了两天变成 1899-12-30。

读的时候是否自动转成 datetime，取决于库的实现。

B3 单元格写入整数 45000、格式掩码 YYYY-MM-DD（对应 2023-03-15）。openpyxl 的行为：

wb = load_workbook('four_pitfalls.xlsx')
ws = wb.active
print(repr(ws['B3'].value))  # datetime.datetime(2023, 3, 15, 0, 0)
print(ws['B3'].number_format)  # 'YYYY-MM-DD'

openpyxl 看到 number_format 是日期格式，自动把整数转成 datetime 对象。但如果你想要原始整数（比如要做日期差计算），就得先检查 number_format 再决定是否转换。

pandas 的行为不同——read_excel 默认不自动解析日期，除非你显式传 parse_dates：

df = pd.read_excel('four_pitfalls.xlsx', header=None)
print(df.iloc[2, 1])  # 45000（整数，没转）
df2 = pd.read_excel('four_pitfalls.xlsx', header=None, parse_dates=[1])
print(df2.iloc[2, 1])  # 2023-03-15（转了）

明确你要的是"显示值"还是"原始值"。要显示值就用 openpyxl 的自动转换或 pandas 的 parse_dates；要原始值就用 openpyxl 读 cell.value 前检查 number_format，或者 pandas 不传 parse_dates。如果源文件的日期列格式不统一（有的单元格是日期格式、有的是常规），openpyxl 会按每个单元格的 number_format 单独判断，pandas 的 parse_dates 则是整列统一处理——遇到混合格式列时 openpyxl 更灵活。

前导零为什么丢了？怎么保留？

Excel 的数值类型不保留前导零——00123 存成数值就是 123。要保留前导零，单元格必须存成文本类型（左上角会有绿色小三角警告，提示"以文本形式存储的数字"）。

B5 写文本 '00123'，B7 写数值 789。openpyxl 读取：

print(repr(ws['B5'].value), type(ws['B5'].value).__name__)  # '00123' str
print(repr(ws['B7'].value), type(ws['B7'].value).__name__)  # 789 int

pandas 的行为取决于 dtype 参数——默认会把看起来像数字的列转成数值型，前导零丢失：

df = pd.read_excel('four_pitfalls.xlsx', header=None, skiprows=4, nrows=3, usecols=[1])
print(df.dtypes[0])  # int64（前导零丢了）
df2 = pd.read_excel('four_pitfalls.xlsx', header=None, skiprows=4, nrows=3,
                    usecols=[1], dtype={1: str})
print(df2.iloc[0, 0])  # '00123'（保留了）

如果源文件是别人发的，先检查那一列是文本还是数值——openpyxl 看 cell.data_type（'s' 是字符串，'n' 是数值）；pandas 在 read_excel 里用 dtype 参数强制指定列为 str。如果是自己生成文件，编号列一律写成文本（Excel 里先设单元格格式为"文本"再输入，或输入时前面加单引号 '00123）。

这个坑在身份证号、邮政编码、产品编号列特别常见。如果源文件已经丢了前导零（存成了数值），Python 读出来也没法恢复——只能找源系统重新导出，导出时把那一列设成文本格式。

公式与缓存值：data_only 参数的两面

Excel 单元格里可以同时存两样东西：公式字符串（如 =B8+C8）和公式算出来的缓存值。缓存值是 Excel 上次打开并计算时留下的——如果文件没被 Excel 打开过，缓存值就是空的。

openpyxl 的 load_workbook 有个 data_only 参数控制读哪个：

• data_only=False（默认）：读公式字符串
• data_only=True：读缓存值

D8 写公式 =B8+C8（B8=10, C8=20，缓存值应为 30）。但这份文件是 openpyxl 造的，Excel 没算过，所以缓存值是空的：

wb_false = load_workbook('four_pitfalls.xlsx', data_only=False)
print(repr(wb_false.active['D8'].value))  # '=B8+C8'（公式字符串）

wb_true = load_workbook('four_pitfalls.xlsx', data_only=True)
print(repr(wb_true.active['D8'].value))  # None（缓存值不存在）

如果这份文件是用 Excel 打开并保存过的，缓存值就会存在，data_only=True 能读到 30。但如果是 Python 生成的、或者别人改了公式但没重新打开 Excel，缓存值就是 None 或旧值。

pandas 的 read_excel 没有这个参数——它总是读"值"（公式的话就是缓存值，没有就 NaN）：

df = pd.read_excel('four_pitfalls.xlsx', header=None, skiprows=7, nrows=2, usecols=[3])
print(df.iloc[0, 0])  # None（缓存值不存在）

如果源文件是别人用 Excel 维护的、公式已经算过，用 data_only=True 读缓存值最省事；如果源文件是 Python 生成的、或者你需要知道公式本身（比如要审计），用 data_only=False 读公式字符串。最稳的做法是两遍都读——第一遍 data_only=False 拿公式，第二遍 data_only=True 拿缓存值，对比一下看缓存是不是旧的。

wb_formulas = load_workbook('four_pitfalls.xlsx', data_only=False)
wb_values = load_workbook('four_pitfalls.xlsx', data_only=True)
for row in ws_formulas.iter_rows():
    for cell in row:
        formula = cell.value
        value = wb_values.active[cell.coordinate].value
        if str(formula).startswith('=') and value is None:
            print(f'{cell.coordinate}: 公式 {formula} 无缓存值')

这个坑在财务报表、自动计算的模板里特别常见。如果源文件是系统导出的（如 ERP、CRM），导出引擎通常会写入缓存值，data_only=True 能读到；如果是同事手工维护的模板、公式引用了其他工作表或外部数据，缓存值可能是旧的甚至空的。站内 file-hash 工具页处理文件列表时也会遇到类似问题：文件名里的前导零如果被系统重命名，哈希值对不上，只能回溯到导出环节解决。

1000 行 × 10 列的 xlsx 读多快？

造一份 1000 行 × 10 列、每格写 行号×列号 的 xlsx，55,715 B（54.4 KB）。openpyxl 两种模式：

pandas read_excel 读同一份文件约 85 ms（含 DataFrame 构建开销）。对于 10 MB 以上的 xlsx，openpyxl 的 read_only=True 是唯一不爆内存的选项；pandas 可以用 engine='openpyxl' 并配合 usecols 只读需要的列来降内存。

如果文件特别大（几十万行），考虑换成 CSV 格式——CSV 没有合并单元格、没有公式、没有格式掩码，读取速度比 xlsx 快一个数量级，站内 csv-viewer 工具页就是纯前端解析 CSV，10 MB 文件在浏览器里也能秒开。但 CSV 的代价是丢失所有格式信息（日期、前导零、公式），所以选格式要看场景：要保留格式用 xlsx，要速度和兼容性用 CSV。

四个坑的速查表

常见问题

为什么 openpyxl 读出来的日期和 Excel 里显示的不一样？

Excel 的日期本质是整数加格式掩码，openpyxl 会根据 number_format 自动转成 datetime 对象。如果显示不一致，通常是时区问题——Excel 不存时区，openpyxl 转出来的是 naive datetime（无时区信息）。如果需要特定时区，用 pytz 或 zoneinfo 手动附加。

pandas 读 Excel 时如何保留所有列的原始数据类型？

在 read_excel 里传 dtype=object，所有列都会读成字符串，保留原始格式（包括前导零、日期文本）。后续需要数值计算时再逐列转换。缺点是失去类型推断的便利，需要自己处理 NaN 和类型转换。

公式列读出来全是 None，怎么办？

说明文件没被 Excel 计算过，缓存值不存在。两个解法：一是用 data_only=False 读公式字符串，自己用 Python 重算（简单公式可以用 eval 或 asteval 库）；二是用 xlwings 调用本地 Excel 进程打开文件并计算，再读缓存值。后者需要 Windows + Excel 授权。

本文数据核验于 2026-11，以 openpyxl 与 pandas 官方文档为准。