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
from xlsConst import xlsConst as xlsc
from BaseLog import CBaseLog


class CExcelParser(CBaseLog):
    def __init__(self, optionExcel, xls_path=None):
        CBaseLog.__init__(self)
        self.__optionExcel = optionExcel
        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

        if not os.path.exists(self.xls_path):
            return

        # 打开文件;
        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)

    # endfun

    def parse_excel(self, sheet, bpath=True):
        pKeys = sheet.row_values(0)
        self.info(u"params sheet %s keys: %s" % (sheet.name, str(pKeys)))
        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]
                    if pKeys[index] == "toparent_key" and oneRow[index] != "":
                        rowDict[pKeys[index]] = self.__parseToParentKey(item)
                    elif pKeys[index] == "layout" and oneRow[index] != "":
                        rowDict[pKeys[index]] = self.__parseLayout(item)
                    # 如果enter_key以新格式配置，则以新格式解析
                    elif pKeys[index] == "enter_key" and str(item).find("key[")>= 0:
                        rowDict[pKeys[index]] = self.__parseEnterKey(item)
                    elif pKeys[index] == "enter_key":
                        keyStr = "key[%s]"%item
                        rowDict[pKeys[index]] = self.__parseEnterKey(keyStr)
                    else:
                        rowDict[pKeys[index]] = item
                if rowDict[xlsc.value_name] is None or rowDict[xlsc.value_name].__len__() < 1:
                    if parentName is None:
                        self.error(u"Level %s error row %s:" % (sheet.name, str(oneRow)))
                        break
                    else:
                        rowDict[xlsc.value_name] = parentName
                        self.__optionExcel.valueParams.addOption(rowDict)
                elif rowDict[xlsc.value_name].__len__() > 0:
                    parentName = rowDict[xlsc.value_name]
                    self.__optionExcel.valueParams.addParent(rowDict)
        elif "Dialog" == sheet.name:
            self.parsePath(sheet, pKeys, self.__optionExcel.dialogParams)
        else:  # 路径;
            self.parsePath(sheet, pKeys, self.__optionExcel.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]
                if pKeys[index] == "toparent_key" and oneRow[index] != "":
                    rowDict[pKeys[index]] = self.__parseToParentKey(item)
                elif pKeys[index] == "layout" and oneRow[index] != "":
                    rowDict[pKeys[index]] = self.__parseLayout(item)
                # 如果enter_key以新格式配置，则以新格式解析
                elif pKeys[index] == "enter_key" and str(item).find("key[") is True:
                    rowDict[pKeys[index]] = self.__parseEnterKey(item)
                elif pKeys[index] == "enter_key":
                    keyStr = "key[%s]"%item
                    rowDict[pKeys[index]] = self.__parseEnterKey(keyStr)
                else:
                    rowDict[pKeys[index]] = item
            if rowDict[xlsc.parent] is None or rowDict[xlsc.parent].__len__() < 1:
                if parentName is None:
                    self.error(u"Level %s error row %s:" % (sheet.name, str(oneRow)))
                    continue
                else:
                    rowDict[xlsc.parent] = parentName
                    treeParams.addOption(sheet.name, rowDict)
            elif rowDict[xlsc.FLevel_Params[0]].__len__() > 0:
                parentName = rowDict[xlsc.parent]
                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

    def __parseToParentKey(self, keyStr):
        keyList = ["key", "duration", "wait", "tolevel", "dialog"]
        # 按照"key[]"的方式切片并解析成字典
        keyDict = self.__parseMulParam(keyList, keyStr)
        keyDict["key"] = tuple(keyDict["key"].split(","))
        return keyDict

    def __parseLayout(self, layoutStr):
        keyList = ["bounds"]
        keyDict = self.__parseMulParam(keyList, layoutStr)
        keyDict["bounds"] = tuple(keyDict["bounds"].split(","))
        return keyDict

    def __parseEnterKey(self, keyStr):
        keyList = ["key", "duration", "wait", "isback", "tolevel"]
        keyDict = self.__parseMulParam(keyList, keyStr)
        keyDict["key"] = tuple(keyDict["key"].split(","))
        return keyDict

    '''
    返回:{key:value,key2:value2,...}
    '''
    def __parseMulParam(self, keyList, params):
        paramDict = {}
        for key in keyList:
            value = self.__parseParam(key, params)
            paramDict[key] = value
        return paramDict

    '''
    char为该项参数的括符符号，必须成对带入。默认为中括号[]
    '''
    #将数据从excel表格里取出来，只返回括号里面的内容
    def __parseParam(self, key, params, char="[]"):
        # 为防止key在其他地方存在相同关键字，添加一个"["做区别
        key1 = key + char[0]
        keyIndex = params.find(key1)
        if keyIndex == -1:
            return ""
        # key.__len__()-1 为去掉"["的处理
        str1 = params[keyIndex + key1.__len__()-1:params.__len__()]
        i1 = str1.find(char[0])
        i2 = str1.find(char[1])
        if i1 == -1 or i2 == -1:
            return ""
        str2 = str1[i1 + 1: i2]
        return str2.strip()


if __name__ == "__main__":
    pass