import pandas as pd import numpy as np from pathlib import Path import openpyxl class GradeProcessor: def __init__(self): """初始化成绩处理器""" # 定义成绩计算规则 self.scoring_rules = { '平时作业1': ['work1', 'work2'], # work1和work2求和 '平时作业2': ['work3', 'work4', 'work5'] # work3、work4、work5求和 } def convert_xlsx_to_csv(self): """前置操作：将Excel文件转换为CSV文件""" try: # 转换ds_1_2.xlsx到CSV df_12 = pd.read_excel('data/ds_1_2.xlsx', sheet_name='c1_2') df_12.to_csv('data/ds_1_2.csv', index=False, encoding='utf-8-sig') # 转换ds_3_4.xlsx到CSV df_34 = pd.read_excel('data/ds_3_4.xlsx', sheet_name='01-绪论') df_34.to_csv('data/ds_3_4.csv', index=False, encoding='utf-8-sig') print("✓ Excel文件转换完成") return True except Exception as e: print(f"✗ Excel转换失败：{e}") return False def extract_student_lists_from_xlsx(self): """前置操作：从原始xlsx文件中提取学生名单""" try: # 读取原始的成绩记录表 excel_file = pd.ExcelFile('data/数据结构成绩记录表(1).xlsx') # 定义sheet名称映射 sheet_mapping = { '20201-1小1': '1小1', '20201-1小2': '1小2', '20201-3小1': '3小1', '20201-3小2': '3小2' } for sheet_name, group_name in sheet_mapping.items(): if sheet_name in excel_file.sheet_names: # 读取sheet数据 df = pd.read_excel('data/数据结构成绩记录表(1).xlsx', sheet_name=sheet_name) # 只保留前两列（姓名和班级） df_output = df.iloc[:, :2] # 保存为CSV文件 output_filename = f'data/{sheet_name}.csv' df_output.to_csv(output_filename, index=False, header=False, encoding='utf-8-sig') else: print(f"✗ 未找到sheet '{sheet_name}'") print("✓ 学生名单提取完成") return True except Exception as e: print(f"✗ 学生名单提取失败：{e}") return False def read_grade_files(self): """读取平时成绩文件""" try: # 读取1、2班成绩 df_12 = pd.read_csv('data/ds_1_2.csv') # 读取3、4班成绩 df_34 = pd.read_csv('data/ds_3_4.csv') # 合并两个班级的数据 df_combined = pd.concat([df_12, df_34], ignore_index=True) print(f"✓ 成绩数据读取完成：{len(df_combined)}条记录") return df_combined except FileNotFoundError as e: print(f"✗ 文件未找到：{e}") return None except Exception as e: print(f"✗ 读取文件错误：{e}") return None def read_student_lists(self): """读取学生名单文件（添加表头）""" student_lists = {} # 定义表头 headers = ['姓名', '班级'] try: # 读取四个班级的学生名单 files = [ ('data/20201-1小1.csv', '1小1'), ('data/20201-1小2.csv', '1小2'), ('data/20201-3小1.csv', '3小1'), ('data/20201-3小2.csv', '3小2') ] total_students = 0 for file_name, group_name in files: df = pd.read_csv(file_name, header=None, names=headers) student_lists[group_name] = df total_students += len(df) print(f"✓ 学生名单读取完成：{total_students}人") return student_lists except Exception as e: print(f"✗ 读取学生名单错误：{e}") return None def calculate_scores(self, df_grades): """根据规则计算成绩""" if df_grades is None: return None # 创建结果DataFrame result_df = df_grades.copy() # 根据规则计算新的成绩列 calculated_count = 0 for rule_name, work_columns in self.scoring_rules.items(): # 检查所需列是否存在 missing_cols = [col for col in work_columns if col not in df_grades.columns] if missing_cols: print(f"⚠ 缺少列 {missing_cols}，跳过 {rule_name}") continue # 计算成绩（处理缺失值） scores = df_grades[work_columns].fillna(0).sum(axis=1) # 保留一位小数 result_df[rule_name] = scores.round(1) calculated_count += 1 print(f"✓ 成绩计算完成：{calculated_count}项") return result_df def create_final_grade_table(self, df_calculated, student_lists): """创建最终的成绩记录表""" if df_calculated is None or student_lists is None: return None # 合并所有学生名单 all_students = [] for group_name, student_df in student_lists.items(): students_with_group = student_df.copy() students_with_group['分组'] = group_name all_students.append(students_with_group) df_all_students = pd.concat(all_students, ignore_index=True) # 创建最终表格的列 final_columns = ['姓名', '班级', '分组', 'work1', 'work2', 'work3', 'work4', 'work5'] # 添加计算出的成绩列 for rule_name in self.scoring_rules.keys(): final_columns.append(rule_name) # 添加其他可能需要的列 final_columns.extend(['单选题（总分）', '多选题（总分）', '备注']) # 创建最终DataFrame final_df = pd.DataFrame(columns=final_columns) # 填充学生基本信息 final_df['姓名'] = df_all_students['姓名'] final_df['班级'] = df_all_students['班级'] final_df['分组'] = df_all_students['分组'] # 匹配成绩数据 matched_count = 0 for idx, student_name in enumerate(df_all_students['姓名']): # 在成绩数据中查找该学生 student_grade = df_calculated[df_calculated['name'] == student_name] if not student_grade.empty: matched_count += 1 # 填充work成绩 for work_col in ['work1', 'work2', 'work3', 'work4', 'work5']: if work_col in student_grade.columns: # 保留一位小数 final_df.loc[idx, work_col] = round(float(student_grade.iloc[0][work_col]), 1) if pd.notna(student_grade.iloc[0][work_col]) else 0.0 # 填充计算后的成绩 for rule_name in self.scoring_rules.keys(): if rule_name in student_grade.columns: # 保留一位小数 final_df.loc[idx, rule_name] = round(float(student_grade.iloc[0][rule_name]), 1) if pd.notna(student_grade.iloc[0][rule_name]) else 0.0 # 填充其他成绩 if '单选题（总分）' in student_grade.columns: final_df.loc[idx, '单选题（总分）'] = round(float(student_grade.iloc[0]['单选题（总分）']), 1) if pd.notna(student_grade.iloc[0]['单选题（总分）']) else 0.0 if '多选题（总分）' in student_grade.columns: final_df.loc[idx, '多选题（总分）'] = round(float(student_grade.iloc[0]['多选题（总分）']), 1) if pd.notna(student_grade.iloc[0]['多选题（总分）']) else 0.0 print(f"✓ 成绩表创建完成：{len(df_all_students)}人，匹配{matched_count}人") return final_df def save_results(self, final_df, csv_filename='data/数据结构成绩记录表_完整.csv', xlsx_filename='data/数据结构成绩记录表_最终.xlsx'): """保存最终结果：先保存完整CSV，再按分组保存到Excel的不同sheet""" if final_df is None: print("✗ 没有数据可保存") return False try: numeric_columns = ['work1', 'work2', 'work3', 'work4', 'work5', '平时作业1', '平时作业2', '单选题（总分）', '多选题（总分）'] for col in numeric_columns: if col in final_df.columns: final_df[col] = pd.to_numeric(final_df[col], errors='coerce').round(1) # 第一步：保存完整的CSV文件 final_df.to_csv(csv_filename, index=False, encoding='utf-8-sig') # 第二步：按分组保存到Excel的不同sheet，并添加总结sheet with pd.ExcelWriter(xlsx_filename, engine='openpyxl') as writer: # 按分组分别保存到不同的sheet groups = final_df['分组'].unique() for group in groups: # 筛选当前分组的数据 group_df = final_df[final_df['分组'] == group].copy() # 删除分组列（因为每个sheet代表一个分组） group_df_output = group_df.drop('分组', axis=1) # 重新排序列，确保逻辑顺序 output_columns = ['姓名', '班级', 'work1', 'work2', 'work3', 'work4', 'work5'] # 添加计算出的成绩列 for rule_name in self.scoring_rules.keys(): if rule_name in group_df_output.columns: output_columns.append(rule_name) # 添加其他列 remaining_columns = [col for col in group_df_output.columns if col not in output_columns] output_columns.extend(remaining_columns) # 重新排序DataFrame group_df_output = group_df_output[output_columns] # 保存到对应的sheet，使用分组名作为sheet名 sheet_name = f"20201-{group}" group_df_output.to_excel(writer, sheet_name=sheet_name, index=False) print(f"✓ 结果保存完成：{len(final_df)}条记录，{len(final_df['分组'].unique())}个分组") return True except Exception as e: print(f"✗ 保存失败：{e}") return False def display_rules(self): """显示当前的计算规则""" print("计算规则：", end="") rules = [] for rule_name, work_columns in self.scoring_rules.items(): rules.append(f"{rule_name}({'+'.join(work_columns)})") print(" | ".join(rules)) def process_all(self): """执行完整的处理流程""" print("数据结构成绩处理开始...") # 前置操作1：转换Excel文件为CSV if not self.convert_xlsx_to_csv(): return False # 前置操作2：提取学生名单 if not self.extract_student_lists_from_xlsx(): return False # 显示计算规则 self.display_rules() # 读取成绩数据 df_grades = self.read_grade_files() if df_grades is None: return False # 读取学生名单 student_lists = self.read_student_lists() if student_lists is None: return False # 计算成绩 df_calculated = self.calculate_scores(df_grades) if df_calculated is None: return False # 创建最终表格 final_df = self.create_final_grade_table(df_calculated, student_lists) if final_df is None: return False # 保存结果 success = self.save_results(final_df) if success: print("✓ 处理完成！输出文件：") print(" - 数据结构成绩记录表_完整.csv") print(" - 数据结构成绩记录表_最终.xlsx") return success # 使用示例 if __name__ == "__main__": # 创建处理器实例 processor = GradeProcessor() # 执行完整处理流程 success = processor.process_all() # 如果处理成功，询问是否生成图表 if success: try: user_input = input("\n是否生成可视化图表？(y/n): ").strip().lower() if user_input in ['y', 'yes', '是']: from grade_visualizer import GradeVisualizer visualizer = GradeVisualizer() if visualizer.load_data(): print("正在生成图表...") visualizer.generate_all_charts() visualizer.create_summary_report() print("✓ 图表生成完成！请查看 assets/ 目录") except Exception as e: print(f"生成图表时出错：{e}")