Python基于唯一USN提取Excel多表数据 按学生导出CSV方法

实现思路 #

• 依赖安装：使用openpyxl读取Excel工作簿，csv为Python内置模块无需额外安装，先执行安装命令：pip install openpyxl
• 数据归集：遍历工作簿内所有工作表，以USN为唯一标识，将每个工作表内的学生考勤行归集到对应学生的数据集合中，额外增加Course字段标记记录所属课程（即工作表名），避免多门课数据混淆
• 文件生成：遍历所有归集完成的学生数据，为每位学生创建独立CSV文件，写入对应多门课的考勤记录，文件以USN+学生姓名命名避免重名

完整可运行代码 #

from openpyxl import load_workbook
import csv
import os

# 配置项
EXCEL_PATH = "your_attendance_file.xlsx"  # 替换为你的Excel文件实际路径
OUTPUT_DIR = "student_attendance"
os.makedirs(OUTPUT_DIR, exist_ok=True)

# 存储所有学生数据，结构: {usn: {"name": 姓名, "records": [{"course": 课程号, "attended": 出勤数, "total": 总课时, "percent": 出勤率}]}}
student_data = {}

# 加载Excel工作簿，read_only模式适配大文件读取
wb = load_workbook(EXCEL_PATH, read_only=True, data_only=True)

for sheet_name in wb.sheetnames:
    ws = wb[sheet_name]
    rows = ws.iter_rows(values_only=True)
    # 读取表头，建立字段名到索引的映射
    header = next(rows)
    col_map = {col_name.strip(): idx for idx, col_name in enumerate(header) if col_name}
    
    # 遍历当前工作表的所有学生行
    for row in rows:
        usn = row[col_map["USN"]]
        if not usn:  # 跳过空行
            continue
        student_name = row[col_map["Name"]]
        attended = row[col_map["Attended"]]
        total = row[col_map["Total"]]
        percent = row[col_map["Percent"]]
        
        # 学生首次出现时初始化数据结构
        if usn not in student_data:
            student_data[usn] = {
                "name": student_name,
                "records": []
            }
        
        # 追加当前课程的考勤记录
        student_data[usn]["records"].append({
            "course": sheet_name,
            "attended": attended,
            "total": total,
            "percent": percent
        })

wb.close()

# 逐个生成学生对应的CSV文件
csv_header = ["USN", "Name", "Course", "Attended", "Total", "Percent"]
for usn, info in student_data.items():
    # 替换文件名里的非法字符，避免写入失败
    safe_name = str(info["name"]).replace("/", "_").replace("\\", "_").replace(":", "_")
    file_path = os.path.join(OUTPUT_DIR, f"{usn}_{safe_name}.csv")
    with open(file_path, "w", newline="", encoding="utf-8-sig") as f:
        writer = csv.DictWriter(f, fieldnames=csv_header)
        writer.writeheader()
        for record in info["records"]:
            writer.writerow({
                "USN": usn,
                "Name": info["name"],
                "Course": record["course"],
                "Attended": record["attended"],
                "Total": record["total"],
                "Percent": record["percent"]
            })

print(f"处理完成，共生成{len(student_data)}个学生考勤CSV文件，存储路径：{OUTPUT_DIR}")

运行说明 #

• 运行前将代码中EXCEL_PATH变量的值替换为本地考勤Excel文件的实际路径
• 生成的CSV文件默认存储在脚本同级目录的student_attendance文件夹下，脚本会自动创建该目录无需手动新建
• 代码自动匹配表头字段位置，不需要手动指定列号，只要所有工作表的字段名和给出的USN/Name/Attended/Total/Percent一致，哪怕字段顺序调整也能正常读取
• 输出CSV采用utf-8-sig编码，直接用Excel打开不会出现乱码，同时自动处理文件名中的非法字符，避免系统报错

内容的提问来源于stack exchange，提问作者Kalyan N