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'
