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 __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 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 _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 gen_workbook(input_file_or_dir,output_file):
wb = Workbook()
info_token=plot_state()
if os.path.isfile(input_file_or_dir):
files=[input_file_or_dir]
if os.path.isdir(input_file_or_dir):
files=glob.glob(os.path.join(input_file_or_dir,"*.dat"))
else:
return
for my_file in files:
print("about to save1",my_file)
if plot_load_info(info_token,my_file)==True:
x=[]
y=[]
z=[]
data=dat_file()
if dat_file_read(data,my_file)==True:
print("read",my_file)
ws = wb.create_sheet(title=title_truncate(os.path.basename(my_file)))
ws.cell(column=1, row=1, value=info_token.title)
ws.cell(column=1, row=2, value=info_token.x_label+" ("+info_token.x_units+") ")
ws.cell(column=2, row=2, value=info_token.y_label+" ("+info_token.y_units+") ")
for i in range(0,data.y_len):
ws.cell(column=1, row=i+3, value=data.y_scale[i])
ws.cell(column=2, row=i+3, value=data.data[0][0][i])
c1 = ScatterChart()
c1.title = info_token.title
c1.style = 13
c1.height=20
c1.width=20
c1.y_axis.title = info_token.y_label+" ("+info_token.y_units+") "
c1.x_axis.title = info_token.x_label+" ("+info_token.x_units+") "
xdata = Reference(ws, min_col=1, min_row=3, max_row=3+data.y_len)
ydata = Reference(ws, min_col=2, min_row=3, max_row=3+data.y_len)
series = Series(ydata,xdata, title_from_data=True)
c1.series.append(series)
ws.add_chart(c1, "G4")
print("about to save1")
wb.save(filename = output_file)
print("about to save0")
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'))
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 histogram(ws, series: np.ndarray, title="Distribution", bins=50):
"""
:param title: 图表标题
:param ws: 储存数据的worksheet
:param series: 画直方图的数据
:param bins: 分段的个数
:return: chart
"""
hist, bin_edges = np.histogram(series, bins)
count = np.insert(hist, 0, hist[0])
max_row = bins + 1
current = np.repeat(series[-1], max_row)
max_number = np.linspace(0, hist.max(), max_row, endpoint=True)
data = np.vstack((bin_edges, count, current, max_number)).transpose()
row_offset = 0 if ws.max_row == 1 else ws.max_row
print(row_offset)
for r in data:
ws.append(r.tolist())
chart = ScatterChart()
chart.width = 22 # default is 15
chart.height = 15 # default is 7.5
chart.style = 2
chart.title = title
chart.y_axis.title = 'Count'
chart.x_axis.majorGridlines = None
chart.y_axis.number_format = COMMA0_FORMAT
chart.x_axis.title = 'Bin'
chart.x_axis.number_format = PERCENT_FORMAT
yvalues = Reference(ws, min_col=2, min_row=row_offset+1, max_row=ws.max_row)
xvalues = Reference(ws, min_col=1, min_row=row_offset+1, max_row=ws.max_row)
series = Series(values=yvalues, xvalues=xvalues, title='Count')
series.smooth = True
chart.series.append(series)
yvalues = Reference(ws, min_col=4, min_row=row_offset+1, max_row=ws.max_row)
xvalues = Reference(ws, min_col=3, min_row=row_offset+1, max_row=ws.max_row)
series = Series(values=yvalues, xvalues=xvalues, title='Current')
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 generate_captures_graph(self, captures_info, row_count):
""" Gera o gráfico Sinais X Tempo. """
my_chart = ScatterChart()
my_chart.title = 'Gráfico dos Sinais'
my_chart.style = 16
my_chart.y_axis.title = 'Sinal'
my_chart.x_axis.title = 'Tempo (segundos)'
x_values = Reference(self.spreadsheet,
min_col=2,
min_row=row_count - len(captures_info),
max_row=row_count - 3)
y_values = Reference(self.spreadsheet,
min_col=6,
min_row=row_count - len(captures_info) - 1,
max_row=row_count - 3)
series = Series(y_values, x_values, title_from_data=True)
my_chart.series.append(series)
my_chart.width = 23
my_chart.height = 10
self.spreadsheet.add_chart(my_chart, f"A{row_count}")
def format_chart(chart: ScatterChart, x_axis_title: str, y_axis_title: str, title: str):
chart.height = 15
chart.width = 30
chart.x_axis.tickLblPos = "low"
chart.title = title
chart.x_axis.title = x_axis_title
chart.y_axis.title = y_axis_title
font = drawing.text.Font(typeface='Arial')
cp_axis = CharacterProperties(latin=font, sz=1600, b=True)
cp_axis_title = CharacterProperties(latin=font, sz=1600)
cp_title = CharacterProperties(latin=font, sz=1200)
chart.y_axis.txPr = RichText(p=[Paragraph(pPr=ParagraphProperties(defRPr=cp_axis), endParaRPr=cp_axis)])
chart.y_axis.title.txPr = RichText(p=[Paragraph(pPr=ParagraphProperties(defRPr=cp_axis),
endParaRPr=cp_axis_title)])
chart.x_axis.txPr = RichText(p=[Paragraph(pPr=ParagraphProperties(defRPr=cp_axis), endParaRPr=cp_axis)])
chart.x_axis.title.txPr = RichText(p=[Paragraph(pPr=ParagraphProperties(defRPr=cp_axis),
endParaRPr=cp_axis_title)])
chart.title.txPr = RichText(p=[Paragraph(pPr=ParagraphProperties(defRPr=cp_title), endParaRPr=cp_title)])
def write_excel(data, fname):
# write data to excel spreadsheet
wb = Workbook()
ws = wb.active
for row in data:
ws.append(row)
# create
chart = ScatterChart()
chart.title = "RSSI v. Distance (ft)"
chart.style = 1
chart.x_axis.title = "Distance"
chart.y_axis.title = "RSSI"
x_values = Reference(ws, min_col=1, min_row=1, max_row=len(data))
y_values = Reference(ws, min_col=2, min_row=1, max_row=len(data))
series = Series(y_values, x_values, title_from_data=True)
chart.series.append(series)
ws.add_chart(chart, "D2")
wb.save(fname)
print "%s saved!" % (fname)
def draw_chart_excel():
for row in Hp_result_array:
ws.append(row)
c1 = ScatterChart()
c1.title = "Amp vs Freq at different input levels"
c1.style = 13
c1.x_axis.title = 'Freq (Mhz)'
c1.y_axis.title = 'Amp (dBm)'
Pre_stop = 0
for i in range(len(Size)):
Start_row = Pre_stop + 1
Stop_row = Start_row + Size[i] - 1
if Start_row < Stop_row:
#print( 'Start from: ' + str( Start_row ) )
#print( 'Stop from: ' + str( Stop_row ) )
data_y = Reference(ws,
min_col=1,
min_row=Start_row,
max_row=Stop_row)
data_x = Reference(ws,
min_col=2,
min_row=Start_row,
max_row=Stop_row)
Series_name = str(Hp_result_array[Start_row][2]) + 'dBm input'
s = Series(data_y, xvalues=data_x, title=Series_name)
#print( Series_name )
c1.append(s)
Pre_stop = Stop_row
else:
print("Wrong setting\n")
ws.add_chart(c1, "A10")
wb.save(XLSX_name)
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 add_scatter_chart(self, title, min_col=1, min_row=1):
chart = ScatterChart()
chart.title = title
chart.height = 10
chart.width = 18
chart.style = 2 # 线条的style（8种颜色循环，1：灰度，2：异色，3-8：同色渐变-蓝，棕红，绿，紫，青，橙；1-8线条较细，9-16加粗）
chart.y_axis.title = 'Temperature'
chart.x_axis.title = "Open percentage"
chart.x_axis.scaling.max = 1
xvalues = Reference(self.sheet,
min_col=min_col,
min_row=min_row + 1,
max_row=self.sheet.max_row)
for i in range(2, self.sheet.max_column + 1): # 数据列循环
yvalues = Reference(self.sheet,
min_col=i,
min_row=min_row,
max_row=self.sheet.max_row)
series = Series(yvalues, xvalues, title_from_data=True)
chart.series.append(series)
self.sheet.add_chart(chart, "A16") # 将图表添加到 sheet中
def plot_inside_excel():
wb = load_workbook(filename=saints_excel_name)
# Active WorkSheet
ws = wb.active
chat1 = ScatterChart()
# style = MinMax(allow_none=True, min=1, max=48)
# chat1.style = 7
chat1.title = '2020 One Year Bible Study'
chat1.x_axis.title = 'Date'
chat1.y_axis.title = 'Time(o\'clock)'
# set major/minor unit and max value of y axis
chat1.y_axis.majorUnit = 1
chat1.y_axis.minorUnit = 1
chat1.y_axis.scaling.max = 24
chat1.y_axis.scaling.min = 0
# enlarge the chart, default is too small
# width = 15 # in cm, approx 5 rows
# height = 7.5 # in cm, approx 14 rows
chat1.height = chat1.height + 8
chat1.width = chat1.width + 32
xvalues = Reference(ws, min_col=2, min_row=2, max_row=ws.max_row)
for i in range(len(saint_name_list)):
values = Reference(ws, min_col=8 + i, min_row=2, max_row=ws.max_row)
series = Series(values,
xvalues,
title=saint_name_list[i],
title_from_data=False)
# {'triangle', 'dash', 'x', 'auto', 'diamond', 'circle', 'star',
# 'picture', 'square', 'dot', 'plus'}
series.marker = openpyxl.chart.marker.Marker('circle')
series.graphicalProperties.line.noFill = True
chat1.series.append(series)
ws.add_chart(chat1, "A10")
wb.save(filename=saints_excel_name)
def draw_chart_excel():
for row in Hp_result_array:
ws.append( row )
c1 = ScatterChart()
c1.title = "Amp vs Freq at different input levels"
c1.style = 13
c1.x_axis.title = 'Freq (Mhz)'
c1.y_axis.title = 'Amp (dBm)'
#for i in range ( 1, len( Size ) ):
#data = Reference(ws, min_col=1, min_row=Size[ i - 1 ] + 1, max_col=2, max_row= Size[ i - 1 ] + Size[ i ] )
#c1.add_data(data, titles_from_data=False)
#data = Reference(ws, min_col=1, min_row= 1, max_col=2, max_row= len(Hp_result_array) )
#c1.add_data(data, titles_from_data=False)
#print ( len(Size) )
#print( Size )
Pre_stop = 0
for i in range ( len( Size ) ):
Start_row = Pre_stop + 1
Stop_row = Start_row + Size[ i ] - 1
#print( 'Start from: ' + str( Start_row ) )
#print( 'Stop from: ' + str( Stop_row ) )
data_y = Reference(ws, min_col=1, min_row= Start_row, max_row= Stop_row )
data_x = Reference(ws, min_col=2, min_row= Start_row, max_row= Stop_row )
Series_name = str( Hp_result_array[ Start_row ] [ 2 ] ) + 'dBm input'
s = Series( data_y, xvalues = data_x, title = Series_name )
#print( Series_name )
c1.append( s )
Pre_stop = Stop_row
ws.add_chart(c1, "A10")
wb.save("Direct_2cable_test_2to4Ghz_-20to0dBm_A.xlsx")
def parse_statistics(logfile):
xl = pd.ExcelFile(logfile)
df = xl.parse("Sheet")
df = df.sort_values(by='Line Numbers')
writer = pd.ExcelWriter(logfile)
df.to_excel(writer, sheet_name='Sheet', index=False)
writer.save()
wb = openpyxl.load_workbook(logfile)
ws = wb.active
row_count = ws.max_row
column_count = ws.max_column
chart = ScatterChart()
chart.title = "Time upload domain names"
chart.style = 13
chart.x_axis.title = "Line numbers"
chart.y_axis.title = "Time, sec"
xvalues = Reference(ws, min_col=1, min_row=2, max_row=row_count)
color_choice = ['3F888F', 'D24D57']
for i in range(2, column_count + 1):
values = Reference(ws, min_col=i, min_row=1, max_row=row_count)
series = Series(values, xvalues, title_from_data=True)
series.marker.symbol = "diamond"
series.graphicalProperties.line.solidFill = color_choice[i-2]
series.marker.graphicalProperties.line.solidFill = color_choice[i-2]
series.marker.graphicalProperties.solidFill = color_choice[i-2]
series.graphicalProperties.line.width = 20000
chart.series.append(series)
chart.legend.legendPos = 'b'
ws.add_chart(chart)
wb.save(logfile)
def createGraph2(disl, disl2): #,disl2,disl3):
res = 'Resistencia'
efg = 'Ef.Generador'
disl = [res] + disl
disl2 = [efg] + disl2
for g in range(1):
graph2.append(disl)
graph2.append(disl2)
chart = ScatterChart()
chart.title = 'R[Ω] vs Eficiencia Generador'
chart.x_axis.title = 'Efm'
chart.y_axis.title = 'R[Ω]'
xvals = Reference(graph2, min_col=2, min_row=1, max_col=len(disl))
#for s in range(1,len(disl)): #10
values = Reference(graph2, 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]
graph2.add_chart(chart, "M1")
wb.save(filesheet)
def parse_statistics(logfile):
xl = pd.ExcelFile(logfile)
df = xl.parse("Sheet")
df = df.sort_values(by='Line Numbers')
writer = pd.ExcelWriter(logfile)
df.to_excel(writer, sheet_name='Sheet', index=False)
writer.save()
wb = openpyxl.load_workbook(logfile)
ws = wb.active
row_count = ws.max_row
column_count = ws.max_column
chart = ScatterChart()
chart.title = "Time upload domain names"
chart.style = 13
chart.x_axis.title = "Line numbers"
chart.y_axis.title = "Time, sec"
xvalues = Reference(ws, min_col=1, min_row=2, max_row=row_count)
color_choice = ['3F888F', 'D24D57']
for i in range(2, column_count + 1):
values = Reference(ws, min_col=i, min_row=1, max_row=row_count)
series = Series(values, xvalues, title_from_data=True)
series.marker.symbol = "diamond"
series.graphicalProperties.line.solidFill = color_choice[i - 2]
series.marker.graphicalProperties.line.solidFill = color_choice[i - 2]
series.marker.graphicalProperties.solidFill = color_choice[i - 2]
series.graphicalProperties.line.width = 20000
chart.series.append(series)
chart.legend.legendPos = 'b'
ws.add_chart(chart)
wb.save(logfile)
from openpyxl import Workbook
from openpyxl.chart import ScatterChart, Reference, Series
book = Workbook()
sheet = book.active
for i in range(1, 15):
sheet[f'A${i}']= i
for i in range(1, 15):
sheet[f'B${i}']= i*10
# GRAFICA
c1= ScatterChart()
c1.title = 'Grafico de Dispersion'
c1.style = 13
c1.y_axis.title = 'Eje Y'
c1.x_axis.title = 'Eje X'
xvalues = Reference(sheet,min_col=1,min_row=1,max_col=1,max_row=14)
yvalues = Reference(sheet,min_col=2,min_row=1,max_col=2,max_row=14)
ser = Series(yvalues,xvalues, title='recta')
c1.series.append(ser)
sheet.add_chart(c1, 'D3')
book.save('graficos.xlsx')
def make_table():
table_file_path = "/Users/AlexTheLion/Desktop/PythonCourse_SystemProgramming/Task_5/systems/systems.xlsx"
wb = Workbook()
ws = wb.active
system_names = ['Линейная', 'Динамическая', 'Любая']
cells = ['A', 'B', 'C', 'D', 'E', 'F']
linear = [(5, 10), (10, 20), (20, 40), (40, 80), (80, 160)]
dynamic = [(10, math.sin(10) + 1), (20, math.sin(20) + 1),
(40, math.sin(40) + 1), (80, math.sin(80) + 1),
(160, math.sin(160) + 1)]
anything = [(5, math.sin(10) + 1), (10, math.sin(20) + 1),
(20, math.sin(40) + 1), (40, math.sin(80) + 1),
(80, math.sin(160) + 1)]
ws.merge_cells('A1:B1')
ws.merge_cells('C1:D1')
ws.merge_cells('E1:F1')
counter = 0
for letter in ['A', 'C', 'E']:
ws[letter + '1'] = system_names[counter]
ws[letter + '1'].alignment = Alignment(horizontal='center',
vertical='center')
counter += 1
counter = 0
for cell in cells:
if counter % 2 == 0:
ws[cell + str(2)] = 'X'
ws[cell + str(2)].alignment = Alignment(horizontal='center',
vertical='center')
else:
ws[cell + str(2)] = 'Y'
ws[cell + str(2)].alignment = Alignment(horizontal='center',
vertical='center')
counter += 1
for i in range(5):
ws['A' + str(i + 3)] = (linear[i])[0]
ws['B' + str(i + 3)] = (linear[i])[1]
for i in range(5):
ws['C' + str(i + 3)] = (dynamic[i])[0]
ws['D' + str(i + 3)] = (dynamic[i])[1]
for i in range(5):
ws['E' + str(i + 3)] = (anything[i])[0]
ws['F' + str(i + 3)] = (anything[i])[1]
chart_l = ScatterChart()
chart_l.title = "Linear"
chart_l.style = 13
x_values = Reference(ws, min_col=1, min_row=3, max_row=7)
y_values = Reference(ws, min_col=2, min_row=3, max_row=7)
series = Series(y_values, x_values, title_from_data=True)
chart_l.series.append(series)
chart_d = ScatterChart()
chart_d.title = "Dynamic"
chart_d.style = 13
x_values = Reference(ws, min_col=3, min_row=3, max_row=7)
y_values = Reference(ws, min_col=4, min_row=3, max_row=7)
series = Series(y_values, x_values, title_from_data=True)
chart_d.series.append(series)
chart_a = ScatterChart()
chart_a.title = "Anything"
chart_a.style = 13
x_values = Reference(ws, min_col=5, min_row=3, max_row=7)
y_values = Reference(ws, min_col=6, min_row=3, max_row=7)
series = Series(y_values, x_values, title_from_data=True)
chart_a.series.append(series)
ws.add_chart(chart_l, 'A9')
ws.add_chart(chart_d, 'J9')
ws.add_chart(chart_a, 'S9')
wb.save(table_file_path)
def number_to_letter(n):
string = ""
while n > 0:
n, remainder = divmod(n - 1, 26)
string = chr(65 + remainder) + string
return string
num_of_params = 6
step = 9
# Может нужен класс графиков?
for params in range(1, (num_of_params + 1)):
chart = ScatterChart()
chart.title = None
chart.style = 13
chart.x_axis.title = 'Уровень воздействия, ед.'
chart.y_axis.title = str(ws[f"{number_to_letter(params+1)}1"].value)
create_graphs(start_index=1,
step=step,
num_of_param=params,
num_samples=10,
chart=chart,
style_dict=style_dict)
norms = MLine(x_column=1,
y_column=1 + params,
worksheet=ws,
style_tuple=('triangle', "FFFFFF"))
chart.series.append(norms.create_norms())
ws.add_chart(chart, f"{number_to_letter((params-1)*step+1)}21")
from openpyxl.chart import (
ScatterChart,
Reference,
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
def build_chart_single(ws, project_name, approval_point, td_data_dict):
chart = ScatterChart()
chart.title = str(project_name) + ' last approved business case: ' + str(
approval_point)
chart.style = 18
chart.x_axis.title = 'Time delta for each business case (year intervals)'
#chart.y_axis.title = 'Milestones'
chart.auto_axis = False
'''this code is necessary to calculate min chart value if its greater than zero'''
x_axis_min = min_value(project_name, td_data_dict)
if x_axis_min >= 0:
chart.x_axis.scaling.min = 0
elif x_axis_min < 0:
anchor = x_axis_min % 365
chart.x_axis.scaling.min = x_axis_min - anchor
chart.x_axis.scaling.max = max_value(
project_name, td_data_dict
) # max number (of days) in the x axis. calculated by max_value function
chart.y_axis.scaling.min = 0
chart.y_axis.scaling.max = 7 # hard coded for now - although minor issue as number of bc time deltas static
chart.height = 9 # default is 7.5
chart.width = 21 # default is 15
'''changes units on x and y axis'''
chart.x_axis.majorUnit = 365 # hard coded for now - minor issue as td will normally be in year intervals
# chart.y_axis.majorUnit = 1.0 testing to see if required
'''reverses y axis'''
#chart.x_axis.scaling.orientation = "minMax"
#chart.y_axis.scaling.orientation = "maxMin"
'''makes the x axis cross at the max y value'''
#chart.x_axis.crosses = 'max'
'''removes lable on y axis'''
chart.y_axis.delete = True
#TOD: sort styling
'''styling chart'''
'''formating for titles'''
#font = Font(typeface='Calibri')
#size = 1200 # 12 point size
#cp = CharacterProperties(latin=font, sz=size, b=True) # Bold
#pp = ParagraphProperties(defRPr=cp)
#rtp = RichText(p=[Paragraph(pPr=pp, endParaRPr=cp)])
#chart.x_axis.title.tx.rich.p[0].pPr = pp # x_axis title
#size_2 = 1400
#cp_2 = CharacterProperties(latin=font, sz=size_2, b=True)
#pp_2 = ParagraphProperties(defRPr=cp_2)
#rtp_2 = RichText(p=[Paragraph(pPr=pp_2, endParaRPr=cp_2)])
#chart.title.tx.rich.p[0].pPr = pp_2 # chart title
'''the below assigns series information to the data that has been placed in the chart.
old values are placed first show that they show behind the current values'''
for i in range(0, 18, 3):
xvalues = Reference(ws, min_col=7, min_row=i + 1, max_row=i + 1)
yvalues = Reference(ws, min_col=8, min_row=i + 1, max_row=i + 1)
series = Series(values=yvalues,
xvalues=xvalues,
title="Latest quarter")
chart.series.append(series)
s1 = chart.series[i]
s1.marker.symbol = "diamond"
s1.marker.size = 10
s1.marker.graphicalProperties.solidFill = "c9e243" # Marker filling greenish
s1.marker.graphicalProperties.line.solidFill = "c9e243" # Marker outline greenish
s1.graphicalProperties.line.noFill = True
xvalues = Reference(ws, min_col=7, min_row=i + 2, max_row=i + 2)
yvalues = Reference(ws, min_col=8, min_row=i + 2, max_row=i + 2)
series = Series(values=yvalues, xvalues=xvalues, title="Last quarter")
chart.series.append(series)
s1 = chart.series[i + 1]
s1.marker.symbol = "diamond"
s1.marker.size = 10
s1.marker.graphicalProperties.solidFill = "ced0ff" # Marker filling grey/blue
s1.marker.graphicalProperties.line.solidFill = "ced0ff" # Marker outline grey/blue
s1.graphicalProperties.line.noFill = True
xvalues = Reference(ws, min_col=7, min_row=i + 3, max_row=i + 3)
yvalues = Reference(ws, min_col=8, min_row=i + 3, max_row=i + 3)
series = Series(values=yvalues, xvalues=xvalues, title="Baseline")
chart.series.append(series)
s1 = chart.series[i + 2]
s1.marker.symbol = "diamond"
s1.marker.size = 10
s1.marker.graphicalProperties.solidFill = "8187ff" # Marker filling blue
s1.marker.graphicalProperties.line.solidFill = "8187ff" # Marker outline blue
s1.graphicalProperties.line.noFill = True
ws.add_chart(chart, "K2")
return ws
"Where do you want your chart locate in column?: ")
chart_location_row = input("Where do you want your chart locate in row?: ")
#TODO: reading files in different location
chart = ScatterChart()
xvalues = Reference(sheet,
min_col=switch_for_chart(location_for_name),
min_row=2,
max_row=(counter + 1))
yvalues = Reference(sheet,
min_col=switch_for_chart(location_for_score),
min_row=2,
max_row=(counter + 1))
series = Series(values=yvalues, xvalues=xvalues, title="2019 중간고사 성적")
chart.series.append(series)
chart.title = "Number Theory 2019 Middle Term"
chart.x_axis.title = "학생 이름"
chart.y_axis.title = "학생 성적"
sheet.add_chart(chart, str(chart_location_column) + str(chart_location_row))
num.save("num.xlsx")
[5, 30, 25],
[6, 25, 35],
[7, 20, 40],
]
for row in rows:
ws.append(row)
ch1 = ScatterChart()
xvalues = Reference(ws, min_col=1, min_row=2, max_row=7)
for i in range(2, 4):
values = Reference(ws, min_col=i, min_row=1, max_row=7)
series = Series(values, xvalues, title_from_data=True)
ch1.series.append(series)
ch1.title = "Default layout"
ch1.style = 13
ch1.x_axis.title = 'Size'
ch1.y_axis.title = 'Percentage'
ch1.legend.position = 'r'
ws.add_chart(ch1, "B10")
from copy import deepcopy
# Half-size chart, bottom right
ch2 = deepcopy(ch1)
ch2.title = "Manual chart layout"
ch2.legend.position = "tr"
ch2.layout = Layout(manualLayout=ManualLayout(
x=0.25,
return curve_dict[key]['DisplayName']
else:
return ''
header = [header_from_curve(curves['ctStatic'], i) for i in range(dim_count)]
#create Excel file
wb = Workbook()
ws = wb.active
ws.append(header)
for row in curve_data['data']:
ws.append(row)
chart = ScatterChart()
chart.title = "Indentation Curve"
chart.style = 13
chart.x_axis.title = 'Pd'
chart.y_axis.title = 'Fn'
xvalues = Reference(ws, min_col=3, min_row=2, max_row=curve_data['count'])
values = Reference(ws, min_col=2, min_row=2, max_row=curve_data['count'])
series = Series(values, xvalues, title_from_data=False)
chart.series.append(series)
ws.add_chart(chart, "O2")
wb.save("indentation_curve.xlsx")
#plot curve
c = np.transpose(np.array(curve_data['data']))
wb = Workbook()
ws = wb.active
rows = [
['Size', 'Batch 1', 'Batch 2'],
[2, 40, 30],
[3, 40, 25],
[4, 50, 30],
[5, 30, 25],
[6, 25, 35],
[7, 20, 40],
]
for row in rows:
ws.append(row)
chart = ScatterChart()
chart.title = "Scatter Chart"
chart.style = 13
chart.x_axis.title = 'Size'
chart.y_axis.title = 'Percentage'
xvalues = Reference(ws, min_col=1, min_row=2, max_row=7)
for i in range(2, 4):
values = Reference(ws, min_col=i, min_row=1, max_row=7)
series = Series(values, xvalues, title_from_data=True)
chart.series.append(series)
ws.add_chart(chart, "A10")
wb.save("scatter.xlsx")
[6, 25, 35],
[7, 20, 40],
]
for row in rows:
ws.append(row)
ch1 = ScatterChart()
xvalues = Reference(ws, min_col=1, min_row=2, max_row=7)
for i in range(2, 4):
values = Reference(ws, min_col=i, min_row=1, max_row=7)
series = Series(values, xvalues, title_from_data=True)
ch1.series.append(series)
ch1.title = "Default layout"
ch1.style = 13
ch1.x_axis.title = 'Size'
ch1.y_axis.title = 'Percentage'
ch1.legend.position = 'r'
ws.add_chart(ch1, "B10")
from copy import deepcopy
# Half-size chart, bottom right
ch2 = deepcopy(ch1)
ch2.title = "Manual chart layout"
ch2.legend.position = "tr"
ch2.layout=Layout(
manualLayout=ManualLayout(
ws = wb.active
rows = [
['Size', 'Batch 1', 'Batch 2'],
[2, 40, 30],
[3, 40, 25],
[4, 50, 30],
[5, 30, 25],
[6, 25, 35],
[7, 20, 40],
]
for row in rows:
ws.append(row)
chart = ScatterChart()
chart.title = "Scatter Chart"
chart.style = 13
chart.x_axis.title = 'Size'
chart.y_axis.title = 'Percentage'
xvalues = Reference(ws, min_col=1, min_row=2, max_row=7)
for i in range(2, 4):
values = Reference(ws, min_col=i, min_row=1, max_row=7)
series = Series(values, xvalues, title_from_data=True)
chart.series.append(series)
ws.add_chart(chart, "A10")
wb.save("scatter.xlsx")
from openpyxl.chart import (
ScatterChart,
Reference,
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)'