python处理excel的模块

xlrd：读取excel文件shuju
XlsxWrite：写excel文件

安装xlrd：pip install xlrd
安装XlsxWrite:pip install XlsxWriter

xl = xlrd.open_workbook("test.xls")：打开excel
table = xl.sheets()[0]: 通过索引获取工作表
row=table.row_values(0):获取第一行内容
col=table.col_values(0):获取第一列整列内容
table.nrows：行数
table.ncols：列数
table.cell(0,0).value/table.cell_value(0,0)单元格值

>>> import xlrd
>>> xl = xlrd.open_workbook(r"D:\nd\自动化selenium\麦子学院\userinfo.xls")
>>> xl
<xlrd.book.Book object at 0x03370B70>
>>> xl.sheets()
[<xlrd.sheet.Sheet object at 0x03686B50>, <xlrd.sheet.Sheet object at 0x03686D10>, <xlrd.sheet.Sheet object at 0x03686DB0>]
>>> sheet = xl.sheets()[0]
>>> sheet.nrows
4
>>> sheet.ncols
2
>>> sheet.row_values(0)
['uname', 'pwd']
>>> sheet.cell(0,0).value
'uname'

主文件

from selenium import webdriver
import time
# 引入 ActionChains 类
from selenium.webdriver.common.action_chains import ActionChains
from selenium.webdriver.support.ui import WebDriverWait
from usedata import get_webinfo,get_userinfo,XlUserinfo
from log_module import Loginfo


#等待时间
def get_ele_times(driver,times,func):
    return WebDriverWait(driver,times).until(func)

#返回打开的浏览器句柄
def openBrower():
    """
    return: webdriver_handle
    """
    webdriver_handle = webdriver.Firefox()
    return webdriver_handle

#打开网页
def openUrl(handle,url):
    """
    :param handle: driver
    :param url: 网页
    :return: load url
    """
    handle.get(url)


#定义参数
def findElement(driver,arg):
    """
    arg must be dict
    1.text_id:弹出登录框
    2.userid:
    3.pwdid:
    4.loginid:
    return: useEle,pwdEle,loginEle
    """
    #弹出登录框
    if "text_id" in arg:
        ele_login = get_ele_times(driver, 10, \
                                  lambda driver: driver.find_element_by_css_selector(arg["text_id"]))
        ele_login.click()
    useEle =  driver.find_element_by_css_selector(arg["userid"])
    pwdEle = driver.find_element_by_css_selector(arg["pwdid"])
    loginEle = driver.find_element_by_css_selector(arg["loginid"])

    #返回的是一个tuple
    return useEle,pwdEle,loginEle

#输入内容
def sendVals(eletuple,arg):
    """
    :param eletuple:通过findElement(driver,arg)得到返回值-->useEle,pwdEle,loginEle
    eletuple = ("useEle" :"driver.find_element_by_css_selector(arg["userid"]")
                "pwdEle" : "driver.find_element_by_css_selector(arg["pwdid"]")
                "loginEle" : "driver.find_element_by_css_selector(arg["loginid"])")
    :param arg: {"uname":account,"pwd":pwd}
    :return:
    """
    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(driver,arg,log):
    # 判断是否登录成功，鼠标移动上面，判断弹窗字符
    result = False
    user_info_ele = driver.find_element_by_css_selector(".avatar_img")
    time.sleep(1)

    # 触发hover事件
    ActionChains(driver).move_to_element(user_info_ele).perform()

    #获取用户名元素
    user_name_ele = driver.find_element_by_css_selector(".username")
    print("====测试结果====")
    print(user_name_ele.text)
    name = user_name_ele.text
    if name == "测试账号":
        print("Account And Pwd Right!")
        msg = "%s:success:%s\n" % (arg, user_name_ele.text)
        log.log_write(msg)
        result = True
    else:
        print("Account And Pwd error")
        msg = "%s:error:not exit\n" % (arg)
        log.log_write(msg)
    return result

def logout(driver,ele_dict):
    user_info_ele = driver.find_element_by_css_selector(".avatar_img")
    time.sleep(3)
    ActionChains(driver).move_to_element(user_info_ele).perform()
    driver.find_element_by_css_selector(ele_dict["usermenu"]).click()


def login_test(ele_dict,user_list):
    driver = openBrower()
    log = Loginfo()
    openUrl(driver, ele_dict["url"])
    ele_tuple = findElement(driver,ele_dict)
    for arg in user_list:
        sendVals(ele_tuple,arg)
        result = checkResult(driver,arg,log)

        if result:
            #logout注销
            logout(driver, ele_dict)
            #login重新登录
            ele_tuple = findElement(driver, ele_dict)
    log.log_close()
    driver.quit()

if __name__ == '__main__':
    #通过txt文件读取数据
    ele_dict = get_webinfo(r"D:\nd\自动化selenium\麦子学院\webinfo.txt")
    #通过excel表读取数据
    xinfo = XlUserinfo()
    user_list = xinfo.get_sheetinfo_by_index(0)
    login_test(ele_dict,user_list)

usedata.py

#coding:utf-8
import codecs
import xlrd
def get_webinfo(path):
    web_info = {}
    results = []
    config = codecs.open(path,"r","utf-8")
    for line in config:
        result = [ele.strip() for ele in line.split("=")]
        results.append(result)
    web_info.update(dict(results))
    return web_info

def get_userinfo(path):
    user_info = []
    results = []
    config = codecs.open(path,"r","utf-8")
    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("=")]
            results.append(account)
        user_dict = dict(results)
        user_info.append(user_dict)
    return user_info

class XlUserinfo(object):
    def __init__(self,path = "D:\\nd\\自动化selenium\\麦子学院\\userinfo.xls"):
        self.xl = xlrd.open_workbook(path)

     #如果列表里是数字，回自动转换成浮点型
     #通过该方法转成int
    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):
            #通过floattostr方法，把数字转换成int，目前是float
            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):
        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__':
    # info = get_webinfo(r"D:\nd\自动化selenium\麦子学院\webinfo.txt")
    # userinfo = get_userinfo(r"D:\nd\自动化selenium\麦子学院\userinfo.txt")

    #通过excel读取数据
    xinfo = XlUserinfo()
    info = xinfo.get_sheetinfo_by_index(0)
    print(info)
    # info = xinfo.get_sheetinfo_by_name("Sheet1")
    # print(info)