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")
