中间反反复复很多次，本文只记下重点的几次
round1
请在windows下，使用python的selenium库，打开课程查询地址，输入考号、姓名，点击查询按钮， 获取课程的分数、班名、校名。
其中课程查询地址、考号、姓名都发在c:\查分系统.xlsx中。
其中sheet1，存放着课程名称、地址；sheet2，存放着考号、姓名。
请找到每个学生，遍历每一门课程，获取课程名称、分数、班名、校名，存在sheet3中
同时把学生的姓名、各科成绩放到sheet4中的每行中。
注意课程名称是从sheet1中遍历获取，可能是多门课程。
谷歌驱动地址：C:/chrome_drv/chromedriver.exe
xpath信息：
考号：/html/body/div[1]/form/div[2]/div[1]/div[2]/input
姓名：/html/body/div[1]/form/div[2]/div[2]/div[2]/input
查询按钮：/html/body/div[1]/div[2]/button
分数：/html/body/div/div[2]/div[1]/table/tbody/tr[5]/td[2]
班名：/html/body/div/div[2]/div[1]/table/tbody/tr[8]/td[2]
校名：/html/body/div/div[2]/div[1]/table/tbody/tr[9]/td[2]
round2
不对，sheet4中存放的是多门课程的分数。具体有哪些课程，需要从sheet1中获取
round3
再修改一下，第一次循环是，清空sheet3和sheet4
round4
还要修改，sheet3中增加一列：姓名，放到第一列中
round5
在sheet4中再增加一个总分数，是各科成绩之和
round6
再修改一下，每查完一个学生的全部成绩，要写入excel
round7
再优化一下，1、每个学生成绩查完后，从程序输出。2、如果查询出错，自动跳入查询下一个人成绩
round6代码（已验证无误）：
import time
import pandas as pd
from selenium import webdriver
from selenium.webdriver.common.by import By
from selenium.webdriver.chrome.service import Service
from openpyxl import load_workbook# 设置Chrome驱动路径
chrome_driver_path = "C:/chrome_drv/chromedriver.exe"
service = Service(chrome_driver_path)# 加载Excel文件
excel_path = "c:/查分系统.xlsx"
df_courses = pd.read_excel(excel_path, sheet_name='sheet1')
df_students = pd.read_excel(excel_path, sheet_name='sheet2')# 初始化webdriver
driver = webdriver.Chrome(service=service)def write_to_excel(all_results, student_results):# 清理并写入Excelwb = load_workbook(excel_path)if 'sheet3' in wb.sheetnames:wb.remove(wb['sheet3'])if 'sheet4' in wb.sheetnames:wb.remove(wb['sheet4'])wb.save(excel_path)wb.close()with pd.ExcelWriter(excel_path, engine='openpyxl', mode='a') as writer:# 写入sheet3，包含姓名、课程名称、分数、班名、校名pd.DataFrame(all_results, columns=['姓名', '课程名称', '分数', '班名', '校名']).to_excel(writer,sheet_name='sheet3',index=False)# 使用pd.json_normalize将字典转换为DataFrame，并确保‘总分数’列被正确处理student_df = pd.json_normalize(student_results)# 调整列顺序以确保‘总分数’列的位置合适，这里假设放在最后一列cols = list(student_df.columns)if '总分数' in cols:cols.remove('总分数')student_df = student_df[cols + ['总分数']]student_df.to_excel(writer, sheet_name='sheet4', index=False)all_results = []
student_results = []for index, student in df_students.iterrows():student_name = student['姓名']student_exam_id = student['考号'] # 修正此处的赋值语句student_scores = {'姓名': student_name} # 存储单个学生的姓名和各科成绩total_score = 0 # 初始化总分数for course_index, course in df_courses.iterrows():course_url = course['地址']course_name = course['课程名称']# 打开课程查询页面driver.get(course_url)# 输入考号和姓名driver.find_element(By.XPATH, "/html/body/div[1]/form/div[2]/div[1]/div[2]/input").send_keys(student_exam_id)driver.find_element(By.XPATH, "/html/body/div[1]/form/div[2]/div[2]/div[2]/input").send_keys(student_name)# 点击查询按钮driver.find_element(By.XPATH, "/html/body/div[1]/div[2]/button").click()time.sleep(2) # 给予页面加载时间# 获取成绩等信息score = driver.find_element(By.XPATH, "/html/body/div/div[2]/div[1]/table/tbody/tr[5]/td[2]").textclass_name = driver.find_element(By.XPATH, "/html/body/div/div[2]/div[1]/table/tbody/tr[8]/td[2]").textschool_name = driver.find_element(By.XPATH, "/html/body/div/div[2]/div[1]/table/tbody/tr[9]/td[2]").text# 将结果添加到列表中，并包含学生姓名all_results.append([student_name, course_name, score, class_name, school_name])student_scores[course_name] = float(score) if score.replace('.', '', 1).isdigit() else 0 # 添加该生的这门课成绩，并转换为浮点数total_score += float(score) if score.replace('.', '', 1).isdigit() else 0 # 计算总分student_scores['总分数'] = total_score # 添加总分数到学生的成绩字典中student_results.append(student_scores)# 每个学生完成后，写入Excelwrite_to_excel(all_results, student_results)# 关闭浏览器
driver.quit()round6代码（没来得及验证）
import time
import pandas as pd
from selenium import webdriver
from selenium.webdriver.common.by import By
from selenium.webdriver.chrome.service import Service
from selenium.common.exceptions import NoSuchElementException, WebDriverException
from openpyxl import load_workbook# 设置Chrome驱动路径
chrome_driver_path = "C:/chrome_drv/chromedriver.exe"
service = Service(chrome_driver_path)# 加载Excel文件
excel_path = "c:/查分系统.xlsx"
df_courses = pd.read_excel(excel_path, sheet_name='sheet1')
df_students = pd.read_excel(excel_path, sheet_name='sheet2')# 初始化webdriver
driver = webdriver.Chrome(service=service)def write_to_excel(all_results, student_results):# 清理并写入Excelwb = load_workbook(excel_path)if 'sheet3' in wb.sheetnames:wb.remove(wb['sheet3'])if 'sheet4' in wb.sheetnames:wb.remove(wb['sheet4'])wb.save(excel_path)wb.close()with pd.ExcelWriter(excel_path, engine='openpyxl', mode='a') as writer:# 写入sheet3，包含姓名、课程名称、分数、班名、校名pd.DataFrame(all_results, columns=['姓名', '课程名称', '分数', '班名', '校名']).to_excel(writer, sheet_name='sheet3', index=False)# 使用pd.json_normalize将字典转换为DataFrame，并确保‘总分数’列被正确处理student_df = pd.json_normalize(student_results)# 调整列顺序以确保‘总分数’列的位置合适，这里假设放在最后一列cols = list(student_df.columns)if '总分数' in cols:cols.remove('总分数')student_df = student_df[cols + ['总分数']]student_df.to_excel(writer, sheet_name='sheet4', index=False)all_results = []
student_results = []for index, student in df_students.iterrows():student_name = student['姓名']student_exam_id = student['考号']student_scores = {'姓名': student_name} # 存储单个学生的姓名和各科成绩total_score = 0 # 初始化总分数print(f"开始查询学生 {student_name} 的成绩...")try:for course_index, course in df_courses.iterrows():course_url = course['地址']course_name = course['课程名称']# 打开课程查询页面driver.get(course_url)# 输入考号和姓名driver.find_element(By.XPATH, "/html/body/div[1]/form/div[2]/div[1]/div[2]/input").send_keys(student_exam_id)driver.find_element(By.XPATH, "/html/body/div[1]/form/div[2]/div[2]/div[2]/input").send_keys(student_name)# 点击查询按钮driver.find_element(By.XPATH, "/html/body/div[1]/div[2]/button").click()time.sleep(2) # 给予页面加载时间# 获取成绩等信息score = driver.find_element(By.XPATH, "/html/body/div/div[2]/div[1]/table/tbody/tr[5]/td[2]").textclass_name = driver.find_element(By.XPATH, "/html/body/div/div[2]/div[1]/table/tbody/tr[8]/td[2]").textschool_name = driver.find_element(By.XPATH, "/html/body/div/div[2]/div[1]/table/tbody/tr[9]/td[2]").text# 将结果添加到列表中，并包含学生姓名all_results.append([student_name, course_name, score, class_name, school_name])student_scores[course_name] = float(score) if score.replace('.','',1).isdigit() else 0 # 添加该生的这门课成绩，并转换为浮点数total_score += float(score) if score.replace('.','',1).isdigit() else 0 # 计算总分student_scores['总分数'] = total_score # 添加总分数到学生的成绩字典中student_results.append(student_scores)# 输出学生成绩print(f"学生 {student_name} 的成绩已成功查询。总分为: {total_score}")except (NoSuchElementException, WebDriverException) as e:# 如果查询出错，记录错误并跳过当前学生print(f"查询学生 {student_name} 成绩时发生错误: {e}. 跳过此学生.")continue# 每个学生完成后，写入Excelwrite_to_excel(all_results, student_results)# 关闭浏览器
driver.quit()