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