def pie_chart():
data = [
['Pie', 'Sold'],
['Apple', 50],
['Cherry', 30],
['Pumpkin', 10],
['Chocolate', 40],
]
wb = Workbook()
ws = wb.active
for row in data:
ws.append(row)
pie = PieChart()
labels = Reference(ws, min_col=1, min_row=2, max_row=5)
data = Reference(ws, min_col=2, min_row=1, max_row=5)
pie.add_data(data, titles_from_data=True)
pie.set_categories(labels)
pie.title = "Pies sold by category"
# Cut the first slice out of the pie
slice = DataPoint(idx=0, explosion=20)
pie.series[0].data_points = [slice]
ws.add_chart(pie, "D1")
wb.save("chart.xlsx")
def chartBreakdown():
'''Makes pie chart and bar chart for summary tab'''
pie = PieChart()
z = len(categories)
data = Reference(Sum, min_col=9, min_row=2, max_row= z-1)
labels = Reference(Sum, min_col=8, min_row=2, max_row= z-1)
pie.add_data(data)
pie.set_categories(labels)
pie.title = 'Breakdown of Expenses'
pie.width = 15.0
pie.height = 12.0
pie.legend.layout = Layout(manualLayout=ManualLayout(x=0.25, y=0.25, h=0.99, w=0.25))
Sum.add_chart(pie, 'A1')
pie.dataLabels = DataLabelList()
pie.dataLabels.showPercent = True
bar = BarChart()
barData1 = Reference(Sum, min_col=mNum+9, min_row=1, max_row=z-1)
barData2 = Reference(Sum, min_col=mNum+12, min_row=1, max_row=z-1)
bar.add_data(barData1, titles_from_data=True)
bar.add_data(barData2, titles_from_data=True)
bar.set_categories(labels)
bar.title = 'Goal Comparison'
bar.width = 2.0*z
bar.height = 12.0
bar.legend.layout = Layout(manualLayout=ManualLayout(x=0.25, y=0.25, h=1.99, w=0.25))
Sum.add_chart(bar, 'A28')
def create_chart_file(name_file, ws_result):
actual_path = os.path.abspath(os.path.dirname(__file__))
result_file_path = os.path.join(actual_path,
f"../files/result_files/{name_file}.xlsx")
try:
wb = openpyxl.load_workbook(result_file_path)
chart_worksheet = wb.create_sheet(title="Gráfico")
chart_worksheet['A1'] = "Total Sucess"
chart_worksheet['A2'] = "Total Failed"
chart_worksheet['B1'] = f'=COUNTIF(${ws_result}.E:E;"Sucess")'
chart_worksheet['B2'] = f'=COUNTIF(${ws_result}.E:E;"Failed")'
pie_chart = PieChart()
values = Reference(chart_worksheet,
min_col=1,
min_row=1,
max_col=2,
max_row=2)
labels = Reference(chart_worksheet,
min_col=1,
min_row=1,
max_col=2,
max_row=2)
pie_chart.add_data(values, titles_from_data=True)
pie_chart.set_categories(labels)
chart_worksheet.add_chart(pie_chart, "A1")
wb.save(result_file_path)
except Exception as e:
print(e)
def pie_chart(wp):
table = wp.get_sheet_by_name(wp.get_sheet_names()[0])
#生成饼图对象
pie = PieChart()
#图的标题
pie.title = "API接口测试统计"
'''行数和列数都是从1开始的，和遍历用例是一样都是从1开始'''
#获取标签(取范围第一列的最小行数和最大行数)
labels = Reference(table, min_col=4, min_row=6, max_col=4, max_row=7)
#获取数据（取范围第二列的最小行数-1和最大行数）
data = Reference(table, min_col=5, min_row=5, max_col=5, max_row=7)
#添加数据和标签到饼图中
pie.add_data(data, titles_from_data=True)
pie.set_categories(labels)
#放在excel表中
table.add_chart(pie, "A10")
#保存excel
# wb.save("test1.xlsx")
# wb = load_workbook("D:\\PycharmProjects\\test.xlsx")
# pie_chart(wb)
def __init__(self, db_name, save_atexit_only=True):
self.db_name = db_name
if path.exists(self.db_name):
self.wb = openpyxl.load_workbook(self.db_name)
self.sheet = self.wb.active
else:
self.wb = openpyxl.Workbook()
self.sheet = self.wb.active
data = Reference(self.sheet,
min_col=4,
min_row=1,
max_col=6,
max_row=2)
chart = PieChart()
chart.title = 'Статистика'
chart.add_data(data, titles_from_data=True)
self.sheet.add_chart(chart, 'D4')
self.sheet['D1'] = 'Негативных'
self.sheet['D2'] = '=COUNTIF(B3:B20000,"Негативный")'
self.sheet['E1'] = 'Нейтральных'
self.sheet['E2'] = '=COUNTIF(B3:B20000,"Нейтральный")'
self.sheet['F1'] = 'Позитивных'
self.sheet['F2'] = '=COUNTIF(B3:B20000,"Позитивный")'
if save_atexit_only:
atexit.register(self.save)
def add_pie_chart(writer, df):
# 初始化excel并确立图表类型
sheet = writer.sheets['全球最新疫情分布']
chart = PieChart()
# 设置插入序列
max_row = len(df)
labels = Reference(sheet,
min_col=2,
max_col=2,
min_row=2,
max_row=max_row + 1)
chart_data = Reference(sheet,
min_col=3,
max_col=3,
min_row=1,
max_row=max_row + 1)
chart.add_data(chart_data, titles_from_data=True)
chart.set_categories(labels)
# 设置图表格式
chart.title = "除湖北外全球各地区确诊人数占比"
chart.height, chart.width = 14, 21
chart.style = 5
chart.dataLabels = DataLabelList()
chart.dataLabels.showCatName = True
chart.dataLabels.showPercent = True
# 插入图表
sheet.add_chart(chart, 'F1')
writer.save()
def create_pie_chart(sheet, numOfStocks, sectors):
dictSector = Counter(sectors)
pie = PieChart()
labels = Reference(sheet, min_col=1, min_row=2, max_row=numOfStocks + 1)
data = Reference(sheet, min_col=5, min_row=1, max_row=numOfStocks + 1)
pie.add_data(data, titles_from_data=True)
pie.set_categories(labels)
pie.title = "Percentage of Shares in Portfolio"
pie.height = 12
pie.width = 20
sheet.add_chart(pie, "K1")
pie2 = PieChart()
labels = Reference(sheet,
min_col=7,
min_row=27,
max_row=26 + len(dictSector))
data = Reference(sheet,
min_col=8,
min_row=26,
max_row=26 + len(dictSector))
pie2.add_data(data, titles_from_data=True)
pie2.set_categories(labels)
pie2.title = "Percentage of Sectors in Portfolio"
pie2.height = 12
pie2.width = 20
sheet.add_chart(pie2, "K26")
def excel_automation(filename):
# for experiment, ignore it
# wb = xl.load_workbook("transactions.xlsx")
wb = xl.load_workbook(f"{filename}")
sheet = wb['Sheet1']
for row in range(2, sheet.max_row + 1):
cell = sheet.cell(row, 3)
# add corrected formula here
corrected_price = cell.value * 0.7
corrected_price_cell = sheet.cell(row, sheet.max_column + 1)
corrected_price_cell.value = corrected_price
graphValues = Reference(sheet,
min_row=2,
max_row=sheet.max_row,
min_col=4,
max_col=4)
chart = PieChart()
chart.add_data(graphValues)
sheet.add_chart(chart, 'f2')
wb.save(f'{filename}')
def make_pie(sheet, left_col, top_row, bot_row, title, print_cell, height,
width):
left_col = int(left_col)
right_col = left_col + 1
top_row = int(top_row)
bot_row = int(bot_row)
title = str(title)
print_cell = str(print_cell)
height = float(height)
width = float(width)
pie = PieChart()
labels = Reference(sheet,
min_col=left_col,
max_col=left_col,
min_row=top_row + 1,
max_row=bot_row)
data = Reference(sheet,
min_col=right_col,
max_col=right_col,
min_row=top_row,
max_row=bot_row)
pie.add_data(data, titles_from_data=True)
pie.set_categories(labels)
pie.title = title
pie.height = height
pie.width = width
sheet.add_chart(pie, print_cell)
return None
def pieChart():
from openpyxl import Workbook
from openpyxl.chart import PieChart, Reference
data = [
['水果', '销量'],
['苹果', 50],
['樱桃', 30],
['橘子', 10],
['香蕉', 40],
]
wb = Workbook()
ws = wb.active
for row in data:
ws.append(row)
pie = PieChart()
labels = Reference(ws, min_col=1, min_row=2, max_row=5)
data = Reference(ws, min_col=2, min_row=1, max_row=5)
pie.add_data(data, titles_from_data=True)
pie.set_categories(labels)
pie.title = "水果销量占比"
ws.add_chart(pie, "D1")
wb.save('piechart.xlsx')
print('打开文件 piechart.xlsx 查看结果')
def summarize_amount(file: str, sheet_name: str):
wb = openpyxl.load_workbook(file)
sheet = wb[sheet_name]
last_row = sheet.max_row
last_col = sheet.max_column
for i in range(2, last_row + 1):
for j in range(6, last_col):
sheet.cell(row=i, column=j).number_format = '#,##,0.00'
sheet.cell(row=i, column=last_col).number_format = '0.00%'
summaries = [
{'location': (last_row + 2, 1),
'letter': 'A',
'labels': ['招商银行', '恒生银行', '银河', '华盛*', '富途*', '蛋卷*', '同花顺'],
'category': 'platform',
'anchor': 'K1'},
{'location': (last_row + 2, 4),
'letter': 'B',
'labels': ['rmb', 'hkd', 'usd'],
'category': 'currency',
'anchor': 'K16'},
{'location': (last_row + 2, 7),
'letter': 'E',
'labels': [0, 1, 2, 3],
'category': 'risk',
'anchor': 'K31'}
]
for summary in summaries:
row, col = summary['location']
le = [get_column_letter(j) for j in range(col, col + 3)]
for i in range(len(summary['labels'])):
sheet.cell(row=row+i, column=col).value = summary['labels'][i]
c = sheet.cell(row=row+i, column=col+1)
c.number_format = "#,##,0.00"
c.value = '=SUMIF(${0}$2:${0}${1},{2}{3},$H$2:$H${1})'.format(summary['letter'],
last_row, le[0], row + i)
c = sheet.cell(row=row+i, column=col+2)
c.number_format = "#,##,0.00"
c.value = '=SUMIF(${0}$2:${0}${1},{2}{3},$I$2:$I${1})'.format(summary['letter'],
last_row, le[0], row + i)
sheet.cell(row=row+i+1, column=col).value = 'sum'
c = sheet.cell(row=row+i+1, column=col+1)
c.number_format = "#,##,0.00"
c.value = '=SUM({0}{1}:{0}{2})'.format(le[1], row, row + i)
c = sheet.cell(row=row+i+1, column=col+2)
c.number_format = "#,##,0.00"
c.value = '=SUM({0}{1}:{0}{2})'.format(le[2], row, row + i)
pie = PieChart()
labels = Reference(sheet, min_col=col, min_row=row, max_row=row+i)
data = Reference(sheet, min_col=col+1, min_row=row-1, max_row=row+i)
pie.add_data(data, titles_from_data=True)
pie.set_categories(labels)
pie.title = summary['category']
sheet.add_chart(pie, summary['anchor'])
wb.save(file)
def draw_chart(input_wb, max_row):
# 拿到我们需要操作的sheet top10
top_10_sheet = input_wb.worksheets[-1]
top_10_sheet.sheet_view.zoomScale = 200
# 初始化chart
bar_chart = BarChart()
line_chart = LineChart()
pie_chart = PieChart()
# 生成数据
bar_chart_data = Reference(top_10_sheet,
min_col=4,
max_col=7,
min_row=1,
max_row=max_row)
line_chart_data = Reference(top_10_sheet,
min_col=4,
max_col=4,
min_row=1,
max_row=max_row)
pie_chart_data = Reference(top_10_sheet,
min_col=4,
max_col=4,
min_row=1,
max_row=max_row)
# 指定chart的x_axis
x_data = Reference(top_10_sheet,
min_col=2,
max_col=2,
min_row=2,
max_row=max_row)
# 设置chart样式
bar_chart.height, bar_chart.width = 7, 15
line_chart.height, line_chart.width = 7, 15
pie_chart.height, pie_chart.width = 7, 15
bar_chart.title, bar_chart.y_axis.title, bar_chart.x_axis.title = 'top10', '人数', '国家'
# bar_chart.y_axis.scaling.max = 5000000
# 把数据添加进chart
bar_chart.add_data(bar_chart_data, titles_from_data=True)
line_chart.add_data(line_chart_data, titles_from_data=True)
bar_chart.set_categories(x_data)
line_chart.set_categories(x_data)
pie_chart.add_data(pie_chart_data, titles_from_data=True)
pie_chart.set_categories(x_data)
pie_chart.dataLabels = DataLabelList()
pie_chart.dataLabels.showVal = True
pie_chart.dataLabels.showLegendKey = True
# 把chart添加到sheet
# top_10_sheet.add_chart(bar_chart, 'I1')
# top_10_sheet.add_chart(pie_chart, 'I11')
bar_chart += line_chart
top_10_sheet.add_chart(bar_chart, 'I1')
# 保存我们的workbook
input_wb.save('./excel_files/report_chart.xlsx')
def plot_sentiment(tickers):
df = scored_news(tickers)
#getrange of headlines dates and daily scores
dates = date_range(df)
daily = daily_sentiment(tickers)
df = tally_scores(df).T
blank = []
columns = list(df)
path = "C:\\Users\\matth\\Desktop\\stock-sentiment-analyzer\\pie.xlsx"
sf = StyleFrame(daily.T)
col_list = list(daily.columns)
writer = pd.ExcelWriter(path, engine='xlsxwriter')
daily.T.to_excel(excel_writer=writer, sheet_name='Daily Sentiment Summary')
worksheet = writer.sheets['Daily Sentiment Summary']
worksheet.set_column('B:B', 15)
writer.save()
wb = load_workbook("pie.xlsx")
for i in range(0, len(df.columns)):
ws = wb.create_sheet(tickers[i])
score = 1
for index, row in df.iterrows():
vals = []
if index == 'tickers':
continue
vals.append(df.loc[index, i])
vals.insert(0, df.index[score])
score = score + 1
ws.append(vals)
pie = PieChart()
labels = Reference(ws, min_col=1, max_col=1, min_row=1, max_row=3)
data = Reference(ws, min_col=2, max_col=2, min_row=1, max_row=3)
pie.add_data(data)
pie.set_categories(labels)
pie.title = tickers[i] + ' Sentiment'
pie.layout = Layout(manualLayout=ManualLayout(
h=0.7,
w=0.7,
))
ws.add_chart(pie, "E3")
wb.save("pie.xlsx")
def drawFinalPieChart(sheetName):
wb = loadExcel()
ws = wb.get_sheet_by_name(sheetName)
pie = PieChart()
labels = Reference(ws, min_col=1, min_row=2, max_row=3)
data = Reference(ws, min_col=2, min_row=1, max_row=3)
pie.add_data(data, titles_from_data=True)
pie.set_categories(labels)
pie.title = "AWSB FINAL TEST REPORT"
ws.add_chart(pie, "A6")
wb.save(excelSheetName)
def make_chart_carrier(result_file="MainTestResults/MainResult.xlsx",
summary_file="CarrierTestResults"
"/CarrierTestResult.json"):
try:
wb = load_workbook(os.path.join(current_path, result_file))
except:
wb = Workbook()
if "Carrier Summary" in wb.sheetnames:
del wb["Carrier Summary"]
wb.create_sheet("Carrier Summary")
ws = wb["Carrier Summary"]
_ = ws.cell(row=1, column=1, value='Carrier Summary')
_.font = Font(bold=True)
# summary_file = "CarrierTestResults/CarrierTestResult.json"
with open(os.path.join(current_path, summary_file), 'r') as f:
res = json.load(f)
ips = [key for key in res.keys() if key != 'summary']
row = 1
pie_row_start = 4
for ip in ips:
data_to_add = [["made ip", ip]]
for key in res[ip].keys():
data_to_add.append([key, res[ip][key]])
for line in data_to_add:
ws.append(line)
pie_row_end = pie_row_start + 3
pie = PieChart()
print("pie row start:", pie_row_start)
print("pie row end:", pie_row_end)
labels = Reference(ws,
min_col=1,
min_row=pie_row_start,
max_row=pie_row_end)
data = Reference(ws,
min_col=2,
min_row=pie_row_start - 1,
max_row=pie_row_end)
pie.add_data(data, titles_from_data=True)
pie.set_categories(labels)
pie.title = ip + " fail rate"
pie.dataLabels = DataLabelList()
pie.dataLabels.showPercent = True
ws.add_chart(pie, "D" + str(row))
pie_row_start += len(data_to_add)
row += 18
wb.save(os.path.join(current_path, result_file))
def create_pie_chart():
wb, ws, max_row = load_book(Main)
chart_worksheet = wb[Category_Data]
cs = wb.create_chartsheet("Category Chart")
r = load_chart_data(ws, "Category", chart_worksheet)
category_expenses = PieChart()
labels = Reference(chart_worksheet, min_col=2, min_row=3, max_row=r - 1)
data = Reference(chart_worksheet, min_col=3, min_row=2, max_row=r - 1)
category_expenses.add_data(data, titles_from_data=True)
category_expenses.set_categories(labels)
category_expenses.title = "Expenses by Category"
cs.add_chart(category_expenses)
wb.save(file_path)
def add_pie_chart(workbook, worksheet_level):
pie = PieChart()
cats = Reference(worksheet_level,
min_col=1,
min_row=2,
max_row=worksheet_level.max_row)
data = Reference(worksheet_level,
min_col=2,
min_row=1,
max_row=worksheet_level.max_row)
pie.add_data(data, titles_from_data=True)
pie.set_categories(cats)
pie.title = '问题评级'
data_points = DataPoint(idx=0, explosion=20)
pie.series[0].data_points = [data_points]
worksheet_level.add_chart(pie, "C9")
workbook.save(PATH + '/total.xlsx')
def B():
sheetB = wb.create_sheet('주요국가별 출원동향', 1)
B그래프data = Data.주요국출원동향()
for r in dataframe_to_rows(B그래프data, index=False, header=True):
sheetB.append(r)
sheetB.insert_cols(2)
for row, cellobj in enumerate(list(sheetB.columns)[1]):
n = '=right(A%d,2)' % (row + 1)
cellobj.value = n
sheetB['B22'] = '합계'
sheetB['C22'] = '=SUM(C2:C21)'
sheetB['D22'] = '=SUM(D2:D21)'
sheetB['E22'] = '=SUM(E2:E21)'
sheetB['F22'] = '=SUM(F2:F21)'
chartB1 = LineChart()
dataB1 = Reference(sheetB, min_col=3, min_row=1, max_row=21, max_col=6)
catsB1 = Reference(sheetB, min_col=2, min_row=2, max_row=21)
chartB1.add_data(dataB1, titles_from_data=True)
chartB1.set_categories(catsB1)
chartB1.y_axis.majorGridlines = None
chartB1.width = 15
chartB1.height = 10
chartB1.legend.position = 't'
chartB1.graphical_properties = GraphicalProperties(ln=LineProperties(
noFill=True))
sheetB.add_chart(chartB1, 'H2')
chartB2 = PieChart()
dataB2 = Reference(sheetB, min_col=3, min_row=22, max_col=6)
labelsB2 = Reference(sheetB, min_col=3, min_row=1, max_col=6)
chartB2.add_data(dataB2, from_rows=22, titles_from_data=False)
chartB2.set_categories(labelsB2)
chartB2.width = 5
chartB2.height = 5
chartB2.legend = None
chartB2.graphical_properties = GraphicalProperties(
ln=LineProperties(noFill=True, solidFill=None, gradFill=None))
chartB2.dLbls = DataLabelList()
chartB2.dLbls.showPercent = True
sheetB.add_chart(chartB2, 'M1')
def monthChartBreakdown():
'''Makes pie chart for each month'''
curMonth = ['', 'January', 'February', 'March', 'April', 'May', 'June',
'July', 'August', 'September', 'October', 'November', 'December']
for i in range(1, mNum+1):
pie = PieChart()
length_m = len(categories)
monthData = Reference(Sum, min_col = i+9, min_row = 2, max_row = length_m-1)
labels = Reference(Sum, min_col=8, min_row=2, max_row = length_m-1)
pie.add_data(monthData)
pie.set_categories(labels)
pie.title = curMonth[i]+ ' Expenses by Category'
pie.width = 18.0
pie.height = 12.0
pie.legend.layout = Layout(manualLayout=ManualLayout(x=0.25, y=0.25, h=0.99, w=0.25))
wb.get_sheet_by_name(TM[i]).add_chart(pie, 'G3')
def export_pie_chart(self, data, title):
wb = Workbook()
ws = wb.active
for row in [('', '')] + data:
ws.append(row)
pie = PieChart()
labels = Reference(ws, min_col=1, min_row=2, max_row=len(data) + 1)
items = Reference(ws, min_col=2, min_row=1, max_row=len(data) + 1)
pie.add_data(items, titles_from_data=True)
pie.set_categories(labels)
pie.title = title
ws.add_chart(pie, 'A{0}'.format(len(data) + 3))
output = StringIO.StringIO()
wb.save(filename=output)
output.flush()
return output.getvalue()
def export_pie_chart(self, data, title):
wb = Workbook()
ws = wb.active
for row in [('', '')] + data:
ws.append(row)
pie = PieChart()
labels = Reference(ws, min_col=1, min_row=2, max_row=len(data)+1)
items = Reference(ws, min_col=2, min_row=1, max_row=len(data)+1)
pie.add_data(items, titles_from_data=True)
pie.set_categories(labels)
pie.title = title
ws.add_chart(pie, 'A{0}'.format(len(data)+3))
output = StringIO.StringIO()
wb.save(filename=output)
output.flush()
return output.getvalue()
def _draw_pie_charts(self):
"""
画两个饼图
:return: None
"""
ws = self._wb['analysis']
# 设置单元格值，饼图引用
ws['G3'] = '失败'
ws['G4'] = '通过'
ws['H3'] = self._api_failure
ws['H4'] = self._api_num - self._api_failure
ws['N3'] = '失败'
ws['N4'] = '通过'
ws['O3'] = self._case_failure
ws['O4'] = self._case_num - self._case_failure
# 画接口饼图
pie = PieChart()
labels = Reference(ws, min_col=7, min_row=3, max_row=4)
data = Reference(ws, min_col=8, min_row=2, max_row=4)
pie.add_data(data, titles_from_data=True)
pie.set_categories(labels)
pie.title = "接口执行情况"
slice_ = DataPoint(idx=0, explosion=10)
pie.series[0].data_points = [slice_]
ws.add_chart(pie, "F1")
pie.height = 9.5
pie.width = 13
self._log.info('已生成接口执行情况饼图.')
# 画用例饼图
pie2 = PieChart()
labels2 = Reference(ws, min_col=14, min_row=3, max_row=4)
data2 = Reference(ws, min_col=15, min_row=2, max_row=4)
pie2.add_data(data2, titles_from_data=True)
pie2.set_categories(labels2)
pie2.title = "用例执行情况"
slice2_ = DataPoint(idx=0, explosion=10)
pie2.series[0].data_points = [slice2_]
ws.add_chart(pie2, "M1")
pie2.height = 9.5
pie2.width = 13
self._log.info('已生成用例执行情况饼图.')
def make_pie_chart(self):
sheetnames = self.__wb.get_sheet_names()
# ws = self.__wb.get_sheet_by_name(sheetnames[0])
ws_new = self.__wb.create_sheet("pie chart")
# 最后一行
max_r = self.__ws.max_row
pie = PieChart()
labels = Reference(self.__ws, min_col=1, min_row=max_r - 2, max_row=max_r - 1)
data = Reference(self.__ws, min_col=2, min_row=max_r - 3, max_row=max_r - 1)
pie.add_data(data, titles_from_data=True)
pie.set_categories(labels)
pie.title = "Test Result Pie Chart"
# Cut the first slice out of the pie
slice = DataPoint(idx=0, explosion=20)
pie.series[0].data_points = [slice]
ws_new.add_chart(pie, "A1")
self.__wb.save(self.__filename)
def process_workbook(filename):
wb = xl.load_workbook(filename)
sheet = wb['Sheet1']
for row in range(2, sheet.max_row + 1):
cell = sheet.cell(row, 3)
corrected_price = cell.value * 0.9
corrected_price_cell = sheet.cell(row, 4)
corrected_price_cell.value = corrected_price
print(type(corrected_price))
value = Reference(sheet, min_row=2,
max_row=sheet.max_row,
min_col=4,
max_col=4)
chart = PieChart()
chart.add_data(value)
sheet.add_chart(chart, 'a2')
wb.save(filename)
wb.close()
def generate_picks_chart(workbook):
print("Generating picks chart")
chart = PieChart()
chart.title = "Picks"
datasheet = workbook['Summary']
data = Reference(datasheet, min_col=4, max_col=4, min_row=13, max_row=17)
titles = Reference(datasheet, min_col=1, min_row=14, max_row=17)
chart.add_data(data=data, titles_from_data=True)
chart.set_categories(titles)
chart.dataLabels = DataLabelList()
chart.dataLabels.showPercent = True
chart.dataLabels.showVal = True
chart.dataLabels.showCatName = False
chart.dataLabels.showSerName = False
chart.dataLabels.showLegendKey = False
return chart
def results_to_excel(position, techs, posts_seen, total_found):
wb = Workbook()
sheet = wb.active
date = datetime.datetime.now()
# initial format of the sheet
sheet.cell(1, 1).value = 'Total posts viewed:'
sheet.cell(1, 2).value = posts_seen
sheet.cell(1, 4).value = 'Date:'
sheet.cell(1, 5).value = date
sheet.cell(3, 1).value = 'Tech/Lang'
sheet.cell(3, 2).value = 'Number of appearances'
sheet.cell(3, 3).value = 'Ocurrence percentage (*)'
sheet['F19'] = "(*) respect to the total number of posts viewed"
sheet.cell(1, 1).font = Font(name='Arial', bold=True, size=13)
sheet.cell(1, 4).font = Font(name='Arial', bold=True, size=13)
sheet.cell(3, 1).font = Font(name='Arial', bold=True, size=13)
sheet.cell(3, 2).font = Font(name='Arial', bold=True, size=13)
sheet.cell(3, 3).font = Font(name='Arial', bold=True, size=13)
sheet['F19'].font = Font(name='Arial', size=13)
# dump the techs dictionary the sheet
row = 4
for key, value in techs.items():
sheet.cell(row, 1).value = key
sheet.cell(row, 2).value = value
sheet.cell(row, 3).value = f'{"{:.2f}".format(value*100/posts_seen)}%'
sheet.cell(row, 3).alignment = Alignment(horizontal='right')
row += 1
# generates the chart of the results
pie = PieChart()
labels = Reference(sheet, min_col=1, min_row=4, max_row=row - 1)
data = Reference(sheet, min_col=2, min_row=4, max_row=row - 1)
pie.add_data(data)
pie.set_categories(labels)
pie.title = f'Number of times a technology/language was found from {total_found} techs found'
sheet.add_chart(pie, 'F3')
wb.save(f'{position} {date}.xlsx')
def write_text_xlsx_file(used_list):
file1 = open("WordAnalysis.txt", 'w')
word_string = list()
value_string = list()
excel_file = Workbook()
sheet = excel_file.active
end_word = "A" + str(length)
end_data = "B" + str(length)
cell_range_word = sheet["A2": end_word]
cell_range_data = sheet["B2": end_data]
cell_range = sheet["A1": end_data] # 엑셀 범위 지정
sheet["A1"].value = "단어"
sheet["B1"].value = "횟수"
sheet['C1'].value = "많이 쓰인 단어"
sheet['D1'].value = "횟수"
marker = 2 # 셀번호
for word in used_list:
word_string.append(str(word))
sheet["A" + str(marker)] = str(word)
value_string.append(my_list.count(word))
sheet["B"+str(marker)] = my_list.count(word)
marker += 1
file1.write(str(word) + " : " + str(my_list.count(word)) + "\n") # 엑셀, 텍스트파일에 자료 이동
row_num = 0
for row in cell_range:
if row[1].value > (length / 200):
row_num += 1
sheet.cell(row = row_num, column = 4, value = row[1].value)
sheet.cell(row = row_num, column = 3, value = row[0].value) # 파이 차트에서 쓸 값 정리
pie = PieChart()
labels = Reference(sheet, min_col = 3, min_row = 2, max_row = marker - 1)
data = Reference(sheet, min_col = 4, min_row = 1, max_row = marker - 1)
pie.add_data(data, titles_from_data=True)
pie.set_categories(labels)
pie.title = "Significant words in article"
sheet.add_chart(pie, "M1")
excel_file.save("Word_AnalyticsFinal.xlsx")
def pie_chart(wb):
table = wb.get_sheet_by_name(wb.get_sheet_names()[0])
# 生成饼图对象
pie = PieChart()
# 饼图的标题
pie.title = "接口测试统计"
# 获取标签 获得的是范围
labels = Reference(table, min_col=7, min_row=11, max_col=7, max_row=13)
# 获取数据，需要注意的是excel中的data上面需要留个空行
data = Reference(table, min_col=8, min_row=10, max_col=8, max_row=13)
# 添加数据和标签到图表里
pie.add_data(data, titles_from_data=True)
pie.set_categories(labels)
# 添加图表到sheet里
table.add_chart(pie, "C21")
#保存excel
wb.save("test1.xlsx")
def sales_func(filename):
# Load the file and the sheet tab
wb = xl.load_workbook(filename)
sheet = wb['Sheet1']
# Create a new price column based on the original price
for row in range(2, sheet.max_row + 1):
cell = sheet.cell(row, 3)
corrected_price = cell.value * 0.9
corrected_price_cell = sheet.cell(row, 4)
corrected_price_cell.value = corrected_price
# Bar chart
values = Reference(sheet,
min_row=1,
max_row=sheet.max_row,
min_col=3,
max_col=4)
transaction_id = Reference(sheet,
min_row=2,
max_row=sheet.max_row,
min_col=1,
max_col=1)
chart = BarChart3D()
chart.title = '3D Bar Chart'
chart.x_axis.title = "Transaction ID"
chart.y_axis.title = "Adjusted Price ($)"
chart.add_data(values, titles_from_data=True)
chart.set_categories(transaction_id)
sheet.add_chart(chart, 'E2')
# Pie chart
quantities = Reference(sheet, min_row=1, max_row=5, min_col=13, max_col=13)
labels = Reference(sheet, min_row=2, max_row=5, min_col=12, max_col=12)
chart2 = PieChart()
chart2.title = 'Pie Chart'
chart2.add_data(quantities, titles_from_data=True)
chart2.set_categories(labels)
sheet.add_chart(chart2, 'N2')
wb.save(filename)
def make_chart_main(result_file="MainTestResults/MainResult.xlsx",
result_json='MainTestResults/main_result.json'):
try:
wb = load_workbook(os.path.join(current_path, result_file))
if "Main Summary" in wb.sheetnames:
del wb['Main Summary']
wb.create_sheet('Main Summary')
ws = wb['Main Summary']
except:
wb = Workbook()
ws = wb.active
ws.title = 'Main Summary'
with open(os.path.join(current_path, result_json), 'r') as f:
res = json.load(f)
for key in res.keys():
ws.append([key, res[key]])
pie_row_start = 2
pie_row_end = pie_row_start + len(res) - 2
pie = PieChart()
print("pie row start:", pie_row_start)
print("pie row end:", pie_row_end)
labels = Reference(ws,
min_col=1,
min_row=pie_row_start,
max_row=pie_row_end)
data = Reference(ws,
min_col=2,
min_row=pie_row_start - 1,
max_row=pie_row_end)
pie.add_data(data, titles_from_data=True)
pie.set_categories(labels)
pie.title = "main cycle fail rate"
pie.dataLabels = DataLabelList()
pie.dataLabels.showPercent = True
ws.add_chart(pie, "D2")
wb.save(os.path.join(current_path, result_file))
def pie(self):
try:
self.mylogger.info("准备打开用例文件，准备生成图表")
wb = openpyxl.load_workbook(self.caseFile)
self.mylogger.info("成功用例数：{}个，失败用例数：{}个，不执行用例数：{}个 ".format(
self.successNum, self.failedNum, self.noRunNum))
rows = [
['结果', '数量'],
['成功', self.successNum],
['失败', self.failedNum],
['不执行', self.noRunNum],
]
ws = wb.create_sheet('casePie')
# 把值添加在excel中
for row in rows:
ws.append(row)
# 创建图表实例
chart = PieChart()
# 设置类别的取值，min_row和max_row有bug，需要加1
labels = Reference(ws, min_col=1, min_row=2, max_row=5)
# 设置数据的取值，min_col在哪一行取值
data = Reference(ws, min_col=2, min_row=1, max_row=4)
# titles_from_data：鼠标移动上去图表上显示数据
chart.add_data(data, titles_from_data=True)
# 设置标题
chart.title = "用例运行结果图表"
# 设置类别
chart.set_categories(labels=labels)
# 将图表添加在哪个位置
ws.add_chart(chart, 'D1')
wb.save(self.caseFile)
wb.close()
self.mylogger.info("生成图表完成，保存在{}文件中".format(self.caseFile))
except Exception as e:
self.mylogger.debug("出现异常，异常日志：{}".format(e))
def createCharts(sheet, recordsLen, rozmowcyLen, operatorzyLen):
''' Creates openpyxl Chart for xls file.
sheet: openpyxl sheet
recordsLen: int
rozmowcyLen: int
operatorzyLen: int
returns: chartMB, chartSMS, chartMIN, chartPPL'''
chartMB = LineChart()
chartSMS = LineChart()
chartMIN = LineChart()
chartPPL = PieChart()
chartMB.legend = None
chartSMS.legend = None
chartMIN.legend = None
chartMB.title = "MB"
chartSMS.title = "SMS"
chartMIN.title = "MIN"
chartPPL.title = "Ludzie"
data = Reference(sheet, min_row=7, max_row=recordsLen + 7, min_col=2)
chartMB.add_data(data, titles_from_data=False)
data = Reference(sheet, min_row=7, max_row=recordsLen + 7, min_col=3)
chartSMS.add_data(data, titles_from_data=False)
data = Reference(sheet, min_row=7, max_row=recordsLen + 7, min_col=4)
chartMIN.add_data(data, titles_from_data=False)
dates = Reference(sheet, min_row=7, max_row=recordsLen + 7, min_col=1)
chartMB.set_categories(dates)
chartSMS.set_categories(dates)
chartMIN.set_categories(dates)
data = Reference(sheet, min_col=6, min_row=5, max_row=5 + rozmowcyLen)
labels = Reference(sheet, min_col=8, min_row=5, max_row=5 + rozmowcyLen)
chartPPL.add_data(data)
chartPPL.set_categories(labels)
return chartMB, chartSMS, chartMIN, chartPPL
['Category', 'Number'],
['Paid', paidStaff],
['Volunteer', volunteerStaff],
]
wb = Workbook()
ws = wb.active
ws = wb.create_sheet(title="Ministry Paid")
for row in data:
ws.append(row)
pie = PieChart()
labels = Reference(ws, min_col=1, min_row=2, max_row=5)
data = Reference(ws, min_col=2, min_row=1, max_row=5)
pie.add_data(data, titles_from_data=True)
pie.set_categories(labels)
pie.title = "Volunteers vs. Paid"
# Cut the first slice out of the pie
slice = DataPoint(idx=0, explosion=20)
pie.series[0].data_points = [slice]
ws.add_chart(pie, "D1")
wb.save("pie.xlsx")
def drawSummary(self):
self.ws.SetColWid(2,10)
self.ws.SetColWid(3,60)
self.ws.SetColWid(4,20)
self.ws.SetColWid(5,20)
self.ws.SetColWid(6,10)
self.ws.DrawRegion(2,2,66,6,'thick','Blue 1')
self.ws.ws.merge_cells('C3:E3')
fmt1 = {'hAlign':'C','vAlign':'C','font':{'emph':'B','size':14}}
fmt2 = {'hAlign':'L','vAlign':'C','border':{'A':'thin'},'fill':'Blue 2','font':{'emph':'B','size':11}}
fmt2a = {'hAlign':'C','vAlign':'C','border':{'A':'thin'},'fill':'Blue 2','font':{'emph':'B','size':11}}
fmt2b = {'hAlign':'C','vAlign':'C','border':{'A':'thin'},'fill':'Blue 2','numFmt':'0.0','font':{'emph':'B'}}
fmt3 = {'hAlign':'L','vAlign':'C','border':{'A':'thin'},'fill':'Blue 3'}
fmt3a = {'hAlign':'C','vAlign':'C','border':{'A':'thin'},'fill':'Blue 3'}
fmt3b = {'hAlign':'C','vAlign':'C','border':{'A':'thin'},'fill':'Blue 3','numFmt':'0.0','font':{'emph':'B'}}
fmt4 = {'hAlign':'L','vAlign':'C','border':{'A':'thin'},'fill':'Green 1'}
fmt4b = {'hAlign':'C','vAlign':'C','border':{'A':'thin'},'fill':'Green 1','numFmt':'0.0','font':{'emph':'B'}}
fmt5 = {'hAlign':'L','vAlign':'C','border':{'A':'thin'},'fill':'Orange 1'}
fmt5b = {'hAlign':'C','vAlign':'C','border':{'A':'thin'},'fill':'Orange 1','numFmt':'0.0','font':{'emph':'B'}}
fmt6 = {'hAlign':'L','vAlign':'C','border':{'A':'thin'},'fill':'Red 1'}
fmt6b = {'hAlign':'C','vAlign':'C','border':{'A':'thin'},'fill':'Red 1','numFmt':'0.0','font':{'emph':'B'}}
fmt7 = {'hAlign':'L','vAlign':'C','border':{'A':'thin'},'fill':'Yellow 1'}
fmt7b = {'hAlign':'C','vAlign':'C','border':{'A':'thin'},'fill':'Yellow 1','numFmt':'0.0','font':{'emph':'B'}}
self.ws.SetCell( 3, 3,'Monthly Summary Statement: Year To Date (YTD)',fmt1)
self.ws.SetCell( 6, 3,'Hours',fmt2)
self.ws.SetCell( 6, 4,'Hours',fmt2a)
self.ws.SetCell( 6, 5,'As a % of Contracted',fmt2a)
self.ws.SetCell( 7, 3,'Contracted number of hours',fmt3)
self.ws.SetCell( 7, 4,'=SUM(GS243:HE243)',fmt3b)
self.ws.SetCell( 7, 5,None,fmt3b)
self.ws.SetCell( 8, 3,'Total Hours booked',fmt3)
self.ws.SetCell( 8, 4,'=SUM(GS223:HE223)',fmt3b)
self.ws.SetCell( 8, 5,None,fmt3b)
self.ws.SetCell( 9, 3,'Additional hours worked over contracted',fmt3)
self.ws.SetCell( 9, 4,'=SUM(GS242:HE242)',fmt3b)
self.ws.SetCell( 9, 5,None,fmt3b)
self.ws.SetCell(10, 3,'Number of Heads',fmt3)
self.ws.ws.merge_cells('D10:E10')
self.ws.SetCell(10, 4,16,fmt3b)
self.ws.SetCell(10, 5,None,fmt3b)
self.ws.ws.merge_cells('D12:E12')
self.ws.ws.merge_cells('D13:E13')
self.ws.ws.merge_cells('D14:E14')
self.ws.ws.merge_cells('D15:E15')
self.ws.ws.merge_cells('D16:E16')
self.ws.SetCell(12, 3,'Activity (Summary) AVERAGE ACCROSS YEAR',fmt2)
self.ws.SetCell(12, 4,'Percentage %',fmt2a)
self.ws.SetCell(13, 3,'Utilisation (Customer Funded works)',fmt4)
self.ws.SetCell(13, 4,'=HF224',fmt4b)
self.ws.SetCell(13, 5,None,fmt4b)
self.ws.SetCell(14, 3,'Utilisation (Pre Sales work)',fmt5)
self.ws.SetCell(14, 4,'=HF229',fmt5b)
self.ws.SetCell(14, 5,None,fmt5b)
self.ws.SetCell(15, 3,'Utilisation (Downtime,Exc Leave and Sickness)',fmt6)
self.ws.SetCell(15, 4,'=HF234',fmt6b)
self.ws.SetCell(15, 5,None,fmt6b)
self.ws.SetCell(16, 3,'Utilisation (Leave and Sickness)',fmt7)
self.ws.SetCell(16, 4,'=HF239',fmt7b)
self.ws.SetCell(16, 5,None,fmt7b)
self.ws.SetCell(19, 3,'Activity (Detailed)',fmt2)
self.ws.SetCell(19, 4,'Hours',fmt2a)
self.ws.SetCell(19, 5,'As a % of Total',fmt2a)
self.ws.SetCell(20, 3,'Support agreement (Software)',fmt4)
self.ws.SetCell(20, 4,'=SUM(GS201:HE201)',fmt4b)
self.ws.SetCell(20, 5,'=D20/SUM(D20:D27)*100',fmt4b)
self.ws.SetCell(21, 3,'Hardware agreement (Hardware)',fmt4)
self.ws.SetCell(21, 4,'=SUM(GS202:HE202)',fmt4b)
self.ws.SetCell(21, 5,'=D21/SUM(D20:D27)*100',fmt4b)
self.ws.SetCell(22, 3,'Post Sales support (SW-Customer Funded)',fmt4)
self.ws.SetCell(22, 4,'=SUM(GS205:HE205)',fmt4b)
self.ws.SetCell(22, 5,'=D22/SUM(D20:D27)*100',fmt4b)
self.ws.SetCell(23, 3,'Post Sales support (HW-Customer Funded',fmt4)
self.ws.SetCell(23, 4,'=SUM(GS206:HE206)',fmt4b)
self.ws.SetCell(23, 5,'=D23/SUM(D20:D27)*100',fmt4b)
self.ws.SetCell(24, 3,'NRE (Customer funded)',fmt4)
self.ws.SetCell(24, 4,'=SUM(GS207:HE207)',fmt4b)
self.ws.SetCell(24, 5,'=D24/SUM(D20:D27)*100',fmt4b)
self.ws.SetCell(25, 3,'Training - Providing - Non Customer Specific',fmt4)
self.ws.SetCell(25, 4,'=SUM(GS208:HE208)',fmt4b)
self.ws.SetCell(25, 5,'=D25/SUM(D20:D27)*100',fmt4b)
self.ws.SetCell(26, 3,'Training - Providing - Customer Specific',fmt4)
self.ws.SetCell(26, 4,'=SUM(GS209:HE209)',fmt4b)
self.ws.SetCell(26, 5,'=D26/SUM(D20:D27)*100',fmt4b)
self.ws.SetCell(27, 3,'Post Sales Support (Warranty Period)',fmt4)
self.ws.SetCell(27, 4,'=SUM(GS214:HE214)',fmt4b)
self.ws.SetCell(27, 5,'=D27/SUM(D20:D27)*100',fmt4b)
self.ws.SetCell(28, 3,'Pre Sales Support',fmt5)
self.ws.SetCell(28, 4,'=SUM(GS203:HE203)',fmt5b)
self.ws.SetCell(28, 5,'=D28/SUM(D28:D33)*100',fmt5b)
self.ws.SetCell(29, 3,'Post Sales Support (Non contract)',fmt5)
self.ws.SetCell(29, 4,'=SUM(GS204:HE204)',fmt5b)
self.ws.SetCell(29, 5,'=D29/SUM(D28:D33)*100',fmt5b)
self.ws.SetCell(30, 3,'Training - Receiving - Non Customer Specific',fmt5)
self.ws.SetCell(30, 4,'=SUM(GS210:HE210)',fmt5b)
self.ws.SetCell(30, 5,'=D30/SUM(D28:D33)*100',fmt5b)
self.ws.SetCell(31, 3,'Training - Receiving - Customer Specific',fmt5)
self.ws.SetCell(31, 4,'=SUM(GS211:HE211)',fmt5b)
self.ws.SetCell(31, 5,'=D31/SUM(D28:D33)*100',fmt5b)
self.ws.SetCell(32, 3,'Internal Business Meeting',fmt5)
self.ws.SetCell(32, 4,'=SUM(GS212:HE212)',fmt5b)
self.ws.SetCell(32, 5,'=D32/SUM(D28:D33)*100',fmt5b)
self.ws.SetCell(33, 3,'Professional Services',fmt5)
self.ws.SetCell(33, 4,'=SUM(GS213:HE213)',fmt5b)
self.ws.SetCell(33, 5,'=D33/SUM(D28:D33)*100',fmt5b)
self.ws.SetCell(34, 3,'Downtime - Excluding Leave & Sickness',fmt6)
self.ws.SetCell(34, 4,'=SUM(GS232:HE232)',fmt6b)
self.ws.SetCell(34, 5,'=D34/SUM(D34:D34)*100',fmt6b)
self.ws.SetCell(35, 3,'Leave and Sickness',fmt7)
self.ws.SetCell(35, 4,'=SUM(GS237:HE237)',fmt7b)
self.ws.SetCell(35, 5,'=D35/SUM(D35:D35)*100',fmt7b)
self.ws.SetCell(37, 3,'Customer split',fmt2)
self.ws.SetCell(37, 4,'Hours',fmt2a)
self.ws.SetCell(37, 5,'As a % of Total',fmt2a)
self.ws.SetCell(38, 3,'Ericsson',fmt3)
self.ws.SetCell(38, 4,'=SUM(GS247:HE247)',fmt3b)
self.ws.SetCell(38, 5,'=D38/SUM(D38:D44)*100',fmt3b)
self.ws.SetCell(39, 3,'Nokia',fmt3)
self.ws.SetCell(39, 4,'=SUM(GS248:HE248)',fmt3b)
self.ws.SetCell(39, 5,'=D39/SUM(D38:D44)*100',fmt3b)
self.ws.SetCell(40, 3,'Alcatel-Lucent',fmt3)
self.ws.SetCell(40, 4,'=SUM(GS249:HE249)',fmt3b)
self.ws.SetCell(40, 5,'=D40/SUM(D38:D44)*100',fmt3b)
self.ws.SetCell(41, 3,'Sum of all other customers',fmt3)
self.ws.SetCell(41, 4,'=SUM(GS250:HE250)',fmt3b)
self.ws.SetCell(41, 5,'=D41/SUM(D38:D44)*100',fmt3b)
self.ws.SetCell(42, 3,'Cobham',fmt3)
self.ws.SetCell(42, 4,'=SUM(GS251:HE251)',fmt3b)
self.ws.SetCell(42, 5,'=D42/SUM(D38:D44)*100',fmt3b)
self.ws.SetCell(43, 3,'Technical Training - All Types',fmt3)
self.ws.SetCell(43, 4,'=SUM(GS252:HE251)',fmt3b)
self.ws.SetCell(43, 5,'=D43/SUM(D38:D44)*100',fmt3b)
self.ws.SetCell(44, 3,'Customer \'Other\'',fmt3)
self.ws.SetCell(44, 4,'=SUM(GS253:HE251)',fmt3b)
self.ws.SetCell(44, 5,'=D44/SUM(D38:D44)*100',fmt3b)
self.ws.SetCell(46, 3,'Labour and Travel',fmt2)
self.ws.SetCell(46, 4,'Hours',fmt2a)
self.ws.SetCell(46, 5,'As a % of Total',fmt2a)
self.ws.SetCell(47, 3,'Number of Labour Hours',fmt3)
self.ws.SetCell(47, 4,'=SUM(GS217:HE217)',fmt3b)
self.ws.SetCell(47, 5,'=D47/SUM(D47:D49)*100',fmt3b)
self.ws.SetCell(48, 3,'Number of Labour Hours',fmt3)
self.ws.SetCell(48, 4,'=SUM(GS218:HE218)',fmt3b)
self.ws.SetCell(48, 5,'=D48/SUM(D47:D49)*100',fmt3b)
self.ws.SetCell(49, 3,'Number of Labour Hours',fmt3)
self.ws.SetCell(49, 4,'=SUM(GS219:HE219)',fmt3b)
self.ws.SetCell(49, 5,'=D49/SUM(D47:D49)*100',fmt3b)
self.ws.DrawBorder( 6, 3, 10, 5,'medium')
self.ws.DrawBorder( 6, 3, 6, 5,'medium')
self.ws.DrawBorder(12, 3, 16, 5,'medium')
self.ws.DrawBorder(12, 3, 12, 5,'medium')
self.ws.DrawBorder(19, 3, 35, 5,'medium')
self.ws.DrawBorder(19, 3, 19, 5,'medium')
self.ws.DrawBorder(37, 3, 44, 5,'medium')
self.ws.DrawBorder(37, 3, 37, 5,'medium')
self.ws.DrawBorder(46, 3, 49, 5,'medium')
self.ws.DrawBorder(46, 3, 46, 5,'medium')
ltsChart = PieChart()
data = Reference(self.ws.ws,min_col=5,min_row=47,max_row=49)
labels = Reference(self.ws.ws,min_col=3,min_row=47,max_row=49)
ltsChart.add_data(data)
ltsChart.set_categories(labels)
ltsChart.title = 'Labour Vs Travel'
self.ws.ws.add_chart(ltsChart,'H46')
