def statistics_to_excel():
df = pd.DataFrame([
["Commits with Java file changes", _java_files_commits],
["Commits having JavaDoc tags changed", _mixed_commits + _only_javadoc_in_some_files_commits + _pure_javadoc_commits],
["Commits having Code and JavaDoc tags changed in all files", _mixed_commits],
["Commits having files with only JavaDoc tag changes", _only_javadoc_in_some_files_commits],
["Commits exclusively of JavaDoc tag changes", _pure_javadoc_commits]
])
with pd.ExcelWriter('__statistics.xlsx', engine='openpyxl') as writer:
df.to_excel(writer, 'Statistics', index_label=False, index=False, header=False)
wb = openpyxl.load_workbook('__statistics.xlsx')
worksheet = wb.active
col = worksheet['A']
max_length = 0
for cell in col:
try:
if len(str(cell.value)) > max_length:
max_length = len(cell.value)
except Exception as e:
logging.warning(str(e))
continue
adjusted_width = (max_length + 2) * 1.2
worksheet.column_dimensions['A'].width = adjusted_width
chart = DoughnutChart()
chart.type = "filled"
labels = Reference(worksheet, min_col = 1, min_row = 3, max_row = 5)
data = Reference(worksheet, min_col = 2, min_row = 3, max_row = 5)
chart.add_data(data, titles_from_data = False)
chart.set_categories(labels)
chart.title = "Commits Chart"
chart.style = 26
worksheet.add_chart(chart, "C7")
wb.save('__statistics.xlsx')
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 main():
wb = Workbook()
ws = wb.active
for row in sales_data:
ws.append(row)
data_points = create_data_points() #←データ系列を作成
categories = Reference(ws, min_col=1, min_row=2, max_row=4) #←判例の範囲指定
chart_2017 = DoughnutChart() #←ドーナツグラフ
data = Reference(ws, min_col=2, min_row=1, max_row=4) #←2017年売上データの範囲指定
chart_2017.add_data(data, titles_from_data=True)
chart_2017.set_categories(categories)
chart_2017.title = 'カテゴリ別年間売上'
chart_2017.style = 26
chart_2017.series[0].data_points = data_points #←データ系列を設定
ws.add_chart(chart_2017, 'E1') #←2017年グラフをE列1行目に追加
data = Reference(ws, min_col=3, min_row=1, max_row=4) #←2018年売上データの範囲指定
chart_2018 = create_new_chart(chart_2017, data, data_points, categories)
ws.add_chart(chart_2018, 'E17') #←2018年グラフをE列17行目に追加
data = Reference(ws, min_col=4, min_row=1, max_row=4) #←2019年売上データの範囲指定
chart_2019 = create_new_chart(chart_2018, data, data_points, categories)
ws.add_chart(chart_2019, 'E33') #←2018年グラフをE列33行目に追加
wb.save('sales_doughnut.xlsx')
def create_pie():
global compCount
wbNew = load_workbook('nc_report.xlsx')
newSheet = wbNew.active
data = [
['NC Status', 'Count'],
['Pending', len(pendRows)],
['Overdue', len(overRows)],
['Completed', compCount]
]
newSheet.append([""])
for row in data:
newSheet.append(row)
global rows
chart = DoughnutChart()
labels = Reference(newSheet, min_col=1, min_row=rows + 4, max_row=rows + 6)
data = Reference(newSheet, min_col=2, min_row=rows + 3, max_row=rows + 6)
chart.add_data(data, titles_from_data=True)
chart.set_categories(labels)
chart.title = "NC Report"
chart.style = 26
newSheet.add_chart(chart, "A{x}".format(x=rows + 7))
wbNew.save('nc_report.xlsx')
["Donut", "Pie"],
[25, 75],
[50, 1],
[25, 124],
[100],
]
# 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]
data = [
['Pie', 2014, 2015],
['Plain', 40, 50],
['Jam', 2, 10],
['Lime', 20, 30],
['Chocolate', 30, 40],
]
wb = Workbook()
ws = wb.active
for row in data:
ws.append(row)
chart = DoughnutChart()
labels = Reference(ws, min_col=1, min_row=2, max_row=5)
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)]
#print(slices)
plain, jam, lime, chocolate = slices
chart.series[0].data_points = slices
plain.graphicalProperties.solidFill = "FAE1D0"
jam.graphicalProperties.solidFill = "BB2244"
data = [
['Pie', 2014, 2015],
['Plain', 40, 50],
['Jam', 2, 10],
['Lime', 20, 30],
['Chocolate', 30, 40],
]
wb = Workbook()
ws = wb.active
for row in data:
ws.append(row)
chart = DoughnutChart()
labels = Reference(ws, min_col=1, min_row=2, max_row=5)
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"
def append_data_into_cells(worksheet, report):
current_row = 5
current_column = 1
my_red = colors.Color(colors.RED)
my_fill_red = fills.PatternFill(patternType='solid', fgColor=my_red)
my_green = colors.Color(colors.GREEN)
my_fill_green = fills.PatternFill(patternType='solid',
fgColor=my_green)
my_pink = colors.Color(rgb='FF9999')
my_fill_pink = fills.PatternFill(patternType='solid', fgColor=my_pink)
for module in report._listModules:
worksheet.cell(row=current_row,
column=current_column).value = module._name
for tca in module._listTestCases:
current_column += 1
worksheet.cell(row=current_row,
column=current_column).value = tca._testCaseName
current_column += 1
worksheet.cell(row=current_row,
column=current_column).value = tca._result
if Generator.pass_str in tca._result:
worksheet.cell(row=current_row,
column=current_column).fill = my_fill_green
elif Generator.fail_str in tca._result:
worksheet.cell(row=current_row,
column=current_column).fill = my_fill_red
else:
worksheet.cell(row=current_row,
column=current_column).fill = my_fill_pink
current_column += 1
worksheet.cell(row=current_row,
column=current_column).value = tca._exitCode
current_column = 1
current_row += 1
current_column = 1
current_row += 1
worksheet.cell(row=current_row,
column=current_column).value = 'Summary:'
current_row += 1
worksheet.cell(row=current_row,
column=current_column).value = Generator.total_tests
current_column += 1
worksheet.cell(row=current_row,
column=current_column).value = report.nrTotalTest
current_column = 1
current_row += 1
worksheet.cell(row=current_row,
column=current_column).value = Generator.skipped_test
current_column += 1
worksheet.cell(row=current_row,
column=current_column).value = report.nrTotalSkipped
current_row += 1
current_column = 1
worksheet.cell(row=current_row,
column=current_column).value = Generator.total_failures
current_column += 1
worksheet.cell(row=current_row,
column=current_column).value = report.nrTotalFailures
current_column = 1
current_row += 1
worksheet.cell(row=current_row,
column=current_column).value = 'Percentage Pass'
worksheet.cell(row=current_row,
column=current_column).fill = my_fill_green
current_column += 1
worksheet.cell(row=current_row,
column=current_column).value = report.percentagePass
worksheet.cell(row=current_row,
column=current_column).fill = my_fill_green
current_column = 1
current_row += 1
worksheet.cell(row=current_row,
column=current_column).value = 'Percentage Fail'
worksheet.cell(row=current_row,
column=current_column).fill = my_fill_red
current_column += 1
worksheet.cell(row=current_row,
column=current_column).value = report.percentageFail
worksheet.cell(row=current_row,
column=current_column).fill = my_fill_red
current_column = 1
current_row += 1
worksheet.cell(row=current_row,
column=current_column).value = 'Percentage Conf'
worksheet.cell(row=current_row,
column=current_column).fill = my_fill_pink
current_column += 1
worksheet.cell(row=current_row,
column=current_column).value = report.percentageConf
worksheet.cell(row=current_row,
column=current_column).fill = my_fill_pink
current_row -= 2
current_column = 1
# at the end create a chart
chart = DoughnutChart()
labels = Reference(worksheet,
min_col=current_column,
min_row=current_row,
max_row=current_row + 2)
data = Reference(worksheet,
min_col=current_column + 1,
min_row=current_row,
max_row=current_row + 2)
chart.add_data(data)
chart.set_categories(labels)
chart.title = "LTP test results"
# Change bar filling and line color
# serie1= chart.series[0];
# serie1.graphicalProperties.solidFill = "7E3F00"
# serie2 = chart.series[1];
chart.style = 2
worksheet.add_chart(chart, "F3")
["Donut", "Pie"],
[25, 75],
[50, 1],
[25, 124],
[100],
]
# 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]
def append_data_into_cells(worksheet):
"""append data into the worksheet"""
current_row = 5
current_column = 1
my_red = colors.Color(colors.RED)
my_fill_red = fills.PatternFill(patternType='solid', fgColor=my_red)
my_green = colors.Color(colors.GREEN)
my_fill_green = fills.PatternFill(patternType='solid',
fgColor=my_green)
my_pink = colors.Color(rgb='FF9999')
my_fill_pink = fills.PatternFill(patternType='solid', fgColor=my_pink)
for module in Generator.report_csv._listModules:
for tca in module._listTestCases:
worksheet.cell(row=current_row,
column=current_column).value = tca._module_git
current_column += 1
worksheet.cell(row=current_row,
column=current_column).value = tca._testCaseName
current_column += 1
worksheet.cell(row=current_row,
column=current_column).value = tca._result
if Generator.pass_str in tca._result:
worksheet.cell(row=current_row,
column=current_column).fill = my_fill_green
elif Generator.fail_str in tca._result:
worksheet.cell(row=current_row,
column=current_column).fill = my_fill_red
else:
worksheet.cell(row=current_row,
column=current_column).fill = my_fill_pink
current_column += 3
worksheet.cell(
row=current_row, column=current_column
).value = f"=VLOOKUP(A{current_row},'ES6 - LTP Test Results'!A:B,2)"
current_column += 1
worksheet.cell(
row=current_row, column=current_column
).value = f'=IF(AND(C{current_row}<>F{current_row},F{current_row}<>"N/A"),"Different","OK")'
# current_column += 1
# worksheet.cell(row=current_row, column=current_column).value = tca._exitCode
current_column = 1
current_row += 1
current_column = 1
current_row += 1
worksheet.cell(row=current_row,
column=current_column).value = 'Summary:'
current_row += 1
worksheet.cell(row=current_row,
column=current_column).value = Generator.total_tests
current_column += 1
worksheet.cell(
row=current_row,
column=current_column).value = Generator.report_csv.nrTotalTest
current_column = 1
current_row += 1
worksheet.cell(row=current_row,
column=current_column).value = Generator.skipped_test
current_column += 1
worksheet.cell(
row=current_row,
column=current_column).value = Generator.report_csv.nrTotalSkipped
current_row += 1
current_column = 1
worksheet.cell(row=current_row,
column=current_column).value = Generator.total_failures
current_column += 1
worksheet.cell(
row=current_row,
column=current_column).value = Generator.report_csv.nrTotalFailures
current_column = 1
current_row += 1
worksheet.cell(row=current_row,
column=current_column).value = 'Percentage Pass'
worksheet.cell(row=current_row,
column=current_column).fill = my_fill_green
current_column += 1
worksheet.cell(
row=current_row,
column=current_column).value = Generator.report_csv.percentagePass
worksheet.cell(row=current_row,
column=current_column).fill = my_fill_green
current_column = 1
current_row += 1
worksheet.cell(row=current_row,
column=current_column).value = 'Percentage Fail'
worksheet.cell(row=current_row,
column=current_column).fill = my_fill_red
current_column += 1
worksheet.cell(
row=current_row,
column=current_column).value = Generator.report_csv.percentageFail
worksheet.cell(row=current_row,
column=current_column).fill = my_fill_red
current_column = 1
current_row += 1
worksheet.cell(row=current_row,
column=current_column).value = 'Percentage Skipped'
worksheet.cell(row=current_row,
column=current_column).fill = my_fill_pink
current_column += 1
worksheet.cell(
row=current_row,
column=current_column).value = Generator.report_csv.percentageConf
worksheet.cell(row=current_row,
column=current_column).fill = my_fill_pink
current_row -= 2
current_column = 1
# at the end create a chart
chart = DoughnutChart()
labels = Reference(worksheet,
min_col=current_column,
min_row=current_row,
max_row=current_row + 2)
data = Reference(worksheet,
min_col=current_column + 1,
min_row=current_row,
max_row=current_row + 2)
# worksheet.auto_filter.ref = 'A5:C1766'
# worksheet.auto_filter.add_sort_condition('A{0}:A{1}'.format(5, 1766))
chart.add_data(data)
chart.set_categories(labels)
chart.title = "LTP test results"
chart.style = 10
worksheet.add_chart(chart, "I8")
from openpyxl.chart.series import DataPoint
data = [
['Pie', 2014, 2015],
['Plain', 40, 50],
['Jam', 2, 10],
['Lime', 20, 30],
['Chocolate', 30, 40],
]
ws12 = wb.create_sheet(title="Doughnut Charts ")
for row in data:
ws12.append(row)
chart = DoughnutChart()
labels = Reference(ws12, min_col=1, min_row=2, max_row=5)
data = Reference(ws12, 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"