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_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 drawScatterChart(self, fileName, sheetName, saveFileName = None): if saveFileName is None: saveFileName = fileName wb = load_workbook(fileName) ws = wb['gegevens'] chart = ScatterChart() chart.title = "Scatter Chart" chart.style = 13 chart.x_axis.scaling.min = 19 chart.x_axis.scaling.max = 31 chart.y_axis.scaling.min = 110 chart.y_axis.scaling.max = 140 chart.x_axis.title = 'gewicht' chart.y_axis.title = 'lengte' chart.legend = None xvalues = Reference(ws, min_col=6, min_row=2, max_row=101) values = Reference(ws, min_col=7, min_row=2, max_row=101) #fill x and y, skip first x=[] y=[] iterrows = iter(ws.rows) next(iterrows) for row in iterrows: x.append(row[5].value) y.append(row[6].value) series = Series(values, xvalues) series.graphicalProperties.line.noFill = True series.marker = marker.Marker('circle', 5.2) chart.series.append(series) # Style the lines s1 = chart.series[0] s1.marker.symbol = "circle" s1.marker.graphicalProperties.solidFill = "4076A9" # Marker filling s1.marker.graphicalProperties.line.solidFill = "4076A9" # Marker outline s1.graphicalProperties.line.noFill = True ws = wb[sheetName] ws.add_chart(chart, "L7") wb.save(saveFileName) area = np.pi * 20 plt.scatter(x, y, s=area, alpha=1) plt.xlabel("gewicht") plt.ylabel("lengte") plt.grid(True,alpha=0.5) plt.axis([19,31,110,140]) plt.show()
def createEXCEL(fileName, dic): if fileName is None or dic is None: print("Error occurred") return headers = ['x', 'y'] wb = Workbook() ws = wb.active # Create table headers ws.append(headers) # Create ordered table for k, v in sorted(dic.items(), key=operator.itemgetter(0)): ws.append([k, v]) # Center all cels for col in ws.columns: for cell in col: cell.alignment = Alignment(horizontal="center") # Border + background for headers thin_border = Border(left=Side(style='thin'), right=Side(style='thin'), top=Side(style='thin'), bottom=Side(style='thin')) for i in range(len(dic) + 1): for j in range(len(headers)): ws.cell(row=i + 1, column=j + 1).border = thin_border if i == 0: ws.cell(1, j + 1).fill = PatternFill(start_color="FFC7CE", end_color="FFC7CE", fill_type="solid") # Create graph chart = ScatterChart() chart.title = "LineChart" chart.style = 13 chart.x_axis.title = 'X' chart.y_axis.title = 'Y' chart.legend = None x = Reference(ws, min_col=1, min_row=2, max_col=1, max_row=(len(dic) + 1)) y = Reference(ws, min_col=2, min_row=2, max_col=2, max_row=(len(dic) + 1)) s = Series(y, xvalues=x) chart.append(s) ws.add_chart(chart, "E4") wb.save(fileName + ".xlsx")
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 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 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 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 save_excel(self, filename): sheet = self.wb.sheetnames # Now delete all output worksheets above #3, i.e. keep only 3 poages!!! for i in range(3, len(sheet)): self.wb.remove(wb[sheet[i]]) N = self.size ws_eig = self.wb.create_sheet() ws_eig.title = "Sticks" ws_eig[sts(0, 0)].value = "Eval" ws_eig[sts(1, 0)].value = "Abs" ws_eig[sts(2, 0)].value = "Rot" ws_eig[sts(3, 0)].value = "Evector" for i in range(0, N): ws_eig[sts(0, i + 1)].value = self.eval[i] ws_eig[sts(1, i + 1)].value = self.stickA[i] ws_eig[sts(2, i + 1)].value = self.stickCD[i] for j in range(0, N): ws_eig[sts(3 + j, i + 1)].value = self.evec[j, i] ws_spec = self.wb.create_sheet() ws_spec.title = "Spectra" for i in range(0, np.size(self.x)): ws_spec[sts(0, i)].value = self.x[i] ws_spec[sts(1, i)].value = self.abs[i] ws_spec[sts(2, i)].value = self.CD[i] # add chart c1 = ScatterChart() c1.title = "" c1.x_axis.title = "wavenumber" c1.y_axis.title = "Absorbance" c1.style = 13 xvalues = Reference(ws_spec, min_col=1, min_row=1, max_row=len(self.x)) values = Reference(ws_spec, min_col=2, max_col=2, min_row=1, max_row=len(self.x)) series = Series(values, xvalues) series.graphicalProperties.line.solidFill = "FF0000" series.graphicalProperties.line.width = 3 c1.series.append(series) c1.x_axis.scaling.min = self.x[0] c1.x_axis.scaling.max = self.x[len(self.x) - 1] c1.x_axis.tickLblPos = "low" c1.y_axis.tickLblPos = "low" c1.legend = None ws_spec.add_chart(c1, "D2") c2 = ScatterChart() c2.title = "" c2.x_axis.title = "wavenumber" c2.y_axis.title = "CD" c2.style = 13 # xvalues = Reference(ws_spec, min_col=1, min_row=1, max_row=len(self.x)) values = Reference(ws_spec, min_col=3, max_col=3, min_row=1, max_row=len(self.x)) series = Series(values, xvalues) series.graphicalProperties.line.solidFill = "0000FF" series.graphicalProperties.line.width = 3 c2.series.append(series) c2.x_axis.scaling.min = self.x[0] c2.x_axis.scaling.max = self.x[len(self.x) - 1] c2.x_axis.tickLblPos = "low" c2.y_axis.tickLblPos = "low" c2.legend = None ws_spec.add_chart(c2, "D17") self.wb.save(filename) return
workspace[l2] = "=pmu!" + get_column_letter(L2) + str(row) workspace[l3] = "=pmu!" + get_column_letter(L3) + str(row) workspace[l4] = "=pmu!" + get_column_letter(L4) + str(row) # chart chart = ScatterChart() chart.title = 'Cache Impact' + cpu chart.x_axis.title = 'Time' xvalues = Reference(workspace, min_col=1, min_row=2, max_row=row_num + 1) values = Reference(workspace, min_col=column, min_row=2, max_row=row_num + 1) series = Series(values, xvalues) chart.series.append(series) chart.legend = None workspace.add_chart(chart, w5) chart = ScatterChart() chart.title = 'CPI' + cpu chart.x_axis.title = 'Time' xvalues = Reference(workspace, min_col=1, min_row=2, max_row=row_num + 1) values = Reference(workspace, min_col=column + 2, min_row=2, max_row=row_num + 1) series = Series(values, xvalues) chart.series.append(series) chart.legend = None workspace.add_chart(chart, w21)
Series, ) import math wb = Workbook() ws = wb.active ws.append(['X', 'Gaussian']) for i, x in enumerate(range(-10, 11)): ws.append([x, "=EXP(-(($A${row}/6)^2))".format(row=i + 2)]) chart1 = ScatterChart() chart1.title = "No Scaling" chart1.x_axis.title = 'x' chart1.y_axis.title = 'y' chart1.legend = None chart2 = ScatterChart() chart2.title = "X Log Scale" chart2.x_axis.title = 'x (log10)' chart2.y_axis.title = 'y' chart2.legend = None chart2.x_axis.scaling.logBase = 10 chart3 = ScatterChart() chart3.title = "Y Log Scale" chart3.x_axis.title = 'x' chart3.y_axis.title = 'y (log10)' chart3.legend = None chart3.y_axis.scaling.logBase = 10
Series, ) wb = Workbook() ws = wb.active ws.append(['X', '1/X']) for x in range(-10, 11): if x: ws.append([x, 1.0 / x]) chart1 = ScatterChart() chart1.title = "Full Axes" chart1.x_axis.title = 'x' chart1.y_axis.title = '1/x' chart1.legend = None chart2 = ScatterChart() chart2.title = "Clipped Axes" chart2.x_axis.title = 'x' chart2.y_axis.title = '1/x' chart2.legend = None chart2.x_axis.scaling.min = 0 chart2.y_axis.scaling.min = 0 chart2.x_axis.scaling.max = 11 chart2.y_axis.scaling.max = 1.5 x = Reference(ws, min_col=1, min_row=2, max_row=22) y = Reference(ws, min_col=2, min_row=2, max_row=22) s = Series(y, xvalues=x)
datax = Reference(sheet1, min_col=4, min_row=2, max_col=4, max_row=11) categs = Reference(sheet1, min_col=2, min_row=2, max_row=11) chart1 = BarChart() chart1.add_data(datax) chart1.set_categories(categs) chart1.legend = None # 범례 chart1.varyColors = True chart1.title = "상위 10위 좋아요수" sheet3.add_chart(chart1, "A8") # Trythis 3-2번 chart2 = ScatterChart() chart2.style = 13 datax = Reference(sheet1, min_col=1, min_row=2, max_row=11) value = Reference(sheet1, min_col=5, min_row=1, max_row=11) series = Series(value, datax, title_from_data=True) chart2.series.append(series) categs = Reference(sheet1, min_col=2, min_row=2, max_row=11) # chart1.add_data(datax) chart2.set_categories(categs) chart2.legend = None # 범례 chart2.varyColors = True chart2.title = "상위 10위 좋아요차이수" sheet3.add_chart(chart2, "K8") book.save("./data/meltop100.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.')
Series, ) import math wb = Workbook() ws = wb.active ws.append(['X', 'Gaussian']) for i, x in enumerate(range(-10, 11)): ws.append([x, "=EXP(-(($A${row}/6)^2))".format(row = i + 2)]) chart1 = ScatterChart() chart1.title = "No Scaling" chart1.x_axis.title = 'x' chart1.y_axis.title = 'y' chart1.legend = None chart2 = ScatterChart() chart2.title = "X Log Scale" chart2.x_axis.title = 'x (log10)' chart2.y_axis.title = 'y' chart2.legend = None chart2.x_axis.scaling.logBase = 10 chart3 = ScatterChart() chart3.title = "Y Log Scale" chart3.x_axis.title = 'x' chart3.y_axis.title = 'y (log10)' chart3.legend = None chart3.y_axis.scaling.logBase = 10
def create_chart_selected(self, output_sheet_name, selector_csv_path): output_ws = self.xl.wb.create_sheet(output_sheet_name) col_range, rows_selected = self.load_selector(selector_csv_path) min_col, max_col = col_range xvalues = Reference(self.data_sheet, min_col=min_col, min_row=self.label_row, max_col=max_col) # 4 cells CHART_WIDTH_CELL = 4 CHART_WIDTH = 1.9 * CHART_WIDTH_CELL CHART_HEIGHT = 1.9 * CHART_WIDTH_CELL * 3/4 CHART_HEIGHT_CELL = int(CHART_HEIGHT/0.5)+2 CHART_POSITION_ROW_INIT = 2 CHART_POSITION_COL_INIT = 2 CHART_REPEAT = 8 CHART_X_AXIS_TITLE = "X" CHART_Y_AXIS_TITLE = "Y" row = CHART_POSITION_ROW_INIT col = CHART_POSITION_COL_INIT r = 0 for rows in rows_selected: chart = ScatterChart() id_list = [] for i in rows: min_row = i max_row = i # ID ID_COL_RANGE = [1, 4] id0_col_letter = get_column_letter(ID_COL_RANGE[0]) id1_col_letter = get_column_letter(ID_COL_RANGE[1]) ids = [j.value for j in self.data_sheet[id0_col_letter + str(min_row): id1_col_letter + str(min_row)][0]] if len(ids) > 1: id_title = '-'.join([str(j) for j in ids]) elif len(ids) == 1: id_title = ids[0] else: id_title = 'none' id_list.append(id_title) values = Reference(self.data_sheet, min_col=min_col, min_row=min_row, max_col=max_col, max_row=max_row) # 系列名 series_title = self.data_sheet[id0_col_letter + str(min_row)].value series = Series(values, xvalues, title=series_title) chart.series.append(series) title_cell = get_column_letter(col) + str(row - 1) if len(id_list) > 1: chart_title = ', '.join([str(i) for i in id_list]) elif len(id_list) == 1: chart_title = id_list[0] else: chart_title = '' output_ws[title_cell] = chart_title # chart style setting chart.width = CHART_WIDTH chart.height = CHART_HEIGHT chart.x_axis.title = CHART_X_AXIS_TITLE chart.x_axis.scaling.logBase = 10 chart.y_axis.title = CHART_Y_AXIS_TITLE chart.legend.overlay = True chart.legend = None output_ws.add_chart(chart, get_column_letter(col)+str(row)) r += 1 if r % CHART_REPEAT == 0: row = CHART_POSITION_ROW_INIT col += CHART_WIDTH_CELL else: row += CHART_HEIGHT_CELL return
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 run(output_path=None, user_provided_master_path=None, date_range=None): """ Main function to run this analyser. :param output_path: :param user_provided_master_path: :return: """ if user_provided_master_path: logger.info(f"Using master file: {user_provided_master_path}") NUMBER_OF_PROJECTS = projects_in_master(user_provided_master_path) else: logger.info(f"Using default master file (refer to config.ini)") NUMBER_OF_PROJECTS = projects_in_master( os.path.join(ROOT_PATH, runtime_config['MasterForAnalysis']['name'])) wb = openpyxl.Workbook() segment_series_generator = _segment_series() logger.debug(f"Using block_start of {BLOCK_START}") logger.debug(f"Using day_range of {DAY_RANGE}") logger.debug(f"Using block_skip of {BLOCK_SKIP}") logger.debug(f"Using block_end of {BLOCK_END}") logger.debug(f"Using forecast_actual_skip of {FORECAST_ACTUAL_SKIP}") for p in range(1, NUMBER_OF_PROJECTS + 1): proj_num, st_row = _row_calc(p) wb = gather_data( st_row, proj_num, wb, block_start_row=BLOCK_START, interested_range=DAY_RANGE, master_path=user_provided_master_path, date_range=date_range)[0] chart = ScatterChart() chart.title = CHART_TITLE chart.style = CHART_STYLE chart.height = CHART_HEIGHT chart.width = CHART_WIDTH chart.x_axis.title = CHART_X_AXIS_TITLE chart.y_axis.title = CHART_Y_AXIS_TITLE chart.legend = None chart.x_axis.majorUnit = CHART_X_AXIS_MAJOR_UNIT chart.x_axis.minorGridlines = None chart.y_axis.majorUnit = CHART_Y_AXIS_MAJOR_UNIT derived_end = 2 if GREYMARKER: markercol = _grey_marker_colours else: markercol = _marker_colours for p in range(NUMBER_OF_PROJECTS): for i in range(1, 8): # 8 here is hard-coded number of segments within a project series (ref: dict in _segment_series() if i == 1: inner_start_row = derived_end else: inner_start_row = derived_end _inner_step = next(segment_series_generator) series, derived_end = _series_producer(wb.active, inner_start_row, _inner_step[1] - 1) if _inner_step[0] == 'sobc': series.marker.symbol = "circle" series.marker.graphicalProperties.solidFill = markercol[0] elif _inner_step[0] == 'obc': series.marker.symbol = "triangle" series.marker.graphicalProperties.solidFill = markercol[1] elif _inner_step[0] == 'ds1': series.marker.symbol = "diamond" series.marker.graphicalProperties.solidFill = markercol[2] elif _inner_step[0] == 'fbc': series.marker.symbol = "square" series.marker.graphicalProperties.solidFill = markercol[3] elif _inner_step[0] == 'ds2': series.marker.symbol = "plus" series.marker.graphicalProperties.solidFill = markercol[4] elif _inner_step[0] == 'ds3': series.marker.symbol = "x" series.marker.graphicalProperties.solidFill = markercol[5] else: series.marker.symbol = "square" series.marker.graphicalProperties.solidFill = markercol[6] series.marker.size = 10 chart.series.append(series) segment_series_generator = _segment_series() derived_end = derived_end + 1 wb.active.add_chart(chart, CHART_ANCHOR_CELL) try: if output_path: wb.save(os.path.join(output_path[0], 'swimlane_milestones.xlsx')) logger.info(f"Saved swimlane_milestones.xlsx to {output_path}") else: output_path = os.path.join(ROOT_PATH, 'output') wb.save(os.path.join(output_path, 'swimlane_milestones.xlsx')) logger.info(f"Saved swimlane_milestones.xlsx to {output_path}") except PermissionError: logger.critical( "Cannot save swimlane_milestones.xlsx file - you already have it open. Close and run again." ) return