Excel 数据清洗四合一：去空格、修手机号、拆列、去空行

2026-10-04 · 喵窝资源铺

别人发来的 Excel 十有八九是"脏"的：姓名前面挂着看不见的空格、手机号是 138-0013-9000 或 13900139001.0、"北京市,朝阳区,xx路" 挤在一个单元格里、末尾还有几行看似空白实则藏了空格的幽灵行。手工 Ctrl+H 清一下午，不如 60 行脚本跑一秒。这篇给一个 只依赖 openpyxl 的清洗脚本，四件事一次做完。文中代码实跑验证，真实输出见下。

完整脚本

存为 excel_clean4.py，运行：

python excel_clean4.py 输入.xlsx 输出.xlsx 手机号 地址 ","

第 3~5 个参数可省略：不填列名就只做空格清理和去空行；分隔符默认逗号。

# -*- coding: utf-8 -*-
"""Excel清洗四合一：去首尾空格、手机号格式化、按分隔符拆列、去全空行
用法：python excel_clean4.py 输入.xlsx 输出.xlsx [手机号列名] [拆分列名] [分隔符]
依赖：pip install openpyxl（只此一个）"""
from openpyxl import load_workbook, Workbook
import sys

src, dst = sys.argv[1], sys.argv[2]
phone_col = sys.argv[3] if len(sys.argv) > 3 else None
split_col = sys.argv[4] if len(sys.argv) > 4 else None
sep = sys.argv[5] if len(sys.argv) > 5 else ","

wb = load_workbook(src)
ws = wb.active
header = [c.value for c in ws[1]]
pi = header.index(phone_col) if phone_col and phone_col in header else None
si = header.index(split_col) if split_col and split_col in header else None

out = Workbook()
o = out.active
if si is not None:
    header = header[:si+1] + [f"{split_col}-部分{i+1}" for i in range(3)] + header[si+1:]
o.append(header)

stats = {"trim":0,"phone":0,"empty":0,"split":0}
for row in ws.iter_rows(min_row=2, values_only=True):
    if row is None or not any(str(v).strip() for v in row if v is not None):
        stats["empty"] += 1; continue
    row = list(row)
    for i,v in enumerate(row):
        if isinstance(v,str) and v != v.strip():
            row[i] = v.strip(); stats["trim"] += 1
    if pi is not None and pi < len(row):
        v = str(row[pi] or "").replace(".0","").replace("-","").replace(" ","")
        if len(v)==11 and v.startswith("1"):
            row[pi] = v
            stats["phone"] += 1
    if si is not None and si < len(row) and row[si]:
        parts = str(row[si]).split(sep)
        parts += [""]*(3-len(parts))
        row = row[:si+1] + parts[:3] + row[si+1:]
        stats["split"] += 1
    o.append(row)
out.save(dst)
print(f"清洗完成: 去空格{stats['trim']}处 手机号修{stats['phone']}个 拆列{stats['split']}行 跳过空行{stats['empty']} -> {dst}")

实跑结果

构造了一份典型的脏数据（3 条记录 + 1 行"空白实为空格"的幽灵行）实际运行：

清洗完成: 去空格3处 手机号修3个 拆列3行 跳过空行1 -> /tmp/clean.xlsx

('姓名',   '手机号',      '地址',            '地址-部分1', '地址-部分2', '地址-部分3')
('张三',   '13800139000', '北京市,朝阳区,xx路', '北京市',    '朝阳区',    'xx路')
('李四',   '13900139001', '上海市',           '上海市',    None,        None)
('王五',   '13800139002', '广州市,天河区',     '广州市',    '天河区',    None)

三种脏手机号（带横杠、带 .0 后缀、两端带空格）全部修正为 11 位标准格式；幽灵空行被正确识别并跳过；地址按逗号拆成 3 列，不足自动补空。

踩坑实录：这脚本第一版就有 2 个 bug

• 幽灵空行：最初用 any(v not in (None,"")) 判空行，结果 " "（一个空格）被当成有效数据混进结果。正确写法是 any(str(v).strip() ...)——判断"内容"之前先剥空格。
• .0 后缀：Excel 里手机号列如果是数字格式，openpyxl 读出来是 13900139001.0（浮点）。直接 str() 就带尾巴，所以脚本里先 replace(".0","")。根治办法是让发件人把该列设为文本格式，但你没法要求所有人。

这也是本店接单的真实流程：每个脚本都在真实数据上跑过、把第一版的坑填掉，才交付。虚拟物品，售出不退，但每个成品都有实跑记录背书。

相关文章：Excel 清洗脚本合集 · CSV 批量合并去重 · Excel 按关键字段保留最新一条