Python 办公：给 Excel 做体检，批量查错并标红

同事填上来的表：手机号少一位、日期写成「2026/13/45」、金额列混进文字、必填项空白。
你眼睛扫 5000 行？写个「数据体检脚本」，问题行自动标红出报告。

一、思路

1. 1. 定义校验规则（字典/函数）。
2. 2. 遍历每行，命中规则就记录「行号 + 列 + 原因」。
3. 3. 生成错误清单 + 在原表标色。

二、核心代码

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

df = pd.read_excel("填报表.xlsx", dtype=str)   # 全字符串读，避免类型陷阱
errors = []

defcheck(row, i):
    no = i + 2# Excel 行号（含表头）
# 必填
for col in ["姓名", "部门", "金额"]:
ifnotstr(row.get(col, "")).strip():
            errors.append((no, col, "必填项为空"))
# 手机号
    phone = str(row.get("手机号", "")).strip()
if phone andnot re.fullmatch(r"1[3-9]\d{9}", phone):
        errors.append((no, "手机号", "格式不对"))
# 日期
    d = pd.to_datetime(row.get("日期"), errors="coerce")
if pd.isna(d):
        errors.append((no, "日期", "无法解析"))
# 金额
try:
float(str(row.get("金额")).replace(",", ""))
except ValueError:
        errors.append((no, "金额", "不是数字"))

for i, row in df.iterrows():
    check(row, i)

report = pd.DataFrame(errors, columns=["行号", "列", "问题"])
report.to_excel("错误清单.xlsx", index=False)
print(f"共发现 {len(report)} 处问题")

三、在原表里标红

wb = load_workbook("填报表.xlsx")
ws = wb.active
red = PatternFill("solid", fgColor="FFC7CE")
headers = [c.value for c in ws[1]]

for row_no, col_name, reason in errors:
if col_name in headers:
        c = ws.cell(row=row_no, column=headers.index(col_name) + 1)
        c.fill = red
        c.font = Font(color="9C0006")
        c.comment = None# 也可加批注写原因

wb.save("填报表_标注.xlsx")

四、常见坑

• • dtype=str 很重要：不指定的话手机号会被读成 1.38e10，身份证号精度丢失。
• • pd.to_datetime 太宽容：它把 "abc" 解析不出返回 NaT 没问题，但 "2026-13-45" 也 NaT，正好当错误。注意别用 dayfirst 混。
• • 空值 NaN 判断：row.get(col) 可能是 float('nan')，str(nan) 是 "nan" 不是空字符串，要先 pd.isna 判断。
• • 行号偏移：pandas 的 i 从 0 开始且不含表头，Excel 行号是 i + 2。

五、进阶：跨表核对（查漏）

all_emp = pd.read_excel("花名册.xlsx")["工号"].astype(str)
submitted = df["工号"].astype(str)
missing = set(all_emp) - set(submitted)
print("未提交的工号：", sorted(missing))

谁没交表，一秒查出来，比在群里 @ 全体成员有用。

六、进阶：输出成好看的 HTML 报告

html = report.style.apply(lambda s: ["color:red"]*len(s), axis=1).to_html()
open("体检报告.html", "w", encoding="utf-8").write(html)

小结

数据校验的核心不是代码，是「规则清单」。把规则写全，脚本能替你把 80% 的脏数据挡在门外。