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文件""" print("=== 前置操作：转换Excel文件为CSV ===") try: # 转换ds_1_2.xlsx到CSV df_12 = pd.read_excel('ds_1_2.xlsx', sheet_name='c1_2') df_12.to_csv('ds_1_2.csv', index=False, encoding='utf-8-sig') print(f"ds_1_2.xlsx 已转换为 ds_1_2.csv：{len(df_12)}条记录") # 转换ds_3_4.xlsx到CSV df_34 = pd.read_excel('ds_3_4.xlsx', sheet_name='01-绪论') df_34.to_csv('ds_3_4.csv', index=False, encoding='utf-8-sig') print(f"ds_3_4.xlsx 已转换为 ds_3_4.csv：{len(df_34)}条记录") return True except Exception as e: print(f"转换Excel文件时发生错误：{e}") return False def extract_student_lists_from_xlsx(self): """前置操作：从原始xlsx文件中提取学生名单""" print("=== 前置操作：提取学生名单 ===") try: # 读取原始的成绩记录表 excel_file = pd.ExcelFile('数据结构成绩记录表(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('数据结构成绩记录表(1).xlsx', sheet_name=sheet_name) # 只保留前两列（姓名和班级） df_output = df.iloc[:, :2] # 保存为CSV文件 output_filename = f'{sheet_name}.csv' df_output.to_csv(output_filename, index=False, header=False, encoding='utf-8-sig') print(f"提取 {sheet_name} 学生名单到 {output_filename}：{len(df_output)}人") else: print(f"警告：未找到sheet '{sheet_name}'") return True except Exception as e: print(f"提取学生名单时发生错误：{e}") return False def read_grade_files(self): """读取平时成绩文件""" try: # 读取1、2班成绩 df_12 = pd.read_csv('ds_1_2.csv') print(f"成功读取1、2班成绩数据：{len(df_12)}条记录") # 读取3、4班成绩 df_34 = pd.read_csv('ds_3_4.csv') print(f"成功读取3、4班成绩数据：{len(df_34)}条记录") # 合并两个班级的数据 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 = [ ('20201-1小1.csv', '1小1'), ('20201-1小2.csv', '1小2'), ('20201-3小1.csv', '3小1'), ('20201-3小2.csv', '3小2') ] for file_name, group_name in files: df = pd.read_csv(file_name, header=None, names=headers) student_lists[group_name] = df print(f"读取{group_name}学生名单：{len(df)}人") 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() # 根据规则计算新的成绩列 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) print(f"计算{rule_name}：使用列 {work_columns}") print(f" - 平均分：{scores.mean():.1f}") print(f" - 最高分：{scores.max():.1f}") print(f" - 最低分：{scores.min():.1f}") 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) print(f"总学生人数：{len(df_all_students)}") # 创建最终表格的列 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['分组'] # 匹配成绩数据 for idx, student_name in enumerate(df_all_students['姓名']): # 在成绩数据中查找该学生 student_grade = df_calculated[df_calculated['name'] == student_name] if not student_grade.empty: # 填充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 else: print(f"警告：未找到学生 {student_name} 的成绩数据") return final_df def save_results(self, final_df, csv_filename='数据结构成绩记录表_完整.csv', xlsx_filename='数据结构成绩记录表_最终.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文件 print("=== 收尾操作：保存完整CSV文件 ===") final_df.to_csv(csv_filename, index=False, encoding='utf-8-sig') print(f"完整成绩记录表已保存为：{csv_filename}") print(f"共包含 {len(final_df)} 条学生记录") # 第二步：按分组保存到Excel的不同sheet print("\n=== 收尾操作：按分组保存到Excel文件 ===") 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"分组 {group} 数据已保存到sheet '{sheet_name}'：{len(group_df_output)} 条记录") print(f"分组成绩记录表已保存为：{xlsx_filename}") print(f"共包含 {len(final_df)} 条学生记录，分为 {len(final_df['分组'].unique())} 个分组") # 显示每个分组的统计信息 print("\n=== 各分组成绩统计信息 ===") groups = final_df['分组'].unique() for group in groups: print(f"\n分组 {group}:") group_df = final_df[final_df['分组'] == group] for rule_name in self.scoring_rules.keys(): if rule_name in group_df.columns: scores = pd.to_numeric(group_df[rule_name], errors='coerce') valid_scores = scores.dropna() if len(valid_scores) > 0: print(f" {rule_name}:") print(f" - 有效成绩数：{len(valid_scores)}") print(f" - 平均分：{valid_scores.mean():.1f}") print(f" - 最高分：{valid_scores.max():.1f}") print(f" - 最低分：{valid_scores.min():.1f}") return True except Exception as e: print(f"保存文件时发生错误：{e}") return False def display_rules(self): """显示当前的计算规则""" print("=== 当前成绩计算规则 ===") for rule_name, work_columns in self.scoring_rules.items(): print(f"{rule_name}: {' + '.join(work_columns)}") print() def process_all(self): """执行完整的处理流程""" print("开始处理成绩数据...") print("="*50) # 前置操作1：转换Excel文件为CSV print("前置操作1：转换Excel文件...") if not self.convert_xlsx_to_csv(): print("Excel转换失败，程序终止") return False # 前置操作2：提取学生名单 print("\n前置操作2：提取学生名单...") if not self.extract_student_lists_from_xlsx(): print("学生名单提取失败，程序终止") return False print("\n" + "="*50) print("开始正式数据处理...") # 显示计算规则 self.display_rules() # 读取成绩数据 print("1. 读取平时成绩数据...") df_grades = self.read_grade_files() if df_grades is None: return False # 读取学生名单 print("\n2. 读取学生名单...") student_lists = self.read_student_lists() if student_lists is None: return False # 计算成绩 print("\n3. 根据规则计算成绩...") df_calculated = self.calculate_scores(df_grades) if df_calculated is None: return False # 创建最终表格 print("\n4. 创建最终成绩记录表...") final_df = self.create_final_grade_table(df_calculated, student_lists) if final_df is None: return False # 保存结果 print("\n5. 保存结果...") success = self.save_results(final_df) if success: print("\n处理完成！") print("="*50) print("输出文件：") print("- 数据结构成绩记录表_完整.csv (完整数据)") print("- 数据结构成绩记录表_最终.xlsx (按分组分sheet)") return success # 使用示例 if __name__ == "__main__": # 创建处理器实例 processor = GradeProcessor() # 执行完整处理流程 processor.process_all() # 如果需要修改规则，可以这样做： # processor.scoring_rules = { # '阶段成绩1': ['work1', 'work2', 'work3'], # '阶段成绩2': ['work4', 'work5'] # } # processor.process_all()