def create_series_bake(self, x_values: Reference, y_values: Reference, index: int) -> Series: series = Series(values=y_values, xvalues=x_values, title=self.fbg_names[index]) rgb_percent = RGBPercent(*hex_to_rgb(index)) series.marker = marker.Marker(symbol=MARKERS[index % len(MARKERS)], spPr=GraphicalProperties(solidFill=ColorChoice(rgb_percent))) series.graphicalProperties.line.noFill = True return series
def _add_series(chart: ScatterChart, index: int, temperature_column: int, series_parameters: SeriesParameters, cycle: int = None): start_row = CALIBRATION_START_ROW end_row = series_parameters.last_row extra_point_indexes = [] if cycle is not None and series_parameters.extra_point_temperatures is not None: for temperature in series_parameters.extra_point_temperatures: for row_number in range(start_row, end_row+1): column_letter = get_column_letter(temperature_column) column = "{}{}".format(column_letter, row_number) excel_temperature = series_parameters.data_sheet[column].value if math.isclose(temperature, excel_temperature, abs_tol=.5): extra_point_indexes.append(row_number - 1) for i in extra_point_indexes: if i < 2: start_row += 1 else: end_row -= 1 x_values = Reference(series_parameters.data_sheet, min_col=temperature_column, min_row=start_row, max_row=end_row) y_values = Reference(series_parameters.data_sheet, min_col=series_parameters.indexes[index] + 1, min_row=start_row, max_row=end_row) series_title = series_parameters.sub_type.get_series_title(cycle) series = Series(xvalues=x_values, values=y_values, title=series_title) series.marker = marker.Marker(MARKERS[index % len(MARKERS)]) series.marker.size = 12 chart.series.append(series) return index + 1
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 drawChartMem(stIdx, endIdx): c2 = LineChart() c2.title = sheet_ranges['A' + str(stIdx)].value[4:] + '_MEM' c2.style = 12 c2.x_axis.number_format = 'yyyymmdd' c2.x_axis.majorTimeUnit = "days" c2.legend.position = 'b' data = Reference(ws, min_col=5, min_row=stIdx, max_row=endIdx) series = Series(data, title="MEMORY PCTUSED MAX(%)") c2.append(series) data = Reference(ws, min_col=6, min_row=stIdx, max_row=endIdx) series = Series(data, title="MEMORY PCTUSED AVG(%)") c2.append(series) data = Reference(ws, min_col=7, min_row=stIdx, max_row=endIdx) series = Series(data, title="MEMORY_SWAPPCTUSED_MAX(%)") c2.append(series) data = Reference(ws, min_col=8, min_row=stIdx, max_row=endIdx) series = Series(data, title="MEMORY_SWAPPCTUSED_AVG(%)") c2.append(series) dates = Reference(ws, min_col=2, min_row=stIdx, max_row=endIdx) c2.set_categories(dates) s1 = c2.series[0] s1.graphicalProperties.line.solidFill = "5698d4" s2 = c2.series[1] s2.graphicalProperties.line.solidFill = "ed7d30" s3 = c2.series[2] s3.graphicalProperties.line.solidFill = "adadad" s4 = c2.series[3] s4.graphicalProperties.line.solidFill = "ffc100" ws.add_chart(c2, "P" + str(stIdx))
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 draw_bar_chart(writer, max_row): sheet = writer.sheets['死亡率'] date = sheet['A2'].value # 初始化我们的bar chart chart = BarChart() # 指定bar chart 的数据范围 chart_data = Reference(sheet, min_col=6, max_col=6, min_row=2, max_row=max_row-1) chart_series = Series(chart_data, title='截止{}死亡率'.format(date)) chart_series.graphicalProperties.solidFill = '5DD092' # Silver Tree # 指定x轴 x_axis_data = Reference(sheet, min_col=3, max_col=3, min_row=2, max_row=max_row-1) # chart 添加到 sheet里 chart.append(chart_series) chart.set_categories(x_axis_data) # 设置chart的样式 chart.height, chart.width = 14, 21 chart.title, chart.y_axis.title = '主要疫情地区死亡率', '死亡率' chart.y_axis.number_format = '0.0%' chart.legend.position = 't' # 画平均死亡率横线line chart line_chart = LineChart() line_data = Reference(sheet, min_col=7, max_col=7, min_row=2, max_row=max_row - 1) line_series = Series(line_data, title='除湖北外死亡率') line_chart.append(line_series) # 合并图表 chart += line_chart # 添加图表并保存 sheet.add_chart(chart, 'I1') writer.save()
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 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 copyToSheet(die, test): dieSheets = [] for sheet in range(len(clSheets)): if die.lower() in clSheets[sheet].lower() and test.lower( ) in clSheets[sheet].lower(): dieSheets.append(clSheets[sheet]) if len(dieSheets) > 0: print('Copying ' + die + ' ' + test) ws = lifetime.create_sheet(title=die + ' ' + test) pasteRow = 2 for sheet in range(len(dieSheets)): active = clWings.sheets[dieSheets[sheet]] for i in range(1, 22): for j in range(1, 3): ws.cell(row=i + pasteRow - 1, column=j).value = active.range( get_column_letter(j) + str(i + 32)).value pasteRow = pasteRow + 21 ws.cell(row=1, column=1).value = '[Cl2] (ppm)' ws.cell(row=1, column=2).value = 'Response (nA)' ws.cell(row=1, column=3).value = 'Calibration (nA)' ws.cell(row=1, column=5).value = 'Slope' ws.cell(row=2, column=5).value = 'Intercept' ws.cell( row=1, column=6).value = clWings.sheets[dieSheets[0]].range('B12').value ws.cell( row=2, column=6).value = clWings.sheets[dieSheets[0]].range('B13').value for i in range(2, ws.max_row): ws.cell(row=i, column=3).value = float(ws['F1'].value) * float( ws.cell(row=i, column=1).value) + float(ws['F2'].value) chart = ScatterChart() chart.title = str(die + ' ' + test + ' Lifetime (' + str(len(dieSheets)) + 'Calibrations)') chart.x_axis.title = '[Cl2] (ppm)' chart.y_axis.title = 'Response (nA)' xvalues = Reference(ws, min_col=1, min_row=2, max_row=ws.max_row) roamValues = Reference(ws, min_col=2, min_row=1, max_row=ws.max_row) roamSeries = Series(roamValues, xvalues, title_from_data=True) calValues = Reference(ws, min_col=3, min_row=1, max_row=ws.max_row) calSeries = Series( calValues, xvalues, title_from_data=True, ) roamSeries.marker = openpyxl.chart.marker.Marker('x') roamSeries.graphicalProperties.line.noFill = True chart.series.append(roamSeries) chart.series.append(calSeries) cs.add_chart(chart, 'A' + str(len(lifetime.worksheets) * 15 - 29))
def create_line_chart(self): c1 = LineChart() c1.title = chart_spend_title c1.style = 12 c1.y_axis.title = "Euros" c1.y_axis.crossAx = 500 c1.x_axis = DateAxis(crossAx=100) c1.x_axis.number_format = 'd-mm-yy' c1.x_axis.majorTimeUnit = "days" self.open_sheet(raw_chart_data_sheet) values = Reference(self.__working_sheet, min_row=1, max_row=self.__working_sheet.max_row, min_col=3, max_col=3) series = Series(values, title_from_data=True) c1.append(series) dates = Reference(self.__working_sheet, min_col=1, max_col=1, min_row=1, max_row=self.__working_sheet.max_row) c1.set_categories(dates) c1.height = 30 c1.width = 50 c2 = LineChart() c2.title = chart_topup_title c2.style = 12 c2.y_axis.title = "Euros" c2.y_axis.crossAx = 500 c2.x_axis = DateAxis(crossAx=100) c2.x_axis.number_category = 'Date' c2.x_axis.number_format = 'd-mm-yy' c2.x_axis.majorTimeUnit = "days" self.open_sheet(raw_chart_data_sheet) values = Reference(self.__working_sheet, min_row=1, max_row=self.__working_sheet.max_row, min_col=2, max_col=2) series = Series(values, title_from_data=True) c2.append(series) dates = Reference(self.__working_sheet, min_col=1, max_col=1, min_row=1, max_row=self.__working_sheet.max_row) c2.set_categories(dates) c2.height = 30 c2.width = 50 self.open_sheet(chart_sheet) self.__working_sheet.add_chart(c1, "C1") self.__working_sheet.add_chart(c2, "C60") self.__wb.save(self.__new_file_name) self.open_sheet(raw_data_sheet)
def create_new_chart(base_chart, data, data_points, categories): #←作成済みのチャートをコピーしてデータのみ書き換える series = Series(data, title_from_data=True) series.data_points = data_points chart = deepcopy(base_chart) chart.title = None chart.series.append(series) chart.set_categories(categories) 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 build_scatter_with_mean_stdev(xvalues_refs, yvalues_refs, stdev_refs, titles, errDir='y'): """Given x values, a list of y values, and the y values' corresponding stdev, it will return a scatter chart with stdev as error bars""" if len(yvalues_refs) != len(stdev_refs) != len(titles): raise ValueError("y-values and stdev list length must be the same") chart = ScatterChart() for xvalues, yvalues, stdev, title in zip(xvalues_refs, yvalues_refs, stdev_refs, titles): # convert reference to data source stdev_data_source = data_source.NumDataSource(numRef=data_source.NumRef(f=stdev)) error_bars = ErrorBars(errDir=errDir, errValType='cust', plus=stdev_data_source, minus=stdev_data_source) series = Series(yvalues, xvalues, title=title) series.errBars = error_bars chart.series.append(series) return chart
def create_avg_chart(workbook, worksheet, min_col, min_row, max_col, max_row): chart = LineChart() #Sets titles on graph chart.y_axis.title = '5S Audit Socre Average' chart.x_axis.title = '5S Weekly Audit Dates' chart.title = 'Past ' + string_weeks + ' Weeks Average Score For 539' #Gets the data from the table data = Reference(worksheet, min_col=min_col, min_row=min_row + 14, max_col=max_col, max_row=max_row) #Gets the Dates from the first row and stores them in a variable dates = Reference(worksheet, min_col=min_col, min_row=min_row, max_col=max_col, max_row=max_row - 14) #Creates a series using the data collected from the specified rows of the table series = Series(data, title='Building 539') #Sets the marker on the chart to be a triangle series.marker.symbol = "triangle" #Adds the data and date labels to the chart chart.append(series) chart.set_categories(dates) chart.dataLabels = DataLabelList() chart.dataLabels.showVal = True #Adds the chart to the worksheet at cell M18 and saves the file worksheet.add_chart(chart, 'K18') workbook.save(wb_name)
def createGraph3(disl, disl3): #,disl2,disl3): res = 'Resistencia' eft = 'Ef.Turbina' disl = [res] + disl disl3 = [eft] + disl3 #for i in range(1,len(disl)): for g in range(1): graph3.append(disl) graph3.append(disl3) chart = ScatterChart() chart.title = 'R[Ω] vs Eficiencia turbina' chart.x_axis.title = 'Eft' chart.y_axis.title = 'R' xvals = Reference(graph3, min_col=2, min_row=1, max_col=len(disl)) #for s in range(1,len(disl)): #10 values = Reference(graph3, min_col=2, min_row=2, max_col=len(disl)) series = Series(values, xvals, title_from_data=True) chart.series.append(series) #chart.add_data(values) #s = chart.series[1] graph3.add_chart(chart, "M1") wb.save(filesheet)
def create_scatter(df, wb, sheet_name): df_columns = df.columns.tolist() ws = wb[sheet_name] chart = ScatterChart() chart.title = sheet_name chart.height = 15 chart.width = 30 chart.x_axis.scaling.max = max(df['timestamps']) chart.x_axis.scaling.min = min(df['timestamps']) chart.x_axis.title = df_columns[1] for i in df_columns: if i in sheet_name: chart.x_axis.title = i chart.y_axis.title = 'intensity_of_emotion' start_row = 2 end_row = len(df) + 1 x_values = Reference(ws, min_col=1, min_row=start_row, max_row=end_row) for i in range(df_columns.index('happy') + 1, df_columns.index('confused') + 2): values = Reference(ws, min_col=i, min_row=1, max_row=end_row) series = Series(values, x_values, title_from_data=True) chart.series.append(series) letter = get_column_letter(len(df_columns) + 2) ws.add_chart(chart, f"{letter}1")
def addChart(groupLength1): print('Printing Chart...') # print('grouplength1: '+str(len(groupLength1))) chart = ScatterChart() chart.title = "Capacity Vs Voltage" chart.style = 13 chart.x_axis.title = 'Capacity' chart.y_axis.title = 'Voltage' le = (len(groupLength1) * 4) + len(groupLength1) # print("The sizes of each grp" + str(groupLength1[0])) k = 0 m = 1 # print("list siz = " + str(len(groupLength1))) # print("le = " + str(le)) for j in range(4, le, 5): print("j = " + str(j)) xvalues = Reference(AutoExcel.sh1_O, min_col=j, min_row=1, max_row=groupLength1[k]) values = Reference(AutoExcel.sh1_O, min_col=m, min_row=1, max_row=groupLength1[k]) series = Series(values, xvalues, title_from_data=True) chart.series.append(series) k += 1 m += 5 AutoExcel.sh1_O.add_chart(chart, 'L3') out_f = "C:\\Users\\Vignesh\\PycharmProjects\\AutoExcel\\src\\uploads\\" + AutoExcel.outputFile + '.xlsx' AutoExcel.wb_O.save(out_f)
def sample_bar_chart(self, title, matrix): """ more details https://openpyxl.readthedocs.io/en/2.5/charts/chart_layout.html#size-and-position """ rows, cols = len(matrix), len(matrix[0]) wb = openpyxl.Workbook() sheet = wb.active for data_row in matrix: sheet.append(data_row) chart = ScatterChart() x = Reference(sheet, min_col=1, min_row=1, max_row=rows) for i in range(2, cols + 1): y = Reference(sheet, min_col=i, min_row=1, max_row=rows) s = Series(y, x, title_from_data=True) chart.series.append(s) chart.title = title chart.style = 12 chart.x_axis.title = "X" chart.y_axis.title = "Y" chart.legend.position = 'r' sheet.add_chart(chart, 'B12') wb.save("sample_chart.xlsx")
def 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 produce(filename, name, date): wb = xl.load_workbook(filename) rez = False for sheet in wb: if sheet.title == name + date: ws = wb[name + date] rez = True break if not rez: ws = wb.create_sheet(title=name + date) for i in range(1, 13): ws.cell(row=1, column=i).value = i ws.cell(row=2, column=i).value = 0 for prod in IterRowDict(wb, "Products"): for sale in IterRowDict(wb, "Sales"): if prod["Name"] == name and sale["Date"][6:] == date and prod[ "id"] == prod["id"]: amount = int(prod["Price"]) * int(sale["Quantity"]) ws.cell(row=2, column=int(sale["Date"][3:5])).value = amount ws = wb[name + date] chart = BarChart() x = Reference(ws, min_col=1, max_col=12, min_row=1) y = Reference(ws, min_col=1, max_col=12, min_row=2) chart.append(Series(y)) chart.set_categories(x) ws.add_chart(chart, "A10") wb.save(filename)
def create_annual_chart(worksheet=None, year=0, min_row=1, max_row=1): chart = ScatterChart() # sets the chart styling chart.title = f'{year}' chart.x_axis.title = 'Month' chart.y_axis.title = 'Amount' chart.legend.position = 'b' chart.height = 7.7 chart.width = 21.5 xvalues = Reference(worksheet=worksheet, min_col=2, min_row=min_row + 1, max_row=max_row) for col in range(3, 6): values = Reference(worksheet=worksheet, min_col=col, min_row=min_row, max_row=max_row) series = Series(values, xvalues, title_from_data=True) chart.series.append(series) return chart
def createGraphs(disl, disl1): #,disl2,disl3): #for i in range(1,len(disl)): res = 'Resistencia' rps = 'RPM' disl = [res] + disl disl1 = [rps] + disl1 for g in range(1): graph.append(disl) graph.append(disl1) chart = ScatterChart() chart.title = 'R[Ω] vs RPM' chart.x_axis.title = 'RPM' chart.y_axis.title = 'R[Ω]' xvals = Reference(graph, min_col=2, min_row=1, max_col=len(disl)) #for s in range(1,len(disl)): #10 values = Reference(graph, min_col=2, min_row=2, max_col=len(disl)) series = Series(values, xvals, title_from_data=True) chart.series.append(series) #chart.add_data(values) #s = chart.series[1] graph.add_chart(chart, "M1") wb.save(filesheet)
def create_graph(result_file, result_book, sheet_name_active, current_format_sheet): # Valiable Assignment book_name_will_plot_graph = os.path.basename(result_file) sheet_name_will_plot_graph = result_book[sheet_name_active] max_row_in_sheet_name = sheet_name_will_plot_graph.max_row # Assign value in X-axis and Y-axis scatter_chart = ScatterChart() y_values = Reference(sheet_name_will_plot_graph, min_row=3, max_row=max_row_in_sheet_name, min_col=3) x_values = Reference(sheet_name_will_plot_graph, min_row=3, max_row=max_row_in_sheet_name, min_col=2) series = Series(y_values, x_values, title=None, title_from_data=False) scatter_chart.series.append(series) # Adjust Scatter Element scatter_chart.y_axis.scaling.min = 0 scatter_chart.legend = None scatter_chart.y_axis.number_format = "0.00" # Paste Scatter_chart current_format_sheet.add_chart(scatter_chart, "A5") # Setting width and height of scatter_chart scatter_chart.width = 14 scatter_chart.height = 8 return scatter_chart
def draw_pattern_scatterchart(data_work_sheet, data_rows_seq, data_columns_list, chart_position, chart_title): chart = ScatterChart() chart.title = str(chart_title) chart.legend.position = 't' #chart.x_axis.title = 'samples from:' + data_work_sheet.title[0:21] #chart.x_axis.title = u'样本:' + data_work_sheet.title[0:25] + '..' chart.x_axis.title = data_work_sheet.title + '..' #chart.x_axis.txPr = #chart_title_font = Font(name='Arial Narrow',size=12) chart.y_axis.title = 'lantency(ms)' #chart.style = 13 #chart.layout = #chart.graphical_properties = #chart.varyColors = 'blue' #chart.scatterStyle = 'marker' #same sheet same sample width of chart, x ray values, get min and max row from pattern_col xvalues = Reference(data_work_sheet, min_col=1, min_row=data_rows_seq[0], max_row=data_rows_seq[-1]) for i in data_columns_list : #print(data_work_sheet.cell(row=1,column=i).value) line_title = data_work_sheet.cell(row=1,column=i).value yvalues = Reference(data_work_sheet, min_col=i, min_row=data_rows_seq[0], max_row=data_rows_seq[-1]) series = Series(yvalues, xvalues, title=line_title) chart.series.append(series) #print(chart.series[0]) # Style the lines for i in range(len(data_columns_list)) : s1 = chart.series[i] s1.marker.symbol = "circle" #s1.marker.symbol = "triangle" #s1.marker.graphicalProperties.solidFill = "FF0000" # Marker filling #s1.marker.graphicalProperties.line.solidFill = "FF0000" # Marker outline s1.graphicalProperties.line.noFill = True # print(wb.chartsheets) wb['ScatterChart'].add_chart(chart, chart_position)
def create_charts_multiple(worksheet): chart = LineChart() #Sets titles on graph chart.y_axis.title = '5S Audit Socres' chart.x_axis.title = '5S Weekly Audit Dates' chart.title = 'Past ' + string_weeks + ' Weeks Scores For All Zones' for i in range(1, 13): #Gets the data from the table data = Reference(worksheet, min_col=2, min_row=1 + i, max_col=weeks + 1, max_row=1 + i) #Gets the Dates from the first row and stores them in a variable dates = Reference(worksheet, min_col=2, min_row=1, max_col=weeks + 1, max_row=1) #Creates a series using the data collected from the specified rows of the table series = Series(data, title='Zone ' + str(i)) #Adds the data and date labels to the chart chart.append(series) chart.set_categories(dates) #chart.dataLabels = DataLabelList() #chart.dataLabels.showVal = True #Adds the chart to the worksheet at cell A10 and saves the file chart.height = 10 chart.width = 20 worksheet.add_chart(chart, 'A18') workbook.save(wb_name)
def create_norms(self): x_values = Reference(ws, min_col=self.x_column, min_row=2, max_row=3) y_values = Reference(ws, min_col=self.y_column, min_row=2, max_row=3) norms = Series(y_values, x_values) norms.marker.symbol = 'triangle' norms.marker.size = 3 norms.marker.graphicalProperties.solidFill = "FFFFFF" return norms
def get_throughput_records_series(self, ws, ws_name): cols = self.get_columns_from_worksheet(ws) return Series(Reference(ws, min_col=cols["Records/Sec"], min_row=2, max_col=cols["Records/Sec"], max_row=ws.max_row), title=ws_name + "-Records/Sec")
def graph_bake(self, parameters: BakingGraphParameters): last_row = parameters.num_rows + 1 start_row = 2 start_column = 2 x_axis_title = "{} Time from start (hr)".format(u"\u0394") y_axis_title = "{} Wavelength (pm)".format(u"\u0394") y_axis_title_sensitivity = "Drift (mK)" y_axis_title_temperature = "{} Temperature (K)".format(u"\u0394") x_values = Reference(parameters.data_sheet, min_col=start_column, min_row=start_row, max_row=last_row) for i, fbg_name in enumerate(self.fbg_names): chart_title = "{} {} Wavelength (pm) vs. {} Time from start ; {}"\ .format(fbg_name, u"\u0394", u"\u0394", self.excel_file_name) chart = ScatterChart() y_values = Reference(parameters.data_sheet, min_col=self._get_baking_wavelength_column(i), min_row=start_row, max_row=last_row) series = self.create_series_bake(x_values, y_values, i) chart.series.append(series) trend_values = Reference(parameters.data_sheet, min_col=parameters.trend_line_indexes[i] + 1, min_row=start_row, max_row=last_row) series = Series(trend_values, x_values, title="Trend (pm)") chart.series.append(series) format_chart(chart, x_axis_title, y_axis_title, chart_title) parameters.chart_sheet.add_chart(chart, "B{}".format(30*i + 2)) if self._valid_sensitivities(): sensitivity_chart_title = "{} drift (mK) vs. {} Time from start; {}"\ .format(fbg_name, u"\u0394", self.excel_file_name) sensitivity_chart = ScatterChart() sensitivity_values = Reference(parameters.data_sheet, min_col=parameters.sensitivity_indexes[i] + 1, min_row=start_row, max_row=last_row) series = Series(sensitivity_values, x_values, title="Drift (mK)") sensitivity_chart.series.append(series) format_chart(sensitivity_chart, x_axis_title, y_axis_title_sensitivity, sensitivity_chart_title) parameters.chart_sheet.add_chart(sensitivity_chart, "V{}".format(30 * i + 2)) temperature_graph_start_column = "AO" if self._valid_sensitivities() else "V" chart_title = "{0} Temperature vs. {0} Time from start; {1}".format(u"\u0394", self.excel_file_name) chart = ScatterChart() values = Reference(parameters.data_sheet, min_col=2, min_row=start_row, max_row=last_row) series = Series(values, x_values, title="{} Temperature (K)".format(u"\u0394")) chart.series.append(series) format_chart(chart, x_axis_title, y_axis_title_temperature, chart_title) parameters.chart_sheet.add_chart(chart, "{}2".format(temperature_graph_start_column))
def contoh_gauge_chart(self, ws): data = [ ["Donut", "Pie"], [25, 75], [50, 1], [25, 124], [100], ] # based on http://www.excel-easy.com/examples/gauge-chart.html for row in data: ws.append(row) # First chart is a doughnut chart c1 = DoughnutChart(firstSliceAng=270, holeSize=50) c1.title = "Code coverage" c1.legend = None ref = Reference(ws, min_col=1, min_row=2, max_row=5) s1 = Series(ref, title_from_data=False) slices = [DataPoint(idx=i) for i in range(4)] slices[0].graphicalProperties.solidFill = "FF3300" # red slices[1].graphicalProperties.solidFill = "FCF305" # yellow slices[2].graphicalProperties.solidFill = "1FB714" # green slices[3].graphicalProperties.noFill = True # invisible s1.data_points = slices c1.series = [s1] # Second chart is a pie chart c2 = PieChart(firstSliceAng=270) c2.legend = None ref = Reference(ws, min_col=2, min_row=2, max_col=2, max_row=4) s2 = Series(ref, title_from_data=False) slices = [DataPoint(idx=i) for i in range(3)] slices[0].graphicalProperties.noFill = True # invisible slices[1].graphicalProperties.solidFill = "000000" # black needle slices[2].graphicalProperties.noFill = True # invisible s2.data_points = slices c2.series = [s2] c1 += c2 # combine charts ws.add_chart(c1, "D1") # Second chart is a pie chart c2 = PieChart(firstSliceAng=270) # c2.legend = None ref = Reference(ws, min_col=2, min_row=2, max_col=2, max_row=4) s2 = Series(ref, title_from_data=False) # slices = [DataPoint(idx=i) for i in range(3)] # slices[0].graphicalProperties.noFill = True # invisible # slices[1].graphicalProperties.solidFill = "000000" # black needle # slices[2].graphicalProperties.noFill = True # invisible # s2.data_points = slices c2.series = [s2] ws.add_chart(c2, "D20")
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)
] # based on http://www.excel-easy.com/examples/gauge-chart.html wb = Workbook() ws = wb.active for row in data: ws.append(row) # First chart is a doughnut chart c1 = DoughnutChart(firstSliceAng=270, holeSize=50) c1.title = "Code coverage" c1.legend = None ref = Reference(ws, min_col=1, min_row=2, max_row=5) s1 = Series(ref, title_from_data=False) slices = [DataPoint(idx=i) for i in range(4)] slices[0].graphicalProperties.solidFill = "FF3300" # red slices[1].graphicalProperties.solidFill = "FCF305" # yellow slices[2].graphicalProperties.solidFill = "1FB714" # green slices[3].graphicalProperties.noFill = True # invisible s1.data_points = slices c1.series = [s1] # Second chart is a pie chart c2 = PieChart(firstSliceAng=270) c2.legend = None ref = Reference(ws, min_col=2, min_row=2, max_col=2, max_row=4)
data = Reference(ws, min_col=2, min_row=1, max_row=5) chart.add_data(data, titles_from_data=True) chart.set_categories(labels) chart.title = "Doughnuts sold by category" chart.style = 26 # Cut the first slice out of the doughnut slices = [DataPoint(idx=i) for i in range(4)] plain, jam, lime, chocolate = slices chart.series[0].data_points = slices plain.graphicalProperties.solidFill = "FAE1D0" jam.graphicalProperties.solidFill = "BB2244" lime.graphicalProperties.solidFill = "22DD22" chocolate.graphicalProperties.solidFill = "61210B" chocolate.explosion = 10 ws.add_chart(chart, "E1") from copy import deepcopy chart2 = deepcopy(chart) chart2.title = None data = Reference(ws, min_col=3, min_row=1, max_row=5) series2 = Series(data, title_from_data=True) series2.data_points = slices chart2.series.append(series2) ws.add_chart(chart2, "E17") wb.save("doughnut.xlsx")