【爬虫】Selenium模拟人工操作-20220323

【爬虫】Selenium模拟人工操作

爬虫是写一次，吐一次；写一次，讨厌一次。
还好，这个模拟人工操作的爬虫，终于写完了。

'''DLA考试开发逻辑'''
# 准备资料：考试排程
# 读取excel
# 登录DLA
# selenium上传DLA
# 在excel标记已完成


from selenium.webdriver.common.keys import Keys
import datetime,time
from unittest import result
import pandas as pd
from openpyxl import load_workbook
import openpyxl
from openpyxl.styles import Font, colors, Alignment,PatternFill,numbers,Border,Side
import os
from selenium import webdriver
from bs4 import BeautifulSoup



# 配置路径  for准备资料
file_address=r'E:\Python_E_20200601\others\DLA_test_apply\config'
file_address_output =r'E:\Python_E_20200601\others\DLA_test_apply\output'

chromedriver = "chromedriver.exe" #这里写本地的chromedriver 的所在路径
os.environ['webdriver.Chrome.driver'] = chromedriver #调用chrome浏览器
driver = webdriver.Chrome(chromedriver)


# 登录DLA
def Login_DLA():
    #登录网页
    driver.get('https://wzstrs.wistron.com/Logon/Logind.aspx') # 访问页面
    driver.maximize_window()
    time.sleep(1)

    #DLA
    DLA1 = driver.get('https://wzstrs.wistron.com/DLTrainingRoadmap/DLA001.aspx') # 访问页面
    time.sleep(2) # 等待两秒

    #点击query
    DLA_Query = driver.find_element_by_id('Query')
    DLA_Query.click() # 点击
    time.sleep(2) # 等待两秒

    # #点击new
    # DLA_new = driver.find_element_by_id('gvQuery_ctl01_btnNew')
    # DLA_new.click() # 点击
    # time.sleep(2) # 等待两秒

Login_DLA()




# selenium上传DLA
def DLA_test_apply(name_list_slice):
    ''' keyin excel记录 '''
    print('输入开始',name_list_slice)

    #Skill_Name
    Skill_Name = driver.find_element_by_id('drpSkillName')
    Skill_Name.send_keys(name_list_slice[0])
    time.sleep(0.5) # 等待两秒

    #Skill_Level
    Skill_Level = driver.find_element_by_id('drpSkillLevel')
    Skill_Level.send_keys(name_list_slice[1])
    time.sleep(0.5) # 等待两秒

    #Deadline1
    Deadline1 = driver.find_element_by_id('txtDeadlineDate').click()
    time.sleep(0.2) # 等待两秒
    driver.find_element_by_id('txtDeadlineDate').send_keys(name_list_slice[2])
    time.sleep(0.5) # 等待两秒

    #Deadline2
    #print("type of name_list_slice[3]",type(name_list_slice[3]))
    #print("name_list_slice[3]",name_list_slice[3])
    Deadline2 = driver.find_element_by_id('txtDeadlineTime').click()
    #关键在于四次向左
    Deadline2 = driver.find_element_by_id('txtDeadlineTime').send_keys(Keys.LEFT)
    Deadline2 = driver.find_element_by_id('txtDeadlineTime').send_keys(Keys.LEFT)
    Deadline2 = driver.find_element_by_id('txtDeadlineTime').send_keys(Keys.LEFT)
    Deadline2 = driver.find_element_by_id('txtDeadlineTime').send_keys(Keys.LEFT)
    driver.find_element_by_id('txtDeadlineTime').send_keys(name_list_slice[3])
    time.sleep(0.5) # 等待两秒

    #Test_Content
    Test_Content = driver.find_element_by_id('txtTContent')
    Test_Content.send_keys(name_list_slice[4])
    time.sleep(0.5) # 等待两秒

    #Venue
    Venue = driver.find_element_by_id('txtVenue')
    Venue.send_keys(name_list_slice[5])
    time.sleep(0.5) # 等待两秒

    #Start_Time1
    Start_Time1 = driver.find_element_by_id('Bsdate').click()
    time.sleep(0.2) # 等待两秒
    driver.find_element_by_id('Bsdate').send_keys(name_list_slice[6])
    time.sleep(0.5) # 等待两秒

    #Start_Time2
    Start_Time2 = driver.find_element_by_id('BsTime').click()
    #关键在于四次向左
    Start_Time2 = driver.find_element_by_id('BsTime').send_keys(Keys.LEFT)
    Start_Time2 = driver.find_element_by_id('BsTime').send_keys(Keys.LEFT)
    Start_Time2 = driver.find_element_by_id('BsTime').send_keys(Keys.LEFT)
    Start_Time2 = driver.find_element_by_id('BsTime').send_keys(Keys.LEFT)
    driver.find_element_by_id('BsTime').send_keys(name_list_slice[7])
    time.sleep(0.5) # 等待两秒

    #End_Time1
    End_Time1 = driver.find_element_by_id('Bedate').click()
    time.sleep(0.2) # 等待两秒
    driver.find_element_by_id('Bedate').send_keys(name_list_slice[8])
    # End_Time1 = driver.find_element_by_id('Bedate')
    # End_Time1.send_keys(name_list_slice[8])
    time.sleep(0.5) # 等待两秒
    
    #End_Time2
    End_Time2 = driver.find_element_by_id('BsTime').click()
    #关键在于四次向左
    End_Time2 = driver.find_element_by_id('BeTime').send_keys(Keys.LEFT)
    End_Time2 = driver.find_element_by_id('BeTime').send_keys(Keys.LEFT)
    End_Time2 = driver.find_element_by_id('BeTime').send_keys(Keys.LEFT)
    End_Time2 = driver.find_element_by_id('BeTime').send_keys(Keys.LEFT)
    End_Time2 = driver.find_element_by_id('BeTime').send_keys(Keys.LEFT)
    driver.find_element_by_id('BeTime').send_keys(name_list_slice[9])
    time.sleep(0.5) # 等待两秒

    #choice_YN
    choice_YN = driver.find_element_by_id('Bquestion0')
    choice_YN.send_keys(name_list_slice[10])
    time.sleep(0.5) # 等待两秒

    #choice_ABCD
    choice_ABCD = driver.find_element_by_id('Bquestion1')
    time.sleep(0.5) # 等待两秒
    print("name_list_slice[11]",name_list_slice[11])
    choice_ABCD.send_keys(name_list_slice[11])
    choice_ABCD.send_keys(Keys.LEFT)
    time.sleep(0.5) # 等待两秒

    #Monitors
    Monitors = driver.find_element_by_id('Bmonitor1')
    time.sleep(0.5) # 等待两秒
    Monitors.send_keys(name_list_slice[12])
    time.sleep(0.5) # 等待两秒




    #choice_ABCD
    choice_ABCD = driver.find_element_by_id('Bquestion1')
    time.sleep(0.5) # 等待两秒
    print("name_list_slice[11]",name_list_slice[11])
    choice_ABCD.send_keys(name_list_slice[11])
    choice_ABCD.send_keys(Keys.LEFT)
    time.sleep(0.5) # 等待两秒


    #students
    students = driver.find_element_by_id('txtEmplid')
    students.send_keys(name_list_slice[13])
    time.sleep(0.5) # 等待两秒


    #students
    students = driver.find_element_by_id('txtEmplid')
    students.send_keys(name_list_slice[13])
    time.sleep(0.5) # 等待两秒

    #ADD
    ADD = driver.find_element_by_id('BtnCheckEmplid')
    ADD.click() # 点击
    time.sleep(0.5) # 等待两秒


    #获取alert对话框
    alert = driver.switch_to_alert()
    alert.accept() 
    time.sleep(0.5) # 等待两秒


    #SAVE
    SAVE = driver.find_element_by_id('BtnSave')
    SAVE.click() # 点击
    time.sleep(1) # 等待两秒


    print('输入完成',name_list_slice)
    
    # 输入成功return 1；否则0
    return 1



# 读取excel
wb = openpyxl.load_workbook(os.path.join(file_address,'schedule.xlsx'))
print(wb.sheetnames)
sheet = wb.active
rows = sheet.max_row


# 读取所有excel的名单的信息
name_list = []
for i in range(2,rows+1):
    Skill_Name = sheet.cell(row=i,column=17).value
    Skill_Level = sheet.cell(row=i,column=18).value
    Deadline1 = sheet.cell(row=i,column=19).value.strftime('%Y%m%d')
    #print("Deadline1",i,Deadline1)
    Deadline2 = sheet.cell(row=i,column=20).value.strftime('%H:%M')
    print("Deadline2",i,Deadline2)
    Test_Content = sheet.cell(row=i,column=21).value
    Venue = sheet.cell(row=i,column=22).value
    Start_Time1 = sheet.cell(row=i,column=23).value.strftime('%Y%m%d')
    Start_Time2 = sheet.cell(row=i,column=24).value.strftime('%H:%M')
    End_Time1 = sheet.cell(row=i,column=25).value.strftime('%Y%m%d')
    End_Time2 = sheet.cell(row=i,column=26).value.strftime('%H:%M')
    choice_YN = sheet.cell(row=i,column=27).value
    choice_ABCD = sheet.cell(row=i,column=28).value
    Monitors = sheet.cell(row=i,column=29).value
    student = sheet.cell(row=i,column=2).value
    name_list.append([Skill_Name,Skill_Level,Deadline1,Deadline2,
                        Test_Content,Venue,Start_Time1,Start_Time2,
                        End_Time1,End_Time2,choice_YN,choice_ABCD,Monitors,student,
                        ])
    #print("name_list",name_list)


#将记录keyin进DLA
for j in range(len(name_list)):

    #点击new
    DLA_new = driver.find_element_by_id('gvQuery_ctl01_btnNew')
    DLA_new.click() # 点击
    time.sleep(2) # 等待两秒


    name_list_slice = name_list[j]
    if DLA_test_apply(name_list_slice):
        sheet.cell(row=2+j,column=30).value = 'Y'




#保存excel
wb.save(os.path.join(file_address_output,'schedule-output.xlsx'))
print("上传成功并保存Y记录")




# driver.close()
# print('ok')