scbc_repos/UITree3/ExcelParser.py

# -*- 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:
            # 如果是gbk字符,转utf8
            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):
                rowDict = {}
                # 获取每行内容;
                oneRow = tuple(sheet.row_values(i))
                # 遍历单元格;
                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)