如何对于单元格数据进行清洗处理

如何对于单元格数据进行清洗处理

陪伴意味着有人愿意把最美好的东西给你，
那就是时间。
当然陪伴也是一个很平常的事情，
日复一日，年复一年。
到最后陪伴就成了一种习惯。
约定好的相逢，伴你天荒地老！
陪伴是最长情的告白！
——董卿 《朗读者》

需求分析

遇到好朋友提出这样一个帮忙的需求，主要功能是对Excel (.xlsx) 文件中的特定表格数据进行处理，并根据不同需求生成SQL更新脚本或保存处理后带有更新语句的新Excel文件。以下是详细的需求分析：

1. 功能需求：

   • 用户通过点击GUI上的按钮，可以选择需要处理的Excel文件。
   • 选定的Excel文件应该包含几列特定的数据，如“修改后的支撑人员名单”、“修改后的服务内容”和“修改后的输出成果物”，以及关联主键“did”。
   • 应用程序读取Excel文件并将数据加载至Pandas DataFrame中。
   • 对DataFrame的列名进行清理，去除额外的空格。
   • 根据DataFrame中的每个数据行生成相应的SQL更新语句，更新的目标表为SQL Server数据库中的[T_ZqProjectSatisfactionSurvey]表，更新字段与源Excel文件的特定列对应。
   • 用户可以选择两种不同的操作：
     a. 保存处理后的Excel文件：在原Excel文件基础上添加一列“修改语句”，记录生成的SQL更新语句，然后将整个带有更新语句的新DataFrame保存为新的Excel文件。
     b. 生成单独的SQL脚本文件：将所有生成的SQL更新语句写入一个单独的SQL文件，便于直接执行或者导入数据库进行批量更新。

2. 非功能性需求：

   • 界面友好：通过Tkinter提供的图形界面引导用户完成文件选择操作，同时有明确的操作提示信息。
   • 数据兼容性：仅支持读取和处理Excel文件(.xlsx格式)。
   • 错误处理：虽然代码未明确提及错误处理机制，但实际应用中应对文件读取失败、列名不存在或格式不符等情况作出适当处理。

3. 操作流程：

   1. 用户启动应用程序，看到一个窗口，窗口上有两个按钮：“请选择需要处理的文件”和“生成单独的SQL脚本”。
   2. 用户点击“请选择需要处理的文件”按钮后，弹出文件选择对话框，用户选择一个Excel文件。
   3. 根据用户的选择，应用程序将读取Excel文件，生成SQL更新语句，并按用户选择的操作类型执行相应功能（保存处理后的Excel文件或生成SQL脚本文件）。
   4. 系统会反馈给用户操作结果，告知其文件保存的路径或SQL脚本文件的生成路径。

核心源码

import pandas as pd
from tkinter import Tk, filedialog, Button
import warnings
warnings.filterwarnings
def select_file():
    # 创建 Tkinter 窗口
    root = Tk()
    root.withdraw()  # 隐藏 Tkinter 窗口

    # 弹出文件选择对话框
    file_path = filedialog.askopenfilename(filetypes=[("Excel files", "*.xlsx")])

    # 读取 Excel 文件
    data_origin = pd.read_excel(file_path)

    # 关闭 Tkinter 窗口
    root.destroy()

    # 返回 DataFrame 和文件路径
    return data_origin, file_path

def generate_sql_script(df, file_path):
    # 确保列名没有额外的空格或其他字符
    df.columns = df.columns.str.strip()

    # 生成 SQL 查询语句
    sql_script = df.apply(lambda row: f"""
        UPDATE [dbo].[T_ZqProjectSatisfactionSurvey]
        SET
            Zpss_SupportUser = '{str(row['修改后的支撑人员名单']).strip()}',
            Zpss_ServiceContent = '{str(row['修改后的服务内容']).strip()}',
            Zpss_InputResult = '{str(row['修改后的输出成果物']).strip()}'
        WHERE
            Zpss_id = {str(row['did']).strip().split('.')[0]};
    """, axis=1)

    # 将 SQL 脚本保存到文件
    script_file_path = file_path.replace(".xlsx", "_单独脚本.sql")
    with open(script_file_path, 'w', encoding='utf-8') as script_file:
        script_file.write('\n'.join(sql_script))

    print(f"SQL 脚本已保存到: {script_file_path}")

def process_data_and_save(df, file_path):
    # 确保列名没有额外的空格或其他字符
    df.columns = df.columns.str.strip()

    # 生成 SQL 查询语句并存储在 '修改语句' 列
    df['修改语句'] = df.apply(lambda row: f"""
        UPDATE [dbo].[T_ZqProjectSatisfactionSurvey]
        SET
            Zpss_SupportUser = '{str(row['修改后的支撑人员名单']).strip()}',
            Zpss_ServiceContent = '{str(row['修改后的服务内容']).strip()}',
            Zpss_InputResult = '{str(row['修改后的输出成果物']).strip()}'
        WHERE
            Zpss_id = {str(row['did']).strip().split('.')[0]};
    """, axis=1)

    # 输出结果
    print(df['修改语句'])

    # 保存处理后的 DataFrame 到新文件
    processed_file_path = file_path.replace(".xlsx", "_处理后.xlsx")
    df.to_excel(processed_file_path, index=False)
    print(f"处理后的数据已保存到: {processed_file_path}")

# 创建 Tkinter 窗口
root = Tk()
root.title("表格自动化处理")  # 设置窗口标题

# 设置窗口大小
root.geometry("400x200")

# 创建按钮
select_button = Button(root, text="请选择需要处理的文件", command=lambda: process_data_and_save(*select_file()), bg="blue", fg="black")
select_button.pack(pady=10)

generate_sql_button = Button(root, text="生成单独的SQL脚本", command=lambda: generate_sql_script(*select_file()), bg="green", fg="black")
generate_sql_button.pack(pady=10)

# 运行 Tkinter 事件循环
root.mainloop()

这段Python代码实现了一个基于Tkinter的简单桌面应用程序，用于从Excel文件中提取数据，并根据数据生成SQL更新脚本或更新后的Excel文件。具体来说，该程序包含以下几个部分：

1. 导入所需库：

   • pandas 用于数据处理，尤其是读取和写入Excel文件。
   • Tkinter 是Python自带的标准GUI库，用于创建图形用户界面。
   • filedialog 是Tkinter的一个模块，用于弹出文件选择对话框。

2. 定义函数：

   • select_file() 函数负责打开一个文件选择对话框让用户选择Excel文件，读取选定的Excel文件内容并将其转换为Pandas DataFrame，然后返回这个DataFrame以及文件路径。
   • generate_sql_script(df, file_path) 函数接收DataFrame和文件路径作为参数，对DataFrame的列名进行清理，然后为DataFrame中的每一行生成对应的SQL UPDATE语句，并将这些语句写入一个单独的SQL脚本文件中，文件名是在原Excel文件名的基础上添加了“_单独脚本.sql”后缀。
   • process_data_and_save(df, file_path) 函数也接收DataFrame和文件路径，同样对列名进行清理，但是它不是生成单独的SQL脚本文件，而是将生成的SQL更新语句添加到DataFrame的一列中（列名为“修改语句”），然后将这个包含了SQL更新语句的新DataFrame保存为一个新的Excel文件，文件名是在原Excel文件名基础上添加了“_处理后.xlsx”后缀。

3. GUI构建：

   • 创建Tkinter窗口实例，并设置窗口标题为“表格自动化处理”。
   • 设置窗口大小为400x200像素。
   • 创建两个按钮：
     • “请选择需要处理的文件”按钮，点击时触发process_data_and_save()函数处理数据并保存处理后的新Excel文件。
     • “生成单独的SQL脚本”按钮，点击时触发generate_sql_script()函数生成SQL更新脚本并保存到文件中。

4. 启动Tkinter的事件循环，使窗口保持运行状态，直到被关闭。

整个应用程序的主要目的是帮助用户快速地基于Excel表格中的数据生成针对特定数据库表（在这个例子中是T_ZqProjectSatisfactionSurvey）的UPDATE SQL语句，并提供了两种方式来保存这些生成的语句，一种是内嵌到一个新的Excel文件中，另一种是以单独的SQL脚本文件形式存在。

效果展示

处理前表格数据

处理后的表格数据

sql脚本文件