Wps data clean

WPS 数据清洗工具

一键清洗杂乱的表格数据，让脏数据变成可用数据。

When to Use

• 表格数据有大量重复、空行、格式不统一
• 手机号、身份证号、日期格式混乱
• 需要合并/拆分单元格内容
• 从外部系统导出的数据需要整理
• 用户说"帮我清洗/整理这个表格"

When NOT to Use

• 需要写公式 → 使用 wps-formula
• 需要数据分析/透视 → 使用 wps-pivot
• 需要图表可视化 → 使用 wps-chart

清洗能力清单

✅ 基础清洗

操作	说明	适用场景
去重	按指定列删除重复行	导入数据有重复记录
去空行	删除全空行或指定列为空的行	数据中间有空行
去空格	去除首尾空格、多余空格	文本前后有隐藏空格
去特殊字符	清除不可见字符、换行符	从网页/PDF复制的数据
统一大小写	全大写/全小写/首字母大写	英文数据不统一
繁简转换	繁体中文→简体中文	港台数据导入

✅ 中国特色数据标准化

数据类型	清洗规则	示例
手机号	去空格/横杠，补+86，验证11位	138-1234-5678 → 13812345678
身份证号	去空格，验证18/15位，校验末位	110101 1990 0101 001X → 11010119900101001X
日期	统一为 YYYY-MM-DD 或 YYYY年M月D日	2024/3/5 2024.03.05 → 2024-03-05
金额	去逗号/¥/元，统一为数值	¥1,234.56元 → 1234.56
性别	统一为 男/女	M/F male/female 1/0 → 男/女
省市区	补全省市区层级	朝阳区 → 北京市朝阳区
姓名	去空格，去称呼后缀	张 三先生 → 张三
邮箱	去空格，转小写，验证格式	[email protected] → [email protected]

✅ 结构性清洗

操作	说明
拆分列	一列拆多列（如姓名→姓+名，地址→省+市+区）
合并列	多列合一列（如姓+名→姓名）
取消合并单元格	合并单元格→填充每个子单元格
转置	行列互换
宽表转长表	多列月份→日期+值的两列
长表转宽表	日期+值→多列月份

工作流程

Step 1: 诊断数据问题

如果用户提供了文件：

pip install openpyxl 2>/dev/null || pip3 install openpyxl 2>/dev/null

读取文件并输出数据诊断报告：

📊 数据诊断报告
═══════════════════════════════════
文件：[文件名]
工作表：[Sheet名]
行数：[X] 行（含表头）
列数：[X] 列

🔍 发现的问题：
┌─────────────────────────────────┐
│ ⚠️  重复行：XX 行（占比 XX%）     │
│ ⚠️  空行：XX 行                  │
│ ⚠️  A列有 XX 个空值              │
│ ⚠️  B列(手机号)格式不统一：XX 个   │
│ ⚠️  C列(日期)格式混乱：XX 个      │
│ ✅  D列(金额)格式正常             │
└─────────────────────────────────┘

📋 建议清洗操作：
1. 删除 XX 行完全重复数据
2. 删除 XX 行空行
3. 标准化手机号为11位数字
4. 统一日期格式为 YYYY-MM-DD

Step 2: 确认清洗方案

向用户展示诊断报告，确认要执行的清洗操作。

Step 3: 执行清洗

根据场景选择技术路线：

场景	技术路线	输出
有 .xlsx 文件在磁盘上	openpyxl脚本	清洗后的新 .xlsx 文件
用户在WPS中打开了表格	JSA宏	粘贴到WPS宏编辑器运行
仅描述需求，无文件	JSA宏模板	.js文件供用户使用

openpyxl 清洗脚本模式：

import openpyxl
import re
from copy import copy

def clean_spreadsheet(input_path, output_path=None):
    wb = openpyxl.load_workbook(input_path)
    ws = wb.active

    if not output_path:
        output_path = input_path.replace('.xlsx', '_cleaned.xlsx')

    stats = {'removed_duplicates': 0, 'removed_blanks': 0,
             'fixed_phones': 0, 'fixed_dates': 0}

    # === 去重 ===
    seen = set()
    rows_to_delete = []
    for row_idx in range(2, ws.max_row + 1):  # 跳过表头
        row_data = tuple(ws.cell(row=row_idx, column=c).value
                        for c in range(1, ws.max_column + 1))
        if row_data in seen:
            rows_to_delete.append(row_idx)
            stats['removed_duplicates'] += 1
        else:
            seen.add(row_data)

    # 从后往前删除（避免索引偏移）
    for row_idx in reversed(rows_to_delete):
        ws.delete_rows(row_idx)

    # === 去空行 ===
    rows_to_delete = []
    for row_idx in range(2, ws.max_row + 1):
        if all(ws.cell(row=row_idx, column=c).value is None
               for c in range(1, ws.max_column + 1)):
            rows_to_delete.append(row_idx)
            stats['removed_blanks'] += 1
    for row_idx in reversed(rows_to_delete):
        ws.delete_rows(row_idx)

    # === 清洗各列 ===
    # 根据实际列内容动态清洗

    wb.save(output_path)
    return output_path, stats

手机号标准化函数：

def clean_phone(value):
    """标准化中国大陆手机号"""
    if value is None:
        return value
    s = re.sub(r'[\s\-\+\(\)（）]', '', str(value))
    s = re.sub(r'^86', '', s)
    s = re.sub(r'^\+86', '', s)
    if re.match(r'^1[3-9]\d{9}$', s):
        return s
    return str(value)  # 无法识别的保留原值

身份证号标准化函数：

def clean_id_card(value):
    """标准化身份证号"""
    if value is None:
        return value
    s = re.sub(r'\s', '', str(value)).upper()
    if re.match(r'^\d{17}[\dX]$', s):
        return s
    if re.match(r'^\d{15}$', s):
        return s  # 一代身份证
    return str(value)

日期标准化函数：

def clean_date(value, output_format='%Y-%m-%d'):
    """统一日期格式"""
    if value is None:
        return value
    if isinstance(value, datetime):
        return value.strftime(output_format)
    s = str(value).strip()
    formats = [
        '%Y-%m-%d', '%Y/%m/%d', '%Y.%m.%d',
        '%Y年%m月%d日', '%d/%m/%Y', '%m/%d/%Y',
        '%Y%m%d', '%d-%m-%Y',
    ]
    for fmt in formats:
        try:
            return datetime.strptime(s, fmt).strftime(output_format)
        except ValueError:
            continue
    return s  # 无法解析的保留原值

Step 4: 输出结果

✅ 清洗完成！
═══════════════════════════════════
输入文件：data.xlsx（1000行）
输出文件：data_cleaned.xlsx（856行）

📊 清洗统计：
  🗑️ 删除重复行：89 行
  🗑️ 删除空行：55 行
  📱 修复手机号：123 个
  📅 修复日期格式：67 个
  ✂️ 去除首尾空格：234 个

⚠️ 请用WPS打开 data_cleaned.xlsx 确认结果

示例

# 清洗指定文件
/wps-data-clean 清洗 sales_data.xlsx，去重并标准化手机号

# 生成清洗宏（在WPS中运行）
/wps-data-clean 写一个JSA宏，去除当前表格中的所有空行和重复行

# 描述需求
/wps-data-clean 我有一个客户表，手机号格式混乱（有的带横杠有的带+86），帮我统一

参考

中国数据格式标准见 reference/cn-data-formats.md。