# -*- coding:utf-8 -*- import os, sys, time import xlrd import xlwt import json # 字典不排序; from collections import OrderedDict from xlwt import XFStyle, Pattern from UIT_treeConstant import TreeConst from UIT_log import info,debug,error INFO =True DEBUG =True ERROR = True class CExcelParser(): cls = "CExcelParser" def __init__(self, xls_path=None,UITree=None): self.UITree = UITree if type(xls_path) == str: xls_path = xls_path.decode('utf-8') self.xls_path = xls_path def read_excel(self, path=None): if path is not None: if type(path) == str: path = path.decode('utf-8') self.xls_path = path # 打开文件; wb = xlrd.open_workbook(filename=self.xls_path) if wb is None: return # 获取所有sheet; sheet = None for sh_name in wb.sheet_names(): sheet = wb.sheet_by_name(sh_name) self.parse_excel(sheet, False) # print "self.valueParams.values:", self.valueParams.values, type(self.valueParams.values) # print "self.pathParams.paths:", self.pathParams.paths, type(self.pathParams.paths) # try: # print json.dumps(self.pathParams.paths) # print '\r\n' # print json.dumps(self.valueParams.values) # print '\r\n' # # print self.pathParams.paths,'\r\n', self.valueParams.values # except Exception, e: # print e # endfun def parse_excel(self, sheet, bpath=True): pKeys = sheet.row_values(0) info(self.cls,"parse_excel","params sheet %s keys: %s"%(sheet.name,str(pKeys)),INFO) parentName = None if u"Value" == sheet.name: for i in range(1, sheet.nrows): # 获取每行内容; oneRow = tuple(sheet.row_values(i)) rowDict = {} for index in range(oneRow.__len__()): item = oneRow[index] rowDict[pKeys[index]] = item if rowDict[TreeConst.Value_Name] is None or rowDict[TreeConst.Value_Name].__len__() < 1: if parentName is None: error(self.cls, "parse_excel", "Level %s error row %s:" % (sheet.name, str(oneRow)), ERROR) break else: rowDict[TreeConst.Value_Name] = parentName self.UITree.valueParams.addOption(rowDict) elif rowDict[TreeConst.Value_Name].__len__() > 0: parentName = rowDict[TreeConst.Value_Name] self.UITree.valueParams.addParent(rowDict) elif "Dialog" == sheet.name: self.parsePath(sheet, pKeys, self.UITree.dialogParams) else: # 路径; self.parsePath(sheet,pKeys, self.UITree.pathParams) def parsePath(self, sheet,pKeys, treeParams): parentName = None for i in range(1, sheet.nrows): # 获取每行内容; oneRow = tuple(sheet.row_values(i)) rowDict = {} for index in range(oneRow.__len__()): item = oneRow[index] rowDict[pKeys[index]] = item if rowDict[TreeConst.Parent_Name] is None or rowDict[TreeConst.Parent_Name].__len__() < 1: if parentName is None: error(self.cls, "parse_excel", "Level %s error row %s:" % (sheet.name, str(oneRow)), ERROR) break else: rowDict[TreeConst.Parent_Name] = parentName treeParams.addOption(sheet.name, rowDict) elif rowDict[TreeConst.FLevel_Params[0]].__len__() > 0: parentName = rowDict[TreeConst.Parent_Name] treeParams.addParent(sheet.name, rowDict) # endfun def saveExcelData(self, checkOptionPathExcelData, repeatOptionList, all_UI_TestLanguagePath): # 设置字体 font = xlwt.Font() font.bold = True # 设置边框 borders = xlwt.Borders() borders.left = xlwt.Borders.THIN borders.right = xlwt.Borders.THIN borders.top = xlwt.Borders.THIN borders.bottom = xlwt.Borders.THIN # 设置居中 alignment = xlwt.Alignment() # alignment.horz = xlwt.Alignment.HORZ_CENTER # 水平方向 alignment.horz = xlwt.Alignment.HORZ_LEFT # 水平方向 alignment.vert = xlwt.Alignment.VERT_TOP # 垂直方向 alignment.wrap = 1 # 设置背景颜色 pattern = xlwt.Pattern() pattern.pattern = xlwt.Pattern.SOLID_PATTERN pattern.pattern_fore_colour = 3 # 背景颜色 # 定义不同的excel style style1 = xlwt.XFStyle() style1.font = font style1.borders = borders style1.alignment = alignment style2 = xlwt.XFStyle() style2.borders = borders style2.alignment = alignment style = XFStyle() style.borders = borders style.alignment = alignment pattern = Pattern() pattern.pattern = Pattern.SOLID_PATTERN pattern.pattern_fore_colour = xlwt.Style.colour_map['red'] # 设置单元格背景色为黄色 style.pattern = pattern # style_align = xlwt.easyxf('align: wrap on') if os.path.exists(all_UI_TestLanguagePath): try: os.remove(all_UI_TestLanguagePath) except Exception, e: print e return False # 创建工作簿; book = xlwt.Workbook(encoding='utf-8') # 创建sheet; sheet = book.add_sheet(u'到达Option路径检测', cell_overwrite_ok=True) # 设置表格自适应宽度的数组 col_width = [2, 25, 15, 120] # 创建标题行; row0 = [u'序号', u'Option名称', u'检测到达路径结果', u'到达Option路径'] for i in range(len(row0)): sheet.write(0, i, row0[i], style=style1) index = 1 for checkdata in checkOptionPathExcelData: sheet.write(index, 0, index, style=style2) for j in range(len(checkdata)): # 如果结果不为Pass则在表格中标红 if j == 1 and checkdata[j] != 'Pass': sheet.write(index, j + 1, checkdata[j], style=style) else: # if j == 1: # sheet.write(index, j + 1, checkdata[j], style=style_align) # else: sheet.write(index, j + 1, checkdata[j], style=style2) # if col_width[j] < self.len_byte(checkdata[j]): # col_width[j] = self.len_byte(checkdata[j]) index += 1 # 设置栏位宽度，栏位宽度小于10时候采用默认宽度 for i in range(len(col_width)): if col_width[i] > 10: sheet.col(i).width = 256 * (col_width[i] + 1) sheet2 = book.add_sheet(u'Option查重', cell_overwrite_ok=True) # 设置表格自适应宽度的数组 col_width2 = [2, 30, 30] # 创建标题行; row02 = [u'序号', u'重复Option名称', u'重复Option在源表格的sheet名称'] for i in range(len(row02)): sheet2.write(0, i, row02[i], style=style1) index2 = 1 for repeatOption in repeatOptionList: sheet2.write(index2, 0, index2, style=style2) for j in range(len(repeatOption)): sheet2.write(index2, j + 1, repeatOption[j], style=style2) index2 += 1 # 设置栏位宽度，栏位宽度小于10时候采用默认宽度 for i in range(len(col_width2)): if col_width2[i] > 10: sheet2.col(i).width = 256 * (col_width2[i] + 1) # 保存xls; try: book.save(all_UI_TestLanguagePath) return True except Exception, e: print e return False # 新增表格查询接口 # # 获取表格Value层级中，value_name下包含的value列表 # def getSubValueList(self, value_name): # valueList = [] # if self.valueParams.values.has_key(value_name): # # print parser.valueParams.values["source"] # # print parser.valueParams.values["source"]["value"] # valueDictList = self.valueParams.values[value_name]["value"] # for valueDict in valueDictList: # valueList.append(valueDict["value"]) # # print "valueList:", valueList, type(valueList) # return valueList if __name__ == "__main__": # path = r'E:\svn\SAT\项目资料\第四期-推广使用和画面检测\06-设计文档\01-蓝图设计\UiTree3.0方案\MenuTree_MS6586_EU.xls' # path = r'E:\SAT\resource\MenuTree\MS3663\DVB-T2 panasonic\MenuTree.xls' # path = r'E:/SAT/resource\MenuTree\MS6586\ATSC\MenuTree.xls' path = r'E:\SAT\resource\MenuTree\MS6586\ATSC\MenuTree_DVBT.xls' # path = r'E:\MenuTree.xls' # path = r'E:\SAT\resource\MenuTree\6586\MS6586_ATSC\MenuTree.xls' # path = getMenuTree3SelectedProjectCfgPath() + '\\MenuTree.xls' parser = CExcelParser(path) parser.read_excel() dataDict = parser.get_parent_ocr_dict("network") print "dataDict:", dataDict, type(dataDict) # parent = "source" # option = "atv" # parser.getOptionTextList(parent, option) # value_name = "source" # value = "atv" # parser.getValueTextList(value_name, value) # SubValueList1 = parser.getSubValueList("source") # SubValueList2 = parser.getSubValueList("backlight") # parent = "picture" # parent = "manual_tuning" # parser.getSubOptionList(parent) # optionList = [] # paths = parser.pathParams.paths # # for level in paths: # if paths[level].has_key(parent): # optionDictList = paths[level][parent]["value"] # for optionDict in optionDictList: # optionList.append(optionDict["option"]) # print "optionList:", optionList, type(optionList) # print parser.get_value("source", "") # checkOptionPathExcelData = parser.getCheckOptionPathExcelData() # print "checkOptionPathExcelData:", checkOptionPathExcelData # repeatOptionList = parser.getRepeatOptionList() # isSaveExcelTrue = parser.saveExcelData(checkOptionPathExcelData, repeatOptionList, # r'E:\MenuTree_Check_Option_Result.xls') # path = r'E:\SAT\resource\MenuTree\6586\MS6586_ATSC\MenuTree.xls' # path = getMenuTree3SelectedProjectCfgPath() + '\\MenuTree.xls' # print "isSaveExcelTrue:", isSaveExcelTrue # valueSheetOptionList = parser.getValueSheetOptionList() # repeatValueName_ValueSheet = parser.getRepeatValueName_ValueSheet() # repeatOption_otherSheet = parser.getRepeatOption_otherSheet() # optionPath, optionPathResult = parser.checkOptionPath("picture_preset11") # print "optionPath:", optionPath, type(optionPath) # print "optionPathResult:", optionPathResult, type(optionPathResult) # optionPath, optionPathResult = parser.checkOptionPath("instant_power_on") # print "optionPath:", optionPath, type(optionPath) # print "optionPathResult:", optionPathResult, type(optionPathResult) # path_params = parser.get_option_paths("picture_preset") # print "path_params:", path_params, type(path_params) # print "path_params[0][0]:",path_params[0][0] # print parser.get_option_paths('picture') # print parser.get_option('picture') # print parser.get_value('backlight',10) # print parser.get_parent_ocr_dict('source_hdmi1') # print parser.get_parent_ocr_list('channel') # print parser.get_parent_ocr_dict('channel') # print parser.get_parent_ocr_dict('other_setting') # print parser.get_parent_ocr_list('5khz') # print parser.get_parent_ocr_dict('picture_preset', True) # print parser.get_pair_values('picture_preset', False) # print parser.get_pair_values('source') # print parser.get_ocr_list('Second','picture','picture_preset') # vp, pp = parser.get_menu_paths("auto_volume_control", "off") # print vp # vp, pp = parser.get_menu_paths("picture_preset", "dynamic") # print vp # pp = parser.get_option_paths('picture_preset') # print pp.__len__(), pp[g_level[0]] # print "\r\nvp:", vp, type(vp) # print "\r\npp:", pp, type(pp) # # print json.dumps(pp) # revpp = reversed(pp) # for item in revpp: # print item, pp[item] # print 'value:\r\n' # print vp # # print "revpp:", revpp, type(revpp)