Customize your burndown chart

In our daily work, We often need to specify a variety of reports. A beautiful and illustrated report can instantly brighten our work. as you known there are a lot of methods to do this thing, such as Excel, PPT, and sorts of statistic app. but today, I wanna to introduce you another approach to reach it, it’s python pyecharts. use python pyecharts we can customize sorts of report and it offers variety of charts to help us complete sorts of statistical illustrated. Okay, let’s see how I can use Python to automatically generate burndown diagrams -

Okay，without further ado, as usual, let me show my code on how to implement this scenario -

Step1 - Import the essential packages

# -*- coding: utf-8 -*-
# @Project Name: PyRepository
# @File: dryRun_pyecharts.py
# @Author: Alan.Yuan
# @Time: 2022/1/15 16:59
# Press Shift+F10 to execute it or replace it with your code, in my laptop you should use this combine keys to execute : "shift + fn + F10"
# Press Double Shift to search everywhere for classes, files, tool windows, actions, and settings.
# --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
# 导入柱状图-Bar
from pyecharts.charts import Bar, Line, Grid #导入相关图形的构造方法
from pyecharts import options as opts #引入配置项入口
from pyecharts.globals import ThemeType

import win32com, random
from win32com.client import Dispatch, constants, DispatchEx

Step2 - Prepare your test data
Usually, the test data points to the initialization pyecharts parameters, You can assign directly or you assign them from a file, here I get the initialization from a specified excel file -

reportFile = r'C:\Users\ayuan2\Desktop\Alan_Studio_Mini App\Test Scenario_R1.3.xlsx'
xlApp = win32com.client.Dispatch("Excel.Application")
xlOpenBook = xlApp.Workbooks.Open(reportFile)

temp = xlOpenBook.Worksheets('Report Statistic').Range("G4:V4")
getDateRangeList = []
for i in range(0, len(temp)):
    getDateRangeList.append(str(temp[i]))
print(getDateRangeList)

temp = xlOpenBook.Worksheets('Report Statistic').Range("G5:V5")
getPlanedRangeList = []
for i in range(0, len(temp)):
    getPlanedRangeList.append(str(temp[i]))
print(getPlanedRangeList)

temp = xlOpenBook.Worksheets('Report Statistic').Range("G6:V6")
getActualRangeList = []
for i in range(0, len(temp)):
    getActualRangeList.append(str(temp[i]))
print(getActualRangeList)

temp = xlOpenBook.Worksheets('Report Statistic').Range("G7:V7")
getPlanedCumulativeRangeList = []
for i in range(0, len(temp)):
    getPlanedCumulativeRangeList.append(str(temp[i]))
print(getPlanedCumulativeRangeList)

temp = xlOpenBook.Worksheets('Report Statistic').Range("G8:V8")
getActualCumulativeRangeList = []
for i in range(0, len(temp)):
    getActualCumulativeRangeList.append(str(temp[i]))
print(getActualCumulativeRangeList)

xlOpenBook.Save() #case senstive, so don't use .save() instead
xlOpenBook.Close()
xlApp.Quit()

colorList = [
    '#ffcc99', '#ff9966',  '#ff6633', '#ff3333', '#cc00ff', '#ccff00', '#9999ff', '#993399', '#990033', '#6600ff', '#3366ff', '#00ff66', '#006633', '#483D8B', '#4B0082',
    '#ff1493', '#b8860b', '#9acd32', '#32cd32', '#48d1cc', '#00ffff', '#008080', '#696969', '#ba55d3', '#FF00FF', '#FF4500', '#006400', '#0000CD', '#9400D3'
    '#C71585', '#FFC0CB', '#8A2BE2', '#1E90FF', '#C71585'
]
getInt = random.randint(0, 34)

Step3 - Recall a specified pyecharts Bar

# 定义Bar Chart的基本属性，并将其对应到 yaxis_index=0 -
bar = Bar(init_opts=opts.InitOpts(width='1560px', height='400px', bg_color='#E0FFFF'))#bg_color='#f5f5f5', #FFF8DC, #F5F5F5, #E0FFFF, #FFFFF0只有在此处加背景才是有效的，下面的Line处加背景无效, theme=ThemeType.MACARONS. bg_color='#4EFEB3'

# 添加X轴, Y轴内容 -
# bar.add_xaxis(["Day01", "Day02", "Day03", "Day04", "Day05", "Day06", "Day07", "Day08", "Day09", "Day10"])
bar.add_xaxis(getDateRangeList)
# bar.add_yaxis("Planned Execution Qty", [5, 7, 9, 11, 13, 15, 17, 19, 16, None], yaxis_index=0, color='   #EE6363')
getInt = random.randint(0, 20)
bar.add_yaxis("Planned Execution Qty", getPlanedRangeList, yaxis_index=0, color=colorList[getInt])
# bar.add_yaxis("Actual Execution Qty", [3, 8, 7, 8, 9, 12, 10, 15, 12, None], yaxis_index=0, color='#00CD66')
getInt = random.randint(0, 20)
bar.add_yaxis("Actual Execution Qty      ", getActualRangeList, yaxis_index=0, color=colorList[getInt])

# 定义第二个轴 (包括坐标刻度大小，及跨度大小min_=0, max_=80, )- # name_location = 'end' 是默认值
bar.extend_axis(yaxis=opts.AxisOpts(name="Daily Cumulative Executions Scale", min_=-0, type_="value", name_location='end', position='right', offset=4, axisline_opts=opts.AxisLineOpts(linestyle_opts=opts.LineStyleOpts(width=4, color='#0000CD')),
                           axislabel_opts=opts.LabelOpts(font_family="Gill Sans MT", font_weight='bold', formatter="{value} PCS"), name_textstyle_opts=opts.TextStyleOpts(font_family="Gill Sans MT", font_size=14, font_weight='bold')))

Step4 - Format Bsr charts parameters

bar.set_series_opts(markpoint_opts=opts.MarkPointOpts(data=[
    # opts.MarkPointItem(type_='max', name="Maximum"),
    # opts.MarkPointItem(type_="min", name="Minimum"),
    opts.MarkPointItem(type_="average", name="平均值", itemstyle_opts=opts.ItemStyleOpts(opacity=0.45)),  #symbol='arrow', 这里的opacity=0.4是设置MarkPointItem的透明度的
],))
bar.set_series_opts(markline_opts=opts.MarkLineOpts(data=[
    # opts.MarkLineItem(type_='min', name='Minimum'),
    # opts.MarkLineItem(type_='max', name='maximum'),
    # opts.MarkLineItem(type_='average', name='average', symbol='none') ## PyECharts 提供的标记类型包括 'circle', 'rect', 'roundRect', 'triangle', 'diamond', 'pin', 'arrow', 'none'
], label_opts=opts.TextStyleOpts(font_family='Gill Sans MT')))

bar.set_series_opts(tooltip_opts=opts.TooltipOpts(border_color="yellow", border_width=1, is_always_show_content=True)) # 设置提示工具框显示
bar.set_series_opts(label_opts=opts.LabelOpts(position='top', font_family='Gill Sans MT', font_weight='normal')) # item 上面的字体设置(#设置数据标签所在的位置 'top'，'left'，'right'，'bottom'，'inside'，'insideLeft'，'insideRight'  # 'insideTop'，'insideBottom'， 'insideTopLeft'，'insideBottomLeft'  # 'insideTopRight'，'insideBottomRight')
bar.set_series_opts(itemstyle_opts=opts.ItemStyleOpts(opacity=0.45)) #设置item显示的透明度
# bar.set_global_opts(yaxis_opts=opts.AxisOpts(name="The Scale Unit", name_gap=36, name_location='right', name_textstyle_opts=opts.TextStyleOpts(font_family='Gill Sans MT', font_size=14, font_weight='bold')))
# bar.set_global_opts(title_opts=opts.TitleOpts(title="这是我的标题Title", subtitle="这里定义我的子标题", title_textstyle_opts=opts.TextStyleOpts(font_family="Gill Sans MT")))


# 设置x轴和y轴上显示字体的属性 - 重复定义某一个变量，容易覆盖之前的定义属性
# bar.set_global_opts(xaxis_opts=opts.AxisOpts(axislabel_opts=opts.TextStyleOpts(font_family='Gill Sans MT', font_size=12, font_weight='bold'))) # 设置x轴字体显示
# bar.set_global_opts(yaxis_opts=opts.AxisOpts(axislabel_opts=opts.TextStyleOpts(font_family='Gill Sans MT', font_size=12, font_weight='bold', font_style='italic', color='green')))  # 设置y轴字体显示

# 定义x轴(yaxis_index=0)坐标轴标签信息 -
bar.set_global_opts(xaxis_opts=opts.AxisOpts(name="QA Cumulative Execution Burndown Chart", name_gap=28, name_location="middle", name_textstyle_opts=opts.TextStyleOpts(font_family="Gill Sans MT", font_size=18, font_weight='bold', color="black"), axislabel_opts=opts.TextStyleOpts(font_family='Gill Sans MT', font_size=12, font_style='italic', font_weight='bold', color='black'),
                                             axisline_opts=opts.AxisLineOpts(linestyle_opts=opts.LineStyleOpts(width=2, color='black')), # 设置x轴粗细度
                                                                             ))

## # 定义y轴(yaxis_index=0)坐标轴标签信息 -(interval = 8 设置刻度间隔, 设置第一个y轴的刻度和间隔  interval=5, max_=30, ）
bar.set_global_opts(yaxis_opts=opts.AxisOpts(name="Daily Execution Scale", max_=15, name_gap=15, name_location='end', name_textstyle_opts=opts.TextStyleOpts(font_family='Gill Sans MT', font_size=14, font_weight='bold'), offset=20, axispointer_opts=opts.AxisPointerOpts(is_show=True), axislabel_opts=opts.TextStyleOpts(font_family='Gill Sans MT', font_size=12, font_weight='bold', font_style='italic', color='black'), axisline_opts=opts.AxisLineOpts(linestyle_opts=opts.LineStyleOpts(width=2, color='black', opacity=0.5,))))
##name_location =  'start', 'middle' 或者 'center','end', 上两句是随x轴，y轴移动的，不会跑上面去

# 定义lengend 标轴标签信息 -
bar.set_global_opts(legend_opts=opts.LegendOpts(padding=5, align='left', pos_left='18%', pos_top=26, textstyle_opts=opts.TextStyleOpts(font_family="Gill Sans MT", font_size=12)))# pos_right='10%', pos_left='20%' #设置图例显示位置

Step5 - Recall another charts Line and format Line char parameters

# 创建Line图示，并将其对应到 yaxis_index=1
line = Line(init_opts=opts.InitOpts(width="1560px", height="400px"))

# line.add_xaxis(["Day01", "Day02", "Day03", "Day04", "Day05", "Day06", "Day07", "Day08", "Day09", "Day10"])
# line.add_yaxis(" ", [5, 12, 21, 32, 45, 60, 77, 96, 112, 118], yaxis_index=1, linestyle_opts=opts.LineStyleOpts(width=3, curve=3, type_='dashed', color='red'), label_opts=opts.LabelOpts(font_family='Gill Sans MT', font_weight='bold', position='top')) ## 'solid', 'dashed', 'dotted'; opacity=1
# line.add_yaxis("Calculative Execution", [3, 11, 18, 26, 46, 62, 75, 96, 108, 118], yaxis_index=1, linestyle_opts=opts.LineStyleOpts(width=2, curve=3, type_='solid', color='green'), label_opts=opts.LabelOpts(font_family='Gill Sans MT', font_weight='bold', font_size=13, position='bottom', vertical_align='middle')) #可选 'top'，'left'，'right'，'bottom'，'inside'，'insideLeft'，'insideRight'.....

# is_smooth=True,  画出平滑的线条
line.add_xaxis(getDateRangeList)
line.add_yaxis("  /", getPlanedCumulativeRangeList, is_smooth=True, yaxis_index=1, linestyle_opts=opts.LineStyleOpts(width=4, curve=4, type_='dashed', color='red'), label_opts=opts.LabelOpts(font_family='Gill Sans MT', font_weight='bold', position='top', vertical_align='middle', border_radius=45, background_color='lightPink'), symbol='diamond', symbol_size=8) ## 'solid', 'dashed', 'dotted'; opacity=1, vertical_algin = top, bottom, middle 设置图标的位置
line.add_yaxis("Calculative Execution", getActualCumulativeRangeList, is_smooth=True, yaxis_index=1, linestyle_opts=opts.LineStyleOpts(width=3, curve=3, type_='solid', color='green'), label_opts=opts.LabelOpts(font_family='Gill Sans MT', font_weight='bold', font_size=13, position='bottom', border_radius=30, vertical_align='middle', background_color='lightGreen'), symbol_size=6) #可选 'top'，'left'，'right'，'bottom'，'inside'，'insideLeft'，'insideRight'.....

Step6 - Overlap the charts and generate the report

bar.overlap(line)
bar.render("burnDownReport.html")

Step7 - See the execution result