def _create_chart(worksheet):
"""Create the f*****g chart"""
chart = ScatterChart()
chart.varyColors = True
chart.title = "Financial Analysis"
chart.style = 1
chart.height = 10
chart.width = 20
chart.x_axis.title = "Financial Quarter"
chart.y_axis.title = "Cost"
chart.legend = None
chart.x_axis.majorUnit = 0.5
chart.x_axis.minorGridlines = None
# chart.y_axis.majorUnit = 200
xvalues = Reference(worksheet, min_col=1, min_row=3, max_row=6)
picker = _color_gen()
for i in range(2, 7):
values = Reference(worksheet, min_col=i, min_row=2, max_row=6)
series = Series(values, xvalues, title_from_data=True)
series.smooth = True
series.marker.symbol = "circle"
line_prop = LineProperties(solidFill=next(picker))
series.graphicalProperties.line = line_prop
chart.series.append(series)
worksheet.add_chart(chart, "G1")
return worksheet
def draw_chart(wb_path: str):
wb = openpyxl.load_workbook(wb_path)
ws = wb.active
c1 = ScatterChart()
c1.y_axis.majorGridlines = None
c1.x_axis.majorGridlines = None
c1.x_axis.tickLblSkip = 100
#data = Reference(ws, min_col=1, min_row=1, max_col=ws.max_column, max_row=ws.max_row)
#c1.add_data(data, titles_from_data=True)
skips = ws.max_column / 2
col = 1
for i in range(1, ws.max_column, 2):
xvalues = Reference(ws, min_col=i, min_row=1, max_row=ws.max_row)
values = Reference(ws, min_col=i + 1, min_row=1, max_row=ws.max_row)
series = Series(values, xvalues, title_from_data=True)
series.smooth = True
c1.series.append(series)
ws.add_chart(c1, "D10")
wb.save(wb_path)
return True
def draw_ordered_ranks(worksheet):
chart = LineChart()
chart.title = 'Ranks in descending order'
chart.x_axis.title = 'Function'
chart.y_axis.title = 'Rank'
chart.style = 11
i = 0
while True:
a = alpha(worksheet, i)
if a is None:
break
col = 3 * i + 2
data = Reference(worksheet,
min_col=col,
max_col=col,
min_row=2,
max_row=min(worksheet.max_row, 100))
series = Series(data, title='a=' + a)
series.smooth = True
series.graphicalProperties.line.width = 20000
chart.append(series)
i += 1
worksheet.add_chart(chart, 'D25')
#line.x_axis.scaling.max = int(max(origYPSNR_dict[filename][0] , testYPSNR_dict[filename][0],
# testYPSNR_dict2[filename][0], testYPSNR_dict3[filename][0])) + 2 # y轴的最大值
oriXdata = Reference(
sheet_anchor,
min_col=4,
min_row=excelCurrRow + (index_num - 1) * dataVerStep,
max_row=excelCurrRow + (index_num - 1) * dataVerStep + 3)
oriYdata = Reference(
sheet_anchor,
min_col=5,
min_row=excelCurrRow + (index_num - 1) * dataVerStep,
max_row=excelCurrRow + (index_num - 1) * dataVerStep + 3)
series = Series(oriYdata, oriXdata, title=anchor_codec)
series.marker.symbol = 'circle'
series.smooth = True
line.series.append(series)
testXdata = Reference(
sheet_refer,
min_col=4,
min_row=excelCurrRow + (index_num - 1) * dataVerStep,
max_row=excelCurrRow + (index_num - 1) * dataVerStep + 3)
testYdata = Reference(
sheet_refer,
min_col=5,
min_row=excelCurrRow + (index_num - 1) * dataVerStep,
max_row=excelCurrRow + (index_num - 1) * dataVerStep + 3)
series = Series(testYdata, testXdata, title=refer1_codec)
series.marker.symbol = 'circle'
series.smooth = True
def graph(string: str):
jsonOBJ = json.loads(string[string.find(BeginJSON) + len(BeginJSON) : string.find(EndJSON)])
dataStream = string[string.find(EndJSON) + len(EndJSON) :]
StrDict = {v: k for k, v in jsonOBJ[1].items()}
data = dict()
dataTitles = list(["Elapsed Time (s)"])
epochSmol = -1
for line in dataStream.splitlines():
try:
msg = line.split(" ")
if len(msg) != 4 or not StrDict.get(int(msg[2])):
continue
msgID = StrDict[int(msg[2])]
if not data.get(msgID):
data[msgID] = list()
dataTitles.append(msgID)
epoch = int(msg[0])
data[msgID].append(list((epoch, int(msg[3]), data[msgID])))
if epochSmol == -1 or epoch < epochSmol:
epochSmol = epoch
except KeyError as e:
print("KeyError: ", e)
pass
for _, value in data.items():
value.sort(key=lambda x: x[0])
for lst in value:
lst[0] -= epochSmol
lst[0] /= 1000000000
wb = openpyxl.Workbook()
ws = wb.active
ws.append(dataTitles)
rows = list()
def getRow(): # IMPROVE: better series generation
row = list()
for title in dataTitles:
column = data.get(title)
if not column or len(column) == 0:
row.append((epochSmol + 1, 0))
else:
row.append(column[0])
smallestEpoch = epochSmol
for item in row:
if item[0] < smallestEpoch:
smallestEpoch = item[0]
if smallestEpoch == epochSmol:
return
finalRow = list([smallestEpoch])
row.pop(0)
for item in row:
if item[0] != smallestEpoch:
finalRow.append(item[1])
else:
finalRow.append(item[2].pop(0)[1])
if len(finalRow) == 1:
return
return finalRow
while True:
row = getRow()
if row:
rows.append(row)
else:
break
for row in rows:
ws.append(row)
chart = ScatterChart()
chart.title = "Interpreted Data"
chart.style = 2
chart.x_axis.title = "Elapsed Time (s)"
chart.y_axis.title = "Value"
chart.height = 20
chart.width = 45
xvalues = Reference(ws, min_col=1, min_row=2, max_row=len(rows) + 1)
for i in range(2, len(dataTitles) + 1):
values = Reference(ws, min_col=i, min_row=1, max_row=len(rows) + 1)
series = Series(values, xvalues, title_from_data=True)
# series.marker.symbol = "circle"
# series.marker.size = 3
# series.graphicalProperties.line.noFill = True
series.smooth = True
series.graphicalProperties.line.width = 10000 # width in EMUs
chart.series.append(series)
ws.add_chart(chart, "A1")
wb.save("{}.xlsx".format(SaveName))
def plot(ws_Excel_Unite_1_exp,
ws_Excel_Unite_2_exp,
ws_Excel_Unite_exp_path,
dir_file_Excel_Unite,
NameGenInvestigated):
global n, value_to_time, name_obj_gen, h
ws_Excel_Unite_exp_path.cell(
column=1,
row=1,
value=f"Загруженный файл {dir_file_Excel_Unite}"
)
num_time_to_row = 0
col_count = ws_Excel_Unite_1_exp.max_column - 1
col_gen = 0
for n in range(1, 50):
value_to_time = ws_Excel_Unite_1_exp[f"A{n}"].value
if value_to_time == ' TIME':
num_time_to_row = int(n) + 1
for h in range(2, col_count + 1):
name_obj_gen = ws_Excel_Unite_1_exp[f"{get_column_letter(h)}{str(num_time_to_row - 1)}"].value
if name_obj_gen == f"P/{NameGenInvestigated}":
col_gen = h
break
for g in range(2, col_count + 1):
nameObjExp1 = ws_Excel_Unite_1_exp[f"{get_column_letter(g)}{str(num_time_to_row - 1)}"].value
nameObjExp2 = ws_Excel_Unite_2_exp[f"{get_column_letter(g)}{str(num_time_to_row - 1)}"].value
nameObjExp1_dict = dict_set[str(nameObjExp1)]
nameObjExp2_dict = dict_set[str(nameObjExp2)]
ws_Excel_Unite_1_exp.cell(column=g,
row=num_time_to_row - 1,
value=f"с PSS {nameObjExp1_dict}")
ws_Excel_Unite_2_exp.cell(column=g,
row=num_time_to_row - 1,
value=f"без PSS {nameObjExp2_dict}")
max_Chart = max_var_object(ws_Excel_Unite_1_exp, col_gen, num_time_to_row)
min_Chart = min_var_object(ws_Excel_Unite_1_exp, col_gen, num_time_to_row)
maxCh60 = max_Ch60(ws_Excel_Unite_1_exp, col_gen, num_time_to_row)
minCh60 = min_Ch60(ws_Excel_Unite_1_exp, col_gen, num_time_to_row)
# График 1 активной мощности СГ на интервале от 0 до 15
ch4 = ScatterChart()
xvalues_exp1 = Reference(ws_Excel_Unite_1_exp,
min_col=1,
min_row=num_time_to_row,
max_row=ws_Excel_Unite_1_exp.max_row)
values_exp1 = Reference(ws_Excel_Unite_1_exp,
min_col=int(col_gen),
min_row=num_time_to_row - 1,
max_row=ws_Excel_Unite_1_exp.max_row)
series_exp1 = Series(values_exp1, xvalues_exp1, title_from_data=True)
name_object = ws_Excel_Unite_1_exp[f'{get_column_letter(col_gen)}{str(num_time_to_row - 1)}'].value
remove_list = ['с', 'PSS']
edit_str_as_list = name_object.split()
final_list = [word for word in edit_str_as_list if word not in remove_list]
final_str = ' '.join(final_list)
final_list.clear()
ch4.title = f"{final_str}"
ch4.x_axis.title = "Время, с" # название оси Х
ch4.y_axis.title = "Активная мощность, МВт" # название оси У
# ch4.style = 12 # стиль диаграммы
ch4.series.append(series_exp1)
ch4.x_axis.scaling.min = 49
ch4.y_axis.scaling.min = 0
ch4.y_axis.scaling.max = max_Chart + 10
ch4.x_axis.scaling.max = 65
ws_Excel_Unite_exp_path.add_chart(ch4, f'{get_column_letter(1)}{str(5)}')
# График 2 активной мощности СГ на интервале от 0 до 15
ch2 = ScatterChart()
xvalues_exp1 = Reference(ws_Excel_Unite_1_exp,
min_col=1,
min_row=num_time_to_row,
max_row=ws_Excel_Unite_1_exp.max_row)
values_exp1 = Reference(ws_Excel_Unite_1_exp,
min_col=int(col_gen),
min_row=num_time_to_row - 1,
max_row=ws_Excel_Unite_1_exp.max_row)
xvalues_exp2 = Reference(ws_Excel_Unite_2_exp,
min_col=1,
min_row=num_time_to_row,
max_row=ws_Excel_Unite_2_exp.max_row)
values_exp2 = Reference(ws_Excel_Unite_2_exp,
min_col=int(col_gen),
min_row=num_time_to_row - 1,
max_row=ws_Excel_Unite_2_exp.max_row)
series_exp1 = Series(values_exp1, xvalues_exp1, title_from_data=True)
series_exp2 = Series(values_exp2, xvalues_exp2, title_from_data=True)
name_object = ws_Excel_Unite_1_exp[f'{get_column_letter(col_gen)}{str(num_time_to_row - 1)}'].value
remove_list = ['с', 'PSS']
edit_str_as_list = name_object.split()
final_list1 = [word for word in edit_str_as_list if word not in remove_list]
final_str = ' '.join(final_list1)
final_list1.clear()
# ch2.style = 12 # стиль диаграммы
ch2.title = f"{final_str}"
ch2.x_axis.title = "Время, с" # название оси Х
ch2.y_axis.title = "Активная мощность, МВт" # название оси У
series_exp2.smooth = False # сграживание кривой
series_exp2.graphicalProperties.line.width = 350 # толщина кривой линии
series_exp2.graphicalProperties.line.solidFill = 'FF0000' # цвет кривой Red
ch2.series.append(series_exp1)
ch2.series.append(series_exp2)
ch2.x_axis.scaling.min = 49
ch2.y_axis.scaling.min = 0
ch2.x_axis.scaling.max = 65
ws_Excel_Unite_exp_path.add_chart(ch2, get_column_letter(11) + str(22))
# График 3 активной мощности СГ на интервале от 1 до 6
ch_PSS = ScatterChart()
xvalues_exp1_pss = Reference(ws_Excel_Unite_1_exp,
min_col=1,
min_row=num_time_to_row,
max_row=ws_Excel_Unite_1_exp.max_row)
values_exp1_pss = Reference(ws_Excel_Unite_1_exp,
min_col=int(col_gen),
min_row=num_time_to_row - 1,
max_row=ws_Excel_Unite_1_exp.max_row)
xvalues_exp2_pss = Reference(ws_Excel_Unite_2_exp,
min_col=1,
min_row=num_time_to_row,
max_row=ws_Excel_Unite_2_exp.max_row)
values_exp2_pss = Reference(ws_Excel_Unite_2_exp,
min_col=int(col_gen),
min_row=num_time_to_row - 1,
max_row=ws_Excel_Unite_2_exp.max_row)
series_exp1_pss = Series(values_exp1_pss,
xvalues_exp1_pss,
title_from_data=True)
series_exp2_pss = Series(values_exp2_pss,
xvalues_exp2_pss,
title_from_data=True)
name_object = ws_Excel_Unite_1_exp[f'{get_column_letter(col_gen)}{str(num_time_to_row - 1)}'].value
remove_list = ['с', 'PSS']
edit_str_as_list = name_object.split()
final_list2 = [word for word in edit_str_as_list if word not in remove_list]
final_str = ' '.join(final_list2)
final_list2.clear()
ch_PSS.title = f"Эффективность PSS(стаб.) {final_str}"
ch_PSS.x_axis.title = "Время, с" # название оси Х
ch_PSS.y_axis.title = "Активная мощность, МВт" # название оси У
series_exp2_pss.smooth = False # сграживание кривой
series_exp2_pss.graphicalProperties.line.width = 350 # сграживание кривой
series_exp2_pss.graphicalProperties.line.solidFill = 'FF0000' # цвет кривой Red
ch_PSS.series.append(series_exp1_pss)
ch_PSS.series.append(series_exp2_pss)
ch_PSS.x_axis.scaling.min = 51
ch_PSS.y_axis.scaling.min = int(min_Chart)
ch_PSS.y_axis.scaling.max = int(max_Chart + 4)
ch_PSS.x_axis.scaling.max = 56
ws_Excel_Unite_exp_path.add_chart(ch_PSS, f'{get_column_letter(21)}{str(22)}')
# График 4 активной мощности СГ на интервале от 11 до 26
ch3 = ScatterChart()
xvalues = Reference(ws_Excel_Unite_1_exp,
min_col=1,
min_row=num_time_to_row,
max_row=ws_Excel_Unite_1_exp.max_row)
values = Reference(ws_Excel_Unite_1_exp,
min_col=col_gen,
min_row=num_time_to_row - 1,
max_row=ws_Excel_Unite_1_exp.max_row)
series = Series(values, xvalues, title_from_data=True)
name_object = ws_Excel_Unite_1_exp[f'{get_column_letter(col_gen)}{str(num_time_to_row - 1)}'].value
remove_list = ['с', 'PSS']
edit_str_as_list = name_object.split()
final_list3 = [word for word in edit_str_as_list if word not in remove_list]
final_str = ' '.join(final_list3)
final_list3.clear()
# ch3.style = 12 # стиль диаграммы
ch3.title = f"{final_str}"
ch3.x_axis.title = "Время, с" # название оси Х
ch3.y_axis.title = "Активная мощность, МВт" # название оси У
ch3.series.append(series)
ch3.x_axis.scaling.min = 61
ch3.y_axis.scaling.min = int(minCh60)
ch3.y_axis.scaling.max = int(maxCh60 + 1)
ch3.x_axis.scaling.max = 76
ws_Excel_Unite_exp_path.add_chart(ch3, get_column_letter(1) + str(22))
for k in range(2, col_count + 1):
ch1 = ScatterChart()
xvalues_exp1 = Reference(ws_Excel_Unite_1_exp,
min_col=1,
min_row=num_time_to_row,
max_row=ws_Excel_Unite_1_exp.max_row)
values_exp1 = Reference(ws_Excel_Unite_1_exp,
min_col=k,
min_row=num_time_to_row - 1,
max_row=ws_Excel_Unite_1_exp.max_row)
xvalues_exp2 = Reference(ws_Excel_Unite_2_exp,
min_col=1,
min_row=num_time_to_row,
max_row=ws_Excel_Unite_2_exp.max_row)
values_exp2 = Reference(ws_Excel_Unite_2_exp,
min_col=k,
min_row=num_time_to_row - 1,
max_row=ws_Excel_Unite_2_exp.max_row)
series_exp1 = Series(values_exp1, xvalues_exp1, title_from_data=True)
series_exp2 = Series(values_exp2, xvalues_exp2, title_from_data=True)
name_object = ws_Excel_Unite_1_exp[get_column_letter(k) + str(num_time_to_row - 1)].value
remove_list = ['с', 'PSS']
edit_str_as_list = name_object.split()
final_list4 = [word for word in edit_str_as_list if word not in remove_list]
final_str = ' '.join(final_list4)
final_list4.clear()
# ch1.style = 12 # стиль диаграммы
ch1.title = f"{final_str}"
ch1.x_axis.title = "Время, с" # название оси Х
ch1.y_axis.title = "Активная мощность, МВт" # название оси У
series_exp2.smooth = False # сграживание кривой
series_exp2.graphicalProperties.line.width = 350 # сграживание кривой
series_exp2.graphicalProperties.line.solidFill = 'FF0000' # цвет кривой Red
series_exp2.smooth = False # сграживание кривой
series_exp2.graphicalProperties.line.width = 350 # сграживание кривой
series_exp2.graphicalProperties.line.solidFill = 'FF0000' # цвет кривой Red
ch1.series.append(series_exp1)
ch1.series.append(series_exp2)
ch1.x_axis.scaling.min = 48
ch1.y_axis.scaling.min = 0
ch1.x_axis.scaling.max = 80
# ch1.y_axis.scaling.max = 650 # диапазоны на графике
size_plot = 10
if k < int(col_count / 2):
if k == 2:
ws_Excel_Unite_exp_path.add_chart(ch1, get_column_letter(1) + str(40))
elif k != 2:
ws_Excel_Unite_exp_path.add_chart(ch1, get_column_letter((k - 2) * size_plot) + str(40))
else:
ws_Excel_Unite_exp_path.add_chart(ch1, get_column_letter((k - 2) * size_plot) + str(40))
