def average_se_trace_full_experiment_chart(file_max_column, file_max_row,
sheet):
# average_se_trace_full_experiment_chart generates a plot with the average values from the average_se_trace_full_experiment function.
chart_cell = sheet.cell(row=4, column=file_max_column + 7).coordinate
chart = ScatterChart()
chart.style = 2
chart.title = "Experiment average trace"
chart.y_axis.title = "Fura2 fluorescence ratio (a.u)"
chart.x_axis.title = "Time (s)"
chart.legend = None
chart.height = 10 # default is 7.5
chart.width = 20 # default is 15
chart.x_axis.majorUnit = 60
ca_ex_st.style_chart(chart.title, chart)
xvalues = Reference(sheet,
min_col=file_max_column + 3,
min_row=3,
max_col=file_max_column + 3,
max_row=file_max_row)
yvalues = Reference(sheet,
min_col=file_max_column + 4,
min_row=3,
max_col=file_max_column + 4,
max_row=file_max_row)
series = Series(yvalues, xvalues)
series_trendline = Series(yvalues, xvalues)
chart.series.append(series)
chart.series.append(series_trendline)
sheet.add_chart(chart, chart_cell)
def create_scatter(df, wb, sheet_name):
df_columns = df.columns.tolist()
ws = wb[sheet_name]
chart = ScatterChart()
chart.title = sheet_name
chart.height = 15
chart.width = 30
chart.x_axis.scaling.max = max(df['timestamps'])
chart.x_axis.scaling.min = min(df['timestamps'])
chart.x_axis.title = df_columns[1]
for i in df_columns:
if i in sheet_name:
chart.x_axis.title = i
chart.y_axis.title = 'intensity_of_emotion'
start_row = 2
end_row = len(df) + 1
x_values = Reference(ws, min_col=1, min_row=start_row, max_row=end_row)
for i in range(df_columns.index('happy') + 1, df_columns.index('confused') + 2):
values = Reference(ws, min_col=i, min_row=1, max_row=end_row)
series = Series(values, x_values, title_from_data=True)
chart.series.append(series)
letter = get_column_letter(len(df_columns) + 2)
ws.add_chart(chart, f"{letter}1")
def create_annual_chart(worksheet=None, year=0, min_row=1, max_row=1):
chart = ScatterChart()
# sets the chart styling
chart.title = f'{year}'
chart.x_axis.title = 'Month'
chart.y_axis.title = 'Amount'
chart.legend.position = 'b'
chart.height = 7.7
chart.width = 21.5
xvalues = Reference(worksheet=worksheet,
min_col=2,
min_row=min_row + 1,
max_row=max_row)
for col in range(3, 6):
values = Reference(worksheet=worksheet,
min_col=col,
min_row=min_row,
max_row=max_row)
series = Series(values, xvalues, title_from_data=True)
chart.series.append(series)
return chart
def create_graph(result_file, result_book, sheet_name_active, current_format_sheet):
# Valiable Assignment
book_name_will_plot_graph = os.path.basename(result_file)
sheet_name_will_plot_graph = result_book[sheet_name_active]
max_row_in_sheet_name = sheet_name_will_plot_graph.max_row
# Assign value in X-axis and Y-axis
scatter_chart = ScatterChart()
y_values = Reference(sheet_name_will_plot_graph, min_row=3, max_row=max_row_in_sheet_name, min_col=3)
x_values = Reference(sheet_name_will_plot_graph, min_row=3, max_row=max_row_in_sheet_name, min_col=2)
series = Series(y_values, x_values, title=None, title_from_data=False)
scatter_chart.series.append(series)
# Adjust Scatter Element
scatter_chart.y_axis.scaling.min = 0
scatter_chart.legend = None
scatter_chart.y_axis.number_format = "0.00"
# Paste Scatter_chart
current_format_sheet.add_chart(scatter_chart, "A5")
# Setting width and height of scatter_chart
scatter_chart.width = 14
scatter_chart.height = 8
return scatter_chart
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 get_chart():
"Returns schatter chart for example"
chart = ScatterChart()
chart.height = 10
chart.width = 15
chart.style = 2
chart.x_axis.title = "X"
chart.y_axis.title = "Y"
return chart
def draw_lateral_pattern_scatterchart(data_work_book, data_columns_list):
# build a new work sheet for contrast charts
data_work_book.create_sheet(title='Lateral Contrast LineChart', index=0)
tmp_dict = get_pattern_rows_map(data_work_book)
#print(tmp_dict['4kread'].keys())
# plot chart
column_num = 0
for data_column in data_columns_list:
#column_position = cu.num2letter(column_num *8 +1)
column_position = cu.num2letter(column_num *9 +1)
column_num = column_num +1
# get pattern info in sheets combined together.
pattern_num = 0
for pattern_name in cu.bp_sort(tmp_dict.keys(), screening=True):
# 4mwrite
#row_position = str(pattern_num *16 +1)
#row_position = str(pattern_num *22 +1)
row_position = str(pattern_num *26 +1)
pattern_num = pattern_num +1
chart_position = column_position + row_position
#print(chart_position)
# chart format
chart = ScatterChart()
#chart.height = 10
chart.height = 12
chart.width = 17
chart.title = str(pattern_name)
chart.legend.position = 't'
tmp_sheet = tmp_dict[pattern_name].keys()[0]
chart.x_axis.title = wb[tmp_sheet][str(cu.num2letter(data_column)) + '1'].value
chart.y_axis.title = 'latency(ms)'
# turn majorGridlines off using shapes.GraphicalProperties and drawing.LineProperties
#chart.y_axis.majorGridlines.spPr = GraphicalProperties(noFill = 'True')
#chart.y_axis.majorGridlines.spPr.ln = LineProperties(solidFill = '000000')
#chart.x_axis.majorGridlines = ChartLines()
chart.x_axis.majorGridlines.spPr = GraphicalProperties(noFill=True)
chart.x_axis.majorGridlines.spPr.ln = LineProperties(solidFill = 'F0F0F0')
#chart.dLbls = DataLabelList()
#chart.dLbls.showVal = 0
# add info from different sheet for a certain pattern , 'sheet1':[n,n+1]
line_set_info = tmp_dict[pattern_name]
#print(line_set_info)
for sheetN_set_name in line_set_info.keys():
line_title = str(sheetN_set_name)
line_set = line_set_info[sheetN_set_name]
#print(sheetN_set_name,line_set)
# width (samples name)
xvalues = Reference(data_work_book[sheetN_set_name], min_col=1, min_row=line_set[0], max_row=line_set[-1])
# height (value point)
yvalues = Reference(data_work_book[sheetN_set_name], min_col=data_column, min_row=line_set[0], max_row=line_set[-1])
series = Series(yvalues, xvalues, title=line_title)
chart.series.append(series)
wb['Lateral Contrast LineChart'].add_chart(chart, chart_position)
def _generate_chart(worksheet, top_row: int,
leftmost_col: int) -> ScatterChart:
chart = ScatterChart()
chart.title = "RCF"
chart.style = 13
chart.height = 18
chart.width = 28
chart.x_axis.title = "Days"
chart.y_axis.title = "Milestone Type"
xvalues = Reference(worksheet, min_col=3, min_row=10, max_row=33)
yvalues = Reference(worksheet, min_col=4, min_row=10, max_row=33)
series = Series(yvalues, xvalues)
series.marker.size = 6
chart.series.append(series)
return chart
def gen_workbook(input_file_or_dir,output_file):
wb = Workbook()
info_token=plot_state()
if os.path.isfile(input_file_or_dir):
files=[input_file_or_dir]
if os.path.isdir(input_file_or_dir):
files=glob.glob(os.path.join(input_file_or_dir,"*.dat"))
else:
return
for my_file in files:
print("about to save1",my_file)
if plot_load_info(info_token,my_file)==True:
x=[]
y=[]
z=[]
data=dat_file()
if dat_file_read(data,my_file)==True:
print("read",my_file)
ws = wb.create_sheet(title=title_truncate(os.path.basename(my_file)))
ws.cell(column=1, row=1, value=info_token.title)
ws.cell(column=1, row=2, value=info_token.x_label+" ("+info_token.x_units+") ")
ws.cell(column=2, row=2, value=info_token.y_label+" ("+info_token.y_units+") ")
for i in range(0,data.y_len):
ws.cell(column=1, row=i+3, value=data.y_scale[i])
ws.cell(column=2, row=i+3, value=data.data[0][0][i])
c1 = ScatterChart()
c1.title = info_token.title
c1.style = 13
c1.height=20
c1.width=20
c1.y_axis.title = info_token.y_label+" ("+info_token.y_units+") "
c1.x_axis.title = info_token.x_label+" ("+info_token.x_units+") "
xdata = Reference(ws, min_col=1, min_row=3, max_row=3+data.y_len)
ydata = Reference(ws, min_col=2, min_row=3, max_row=3+data.y_len)
series = Series(ydata,xdata, title_from_data=True)
c1.series.append(series)
ws.add_chart(c1, "G4")
print("about to save1")
wb.save(filename = output_file)
print("about to save0")
def single_cell_slope_trace_chart(column, column_slope_charts, chart_name,
row_charts, row_number, sheet, slope_name,
slope_time, time_column):
# single_cell_slope_trace_chart function generates 1 scatter chart within the file for each of the traces where:
# x_axis = slope_time
# y_axis = Fura2 fluorescence.#
# column_individual_trace_charts: Determines the column where the chart will be created
# experiment_number: Used as the chart title.
# file_max_row: calculated by any of the analysis functions.
# row_individual_trace_charts: Determines the column where the chart will be created
chart_cell = sheet.cell(row=row_charts,
column=column_slope_charts).coordinate
chart = ScatterChart()
chart.style = 2
chart.title = f"{chart_name}: {slope_name} slope"
chart.y_axis.title = "Fura2 fluorescence ratio (a.u)"
chart.x_axis.title = "Time (s)"
chart.legend = None
chart.height = 7.5 # default is 7.5
chart.width = 15 # default is 15
chart.x_axis.majorUnit = 10
ca_ex_st.style_chart(chart.title, chart)
xvalues = Reference(sheet,
min_col=time_column,
min_row=row_number + 1,
max_col=time_column,
max_row=row_number + 1 + slope_time)
yvalues = Reference(sheet,
min_col=column,
min_row=row_number + 1,
max_col=column,
max_row=row_number + 1 + slope_time)
series = Series(yvalues, xvalues)
series_trendline = Series(yvalues, xvalues)
chart.series.append(series)
chart.series.append(series_trendline)
line = chart.series[0]
line.graphicalProperties.line.noFill = True
line.trendline = Trendline(dispRSqr=True, dispEq=True)
sheet.add_chart(chart, chart_cell)
def histogram(ws, series: np.ndarray, title="Distribution", bins=50):
"""
:param title: 图表标题
:param ws: 储存数据的worksheet
:param series: 画直方图的数据
:param bins: 分段的个数
:return: chart
"""
hist, bin_edges = np.histogram(series, bins)
count = np.insert(hist, 0, hist[0])
max_row = bins + 1
current = np.repeat(series[-1], max_row)
max_number = np.linspace(0, hist.max(), max_row, endpoint=True)
data = np.vstack((bin_edges, count, current, max_number)).transpose()
row_offset = 0 if ws.max_row == 1 else ws.max_row
print(row_offset)
for r in data:
ws.append(r.tolist())
chart = ScatterChart()
chart.width = 22 # default is 15
chart.height = 15 # default is 7.5
chart.style = 2
chart.title = title
chart.y_axis.title = 'Count'
chart.x_axis.majorGridlines = None
chart.y_axis.number_format = COMMA0_FORMAT
chart.x_axis.title = 'Bin'
chart.x_axis.number_format = PERCENT_FORMAT
yvalues = Reference(ws, min_col=2, min_row=row_offset+1, max_row=ws.max_row)
xvalues = Reference(ws, min_col=1, min_row=row_offset+1, max_row=ws.max_row)
series = Series(values=yvalues, xvalues=xvalues, title='Count')
series.smooth = True
chart.series.append(series)
yvalues = Reference(ws, min_col=4, min_row=row_offset+1, max_row=ws.max_row)
xvalues = Reference(ws, min_col=3, min_row=row_offset+1, max_row=ws.max_row)
series = Series(values=yvalues, xvalues=xvalues, title='Current')
chart.series.append(series)
return chart
def _chart2excel(writer, sheet, charts):
import xlrd
from openpyxl.chart import ScatterChart, Series
sn = writer.book.sheetnames
named_ranges = {
'%s!%s' % (sn[d.localSheetId], d.name): d.value
for d in writer.book.defined_names.definedName
}
m, h, w = 3, 7.94, 13.55
for i, (k, v) in enumerate(sorted(charts.items())):
chart = ScatterChart()
chart.height = h
chart.width = w
_map = {
('title', 'name'): ('title', ),
('y_axis', 'name'): ('y_axis', 'title'),
('x_axis', 'name'): ('x_axis', 'title'),
}
_filter = {
('legend', 'position'): lambda x: x[0],
}
it = {
s: _filter[s](o) if s in _filter else o
for s, o in sh.stack_nested_keys(v['set'])
}
for s, o in sh.map_dict(_map, it).items():
c = chart
for j in s[:-1]:
c = getattr(c, j)
setattr(c, s[-1], o)
for s in v['series']:
xvalues = named_ranges[_data_ref(s['x'])]
values = named_ranges[_data_ref(s['y'])]
series = Series(values, xvalues, title=s['label'])
chart.series.append(series)
n = int(i / m)
j = i - n * m
sheet.add_chart(chart, xlrd.cellname(15 * j, 8 * n))
def create_scatter_chart(x_cells, y_cells, x_title, y_title, x_range=None, y_range=None, legends=None, height=10, width=20):
"""
@fn create_scatter_chart()
@brief
@param x_cells 横軸データ参照範囲(Reference)
@param y_cells 縦軸データ参照範囲(Reference)
@param x_title 横軸ラベル
@param y_title 縦軸ラベル
@param x_range 定義域
@param y_range 値域
@param legends 凡例
@param height グラフの高さ
@param width グラフの幅
@retval chart グラフ
"""
chart = ScatterChart()
chart.x_axis.title = x_title
chart.y_axis.title = y_title
chart.style = 2
chart.height = height
chart.width = width
if x_range is not None:
chart.x_axis.scaling.min = min(x_range)
chart.x_axis.scaling.max = max(x_range)
if y_range is not None:
chart.y_axis.scaling.min = min(y_range)
chart.y_axis.scaling.max = max(y_range)
if legends is None:
chart.legend = None
else:
chart.legend.position = "t"
if type(x_cells) != list and type(y_cells) != list:
series = Series(y_cells, x_cells, title=legends)
chart.series.append(series)
elif type(x_cells) != list and type(y_cells) == list:
for y_cells_unit, legend in zip(y_cells, legends):
series = Series(y_cells_unit, x_cells, title=legend)
chart.series.append(series)
elif type(x_cells) == list and type(y_cells) == list:
for x_cells_unit, y_cells_unit, legend in zip(x_cells, y_cells, legends):
series = Series(y_cells_unit, x_cells_unit, title=legend)
chart.series.append(series)
return chart
def single_cell_trace_in_individual_chart(column,
column_individual_trace_charts,
chart_name, file_max_row, sheet,
row_individual_trace_charts,
time_column):
# single_cell_trace_in_individual_chart function generates 1 scatter chart within the file for each of the traces where:
# x_axis = time(s)
# y_axis = Fura2 fluorescence.#
# column_individual_trace_charts: Determines the column where the chart will be created
# experiment_number: Used as the chart title.
# file_max_row: calculated by any of the analysis functions.
# row_individual_trace_charts: Determines the column where the chart will be created
# sheet: calculated by any of the analysis functions.
# time_column: calculates the maximun column number within the file.
chart_cell = sheet.cell(row=row_individual_trace_charts,
column=column_individual_trace_charts).coordinate
chart = ScatterChart()
chart.style = 2
chart.title = f"{chart_name}: individual_trace"
chart.y_axis.title = "Fura2 fluorescence ratio (a.u)"
chart.x_axis.title = "Time (s)"
chart.legend = None
chart.height = 7.5 # default is 7.5
chart.width = 15 # default is 15
chart.x_axis.majorUnit = 60
ca_ex_st.style_chart(chart.title, chart)
xvalues = Reference(sheet,
min_col=time_column,
min_row=3,
max_col=time_column,
max_row=file_max_row)
yvalues = Reference(sheet,
min_col=column,
min_row=3,
max_col=column,
max_row=file_max_row)
series = Series(yvalues, xvalues)
chart.series.append(series)
sheet.add_chart(chart, chart_cell)
def generate_captures_graph(self, captures_info, row_count):
""" Gera o gráfico Sinais X Tempo. """
my_chart = ScatterChart()
my_chart.title = 'Gráfico dos Sinais'
my_chart.style = 16
my_chart.y_axis.title = 'Sinal'
my_chart.x_axis.title = 'Tempo (segundos)'
x_values = Reference(self.spreadsheet,
min_col=2,
min_row=row_count - len(captures_info),
max_row=row_count - 3)
y_values = Reference(self.spreadsheet,
min_col=6,
min_row=row_count - len(captures_info) - 1,
max_row=row_count - 3)
series = Series(y_values, x_values, title_from_data=True)
my_chart.series.append(series)
my_chart.width = 23
my_chart.height = 10
self.spreadsheet.add_chart(my_chart, f"A{row_count}")
def format_chart(chart: ScatterChart, x_axis_title: str, y_axis_title: str, title: str):
chart.height = 15
chart.width = 30
chart.x_axis.tickLblPos = "low"
chart.title = title
chart.x_axis.title = x_axis_title
chart.y_axis.title = y_axis_title
font = drawing.text.Font(typeface='Arial')
cp_axis = CharacterProperties(latin=font, sz=1600, b=True)
cp_axis_title = CharacterProperties(latin=font, sz=1600)
cp_title = CharacterProperties(latin=font, sz=1200)
chart.y_axis.txPr = RichText(p=[Paragraph(pPr=ParagraphProperties(defRPr=cp_axis), endParaRPr=cp_axis)])
chart.y_axis.title.txPr = RichText(p=[Paragraph(pPr=ParagraphProperties(defRPr=cp_axis),
endParaRPr=cp_axis_title)])
chart.x_axis.txPr = RichText(p=[Paragraph(pPr=ParagraphProperties(defRPr=cp_axis), endParaRPr=cp_axis)])
chart.x_axis.title.txPr = RichText(p=[Paragraph(pPr=ParagraphProperties(defRPr=cp_axis),
endParaRPr=cp_axis_title)])
chart.title.txPr = RichText(p=[Paragraph(pPr=ParagraphProperties(defRPr=cp_title), endParaRPr=cp_title)])
def single_cell_traces_in_one_chart(file_max_column, file_max_row, sheet):
# single_cell_traces_in_one_chart function generates 1 single scatter chart within the file with all the traces represented where:
# x_axis = time(s)
# y_axis = Fura2 fluorescence.
# series = one serie for each analyzed cell
# file_max_column: calculated by any of the analysis functions.
# file_max_row: calculated by any of the analysis functions.
# sheet: calculated by any of the analysis functions.
chart_cell = sheet.cell(row=25, column=file_max_column + 7).coordinate
chart = ScatterChart()
chart.style = 2
chart.title = "Single cell traces"
chart.y_axis.title = "Fura2 fluorescence ratio (a.u)"
chart.x_axis.title = "Time (s)"
chart.legend = None
chart.height = 10 # default is 7.5
chart.width = 20 # default is 15
chart.x_axis.majorUnit = 60
ca_ex_st.style_chart(chart.title, chart)
xvalues = Reference(sheet,
min_col=file_max_column + 3,
min_row=3,
max_col=file_max_column + 3,
max_row=file_max_row)
for column in range(2, file_max_column + 1):
# print(column)
values = Reference(sheet,
min_col=column,
min_row=3,
max_row=file_max_row)
series = Series(values, xvalues)
chart.series.append(series)
sheet.add_chart(chart, chart_cell)
def add_scatter_chart(self, title, min_col=1, min_row=1):
chart = ScatterChart()
chart.title = title
chart.height = 10
chart.width = 18
chart.style = 2 # 线条的style（8种颜色循环，1：灰度，2：异色，3-8：同色渐变-蓝，棕红，绿，紫，青，橙；1-8线条较细，9-16加粗）
chart.y_axis.title = 'Temperature'
chart.x_axis.title = "Open percentage"
chart.x_axis.scaling.max = 1
xvalues = Reference(self.sheet,
min_col=min_col,
min_row=min_row + 1,
max_row=self.sheet.max_row)
for i in range(2, self.sheet.max_column + 1): # 数据列循环
yvalues = Reference(self.sheet,
min_col=i,
min_row=min_row,
max_row=self.sheet.max_row)
series = Series(yvalues, xvalues, title_from_data=True)
chart.series.append(series)
self.sheet.add_chart(chart, "A16") # 将图表添加到 sheet中
def create_diagram(quasicycle, height=7, width=10, style=11):
chart = ScatterChart()
chart.title = quasicycle.name
chart.height = height
chart.width = width
chart.x_axis.title = ''
chart.y_axis.title = ''
chart.legend = None
rows_reference = Reference(quasicycle.sheet,
min_col=quasicycle.start_cell_col,
min_row=quasicycle.start_cell_row,
max_row=quasicycle.start_cell_row +
quasicycle.size)
cols_reference = Reference(quasicycle.sheet,
min_col=quasicycle.start_cell_col + 1,
min_row=quasicycle.start_cell_row,
max_row=quasicycle.start_cell_row +
quasicycle.size)
series = Series(cols_reference, rows_reference, title_from_data=False)
chart.layoutTarget = "inner"
chart.style = style
chart.series.append(series)
return chart
def plot_inside_excel():
wb = load_workbook(filename=saints_excel_name)
# Active WorkSheet
ws = wb.active
chat1 = ScatterChart()
# style = MinMax(allow_none=True, min=1, max=48)
# chat1.style = 7
chat1.title = '2020 One Year Bible Study'
chat1.x_axis.title = 'Date'
chat1.y_axis.title = 'Time(o\'clock)'
# set major/minor unit and max value of y axis
chat1.y_axis.majorUnit = 1
chat1.y_axis.minorUnit = 1
chat1.y_axis.scaling.max = 24
chat1.y_axis.scaling.min = 0
# enlarge the chart, default is too small
# width = 15 # in cm, approx 5 rows
# height = 7.5 # in cm, approx 14 rows
chat1.height = chat1.height + 8
chat1.width = chat1.width + 32
xvalues = Reference(ws, min_col=2, min_row=2, max_row=ws.max_row)
for i in range(len(saint_name_list)):
values = Reference(ws, min_col=8 + i, min_row=2, max_row=ws.max_row)
series = Series(values,
xvalues,
title=saint_name_list[i],
title_from_data=False)
# {'triangle', 'dash', 'x', 'auto', 'diamond', 'circle', 'star',
# 'picture', 'square', 'dot', 'plus'}
series.marker = openpyxl.chart.marker.Marker('circle')
series.graphicalProperties.line.noFill = True
chat1.series.append(series)
ws.add_chart(chat1, "A10")
wb.save(filename=saints_excel_name)
def create_bending_chart(filepath_list, data, filepath):
# Extract the data from the native files
for file in filepath_list:
print(f'Started processing file "{file.name}..."')
# Get the sample's id from the filepath
sample_id = get_id_from_filepath(file)
# Prep the dictionary to store the data
data[sample_id] = []
# Open the workbook
workbook = openpyxl.load_workbook(file)
sheet = workbook['Sheet1']
last_row = sheet.max_row
# Collect all the bending data
for i in range(2, last_row):
# Extract the raw data
load = float(sheet['M' + str(i)].value)
extension = float(sheet['K' + str(i)].value)
# Add the data to the master file
data[sample_id].append([load, extension])
print(f'Finished processing file "{file.name}."')
# Add the calculated data into the new workbook
workbook = openpyxl.load_workbook(filepath)
sheet = workbook.active
# Chart formatting
chart = ScatterChart(scatterStyle='smoothMarker')
chart.x_axis.axPos = 'b' # Rotates the label to be horizontal
chart.title = 'Bending Samples'
chart.height = 17
chart.width = 25
chart.legend = None
# Chart axis formatting
chart.x_axis.title = 'Compressive Extension (mm)'
chart.y_axis.title = 'Compressive Load (N)'
for key, values in (sorted(data.items())):
print(f'Started writing data for sample_id {key}...')
# Find the next available columns and rows to add the data to
last_col = sheet.max_column
if last_col == 1:
last_col -= 1
load_col = last_col + 1
extension_col = last_col + 2
start_row = 2
# Add the headers for this key's data
sheet.cell(row=1, column=load_col).value = f'ID({key})-Load'
sheet.cell(row=1, column=extension_col).value = f'ID({key})-Extension'
# Add the bending data in for the key
for i in range(len(values)):
sheet.cell(row=start_row + i, column=load_col).value = values[i][0]
sheet.cell(row=start_row + i,
column=extension_col).value = values[i][1]
print(f'Finished writing data for sample_id {key}.')
# Create a Series for the Chart with the new data
load_reference = Reference(sheet,
min_col=load_col,
max_col=load_col,
min_row=2,
max_row=len(values))
extension_reference = Reference(sheet,
min_col=extension_col,
max_col=extension_col,
min_row=2,
max_row=len(values))
series = Series(values=load_reference, xvalues=extension_reference)
chart.append(series)
sheet.add_chart(chart, 'A1')
workbook.save(filepath)
print(f'Finished creating Chart for Bending Data.')
def build_chart_single(ws, project_name, approval_point, td_data_dict):
chart = ScatterChart()
chart.title = str(project_name) + ' last approved business case: ' + str(
approval_point)
chart.style = 18
chart.x_axis.title = 'Time delta for each business case (year intervals)'
#chart.y_axis.title = 'Milestones'
chart.auto_axis = False
'''this code is necessary to calculate min chart value if its greater than zero'''
x_axis_min = min_value(project_name, td_data_dict)
if x_axis_min >= 0:
chart.x_axis.scaling.min = 0
elif x_axis_min < 0:
anchor = x_axis_min % 365
chart.x_axis.scaling.min = x_axis_min - anchor
chart.x_axis.scaling.max = max_value(
project_name, td_data_dict
) # max number (of days) in the x axis. calculated by max_value function
chart.y_axis.scaling.min = 0
chart.y_axis.scaling.max = 7 # hard coded for now - although minor issue as number of bc time deltas static
chart.height = 9 # default is 7.5
chart.width = 21 # default is 15
'''changes units on x and y axis'''
chart.x_axis.majorUnit = 365 # hard coded for now - minor issue as td will normally be in year intervals
# chart.y_axis.majorUnit = 1.0 testing to see if required
'''reverses y axis'''
#chart.x_axis.scaling.orientation = "minMax"
#chart.y_axis.scaling.orientation = "maxMin"
'''makes the x axis cross at the max y value'''
#chart.x_axis.crosses = 'max'
'''removes lable on y axis'''
chart.y_axis.delete = True
#TOD: sort styling
'''styling chart'''
'''formating for titles'''
#font = Font(typeface='Calibri')
#size = 1200 # 12 point size
#cp = CharacterProperties(latin=font, sz=size, b=True) # Bold
#pp = ParagraphProperties(defRPr=cp)
#rtp = RichText(p=[Paragraph(pPr=pp, endParaRPr=cp)])
#chart.x_axis.title.tx.rich.p[0].pPr = pp # x_axis title
#size_2 = 1400
#cp_2 = CharacterProperties(latin=font, sz=size_2, b=True)
#pp_2 = ParagraphProperties(defRPr=cp_2)
#rtp_2 = RichText(p=[Paragraph(pPr=pp_2, endParaRPr=cp_2)])
#chart.title.tx.rich.p[0].pPr = pp_2 # chart title
'''the below assigns series information to the data that has been placed in the chart.
old values are placed first show that they show behind the current values'''
for i in range(0, 18, 3):
xvalues = Reference(ws, min_col=7, min_row=i + 1, max_row=i + 1)
yvalues = Reference(ws, min_col=8, min_row=i + 1, max_row=i + 1)
series = Series(values=yvalues,
xvalues=xvalues,
title="Latest quarter")
chart.series.append(series)
s1 = chart.series[i]
s1.marker.symbol = "diamond"
s1.marker.size = 10
s1.marker.graphicalProperties.solidFill = "c9e243" # Marker filling greenish
s1.marker.graphicalProperties.line.solidFill = "c9e243" # Marker outline greenish
s1.graphicalProperties.line.noFill = True
xvalues = Reference(ws, min_col=7, min_row=i + 2, max_row=i + 2)
yvalues = Reference(ws, min_col=8, min_row=i + 2, max_row=i + 2)
series = Series(values=yvalues, xvalues=xvalues, title="Last quarter")
chart.series.append(series)
s1 = chart.series[i + 1]
s1.marker.symbol = "diamond"
s1.marker.size = 10
s1.marker.graphicalProperties.solidFill = "ced0ff" # Marker filling grey/blue
s1.marker.graphicalProperties.line.solidFill = "ced0ff" # Marker outline grey/blue
s1.graphicalProperties.line.noFill = True
xvalues = Reference(ws, min_col=7, min_row=i + 3, max_row=i + 3)
yvalues = Reference(ws, min_col=8, min_row=i + 3, max_row=i + 3)
series = Series(values=yvalues, xvalues=xvalues, title="Baseline")
chart.series.append(series)
s1 = chart.series[i + 2]
s1.marker.symbol = "diamond"
s1.marker.size = 10
s1.marker.graphicalProperties.solidFill = "8187ff" # Marker filling blue
s1.marker.graphicalProperties.line.solidFill = "8187ff" # Marker outline blue
s1.graphicalProperties.line.noFill = True
ws.add_chart(chart, "K2")
return ws
def run_CO2_vs_Storage_typ_wochen(number_simulations, name_of_file, name_of_x, name_of_y1, name_of_y2, storage_max,
Variable, enev_restrictions, pv_scenario, status_quo_with_storage,
folder="results"):
print('Creating Output Workbook...')
wb2 = Workbook()
ws2 = wb2.create_sheet('Results', 0)
ws2.cell(row=1, column=2).value = 'Simulation #'
ws2.cell(row=1, column=3).value = 'Battery Capacity'
ws2.cell(row=1, column=4).value = 'TES Capacity'
ws2.cell(row=1, column=5).value = 'Total Storage Capacity'
ws2.cell(row=1, column=6).value = 'Minimal Emissions'
ws2.cell(row=1, column=7).value = 'Emi-gas'
ws2.cell(row=1, column=8).value = 'Emi-pv'
ws2.cell(row=1, column=9).value = 'Emi-grid'
ws2.cell(row=1, column=10).value = 'Emi-lca'
ws2.cell(row=1, column=11).value = 'Costs'
ws2.cell(row=1, column=12).value = '# Battery Equivalent Full Cycles'
ws2.cell(row=1, column=13).value = 'Area PV'
ws2.cell(row=1, column=14).value = 'Area STC'
ws2.cell(row=1, column=15).value = 'LCA PV'
ws2.cell(row=1, column=16).value = 'LCA BATT'
ws2.cell(row=1, column=17).value = 'LCA STC'
ws2.cell(row=1, column=18).value = 'LCA TES'
if pv_scenario:
tag = "pv"
filename_start_values = "start_values_pv.csv"
else:
if enev_restrictions:
tag = "enev_restrictions"
filename_start_values = "start_values_enev.csv"
else:
tag = "no_restrictions"
filename_start_values = "start_values_without_enev.csv"
emissions_max = 1000 # ton CO2 per year
filename_min_costs = folder + "/" + tag + str(0) + ".pkl"
options = {"filename_results": filename_min_costs,
"enev_restrictions": enev_restrictions,
"pv_scenario": pv_scenario,
"status_quo_with_storage": status_quo_with_storage,
"total_storage": 0, # starting value for the storage
"max_storage": storage_max,
"Variable": Variable,
"opt_costs": False,
"store_start_vals": False,
"load_start_vals": True,
"filename_start_vals": filename_start_values,
"envelope_runs": False}
storage_increment = 0
if number_simulations > 0:
storage_increment = storage_max / number_simulations # the larger the number of runs the "higher the resolution" ... i will have more points on the x axis
for i in range(0, number_simulations + 1): # i want to also show the without storage thingy ... so i starts from 0
print('############################' + '\n' + '\n')
print("Running simulation number " + str(i) + " of " + str(number_simulations))
print('############################' + '\n' + '\n')
options["total_storage"] = 0 + i * storage_increment
(max_costs, min_emissions, emi_gas, emi_pv, emi_grid, emi_lca, decision_variables, capacities, powers, storage_Batt ,results_of_clustering) = opti_model.optimize_MA(emissions_max, options)
Battery_size = capacities["Battery"]
TES_size = capacities["TES"]
p_batt_charge = powers["Battery"]["total", "charge"]
ws2.cell(row=2 + i, column=2).value = i
ws2.cell(row=2 + i, column=3).value = Battery_size
ws2.cell(row=2 + i, column=4).value = TES_size
ws2.cell(row=2 + i, column=5).value = Battery_size + TES_size
ws2.cell(row=2 + i, column=6).value = min_emissions
ws2.cell(row=2 + i, column=7).value = emi_gas
ws2.cell(row=2 + i, column=8).value = emi_pv
ws2.cell(row=2 + i, column=9).value = emi_grid
ws2.cell(row=2 + i, column=10).value = emi_lca
ws2.cell(row=2 + i, column=11).value = max_costs
ws2.cell(row=2 + i, column=13).value = capacities["PV" ] /0.125
ws2.cell(row=2 + i, column=14).value = capacities["STC"]
ws2.cell(row=2 + i, column=15).value = (capacities["PV" ] /0.125) * (0.304 / 20)
ws2.cell(row=2 + i, column=16).value = (Battery_size * 243.9) / (1000 * 13.7)
ws2.cell(row=2 + i, column=17).value = (capacities["STC"] * 104.3) / (1000 * 30)
ws2.cell(row=2 + i, column=18).value = (capacities["TES"] * 3.60 * 200) / (1000 * 20)
test =0
delete = 0
if Battery_size > 0:
sigma_p_batt_charge = 0
for j in range(len(p_batt_charge)):
batt_charge_day =0
for jj in range(len(p_batt_charge[j])):
batt_charge_day += p_batt_charge[j][jj]
test += 1
sigma_p_batt_charge += batt_charge_day * results_of_clustering["weights"][j]
delete += 1
ws2.cell(row=2 + i, column=12).value = sigma_p_batt_charge /Battery_size
for i in range(1, (ws2.max_row + 1)):
ws2.row_dimensions[i].height = 15
for i in ('B', 'C', 'D', 'E', 'F', 'G', 'H', 'I', 'J', 'K'):
ws2.column_dimensions[i].width = 20
# Additional code for Plotting the results Automatically
# Chart1 for Emissions vs Capacity
chart1 = ScatterChart()
chart1.title = "Min-Emissions"
chart1.style = 13
chart1.x_axis.title = name_of_x
chart1.y_axis.title = name_of_y1
if name_of_x == "Battery capacity in kwh":
xvalues = Reference(ws2, min_col=3, min_row=2, max_row=number_simulations + 2)
elif name_of_x == "TES capacity in m3":
xvalues = Reference(ws2, min_col=4, min_row=2, max_row=number_simulations + 2)
elif name_of_x == "Total Storage Capacity (kwh/m3)":
xvalues = Reference(ws2, min_col=5, min_row=2, max_row=number_simulations + 2)
values = Reference(ws2, min_col=6, min_row=1, max_row=number_simulations + 2)
series = Series(values, xvalues, title_from_data=True)
chart1.series.append(series)
ws2.add_chart(chart1, "Z7")
chart1.width = 15
chart1.height = 15
chart1.legend.position = 'b'
# Chart2 for Emissions vs Capacity
chart2 = ScatterChart()
chart2.title = "Costs"
chart2.style = 13
chart2.x_axis.title = name_of_x
chart2.y_axis.title = name_of_y2
if name_of_x == "Battery capacity in kwh":
xvalues = Reference(ws2, min_col=3, min_row=2, max_row=number_simulations + 2)
elif name_of_x == "TES capacity in m3":
xvalues = Reference(ws2, min_col=4, min_row=2, max_row=number_simulations + 2)
elif name_of_x == "Total Storage Capacity (kwh/m3)":
xvalues = Reference(ws2, min_col=5, min_row=2, max_row=number_simulations + 2)
values = Reference(ws2, min_col=11, min_row=1, max_row=number_simulations + 2)
series = Series(values, xvalues, title_from_data=True)
chart2.series.append(series)
ws2.add_chart(chart2, "AK7")
chart2.width = 15
chart2.height = 15
chart2.legend.position = 'b'
# Chart3 for Detailed Emissions vs Capacity
chart3 = ScatterChart()
chart3.title = "Detailed_Emissions"
chart3.style = 13
chart3.x_axis.title = name_of_x
chart3.y_axis.title = name_of_y1
if name_of_x == "Battery capacity in kwh":
xvalues = Reference(ws2, min_col=3, min_row=2, max_row=number_simulations + 2)
elif name_of_x == "TES capacity in m3":
xvalues = Reference(ws2, min_col=4, min_row=2, max_row=number_simulations + 2)
elif name_of_x == "Total Storage Capacity (kwh/m3)":
xvalues = Reference(ws2, min_col=5, min_row=2, max_row=number_simulations + 2)
for place_holder in (7, 8, 9, 10):
values = Reference(ws2, min_col=place_holder, min_row=1, max_row=number_simulations + 2)
series = Series(values, xvalues, title_from_data=True)
chart3.series.append(series)
ws2.add_chart(chart3, "Z37")
chart3.width = 15
chart3.height = 15
chart3.legend.position = 'b'
counter = number_simulations + 4
starting_column = 2
ws2.cell(row=counter, column=starting_column).value = 1
ws2.cell(row=number_simulations + 5, column=starting_column).value = 'Simulation #'
ws2.cell(row=number_simulations + 5, column=starting_column + 1).value = 'Device'
ws2.cell(row=number_simulations + 5, column=starting_column + 2).value = 'Factor'
ws2.cell(row=number_simulations + 5, column=starting_column + 3).value = 'Value'
for dev in ["Battery", "Battery_small", "Battery_large", "TES", "Boiler", "CHP", "PV", "HP", "EH", "STC"]:
ws2.cell(row=counter, column=starting_column + 1).value = dev
ws2.cell(row=counter, column=starting_column + 2).value = "x_" + dev
if dev in ("TES", "Boiler", "CHP"):
for i in (0, 1):
if dev == "TES" or dev == "Boiler":
if i == 0:
ws2.cell(row=counter, column=starting_column + 1).value = dev + "_" + str(i)
ws2.cell(row=counter, column=starting_column + 2).value = "x_" + dev + str(i)
ws2.cell(row=counter, column=starting_column + 3).value = decision_variables[dev][i]
counter += 1
else:
ws2.cell(row=counter, column=starting_column + 1).value = dev + "_Continuous"
ws2.cell(row=counter, column=starting_column + 2).value = "x_" + dev + "_Continuous"
ws2.cell(row=counter, column=starting_column + 3).value = decision_variables[dev][i]
ws2.cell(row=counter + 1, column=starting_column + 1).value = dev + "_Continuous"
ws2.cell(row=counter + 1,
column=starting_column + 2).value = "cap_design_" + dev + "_Continuous"
ws2.cell(row=counter + 1, column=starting_column + 3).value = capacities[dev + "_Conti"]
ws2.cell(row=counter + 2, column=starting_column + 1).value = dev + "Total"
ws2.cell(row=counter + 2, column=starting_column + 2).value = "cap_" + dev + "_Total"
ws2.cell(row=counter + 2, column=starting_column + 3).value = capacities[dev]
counter += 3
elif dev == "CHP":
ws2.cell(row=counter, column=starting_column + 1).value = dev + "_" + str(i)
ws2.cell(row=counter, column=starting_column + 2).value = "x_" + dev + str(i)
ws2.cell(row=counter, column=starting_column + 3).value = decision_variables[dev][i]
ws2.cell(row=counter + 1, column=starting_column + 1).value = dev
ws2.cell(row=counter + 1, column=starting_column + 2).value = "cap_design_" + dev
ws2.cell(row=counter + 1, column=starting_column + 3).value = capacities[dev + str(i)]
counter += 1
if i == 1:
ws2.cell(row=counter + 2, column=starting_column + 1).value = dev + "Total"
ws2.cell(row=counter + 2, column=starting_column + 2).value = "cap_" + dev + "_Total"
ws2.cell(row=counter + 2, column=starting_column + 3).value = capacities[dev]
counter += 1
else:
ws2.cell(row=counter, column=starting_column + 1).value = dev
ws2.cell(row=counter, column=starting_column + 2).value = "x_" + dev
ws2.cell(row=counter, column=starting_column + 3).value = decision_variables[dev]
ws2.cell(row=counter + 1, column=starting_column + 1).value = dev
ws2.cell(row=counter + 1, column=starting_column + 2).value = "cap_design_" + dev
ws2.cell(row=counter + 1, column=starting_column + 3).value = capacities[dev]
counter += 2
number_of_representative_periods = results_of_clustering["representative_days"]["T_e_raw"].shape[0]
length_of_cluster = results_of_clustering["representative_days"]["T_e_raw"].shape[1]
counter += 3
column = 4
ws2.cell(row=counter, column=3).value = 'Power Details for last Simulation'
for dev in ["HP", "CHP", "EH", "STC", "PV", "Import", "Battery_small", "Battery_large", "Battery", ]:
ws2.cell(row=counter, column=column).value = dev
rows = counter
if dev in ("Battery_small", "Battery_large", "Battery"):
for state in ("charge", "discharge"):
ws2.cell(row=rows, column=column).value = dev + "_" + state
d_multiplier = -1
for d in range(number_of_representative_periods):
d_multiplier += 1
for t in range(length_of_cluster):
if d == 0 and t == 0:
rows += 1
ws2.cell(row=(rows + (d_multiplier * length_of_cluster) + t), column=column).value = \
powers[dev]["total", state][d][t]
column += 1
rows = counter
ws2.cell(row=rows, column=column).value = "storage" + "_" + dev + "in %"
d_multiplier = -1
for d in range(number_of_representative_periods):
d_multiplier += 1
for t in range(length_of_cluster):
if d == 0 and t == 0:
rows += 1
ws2.cell(row=(rows + (d_multiplier * length_of_cluster) + t), column=column).value = (storage_Batt[
d][t] /
capacities[
dev]) * 100
column += 1
rows = counter
else:
d_multiplier = -1
for d in range(number_of_representative_periods):
d_multiplier += 1
for t in range(length_of_cluster):
if d == 0 and t == 0:
rows += 1
ws2.cell(row=(rows + (d_multiplier * length_of_cluster) + t), column=column).value = powers[dev][d][
t]
column += 1
if dev in ["PV", "CHP", ]:
for method in ("use", "sell"):
rows = counter
ws2.cell(row=counter, column=column).value = dev + "_" + method
d_multiplier = -1
for d in range(number_of_representative_periods):
d_multiplier += 1
for t in range(length_of_cluster):
if d == 0 and t == 0:
rows += 1
ws2.cell(row=(rows + (d_multiplier * length_of_cluster) + t), column=column).value = \
powers[dev, method][d][t]
column += 1
for i in range(1, (ws2.max_row + 1)): # ws2 has fewer rows than ws1 so it will work
ws2.row_dimensions[i].height = 15
for i in ("A", "B", "F", "G", "K", "L", "M", "N", "O", "P", "Q"):
ws2.column_dimensions[i].width = 15
for i in ("C", "D", "E", "H", "I", "J"):
ws2.column_dimensions[i].width = 25
name_of_output = name_of_file
current_date = datetime.datetime.now()
wb2.save(str(current_date.month) + "." + str(current_date.day) + "." + str(current_date.year) + " - " +
str(current_date.hour) + "_" + str(current_date.minute) + "_" + "_" + name_of_output + ".xlsx")
# Workbook to show the clustered outputs and the weights
wb3 = Workbook()
ws3 = wb3.create_sheet('Clustered_Results', 0)
list_of_data_groups = ['CO2(t)', 'dhw', 'electricity', 'sun_rad_0', 'sun_rad_1', 'sun_rad_2', 'sun_rad_3',
'sun_rad_4', 'sun_rad_5', 'T_e_raw']
ws3.cell(row=1, column=1).value = "Weights of the Data types"
ws3.cell(row=1, column=2).value = "Value of the Objective function"
ws3.cell(row=2, column=2).value = results_of_clustering["obj"]
for i in range(len(list_of_data_groups)):
ws3.cell(row=2 + i, column=1).value = list_of_data_groups[i] + "=" + str(
results_of_clustering["weights_of_input"][i])
count_columns = 2
nn = 0
for j in results_of_clustering["list_size_and_elements_of_cluster"]:
count_columns += 1
ws3.cell(row=1, column=count_columns).value = str("Cluster Number : " + str(nn))
ws3.cell(row=2, column=count_columns).value = str("Representative Element : " + str(j[0]))
ws3.cell(row=3, column=count_columns).value = str("Weight of Cluster : " + str(j[1][0]))
ws3.cell(row=4, column=count_columns).value = str("Elements in Cluster" + '_' + str(nn))
count_rows = 5
nn += 1
for i in j[1][1]:
ws3.cell(row=count_rows, column=count_columns).value = i
count_rows += 1
for ii in range(len(results_of_clustering["representative_days"])):
''' len of results_of_clustering gives the number of lists ...
1 List for each data type and each list has n elements ... 1 element for each of the n clusters '''
for jj in range(len(results_of_clustering["representative_days"][list_of_data_groups[0]])):
'''
len of results_of_clustering[0] gives the number of sub elements in each element
this number of sub elements corresponds to the number of clusters ...
each sub element is dedicated to the values of this element for a given cluster
each element represents a Data Group
'''
count_columns += 1
count_rows = 2
for zz in range(len(results_of_clustering["representative_days"][list_of_data_groups[0]][0])):
'''
len of results_of_clustering[0][0] gives the number of values within each sub element
these 24 values will be the hourly values of this representative day of cluster jj
of element ii of the group of data to be clustered
'''
ws3.cell(row=1, column=count_columns).value = str(list_of_data_groups[ii]) + '_' + str(jj)
ws3.cell(row=count_rows, column=count_columns).value = \
results_of_clustering["representative_days"][list_of_data_groups[ii]][jj][zz]
count_rows += 1
for i in range(1, (ws3.max_row + 1)): # ws2 has fewer rows than ws1 so it will work
ws3.row_dimensions[i].height = 15
for i in ("A", "B", "C", "D", "E", "F", "G", "H", "I", "J", "K", "L", "M", "N", "O", "P", "Q", "R", "S", "T", "U",
"V", "W", "X", "Y", "Z"):
ws3.column_dimensions[i].width = 25
current_date = datetime.datetime.now()
wb3.save(str(current_date.month) + "." + str(current_date.day) + "." + str(current_date.year) + " - " +
str(current_date.hour) + "_" + str(current_date.minute) + "_" + "_" + "Clustered Data 2017" + ".xlsx")
ws.cell(row=row + 2,
column=col,
value=pf.multidimensional_poly_func(aCh, n_vec_Ch,
[aoa_anal_rad[row], df]))
#set up charts
point = 12700
#set up CL chart
chart = ScatterChart()
chart.x_axis.title = ws.cell(row=1, column=9).value
chart.y_axis.title = 'CL'
chart.x_axis.scaling.min = aoa_min
chart.x_axis.scaling.max = aoa_max
chart.y_axis.crosses = "min"
chart.x_axis.crosses = "min"
chart.height = 15.
#raw data series
xvalues = Reference(ws, min_col=2, min_row=2, max_row=ws.max_row)
yvalues = Reference(ws, min_col=3, min_row=1, max_row=ws.max_row)
series = Series(yvalues, xvalues, title_from_data=True)
series.marker.symbol = 'circle'
series.marker.size = 5.
series.graphicalProperties.line.noFill = True
series.marker.graphicalProperties.noFill = True
series.marker.graphicalProperties.line.solidFill = '000000'
series.marker.graphicalProperties.line.width = point
chart.series.append(series)
#CL(aoa) series
xvalues = Reference(ws, min_col=9, min_row=2, max_row=N_plot + 1)
yvalues = Reference(ws, min_col=10, min_row=1, max_row=N_plot + 1)
series = Series(yvalues, xvalues, title_from_data=True)
def gen_workbook(input_file_or_dir, output_file):
if work_book_enabled == False:
print("python3-openpyxl not found")
return
wb = Workbook()
if os.path.isfile(input_file_or_dir):
files = [input_file_or_dir]
if os.path.isdir(input_file_or_dir):
files = glob.glob(os.path.join(input_file_or_dir, "*.dat"))
else:
return
ws = wb.active
pos = 1
for i in range(0, epitaxy_get_layers()):
dos_layer = epitaxy_get_dos_file(i)
if dos_layer.startswith("dos") == True:
pos = workbook_from_inp(ws,
pos,
dos_layer + ".inp",
title=epitaxy_get_name(i))
for my_file in files:
#print("about to save1",my_file)
#print(my_file)
data = dat_file()
if data.load(my_file, guess=False) == True:
x = []
y = []
z = []
if data.load(my_file) == True:
#print("read",my_file)
ws = wb.create_sheet(
title=title_truncate(os.path.basename(my_file)))
ws.cell(column=1, row=1, value=data.title)
ws.cell(column=1,
row=2,
value=data.x_label + " (" + data.x_units + ") ")
ws.cell(column=2,
row=2,
value=data.data_label + " (" + data.data_units + ") ")
for i in range(0, data.y_len):
ws.cell(column=1, row=i + 3, value=data.y_scale[i])
ws.cell(column=2, row=i + 3, value=data.data[0][0][i])
c1 = ScatterChart()
c1.title = data.title
c1.style = 13
c1.height = 20
c1.width = 20
c1.y_axis.title = data.data_label + " (" + data.data_units + ") "
c1.x_axis.title = data.x_label + " (" + data.x_units + ") "
xdata = Reference(ws,
min_col=1,
min_row=3,
max_row=3 + data.y_len)
ydata = Reference(ws,
min_col=2,
min_row=3,
max_row=3 + data.y_len)
series = Series(ydata, xdata, title_from_data=True)
c1.series.append(series)
ws.add_chart(c1, "G4")
#print("about to save1")
try:
wb.save(filename=output_file)
except:
return False
return True
min_row=3,
max_col=3,
max_row=dataLen + 2)
# Data series
drillCurveSeries = Series(values=drillCurveRef,
xvalues=penetrationRef,
title='Drill Curve')
feedCurveSeries = Series(values=feedCurveRef,
xvalues=penetrationRef,
title='Feed Curve')
# Chart formatting
chartObj = ScatterChart(scatterStyle='smoothMarker')
chartObj.title = 'Resistance Drill Results'
chartObj.height = 15
chartObj.width = 35
# Chart axis formatting
chartObj.x_axis.title = 'Penetration (mm)'
chartObj.y_axis.title = '% of Torque'
chartObj.x_axis.delete = False
chartObj.y_axis.delete = False
chartObj.x_axis.axPos = 'b' # Rotates the label to be horizontal
chartObj.x_axis.scaling.max = dataPrepped[dataLen - 1][0]
chartObj.x_axis.scaling.min = 0
# Add the data series and create the chart
chartObj.append(drillCurveSeries)
chartObj.append(feedCurveSeries)
sheet.add_chart(chartObj, 'D2')
def send_Email():
List = []
count = 0
f2 = open('dataLog.csv','rt')
fileData = csv.reader(f2)
for row in fileData:
List.append(row)
count += 1
print(row)
humidityList = []
temperatureList = []
lightList = [] # new
count2 = 0
for x in range(count) :
if(List[x][1] == 'humidity') :
humidityList.append(List[x])
count2 +=1
if(List[x][1] == 'temperature') :
temperatureList.append(List[x])
if(List[x][1] == 'light') : # new
lightList.append(List[x]) # new
# open Workbook
wb = Workbook()
ws = wb.active
# Format Values
for x in range(count2) :
humidityList[x][2] = int(humidityList[x][2])
xDT = datetime.datetime.strptime(humidityList[x][0],'%Y-%m-%d %H:%M:%S.%f')
humidityList[x][0] = xDT
ws.append([])
for x in range(count2) :
temperatureList[x][2] = float(temperatureList[x][2])
xDT = datetime.datetime.strptime(temperatureList[x][0],'%Y-%m-%d %H:%M:%S.%f')
temperatureList[x][0] = xDT
for x in range(count2) : #new start
lightList[x][2] = int(lightList[x][2])
xDT = datetime.datetime.strptime(lightList[x][0],'%Y-%m-%d %H:%M:%S.%f')
lightList[x][0] = xDT # new end
dTC = ws.cell(row = 1,column = 1,value='Date-Time')
sTC = ws.cell(row = 1,column = 2,value='Sensor Type')
vC = ws.cell(row = 1,column = 3,value='Value')
uC = ws.cell(row = 1,column = 4,value='Unit')
sC = ws.cell(row = 1,column = 5,value='Symbol')
dTC.font = Font(color=colors.BLUE, italic=False)
sTC.font = Font(color=colors.BLUE, italic=False)
vC.font = Font(color=colors.BLUE, italic=False)
uC.font = Font(color=colors.BLUE, italic=False)
sC.font = Font(color=colors.BLUE, italic=False)
# Insert ınto file
print("-------------------")
for x in range(count2) :
print(humidityList[x])
ws.append(humidityList[x])
ws.append([])
for x in range(count2) :
print(temperatureList[x])
ws.append(temperatureList[x])
ws.append([]) # new start
for x in range(count2) :
print(lightList[x])
ws.append(lightList[x]) # new end
# Fromat Column
ws.column_dimensions['A'].width = 20
ws.column_dimensions['B'].width = 12
# Set Chart
chart = ScatterChart()
#chart.title = "Temperature Chart"
chart.x_axis.title = 'Date-Time'
chart.y_axis.title = '*C'
# axis
tempMinRow = 2 + (count2) + 1
tempMaxRow = 1 + (count2*2) + 1
# x-axis
xvalues = Reference(ws,min_col=1,min_row=tempMinRow,max_row=tempMaxRow)
# y-axis
yvalues = Reference(ws,min_col=3,min_row=tempMinRow,max_row=tempMaxRow)
# Series
series = Series(yvalues,xvalues)
chart.series.append(series)
# Style
chart.style = 10
chart.height = 10
chart.width = 20
#Show axis
chart.x_axis.delete = False
chart.y_axis.delete = False
# Set position of chart
posTemp = (count2*3) + 2 + 4
posTemp2 = "A"+str(posTemp)
cTitle = ws.cell(row = posTemp-1,column = 1,value='Temperature Chart')
cTitle.font = Font(color=colors.BLUE, italic=False)
# Humidity Chart
chart2 = ScatterChart()
chart2.x_axis.title = 'Date-Time'
chart2.y_axis.title = '%'
humMinRow = 2
humMaxRow = 1 + (count2)
xvalues2 = Reference(ws,min_col=1,min_row=humMinRow,max_row=humMaxRow)
yvalues2 = Reference(ws,min_col=3,min_row=humMinRow,max_row=humMaxRow)
series2 = Series(yvalues2,xvalues2)
chart2.series.append(series2)
chart2.style = 10
chart2.height = 10
chart2.width = 20
chart2.x_axis.delete = False
chart2.y_axis.delete = False
posHum = (count2*3) + 2 + 4 + 22
posHum2 = "A"+str(posHum)
cTitle2 = ws.cell(row = posHum-1,column = 1,value='Humidity Chart')
cTitle2.font = Font(color=colors.BLUE, italic=False)
# Light Chart
chart3 = ScatterChart()
chart3.x_axis.title = 'Date-Time'
chart3.y_axis.title = 'nm'
lightMinRow = 2 + (count2)*2 + 2
lightMaxRow = 1 + (count2*3) + 2
xvalues3 = Reference(ws,min_col=1,min_row=lightMinRow,max_row=lightMaxRow)
yvalues3 = Reference(ws,min_col=3,min_row=lightMinRow,max_row=lightMaxRow)
series3 = Series(yvalues3,xvalues3)
chart3.series.append(series3)
chart3.style = 10
chart3.height = 10
chart3.width = 20
chart3.x_axis.delete = False
chart3.y_axis.delete = False
posLight = (count2*3) + 3 + 4 + 44
posLight2 = "A"+str(posLight)
cTitle3 = ws.cell(row = posLight-1,column = 1,value='Light Chart')
cTitle3.font = Font(color=colors.BLUE, italic=False)
# Add and finish
ws.add_chart(chart,posTemp2)
ws.add_chart(chart2,posHum2)
ws.add_chart(chart3,posLight2)
wb.save("Table.xlsx")
file = 'Table.xlsx'
msg = MIMEMultipart()
fp = open(file, 'rb')
part = MIMEBase('application','vnd.ms-excel')
part.set_payload(fp.read())
fp.close()
encoders.encode_base64(part)
part.add_header('Content-Disposition', 'attachment', filename = 'Table.xlsx')
msg.attach(part)
msg['Subject'] = 'Pot Project'
to = ''
gmail_user = ''
gmail_pwd = ''
smtpserver = smtplib.SMTP("smtp.gmail.com",587)
smtpserver.ehlo()
smtpserver.starttls()
smtpserver.ehlo() # extra characters to permit edit
smtpserver.login(gmail_user, gmail_pwd)
smtpserver.sendmail(gmail_user, to, msg.as_string())
smtpserver.quit()
print("Email Sent")
def main():
# コマンドライン引数取得
args = sys.argv
csv_file_path = args[1]
y_axis_columns = []
x_axis_column = X_AXIS_COLUMN
for count in range(2, len(args)):
y_axis_columns.append(args[count])
# 出力先ファイルパスの設定
current_time = datetime.now()
timestamp = current_time.strftime("%Y%m%d-%H%M%S")
split_csv_file_name = os.path.splitext(csv_file_path)
output_file_path = "excel_graphs/" + split_csv_file_name[
0] + "_" + timestamp + EXTENTION_OF_FILE
# ワークブック、ワークシートオブジェクトを作成
work_book = Workbook()
data_sheet = work_book.active
data_sheet.title = DATA_SHEET_NAME
chart_sheet = work_book.create_sheet(title=CHART_SHEET_NAME)
work_book.active = chart_sheet
# csvファイルの読み込み
convert_datas = []
with open(csv_file_path, encoding='utf-8') as csv_file:
csv_reader = csv.reader(csv_file, delimiter=',', quotechar='"')
for line in csv_reader:
convert_datas = convert_type(line)
data_sheet.append(convert_datas)
csv_columns = len(line)
# CSVファイルの列数分、列番号をリストに追加
if y_axis_columns == []:
y_axis_columns = range(x_axis_column + 1, csv_columns + 1)
# グラフの書式設定
chart = ScatterChart()
chart.title = CHART_TITLE
chart.y_axis.title = Y_AXIS_TITLE
chart.x_axis.title = X_AXIS_TITLE
chart.y_axis.scaling.min = Y_AXIS_MIN
chart.y_axis.scaling.max = Y_AXIS_MAX
chart.x_axis.scaling.min = X_AXIS_MIN
chart.x_axis.scaling.max = X_AXIS_MAX
# chart.legend.position = LEGEND_POSITION
chart.height = CHART_HEIGHT
chart.width = CHART_WIDTH
# X軸の参照先の設定
line_number = csv_reader.line_num
x_axis_values = Reference(data_sheet,
min_col=x_axis_column,
min_row=BEGIN_DATA_ROW + 1,
max_row=line_number)
# Y軸の参照先の設定
for y_axis_column in y_axis_columns:
y_axis_values = Reference(data_sheet,
min_col=y_axis_column,
min_row=BEGIN_DATA_ROW,
max_row=line_number)
series = Series(y_axis_values, x_axis_values, title_from_data=True)
chart.series.append(series)
# グラフの挿入
chart_sheet.add_chart(chart, RANGE_TO_ADD_CHART)
work_book.save(output_file_path)
print("Result file is [%s]" % output_file_path)
def _chart2excel(writer, sheet, charts):
try:
add_chart = writer.book.add_chart
m, h, w = 3, 300, 512
for i, (k, v) in enumerate(sorted(charts.items())):
chart = add_chart({'type': 'scatter', 'subtype': 'straight'})
for s in v['series']:
chart.add_series({
'name': s['label'],
'categories': _data_ref(s['x']),
'values': _data_ref(s['y']),
})
chart.set_size({'width': w, 'height': h})
for s, o in v['set'].items():
eval('chart.set_%s(o)' % s)
n = int(i / m)
j = i - n * m
sheet.insert_chart('A1', chart, {
'x_offset': w * n,
'y_offset': h * j
})
except AttributeError:
from openpyxl.chart import ScatterChart, Series
from xlrd import colname as xl_colname
sn = writer.book.get_sheet_names()
named_ranges = {
'%s!%s' % (sn[d.localSheetId], d.name): d.value
for d in writer.book.defined_names.definedName
}
m, h, w = 3, 7.94, 13.55
for i, (k, v) in enumerate(sorted(charts.items())):
chart = ScatterChart()
chart.height = h
chart.width = w
_map = {
('title', 'name'): ('title', ),
('y_axis', 'name'): ('y_axis', 'title'),
('x_axis', 'name'): ('x_axis', 'title'),
}
_filter = {
('legend', 'position'): lambda x: x[0],
}
it = {
s: _filter[s](o) if s in _filter else o
for s, o in dsp_utl.stack_nested_keys(v['set'])
}
for s, o in dsp_utl.map_dict(_map, it).items():
c = chart
for j in s[:-1]:
c = getattr(c, j)
setattr(c, s[-1], o)
for s in v['series']:
xvalues = named_ranges[_data_ref(s['x'])]
values = named_ranges[_data_ref(s['y'])]
series = Series(values, xvalues, title=s['label'])
chart.series.append(series)
n = int(i / m)
j = i - n * m
sheet.add_chart(chart, '%s%d' % (xl_colname(8 * n), 1 + 15 * j))
def run_multi_obj(name_of_output, number_simulations, enev_restrictions=True, pv_scenario=False,
status_quo_with_storage=False, folder="results"):
# Filename definitions
if pv_scenario:
tag = "pv"
filename_start_values = "start_values_pv.csv"
else:
if enev_restrictions:
tag = "enev_restrictions"
filename_start_values = "start_values_enev.csv"
else:
tag = "no_restrictions"
filename_start_values = "start_values_without_enev.csv"
# Compute limits (min costs, min emissions)
print(
'################' + '\n' + '\n' + 'Running the First optimization (1.optimize) to compute minimum cost and maximum emissions')
emissions_max = 1000 # ton CO2 per year
# Minimize costs
filename_min_costs = folder + "/" + tag + str(0) + ".pkl"
options = {"filename_results": filename_min_costs,
"enev_restrictions": enev_restrictions,
"pv_scenario": pv_scenario,
"status_quo_with_storage": status_quo_with_storage,
"opt_costs": True,
"store_start_vals": False,
"load_start_vals": False,
"filename_start_vals": filename_start_values,
"envelope_runs": False}
(min_costs1, max_emissions1, emi_gas1, emi_pv1, emi_grid1, emi_lca1, decision_variables_1, capacities_1, powers_1,
storage_Batt_1, clustered_data) = opti_model.optimize_MA(emissions_max, options)
results_of_first = (
min_costs1, max_emissions1, emi_gas1, emi_pv1, emi_grid1, emi_lca1, decision_variables_1, capacities_1,
powers_1,
storage_Batt_1)
# Minimize emissions (lexicographic optimization)
print(
'################' + '\n' + '\n' + 'Running the Second optimization (2.optimize) to compute minimum emissions and maximum costs')
filename_min_emissions = folder + "/" + tag + str(number_simulations + 1) + ".pkl"
options["opt_costs"] = False
options["store_start_vals"] = True
options["filename_results"] = filename_min_emissions
(max_costs2, min_emissions2, emi_gas2, emi_pv2, emi_grid2, emi_lca2, decision_variables_2, capacities_2, powers_2,
storage_Batt_2, clustered_data) = opti_model.optimize_MA(emissions_max, options)
results_of_second = (
max_costs2, min_emissions2, emi_gas2, emi_pv2, emi_grid2, emi_lca2, decision_variables_2, capacities_2,
powers_2,
storage_Batt_2)
# Second optimization to minimize the costs at minimal emissions
# print('################' + '\n' + '\n' +'Running the Third optimization (3.optimize) to compute minimum emissions and minimum costs')
# options["opt_costs"] = True
# options["store_start_vals"] = True
# options["load_start_vals"] = True
# options["filename_results"] = filename_min_emissions
# (max_costs3, min_emissions3, emi_gas3, emi_pv3, emi_grid3, emi_lca3, decision_variables_3, capacities_3, powers_3, storage_Batt_3 ) = opti_model.optimize(min_emissions, options)
"""The second optimization is commented out because for some simulations it gives a problem since
minimizing the costs for the set emissions is not possible"""
# Run multiple simulations
print(
'################' + '\n' + '\n' + 'Running the Fourth optimization (4.optimize) to compute the multiple simulations of possible solutions and get the pareto front')
options["opt_costs"] = True
options["store_start_vals"] = False
options["load_start_vals"] = True
prev_emissions = max_emissions1
results_of_fourth = {}
for i in range(1, 1 + number_simulations):
# Emissions limit is the minimum of:
# 1. linear interpolation between max_emissions and min_emissions
# 2. previous iteration's emissions * (1-eps)
limit_emissions = min(max_emissions1 - (max_emissions1 - min_emissions2) * i / (number_simulations + 1),
prev_emissions * 0.999)
print("################")
print("################")
print(str(1 + number_simulations - i) + " Simulations left")
print("################")
print("################")
options["filename_results"] = folder + "/" + tag + str(i) + ".pkl"
(costs, prev_emissions, emi_gas4, emi_pv4, emi_grid4, emi_lca4, decision_variables_4, capacities_4, powers_4,
storage_Batt_4, clustered_data) \
= opti_model.optimize_MA(limit_emissions, options)
results_of_fourth[i] = (costs, prev_emissions, emi_gas4, emi_pv4, emi_grid4, emi_lca4, decision_variables_4,
capacities_4, powers_4, storage_Batt_4)
####
# Plotting the results
print('Creating Output Workbook...')
wb2 = Workbook()
ws2 = wb2.create_sheet('Results', 0)
ws2.cell(row=1, column=2).value = 'Simulation #'
ws2.cell(row=1, column=3).value = 'Costs'
ws2.cell(row=1, column=4).value = 'Emissions'
ws2.cell(row=1, column=5).value = 'Emi-gas'
ws2.cell(row=1, column=6).value = 'Emi-pv'
ws2.cell(row=1, column=7).value = 'Emi-grid'
ws2.cell(row=1, column=8).value = 'Emi-lca'
counter = 2
for all in [results_of_first, results_of_fourth, results_of_second]:
if all == results_of_fourth:
for ii in range(1, 1 + number_simulations):
ws2.cell(row=counter, column=2).value = ii + 2
ws2.cell(row=counter, column=3).value = all[ii][0]
ws2.cell(row=counter, column=4).value = all[ii][1]
ws2.cell(row=counter, column=5).value = all[ii][2]
ws2.cell(row=counter, column=6).value = all[ii][3]
ws2.cell(row=counter, column=7).value = all[ii][4]
ws2.cell(row=counter, column=8).value = all[ii][5]
counter += 1
else:
if all == results_of_first:
ws2.cell(row=counter, column=2).value = 1
elif all == results_of_second:
ws2.cell(row=counter, column=2).value = 2
ws2.cell(row=counter, column=3).value = all[0]
ws2.cell(row=counter, column=4).value = all[1]
ws2.cell(row=counter, column=5).value = all[2]
ws2.cell(row=counter, column=6).value = all[3]
ws2.cell(row=counter, column=7).value = all[4]
ws2.cell(row=counter, column=8).value = all[5]
counter += 1
chart1 = ScatterChart()
chart1.title = "Min-Emissions"
chart1.style = 13
chart1.x_axis.title = 'Costs in 1000 Euro per year'
chart1.y_axis.title = 'Emissions in t CO2 per year'
xvalues = Reference(ws2, min_col=3, min_row=2, max_row=number_simulations + 3)
values = Reference(ws2, min_col=4, min_row=1, max_row=number_simulations + 3)
series = Series(values, xvalues, title_from_data=True)
chart1.series.append(series)
ws2.add_chart(chart1, "M2")
chart1.width = 15
chart1.height = 15
chart1.legend.position = 'b'
####
# Device specific results
# for all in [results_of_first, results_of_fourth, results_of_second]:
for simulation_results in [results_of_first, results_of_second]:
counter = number_simulations + 6
if simulation_results == results_of_first:
starting_column = 2
ws2.cell(row=counter, column=starting_column).value = 1
else:
starting_column = 7
ws2.cell(row=counter, column=starting_column).value = 2
ws2.cell(row=number_simulations + 5, column=starting_column).value = 'Simulation #'
ws2.cell(row=number_simulations + 5, column=starting_column + 1).value = 'Device'
ws2.cell(row=number_simulations + 5, column=starting_column + 2).value = 'Factor'
ws2.cell(row=number_simulations + 5, column=starting_column + 3).value = 'Value'
for dev in ["Battery", "Battery_small", "Battery_large", "TES", "Boiler", "CHP", "PV", "HP", "EH", "STC"]:
ws2.cell(row=counter, column=starting_column + 1).value = dev
ws2.cell(row=counter, column=starting_column + 2).value = "x_" + dev
if dev in ("TES", "Boiler", "CHP"):
for i in (0, 1):
if dev == "TES" or dev == "Boiler":
if i == 0:
ws2.cell(row=counter, column=starting_column + 1).value = dev + "_" + str(i)
ws2.cell(row=counter, column=starting_column + 2).value = "x_" + dev + str(i)
ws2.cell(row=counter, column=starting_column + 3).value = simulation_results[6][dev][i]
counter += 1
else:
ws2.cell(row=counter, column=starting_column + 1).value = dev + "_Continuous"
ws2.cell(row=counter, column=starting_column + 2).value = "x_" + dev + "_Continuous"
ws2.cell(row=counter, column=starting_column + 3).value = simulation_results[6][dev][i]
ws2.cell(row=counter + 1, column=starting_column + 1).value = dev + "_Continuous"
ws2.cell(row=counter + 1,
column=starting_column + 2).value = "cap_design_" + dev + "_Continuous"
ws2.cell(row=counter + 1, column=starting_column + 3).value = simulation_results[7][
dev + "_Conti"]
ws2.cell(row=counter + 2, column=starting_column + 1).value = dev + "Total"
ws2.cell(row=counter + 2, column=starting_column + 2).value = "cap_" + dev + "_Total"
ws2.cell(row=counter + 2, column=starting_column + 3).value = simulation_results[7][dev]
counter += 3
elif dev == "CHP":
ws2.cell(row=counter, column=starting_column + 1).value = dev + "_" + str(i)
ws2.cell(row=counter, column=starting_column + 2).value = "x_" + dev + str(i)
ws2.cell(row=counter, column=starting_column + 3).value = simulation_results[6][dev][i]
ws2.cell(row=counter + 1, column=starting_column + 1).value = dev
ws2.cell(row=counter + 1, column=starting_column + 2).value = "cap_design_" + dev
ws2.cell(row=counter + 1, column=starting_column + 3).value = simulation_results[7][
dev + str(i)]
counter += 1
if i == 1:
ws2.cell(row=counter + 2, column=starting_column + 1).value = dev + "Total"
ws2.cell(row=counter + 2, column=starting_column + 2).value = "cap_" + dev + "_Total"
ws2.cell(row=counter + 2, column=starting_column + 3).value = simulation_results[7][dev]
counter += 1
else:
ws2.cell(row=counter, column=starting_column + 1).value = dev
ws2.cell(row=counter, column=starting_column + 2).value = "x_" + dev
ws2.cell(row=counter, column=starting_column + 3).value = simulation_results[6][dev]
ws2.cell(row=counter + 1, column=starting_column + 1).value = dev
ws2.cell(row=counter + 1, column=starting_column + 2).value = "cap_design_" + dev
ws2.cell(row=counter + 1, column=starting_column + 3).value = simulation_results[7][dev]
counter += 2
counter += 3
column = 4
ws2.cell(row=counter, column=3).value = 'Power Details for the 2nd Simulation'
for dev in ["HP", "CHP", "EH", "STC", "PV", "Import", "Battery", "Battery_small", "Battery_large"]:
ws2.cell(row=counter, column=column).value = dev
rows = counter
if dev == "Battery" or dev == "Battery_small" or dev == "Battery_large":
for state in ("charge", "discharge"):
ws2.cell(row=rows, column=column).value = dev + "_" + state
d_multiplier = -1
for d in range(len(results_of_first[8][dev]["total", state])):
d_multiplier += 1
for t in range(len(results_of_first[8][dev]["total", state][d])):
if d == 0 and t == 0:
rows += 1
ws2.cell(row=(rows + (d_multiplier * len(results_of_first[8][dev]["total", state][d])) + t),
column=column).value = results_of_first[8][dev]["total", state][d][t]
ws2.cell(row=(rows + (d_multiplier * len(results_of_first[8][dev]["total", state][d])) + t),
column=3).value = (str(d) + "_" + str(t))
column += 1
rows = counter
else:
d_multiplier = -1
for d in range(len(results_of_first[8][dev])):
d_multiplier += 1
for t in range(len(results_of_first[8][dev][d])):
if d == 0 and t == 0:
rows += 1
ws2.cell(row=(rows + (d_multiplier * len(results_of_first[8][dev][d])) + t), column=column).value = \
results_of_first[8][dev][d][t]
column += 1
if dev in ["PV", "CHP", ]:
for method in ("use", "sell"):
rows = counter
ws2.cell(row=counter, column=column).value = dev + "_" + method
d_multiplier = -1
for d in range(len(results_of_first[8][dev, method])):
d_multiplier += 1
for t in range(len(results_of_first[8][dev, method][d])):
if d == 0 and t == 0:
rows += 1
ws2.cell(row=(rows + (d_multiplier * len(results_of_first[8][dev, method][d])) + t),
column=column).value = \
results_of_first[8][dev, method][d][t]
column += 1
column += 2
ws2.cell(row=counter, column=21).value = 'Power Details for the 1st Simulation'
for dev in ["HP", "CHP", "EH", "STC", "PV", "Import", "Battery", "Battery_small", "Battery_large"]:
ws2.cell(row=counter, column=column).value = dev
rows = counter
if dev == "Battery" or dev == "Battery_small" or dev == "Battery_large":
for state in ("charge", "discharge"):
ws2.cell(row=rows, column=column).value = dev + "_" + state
d_multiplier = -1
for d in range(len(results_of_second[8][dev]["total", state])):
d_multiplier += 1
for t in range(len(results_of_second[8][dev]["total", state][d])):
if d == 0 and t == 0:
rows += 1
ws2.cell(row=(rows + (d_multiplier * len(results_of_second[8][dev]["total", state][d])) + t),
column=column).value = results_of_second[8][dev]["total", state][d][t]
ws2.cell(row=(rows + (d_multiplier * len(results_of_second[8][dev]["total", state][d])) + t),
column=3).value = (str(d) + "_" + str(t))
column += 1
rows = counter
else:
d_multiplier = -1
for d in range(len(results_of_second[8][dev])):
d_multiplier += 1
for t in range(len(results_of_second[8][dev][d])):
if d == 0 and t == 0:
rows += 1
ws2.cell(row=(rows + (d_multiplier * len(results_of_second[8][dev][d])) + t), column=column).value = \
results_of_second[8][dev][d][t]
column += 1
if dev in ["PV", "CHP", ]:
for method in ("use", "sell"):
rows = counter
ws2.cell(row=counter, column=column).value = dev + "_" + method
d_multiplier = -1
for d in range(len(results_of_second[8][dev, method])):
d_multiplier += 1
for t in range(len(results_of_second[8][dev, method][d])):
if d == 0 and t == 0:
rows += 1
ws2.cell(row=(rows + (d_multiplier * len(results_of_second[8][dev, method][d])) + t),
column=column).value = \
results_of_second[8][dev, method][d][t]
column += 1
for i in range(1, (ws2.max_row + 1)): # ws2 has fewer rows than ws1 so it will work
ws2.row_dimensions[i].height = 15
for i in ("A", "B", "F", "G", "K", "L", "M", "N", "O", "P", "Q"):
ws2.column_dimensions[i].width = 15
for i in ("C", "D", "E", "H", "I", "J"):
ws2.column_dimensions[i].width = 25
name_of_output = name_of_output
current_date = datetime.datetime.now()
wb2.save(str(current_date.month) + "." + str(current_date.day) + "." + str(current_date.year) + " - " +
str(current_date.hour) + "_" + str(current_date.minute) + "_" + "_" + name_of_output + ".xlsx")
def create_compression_chart(filepath_list, sub_sample_dict, data,
chart_filepath, name):
# Extract the data from the native files
for file in filepath_list:
print(f'Started processing file "{file.name}..."')
# Get the sample's other data
sample_id = get_id_from_filepath(file)
sample_dict = sub_sample_dict[sample_id]
area = sample_dict["area"]
length = sample_dict["length"]
# Prep the dictionary to store the data
data[sample_id] = []
# Open the workbook
workbook = openpyxl.load_workbook(file)
sheet = workbook.active
last_row = sheet.max_row
# Collect all the stress/strain data from the file
for i in range(2, last_row):
# Extract the raw data
load = float(sheet['M' + str(i)].value)
extension = float(sheet['K' + str(i)].value)
# Calculate the stress and strain values
stress = calc_stress(load, area)
strain = calc_strain(extension, length)
# Add the data to the master file
data[sample_id].append([stress, strain])
print(f'Finished processing file "{file.name}."')
# Step 6: Add the calculated data into the new workbook
workbook = openpyxl.load_workbook(chart_filepath)
sheet = workbook.active
# Chart formatting
chart = ScatterChart(scatterStyle='smoothMarker')
chart.x_axis.axPos = 'b' # Rotates the label to be horizontal
chart.title = f'{name} Samples Stress/Strain Curve'
chart.height = 17
chart.width = 34
chart.legend = None
# Chart axis formatting
chart.x_axis.title = 'Strain (mm)'
chart.y_axis.title = 'Stress (MPa)'
for key, values in (sorted(data.items())):
print(f'Started writing data for sample_id {key}...')
# Find the next available columns and rows to add the data to
last_col = sheet.max_column
if last_col == 1:
last_col -= 1
stress_col = last_col + 1
strain_col = last_col + 2
start_row = 2
# Add the headers for this key's data
sheet.cell(row=1, column=stress_col).value = f'ID({key})-Stress'
sheet.cell(row=1, column=strain_col).value = f'ID({key})-Strain'
# Add the stress/strain data in for the key
for i in range(len(values)):
sheet.cell(row=start_row + i,
column=stress_col).value = values[i][0]
sheet.cell(row=start_row + i,
column=strain_col).value = values[i][1]
print(f'Finished writing data for sample_id {key}.')
# Create a Series for the Chart with the new data
stress_reference = Reference(sheet,
min_col=stress_col,
max_col=stress_col,
min_row=2,
max_row=len(values))
strain_reference = Reference(sheet,
min_col=strain_col,
max_col=strain_col,
min_row=2,
max_row=len(values))
series = Series(values=stress_reference, xvalues=strain_reference)
chart.append(series)
sheet.add_chart(chart, 'A1')
workbook.save(chart_filepath)
print(f'Finished creating Chart for {name} data.')
