提取试卷号为列表

import pandas as pd
import os

path = 'temp/形考成绩-.xlsx'
df = pd.read_excel(io=path, sheet_name='去重', dtype=str)
df = df[['试卷号']].astype(str)
paper_id_ls = df['试卷号'].unique()
paper_id_ls=sorted(paper_id_ls)
print(paper_id_ls)
txt = str(paper_id_ls)
# 删除已有文件
my_file = 'temp/paper_id.py'  # 文件路径
if os.path.exists(my_file):
    os.remove(my_file)

with open('temp/paper_id.py', 'a+') as f:
    f.write('paper_id = ' + txt)

形考成绩录入(补漏)

from selenium import webdriver

from selenium.common.exceptions import TimeoutException
from selenium.webdriver.common.by import By
import selenium.webdriver.support.expected_conditions as EC
import selenium.webdriver.support.ui as ui
import time, random

from 形考成绩录入.temp.paper_id import paper_id_ls
from 形考成绩录入.lib import user_info


# 等待提示


def wait_key():
    while True:
        message = input('请输入“空格键”继续：')
        if message == ' ':
            break
        print('你输入的是 ' + message + '!')


# 一直等待某元素可见，默认超时10秒
def is_visible(locator, timeout=10):
    try:
        ui.WebDriverWait(browser, timeout).until(EC.visibility_of_element_located((By.XPATH, locator)))
        return True
    except TimeoutException:
        return False


# 判断是否有弹出框
def alert_is_present(browser):
    try:
        alert = browser.switch_to.alert
        print(alert.text)
        return alert
    except:
        return False


browser = webdriver.Chrome()
url = 'http://121.28.25.166/StationWeb/pages/common/frameset.jsp'
browser.get(url)
# 登录
browser.find_element_by_name('j_username_temp').send_keys(user_info.username)
browser.find_element_by_name('j_password').send_keys(user_info.pswd)
browser.find_element_by_name('txtschool').send_keys(user_info.school_code)
browser.find_element_by_name('veryCode').click()
wait_key()

for paper_id in paper_id_ls:
    handles = browser.window_handles  # 获得所有窗口句柄
    browser.switch_to.window(handles[-1])  # 取最新的
    browser.switch_to.parent_frame()  # 返回父框架
    browser.switch_to.frame('main')  # 定位到main框架
    browser.switch_to.frame('Info')  # Info
    # todo 输入试卷号
    time.sleep(3)
    browser.find_element_by_name('paperId').clear()
    browser.find_element_by_name('paperId').send_keys(paper_id)
    browser.find_element_by_name('method').click()
    time.sleep(5)

    # 如果有弹出框 点击确定
    if alert_is_present(browser):
        browser.switch_to.alert.accept()
        print(paper_id + ' 录入失败,相应的计划开考科目没有形考项目或没报考记录！')
        time.sleep(2)
        with open('log.txt', 'a+') as f:
            f.write(paper_id + ' 录入失败,相应的计划开考科目没有形考项目或没报考记录！' + '\n')
    else:
        try:
            # 点击查询
            browser.find_element_by_css_selector(
                'table.DataInputTable:nth-child(4) > tbody:nth-child(1) > tr:nth-child(3) > td:nth-child(10) > a:nth-child(1)').click()
            time.sleep(2)
            handles = browser.window_handles  # 获得所有窗口句柄
            browser.switch_to.window(handles[-1])  # 取最新的
            time.sleep(3)
            # 点击查找进入下一页
            browser.find_element_by_xpath('/html/body/form/p/input').click()
            time.sleep(3)
            # 获取该试卷号下所有学生信息
            strsInfo = browser.find_element_by_xpath('/html/body/applet/param[2]').get_attribute('value')
            info_students_num = strsInfo.split(';')[0].split(',')
            # 获取该试卷号下所有学生成绩
            strsForm = browser.find_element_by_xpath('/html/body/applet/param[3]').get_attribute('value')
            info_scores = strsForm.split(';')

            # 查询成绩
            for k in range(len(info_scores)):
                print(k)
                if info_scores[k] == '':  # 如果该学生成绩为空
                    print(info_students_num[k])
                    # try:
                    # 查询成绩索引值
                    # student_id = student_nums.index(info_students_num[k])
                    # 通过索引获取成绩
                    # new_score = student_scores[student_id]
                    new_score = random.randint(92, 96)
                    # 修改成绩
                    js = 'document.querySelector("table.DataInputTable:nth-child(9) > tbody:nth-child(1) > tr:nth-child(5) > td:nth-child({}) > input:nth-child(2)").value="{}";'.format(
                        str(k * 4 + 1), new_score)
                    browser.execute_script(js)
                    # print(f'当前学生成绩{new_score}')
                    # with open('log.txt', 'a+') as f:
                    #     f.write(paper_id + ' ' + student_nums[k] + ' 录入成功！' + '\n')
                elif int(info_scores[k]) < 80:  # 如果该学生成绩小于80
                    # 给成绩赋值
                    new_score = 93
                    # 修改成绩
                    js = 'document.querySelector("table.DataInputTable:nth-child(9) > tbody:nth-child(1) > tr:nth-child(5) > td:nth-child({}) > input:nth-child(2)").value="{}";'.format(
                        str(k * 4 + 1), new_score)
                    # print(f'当前学生成绩{new_score}')
                    browser.execute_script(js)
            # 点击保存
            browser.find_element_by_xpath('/html/body/form/p[1]/input').click()
            time.sleep(3)
            alert = browser.switch_to.alert
            alert.accept()
            time.sleep(2)
            print(paper_id + ' 录入完毕！')
            with open('log.txt', 'a+') as f:
                f.write(paper_id + ' 录入完毕！' + '\n')
            browser.close()
            time.sleep(3)
        except Exception as e:
            print(e)
            print(paper_id + ' 录入失败,省校不准！')
            time.sleep(2)
            browser.close()
            with open('log.txt', 'a+') as f:
                f.write(paper_id + ' 录入失败,省校不准！' + '\n')
            time.sleep(2)
            continue

形考成绩录入统计(数据提取)

from selenium import webdriver
from selenium.webdriver.support.select import Select

from selenium.common.exceptions import TimeoutException
from selenium.webdriver.common.by import By
import selenium.webdriver.support.expected_conditions as EC
import selenium.webdriver.support.ui as ui
import time

from 形考成绩录入.lib import user_info

import pandas as pd


# 等待提示


def wait_key():
    while True:
        message = input('请输入“空格键”继续：')
        if message == ' ':
            break
        print('你输入的是 ' + message + '!')


# 一直等待某元素可见，默认超时10秒
def is_visible(locator, timeout=10):
    try:
        ui.WebDriverWait(browser, timeout).until(EC.visibility_of_element_located((By.XPATH, locator)))
        return True
    except TimeoutException:
        return False


# 判断是否有弹出框
def alert_is_present(browser):
    try:
        alert = browser.switch_to.alert
        print(alert.text)
        return alert
    except:
        return False


browser = webdriver.Chrome()
url = 'http://121.28.25.166/StationWeb/pages/common/frameset.jsp'
browser.get(url)
# 登录
browser.find_element_by_name('j_username_temp').send_keys(user_info.username)
browser.find_element_by_name('j_password').send_keys(user_info.pswd)
browser.find_element_by_name('txtschool').send_keys(user_info.school_code)
browser.find_element_by_name('veryCode').click()
wait_key()

browser.switch_to.parent_frame()  # 返回父框架
browser.switch_to.frame('main')  # 定位到main框架
browser.switch_to.frame('Info')  # Info
# todo 输入试卷号
time.sleep(5)

# 获取表头
table_title = ['序号']
for row in range(2, 9):
    txt = browser.find_element_by_xpath(f'/html/body/form/table[1]/tbody/tr[2]/td[{row}]/a/b').text
    table_title.append(txt)
for row in range(9, 12):
    txt = browser.find_element_by_xpath(f'/html/body/form/table[1]/tbody/tr[2]/td[{row}]/b').text
    table_title.append(txt)
datas_ls = []
try:
    for page_num in range(20):
        for row in range(3, 23):
            row_ls = []
            for col in range(1, 12):
                txt = browser.find_element_by_xpath(f'/html/body/form/table[1]/tbody/tr[{row}]/td[{col}]').text
                row_ls.append(txt)
            datas_ls.append(row_ls)
        time.sleep(2)
        page_num_ = browser.find_element_by_xpath('/html/body/form/table[2]/tbody/tr/td[2]').text
        print(f'第{page_num_}页数据获取完毕！')
        # browser.find_element_by_xpath('/html/body/form/table[2]/tbody/tr/td[4]/input').clear()
        # browser.find_element_by_xpath('/html/body/form/table[2]/tbody/tr/td[4]/input').send_keys(page_num+2)
        if page_num == 0:
            browser.find_element_by_xpath('/html/body/form/table[2]/tbody/tr/td[3]/input').click()
            '/html/body/form/table[2]/tbody/tr/td[4]/input'
        else:
            browser.find_element_by_xpath('/html/body/form/table[2]/tbody/tr/td[4]/input').click()
        time.sleep(30)
finally:
    df = pd.DataFrame(datas_ls, columns=table_title)
    writer = pd.ExcelWriter('rezult.xlsx')
    df.to_excel(writer, 'rezult', index=False)
    print('ok')
    writer.save()