摘要: 在关键字驱动测试框架中, 除了 POM 模式以及一些常规 Action 的封装外, 一个很重要的内容就是读写 Excel, 在团队中如何让不会写代码的人也可以进行自动化测试? 我们可以将自动化测试用例按一定的规格写到 Excel 中去 (如下图所示)
然后通过代码实现对具备这种规格的 Excel 进行解析, 让你的代码获取 Excel 中的步骤, 关键字, 页面元素定位, 操作方式, 最后在写入执行结果, 附上异常截图即可; 团队中不会写代码的人居多, 改改 Excel 执行也可以实现自动化测试
此处在初始化类的时候定义了两个颜色放进字典中, 之后会当做参数传给写 Excel 的函数, 当测试用例执行通过 用绿色字体标注 pass, 当执行失败的时候用红色字体标注 failed
具体实现代码如下
- # 用于实现读取 Excel 数据文件代码封装
- # encoding = utf-8
- """__project__ ='KeyDri'__author__ ='davieyang'__mtime__ ='2018/4/21'"""
- import openpyxl
- from openpyxl.styles import Border, Side, Font
- import time
- class ParseExcel(object):
- def __init__(self):
- self.workBook = None
- self.excelFile = None
- self.font = Font(color=None)
- self.RGBDict = {'red': 'FFFF3030', 'green': 'FF008B00'}
- def loadWorkBook(self, excelPathAndName):
- # 将 Excel 加载到内存, 并获取其 workbook 对象
- try:
- self.workBook = openpyxl.load_workbook(excelPathAndName)
- except Exception as e:
- raise e
- self.excelFile = excelPathAndName
- return self.workBook
- def getSheetByName(self, sheetName):
- # 根据 sheet 名获取该 sheet 对象
- try:
- # sheet = self.workBook.get_sheet_by_name(sheetName)
- sheet = self.workBook[sheetName]
- return sheet
- except Exception as e:
- raise e
- def getSheetByIndex(self, sheetIndex):
- # 根据 sheet 的索引号获取该 sheet 对象
- try:
- # sheetname = self.workBook.get_sheet_names()[sheetIndex]
- sheetname = self.workBook.sheetnames[sheetIndex]
- except Exception as e:
- raise e
- # sheet = self.workBook.get_sheet_by_name(sheetname)
- sheet = self.workBook[sheetname]
- return sheet
- def getRowsNumber(self, sheet):
- # 获取 sheet 中有数据区域的结束行号
- return sheet.max_row
- def getColsNumber(self, sheet):
- # 获取 sheet 中有数据区域的结束列号
- return sheet.max_column
- def getStartRowNumber(self, sheet):
- # 获取 sheet 中有数据区域的开始的行号
- return sheet.min_row
- def getStartColNumber(self, sheet):
- # 获取 sheet 中有数据区域的开始的列号
- return sheet.min_column
- def getRow(self, sheet, rowNo):
- # 获取 sheet 中某一行, 返回的是这一行所有数据内容组成的 tuple
- # 下标从 1 开始, sheet.rows[1] 表示第一行
- try:
- # return sheet.rows[rowNo - 1] 因为 sheet.rows 是生成器类型, 不能使用索引
- # 转换成 list 之后再使用索引, list(sheet.rows)[2] 这样就获取到第二行的 tuple 对象.
- return list(sheet.rows)[rowNo - 1]
- except Exception as e:
- raise e
- def getCol(self, sheet, colNo):
- # 获取 sheet 中某一列, 返回的是这一列所有数据内容组成的 tuple
- # 下标从 1 开始, sheet.columns[1] 表示第一列
- try:
- return list(sheet.columns)[colNo - 1]
- except Exception as e:
- raise e
- def getCellOfValue(self, sheet, coordinate = None, rowNo = None, colNo = None):
- # 根据单元格所在的位置索引获取该单元格中的值, 下标从 1 开始
- # sheet.cell(row = 1, column = 1).value, 表示 Excel 中的第一行第一列的值
- if coordinate is not None:
- try:
- return sheet.cell(coordinate=coordinate).value
- except Exception as e:
- raise e
- elif coordinate is None and rowNo is not None and colNo is not None:
- try:
- return sheet.cell(row=rowNo, column=colNo).value
- except Exception as e:
- raise e
- else:
- raise Exception("Insufficient Coordinates of cell!")
- def getCellOfObject(self, sheet, coordinate = None, rowNo = None, colNo = None):
- # 获取某个单元格对象, 可以根据单元格所在的位置的数字索引, 也可以直接根据 Excel 中单元格的编码及坐标
- # 如 getCellOfObject(sheet, coordinate='A1) or getCellOfObject(sheet, rowNo = 1, colNo = 2)
- if coordinate is not None:
- try:
- return sheet.cell(coordinate=coordinate)
- except Exception as e:
- raise e
- elif coordinate is None and rowNo is not None and colNo is not None:
- try:
- return sheet.cell(row=rowNo, column=colNo)
- except Exception as e:
- raise e
- else:
- raise Exception("Insufficient Coordinates of cell!")
- def writeCell(self, sheet, content, coordinate = None, rowNo = None, colNo = None, style=None):
- # 根据单元格在 Excel 中的编码坐标或者数字索引坐标向单元格中写入数据, 下标从 1 开始
- # 参数 style 表示字体的颜色的名字, 如 red,green
- if coordinate is not None:
- try:
- sheet.cell(coordinate=coordinate).value = content
- if style is not None:
- sheet.cell(coordinate=coordinate).font = Font(color=self.RGBDict[style])
- self.workBook.save(self.excelFile)
- except Exception as e:
- raise e
- elif coordinate is None and rowNo is not None and colNo is not None:
- try:
- sheet.cell(row=rowNo, column=colNo).value = content
- if style is not None:
- sheet.cell(row=rowNo, column=colNo).font = Font(color=self.RGBDict[style])
- self.workBook.save(self.excelFile)
- except Exception as e:
- raise e
- else:
- raise Exception("Insufficient Coordinates of cell!")
- def writeCellCurrentTime(self, sheet, coordinate = None, rowNo = None, colNo = None):
- # 写入当前时间, 下标从 1 开始
- now = int(time.time()) # 显示为时间戳
- timeArray = time.localtime(now)
- currentTime = time.strftime("%Y-%m-%d %H:%M:%S", timeArray)
- if coordinate is not None:
- try:
- sheet.cell(coordinate=coordinate).value = currentTime
- self.workBook.save(self.excelFile)
- except Exception as e:
- raise e
- elif coordinate is None and rowNo is not None and colNo is not None:
- try:
- sheet.cell(row=rowNo, column=colNo).value = currentTime
- self.workBook.save(self.excelFile)
- except Exception as e:
- raise e
- if __name__ == "__main__":
- from Configurations.VarConfig import dataFilePath163
- pe = ParseExcel()
- pe.loadWorkBook(dataFilePath163)
- print("通过名称获取 sheet 对象名字:")
- pe.getSheetByName(u"联系人")
- print("通过 Index 序号获取 sheet 对象的名字")
- pe.getSheetByIndex(0)
- sheet = pe.getSheetByIndex(0)
- print(type(sheet))
- print(pe.getRowsNumber(sheet))
- print(pe.getColsNumber(sheet))
- cols = pe.getCol(sheet, 1)
- for i in cols:
- print(i.value)
- # 获取第一行第一列单元格内容
- print(pe.getCellOfValue(sheet, rowNo=1, colNo=1))
- pe.writeCell(sheet, u'中国北京', rowNo=11, colNo=11, style='red')
- pe.writeCellCurrentTime(sheet, rowNo=10, colNo=11)
那么我们的测试数据除了放在 Excel 中, 还可以存储在 xml 里, 然后关键字驱动框架中提供解析 xml 的 API
- # encoding = utf-8
- """__title__ =''
- __author__ = 'davieyang'
- __mtime__ = '2018/4/21'
- """
- from xml.etree import ElementTree
- class ParseXML(object):
- def __init__(self, xmlPath):
- self.xmlPath = xmlPath
- def getRoot(self):
- # 打开将要解析的 xml 文件
- tree = ElementTree.parse(self.xmlPath)
- # 获取 xml 文件的根节点对象, 然后返回给调用者
- return tree.getroot()
- def findNodeByName(self, parentNode, nodeName):
- # 通过节点的名字获取节点对象
- nodes = parentNode.findall(nodeName)
- return nodes
- def getNodeofChildText(self, node):
- # 获取节点 node 下所有子节点的节点名作为 key, 本节点作为 value 组成的字典对象
- childrenTextDict = {i.tag: i.text for i in list(node.iter())[1:]}
- # 上面代码等价于
- '''
- childrenTextDict = {}
- for i in list(node.iter())[1:]:
- fhildrenTextDict[i.tag] = i.text
- '''
- return childrenTextDict
- def getDataFromXml(self):
- # 获取 xml 文档的根节点对象
- root = self.getRoot()
- # 获取根节点下所有名为 book 的节点对象
- books = self.findNodeByName(root, "book")
- dataList = []
- # 遍历获取到的所有 book 节点对象
- # 取得需要的测试数据
- for book in books:
- childrenText = self.getNodeofChildText(book)
- dataList.append(childrenText)
- return dataList
- if __name__ == "__main__":
- xml = ParseXML(r"F:\seleniumWithPython\TestData\TestData.xml")
- datas = xml.getDataFromXml()
- for i in datas:
- print(i["name"], i["author"])
来源: https://www.cnblogs.com/davieyang/p/10078086.html