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 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 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_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 line(wb): ws = wb.create_sheet(5, "Line") for i in range(1, 5): ws.append([i]) chart = LineChart() values = Reference(ws, (0, 0), (3,0)) series = Series(values) chart.append(series) ws.add_chart(chart)
def _add_chart(ws, xs, ys, title, loc, curve_labels=None): chart = LineChart() for y, label in zip(ys, curve_labels): ser = Series(y, title=label) chart.append(ser) chart.set_categories(xs) chart.title = title ws.add_chart(chart, loc)
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 plot_line_chart(): wb = xl.load_workbook('C:\\Resources\\Program_Xl_Sheets\\Groceriesptfinal.xlsx') # load xl workbook sheet = wb['dATA'] # get the worksheet for i in range(2, 10): cell = sheet['f'+str(i)] sheet['a'+str(i)] = cell.value values = Reference( sheet, min_row=2, max_row=10, min_col=1, max_col=1 ) series_object = xl.chart.Series(values, title="Yearly Revenue") chart = LineChart() chart.append(series_object) sheet.add_chart(chart, 'j2') wb.save('C:\\Resources\\Program_Xl_Sheets\\Groceriesptfinal.xlsx')
def drawChartCPU(stIdx, endIdx): c1 = LineChart() c1.title = sheet_ranges['A' + str(stIdx)].value[4:] + '_CPU' c1.style = 12 c1.x_axis.number_format = 'yyyymmdd' c1.x_axis.majorTimeUnit = "days" c1.legend.position = 'b' data = Reference(ws, min_col=3, min_row=stIdx, max_row=endIdx) series = Series(data, title="CPU BUSY MAX(%)") c1.append(series) data = Reference(ws, min_col=4, min_row=stIdx, max_row=endIdx) series = Series(data, title="CPU BUSY AVG(%)") c1.append(series) dates = Reference(ws, min_col=2, min_row=stIdx, max_row=endIdx) c1.set_categories(dates) s1 = c1.series[0] s1.graphicalProperties.line.solidFill = "5698d4" s2 = c1.series[1] s2.graphicalProperties.line.solidFill = "ed7d30" ws.add_chart(c1, "K" + str(stIdx))
def fill_workbook(self): counter = 1 a=self.has_workbook() if a==True: wb= openpyxl.load_workbook(self.get_workbook()) else: wb = Workbook() wb.save(self.get_workbook()) b=wb.active b.title = self.user.get_name() while b['B'+str(counter)].value!=b['C1'].value: counter=counter+1 localtime = time.asctime( time.localtime(time.time()) ) b['B'+str(counter)]=float(self.web.get_price()) b['A'+str(counter)]=str(localtime) chart = LineChart() prices = Reference(b,min_col=2,max_col=2,min_row=1,max_row=int(counter)) times = Reference(b,min_col=1,max_col=1,min_row=1,max_row=int(counter)) series = Series(prices, title=self.user.get_name()) chart.append(series) chart.set_categories(times) chart.__name__= self.user.get_name()+" Prices" b.add_chart(chart) wb.save(self.get_workbook())
def draw_ordered_ranks(worksheet): chart = LineChart() chart.title = 'Ranks in descending order' chart.x_axis.title = 'Function' chart.y_axis.title = 'Rank' chart.style = 11 i = 0 while True: a = alpha(worksheet, i) if a is None: break col = 3 * i + 2 data = Reference(worksheet, min_col=col, max_col=col, min_row=2, max_row=min(worksheet.max_row, 100)) series = Series(data, title='a=' + a) series.smooth = True series.graphicalProperties.line.width = 20000 chart.append(series) i += 1 worksheet.add_chart(chart, 'D25')
print(ws.max_column, ws.max_row, type(ws)) print(ws['A1'].value, ws['B1'].value, ws['C1'].value) a3 = ws.cell(row=3, column=3) print(a3.value) print('A1 - > A2...순서대로 읽기') for row in ws.rows: for cell in row: print(cell.value, ' ', end='') print('') excel_file2 = file_dir / 'sample2.xlsx' wb = openpyxl.Workbook() # sheet지정 index->위치 title->sheet의 명 ws = wb.create_sheet(index=0, title='신규 sheet') ws['a1'] = 100 wb.save(filename=excel_file2) excel_file3 = file_dir / 'sample3_chart.xlsx' wb = openpyxl.Workbook() ws = wb.create_sheet(index=0, title='챠트 sheet') for i in range(10): ws.append([random.randint(1, 10)]) values = Reference(ws, min_row=1, min_col=1, max_row=10, max_col=1) series = Series(values, title='샘플 챠트') chart = LineChart() chart.append(series) ws.add_chart(chart) wb.save(filename=excel_file3)
m.insert(0, sum(m)) m.append(str("gain")) p.insert(0, sum(p)) p.append(str("view")) p2.insert(0, "?") p2.append(str("log10(view)")) final = list(zip(*[t, m, p, p2]))[::-1] for i in range(len(final)): ws.append(final[i]) c1 = LineChart() c1.title = "日期-播放量" c1.style = 2 c1.x_axis.title = '日期' c2 = LineChart() c2.title = "日期-log10(播放量)" c2.style = 2 c2.x_axis.title = '日期' dates = Reference(ws, min_col=1, min_row=2, max_col=1, max_row=ws.max_row - 1) data = Reference(ws, min_col=3, min_row=2, max_col=3, max_row=ws.max_row - 1) lgdata = Reference(ws, min_col=4, min_row=2, max_col=4, max_row=ws.max_row - 1) seriesObj1 = Series(data, title='播放量') seriesObj2 = Series(lgdata, title='log10(播放量)') c1.append(seriesObj1) c1.set_categories(dates) c2.append(seriesObj2) c2.set_categories(dates) ws.add_chart(c1, "F1") ws.add_chart(c2, "F16") wb.save('D:/播放量可视化.xlsx')
col_max = cws.max_column row_max = int(cws.max_row) mm_cs = cwb.create_chartsheet() mm_cs.title = "Chart_" + dws + "_MM" DT_col = int(col_max) - 13 AC_col = int(col_max) - 12 timings = Reference(cws, min_col=2, min_row=5, max_row=row_max) mm_r_ave = Reference(cws, min_col=DT_col, min_row=5, max_row=row_max) mm_c_ave = Reference(cws, min_col=AC_col, min_row=5, max_row=row_max) mm_chart = LineChart() mm_r_ave_ser = Series(mm_r_ave, title="Dial Testing Score") mm_c_ave_ser = Series(mm_c_ave, title="Mean Score") mm_chart.append(mm_r_ave_ser) mm_chart.append(mm_c_ave_ser) mm_chart.set_categories(timings) mm_cs.add_chart(mm_chart) gender_cs = cwb.create_chartsheet() gender_cs.title = "Chart_" + dws + "_SEX" male_r_col = int(col_max) - 11 male_ave = int(col_max) - 10 female_r_col = int(col_max) - 9 female_ave = int(col_max) - 8 gender_male_r_ave = Reference(cws, min_col=male_r_col, min_row=5, max_row=row_max) gender_female_r_ave = Reference(cws, min_col=female_r_col, min_row=5, max_row=row_max) gender_chart = LineChart() gender_male_r_ave_ser = Series(gender_male_r_ave, title="Male") gender_female_r_ave_ser = Series(gender_female_r_ave, title="Female")
sheet.cell(row=1, column=28).value = "ΔAMC比C0" for n in range(index + 1, sheet.max_row + 1): if sheet.cell(row=n, column=12).value is None: sheet.cell(row=n, column=28).value = "" else: sheet.cell(row=n, column=28).value = "=L" + str(n) + "-" + str(sheet.cell(row=index, column=12).value) sheet.column_dimensions[get_column_letter(3)].width = 13 sheet.column_dimensions[get_column_letter(15)].width = 13 # TODO: make charts chart1 = LineChart() data1 = Reference(sheet, min_col=4, min_row=3, max_row=sheet.max_row) series1 = Series(data1, title='1') chart1.append(series1) data2 = Reference(sheet, min_col=5, min_row=3, max_row=sheet.max_row) series2 = Series(data2, title='2') chart1.append(series2) data5 = Reference(sheet, min_col=8, min_row=3, max_row=sheet.max_row) series3 = Series(data5, title='5') chart1.append(series3) data8 = Reference(sheet, min_col=11, min_row=3, max_row=sheet.max_row) series4 = Series(data8, title='8') chart1.append(series4) data9 = Reference(sheet, min_col=12, min_row=3, max_row=sheet.max_row) series5 = Series(data9, title='9') chart1.append(series5) data22 = Reference(sheet, min_col=26, min_row=3, max_row=sheet.max_row) series6 = Series(data22, title='22') chart1.append(series6)
def format_worksheet(worksheet, scenario_note, imei, scenario_start_time, rssi_power_list, rssi_average_list): # FUNCTION FOR SETTING UP THE EXCEL WORKSHEET LAYOUT, FORMAT AND CHART line_chart = LineChart() line_chart.title = "RSSI Power Graph" line_chart.y_axis.title = 'RSSI Level' line_chart.x_axis.title = 'Data Count' rssi_power_data = Reference(worksheet, min_col=9, min_row=8, max_col=9, max_row=len(rssi_power_list) + 7) power_series = Series(rssi_power_data, title="Actual RSSI Power") line_chart.append(power_series) rssi_average_data = Reference(worksheet, min_col=15, min_row=8, max_col=15, max_row=len(rssi_average_list) + 7) average_series = Series(rssi_average_data, title="Averaged RSSI Power") line_chart.append(average_series) worksheet.add_chart(line_chart, "Q6") worksheet.merge_cells('A1:O1') worksheet.merge_cells('A3:O3') worksheet.merge_cells('A4:O4') worksheet.merge_cells('A6:C6') worksheet.merge_cells('E6:I6') worksheet.merge_cells('K6:O6') worksheet['A3'].fill = PatternFill(start_color="1e90ff", end_color="1e90ff", fill_type="solid") worksheet['A6'].fill = PatternFill(start_color="1e90ff", end_color="1e90ff", fill_type="solid") worksheet['A7'].fill = PatternFill(start_color="1e90ff", end_color="1e90ff", fill_type="solid") worksheet['B7'].fill = PatternFill(start_color="1e90ff", end_color="1e90ff", fill_type="solid") worksheet['C7'].fill = PatternFill(start_color="1e90ff", end_color="1e90ff", fill_type="solid") worksheet['E6'].fill = PatternFill(start_color="1e90ff", end_color="1e90ff", fill_type="solid") worksheet['E7'].fill = PatternFill(start_color="1e90ff", end_color="1e90ff", fill_type="solid") worksheet['F7'].fill = PatternFill(start_color="1e90ff", end_color="1e90ff", fill_type="solid") worksheet['G7'].fill = PatternFill(start_color="1e90ff", end_color="1e90ff", fill_type="solid") worksheet['H7'].fill = PatternFill(start_color="1e90ff", end_color="1e90ff", fill_type="solid") worksheet['I7'].fill = PatternFill(start_color="1e90ff", end_color="1e90ff", fill_type="solid") worksheet['K6'].fill = PatternFill(start_color="1e90ff", end_color="1e90ff", fill_type="solid") worksheet['K7'].fill = PatternFill(start_color="1e90ff", end_color="1e90ff", fill_type="solid") worksheet['L7'].fill = PatternFill(start_color="1e90ff", end_color="1e90ff", fill_type="solid") worksheet['M7'].fill = PatternFill(start_color="1e90ff", end_color="1e90ff", fill_type="solid") worksheet['N7'].fill = PatternFill(start_color="1e90ff", end_color="1e90ff", fill_type="solid") worksheet['O7'].fill = PatternFill(start_color="1e90ff", end_color="1e90ff", fill_type="solid") worksheet['A1'].alignment = Alignment(horizontal='center') worksheet['A3'].alignment = Alignment(horizontal='center') worksheet['A4'].alignment = Alignment(horizontal='center') worksheet['A6'].alignment = Alignment(horizontal='center') worksheet['D6'].alignment = Alignment(horizontal='center') worksheet['E6'].alignment = Alignment(horizontal='center') worksheet['J6'].alignment = Alignment(horizontal='center') worksheet['K6'].alignment = Alignment(horizontal='center') worksheet['A3'].font = Font(bold=True) worksheet['A6'].font = Font(bold=True) worksheet['K6'].font = Font(bold=True) worksheet['A1'].font = Font(bold=True) worksheet['E6'].font = Font(bold=True) worksheet['A1'] = 'RSSI PARSE RESULTS' worksheet['A3'] = 'Scenario notes' worksheet['A4'] = scenario_note worksheet['A6'] = 'Test details' worksheet['A7'] = 'Target IMEI' worksheet['A8'] = imei worksheet['B7'] = 'Start time' worksheet['B8'] = scenario_start_time worksheet['C7'] = 'Duration' worksheet['E6'] = 'Actual RSSI Power' worksheet['E7'] = 'Data count' worksheet['E8'] = '=COUNT(I8:I1048576)' worksheet['F7'] = 'Average' worksheet['F8'] = '=AVERAGE(I8:I1048576)' worksheet['G7'] = 'Max' worksheet['G8'] = '=MAX(I8:I1048576)' worksheet['H7'] = 'Min' worksheet['H8'] = '=MIN(I8:I1048576)' worksheet['I7'] = 'Data' worksheet['K6'] = 'Averaged RSSI Power' worksheet['K7'] = 'Data count' worksheet['K8'] = '=COUNT(O8:O1048576)' worksheet['L7'] = 'Average' worksheet['L8'] = '=AVERAGE(O8:O1048576)' worksheet['M7'] = 'Max' worksheet['M8'] = '=MAX(O8:O1048576)' worksheet['N7'] = 'Min' worksheet['N8'] = '=MIN(O8:O1048576)' worksheet['O7'] = 'Data'