8.使用XLRD读取用户数据（Excel）

首先要安装【xlrd（读取excel文件数据）/xlsxWrite（写excel文件）】：python处理excel的模块。然后需要在获取用户信息的py脚本中增加从excel获取数据的脚本。在执行脚本中修改，主要为获取路径的修改。结果依然和从文本文档调用用户信息的检测结果一样。

PS：获取excel数据要注意type，如果是数字会报错，如123会被读成123.0.

然后需要在获取用户信息的py脚本中增加从excel获取数据的脚本：

#coding:utf-8
import codecs
import xlrd

class XlUserinfo(object):#添加一个获取excel的接口
    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):#逐行读取内容,1表示从第二行开始因为第一行是表头不需要
            #检测每行抓取的信息是否是foat
            info = [self.floattostr(val) for val in self.sheet.row_values(row)]
            tmp = zip(listkey, info)#
            infolist.append(dict(tmp))#将内容和表头组成字典
        return infolist #返回一个由字典组成的列表
    def get_sheetinfo_by_name(self,name):#xl支持通过名字获取表格
        self.sheet = self.xl.sheet_by_name(name)
        return self.get_sheet_info()
    def get_sheetinfo_by_index(self,index):#xl支持通过索引 获取表格
        self.sheet=self.xl.sheet_by_index(index)
        return self.get_sheet_info()
    
def get_webinfo(path):
    web_info={}
    #config=open(path)
    config=codecs.open(path,'r',encoding= 'utf-8-sig')#'r'只读
    #df.to_csv(path, index=False,encoding= u'utf-8')
    #config=codecs.open(path, 'r',encoding= u'utf-8')
    for line in config:
        result=[ele.strip() for ele in line.split('=')]
        web_info.update(dict([result]))

    return web_info

def get_userinfo(path):
    user_info=[]
    #config=open(path)
    config=codecs.open(path,'r',encoding= 'utf-8-sig')#'r'只读
    for line in config:
        user_dict={}
        result=[ele.strip() for ele in line.split(';')]
        for r in result:
            account=[ele.strip() for ele in r.split('=')]
            user_dict.update(dict([account]))
        user_info.append(user_dict)
    return user_info
            
#print (get_webinfo(r'C:/Users/Yang Xiu Yu/Desktop/webinfo.txt'))

if __name__=='__main__':#---------------------------------2
    
    xinfo = XlUserinfo(r'C:/Users/Yang Xiu Yu/Desktop/userinfo.xlsx')
    info = xinfo.get_sheetinfo_by_index(0)
    print (info)
    info = xinfo.get_sheetinfo_by_name('Sheet1')
    print (info)

在执行脚本中修改：主要为获取路径的修改：

#coding:utf-8
from selenium import webdriver
from selenium.webdriver.common.action_chains import ActionChains #模拟鼠标
from selenium.webdriver.support.ui import WebDriverWait
import time
from userdata import get_webinfo,get_userinfo,XlUserinfo#---------1
from log_module import Loginfo 


def get_ele_times(driver,times,func):
    return WebDriverWait(driver,times).until(func)

def openBrower():#打开浏览器
    webdriver_handle = webdriver.Firefox()
    return webdriver_handle

def openUrl(handle,url):#打开网址
    handle.get(url)
    #handle.maximize_window()

def findElement(d, arg):#arg must be a dict
    if 'text_id' in arg:
        ele_login=get_ele_times(d,10,lambda d: d.find_element_by_link_text(arg['text_id']))
        ele_login.click()
    useEle=d.find_element_by_css_selector(arg['userid'])
    pwdEle=d.find_element_by_css_selector(arg['pwdid'])
    loginEle=d.find_element_by_id(arg['loginid'])
    return useEle,pwdEle,loginEle

def sendVals(eletuple,arg):
    '''
    ele tuple
    account:uname,pwd
    '''
    listkey = ['uname','pwd']
    i = 0
    for key in listkey:
        eletuple[i].send_keys('')
        eletuple[i].clear()
        eletuple[i].send_keys(arg[key])
        i+=1
        
    eletuple[2].click()

def checkResult(d,err_id,arg,log):
    result = False #代表检查结果错误
    time.sleep(2)
    try:
        err=d.find_element_by_css_selector(err_id)
        print("Account And Pwd Error!")
        
        e=err.text #把抓取的值赋值给变量e，以变量e的格式储存
        #str(err.text)str无法直接读取
        msg='uname=%s pwd=%s:error:%s\n'%(arg['uname'],arg['pwd'],e)
        print(str(e)) #解析e以string的方式，显示当时错误提示的文字
        log.log_write(msg)
    except:
        msg='uname=%s pwd=%s:pass\n'%(arg['uname'],arg['pwd'])
        log.log_write(msg)
        print("Account And Pwd Rignt!")
        result = True 
    return result

def logout(d,ele_dict):#-新增接口-
    #d.find_element_by_class_name(ele_dict['usermenu']).click()#注销所属目录现网页已经没有了
    d.find_element_by_css_selector(ele_dict['logout']).click()#注销
    
def login_test(ele_dict,user_list):
    d=openBrower()#打开浏览器
    log=Loginfo()
    openUrl(d,ele_dict['url'])#打开网址
     #5
    
    ele_tuple=findElement(d, ele_dict)
    for arg in user_list:
        sendVals(ele_tuple,arg)
        #arg是账户和密码
        result = checkResult(d,ele_dict['errorid'],arg,log)
        if result:#如果返回True也就是登陆成功则回复现场1.注销logout2.重新登陆login
            logout(d,ele_dict)
            ele_tuple=findElement(d,ele_dict)#重新login
    log.log_close()

if __name__ == '__main__':#--------------------------------1
    
    ele_dict= get_webinfo(r'C:/Users/Yang Xiu Yu/Desktop/webinfo.txt')
    #user_list=get_userinfo(r'C:\Users\Yang Xiu Yu\Desktop\userinfo.txt')旧路径从文本
    xinfo = XlUserinfo(r'C:/Users/Yang Xiu Yu/Desktop/userinfo.xlsx')
    user_list = xinfo.get_sheetinfo_by_index(0)
    login_test(ele_dict,user_list)