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 _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 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 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 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 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 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 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 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 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 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 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 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 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'))
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 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 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 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 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 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")
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")
[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, y=0.25,
