def scatter(wb): ws = wb.create_sheet(6, "Scatter") for i in range(10): ws.append([i, i]) chart = ScatterChart() xvalues = Reference(ws, (0, 1), (9, 1)) values = Reference(ws, (0, 0), (9, 0)) series = Series(values, xvalues=xvalues) chart.append(series) ws.add_chart(chart)
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) chart1.append(s) chart2.append(s) ws.add_chart(chart1, "C1") ws.add_chart(chart2, "C15") wb.save("minmax.xlsx")
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 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.')
chart4.title = "Both Log Scale" chart4.x_axis.title = 'x (log10)' chart4.y_axis.title = 'y (log10)' chart4.legend = None chart4.x_axis.scaling.logBase = 10 chart4.y_axis.scaling.logBase = 10 chart5 = ScatterChart() chart5.title = "Log Scale Base e" chart5.x_axis.title = 'x (ln)' chart5.y_axis.title = 'y (ln)' chart5.legend = None chart5.x_axis.scaling.logBase = math.e chart5.y_axis.scaling.logBase = math.e 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) chart1.append(s) chart2.append(s) chart3.append(s) chart4.append(s) chart5.append(s) ws.add_chart(chart1, "C1") ws.add_chart(chart2, "I1") ws.add_chart(chart3, "C15") ws.add_chart(chart4, "I15") ws.add_chart(chart5, "F30") wb.save("log.xlsx")
def _create_chart(self, wb): """Creates a chart sheet of the processed CSV data in the Excel workbook. Parameters ---------- wb (workbook): Excel workbook of the mapped data Returns ------- None """ ws = wb.active outputs = self.mapped_settings.get_column('Output') new_titles = self.mapped_settings.get_column('Title') chart_title = self.general_settings.get_column('Chart Title') row_size = self.output_data[ new_titles.loc[0]].size # Get the row number of the last cell # Create a ScatterChart chart sheet cs = wb.create_chartsheet() chart = ScatterChart() # Store the row indices of the x-axis and y-axis column labels in the configuration # file mapped_settings. x_axis_index = self.get_x_axis().index[0] y_axis_indices = self.get_y_axis().index # Set x-axis x = Reference(ws, min_col=outputs.loc[x_axis_index], min_row=2, max_row=row_size) # Plot multiple graphs in a single chart for row in y_axis_indices: y = Reference(ws, min_col=outputs.loc[row], min_row=2, max_row=row_size) s = Series(y, x, title=new_titles.loc[row]) chart.append(s) # Set the x-axis label chart.x_axis.title = new_titles.loc[x_axis_index] # Situate x-axis below negative numbers chart.x_axis.tickLblPos = "low" # Create the chart legend or set the y-axis label self._chart_legend(chart, new_titles.loc[y_axis_indices[0]], y_axis_indices) # Title the chart chart.title = self.get_chart_title(new_titles, chart_title, x_axis_index, y_axis_indices) # Set grid lines on or off. self._grid_lines(chart) # Set the scaling limits of the x and y axis self._chart_scaling(chart) # Add chart to the workbook cs.add_chart(chart)
chart4.x_axis.title = 'x (log10)' chart4.y_axis.title = 'y (log10)' chart4.legend = None chart4.x_axis.scaling.logBase = 10 chart4.y_axis.scaling.logBase = 10 chart5 = ScatterChart() chart5.title = "Log Scale Base e" chart5.x_axis.title = 'x (ln)' chart5.y_axis.title = 'y (ln)' chart5.legend = None chart5.x_axis.scaling.logBase = math.e chart5.y_axis.scaling.logBase = math.e 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) chart1.append(s) chart2.append(s) chart3.append(s) chart4.append(s) chart5.append(s) ws.add_chart(chart1, "C1") ws.add_chart(chart2, "I1") ws.add_chart(chart3, "C15") ws.add_chart(chart4, "I15") ws.add_chart(chart5, "F30") wb.save("log.xlsx")
chart.style = 13 ## Defines msn code and state for kth 50 chunk c = seseds.cell(row=(50*k+2), column=1) d = seseds.cell(row=(50*k+2), column=2) code = c.value state = d.value ## Create data coordinates for chart and appends data to chart year = Reference(seseds, min_col=3, min_row = (50*k+2), max_row=(50*(k+1)+1)) data = Reference(seseds, min_col=4, min_row = (50*k+2), max_row=(50*(k+1)+1)) s = Series(data, xvalues=year) chart.append(s) ## Collects corresponding despriction and units from msncodes title = msncodes.cell(row=(numberof4s+1), column=2) y_axis = msncodes.cell(row=(numberof4s+1), column=3) ## Defines new title that will Description followed by state newtitle = (title.value + " " + state) ## Gives appropriate names to title, x-axis and y-axis for each chart chart.title = newtitle chart.x_axis.title = 'Years' chart.y_axis.title = y_axis.value
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) chart1.append(s) chart2.append(s) ws.add_chart(chart1, "C1") ws.add_chart(chart2, "C15") wb.save("minmax.xlsx")