scbc_repos/ssat_sdk/MenuTree3/TExcelParser.py

# -*- coding:utf-8 -*-
import os, sys, time
import xlrd
import xlwt
import json
# 字典不排序;
from collections import OrderedDict

from xlwt import XFStyle, Pattern

g_level = ['First', 'Second', 'Third', 'Fourth', 'Fifth', 'Sixth', 'Seventh', 'Eighth', 'Ninth', 'Tenth', 'Eleventh',
           'Twelfth']


class CPathParams():
    def __init__(self):
        self.paths = OrderedDict()

    def add_name(self, level, parent, move_key, enter_key, others):
        if level not in self.paths:
            self.paths[level] = {}
        # endif
        move_key = move_key.replace(' ', '')
        self.paths[level][parent] = {"move_key": move_key.split(';'), "enter_key": enter_key, "others": others,
                                     "value": []}

    def add_item(self, level, parent, option, ocr, move_key, enter_key, others):
        if level in self.paths:
            if parent not in self.paths[level]:
                self.paths[level][parent] = {}
        else:
            self.paths[level] = {}
            self.paths[level][parent] = {}
        self.paths[level][parent]["value"].append(
            {"option": option, "option_for_ocr": ocr, "option_move_key": move_key, "option_enter_key": enter_key,
             "option_others": others})


class CValueParams():
    def __init__(self):
        self.values = OrderedDict()

    def add_name(self, name, move_key, enter_key, others):
        if name not in self.values:
            move_key = move_key.replace(' ', '')
            self.values[name] = {"move_key": move_key.split(';'), "enter_key": enter_key, "others": others, "value": []}

    def add_item(self, name, option, ocr):
        if name in self.values:
            self.values[name]["value"].append({"value": option, "value_for_ocr": ocr})


class CExcelParser():
    def __init__(self, xls_path):
        if type(xls_path) == str:
            xls_path = xls_path.decode('utf-8')
        self.xls_path = xls_path
        self.pathParams = CPathParams()
        self.valueParams = CValueParams()

    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):
        last_name = None
        if u"Value" == sheet.name:
            for i in range(1, sheet.nrows):
                # 获取每行内容;
                rows = tuple(sheet.row_values(i))
                if rows[1].__len__() > 0 and rows[2].__len__() > 0 and last_name.__len__() > 0:
                    # print sheet.name,"last_name:",last_name
                    self.valueParams.add_item(last_name, rows[1], rows[2])
                elif rows[0].__len__() > 0:
                    last_name = rows[0]
                    # print sheet.name, "last_name:", last_name
                    self.valueParams.add_name(last_name, rows[3], rows[4], rows[5])
        else:  # 路径;
            for i in range(1, sheet.nrows):
                # 获取每行内容;
                rows = tuple(sheet.row_values(i))
                if rows[1].__len__() > 0 and rows[2].__len__() > 0 and last_name.__len__() > 0:
                    move_key = rows[3].split(';')
                    self.pathParams.add_item(sheet.name, last_name, rows[1], rows[2], move_key, rows[4], rows[5])
                elif rows[0].__len__() > 0:
                    last_name = rows[0]
                    self.pathParams.add_name(sheet.name, last_name, rows[3], rows[4], rows[5])

    # endfun

    def get_menu_paths(self, option, value):
        if type(option) == str:
            option = unicode(option)
        if type(value) == str:
            value = unicode(value)
        # pp必须不排序;
        vp, pp = {}, OrderedDict()
        if option in self.valueParams.values:
            vp = {"option": option, "value": value, "enter_key": self.valueParams.values[option]["enter_key"],
                  "move_key": self.valueParams.values[option]["move_key"]}
            # 找到value对应的ocr;
            for item in self.valueParams.values[option]["value"]:
                if "range(" in item["value"]:
                    vp["value_for_ocr"] = item["value_for_ocr"].split(';')
                    break
                elif item["value"].lower() == value.lower():
                    vp["value_for_ocr"] = item["value_for_ocr"].split(';')
                    break

        # 首先，字典不排序，需要倒序;
        paths = reversed(self.pathParams.paths)
        for level in paths:
            found = False
            for parent in self.pathParams.paths[level]:
                # print parent,self.pathParams.paths[level][parent],'\r\n'
                for item in self.pathParams.paths[level][parent]["value"]:
                    # print item
                    if item["option"].lower() == option.lower():
                        pp[level] = {
                            "parent": parent,
                            "move_key": self.pathParams.paths[level][parent]["move_key"],
                            "enter_key": self.pathParams.paths[level][parent]["enter_key"],
                            "others": self.pathParams.paths[level][parent]["others"],
                            "option": item["option"],
                            "option_for_ocr": item["option_for_ocr"].split(';'),
                            "option_move_key": item["option_move_key"],
                            "option_enter_key": item["option_enter_key"],
                            "option_others": item["option_others"]
                        }
                        option = parent
                        found = True
                        break
                if found:
                    break
                else:
                    if parent == option:
                        for item in self.pathParams.paths[level][parent]["value"]:
                            # print item
                            if item["option"].lower() == value.lower():
                                pp[level] = {
                                    "parent": parent,
                                    "move_key": self.pathParams.paths[level][parent]["move_key"],
                                    "enter_key": self.pathParams.paths[level][parent]["enter_key"],
                                    "others": self.pathParams.paths[level][parent]["others"],
                                    "option": item["option"],
                                    "option_for_ocr": item["option_for_ocr"].split(';'),
                                    "option_move_key": item["option_move_key"],
                                    "option_enter_key": item["option_enter_key"],
                                    "option_others": item["option_others"]
                                }
                                option = parent
                                found = True
                                break
                            # endif
                        # endfor
                        break
                    # endif
        # 需要对path倒序使用;
        return vp, pp

    def get_option_paths(self, option):
        # pp必须不排序;
        pp = OrderedDict()
        # 首先，字典不排序，需要倒序;
        paths = reversed(self.pathParams.paths)
        for level in paths:
            found = False
            for parent in self.pathParams.paths[level]:
                # print parent,self.pathParams.paths[level][parent],'\r\n'
                for item in self.pathParams.paths[level][parent]["value"]:
                    # print item
                    if item["option"].lower() == option.lower():
                        pp[level] = {
                            "parent": parent,
                            "move_key": self.pathParams.paths[level][parent]["move_key"],
                            "enter_key": self.pathParams.paths[level][parent]["enter_key"],
                            "others": self.pathParams.paths[level][parent]["others"],
                            "option": item["option"],
                            "option_for_ocr": item["option_for_ocr"].split(';'),
                            "option_move_key": item["option_move_key"],
                            "option_enter_key": item["option_enter_key"],
                            "option_others": item["option_others"]
                        }
                        option = parent
                        found = True
                        break
                if found:
                    break
                else:
                    if parent == option:
                        for item in self.pathParams.paths[level][parent]["value"]:
                            # print item
                            if item["option"].lower() == option.lower():
                                pp[level] = {
                                    "parent": parent,
                                    "move_key": self.pathParams.paths[level][parent]["move_key"],
                                    "enter_key": self.pathParams.paths[level][parent]["enter_key"],
                                    "others": self.pathParams.paths[level][parent]["others"],
                                    "option": item["option"],
                                    "option_for_ocr": item["option_for_ocr"].split(';'),
                                    "option_move_key": item["option_move_key"],
                                    "option_enter_key": item["option_enter_key"],
                                    "option_others": item["option_others"]
                                }
                                option = parent
                                found = True
                                break
                            # endif
                        # endfor
                        break
                    # endif
            # endfor
        # endfor
        # 需要对path倒序使用;
        # dict_pp = OrderedDict()
        # # 逆序路径key;
        # revpp = reversed(pp)
        # for path in revpp:
        #     dict_pp[path] = {"parent": pp[path]['parent'],"move_key": pp[path]['move_key'],"enter_key": pp[path]['enter_key'],"others":pp[path]['others'],"option": pp[path]['option'],"option_for_ocr": pp[path]['option_for_ocr']}
        # # 返回逆序后的结果;
        # return dict_pp
        return pp

    # 获取键对值
    def get_pair_values(self, name, option=True):
        if type(name) == str:
            name = unicode(name)
        pairs = []
        if option is True:
            find = False
            for level in self.pathParams.paths:
                for item in self.pathParams.paths[level]:
                    if item.lower() == name.lower():
                        find = True
                        for item in self.pathParams.paths[level][name]["value"]:
                            pairs.append({item['option']: item['option_for_ocr'].split(';')})
                        break
                # endfor
                if find is True:
                    break
            # endfor
        else:
            if name in self.valueParams.values:
                for item in self.valueParams.values[name]["value"]:
                    pairs.append({item['value']: item['value_for_ocr'].split(';')})
        # endif
        return pairs

    # 获取指定层级的path或value的xxx_for_ocr列表;
    def get_ocr_list(self, level, name, option, is_option=True):
        if type(name) == str:
            name = unicode(name)
        if type(option) == str:
            option = unicode(option)
        list_ocr = []
        if is_option is True:
            find = False
            for item in self.pathParams.paths[level]:
                if item.lower() == name.lower():
                    find = True
                    for item in self.pathParams.paths[level][name]["value"]:
                        if str(option).lower() != str(item["option"]).lower():
                            list_ocr.append(item['option_for_ocr'].split(';'))
                    break
            # endfor
        else:
            if name in self.valueParams.values:
                for item in self.valueParams.values[name]["value"]:
                    list_ocr.append(item['value_for_ocr'].split(';'))
        # endif
        return list_ocr

    # 获取指定value_name所在菜单项的全部同级菜单项ocr列表;
    def get_parent_ocr_list(self, option):
        # 编码转换;
        if type(option) == str:
            option = unicode(option)

        found = False
        list_ocr = []
        # 首先，字典不排序，需要倒序;
        paths = reversed(self.pathParams.paths)
        for level in paths:
            for parent in self.pathParams.paths[level]:
                for vals in self.pathParams.paths[level][parent]["value"]:
                    if vals['option'].lower() == option.lower():
                        found = True
                        break
                # endfor
                # 找到退出;
                if found is True:
                    break
            # endfor
            # 找到退出;
            if found is True:
                break
        # endfor

        # 遍历value列表;
        if found is True:
            for val in self.pathParams.paths[level][parent]["value"]:
                list_ocr.extend(val['option_for_ocr'].split(';'))
        # 按长度排序;
        list_ocr.sort(key=lambda i: len(i), reverse=True)
        # 返回结果;
        return list_ocr

    # 当value_sheet = False，表示获取path表里的option层级的所有option_for_ocr:option
    # 当value_sheet = True，表示获取value表里的value_name层级的所有value_for_ocr:value
    def get_parent_ocr_dict(self, option, value_sheet=False):
        print "get_parent_ocr_dict.option:", option
        # 编码转换;
        if type(option) == str:
            option = unicode(option)

        found = False
        dict_ocr = {}
        list_ocr = []
        if value_sheet is False:
            # 首先，字典不排序，需要倒序;
            paths = reversed(self.pathParams.paths)
            for level in paths:
                for parent in self.pathParams.paths[level]:
                    for vals in self.pathParams.paths[level][parent]["value"]:
                        if vals['option'].lower() == option.lower():
                            found = True
                            break
                    # endfor
                    # 找到退出;
                    if found is True:
                        break
                # endfor
                # 找到退出;
                if found is True:
                    break

            # 遍历value列表;
            if found is True:
                for val in self.pathParams.paths[level][parent]["value"]:
                    list_ocr = val['option_for_ocr'].split(';')
                    for ocr in list_ocr:
                        dict_ocr[ocr.lower()] = val['option']
        else:
            if option in self.valueParams.values:
                for val in self.valueParams.values[option]["value"]:
                    list_ocr = val['value_for_ocr'].split(';')
                    for ocr in list_ocr:
                        dict_ocr[ocr.lower()] = val['value']

        print 'unsorted=', dict_ocr
        # 按长度排序;
        list_ocr = sorted(dict_ocr.keys(), key=lambda key: len(key), reverse=True)
        dict_reuslt = OrderedDict()
        for ocr in list_ocr:
            dict_reuslt[ocr] = dict_ocr[ocr]
        # 返回结果;
        return dict_reuslt

    def get_option_ocr(self, option):
        # 编码转换;
        if type(option) == str:
            option = unicode(option)

        found = False
        list_ocr = []
        # 首先，字典不排序，需要倒序;
        paths = reversed(self.pathParams.paths)
        for level in paths:
            for parent in self.pathParams.paths[level]:
                for vals in self.pathParams.paths[level][parent]["value"]:
                    if vals['option'].lower() == option.lower():
                        list_ocr = vals['option_for_ocr'].split(';')
                        found = True
                        break
                # endfor
                # 找到退出;
                if found is True:
                    break
            # endfor
            # 找到退出;
            if found is True:
                break
        # endfor
        # 返回结果;
        return list_ocr

    def get_value(self, option, value=""):
        # 编码转换;
        if type(option) == str:
            option = unicode(option)
        xlsValue = self.valueParams.values[option]["value"]
        valueList = []
        valueOcrList = []
        for item in xlsValue:
            valueList.append(item["value"])
            valueOcrList.append(item["value_for_ocr"])
        vp = {}
        if option in self.valueParams.values:
            vp = {"option": option, "value": valueList, "value_for_ocr": valueOcrList,
                  "enter_key": self.valueParams.values[option]["enter_key"],
                  "move_key": self.valueParams.values[option]["move_key"],
                  "others": self.valueParams.values[option]['others']}
            # 找到value对应的ocr;
            if value != "":
                vp["value"] = value
                for item in self.valueParams.values[option]["value"]:
                    if "range(" in item["value"]:
                        vp["value_for_ocr"] = item["value_for_ocr"].split(';')
                        break
                    elif item["value"].lower() == value.lower():
                        vp["value_for_ocr"] = item["value_for_ocr"].split(';')
                        break
                # endfor
            # endif
        # endif
        # 返回结果;
        return vp

    # 获取option层级内容;
    def get_option(self, option):
        # 编码转换;
        if type(option) == str:
            option = unicode(option)

        found = False
        layers = 0
        dict_option = {}
        # 首先，字典不排序，需要倒序;
        paths = reversed(self.pathParams.paths)
        for level in paths:
            for parent in self.pathParams.paths[level]:
                for vals in self.pathParams.paths[level][parent]["value"]:
                    if vals['option'].lower() == option.lower():
                        if dict_option.__len__() == 0:
                            found = True
                            dict_option['level'] = level
                            dict_option['parent'] = parent
                            dict_option['others'] = self.pathParams.paths[level][parent]['others']
                            dict_option['enter_key'] = self.pathParams.paths[level][parent]['enter_key']
                            dict_option['move_key'] = self.pathParams.paths[level][parent]['move_key']
                            dict_option['option_ocr'] = vals['option_for_ocr'].split(';')
                            dict_option['option_move_key'] = vals['option_move_key']
                            dict_option['option_enter_key'] = vals['option_enter_key']
                            dict_option['option_others'] = vals['option_others']
                        option = parent
                        layers += 1  # 层数;
                        break
                # endfor
            # endfor
        # endfor
        if found is True:
            dict_option['layers'] = layers
            # first层次的parent名称;
            dict_option['first_parent'] = option
        # 返回结果;
        return found, dict_option

    #
    def getRepeatValueName_ValueSheet(self):
        wb = xlrd.open_workbook(filename=self.xls_path)
        valueSheet = wb.sheet_by_name("Value")
        rows = valueSheet.nrows  # 获取行数
        all_value_option_list = []
        RepeatValueName_ValueSheet_List = []

        for i in range(1, rows):
            # 获取每行内容;
            rows = tuple(valueSheet.row_values(i))
            if rows[0].__len__() > 0:
                if rows[0] in all_value_option_list:
                    RepeatValueName_ValueSheet_List.append([rows[0], "Value"])
                else:
                    all_value_option_list.append(rows[0])
        print "RepeatValueName_ValueSheet_List:", RepeatValueName_ValueSheet_List
        return RepeatValueName_ValueSheet_List

    def getRepeatOption_otherSheet(self):
        wb = xlrd.open_workbook(filename=self.xls_path)
        all_option_list = []
        repeatOption_otherSheet_List = []
        for sh_name in wb.sheet_names():
            sheet = wb.sheet_by_name(sh_name)
            if u"Value" == sheet.name:
                pass
            else:  # 路径;
                for i in range(1, sheet.nrows):
                    # 获取每行内容;
                    rows = tuple(sheet.row_values(i))
                    if rows[1].__len__() > 0:
                        if rows[1] in all_option_list:
                            repeatOption_otherSheet_List.append([rows[1], sheet.name])
                            # print "rows[1]:", rows[1], "sheetName:", sheet.name
                        else:
                            all_option_list.append(rows[1])
        print "repeatOption_otherSheet_List:", repeatOption_otherSheet_List
        return repeatOption_otherSheet_List

    def getRepeatOptionList(self):
        RepeatOptionList = []
        repeatValueName_ValueSheet = self.getRepeatValueName_ValueSheet()
        repeatOption_otherSheet = self.getRepeatOption_otherSheet()
        RepeatOptionList = repeatOption_otherSheet + repeatValueName_ValueSheet
        print "RepeatOptionList:", RepeatOptionList
        return RepeatOptionList

    def getValueSheetOptionList(self):
        wb = xlrd.open_workbook(filename=self.xls_path)
        valueSheet = wb.sheet_by_name("Value")
        rows = valueSheet.nrows  # 获取行数
        all_value_option_list = []

        for i in range(1, rows):
            # 获取每行内容;
            rows = tuple(valueSheet.row_values(i))
            if rows[0].__len__() > 0:
                all_value_option_list.append(rows[0])
        print "all_value_option_list:", all_value_option_list
        return all_value_option_list

    def checkOptionPath(self, optionName):
        optionPath = ""
        optionPathResult = "Fail"
        path_params = self.get_option_paths(optionName)
        # 如果传入的option不存在则直接返回
        if path_params.__len__() <= 0:
            optionPathResult = "NoExit"
            optionPath = u"表格中不存在到达Option:   %s   的路径" % str(optionName)
            return optionPath, optionPathResult
        else:
            optionPath = str(path_params)
            # 逆序路径key;
            revpp = reversed(path_params)
            level_count = 0
            for level in revpp:
                if str(level) == str(g_level[level_count]):
                    level_count = level_count + 1
                else:
                    # 如果执行路径不是按照First、Second......执行则返回执行出错
                    return optionPath, optionPathResult
            optionPathResult = "Pass"
            return optionPath, optionPathResult

    def checkRunOptionPath(self, path_params):
        optionPathResult = "Fail"
        # 如果传入的option不存在则直接返回
        if path_params.__len__() <= 0:
            optionPathResult = "NoExit"
            return optionPathResult
        else:
            optionPath = str(path_params)
            # 逆序路径key;
            revpp = reversed(path_params)
            level_count = 0
            for level in revpp:
                if str(level) == str(g_level[level_count]):
                    level_count = level_count + 1
                else:
                    # 如果执行路径不是按照First、Second......执行则返回执行出错
                    return optionPathResult
            optionPathResult = "Pass"
            return optionPathResult

    def getCheckOptionPathExcelData(self):
        checkOptionPathExcelData = []
        checkOptionPathExcelData_Fail = []
        checkOptionPathExcelData_NoExit = []
        checkOptionPathExcelData_Pass = []
        valueSheetOptionList = self.getValueSheetOptionList()
        for optionName in valueSheetOptionList:
            optionPath, optionPathResult = self.checkOptionPath(optionName)
            if str(optionPathResult) == "Fail":
                checkOptionPathExcelData_Fail.append([optionName, optionPathResult, optionPath])
            if str(optionPathResult) == "NoExit":
                checkOptionPathExcelData_NoExit.append([optionName, optionPathResult, optionPath])
            if str(optionPathResult) == "Pass":
                checkOptionPathExcelData_Pass.append([optionName, optionPathResult, optionPath])

            # checkOptionPathExcelData.append([optionName, optionPathResult, optionPath])
        checkOptionPathExcelData = checkOptionPathExcelData_Fail + checkOptionPathExcelData_NoExit + checkOptionPathExcelData_Pass
        return checkOptionPathExcelData

    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

    # 获取表格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)
        if valueList.__len__() == 1 and "range(" in valueList[0]:
            value_for_ocr_low = str(valueList[0]).lower()
            str_num = value_for_ocr_low.replace("range", "")
            list_num = eval(str_num)
            min_num = list_num[0]
            max_num = list_num[1]
            return [min_num, max_num]

        return valueList

    # 获取表格First~Sixth层级中，parent下包含的option列表
    def getSubOptionList(self, parent):
        optionList = []
        paths = self.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)
        return optionList

    # 获取表格Value层级中，value_name下的value的ocr列表
    def getValueTextList(self, value_name, value):
        valueTextList = []
        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:
                if valueDict["value"] == value:
                    value_for_ocr = valueDict["value_for_ocr"]
                    # print "value_for_ocr:", value_for_ocr, type(value_for_ocr)
                    valueTextList = value_for_ocr.split(';')

        # print "valueTextList:", valueTextList, type(valueTextList)
        return valueTextList

    # 获取表格First~Sixth层级中，parent下的option的ocr列表
    def getOptionTextList(self, parent, option):
        optionTextList = []
        paths = self.pathParams.paths
        for level in paths:
            if paths[level].has_key(parent):
                optionDictList = paths[level][parent]["value"]
                for optionDict in optionDictList:
                    if optionDict["option"] == option:
                        option_for_ocr = optionDict["option_for_ocr"]
                        # print "option_for_ocr:", option_for_ocr, type(option_for_ocr)
                        optionTextList = option_for_ocr.split(';')
        # print "optionList:", optionTextList, type(optionTextList)
        return optionTextList


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)