基于学生分组Excel表实现Canvas作业文件自动重命名与分组归类的脚本开发需求

Hey there! I’ve struggled with this exact Canvas submission sorting problem before—manually renaming and organizing those files is such a time drain. Let’s put together a Python script to automate this whole process, since it’s perfect for handling file operations and Excel data.

先准备好工具 #

First, you’ll need to install pandas (it makes reading Excel files a breeze) if you don’t have it already:

pip install pandas

脚本思路与实现 #

The script will do three core things:

1. Read your Excel spreadsheet and create a quick lookup map of student names (formatted to match Canvas filenames) to their group numbers.
2. Scan through all your downloaded Canvas submission PDFs.
3. For each file, match the student name to their group, rename the file with the group prefix, and move it into a dedicated group folder.

Here’s the full script—just tweak the configuration variables at the top to match your files:

import os
import pandas as pd
import re

# ---------------------- 配置参数：根据你的实际情况修改 ----------------------
EXCEL_FILE = "学生分组信息表.xlsx"  # 你的Excel文件路径
SUBMISSIONS_DIR = "Canvas作业下载文件夹"  # 存放下载作业的文件夹路径
# Excel表格里的列名：比如姓名列叫"学生姓名"，组号列叫"分组编号"
NAME_COL_IN_EXCEL = "学生姓名"
GROUP_COL_IN_EXCEL = "分组编号"
# -------------------------------------------------------------------------

def organize_canvas_submissions():
    # 1. 读取Excel，构建姓名到组号的映射字典
    try:
        df = pd.read_excel(EXCEL_FILE)
    except FileNotFoundError:
        print(f"错误：找不到Excel文件 {EXCEL_FILE}，请检查路径是否正确！")
        return

    name_group_map = {}
    for _, row in df.iterrows():
        full_name = str(row[NAME_COL_IN_EXCEL]).strip()
        # 这里假设Excel里的姓名是"名 姓"格式，比如"John Doe"
        # 如果你的Excel是"姓, 名"格式，改成 last_name, first_name = full_name.split(", ")
        try:
            first_name, last_name = full_name.split()
        except ValueError:
            print(f"警告：无法拆分姓名 {full_name}，请检查Excel里的姓名格式！")
            continue
        
        # 转成和Canvas文件名一致的小写格式：lastname_firstname
        canvas_name = f"{last_name.lower()}_{first_name.lower()}"
        # 组号格式化为"groupX"
        group_number = row[GROUP_COL_IN_EXCEL]
        name_group_map[canvas_name] = f"group{group_number}"

    # 2. 遍历并处理每个作业文件
    for filename in os.listdir(SUBMISSIONS_DIR):
        if not filename.lower().endswith(".pdf"):
            continue  # 只处理PDF文件

        # 从文件名提取姓氏+名字部分（忽略后面的随机数和提交时间）
        # 正则匹配开头的"lastname_firstname"部分
        match_result = re.match(r"([a-zA-Z-]+_[a-zA-Z-]+)", filename.lower())
        if not match_result:
            print(f"跳过：无法识别文件名格式 - {filename}")
            continue

        extracted_name = match_result.group(1)
        # 查找对应的组号
        if extracted_name not in name_group_map:
            print(f"跳过：未找到学生 {extracted_name} 的分组信息")
            continue

        target_group = name_group_map[extracted_name]
        # 创建目标组文件夹（如果不存在）
        group_folder_path = os.path.join(SUBMISSIONS_DIR, target_group)
        if not os.path.exists(group_folder_path):
            os.makedirs(group_folder_path)
            print(f"已创建文件夹：{group_folder_path}")

        # 3. 重命名并移动文件
        old_file_path = os.path.join(SUBMISSIONS_DIR, filename)
        new_filename = f"{target_group}_{filename}"
        new_file_path = os.path.join(group_folder_path, new_filename)
        
        # 执行移动操作（如果文件名重复会覆盖，建议先备份！）
        os.rename(old_file_path, new_file_path)
        print(f"已处理：{filename} → {new_filename}")

if __name__ == "__main__":
    organize_canvas_submissions()

重要注意事项 #

• 测试先！ 先拿几个测试文件运行脚本，确认它能正确匹配和移动，避免误操作丢失文件。
• 姓名格式匹配：如果你的Excel姓名格式不是"名 姓"（比如是"姓 名"或者带中间名），要调整脚本里拆分姓名的代码。
• 特殊字符：如果学生姓名里有连字符（比如"van der Sar"），可以把正则里的[a-zA-Z]+改成[a-zA-Z-]+来适配。
• 备份文件：建议在运行脚本前先备份所有作业文件，以防万一。

备注：内容来源于stack exchange，提问作者ferris