#-*- coding:utf-8 -*- import os, sys, time import xlrd import xlwt import sqlite3 import json from ssat_sdk.picture.feature_detect import FeatureDetect from ssat_sdk.utils import LoggingUtil from ssat_sdk.picture import image_util class CExcelData(): def __init__(self): # 编号; self.num = 0 # 类型; self.type = '' # 控件id; self.ctrlid = '' # 类型描述; self.desc = '' # 类型事件; self.event = '' # 类型参数; self.params = '' # 类型结果; self.typeresult = '' # 事件结果; self.eventresult = '' def getdata(self, data): if data is not None: self.num, self.type, self.ctrlid, self.desc, self.event, self.params, self.typeresult, self.eventresult = tuple(data) #end-fun def tolist(self): return [self.num, self.type, self.ctrlid, self.desc, self.event, self.params, self.typeresult, self.eventresult] #end-fun class CImgView(): def __init__(self): # 图元id; self.id = '' # 图元名称; self.name = '' # 图元类型：按钮、滑块 self.type = '' # 图元领空区域; self.airspace = [] # 图元未选时图标区域; self.iconarea = [] # 图元未选时文本区域; self.textarea = [] # 图元未选时文本内容; self.textcontent = '' # 图元未选时图标内容：使用路径; self.iconcontent = '' # 图元选中时图标区域; self.iconarea2 = [] # 图元选中时文本区域； self.textarea2 = [] # 图元选中时的文件内容; self.textcontent2 = '' # 图元选中时的图标内容; self.iconcontent2 = '' # 展开键; self.openkey = '' # 返回键; self.returnkey = '' # 弹出键; self.popkey = '' # 图元所属图区id; self.areaid = 0 # 图元关联图区id; self.nextareaid = 0 def getdata(self,data): self.id = data[0] self.name = data[1] self.type = data[2] self.airspace = [] if data[3] is None else json.loads(data[3]) self.iconarea = [] if data[4] is None else json.loads(data[4]) self.textarea = [] if data[5] is None else json.loads(data[5]) self.textcontent = data[6] self.iconcontent = data[7] self.iconarea2 = data[8] self.textarea2 = data[9] self.textcontent2 = data[10] self.iconcontent2 = data[11] self.openkey = data[12] self.returnkey = data[13] self.popkey = data[14] self.areaid = data[15] self.nextareaid = data[16] class CImgArea(): def __init__(self): self.id = '' self.name = '' self.isroot = False self.popkey = '' self.returnkey = '' self.examplepic = '' self.coodinate = [] self.bgproperty = '' self.uidtype = '' self.uidcontent = '' self.uidarea = [] self.layouttype = '' self.direction = '' self.method = '' self.refcoordFocusArea = [] self.refcoordIconArea = [] self.refcoordTextArea = [] self.methodParam = {} self.layoutParam = {} self.ocrParam = [] def getdata(self, data): self.id = data[0] self.name = data[1] self.isroot = data[2] self.popkey = data[3] self.returnkey = data[4] self.examplepic = data[5] self.coodinate = [] if data[6] is None else json.loads(data[6]) self.bgproperty = data[7] self.uidtype = data[8] self.uidcontent = data[9] self.uidarea = data[10] self.layouttype = data[11] self.direction = data[12] self.method = data[13] self.refcoordFocusArea = [] if data[14] is None else json.loads(data[14]) self.refcoordIconArea = [] if data[15] is None else json.loads(data[15]) self.refcoordTextArea = [] if data[16] is None else json.loads(data[16]) self.methodParam = {} if data[17] is None else json.loads(data[17]) self.layoutParam = {} if data[18] is None else json.loads(data[18]) self.ocrParam = [] if data[19] is None else json.loads(data[19]) #end-fun class CParameters(): def __init__(self): self.id = 0 self.areaid = 0 self.method = '' self.params = '' def getdata(self,data): self.id = data[0] self.areaid = data[1] self.method = data[2] self.params = data[3] if self.method == u'全局二值化': print(u'全局二值化') elif self.method == u'局部二值化': pass elif self.method == u'方差二值化': pass elif self.method == u'模板匹配': pass elif self.method == u'形状匹配': pass #end-fun class CExecutor(): def __init__(self, xls_path, db_path): # db连接对象; self.conn = None # 游标; self.cursor = None # excel路径; self.xls_path = xls_path # db路径; self.db_path = db_path # excel数据; self.xls_data = [] # 识别类对象; self.feature = FeatureDetect() #end-fun def read_excel(self, path = None): if path is None: path = self.xls_path #end-if # 打开文件; wb = xlrd.open_workbook(filename=path) if wb is None: return #通过索引获取表格; sheet1 = wb.sheet_by_index(0) for i in range(1, sheet1.nrows): # 获取行内容; rows = sheet1.row_values(i) data = CExcelData() data.getdata(rows) self.xls_data.append(data) #end-for #end-fun def get_conn(self, path = None): if path is None: path = self.db_path #end-if if os.path.exists(path) and os.path.isfile(path) : self.conn = sqlite3.connect(path) #end-if #end-fun def get_cursor(self) : if self.conn is not None: self.cursor = self.conn.cursor() else: self.cursor = self.get_conn().cursor() #end-fun def fetchone(self, sql, data) : if self.conn is None: return if sql is not None and sql != '' : if data is not None : #Do this instead d = (data, ) cu = self.cursor cu.execute(sql, d) rec = cu.fetchall() return rec else: print('the [{}] equal None!'.format(data)) return None else: print('the [{}] is empty or equal None!'.format(sql)) return None #end-fun def getImgViewData(self,id): sql = "SELECT ImgView.Id,"\ "ImgView.Name,"\ "ImgView.Type,"\ "ImgView.AirspaceArea,"\ "ImgView.IconArea,"\ "ImgView.TextArea,"\ "ImgView.TextContent,"\ "ImgView.IconContent,"\ "ImgView.IconArea2,"\ "ImgView.TextArea2,"\ "ImgView.TextContent2,"\ "ImgView.IconContent2,"\ "ImgView.OpenKey,"\ "ImgView.ReturnKey,"\ "ImgView.PopKey,"\ "ImgView.AreaId,"\ "ImgView.NextAreaId "\ "FROM ImgView WHERE id = ?" # 获取记录集; record = self.fetchone(sql, id) data = CImgView() if record is not None: if len(record) > 0: data.getdata(record[0]) return data return None #end-fun def getImgAreaData(self, id): sql = "SELECT ImgArea.Id,"\ "ImgArea.Name,"\ "ImgArea.IsRoot,"\ "ImgArea.PopKey,"\ "ImgArea.ReturnKey,"\ "ImgArea.ExamplePicture,"\ "ImgArea.Coordinate,"\ "ImgArea.BackgroundProperties,"\ "ImgArea.UIdType,"\ "ImgArea.UIdContent,"\ "ImgArea.UIdArea,"\ "ImgArea.LayoutType,"\ "ImgArea.Direction,"\ "ImgArea.Method,"\ "ImgArea.refcoordFocusArea,"\ "ImgArea.refcoordIconArea,"\ "ImgArea.refcoordTextArea,"\ "ImgArea.MethodParam,"\ "ImgArea.LayoutParam,"\ "ImgArea.OCRParam "\ "FROM ImgArea WHERE id = ?" # 获取记录集; record = self.fetchone(sql, id) data = CImgArea() if record is not None: if len(record) > 0: data.getdata(record[0]) return data return None #end-fun def getParameters(self, areaid): sql = "SELECT Parameter.Id,"\ "Parameter.AreaId,"\ "Parameter.Method,"\ "Parameter.Parameters "\ "FROM Parameter WHERE AreaId = ?" # 获取记录集; record = self.fetchone(sql, areaid) data = CParameters() if record is not None: if len(record) > 0: data.getdata(record[0]) return data return None #end-fun # 1、找到加返回True,否则False # 2、返回当前焦点坐标; def doVerifyOnTargetCtrl(self, pel_data, map_data): # 截图，原图路径; screenshot = os.path.join(LoggingUtil.getCaseRunLogDirPath(), "TVShot_" + time.strftime("%Y%m%d%H%M%S", time.localtime()) + ".png") self.feature.vp.takePicture(screenshot) # 根据方法分类执行; if map_data.method == u"全局二值化": contour = self.feature.getGrayBinaryContour(screenshot, map_data.coodinate, map_data.methodParam['gray'], 255, map_data.methodParam['inside'], map_data.methodParam['min-peri'], map_data.methodParam['max-peri'], map_data.methodParam['min-area'], map_data.methodParam['max-area'],) # 是否找到轮廓; if contour is None: return False, [], screenshot # 获取文本坐标; textbox = self.feature.getObjAbsoluteBox(contour, map_data.refcoordFocusArea, map_data.refcoordTextArea) # ocr识别; ocrImgPath = os.path.join(LoggingUtil.getCaseRunLogDirPath(), "OCR_" + time.strftime("%Y%m%d%H%M%S", time.localtime()) + ".png") image_util.saveCropPic(screenshot, ocrImgPath, textbox) result, text = self.feature.OCR.findPicStr(pel_data.textcontent, ocrImgPath, map_data.ocrParam[0], map_data.ocrParam[1], map_data.ocrParam[2]) # 返回结果; return result, contour, screenshot elif map_data.method == u"局部二值化": pass elif map_data.method == u"方差二值化": pass elif map_data.method == u"模板匹配": # 模板匹配函数需要改造; setting = {'method': 5, 'colorType': 0, 'thresholdVal': 0, 'thresholdMaxVal': 255, 'matchVal': map_data.methodParam['match'] } match_result = self.feature.matchSingleImage(screenshot, map_data.coodinate, map_data.methodParam['tempdir'], setting) if match_result is None: return False, [], screenshot else: # 获取文本坐标; textbox = self.feature.getObjAbsoluteBox(match_result['coordinate'], map_data.refcoordFocusArea, map_data.refcoordTextArea) # ocr识别; ocrImgPath = os.path.join(LoggingUtil.getCaseRunLogDirPath(), "OCR_" + time.strftime("%Y%m%d%H%M%S", time.localtime()) + ".png") image_util.saveCropPic(screenshot, ocrImgPath, textbox) result, text = self.feature.OCR.findPicStr(pel_data.textcontent, ocrImgPath, map_data.ocrParam[0], map_data.ocrParam[1], map_data.ocrParam[2]) # 返回结果; return result, match_result['coordinate'], screenshot elif map_data.method == u"形状匹配": pass # 不存在的方法,直接返回False; return False, [], screenshot #end-fun # 判断是否在目标图区上; # 根据唯一标识内容来判断; def doVerifyOnTargetMapArea(self, map_data): result, coodr = False, [] # 截图，原图路径; screenshot = os.path.join(LoggingUtil.getCaseRunLogDirPath(), "TVShot_" + time.strftime("%Y%m%d%H%M%S", time.localtime()) + ".png") self.feature.vp.takePicture(screenshot) if map_data.uidtype == u"图标": # 模板匹配函数需要改造; setting = {'method': 5, 'colorType': 0, 'thresholdVal': 0, 'thresholdMaxVal': 255, 'matchVal': map_data.methodParam['match'] } match_result = self.feature.matchSingleImage(screenshot, map_data.coodinate, map_data.methodParam['tempdir'], setting) if match_result is None: return False, [], screenshot else: return True, match_result['coordinate'], screenshot elif map_data.uidtype == u"文本": pass elif map_data.uidtype == u"颜色": pass elif map_data.uidtype == u"轮廓": pass elif map_data.uidtype == u"形状": pass elif map_data.uidtype == u"": pass # 返回结果; return result, coodr, screenshot # 执行事件; def doCtrlEvent(self, xls_data, pel_data, map_data): # 执行前截图分析; screenshot = os.path.join(LoggingUtil.getCaseRunLogDirPath(), "TVShot_" + time.strftime("%Y%m%d%H%M%S", time.localtime()) + ".png") self.feature.vp.takePicture(screenshot) # 类型判断; if pel_data.type == u"滑块": if xls_data.event == u"编辑": if map_data.layouttype == u"图独": # 获取文本坐标; textbox = self.feature.getObjAbsoluteBox(map_data.coodinate, map_data.refcoordFocusArea, map_data.refcoordTextArea) # ocr识别; ocrImgPath = os.path.join(LoggingUtil.getCaseRunLogDirPath(), "OCR_" + time.strftime("%Y%m%d%H%M%S", time.localtime()) + ".png") image_util.saveCropPic(screenshot, ocrImgPath, textbox) text = self.feature.OCR.getStrWithImgProcess(ocrImgPath, map_data.ocrParam[2], map_data.ocrParam[0], map_data.ocrParam[1]) if text is not None and text is not '': count = int(xls_data.params) - int(text) if count > 0: self.feature.redRat3.sendKey('right', count, 0.1) elif count < 0: self.feature.redRat3.sendKey('left', abs(count), 0.1) # 执行后截图分析; screenshot = os.path.join(LoggingUtil.getCaseRunLogDirPath(), "TVShot_" + time.strftime("%Y%m%d%H%M%S", time.localtime()) + ".png") self.feature.vp.takePicture(screenshot) # 验证结果是否正确;# ocr识别; ocrImgPath = os.path.join(LoggingUtil.getCaseRunLogDirPath(), "OCR_" + time.strftime("%Y%m%d%H%M%S", time.localtime()) + ".png") image_util.saveCropPic(screenshot, ocrImgPath, textbox) text = self.feature.OCR.getStrWithImgProcess(ocrImgPath, map_data.ocrParam[2], map_data.ocrParam[0], map_data.ocrParam[1]) if text is not None and text is not '': if int(xls_data.params) == int(text): return True, textbox, screenshot return False, textbox, screenshot else: print('ocr convert error!') return False, textbox, screenshot #end-if elif xls_data.event == u"单击": self.feature.redRat3.sendKey(pel_data.openkey) # 是否成功进入关联图区; elif xls_data.event == u"返回": self.feature.redRat3.sendKey(pel_data.returnkey) # 是否成功返回上层图区; elif xls_data.event == u"弹出": self.feature.redRat3.sendKey(pel_data.popkey) # 是否成功弹出某图区; #end-if elif pel_data.type == u"按钮": if xls_data.event == u"单击": self.feature.redRat3.sendKey(pel_data.openkey) # 是否成功进入关联图区; elif xls_data.event == u"返回": self.feature.redRat3.sendKey(pel_data.returnkey) # 是否成功返回上层图区; elif xls_data.event == u"弹出": self.feature.redRat3.sendKey(pel_data.popkey) # 是否成功弹出某图区; elif pel_data.type == u"编辑框": pass # 默认返回True; return True, [], screenshot #end-fun # 根据布局方式，到达目标控件; def doMove2TargetCtrl(self, pel_data, map_data): print pel_data.id, pel_data.name, pel_data.areaid, map_data.id, map_data.name key, result, curbox, lastbox, screenshot = '', False, [], [], '' if map_data.layouttype == u"网格": pass elif map_data.layouttype == u"纵向列表": # 开始位置;用于循环列表; beginbox = [] # 默认遍历方向; key = 'down' # 遍历计数; count = 0 # 是否逆序遍历; isAdverse = False while True: result, curbox, screenshot = self.doVerifyOnTargetCtrl(pel_data, map_data) # 找到目标焦点; if result is True: break # 正序遍历; if isAdverse is False: # 当前焦点框与上次焦点框相同,认为到达边界端; if curbox == lastbox: key = 'up'#改变寻路方向; # 标记已开始逆序 isAdverse = True # 根据count计数，直接返回原本位置; self.feature.redRat3.sendKey(key,count,0.2) # 循环列表完成一次遍历回到beginbox; if curbox == beginbox: break else: # 已逆序,且再次到达另一边界端; if curbox == lastbox: break # 记录起始位置; if beginbox == []: beginbox = curbox # 记录本次位置; lastbox = curbox # 下一位置; self.feature.redRat3.sendKey(key) #end-while elif map_data.layouttype == u"横向列表": # 开始位置;用于循环列表; beginbox = [] # 默认遍历方向; key = 'right' # 遍历计数; count = 0 # 是否逆序遍历; isAdverse = False while True: result, curbox, screenshot = self.doVerifyOnTargetCtrl(pel_data, map_data) # 找到目标焦点; if result is True: break # 正序遍历; if isAdverse is False: # 当前焦点框与上次焦点框相同,认为到达边界端; if curbox == lastbox: key = 'left' # 改变寻路方向; # 标记已开始逆序 isAdverse = True # 根据count计数，直接返回原本位置; self.feature.redRat3.sendKey(key, count, 0.2) # 循环列表完成一次遍历回到beginbox; if curbox == beginbox: break else: # 已逆序,且再次到达另一边界端; if curbox == lastbox: break # 记录起始位置; if beginbox == []: beginbox = curbox # 记录本次位置; lastbox = curbox # 下一位置; self.feature.redRat3.sendKey(key) # end-while elif map_data.layouttype == u"标签列表": pass elif map_data.layouttype == u"图独": return True, [], screenshot # 返回结果以及坐标; return result, curbox, screenshot #end-fun # 结果比较; def doResultComparison(self, row, xls_data): result, desc = True, '' # 解析json为字典; try: data = json.loads(row.params) except Exception, e: data = {} desc = str(e) if data is None or data == {}: return False, u'Json参数解析出错=%s'%('数据有误' if desc == '' else desc) # 根据描述选择方法; if row.event == u"示例": # [{"num":13},{"num":8}] param1 = data[0] param2 = data[1] # 解析对应的参数; for r in xls_data: if r.num == param1['num']: param1["data"] = r.eventresult if r.num == param2['num']: param2['data'] = r.eventresult #end-for print param1,param2 elif row.event == u"明暗对比": pass elif row.event == u"": pass elif row.event == u"": pass elif row.event == u"": pass elif row.event == u"": pass elif row.event == u"": pass elif row.event == u"": pass elif row.event == u"": pass # 默认返回值; return result, desc #end-fun # 保存结果; def doSaveResult(self, xls_data, save_path): # 创建工作簿; book = xlwt.Workbook() # 创建sheet; sheet = book.add_sheet(u'result', cell_overwrite_ok=True) # 创建标题行; row0 = [u'编号',u'类型', u'控件ID',u'描述',u'事件',u'事件参数',u'类型结果',u'事件结果'] for i in range(len(row0)): sheet.write(0, i, row0[i], style = xlwt.XFStyle()) index = 1 # 写入其他数据; for row in xls_data: l = row.tolist() for j in range(len(l)): sheet.write(index, j, l[j], style = xlwt.XFStyle()) index += 1 #end-for # 保存xls; book.save(save_path) #end-fun def run(self): # 读取xls self.read_excel() # 打开数据库; self.get_conn() # 获取游标; self.get_cursor() # 遍历excel数据; for row in self.xls_data: if row.type == u'控件操作': # 获取图元信息; pel_data = self.getImgViewData(row.ctrlid) if pel_data is not None: # 获取图元图区信息; map_data = self.getImgAreaData(pel_data.areaid) print pel_data, map_data, row if map_data is not None: # 执行弹出按键; # 注：如果多个控件连续且都在同一个根图区时，多次执行会出问题，需完善这里的判断机制; if map_data.isroot == 1: if self.doVerifyOnTargetMapArea(map_data)[0] is False: self.feature.redRat3.sendKey(map_data.popkey) self.feature.redRat3.sendKey('down') # 到达目标图元; result, coodr, screenshot = self.doMove2TargetCtrl(pel_data, map_data) # 结果; row.typeresult = u'{"结果":%s, "坐标":%s, "图像":%s}'%('True' if result is True else 'False', str(coodr), screenshot) if result is True: # 执行图元事件; result, coodr, screenshot = self.doCtrlEvent(row, pel_data, map_data) row.eventresult = u'{"结果":%s, "坐标":%s, "图像":%s}'%('True' if result is True else 'False', str(coodr), screenshot) else: print("unable find target ctrl") break else: print('map data is none') break else: print('pel data is none') break #end-if elif row.type == u'结果比较': result, desc = self.doResultComparison(row, self.xls_data) row.eventresult = u'{"结果":%s, "描述":%s}'%('True' if result is True else 'False', desc) elif row.type == u"截屏": # 默认截屏一张; screenshot = os.path.join(LoggingUtil.getCaseRunLogDirPath(), "TVShot_" + time.strftime("%Y%m%d%H%M%S", time.localtime()) + ".png") self.feature.vp.takePicture(screenshot) row.typeresult = screenshot #end-for # 保存结果; self.doSaveResult(self.xls_data, r'D:\result.xls') #end-fun if __name__ == "__main__": executor = CExecutor(r'F:\scbc-sat\command.xlsx', r'F:\scbc-sat\ui.db') executor.run()