🛡️ 用 Python 构建智能 Excel 数据清洗器（逐步指南）

Source: Dev.to

请提供您希望翻译的文章正文内容，我将为您翻译成简体中文并保持原有的格式、Markdown 语法以及技术术语不变。

您将构建的内容

• 一个用于 Excel 清理的桌面 GUI 应用程序
• 自动缺失值处理
• 重复检测
• 启发式“数据健康”评分
• 将结果导出为 Excel、PDF、JSON 和 TXT

GitHub 仓库（完整脚本）：
👉

🧰 前置条件

• Python 3.9+
• 基础的 Python 知识

安装所需的包

pip install pandas numpy openpyxl ttkbootstrap reportlab

📁 项目结构

SmartExcelGuardian/
│── main.py
│── logo.ico
│── excelguardian.log

1️⃣ 导入所需库

# Core
import os
import sys
import threading
import json
import tkinter as tk
from tkinter import filedialog

# UI
import ttkbootstrap as tb
from ttkbootstrap.constants import *

# Misc
from datetime import datetime

为什么需要这些模块？

# Data & Excel
import pandas as pd
import numpy as np
import re
from openpyxl import Workbook
from openpyxl.styles import PatternFill, Font
from openpyxl.utils.dataframe import dataframe_to_rows

# PDF Export
from reportlab.pdfgen import canvas
from reportlab.lib.pagesizes import A4
from reportlab.lib.units import mm
from reportlab.lib.colors import red, orange, green, black

2️⃣ 全局状态与日志记录

stop_event      = threading.Event()          # Allows canceling cleanup
cleanup_results = {}                         # Shared export data
log_file        = os.path.join(os.getcwd(), "excelguardian.log")

3️⃣ 实用辅助函数

资源加载器（用于打包的应用）

def resource_path(file_name):
    """Return absolute path for bundled resources (PyInstaller support)."""
    base_path = getattr(sys, "_MEIPASS", os.path.dirname(os.path.abspath(__file__)))
    return os.path.join(base_path, file_name)

列名清理器

def clean_column_name(name):
    """Normalize column names: strip, lower‑case, remove punctuation, replace spaces with '_'."""
    name = name.strip().lower()
    name = re.sub(r"[^\w\s]", "", name)   # Remove non‑alphanumeric chars
    name = re.sub(r"\s+", "_", name)      # Replace spaces with underscores
    return name

示例

NumPy → JSON 转换器

def convert_numpy(obj):
    """Make NumPy types JSON‑serialisable."""
    if isinstance(obj, np.integer):
        return int(obj)
    if isinstance(obj, np.floating):
        return float(obj)
    if isinstance(obj, np.ndarray):
        return obj.tolist()
    raise TypeError(f"Object of type {type(obj)} is not JSON serialisable")

4️⃣ 创建主窗口

app = tb.Window(themename="darkly")
app.title("SmartExcelGuardian v1.1.0")
app.geometry("1100x650")

为什么 ttkbootstrap？

• 开箱即用的现代样式
• 内置暗模式支持
• 响应式布局助手

5️⃣ 标题部分

tb.Label(app,
         text="SmartExcelGuardian",
         font=("Segoe UI", 22, "bold")).pack(pady=(10, 2))

tb.Label(app,
         text="Professional Excel Data Guardian Tool",
         font=("Segoe UI", 10, "italic"),
         foreground="#9ca3af").pack(pady=(0, 8))

创建应用程序标题。

6️⃣ Excel 文件选择器

file_path = tk.StringVar()

# Row container (you’ll need to create `row1` as a Frame first)
tb.Entry(row1,
        textvariable=file_path,
        width=60).pack(side="left", padx=6)

tb.Button(row1,
          text="📄 Excel File",
          command=lambda: file_path.set(
              filedialog.askopenfilename(
                  filetypes=[("Excel Files", "*.xlsx *.xls")]
              )
          )).pack(side="left")

允许用户选择一个 Excel 工作簿。

7️⃣ 清理控制按钮

start_btn = tb.Button(row2,
                     text="🛡 CLEAN DATA",
                     bootstyle="success")

stop_btn = tb.Button(row2,
                    text="🛑 STOP",
                    bootstyle="danger-outline",
                    state="disabled")

• CLEAN DATA → 启动一个后台线程运行清理引擎。
• STOP → 安全地停止处理。

8️⃣ 结果表 (Treeview)

cols = (
    "column", "original_type", "suggested_type",
    "cleaned_type", "missing_values",
    "duplicates_detected", "heuristic_score",
    "rename_suggestion"
)

tree = tb.Treeview(row3, columns=cols, show="headings")

显示逐列的健康分析。

9️⃣ 启发式评分系统

def heuristic_score(missing, duplicates, type_issue):
    """Return a risk score from 0‑100."""
    score = 0
    score += min(30, missing * 2)          # Missing values (max 30)
    score += min(30, duplicates * 2)       # Duplicates (max 30)
    score += 40 if type_issue else 0       # Type‑mismatch (max 40)
    return min(score, 100)

🔟 数据清洗引擎

def assess_and_clean(df):
    """Iterate over columns, assess health, and clean data in‑place."""
    for col in df.columns:
        series = df[col]

        # ---------- Numeric Columns ----------
        coerced = pd.to_numeric(series, errors="coerce")
        if coerced.notna().any():                     # At least one numeric value
            cleaned_series = coerced.fillna(coerced.mean())
            df[col] = cleaned_series
            continue

        # ---------- Text Columns ----------
        cleaned_series = series.astype("string").fillna(series.mode()[0])
        df[col] = cleaned_series

关键操作

• 数值列 → 强制转换为数字，使用列均值填充缺失值。
• 文本列 → 转换为字符串类型，使用出现频率最高的值（众数）填充缺失值。

后续步骤（摘录未显示）

• 用启发式评分和重命名建议填充 Treeview。
• 将 CLEAN DATA 按钮绑定，以在后台线程中启动 assess_and_clean。
• 使用上述定义的辅助工具实现 Excel、PDF、JSON 和 TXT 的导出功能。
• 为 excelguardian.log 添加适当的错误处理和日志记录。

祝清洗愉快！ 🎉

1️⃣ 线程化清理执行

threading.Thread(
    target=run_cleanup,
    daemon=True
).start()

为什么使用线程？

• 保持 UI 响应
• 防止在大型 Excel 文件上冻结

2️⃣ 带公式的 Excel 导出

sum_formula  = f"=SUM(A2:A{ws.max_row})"
mean_formula = f"=AVERAGE(A2:A{ws.max_row})"

自动添加：

• SUM
• AVERAGE

到数值列。

3️⃣ 条件格式化

fill = PatternFill(start_color="FF9999", fill_type="solid")
cell.font = Font(bold=True)

高风险列包括：

• 已高亮 🔴
• 加粗以提升可见性

4️⃣ PDF 报告导出

def score_color(score):
    if score >= 71:
        return red
    elif score >= 31:
        return orange
    else:
        return green

创建一个多页 PDF 审计报告，包含：

• 颜色编码的分数
• 列汇总
• 页码

5️⃣ 关于 & 帮助 窗口

tb.Label(frame, text="How to Use", font=("Segoe UI", 12, "bold"))

提供：

• 功能概览
• 使用步骤
• 开发者信息

🚀 最终结果

您现在拥有：

• 专业的 Excel 清理工具
• 桌面 GUI
• 启发式评分系统
• 多格式导出

📌 下一步改进

• 添加每个工作表的选择
• 添加图表（数据健康趋势）
• 保存用户预设
• 打包为 .exe

🔗 完整源代码

SmartExcelGuardian on GitHub