编辑代码

import tkinter as tk
from tkinter import filedialog, messagebox
import pandas as pd
import numpy as np
import os

# 常量定义
REQUIRED_COLUMNS = ['学校', '班级', '姓名', '语文', '数学', '英语', 
                   '科学', '德育', '音乐', '美术', '劳动', '心理健康']
SUBJECTS = ['语文', '数学', '英语', '科学', '德育', '音乐', '美术', '劳动', '心理健康']

def select_files():
    file_paths = filedialog.askopenfilenames(
        title='选择数据文件',
        filetypes=[("Excel文件", "*.xls *.xlsx")]
    )
    if file_paths:
        global selected_files
        selected_files = file_paths
        file_list_label.config(text=f"已选择 {len(file_paths)} 个文件")

def process_data():
    if not selected_files:
        messagebox.showerror("错误", "请先选择文件！")
        return

    # 合并数据
    merged_df = pd.DataFrame()
    missing_col_files = []
    
    for file in selected_files:
        try:
            df = pd.read_excel(file, header=0)
            missing_cols = [col for col in REQUIRED_COLUMNS if col not in df.columns]
            if missing_cols:
                missing_col_files.append(f"{os.path.basename(file)} 缺少列：{', '.join(missing_cols)}")
                continue
            merged_df = pd.concat([merged_df, df[REQUIRED_COLUMNS]], ignore_index=True)
        except Exception as e:
            missing_col_files.append(f"{os.path.basename(file)} 读取失败：{str(e)}")
    
    # 数据清洗
    merged_df = merged_df.dropna(subset=['姓名'])
    for col in SUBJECTS:
        merged_df[col] = pd.to_numeric(merged_df[col], errors='coerce')

    # 保存合并结果
    merged_df.to_excel("合并表.xlsx", index=False)

    # 计算统计指标
    def calculate_stats(df):
        stats = []
        for subject in SUBJECTS:
            valid_data = df[subject].dropna()
            count = len(valid_data)
            mean = round(valid_data.mean(), 2)
            std = round(valid_data.std(), 2)
            cv = round((std / abs(mean)) * 100, 2) if mean != 0 else np.nan
            stats.append([count, mean, std, cv])
        return stats

    # 全区统计
    region_stats = calculate_stats(merged_df)
    region_df = pd.DataFrame(region_stats, 
                            columns=['统计人数', '平均分', '标准差', '差异系数'],
                            index=SUBJECTS).reset_index().rename(columns={'index':'科目'})
    region_df.insert(0, '学校', '全区')

    # 学校统计
    school_dfs = []
    for school, group in merged_df.groupby('学校'):
        stats = calculate_stats(group)
        school_df = pd.DataFrame(stats,
                                columns=['统计人数', '平均分', '标准差', '差异系数'],
                                index=SUBJECTS).reset_index().rename(columns={'index':'科目'})
        school_df.insert(0, '学校', school)
        school_dfs.append(school_df)

    # 合并所有统计结果
    final_stats = pd.concat([region_df] + school_dfs, ignore_index=True)

    # 保存统计结果
    with pd.ExcelWriter("统计结果.xlsx") as writer:
        final_stats.to_excel(writer, sheet_name='统计结果', index=False)

    # 显示完成信息
    msg = "处理完成！\n\n结果文件已保存为：\n- 合并表.xlsx\n- 统计结果.xlsx"
    if missing_col_files:
        msg += "\n\n以下文件存在问题：\n" + "\n".join(missing_col_files)
    messagebox.showinfo("处理完成", msg)

# 创建UI界面
root = tk.Tk()
root.title("学生成绩统计工具")
root.geometry("400x200")

selected_files = []

tk.Label(root, text="学生成绩统计工具", font=('Arial', 14)).pack(pady=10)
file_list_label = tk.Label(root, text="已选择 0 个文件")
file_list_label.pack()

button_frame = tk.Frame(root)
button_frame.pack(pady=10)

tk.Button(button_frame, text="选择文件", command=select_files).pack(side=tk.LEFT, padx=5)
tk.Button(button_frame, text="开始处理", command=process_data).pack(side=tk.LEFT, padx=5)

root.mainloop()