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,