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