def createWorkBook () :
wb = Workbook()
ws = wb.active
for row in data:
ws.append(row)
chart = ScatterChart()
chart.title = "Scatter Chart"
chart.style = 13
chart.x_axis.title = 'Size'
chart.y_axis.title = 'Percentage'
j = 1
valueList = []
for row in ws :
for cell in row :
chart = ScatterChart()
if str(cell.value) == 'from' :
xvalues = Reference(ws, min_col = 2, max_col=3, min_row = cell.row +1, max_row= cell.row +1)
values = Reference(ws, min_col = 2, max_col=3,min_row= cell.row +2, max_row = cell.row +2)
series = Series(values,xvalues , title_from_data=False)
valueList.append(series)
elif (str(cell.value) == 'title' and cell.row > 1) or str(cell.value) == 'end':
for value in valueList :
chart.series.append(value)
ws.add_chart(chart, "A10")
valueList = []
wb.save('test.xlsx')
def addChart(groupLength1):
print('Printing Chart...')
# print('grouplength1: '+str(len(groupLength1)))
chart = ScatterChart()
chart.title = "Capacity Vs Voltage"
chart.style = 13
chart.x_axis.title = 'Capacity'
chart.y_axis.title = 'Voltage'
le = (len(groupLength1) * 4) + len(groupLength1)
# print("The sizes of each grp" + str(groupLength1[0]))
k = 0
m = 1
# print("list siz = " + str(len(groupLength1)))
# print("le = " + str(le))
for j in range(4, le, 5):
print("j = " + str(j))
xvalues = Reference(AutoExcel.sh1_O,
min_col=j,
min_row=1,
max_row=groupLength1[k])
values = Reference(AutoExcel.sh1_O,
min_col=m,
min_row=1,
max_row=groupLength1[k])
series = Series(values, xvalues, title_from_data=True)
chart.series.append(series)
k += 1
m += 5
AutoExcel.sh1_O.add_chart(chart, 'L3')
out_f = "C:\\Users\\Vignesh\\PycharmProjects\\AutoExcel\\src\\uploads\\" + AutoExcel.outputFile + '.xlsx'
AutoExcel.wb_O.save(out_f)
def sample_bar_chart(self, title, matrix):
"""
more details
https://openpyxl.readthedocs.io/en/2.5/charts/chart_layout.html#size-and-position
"""
rows, cols = len(matrix), len(matrix[0])
wb = openpyxl.Workbook()
sheet = wb.active
for data_row in matrix:
sheet.append(data_row)
chart = ScatterChart()
x = Reference(sheet, min_col=1, min_row=1, max_row=rows)
for i in range(2, cols + 1):
y = Reference(sheet, min_col=i, min_row=1, max_row=rows)
s = Series(y, x, title_from_data=True)
chart.series.append(s)
chart.title = title
chart.style = 12
chart.x_axis.title = "X"
chart.y_axis.title = "Y"
chart.legend.position = 'r'
sheet.add_chart(chart, 'B12')
wb.save("sample_chart.xlsx")
def createGraph3(disl, disl3): #,disl2,disl3):
res = 'Resistencia'
eft = 'Ef.Turbina'
disl = [res] + disl
disl3 = [eft] + disl3
#for i in range(1,len(disl)):
for g in range(1):
graph3.append(disl)
graph3.append(disl3)
chart = ScatterChart()
chart.title = 'R[Ω] vs Eficiencia turbina'
chart.x_axis.title = 'Eft'
chart.y_axis.title = 'R'
xvals = Reference(graph3, min_col=2, min_row=1, max_col=len(disl))
#for s in range(1,len(disl)): #10
values = Reference(graph3, min_col=2, min_row=2, max_col=len(disl))
series = Series(values, xvals, title_from_data=True)
chart.series.append(series)
#chart.add_data(values)
#s = chart.series[1]
graph3.add_chart(chart, "M1")
wb.save(filesheet)
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 plot_graph(workbook, output_file, index_ticker, company_ticker):
sheets_list = workbook.sheetnames
beta_output = sheets_list[0]
company_ticker = sheets_list[1]
market_ticker = sheets_list[2]
beta_worksheet = workbook[beta_output]
company_change_len = len(beta_worksheet['A'])
chart = ScatterChart()
chart.title = "Beta"
chart.style = 13
chart.x_axis.title = '{0} % change'.format(index_ticker)
chart.y_axis.title = '{0} % change'.format(company_ticker)
xvalues = Reference(beta_worksheet,
min_col=2,
min_row=2,
max_row=company_change_len)
yvalues = Reference(beta_worksheet,
min_col=1,
min_row=2,
max_row=company_change_len)
series = Series(yvalues, xvalues, title_from_data=False)
series.graphicalProperties.line.noFill = True
series.marker.symbol = "circle"
series.marker.size = "2"
series.trendline = Trendline(dispRSqr=True,
trendlineType='linear',
dispEq=True)
chart.series.append(series)
beta_worksheet.add_chart(chart, "F5")
workbook.save(output_file)
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_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 draw_pattern_scatterchart(data_work_sheet, data_rows_seq, data_columns_list, chart_position, chart_title):
chart = ScatterChart()
chart.title = str(chart_title)
chart.legend.position = 't'
#chart.x_axis.title = 'samples from:' + data_work_sheet.title[0:21]
#chart.x_axis.title = u'样本:' + data_work_sheet.title[0:25] + '..'
chart.x_axis.title = data_work_sheet.title + '..'
#chart.x_axis.txPr =
#chart_title_font = Font(name='Arial Narrow',size=12)
chart.y_axis.title = 'lantency(ms)'
#chart.style = 13
#chart.layout =
#chart.graphical_properties =
#chart.varyColors = 'blue'
#chart.scatterStyle = 'marker'
#same sheet same sample width of chart, x ray values, get min and max row from pattern_col
xvalues = Reference(data_work_sheet, min_col=1, min_row=data_rows_seq[0], max_row=data_rows_seq[-1])
for i in data_columns_list :
#print(data_work_sheet.cell(row=1,column=i).value)
line_title = data_work_sheet.cell(row=1,column=i).value
yvalues = Reference(data_work_sheet, min_col=i, min_row=data_rows_seq[0], max_row=data_rows_seq[-1])
series = Series(yvalues, xvalues, title=line_title)
chart.series.append(series)
#print(chart.series[0])
# Style the lines
for i in range(len(data_columns_list)) :
s1 = chart.series[i]
s1.marker.symbol = "circle"
#s1.marker.symbol = "triangle"
#s1.marker.graphicalProperties.solidFill = "FF0000" # Marker filling
#s1.marker.graphicalProperties.line.solidFill = "FF0000" # Marker outline
s1.graphicalProperties.line.noFill = True
# print(wb.chartsheets)
wb['ScatterChart'].add_chart(chart, chart_position)
def write_plots(worksheet, spec):
chart = ScatterChart()
chart.title = 'Measured Data w/ Exponential Fit \n Pressure vs. Temperature'
chart.style = 13
chart.x_axis.title = 'Time (hrs)'
chart.y_axis.title = 'Pressure (psi)'
# time values (hours)
x_values = Reference(worksheet, min_col=9, min_row=3, max_row=len(spec.p) + 3)
# pressure data (psi)
p = Reference(worksheet, min_col=11, min_row=3, max_row=len(spec.p) + 3)
p_series = Series(p, x_values)
chart.series.append(p_series)
# exponential curve fit data (psi)
exp = Reference(worksheet, min_col=10, min_row=3, max_row=len(spec.p) + 3)
exp_series = Series(exp, x_values)
chart.series.append(exp_series)
# Style the lines
s1 = chart.series[1]
s1.marker.symbol = "triangle"
s1.marker.graphicalProperties.solidFill = "FF0000" # Marker filling
s1.marker.graphicalProperties.line.solidFill = "FF0000" # Marker outline
s1.graphicalProperties.line.noFill = True
s2 = chart.series[0]
s2.graphicalProperties.line.solidFill = "00AAAA"
s2.graphicalProperties.line.dashStyle = "sysDot"
s2.graphicalProperties.line.width = 100050 # width in EMUs
worksheet.add_chart(chart, 'A7')
return worksheet
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 createGraphs(disl, disl1): #,disl2,disl3):
#for i in range(1,len(disl)):
res = 'Resistencia'
rps = 'RPM'
disl = [res] + disl
disl1 = [rps] + disl1
for g in range(1):
graph.append(disl)
graph.append(disl1)
chart = ScatterChart()
chart.title = 'R[Ω] vs RPM'
chart.x_axis.title = 'RPM'
chart.y_axis.title = 'R[Ω]'
xvals = Reference(graph, min_col=2, min_row=1, max_col=len(disl))
#for s in range(1,len(disl)): #10
values = Reference(graph, min_col=2, min_row=2, max_col=len(disl))
series = Series(values, xvals, title_from_data=True)
chart.series.append(series)
#chart.add_data(values)
#s = chart.series[1]
graph.add_chart(chart, "M1")
wb.save(filesheet)
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 copyToSheet(die, test):
dieSheets = []
for sheet in range(len(clSheets)):
if die.lower() in clSheets[sheet].lower() and test.lower(
) in clSheets[sheet].lower():
dieSheets.append(clSheets[sheet])
if len(dieSheets) > 0:
print('Copying ' + die + ' ' + test)
ws = lifetime.create_sheet(title=die + ' ' + test)
pasteRow = 2
for sheet in range(len(dieSheets)):
active = clWings.sheets[dieSheets[sheet]]
for i in range(1, 22):
for j in range(1, 3):
ws.cell(row=i + pasteRow - 1,
column=j).value = active.range(
get_column_letter(j) + str(i + 32)).value
pasteRow = pasteRow + 21
ws.cell(row=1, column=1).value = '[Cl2] (ppm)'
ws.cell(row=1, column=2).value = 'Response (nA)'
ws.cell(row=1, column=3).value = 'Calibration (nA)'
ws.cell(row=1, column=5).value = 'Slope'
ws.cell(row=2, column=5).value = 'Intercept'
ws.cell(
row=1,
column=6).value = clWings.sheets[dieSheets[0]].range('B12').value
ws.cell(
row=2,
column=6).value = clWings.sheets[dieSheets[0]].range('B13').value
for i in range(2, ws.max_row):
ws.cell(row=i, column=3).value = float(ws['F1'].value) * float(
ws.cell(row=i, column=1).value) + float(ws['F2'].value)
chart = ScatterChart()
chart.title = str(die + ' ' + test + ' Lifetime (' +
str(len(dieSheets)) + 'Calibrations)')
chart.x_axis.title = '[Cl2] (ppm)'
chart.y_axis.title = 'Response (nA)'
xvalues = Reference(ws, min_col=1, min_row=2, max_row=ws.max_row)
roamValues = Reference(ws, min_col=2, min_row=1, max_row=ws.max_row)
roamSeries = Series(roamValues, xvalues, title_from_data=True)
calValues = Reference(ws, min_col=3, min_row=1, max_row=ws.max_row)
calSeries = Series(
calValues,
xvalues,
title_from_data=True,
)
roamSeries.marker = openpyxl.chart.marker.Marker('x')
roamSeries.graphicalProperties.line.noFill = True
chart.series.append(roamSeries)
chart.series.append(calSeries)
cs.add_chart(chart, 'A' + str(len(lifetime.worksheets) * 15 - 29))
def graph_bake(self, parameters: BakingGraphParameters):
last_row = parameters.num_rows + 1
start_row = 2
start_column = 2
x_axis_title = "{} Time from start (hr)".format(u"\u0394")
y_axis_title = "{} Wavelength (pm)".format(u"\u0394")
y_axis_title_sensitivity = "Drift (mK)"
y_axis_title_temperature = "{} Temperature (K)".format(u"\u0394")
x_values = Reference(parameters.data_sheet, min_col=start_column, min_row=start_row, max_row=last_row)
for i, fbg_name in enumerate(self.fbg_names):
chart_title = "{} {} Wavelength (pm) vs. {} Time from start ; {}"\
.format(fbg_name, u"\u0394", u"\u0394", self.excel_file_name)
chart = ScatterChart()
y_values = Reference(parameters.data_sheet, min_col=self._get_baking_wavelength_column(i),
min_row=start_row, max_row=last_row)
series = self.create_series_bake(x_values, y_values, i)
chart.series.append(series)
trend_values = Reference(parameters.data_sheet, min_col=parameters.trend_line_indexes[i] + 1,
min_row=start_row, max_row=last_row)
series = Series(trend_values, x_values, title="Trend (pm)")
chart.series.append(series)
format_chart(chart, x_axis_title, y_axis_title, chart_title)
parameters.chart_sheet.add_chart(chart, "B{}".format(30*i + 2))
if self._valid_sensitivities():
sensitivity_chart_title = "{} drift (mK) vs. {} Time from start; {}"\
.format(fbg_name, u"\u0394", self.excel_file_name)
sensitivity_chart = ScatterChart()
sensitivity_values = Reference(parameters.data_sheet, min_col=parameters.sensitivity_indexes[i] + 1,
min_row=start_row, max_row=last_row)
series = Series(sensitivity_values, x_values, title="Drift (mK)")
sensitivity_chart.series.append(series)
format_chart(sensitivity_chart, x_axis_title, y_axis_title_sensitivity, sensitivity_chart_title)
parameters.chart_sheet.add_chart(sensitivity_chart, "V{}".format(30 * i + 2))
temperature_graph_start_column = "AO" if self._valid_sensitivities() else "V"
chart_title = "{0} Temperature vs. {0} Time from start; {1}".format(u"\u0394", self.excel_file_name)
chart = ScatterChart()
values = Reference(parameters.data_sheet, min_col=2, min_row=start_row, max_row=last_row)
series = Series(values, x_values, title="{} Temperature (K)".format(u"\u0394"))
chart.series.append(series)
format_chart(chart, x_axis_title, y_axis_title_temperature, chart_title)
parameters.chart_sheet.add_chart(chart, "{}2".format(temperature_graph_start_column))
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
def all_sheets(sheet_name):
chart = ScatterChart()
chart.title = "Scatter Chart"
chart.style = 2
last_index_landing, last_index_parking, last_index_takeoff = 0, 0, 0
ws = wb[sheet_name]
for i in range(2, ws.max_row + 2):
for j in range(8, 19, 5):
if ws.cell(row=i, column=j).value is None:
if j == 8 and last_index_landing == 0:
last_index_landing = i - 1
elif j == 13 and last_index_parking == 0:
last_index_parking = i - 1
elif j == 18 and last_index_takeoff == 0:
last_index_takeoff = i - 1
print(last_index_landing, last_index_parking, last_index_takeoff)
# -------------------LANDING---------------------------------------------
x_landing = Reference(ws, min_col=8, min_row=2, max_row=last_index_landing)
y_landing = Reference(ws, min_col=9, min_row=2, max_row=last_index_landing)
seriesLanding = Series(y_landing, x_landing, title="landing_queue")
chart.series.append(seriesLanding)
# -------------------TAKEOFF---------------------------------------------
x_takeoff = Reference(ws,
min_col=18,
min_row=2,
max_row=last_index_takeoff)
y_takeoff = Reference(ws,
min_col=19,
min_row=2,
max_row=last_index_takeoff)
seriesTakeoff = Series(y_takeoff, x_takeoff, title="takeoff_queue")
chart.series.append(seriesTakeoff)
# -------------------PARKING---------------------------------------------
x_parking = Reference(ws,
min_col=13,
min_row=2,
max_row=last_index_parking)
y_parking = Reference(ws,
min_col=14,
min_row=2,
max_row=last_index_parking)
seriesParking = Series(y_parking, x_parking, title="parking_queue")
chart.series.append(seriesParking)
# -----------------------------------------------------------------------
ws.add_chart(chart, "A10")
wb.save(file)
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)
def create_chart(temperatures: List[float], extra_point_temperatures: List[float]=None) -> ScatterChart:
chart = ScatterChart()
chart.scatterStyle = "smoothMarker"
temperatures_copy = [t for t in temperatures]
if extra_point_temperatures is not None:
for extra in extra_point_temperatures:
for temperature in temperatures:
if math.isclose(extra, temperature, abs_tol=0.5):
temperatures_copy.remove(temperature)
chart.x_axis.scaling.min = round(temperatures_copy[0], 1) - 1
chart.x_axis.scaling.max = round(temperatures_copy[-1], 1) + 1
return chart
def __writeCharts(sheet: Worksheet, tests: Iterable[str], order: Iterable[str],
charts: Iterable[Tuple[str, str]], data: Dict[str,
dict]) -> None:
row = 1
bound_scale = 10
def updateBounds(bounds, x_data, y_data):
from statistics import mean
if not bounds:
bounds = [0, 0, 0, 0, 0, 0, 0]
bounds[0] = min(bounds[0], *x_data)
bounds[1] = max(bounds[1], *x_data)
bounds[2] = mean(bounds[2], *x_data)
bounds[3] = min(bounds[3], *y_data)
bounds[4] = max(bounds[4], *y_data)
bounds[5] = mean(bounds[5], *y_data)
return bounds
for seq in order:
col = 0
for (typeX, typeY) in charts:
chart = ScatterChart(scatterStyle='lineMarker')
chart.title = seq
chart.x_axis.title = typeX
chart.y_axis.title = typeY
chart.visible_cells_only = False
bounds = None
for test in tests:
#bounds = updateBounds(bounds, data[seq][test][__DATA][typeX], data[seq][test][__DATA][typeY])
rX = data[seq][test][typeX]
rY = data[seq][test][typeY]
series = Series(Reference(sheet,
min_col=rY.min_col,
max_col=rY.max_col,
min_row=rY.min_row),
Reference(sheet,
min_col=rX.min_col + 1,
max_col=rX.max_col,
min_row=rX.min_row),
title_from_data=True)
series.marker.symbol = 'auto'
chart.series.append(series)
if bounds:
sheet.x_axis.scaling.min = max(
bounds[0] - bounds[2] / bound_scale, 0)
sheet.x_axis.scaling.max = bounds[1] + bounds[2] / bound_scale
sheet.y_axis.scaling.min = max(
bounds[3] - bounds[5] / bound_scale, 0)
sheet.y_axis.scaling.max = bounds[4] + bounds[5] / bound_scale
sheet.add_chart(chart, get_column_letter(7 + col) + str(row))
col += 9
row += 15
def setUp(self):
wb = Workbook()
ws = wb.get_active_sheet()
ws.title = 'data'
for i in range(10):
ws.cell(row=i, column=0).value = i
ws.cell(row=i, column=1).value = i
self.scatterchart = ScatterChart()
self.scatterchart.add_serie(Serie(Reference(ws, (0, 0), (10, 0)),
xvalues=Reference(ws, (0, 1), (10, 1))))
self.cw = ChartWriter(self.scatterchart)
self.root = Element('test')
def format_graph(chart_data, multiple=None):
wb = load_workbook(chart_data)
ws = wb['Graph']
# dimensions of the excel data ===> A1:D6
table_dimension = ws.dimensions
#split dimensions to [A1, D6]
table_dimension = table_dimension.split(':')
print(table_dimension)
col_row_list = []
#do this to split table_dimensions into columns and rows ===> [A, 1, D, 6]
for col_row in table_dimension:
for x in col_row:
if x.isnumeric():
ind = col_row.index(x)
col_row_list.append(col_row[:ind])
col_row_list.append(col_row[ind:])
break
print(col_row_list)
#use the ord method to convert letters to numbers ==> [A, 1, D, 6] = [1, 1, 4, 6]
y = [ord(i.lower()) - 96 if i.isalpha() else int(i) for i in col_row_list]
min_column = y[0] + 1
min_row = y[1]
max_column = y[2]
max_row = y[3]
chart = ScatterChart()
chart.title = "Graph"
chart.style = 2
chart.x_axis.title = 'Distance'
chart.y_axis.title = 'RSSI'
x_data = Reference(ws,
min_col=min_column,
min_row=min_row + 1,
max_row=max_row)
for i in range(min_column + 1, max_column + 1):
values = Reference(ws, min_col=i, min_row=min_row, max_row=max_row)
series = Series(values, x_data, title_from_data=True)
chart.series.append(series)
ws.add_chart(chart, "G10")
wb.save(chart_data)
def build_scatter_with_mean_stdev(xvalues_refs, yvalues_refs, stdev_refs, titles, errDir='y'):
"""Given x values, a list of y values, and the y values' corresponding
stdev, it will return a scatter chart with stdev as error bars"""
if len(yvalues_refs) != len(stdev_refs) != len(titles):
raise ValueError("y-values and stdev list length must be the same")
chart = ScatterChart()
for xvalues, yvalues, stdev, title in zip(xvalues_refs, yvalues_refs, stdev_refs, titles):
# convert reference to data source
stdev_data_source = data_source.NumDataSource(numRef=data_source.NumRef(f=stdev))
error_bars = ErrorBars(errDir=errDir, errValType='cust', plus=stdev_data_source, minus=stdev_data_source)
series = Series(yvalues, xvalues, title=title)
series.errBars = error_bars
chart.series.append(series)
return chart
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
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 export():
print("in export")
# get all data
restaurant_list = Restaurant.query.all()
# print(restaurant_list)
# create the excel workbook
wb = Workbook()
sheet = wb.active
style_headline = 'Headline 1'
style_data = 'Headline 1'
# header
sheet.cell(row=1, column=1).value = 'Bill'
sheet.cell(row=1, column=1).style = style_headline
sheet.cell(row=1, column=2).value = 'Tip'
sheet.cell(row=1, column=2).style = style_headline
# data
row_index = 2
for restaurant in restaurant_list:
sheet.cell(row=row_index, column=1).value = restaurant.bill
sheet.cell(row=row_index, column=1).style = style_data
sheet.cell(row=row_index, column=2).value = restaurant.tip
sheet.cell(row=row_index, column=2).style = style_data
row_index += 1
# add chart
chart = ScatterChart()
chart.title = "Scatter Chart"
chart.style = 13
chart.x_axis.title = 'Bill Amount'
chart.y_axis.title = 'Tip Amount'
xvalues = Reference(sheet, min_col=1, min_row=2, max_row=1001)
for i in range(2, 3):
values = Reference(sheet, min_col=i, min_row=1, max_row=1001)
series = Series(values, xvalues, title_from_data=True)
chart.series.append(series)
sheet.add_chart(chart, "D1")
filename = "restaurant.xlsx"
full_path = os.path.join(app.config['UPLOAD_FOLDER'], filename)
print("full_path", full_path)
# save the workbook
wb.save(full_path)
return send_file(full_path, as_attachment=True)
def test_no_write_legend(self):
wb = Workbook()
ws = wb.get_active_sheet()
ws.title = 'data'
for i in range(10):
ws.cell(row=i, column=0).value = i
ws.cell(row=i, column=1).value = i
scatterchart = ScatterChart()
scatterchart.add_serie(Serie(Reference(ws, (0, 0), (10, 0)),
xvalues=Reference(ws, (0, 1), (10, 1))))
cw = ChartWriter(scatterchart)
root = Element('test')
scatterchart.show_legend = False
cw._write_legend(root)
eq_(get_xml(root), '<?xml version=\'1.0\' encoding=\'UTF-8\'?><test />')
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 main():
wb = openpyxl.Workbook()
start_generator = _start_cells()
segment_series_generator = _segment_series()
for p in range(1, 31):
proj_num, st_row = _row_calc(p)
wb = milestone_swimlane(st_row,
proj_num,
wb,
block_start_row=90,
interested_range=365)[0]
chart = ScatterChart()
chart.title = "Swimlane Chart"
chart.style = 1
chart.x_axis.title = 'Days from Today'
chart.y_axis.title = 'Project No'
derived_end = 2
for p in range(1, 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)[1]
series, derived_end = _series_producer(wb.active, inner_start_row,
_inner_step)
if _inner_step == 1:
series.marker.symbol = "triangle"
series.marker.graphicalProperties.solidFill = "01a852"
else:
series.marker.symbol = "square"
series.marker.graphicalProperties.solidFill = "FF0000"
series.marker.size = 10
chart.series.append(series)
start_generator = _start_cells()
segment_series_generator = _segment_series()
derived_end = derived_end + 1
wb.active.add_chart(chart, "E1")
wb.save(os.path.join(DESKTOP, 'output.xlsx'))