import streamlit as st import pandas as pd import io import re import difflib # ========================================== # 页面基础设置 # ========================================== # 使用 centered 布局可以让页面更集中，两列宽度的拖拽框面积更大、更美观 st.set_page_config(page_title="竞赛积分全自动赋分系统", page_icon="🏆", layout="centered") st.title("🏆 竞赛积分全自动赋分系统") st.markdown("请在下方依次上传对应的四个数据文件（**支持点击上传，或直接将文件拖拽至虚线框内**）。") st.divider() # 添加一条分割线，提升视觉层次 # ========================================== # 文件上传区 (2x2 网格布局，增大拖拽面积) # ========================================== col1, col2 = st.columns(2) with col1: f_comp = st.file_uploader("请上传《需要录入的竞赛名录.xlsx》", type=["xlsx", "xls", "csv"]) with col2: f_score = st.file_uploader("请上传《积分赋分参考.xlsx》", type=["xlsx", "xls", "csv"]) col3, col4 = st.columns(2) with col3: f_student = st.file_uploader("请上传《学生获奖统计.xlsx》", type=["xlsx", "xls", "csv"]) with col4: f_template = st.file_uploader("请上传《系统批量导入模板.xlsx》", type=["xlsx", "xls", "csv"]) # 辅助函数：文件读取 def load_data(file, as_raw=False): file.seek(0) if file.name.endswith('.csv'): return pd.read_csv(file, header=None if as_raw else 'infer') else: return pd.read_excel(file, header=None if as_raw else 0) # ========================================== # 数据处理区 # ========================================== if f_comp and f_score and f_student and f_template: st.success("✅ 文件已全部就绪，请点击下方按钮开始处理数据。") if st.button("🚀 一键处理并生成导入文件", type="primary", use_container_width=True): with st.spinner("系统正在处理数据，请稍候..."): try: # 1. 加载数据 df_comp = load_data(f_comp, as_raw=False) df_score = load_data(f_score, as_raw=True) df_student = load_data(f_student, as_raw=False) df_template = load_data(f_template, as_raw=True) valid_comps = df_comp['竞赛名称'].dropna().astype(str).unique().tolist() # 名称清洗与匹配逻辑 def clean_name(name): name = re.sub(r'(20\d{2}|第[一二三四五六七八九十百]+届|年度)', '', name) name = re.sub(r'(总决赛|决赛|校内选拔赛|校内赛|选拔赛|系列赛)', '', name) return re.sub(r'[^\w\u4e00-\u9fa5]', '', name).lower() comp_clean_map = {c: clean_name(c) for c in valid_comps if len(clean_name(c)) >= 2} student_comps_unique = df_student['竞赛项目名称'].dropna().astype(str).unique() mapping_dict = {} for s_name in student_comps_unique: s_clean = clean_name(s_name) best_match, best_match_score = None, 0 for c_orig, c_clean in comp_clean_map.items(): if c_clean in s_clean or s_clean in c_clean: if len(c_clean) > best_match_score: best_match_score = len(c_clean) best_match = c_orig if not best_match: best_ratio = 0 for c_orig, c_clean in comp_clean_map.items(): ratio = difflib.SequenceMatcher(None, s_clean, c_clean).ratio() if ratio > best_ratio: best_ratio = ratio best_match = c_orig if best_ratio > 0.55: mapping_dict[s_name] = best_match else: mapping_dict[s_name] = best_match df_student['最终匹配名录名称'] = df_student['竞赛项目名称'].map(mapping_dict) df_matched_students = df_student.dropna(subset=['最终匹配名录名称']).copy() # 2. 赋分规则解析 score_dict = {} for index, row in df_score.iterrows(): row_str = "".join([str(v) for v in row.values]) level = None if '院级' in row_str: level = '院级' elif '校级' in row_str: level = '校级' elif '省部级' in row_str or '市级' in row_str: level = '省部级（北京市级）' elif '国家' in row_str: level = '国家级及以上' if level: nums = [] for v in row.values: val_str = str(v).strip() if re.match(r'^\d+(\.\d+)?$', val_str): nums.append(float(val_str)) if len(nums) >= 10: nums = nums[-10:] score_dict[level] = { '队长': { '一等奖及以上': nums[0], '二等奖': nums[1], '三等奖': nums[2], '优秀奖': nums[3], '参与但未获奖': nums[4] }, '队员': { '一等奖及以上': nums[5], '二等奖': nums[6], '三等奖': nums[7], '优秀奖': nums[8], '参与但未获奖': nums[9] } } if not score_dict: st.error("数据读取失败：无法在《积分赋分参考.xlsx》中读取到有效的数字分数，请检查文件是否上传正确。") st.stop() # 3. 计算得分 def calculate_score(row): try: sort_val = float(row.get('获奖者排序', 0)) except: sort_val = 0 role = '队长' if sort_val == 1.0 else '队员' raw_level = str(row.get('获奖级别', '')).strip() level = '' if '国家' in raw_level: level = '国家级及以上' elif '省' in raw_level or '市' in raw_level: level = '省部级（北京市级）' elif '校' in raw_level: level = '校级' elif '院' in raw_level: level = '院级' raw_award = str(row.get('获奖等级', '')).strip() award = '参与但未获奖' if '特等' in raw_award or '一等' in raw_award: award = '一等奖及以上' elif '二等' in raw_award: award = '二等奖' elif '三等' in raw_award: award = '三等奖' elif '优秀' in raw_award: award = '优秀奖' if level in score_dict and role in score_dict[level] and award in score_dict[level][role]: return score_dict[level][role][award] return 0 df_matched_students['发放学分值'] = df_matched_students.apply(calculate_score, axis=1) def format_award_for_export(award_str): award_str = str(award_str).strip() if '优秀' in award_str: return '三等奖' return award_str df_matched_students['导出用奖项名称'] = df_matched_students['获奖等级'].apply(format_award_for_export) # 4. 生成最终系统模板 head_rows = min(3, len(df_template)) template_head = df_template.iloc[0:head_rows].copy() result_cols = None for i in range(len(df_template)): row_vals = [str(v) for v in df_template.iloc[i].values] if '学号' in row_vals and '姓名' in row_vals: result_cols = df_template.iloc[i].tolist() break if result_cols is None: result_cols = ['学号', '姓名', '开始时间', '结束时间', '内容', '活动一级分类', '活动二级分类', '活动等级', '奖项内容', '学分类型', '发放学分值'] df_result = pd.DataFrame(columns=result_cols) df_result['学号'] = df_matched_students['获奖者学号'] df_result['姓名'] = df_matched_students['获奖者姓名'] df_result['开始时间'] = df_matched_students['获奖时间'] df_result['内容'] = df_matched_students['最终匹配名录名称'] df_result['活动一级分类'] = '学科竞赛' df_result['活动二级分类'] = '学科竞赛' df_result['活动等级'] = df_matched_students['获奖级别'] df_result['奖项内容'] = df_matched_students['导出用奖项名称'] df_result['学分类型'] = '竞赛加分' df_result['发放学分值'] = df_matched_students['发放学分值'] df_result.columns = template_head.columns final_df = pd.concat([template_head, df_result], ignore_index=True) output = io.BytesIO() with pd.ExcelWriter(output, engine='openpyxl') as writer: final_df.to_excel(writer, index=False, header=False) processed_data = output.getvalue() # ========================================== # 处理完成及下载区 # ========================================== st.divider() st.success(f"数据处理完毕！共生成 {len(df_result)} 条积分记录。") st.download_button( label="📥 点击下载最终导入文件 (Excel格式)", data=processed_data, file_name="最终系统批量导入文件.xlsx", mime="application/vnd.openxmlformats-officedocument.spreadsheetml.sheet", use_container_width=True ) st.markdown("### 数据预览 (节选前10条)") df_result.columns = result_cols st.dataframe(df_result.head(10), use_container_width=True) except Exception as e: st.error(f"处理数据时出现异常，请检查文件格式。错误详情：{e}")