志愿时长收集器

温州医科大学各二级学院内部工具，用于将收集上来的志愿者报名表格汇总为规范格式的志愿时长统计表。读取到的源数据往往不符合规范，需要发挥判断力自行推理和修正，只有确实无法确定的才向用户提问。

前置要求

确保已安装依赖：

pip install openpyxl pandas

核心资源

• assets/志愿服务.xlsx — 输出模板文件，Sheet1（模板）是输出格式，Sheet2（学院专业简称表）是学院专业简称对照表
• scripts/helpers.py — 辅助 Python 脚本，提供常用函数
• references/mapping.md — 学院专业简称对照表（完整版，含模糊匹配规则）

工作流程

整个流程分为五个步骤，必须按顺序执行，每一步遇到需要用户确认的情况必须暂停等待回复后再继续。

第一步：复制模板

首先确认用户的工作目录（用户指定的目录或当前系统工作目录）。

将 assets/志愿服务.xlsx 复制到工作目录，使用以下方式：

from scripts.helpers import copy_template
dest = copy_template("<工作目录>")
print(f"模板已复制到: {dest}")

后续所有修改都针对这个复制后的文件进行，不修改原始模板和 assets/ 中的文件。

第二步：扫描并读取源文件

使用 glob 扫描工作目录下的所有表格文件：

import os, glob
files = glob.glob(os.path.join("<工作目录>", "*.xlsx")) + \
        glob.glob(os.path.join("<工作目录>", "*.xls")) + \
        glob.glob(os.path.join("<工作目录>", "*.csv"))

排除模板文件自身（"志愿服务.xlsx"）。

对每个文件，用 pandas 逐行读取并解析为结构化记录。重要规则：

1. 跳过模板文件（"志愿服务.xlsx"）。
2. 同时读取多个 sheet：如果文件有多个 sheet，每个 sheet 都要读取。
3. 自动识别列：源文件的列名可能不规范。在读取每列数据时，需要智能匹配列名。常见变体包括：
   • 姓名列可能叫：姓名、名字、志愿者姓名、学生姓名
   • 学号列可能叫：学号、student ID、学生编号
   • 班级/年级列可能叫：班级、年级、专业班级
   • 学院列可能叫：学院、所属学院
   • 专业列可能叫：专业、所学专业
   • 手机号列可能叫：手机、手机号、联系电话、联系方式、电话
   • 短号列可能叫：短号、手机短号
   • 服务时长列可能叫：服务时长、志愿时长、时长、工时、小时
   • 序号列可能叫：序号、编号、No.
4. 合并记录：将所有文件、所有 sheet 中解析出的记录合并到一个列表中。
5. 如果无法解析任何文件，停止流程并告知用户："无法解析文件 [文件名]，请检查文件格式。"

活动名称与时间

有些表格文件可能不包含活动名称和活动时间信息。在读取完成后，检查是否所有记录都已获取到活动名称和时间：

• 如果缺失活动名称，转向第三步请用户确认
• 如果缺失活动时间，转向第三步请用户确认
• 通常每个表格文件对应一个志愿服务活动，该文件中的所有条目共享同一活动名称和时间

第三步：确认未知信息

3a. 确认活动名称

对每个唯一的活动/文件，如果原始数据中没有活动名称，或者只有简短描述（如"非遗香囊"、"招生宣传"等），需要向用户确认活动名称。

由我自己根据文件名、表格内嵌的活动标题行、以及已有描述推测活动名称，规则：

1. 如果需要加学院前缀（如阿尔伯塔|XX志愿服务），先问用户需要的格式
2. 生成 2-4 个候选名称作为选项，使用选择题呈现给用户确认（不要手动添加"自定义输入"等兜底选项）

例如（以阿尔伯塔学院为例）：

• "非遗香囊" → "阿尔伯塔|非遗香囊制作志愿服务"
• "2026年阿尔伯塔学院招生宣传志愿服务活动" → "阿尔伯塔|招生宣传志愿服务"
• "三位一体综合评价招生" → "阿尔伯塔|三位一体招生志愿服务"

3b. 确认活动时间

如果源数据中没有活动时间（起止），向用户确认：

1. 提问活动开始日期
2. 提问活动结束日期
3. 日期格式为 "YYYY年M月D日"，例如 "2026年1月10日"

通常每个表格中的所有条目属于同一个活动，相同活动的条目活动名称和时间相同。

第四步：逐条解析与填写

对合并后的每一条记录，逐一解析各字段并写入模板。

重要：所有写入操作使用 openpyxl 直接操作工作表，调用 scripts/helpers.py 中的函数。不要手动用 pandas 重建。

4.0 准备数据区

先统计总共有多少条记录（设为 N 条），然后调整模板的数据区行数：

from scripts.helpers import load_template, adjust_rows, get_data_range, fill_data

wb, ws = load_template("<工作目录>/志愿服务.xlsx")
adjust_rows(ws, N)  # 自动插入或删除行，使数据区恰好容纳 N 条记录

4.1 排序规则

所有记录按以下优先级排序后逐条填入：

1. 活动开始时间早的优先（第一优先级）
2. 活动结束时间早的优先（第二优先级）
3. 同一活动中按原始序号排序（如果有）

4.2 字段解析规则

对每条记录的每个字段，按以下规则解析：

活动名称（A列）

• 使用第三步确认的活动名称
• 同名活动的每条记录填相同的活动名称（每行都要填）

活动时间起（B列）与活动时间止（C列）

• 格式：YYYY年M月D日，如 2026年1月10日
• 使用 helpers.parse_date() 解析，再用 helpers.date_to_chinese() 格式化

序号（D列）

• 从 1 开始，逐条递增
• 跨文件、跨活动全局连续编号

报名来源（E列）

• 先问用户报名来源名称（如阿尔伯塔学院用"白求恩"）
• 统一使用用户指定的来源名称

姓名（F列）

• 按原始数据如实填写
• 去除前后空格

学院简称（G列）

• 使用 helpers.resolve_college(raw) 解析
• 如果返回 None，则使用平台内置交互工具向用户提问确认
• 参考 references/mapping.md 中的模糊匹配规则

年级（H列）

• 格式：入学年份后两位，如 25
• 解析规则：
  • "2025"/"2025级"/"25级" → 提取后两位 "25"
  • "大二"等 → 用当前日期计算（需用 webfetch 搜当前年份确认）
  • 使用 helpers.extract_grade() 函数
• 必须验证：年级应与"学号"列的前两位一致
  • 使用 helpers.validate_grade_vs_student_id() 验证
  • 如果不一致，向用户汇报冲突，由用户决定

专业简称（I列）

• 使用 helpers.resolve_major(raw, college_abbr) 解析
• 返回结果有几种情况：
  • certain=True: 直接使用 major 值
  • possible 非空（如"护理"无法区分护本/护专）：用平台内置交互工具请用户选择
  • major 为 None（无法识别）：用平台内置交互工具请用户手动告知
  • is_graduate=True：保留原始数据，在流程结束后统一汇报
• 特殊规则：
  • "生物医药数据科学"属眼生学院，是较新专业，直接使用全称
  • 若原始数据是班级名（如"25护本18"），优先提取专业部分（如"护本"）
• 参考 references/mapping.md 中的完整对照表和模糊匹配规则

学号（J列）

• 按原始数据如实填写
• 确保为文本格式（字符串），避免 Excel 将长数字转为科学计数法

联系方式长号（K列）

• 按原始数据如实填写
• 确保为文本格式

联系方式短号（L列）

• 如果有短号，如实填写
• 如果没有，填写 无
• 确保为文本格式

服务时长（M列）

• 格式：整数或半整数的阿拉伯数字，如 4.5、7
• 组长识别：每个文件通常有 1-2 个组长，组长的服务时长比其他人多 2 小时
  • 需要在源数据中识别组长（可能有"组长"标记，或服务时长明显高于其他人）
  • 如果没有发现组长，在流程结束后向用户汇报
• 上限：服务时长不能超过 8 小时。如果原始数据超过 8，填 8 并向用户汇报
• 服务时长仅填数字，不加单位

备注（N列）

• 非必填
• 如有需要记录的特殊信息（如双倍积分等），在此填写

4.3 填写格式

• 字体：宋体（name='宋体'），字号 11
• 对齐：水平和垂直均居中
• 边框：细线边框（thin border，颜色 000000）
• 使用 helpers.fill_row() 或 helpers.fill_data() 批量填写

表头（1-4行）和表尾（最后10行）绝对不能修改，只填写中间的数据区。

4.4 填写示例代码

from scripts.helpers import load_template, adjust_rows, fill_data, date_to_chinese

wb, ws = load_template("工作目录/志愿服务.xlsx")

records = [
    {
        "活动名称": "非遗香囊制作志愿服务",
        "活动时间起": "2026年1月10日",
        "活动时间止": "2026年1月10日",
        "序号": 1,
        "报名来源": "（用户指定来源）",
        "姓名": "张三",
        "学院": "临一",
        "年级": "25",
        "专业": "临床",
        "学号": "2518260000",
        "长号": "13812345678",
        "短号": "无",
        "服务时长": 4.5,
        "备注": "",
    },
    # ... 更多记录
]

adjust_rows(ws, len(records))
fill_data(ws, records)
wb.save("工作目录/志愿服务.xlsx")

第五步：保存与重命名

保存填写好的文件后，重命名为最终交付名称（先问用户学院名称/前缀，如阿尔伯塔学院用"阿尔伯塔"）：

（学院名）X月份志愿收集.xlsx

例如 阿尔伯塔6月份志愿收集.xlsx。

确定月份：

• 如果当前系统日期是当月 25 日之后（含 25 日），X 取当月月份
• 如果当前系统日期是当月 5 日之前（含 5 日），X 取上月月份
• 如果在 6 日到 24 日之间，向用户确认是当月还是上月
• 月份格式为阿拉伯数字，如 阿尔伯塔1月份志愿收集.xlsx

第六步：流程结束汇报

流程完成后，向用户汇报以下信息：

1. 总共处理了多少条记录
2. 来自多少个源文件
3. 输出文件路径
4. 汇报所有需要关注的问题：
   • 未发现组长的情况（列出哪些活动/文件没有组长）
   • 服务时长超过 8 小时被截断的记录
   • 年级与学号不匹配的记录
   • 推定为研究生的记录（如有）
   • 任何其他异常或需要人工复核的情况

学院专业对照速查

完整对照表见 references/mapping.md。以下为常用匹配规则：

学院模糊匹配

专业模糊匹配

注意事项

1. 不要修改表头和表尾：第 1-4 行是表头，最后 10 行是表尾（注意事项），不要对它们做任何编辑
2. 活动名称每行都要写：即使同一活动的多条记录，活动名称和活动时间每行都要填
3. 学号和手机号必须是文本：在 openpyxl 中设置 cell.value 为字符串类型（str），或将单元格格式设为文本（number_format='@'），避免 Excel 自动转换
4. 模板格式保留：复制模板后只修改数据区，不要改动列宽、行高、合并单元格等
5. 学年判断：根据当前系统日期动态确定。例如当前在 2026 年 9 月，学生写"大二"则意味着 2025 年入学（年级="25"）。9 月前入学的年级计算需减 1 年。始终通过 date.today() 获取当前日期进行计算。
6. 服务时长取少不取多：按模板注意事项第 6 条，如果需要取半整数，向下取整到最近的 0.5（如 3.7 → 3.5）