Python Excel 数据处理和成绩录入脚本优化

{"title": "Python Excel 数据处理和成绩录入脚本优化", "description": "本脚本使用 Python 处理 Excel 文件，实现学生成绩录入、更新和保存功能。代码经过优化，使用更具可读性和可维护性的方法，包括函数划分、异常处理、文件操作、数据格式化等。", "keywords": "Python, Excel, 数据处理, 成绩录入, 脚本优化, 函数, 异常处理, 文件操作, 数据格式化", "content": "import xlrd\nimport xlwt\n\ndef load_student_data(file_path, start_row, end_row):\n """ 从 Excel 文件中加载学生数据。\n \n Args:\n file_path (str): Excel 文件路径。\n start_row (int): 数据开始行。\n end_row (int): 数据结束行。\n \n Returns:\n tuple: 包含学生姓名、学号、班级列表的元组。\n """ book = xlrd.open_workbook(file_path)\n sheet = book.sheets()[0]\n student_names = []\n student_orders = []\n student_classes = []\n for i in range(start_row, end_row):\n student_names.append(sheet.cell_value(i, 2))\n student_orders.append(str(int(sheet.cell_value(i, 1))))\n student_classes.append(sheet.cell_value(i, 4))\n return student_names, student_orders, student_classes\n\ndef load_saved_data(file_path):\n """ 加载保存的成绩数据。\n \n Args:\n file_path (str): 保存成绩数据的文件路径。\n \n Returns:\n tuple: 包含学生学号和成绩列表的元组。\n """ saved_orders = []\n saved_grades = []\n try:\n with open(file_path, 'r') as fp:\n for line in fp:\n order, grade = line.strip().split(':')\n saved_orders.append(order)\n saved_grades.append(grade)\n except FileNotFoundError:\n pass\n return saved_orders, saved_grades\n\ndef save_data(data, file_path):\n """ 保存数据到文件。\n \n Args:\n data (list): 要保存的数据列表。\n file_path (str): 保存数据的文件路径。\n """ with open(file_path, 'w') as fp:\n for item in data:\n fp.write(f"{item[0]}:{item[1]}\n")\n\ndef input_grade(student_orders, student_names, student_classes, saved_orders, saved_grades):\n """ 输入成绩，并更新保存的成绩数据。\n \n Args:\n student_orders (list): 学生学号列表。\n student_names (list): 学生姓名列表。\n student_classes (list): 学生班级列表。\n saved_orders (list): 保存的学号列表。\n saved_grades (list): 保存的成绩列表。\n \n Returns:\n list: 更新后的保存的学号和成绩列表。\n """ while True:\n inp = input("姓名或学号：")\n if inp == '':\n break\n if inp[-1] in "0123456789":\n index = student_orders\n inp = "2021" + inp\n else:\n index = student_names\n try:\n i = index.index(inp)\n except ValueError:\n l_t = [j for j in range(len(index)) if inp in index[j]]\n if l_t:\n for j in l_t:\n print(f"{j}: {student_names[j]} {student_orders[j]}")\n while True:\n try:\n i = int(input("输入序号："))\n if i in l_t:\n break\n except ValueError:\n print("输入有误")\n else:\n print("输入有误请重新输入")\n continue\n print(f"{student_orders[i]}: {student_names[i]}: {student_classes[i]}")\n gin = input("输入成绩：")\n try:\n tmp_index = saved_orders.index(student_orders[i])\n gin = input(f"{student_orders[i]}: {student_names[i]}: {student_classes[i]}已有成绩：{saved_grades[tmp_index]}\n请输入修改后的成绩")\n saved_grades[tmp_index] = gin\n except ValueError:\n saved_orders.append(student_orders[i])\n saved_grades.append(gin)\n return saved_orders, saved_grades\n\nif name == "main":\n student_file = "理论力学21.xlsx"\n start_row = 1\n end_row = 88\n student_names, student_orders, student_classes = load_student_data(student_file, start_row, end_row)\n saved_orders, saved_grades = load_saved_data("tmp.tmp")\n saved_orders, saved_grades = input_grade(student_orders, student_names, student_classes, saved_orders, saved_grades)\n if input("是否保存tmp.tmp(默认 否)"):\n save_data(list(zip(saved_orders, saved_grades)), "tmp.tmp")\n else:\n wt_book = xlwt.Workbook(encoding="utf-8")\n wt_sheet = wt_book.add_sheet("Test")\n for i in range(len(saved_orders)):\n j = student_orders.index(saved_orders[i])\n wt_sheet.write(j, 0, saved_orders[i])\n wt_sheet.write(j, 1, saved_grades[i])\n wt_book.save("Test.xls") }