test.py

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}")