Esempio n. 1
0
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)
Esempio n. 2
0
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
Esempio n. 5
0
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
Esempio n. 6
0
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
Esempio n. 7
0
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)
Esempio n. 8
0
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
Esempio n. 9
0
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")
Esempio n. 10
0
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)
Esempio n. 11
0
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
Esempio n. 12
0
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))
Esempio n. 13
0
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
Esempio n. 14
0
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}")
Esempio n. 16
0
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)])
Esempio n. 17
0
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)
Esempio n. 18
0
    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中
Esempio n. 19
0
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
Esempio n. 20
0
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
Esempio n. 23
0
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)
Esempio n. 25
0
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')
Esempio n. 27
0
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)
Esempio n. 29
0
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))
Esempio n. 30
0
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.')