Python学生课程分配代码报错zipfile.BadZipFile: File is not a zip file

问题背景 #

我编写了一段Python代码实现学生与偏好课程的匹配逻辑，代码如下：

import random
import pandas as pd
from openpyxl import load_workbook

class Student:
    def __init__(self, name, preferences, english, mathematics, science):
        self.name = name
        self.preferences = preferences
        self.english = english
        self.mathematics = mathematics
        self.science = science
        self.assigned_course = None

class Course:
    def __init__(self, name, capacity, criteria):
        self.name = name
        self.capacity = capacity
        self.enrolled_students = []
        self.criteria = criteria

def read_students_from_excel(file_path):
    students_df = pd.read_excel(file_path, sheet_name='Students')
    students = []

    for index, row in students_df.iterrows():
        name = row['Name']
        preferences = row['Preferences']
        english = row['English']
        mathematics = row['Mathematics']
        science = row['Science']
        student = Student(name, preferences, english, mathematics, science)
        students.append(student)

    return students

def read_courses_from_excel(file_path):
    courses_df = pd.read_excel(file_path, sheet_name='Courses')
    courses = []

    for index, row in courses_df.iterrows():
        name = row['Name']
        capacity = row['Capacity']
        criteria = row['Criteria']

        # Define criteria functions based on the "Criteria" column
        def course_criteria(student):
            return eval(criteria)  # Evaluate the criteria expression

        course = Course(name, capacity, course_criteria)
        courses.append(course)

    return courses

def match_students_to_courses(students, courses):
    for student in students:
        for preference in student.preferences:
            for course in courses:
                if (
                    course.name == preference
                    and len(course.enrolled_students) < course.capacity
                    and course.criteria(student)
                ):
                    course.enrolled_students.append(student)
                    student.assigned_course = course.name
                    break

def write_results_to_excel(students, file_path):
    writer = pd.ExcelWriter(file_path, engine='openpyxl')
    writer.book = load_workbook(file_path)

    students_df = pd.DataFrame([(student.name, student.assigned_course) for student in students], columns=['Name', 'Assigned Course'])
    students_df.to_excel(writer, sheet_name='Results', index=False)

    writer.save()

if __name__ == "__main__":
    input_excel_file = "input_data.xlsx"
    output_excel_file = "output_data.xlsx"

    students = read_students_from_excel(input_excel_file)
    courses = read_courses_from_excel(input_excel_file)

    match_students_to_courses(students, courses)
    write_results_to_excel(students, output_excel_file)

运行代码时触发如下错误：

Traceback (most recent call last):
  File "C:\Users\User\AppData\Local\Programs\Python\Python312\subjectallocation.py", line 84, in <module>
    write_results_to_excel(students, output_excel_file)
  File "C:\Users\User\AppData\Local\Programs\Python\Python312\subjectallocation.py", line 69, in write_results_to_excel
    writer.book = load_workbook(file_path)
  File "C:\Users\User\AppData\Local\Programs\Python\Python312\Lib\site-packages\openpyxl\reader\excel.py", line 344, in load_workbook
    reader = ExcelReader(filename, read_only, keep_vba,
  File "C:\Users\User\AppData\Local\Programs\Python\Python312\Lib\site-packages\openpyxl\reader\excel.py", line 123, in __init__
    self.archive = _validate_archive(fn)
  File "C:\Users\User\AppData\Local\Programs\Python\Python312\Lib\site-packages\openpyxl\reader\excel.py", line 95, in _validate_archive
    archive = ZipFile(filename, 'r')
  File "C:\Users\User\AppData\Local\Programs\Python\Python312\Lib\zipfile\__init__.py", line 1338, in __init__
    self._RealGetContents()
  File "C:\Users\User\AppData\Local\Programs\Python\Python312\Lib\zipfile\__init__.py", line 1405, in _RealGetContents
    raise BadZipFile("File is not a zip file")
zipfile.BadZipFile: File is not a zip file

错误定位在write_results_to_excel函数中调用load_workbook加载输出Excel文件的步骤。

问题原因 #

• 首次运行时，output_data.xlsx文件尚未创建，load_workbook尝试读取不存在的文件时，系统会生成一个空文件，但空文件并非有效的XLSX格式（XLSX本质是ZIP压缩包），因此触发BadZipFile错误。
• 原代码逻辑默认输出文件已存在，未处理文件不存在的边界情况。

修复方案 #

修改write_results_to_excel函数，增加文件存在性检查，根据情况创建新文件或追加现有文件：

def write_results_to_excel(students, file_path):
    import os
    # 检查输出文件是否存在
    if os.path.exists(file_path):
        # 文件存在时，以追加模式打开，若工作表已存在则替换
        writer = pd.ExcelWriter(file_path, engine='openpyxl', mode='a', if_sheet_exists='replace')
    else:
        # 文件不存在时，创建新文件
        writer = pd.ExcelWriter(file_path, engine='openpyxl')
    
    students_df = pd.DataFrame([(student.name, student.assigned_course) for student in students], columns=['Name', 'Assigned Course'])
    students_df.to_excel(writer, sheet_name='Results', index=False)
    
    # 改用close()方法，save()已被pandas弃用
    writer.close()

额外注意事项 #

1. 偏好列格式问题：若Excel中Preferences列存储的是字符串格式（如"['Math', 'English']"），直接赋值给student.preferences会导致后续遍历出错，需将其转换为列表，可改用ast.literal_eval(preferences)代替直接赋值。
2. eval安全风险：read_courses_from_excel中使用eval(criteria)存在代码注入风险，建议改用自定义条件解析逻辑（如根据字段映射判断），避免直接执行外部输入的代码。

内容的提问来源于stack exchange，提问作者Kenny Sng