中间反反复复很多次,本文只记下重点的几次
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()