python与selenium自动化基础-xlrd读取数据,Excel生成报告
代码如下:
from selenium import webdriver from selenium.webdriver.common.action_chains import ActionChains import time from log_module import Xlloginfo from userdata import get_webinfo,XlUserInfo def openBrower(): webdriver_handle = webdriver.Firefox() return webdriver_handle def openUrl(handle,url): handle.get(url) def findElement(d,arg): ele_login = d.find_element_by_link_text(arg['login_text']) ele_login.click() time.sleep(5) userEle = d.find_element_by_name(arg['userid']) pwdEle = d.find_element_by_name(arg['pwdid']) loginEle = d.find_element_by_css_selector('input[type="button"]') return userEle,pwdEle,loginEle def sendVals(eletuple,arg): listkey = ['uname','pwd'] i = 0 for key in listkey: eletuple[i].clear() eletuple[i].send_keys(arg[key]) i+=1 eletuple[2].click() def checkResult(d,xpath1,arg,log): result = False try: err = d.find_element_by_xpath(xpath1) print("Account and Pwd Error!") log.log_write(arg['uname'],arg['pwd'],'Error',err.text) except: print("Account and Pwd Right!") log.log_write(arg['uname'],arg['pwd'],'Pass') result = True return result def logout(d,ele_dict): time.sleep(5) ele = d.find_element_by_id(ele_dict['usermenu']) ActionChains(d).move_to_element(ele).perform() time.sleep(5) d.find_element_by_link_text(ele_dict['logout']).click() def login_test(ele_dict,user_list): d = openBrower() log = Xlloginfo() log.log_init('log','uname','pwd','result','msg') openUrl(d,ele_dict['url']) ele_tuple = findElement(d,ele_dict) for arg in user_list: sendVals(ele_tuple, arg) time.sleep(10) result = checkResult(d,ele_dict['errorid'],arg,log) if result: logout(d,ele_dict) time.sleep(3) ele_tuple = findElement(d,ele_dict) time.sleep(10) log.log_close() # 当.py文件被直接运行时,if __name__ == '__main__'之下的代码块将被运行;当.py文件以模块形式被导入时,if __name__ == '__main__'之下的代码块不被运行 if __name__ == '__main__': ele_dict = get_webinfo(r'G:\yaya\pycharm\webinfo') xlinfo = XlUserInfo(r'G:\yaya\pycharm\userinfo.xlsx') user_list = xlinfo.get_sheetinfo_by_name('Sheet1') login_test(ele_dict,user_list)
webinfo
url = https://www.imooc.com/ login_text = 登录 userid = email pwdid = password errorid=/html/body/div[9]/div[2]/div/form/div[1]/p usermenu=header-avator logout=安全退出
userinfo.xlsx
userdata.py
import xlrd def get_webinfo(path): web_info = {} config = open(path,encoding='UTF-8') for line in config: result = [ele.strip() for ele in line.split('=')] web_info.update(dict([result])) return web_info class XlUserInfo(object): def __init__(self,path=''): self.xl = xlrd.open_workbook(path) def floattostr(self,val): if isinstance(val,float): val = str(int(val)) return val def get_sheet_info(self): listkey = ['uname','pwd'] infolist = [] for row in range(1,self.sheet.nrows): info = [self.floattostr(val) for val in self.sheet.row_values(row)] #EXCEL中输入的是数字,默认时会当成浮点型 tmp = zip(listkey,info) infolist.append(dict(tmp)) return infolist def get_sheetinfo_by_name(self,name): self.sheet = self.xl.sheet_by_name(name) return self.get_sheet_info() def get_sheetinfo_by_index(self,index): self.sheet = self.xl.sheet_by_index(index) return self.get_sheet_info() if __name__ == '__main__': webinfo = get_webinfo(r'G:\yaya\pycharm\webinfo') for key in webinfo: print(key,webinfo[key]) userinfo = XlUserInfo(r'G:\yaya\pycharm\userinfo.xlsx') info = userinfo.get_sheetinfo_by_name('Sheet1') print(info)
log_module.py
import time import xlsxwriter class Loginfo(object): def __init__(self,path='',mode='w'): fname = path+time.strftime('%Y-%m-%d',time.gmtime()) self.log = open(path+fname+'.txt',mode,encoding='UTF-8') def log_write(self,msg): self.log.write(msg) def close(self): self.log.close() class Xlloginfo(object): def __init__(self,path=''): fname = path+time.strftime('%Y-%m-%d',time.gmtime()) self.row = 0 self.xl = xlsxwriter.Workbook(path+fname+'.xlsx') self.style = self.xl.add_format({'bg_color':'red'}) def xl_write(self,*args): col = 0 style = '' if 'Error' in args: style = self.style for val in args: self.sheet.write_string(self.row,col,val,style) col += 1 self.row+=1 def log_init(self,sheetname,*title): self.sheet = self.xl.add_worksheet(sheetname) self.sheet.set_column('A:E',30) self.xl_write(*title) def log_write(self,*args): self.xl_write(*args) def log_close(self): self.xl.close() if __name__ == '__main__': xlinfo = Xlloginfo() xlinfo.log_init('test','uname','pwd','result','info') xlinfo.log_close()
完