# -*- 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