def getRadar(self, sheet: xlrd.sheet) -> Radar:
namelist = sheet.col_values(0, 1)
maxvaluelist = sheet.col_values(1, 1)
maxlist = [
opts.RadarIndicatorItem(name=namelist[i], max_=maxvaluelist[i])
for i in range(len(namelist))
]
valuelists = dict()
for i in range(2, sheet.ncols):
valuelists[sheet.cell(0, i).value] = sheet.col_values(i, 1)
r = Radar()
r.add_schema(
schema=maxlist,
splitarea_opt=opts.SplitAreaOpts(
is_show=True, areastyle_opts=opts.AreaStyleOpts(opacity=1)),
textstyle_opts=opts.TextStyleOpts(color="#fff"),
)
#设置颜色的列表
colorstr = ["#CD0000", "#5CACEE", "#7B68EE", "#FFFF00"]
colorth = 0
for name, values in valuelists.items():
r.add(
series_name=name,
data=values,
linestyle_opts=opts.LineStyleOpts(color=colorstr[colorth]),
)
colorth = (colorth + 1) % len(colorstr) #颜色循环
r.set_series_opts(label_opts=opts.LabelOpts(is_show=False))
r.set_global_opts(title_opts=opts.TitleOpts(
title=sheet.name,
title_textstyle_opts=opts.TextStyleOpts(color="#fff")),
legend_opts=opts.LegendOpts())
return r
def getPie(self, sheet: xlrd.sheet) -> Pie:
names = sheet.col_values(0)
values = sheet.col_values(1)
c = (Pie(init_opts=opts.InitOpts(theme="chalk")).add(
"tooltip 名",
list(zip(names, values)),
radius=["40%", "75%"],
).set_global_opts(
title_opts=opts.TitleOpts(title=sheet.name),
legend_opts=opts.LegendOpts(orient="vertical",
pos_top="15%",
pos_left="2%"),
).set_series_opts(label_opts=opts.LabelOpts(formatter="{b}: {c}")))
return c
def checkSheet(sheet: xlrd.sheet, path: str):
if (sheet.nrows <= 0 or sheet.ncols <= 0):
return
importType = str(sheet.cell(0, 1).value)
configName = str(sheet.cell(1, 1).value)
configHead = str(sheet.cell(0, 4).value)
configTail = str(sheet.cell(1, 4).value)
configKeyCount = int(sheet.cell(2, 1).value)
if (len(importType) > 0):
if (importType.find('tiny') >= 0):
doTiny(sheet, configName, configHead, configTail, configKeyCount)
elif (importType.find('base') >= 0):
doBase(sheet, configName, configHead, configTail, configKeyCount)
else:
print(path, "->表格数据错误")
def get_sheet_row_as_array(workbook_sheet: xlrd.sheet,
sheet_row_number: int = 1):
worksheet_col_count = workbook_sheet.ncols
row_as_array = []
for each_col in range(0, worksheet_col_count):
row_as_array.append(
workbook_sheet.cell(sheet_row_number, each_col).value)
return row_as_array
def __get_indices(self, data: xlrd.sheet, kwargs: dict) -> dict:
indices = []
for x in [*kwargs.values()]:
try:
indices.append(data.row_values(0).index(x))
except ValueError:
continue
return dict(zip(indices, [*kwargs.keys()]))
def getGauge(self, sheet: xlrd.sheet) -> Gauge:
key = sheet.cell(0, 0).value
value = sheet.cell(0, 1).value
g = Gauge()
g.add(
series_name=sheet.name,
data_pair=[(key, value)],
title_label_opts=opts.LabelOpts(font_size=40,
color="blue",
font_family="Microsoft YaHei"),
) #改标题字体颜色
g.set_global_opts(
legend_opts=opts.LegendOpts(is_show=False),
#tooltip_opts=opts.TooltipOpts(is_show=True, formatter="{a} <br/>{b} : {c}%",textstyle_opts=opts.TextStyleOpts(color="#fff")),#图例颜色和显示格式
title_opts=opts.TitleOpts(title=sheet.name) #加上图标题
)
return g
def sheet_to_json(sheet: xlrd.sheet, sheetname: str, key_column, value_column,
start_row, end_row):
if not end_row: # getting end row if end row is not supplied
end_row = sheet.nrows
key_column -= 1 # subtracting 1 to match the python index with excel index
value_column -= 1
start_row -= 1
js = {}
for row in range(start_row, end_row):
try:
key = int(sheet.cell(row, key_column).value * 100)
value = int(sheet.cell(row, value_column).value * 100)
js[key] = value
except Exception as ex:
print(f"Exception in row {str(row)}: {str(ex)}")
with open(sheetname + ".json", "w") as file: # using sheetname as filename
json.dump(js, file, indent=4)
print(sheetname + ".json created successfully.")
def getLine(self, sheet: xlrd.sheet) -> Line:
#记录第一列的第二个单元格开始的数据，表示横轴
xlist = sheet.col_values(0, 1)
#记录所有的列数据，表示y
ylist = dict()
for i in range(1, sheet.ncols):
ylist[sheet.cell(0, i).value] = sheet.col_values(i, 1)
c = (Line(init_opts=opts.InitOpts(theme="chalk")).add_xaxis(
xaxis_data=xlist))
for name, list in ylist.items():
c.add_yaxis(series_name=name, y_axis=list)
c.set_global_opts(
title_opts=opts.TitleOpts(title=sheet.name),
tooltip_opts=opts.TooltipOpts(trigger="axis"), #hover时的状态
#toolbox_opts=opts.ToolboxOpts(is_show=True),#是否显示工具栏
xaxis_opts=opts.AxisOpts(type_="category", boundary_gap=False),
legend_opts=opts.LegendOpts(pos_left="20%", pos_top='5%'))
return c
def analysis_data(self, work_sheet: sheet):
"""
分析数据
:param work_sheet: excel完整路径
:return:
"""
__results = []
if self.__auditType == AuditType.ACCESS:
__results = [self.__accessCount, self.__accessResultCount]
self.__headers += self.__cmdTypeCount.keys()
__results += self.__cmdTypeCount.values()
if self.__auditType == AuditType.LOGON:
__results = [self.__logonCount, self.__logoffCount]
nrows = len(work_sheet.rows)
for index, header in enumerate(self.__headers):
work_sheet.write(nrows + index, 0, header)
work_sheet.write(nrows + index, 1, __results[index])
def getCell(st: sheet, row, col):
"""
获取指定单元格内容
:param st: sheet
:param row: 行数
:param col: 列数
:return: 单元格内容
"""
value = st.cell(row, col).value.strip()
return value
def __FromRowAtToList(self, sheet : xlrd.sheet, start:int , end:int):
records = list();
for i in range(start, end):
row = sheet.row(i)
table = list()
for cell in row:
table.append(cell.value)
records.append(table)
return records
def doBase(sheet: xlrd.sheet, configName: str, configHead: str,
configTail: str, configKeyCount: int):
outputTypeList = sheet.row_values(5, 1, sheet.ncols)
markList = sheet.row_values(6, 1, sheet.ncols)
clientConfig = {}
serverConfig = []
print("key数量:", configKeyCount)
for i in range(configKeyCount):
for j in range(7, sheet.nrows):
cellValue = sheet.cell(j, i + 1).value
tempdict = {}
if ((cellValue in clientConfig) == True):
for k in range(1, sheet.ncols):
tempdict[markList[k - 1]] = sheet.cell(j, k).value
clientConfig[cellValue].append(tempdict)
else:
for n in range(1, sheet.ncols):
tempdict[markList[n - 1]] = sheet.cell(j, n).value
clientConfig[cellValue] = [tempdict]
print("数据：", clientConfig)
def getBar(self, sheet: xlrd.sheet) -> Bar:
# 记录第一列的第二个单元格开始的数据，表示横轴
xlist = sheet.col_values(0, 1)
# 记录所有的列数据，表示y
ylist = dict()
for i in range(1, sheet.ncols):
ylist[sheet.cell(0, i).value] = sheet.col_values(i, 1)
c = Bar(init_opts=opts.InitOpts(theme="wonderland"))
c.add_xaxis(xlist)
for k, v in ylist.items():
c.add_yaxis(k, v)
c.set_global_opts(title_opts=opts.TitleOpts(
title=sheet.name + "\n",
pos_left='left',
title_textstyle_opts={'color': '#FFFFFF'}),
legend_opts=opts.LegendOpts(pos_left="20%",
pos_top='5%'))
#设置x轴大小
# c.set_global_opts(xaxis_opts=opts.AxisOpts(axislabel_opts=opts.LabelOpts(font_size=10,interval=0)))
return c
def analysis_data(self, work_sheet: sheet, *args):
expect_results = None
audit_type = None
for param in args:
if isinstance(param, AuditType):
audit_type = param
if isinstance(param, list) and len(param) > 0 and isinstance(
param[0], ExpectResult):
expect_results = param
nrows = len(work_sheet.rows)
for index, header in enumerate(self.__headers):
work_sheet.write(nrows, index, header)
if expect_results is not None:
for row_index in range(len(expect_results)):
if expect_results[
row_index].strategyType == StrategyType.MULTIPLE_FIELDS_MATCH:
row = expect_results[row_index].to_list_for_out(audit_type)
for cell_index in range(len(row)):
work_sheet.write(row_index + 1, cell_index,
str(row[cell_index]))
def __init__(self, env: simpy.Environment, sheet: xlrd.sheet,
machine: Machine, conditions: RoadConditionsState,
user_memory: UserMemory, persona: str, max_mental: int,
max_perc: int):
self.visual = simpy.PreemptiveResource(env, capacity=1)
self.visual_peripheral = simpy.PreemptiveResource(env, capacity=1)
self.sound_vocal = simpy.PreemptiveResource(env, capacity=1)
self.sound_non_vocal = simpy.PreemptiveResource(env, capacity=1)
self.haptic_hands = simpy.PreemptiveResource(env, capacity=1)
self.haptic_seat = simpy.PreemptiveResource(env, capacity=1)
self.psychomotor = simpy.PreemptiveResource(env, capacity=1)
self.cognitive_workload = simpy.Container(env, capacity=max_mental)
self.perceptional_workload = simpy.Container(env, capacity=max_perc)
self.machine = machine
self.road_conditions = conditions
self.user_memory = user_memory
# cognitive resources of human
self.tasks = [
Task(env=env,
row=sheet.row_values(i),
resources=[
self.visual, self.visual_peripheral, self.sound_vocal,
self.sound_non_vocal, self.haptic_hands, self.haptic_seat,
self.psychomotor, self.cognitive_workload,
self.perceptional_workload
],
machine=machine,
user_memory=user_memory,
road_conditions=conditions)
for i in np.arange(1, sheet.nrows)
]
self.tasks = [
task for task in self.tasks
if task.persona == persona or task.persona == ''
]
self.control_tasks = [
task for task in self.tasks if 'user_switch_regime' in task.trigger
]
self.env = env
self.tasks_to_execute: List[Task] = []
self.interrupted_tasks = []
self.finished_tasks = []
self.env.process(self.interrupted_tasks_monitor())
def getFunnel(self, sheet: xlrd.sheet) -> Funnel:
x_data = sheet.col_values(0)
y_data = sheet.col_values(1)
data = [(x_data[i], y_data[i]) for i in range(len(x_data))]
f = Funnel()
f.add(
series_name=sheet.name,
data_pair=data,
gap=2,
#tooltip_opts=opts.TooltipOpts(trigger="item", formatter="{a} <br/>{b} : {c}%",textstyle_opts=opts.TextStyleOpts(color="#fff")),#图例颜色和显示格式
label_opts=opts.LabelOpts(is_show=True, position="inside"),
itemstyle_opts=opts.ItemStyleOpts(border_color="#fff",
border_width=1),
)
f.set_global_opts(
title_opts=opts.TitleOpts(title=sheet.name,
title_textstyle_opts=opts.TextStyleOpts(
color="#fff")))
return f
def checkData(self, sheet: xlrd.sheet, typename) -> bool:
if typename == "柱状图":
if sheet.cell(1, 1).ctype == 0 or sheet.cell(1, 0).ctype == 0:
return False
#空格问题太要命，先不检测数据是否完整了
elif typename == "折线图":
if sheet.cell(1, 1).ctype == 0 or sheet.cell(1, 0).ctype == 0:
return False
elif typename == "饼图":
if sheet.cell(0, 1).ctype == 0 or sheet.cell(0, 0).ctype == 0:
return False
elif typename == "仪表盘":
if sheet.cell(0, 0).ctype == 0 or sheet.cell(0, 1).ctype == 0:
return False
elif typename == "漏斗图":
if sheet.cell(0, 0).ctype == 0 or sheet.cell(0, 1).ctype == 0:
return False
elif typename == "雷达图":
#检查 是否maxvalue齐全
if 0 in sheet.col_types(1, 1) or 0 in sheet.col_types(2, 1):
return False
#检查每个value是否小于等于maxvalue
for j in range(2, sheet.ncols):
for i in range(1, sheet.nrows):
if sheet.cell(i, j).value > sheet.cell(i, 1).value:
return False
return True
def doTiny(sheet: xlrd.sheet, configName: str, configHead: str,
configTail: str, configKeyCount: int):
clientConfig = {}
fileName = configHead.split("=")[0]
for i in range(5, sheet.nrows):
markSign = sheet.cell(i, 1).value
param = sheet.cell(i, 2).value
value = sheet.cell(i, 3).value
print("参数名字", param)
#分析单元格数据
if (type(value) == str):
print("字符串数据", value)
valueList = re.findall(r'{(.*)}', value)
print("valueList:", valueList)
if (len(valueList) > 0):
str1 = valueList[0]
valueList1 = re.findall(r'{(.*?)}', str1)
#双层括号变数组
if (len(valueList1) > 0):
resultList = []
for item in range(len(valueList1)):
temp = valueList1[item]
doubleList2 = temp.split(",")
resultDict = {}
for item2 in range(len(doubleList2)):
str3 = doubleList2[item2]
doubleList3 = str3.split("=")
# print(doubleList3)
if is_number(doubleList3[1]) == True:
doubleList3[1] = float(doubleList3[1])
resultDict[doubleList3[0]] = doubleList3[1]
resultList.append(resultDict)
# print("result:",resultList)
if (markSign.find("c") >= 0):
clientConfig[param] = resultList
#单层括号拆分
else:
# print("单层括号：",str1)
if (str1.find("=") >= 0):
str1List = str1.split(",")
resultDict = {}
for mark in range(len(str1List)):
str2 = str1List[mark]
str2List = str2.split("=")
testArray = re.findall(r'[[](.*?)[]]', str2List[0])
if is_number(str2List[1]) == True:
str2List[1] = float(str2List[1])
if len(testArray) > 0:
resultDict[testArray[0]] = str2List[1]
else:
# print("str2List",str2List)
resultDict[str2List[0]] = str2List[1]
# print("result:",resultDict)
if (markSign.find("c") >= 0):
clientConfig[param] = resultDict
else:
str1List = str1.split(",")
if len(str1List) > 0:
resultList = []
resultList = str1List
if (markSign.find("c") >= 0):
clientConfig[param] = resultList
else:
if (markSign.find("c") >= 0):
if is_number(str1) == True:
str1 = float(str1)
clientConfig[param] = str1
else:
print("无大括号的数据", value)
if (markSign.find("c") >= 0):
clientConfig[param] = value
else:
print("不是字符串的数据", value)
if (markSign.find("c") >= 0):
clientConfig[param] = value
print("最终结果", clientConfig)
if (len(clientConfig) > 0):
fileDict = {}
fileDict[fileName] = clientConfig
outputResult = json.dumps(fileDict, ensure_ascii=False)
os.chdir(targetPath)
file_obj = open(fileName + ".json", 'w', encoding="utf-8")
file_obj.write(outputResult)
file_obj.close()
