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 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 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 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 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 __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 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 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_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 graphs(wb):
ws_sky=wb.worksheets[1]
ws_RC=wb.worksheets[2]
ws_both=wb.worksheets[3]
chart_sky = PieChart()
labels_Sky = Reference(ws_sky, min_col = 7, max_col=8, min_row = 1)
data_Sky = Reference(ws_sky, min_col = 7,max_col=8, min_row = 2)
chart_sky.add_data(data_Sky, titles_from_data = True)
# set labels in the chart object
chart_sky.set_categories(labels_Sky)
# set the title of the chart
chart_sky.title = " Porcentaje de Placas "
ws_sky.add_chart(chart_sky, "F9")
chart_RC = PieChart()
labels_RC = Reference(ws_RC, min_col = 8, max_col=9, min_row = 1)
data_RC = Reference(ws_RC, min_col = 8,max_col=9, min_row = 2)
chart_RC.add_data(data_RC, titles_from_data = True)
# set labels in the chart object
chart_RC.set_categories(labels_RC)
# set the title of the chart
chart_RC.title = " Porcentaje de Lineas "
ws_RC.add_chart(chart_RC, "I9")
chart_both = PieChart()
labels_both = Reference(ws_both, min_col = 8, max_col=9, min_row = 1)
data_both = Reference(ws_both, min_col = 8,max_col=9, min_row = 2)
chart_both.add_data(data_both, titles_from_data = True)
# set labels in the chart object
chart_both.set_categories(labels_both)
# set the title of the chart
chart_both.title = " Porcentaje de Viajes "
ws_both.add_chart(chart_both, "F9")
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 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 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 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 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_chert(wb):
table = wb.get_sheet_by_name(wb.get_sheet_by_name()[0])
#生成饼图对象
pie = PieChart()
#图的标题
pie.title = '接口测试统计'
#获取标签 第1列，2行到3行
labels = Reference(table, min_col=1, min_row=2, max_col=1, max_row=3)
# 获取数据，这里需要注意，上面必须多取1行空的，excel系列导致
# 第2列1到3行，实际：第2列2到3行
data = Reference(table, min_col=2, min_row=1, max_col=2, max_row=3)
#添加数据和标签到图表里
pie.add_data(data, titles_from_data=True)
pie.set_categories(labels)
#添加图表到sheet里
table.add_chart(pie, 'A10')
#保存excel
wb.save('D:\\python_code\\re_Automation\\test_folder\\testcase_study.xlsx')
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
def chart_pie(self, ws, destination_cell_coordinate,
label_min_row, label_max_row, label_min_col, label_max_col,
data_min_row, data_max_row, data_min_col, data_max_col,
title='',from_rows=False, chart_height = default_chart_height, chart_width = default_chart_width,
is_3d = False, add_chart = True
, **kw
):
if is_3d:
chart = PieChart3D()
else:
chart = PieChart()
# print("@@@@@@@@@@@@@@@ title = {title} @@@@@@@@@@@@@@@@@@".format(title=title))
# print("default_height={} | width:{} ||| new: {} | {}".format(chart.height, chart.width, chart_height, chart_width))
chart.height = chart_height # default is 7.5
chart.width = chart_width # default is 15
# print("@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@")
# print("@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@")
# print("@@@@@@@@@@@@@@@ title = {title} @@@@@@@@@@@@@@@@@@".format(title=title))
# print("labels = min_col={label_min_col}, max_col={label_max_col}, min_row={label_min_row}, max_row={label_max_row}".format(label_min_col=label_min_col,label_max_col=label_max_col,label_min_row=label_min_row,label_max_row=label_max_row,))
# print("data = min_col={data_min_col}, max_col={data_max_col}, min_row={data_min_row}, max_row={data_max_row}".format(data_min_col=data_min_col,data_max_col=data_max_col,data_min_row=data_min_row,data_max_row=data_max_row,))
# print("from_rows: ", from_rows)
# print("@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@")
# print("@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@")
labels = Reference(ws, min_row=label_min_row, max_row=label_max_row, min_col=label_min_col, max_col=label_max_col,)
data = Reference(ws, min_row=data_min_row, max_row=data_max_row, min_col=data_min_col, max_col=data_max_col,)
chart.add_data(data, titles_from_data=False, from_rows=from_rows)
chart.set_categories(labels)
chart.title = title
if add_chart:
ws.add_chart(chart, destination_cell_coordinate)
return chart
# Charts
data_list = [['Capital', 'SomeInfo'], ['London', 1000], ['Budapest', 999],
['Madrid', 850], ['Paris', 2900], ['New York', 5000]]
ws = wb['London']
for row in data_list:
ws.append(row)
labels = Reference(ws, min_row=2, min_col=1, max_row=6)
data = Reference(ws, min_row=2, min_col=2, max_row=6)
chart = PieChart()
chart.add_data(data)
chart.set_categories(labels)
chart.title = 'Capitals Dummy Information - PieChart'
ws.add_chart(chart, 'D1')
chart = LineChart()
chart.add_data(data)
chart.set_categories(labels)
chart.title = 'Capitals Dummy Information - LineChart'
ws.add_chart(chart, 'M1')
chart = BarChart()
chart.add_data(data)
chart.set_categories(labels)
chart.title = 'Capitals Dummy Information - BarChart'
ws.add_chart(chart, 'D17')
chart = AreaChart()
['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")
ws = wb.create_sheet(title="Projection")
data = [
['Page', 'Views'],
['Search', 95],
['Products', 4],
['Offers', 0.5],
['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")
from openpyxl import Workbook
from openpyxl.chart import PieChart, Reference, Series
wb = Workbook()
ws = wb.active
cs = wb.create_chartsheet()
rows = [
["Bob", 3],
["Harry", 2],
["James", 4],
]
for row in rows:
ws.append(row)
chart = PieChart()
labels = Reference(ws, min_col=1, min_row=1, max_row=3)
data = Reference(ws, min_col=2, min_row=1, max_row=3)
chart.series = (Series(data),)
chart.title = "PieChart"
cs.add_chart(chart)
wb.save("demo.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')
def generate_excel(report,
name,
reporting_start_datetime_local,
reporting_end_datetime_local,
period_type):
wb = Workbook()
ws = wb.active
ws.title = "CombinedEquipmentSaving"
# Row height
ws.row_dimensions[1].height = 102
for i in range(2, 2000 + 1):
ws.row_dimensions[i].height = 42
# Col width
ws.column_dimensions['A'].width = 1.5
ws.column_dimensions['B'].width = 25.0
for i in range(ord('C'), ord('L')):
ws.column_dimensions[chr(i)].width = 15.0
# Font
name_font = Font(name='Arial', size=15, bold=True)
title_font = Font(name='Arial', size=15, bold=True)
table_fill = PatternFill(fill_type='solid', fgColor='1F497D')
f_border = Border(left=Side(border_style='medium', color='00000000'),
right=Side(border_style='medium', color='00000000'),
bottom=Side(border_style='medium', color='00000000'),
top=Side(border_style='medium', color='00000000')
)
b_border = Border(
bottom=Side(border_style='medium', color='00000000'),
)
b_c_alignment = Alignment(vertical='bottom',
horizontal='center',
text_rotation=0,
wrap_text=True,
shrink_to_fit=False,
indent=0)
c_c_alignment = Alignment(vertical='center',
horizontal='center',
text_rotation=0,
wrap_text=True,
shrink_to_fit=False,
indent=0)
b_r_alignment = Alignment(vertical='bottom',
horizontal='right',
text_rotation=0,
wrap_text=True,
shrink_to_fit=False,
indent=0)
# Img
img = Image("excelexporters/myems.png")
ws.add_image(img, 'A1')
# Title
ws['B3'].alignment = b_r_alignment
ws['B3'] = 'Name:'
ws['C3'].border = b_border
ws['C3'].alignment = b_c_alignment
ws['C3'] = name
ws['D3'].alignment = b_r_alignment
ws['D3'] = 'Period:'
ws['E3'].border = b_border
ws['E3'].alignment = b_c_alignment
ws['E3'] = period_type
ws['B4'].alignment = b_r_alignment
ws['B4'] = 'Reporting Start Datetime:'
ws['C4'].border = b_border
ws['C4'].alignment = b_c_alignment
ws['C4'] = reporting_start_datetime_local
ws['D4'].alignment = b_r_alignment
ws['D4'] = 'Reporting End Datetime:'
ws['E4'].border = b_border
ws['E4'].alignment = b_c_alignment
ws['E4'] = reporting_end_datetime_local
if "reporting_period" not in report.keys() or \
"names" not in report['reporting_period'].keys() or len(report['reporting_period']['names']) == 0:
filename = str(uuid.uuid4()) + '.xlsx'
wb.save(filename)
return filename
####################################################################################################################
current_row_number = 6
reporting_period_data = report['reporting_period']
has_names_data_flag = True
if "names" not in reporting_period_data.keys() or \
reporting_period_data['names'] is None or \
len(reporting_period_data['names']) == 0:
has_names_data_flag = False
if has_names_data_flag:
ws['B' + str(current_row_number)].font = title_font
ws['B' + str(current_row_number)] = name + ' ' + 'Reporting Period Savings'
current_row_number += 1
category = reporting_period_data['names']
ca_len = len(category)
ws.row_dimensions[current_row_number].height = 75
ws['B' + str(current_row_number)].fill = table_fill
ws['B' + str(current_row_number)].border = f_border
col = 'C'
for i in range(0, ca_len):
ws[col + str(current_row_number)].fill = table_fill
ws[col + str(current_row_number)].font = name_font
ws[col + str(current_row_number)].alignment = c_c_alignment
ws[col + str(current_row_number)].border = f_border
ws[col + str(current_row_number)] = reporting_period_data['names'][i] + ' ' + \
'(Baseline - Actual)(' + reporting_period_data['units'][i] + ")"
col = chr(ord(col) + 1)
ws[col + str(current_row_number)].fill = table_fill
ws[col + str(current_row_number)].font = name_font
ws[col + str(current_row_number)].alignment = c_c_alignment
ws[col + str(current_row_number)].border = f_border
ws[col + str(current_row_number)] = 'Ton of Standard Coal(Baseline - Actual)(TCE)'
col = chr(ord(col) + 1)
ws[col + str(current_row_number)].fill = table_fill
ws[col + str(current_row_number)].font = name_font
ws[col + str(current_row_number)].alignment = c_c_alignment
ws[col + str(current_row_number)].border = f_border
ws[col + str(current_row_number)] = 'Ton of Carbon Dioxide Emissions(Baseline - Actual)(TCO2E)'
col = chr(ord(col) + 1)
current_row_number += 1
ws['B' + str(current_row_number)].font = title_font
ws['B' + str(current_row_number)].alignment = c_c_alignment
ws['B' + str(current_row_number)].border = f_border
ws['B' + str(current_row_number)] = 'Savings'
col = 'C'
for i in range(0, ca_len):
ws[col + str(current_row_number)].font = name_font
ws[col + str(current_row_number)].alignment = c_c_alignment
ws[col + str(current_row_number)].border = f_border
ws[col + str(current_row_number)] = round(reporting_period_data['subtotals_saving'][i], 2)
col = chr(ord(col) + 1)
ws[col + str(current_row_number)].font = name_font
ws[col + str(current_row_number)].alignment = c_c_alignment
ws[col + str(current_row_number)].border = f_border
ws[col + str(current_row_number)] = round(reporting_period_data['total_in_kgce_saving'] / 1000, 2)
col = chr(ord(col) + 1)
ws[col + str(current_row_number)].font = name_font
ws[col + str(current_row_number)].alignment = c_c_alignment
ws[col + str(current_row_number)].border = f_border
ws[col + str(current_row_number)] = round(reporting_period_data['total_in_kgco2e_saving'] / 1000, 2)
col = chr(ord(col) + 1)
current_row_number += 1
ws['B' + str(current_row_number)].font = title_font
ws['B' + str(current_row_number)].alignment = c_c_alignment
ws['B' + str(current_row_number)].border = f_border
ws['B' + str(current_row_number)] = 'Increment Rate'
col = 'C'
for i in range(0, ca_len):
ws[col + str(current_row_number)].font = name_font
ws[col + str(current_row_number)].alignment = c_c_alignment
ws[col + str(current_row_number)].border = f_border
ws[col + str(current_row_number)] = str(
round(reporting_period_data['increment_rates_saving'][i] * 100, 2)) + '%' \
if reporting_period_data['increment_rates_saving'][i] is not None else '-'
col = chr(ord(col) + 1)
ws[col + str(current_row_number)].font = name_font
ws[col + str(current_row_number)].alignment = c_c_alignment
ws[col + str(current_row_number)].border = f_border
ws[col + str(current_row_number)] = str(
round(reporting_period_data['increment_rate_in_kgce_saving'] * 100, 2)) + '%' \
if reporting_period_data['increment_rate_in_kgce_saving'] is not None else '-'
col = chr(ord(col) + 1)
ws[col + str(current_row_number)].font = name_font
ws[col + str(current_row_number)].alignment = c_c_alignment
ws[col + str(current_row_number)].border = f_border
ws[col + str(current_row_number)] = str(
round(reporting_period_data['increment_rate_in_kgco2e_saving'] * 100, 2)) + '%' \
if reporting_period_data['increment_rate_in_kgco2e_saving'] is not None else '-'
col = chr(ord(col) + 1)
current_row_number += 2
ws['B' + str(current_row_number)].font = title_font
ws['B' + str(current_row_number)] = name + ' ' + 'Ton of Standard Coal(TCE) by Energy Category'
current_row_number += 1
table_start_row_number = current_row_number
chart_start_row_number = current_row_number
ws.row_dimensions[current_row_number].height = 60
ws['B' + str(current_row_number)].fill = table_fill
ws['B' + str(current_row_number)].border = f_border
ws['C' + str(current_row_number)].fill = table_fill
ws['C' + str(current_row_number)].font = name_font
ws['C' + str(current_row_number)].alignment = c_c_alignment
ws['C' + str(current_row_number)].border = f_border
ws['C' + str(current_row_number)] = 'Savings'
ws['D' + str(current_row_number)].fill = table_fill
ws['D' + str(current_row_number)].font = name_font
ws['D' + str(current_row_number)].alignment = c_c_alignment
ws['D' + str(current_row_number)].border = f_border
ws['D' + str(current_row_number)] = 'Ton of Standard Coal(TCE) by Energy Category'
current_row_number += 1
subtotals_in_kgce_saving_sum = sum_list(reporting_period_data['subtotals_in_kgce_saving'])
for i in range(0, ca_len):
ws['B' + str(current_row_number)].font = title_font
ws['B' + str(current_row_number)].alignment = c_c_alignment
ws['B' + str(current_row_number)].border = f_border
ws['B' + str(current_row_number)] = reporting_period_data['names'][i]
ws['C' + str(current_row_number)].font = name_font
ws['C' + str(current_row_number)].alignment = c_c_alignment
ws['C' + str(current_row_number)].border = f_border
ws['C' + str(current_row_number)] = round(reporting_period_data['subtotals_in_kgce_saving'][i] / 1000, 3)
ws['D' + str(current_row_number)].font = name_font
ws['D' + str(current_row_number)].alignment = c_c_alignment
ws['D' + str(current_row_number)].border = f_border
ws['D' + str(current_row_number)] = str(round(reporting_period_data['subtotals_in_kgce_saving'][i] /
subtotals_in_kgce_saving_sum * 100, 2)) + '%'\
if abs(subtotals_in_kgce_saving_sum) > 0 else '-'
current_row_number += 1
table_end_row_number = current_row_number - 1
if ca_len < 4:
current_row_number = current_row_number - ca_len + 4
current_row_number += 1
pie = PieChart()
pie.title = name + ' ' + 'Ton of Standard Coal(TCE) by Energy Category'
labels = Reference(ws, min_col=2, min_row=table_start_row_number + 1, max_row=table_end_row_number)
pie_data = Reference(ws, min_col=3, min_row=table_start_row_number, max_row=table_end_row_number)
pie.add_data(pie_data, titles_from_data=True)
pie.set_categories(labels)
pie.height = 7.25
pie.width = 9
s1 = pie.series[0]
s1.dLbls = DataLabelList()
s1.dLbls.showCatName = False
s1.dLbls.showVal = True
s1.dLbls.showPercent = True
ws.add_chart(pie, 'E' + str(chart_start_row_number))
ws['B' + str(current_row_number)].font = title_font
ws['B' + str(current_row_number)] = name + ' ' + 'Ton of Carbon Dioxide Emissions(TCO2E) by Energy Category'
current_row_number += 1
table_start_row_number = current_row_number
chart_start_row_number = current_row_number
ws.row_dimensions[current_row_number].height = 60
ws['B' + str(current_row_number)].fill = table_fill
ws['B' + str(current_row_number)].border = f_border
ws['C' + str(current_row_number)].fill = table_fill
ws['C' + str(current_row_number)].font = name_font
ws['C' + str(current_row_number)].alignment = c_c_alignment
ws['C' + str(current_row_number)].border = f_border
ws['C' + str(current_row_number)] = 'Savings'
ws['D' + str(current_row_number)].fill = table_fill
ws['D' + str(current_row_number)].font = name_font
ws['D' + str(current_row_number)].alignment = c_c_alignment
ws['D' + str(current_row_number)].border = f_border
ws['D' + str(current_row_number)] = 'Ton of Carbon Dioxide Emissions(TCO2E) by Energy Category'
current_row_number += 1
subtotals_in_kgco2e_saving_sum = sum_list(reporting_period_data['subtotals_in_kgco2e_saving'])
for i in range(0, ca_len):
ws['B' + str(current_row_number)].font = title_font
ws['B' + str(current_row_number)].alignment = c_c_alignment
ws['B' + str(current_row_number)].border = f_border
ws['B' + str(current_row_number)] = reporting_period_data['names'][i]
ws['C' + str(current_row_number)].font = name_font
ws['C' + str(current_row_number)].alignment = c_c_alignment
ws['C' + str(current_row_number)].border = f_border
ws['C' + str(current_row_number)] = round(reporting_period_data['subtotals_in_kgco2e_saving'][i] / 1000, 3)
ws['D' + str(current_row_number)].font = name_font
ws['D' + str(current_row_number)].alignment = c_c_alignment
ws['D' + str(current_row_number)].border = f_border
ws['D' + str(current_row_number)] = str(round(reporting_period_data['subtotals_in_kgco2e_saving'][i] /
subtotals_in_kgco2e_saving_sum * 100, 2)) + '%'\
if abs(subtotals_in_kgco2e_saving_sum) > 0 else '-'
current_row_number += 1
table_end_row_number = current_row_number - 1
if ca_len < 4:
current_row_number = current_row_number - ca_len + 4
current_row_number += 1
pie = PieChart()
pie.title = name + ' ' + 'Ton of Carbon Dioxide Emissions(TCO2E) by Energy Category'
labels = Reference(ws, min_col=2, min_row=table_start_row_number + 1, max_row=table_end_row_number)
pie_data = Reference(ws, min_col=3, min_row=table_start_row_number, max_row=table_end_row_number)
pie.add_data(pie_data, titles_from_data=True)
pie.set_categories(labels)
pie.height = 7.25
pie.width = 9
s1 = pie.series[0]
s1.dLbls = DataLabelList()
s1.dLbls.showCatName = False
s1.dLbls.showVal = True
s1.dLbls.showPercent = True
ws.add_chart(pie, 'E' + str(chart_start_row_number))
####################################################################################################################
has_values_saving_data = True
has_timestamps_data = True
if 'values_saving' not in reporting_period_data.keys() or \
reporting_period_data['values_saving'] is None or \
len(reporting_period_data['values_saving']) == 0:
has_values_saving_data = False
if 'timestamps' not in reporting_period_data.keys() or \
reporting_period_data['timestamps'] is None or \
len(reporting_period_data['timestamps']) == 0 or \
len(reporting_period_data['timestamps'][0]) == 0:
has_timestamps_data = False
if has_values_saving_data and has_timestamps_data:
ca_len = len(reporting_period_data['names'])
time = reporting_period_data['timestamps'][0]
real_timestamps_len = timestamps_data_not_equal_0(report['parameters']['timestamps'])
ws['B' + str(current_row_number)].font = title_font
ws['B' + str(current_row_number)] = name + ' ' + 'Detailed Data'
current_row_number += 1
chart_start_row_number = current_row_number
current_row_number += ca_len * 6 + real_timestamps_len * 7 + 1
table_start_row_number = current_row_number
ws.row_dimensions[current_row_number].height = 60
ws['B' + str(current_row_number)].fill = table_fill
ws['B' + str(current_row_number)].font = title_font
ws['B' + str(current_row_number)].alignment = c_c_alignment
ws['B' + str(current_row_number)].border = f_border
ws['B' + str(current_row_number)] = 'Datetime'
col = 'C'
for i in range(0, ca_len):
ws[col + str(current_row_number)].fill = table_fill
ws[col + str(current_row_number)].font = title_font
ws[col + str(current_row_number)].alignment = c_c_alignment
ws[col + str(current_row_number)].border = f_border
ws[col + str(current_row_number)] = \
reporting_period_data['names'][i] + " (" + reporting_period_data['units'][i] + ")"
col = chr(ord(col) + 1)
current_row_number += 1
for i in range(0, len(time)):
ws['B' + str(current_row_number)].font = title_font
ws['B' + str(current_row_number)].alignment = c_c_alignment
ws['B' + str(current_row_number)].border = f_border
ws['B' + str(current_row_number)] = time[i]
col = 'C'
for j in range(0, ca_len):
ws[col + str(current_row_number)].font = title_font
ws[col + str(current_row_number)].alignment = c_c_alignment
ws[col + str(current_row_number)].border = f_border
ws[col + str(current_row_number)] = round(reporting_period_data['values_saving'][j][i], 2) \
if reporting_period_data['values_saving'][j][i] is not None else 0.00
col = chr(ord(col) + 1)
current_row_number += 1
table_end_row_number = current_row_number - 1
ws['B' + str(current_row_number)].font = title_font
ws['B' + str(current_row_number)].alignment = c_c_alignment
ws['B' + str(current_row_number)].border = f_border
ws['B' + str(current_row_number)] = 'Subtotal'
col = 'C'
for i in range(0, ca_len):
ws[col + str(current_row_number)].font = title_font
ws[col + str(current_row_number)].alignment = c_c_alignment
ws[col + str(current_row_number)].border = f_border
ws[col + str(current_row_number)] = round(reporting_period_data['subtotals_saving'][i], 2)
col = chr(ord(col) + 1)
current_row_number += 2
format_time_width_number = 1.0
min_len_number = 1.0
min_width_number = 11.0 # format_time_width_number * min_len_number + 4 and min_width_number > 11.0
if period_type == 'hourly':
format_time_width_number = 4.0
min_len_number = 2
min_width_number = 12.0
elif period_type == 'daily':
format_time_width_number = 2.5
min_len_number = 4
min_width_number = 14.0
elif period_type == 'monthly':
format_time_width_number = 2.1
min_len_number = 4
min_width_number = 12.4
elif period_type == 'yearly':
format_time_width_number = 1.5
min_len_number = 5
min_width_number = 11.5
for i in range(0, ca_len):
line = LineChart()
line.title = 'Reporting Period Savings - ' + \
reporting_period_data['names'][i] + " (" + reporting_period_data['units'][i] + ")"
labels = Reference(ws, min_col=2, min_row=table_start_row_number + 1, max_row=table_end_row_number)
line_data = Reference(ws, min_col=3 + i, min_row=table_start_row_number, max_row=table_end_row_number)
line.add_data(line_data, titles_from_data=True)
line.set_categories(labels)
line_data = line.series[0]
line_data.marker.symbol = "circle"
line_data.smooth = True
line.height = 8.25
line.width = format_time_width_number * len(time) if len(time) > min_len_number else min_width_number
if line.width > 24:
line.width = 24
line.x_axis.crosses = 'min'
line.dLbls = DataLabelList()
line.dLbls.dLblPos = 't'
line.dLbls.showVal = True
line.dLbls.showPercent = False
chart_col = 'B'
chart_cell = chart_col + str(chart_start_row_number)
chart_start_row_number += 6
ws.add_chart(line, chart_cell)
####################################################################################################################
has_associated_equipment_flag = True
if "associated_equipment" not in report.keys() or \
"energy_category_names" not in report['associated_equipment'].keys() or \
len(report['associated_equipment']["energy_category_names"]) == 0 \
or 'associated_equipment_names_array' not in report['associated_equipment'].keys() \
or report['associated_equipment']['associated_equipment_names_array'] is None \
or len(report['associated_equipment']['associated_equipment_names_array']) == 0 \
or len(report['associated_equipment']['associated_equipment_names_array'][0]) == 0:
has_associated_equipment_flag = False
if has_associated_equipment_flag:
associated_equipment = report['associated_equipment']
ws['B' + str(current_row_number)].font = title_font
ws['B' + str(current_row_number)] = name + ' ' + 'Associated Equipment Data'
current_row_number += 1
table_start_row_number = current_row_number
ws.row_dimensions[current_row_number].height = 60
ws['B' + str(current_row_number)].fill = table_fill
ws['B' + str(current_row_number)].font = name_font
ws['B' + str(current_row_number)].alignment = c_c_alignment
ws['B' + str(current_row_number)].border = f_border
ws['B' + str(current_row_number)] = 'Associated Equipment'
ca_len = len(associated_equipment['energy_category_names'])
for i in range(0, ca_len):
row = chr(ord('C') + i)
ws[row + str(current_row_number)].fill = table_fill
ws[row + str(current_row_number)].font = name_font
ws[row + str(current_row_number)].alignment = c_c_alignment
ws[row + str(current_row_number)].border = f_border
ws[row + str(current_row_number)] = \
reporting_period_data['names'][i] + " (" + reporting_period_data['units'][i] + ")"
associated_equipment_len = len(associated_equipment['associated_equipment_names_array'][0])
for i in range(0, associated_equipment_len):
current_row_number += 1
row = str(current_row_number)
ws['B' + row].font = title_font
ws['B' + row].alignment = c_c_alignment
ws['B' + row] = associated_equipment['associated_equipment_names_array'][0][i]
ws['B' + row].border = f_border
for j in range(0, ca_len):
col = chr(ord('C') + j)
ws[col + row].font = title_font
ws[col + row].alignment = c_c_alignment
ws[col + row] = round(associated_equipment['subtotals_saving_array'][j][i], 2)
ws[col + row].border = f_border
####################################################################################################################
current_sheet_parameters_row_number = chart_start_row_number + 1
has_parameters_names_and_timestamps_and_values_data = True
if 'parameters' not in report.keys() or \
report['parameters'] is None or \
'names' not in report['parameters'].keys() or \
report['parameters']['names'] is None or \
len(report['parameters']['names']) == 0 or \
'timestamps' not in report['parameters'].keys() or \
report['parameters']['timestamps'] is None or \
len(report['parameters']['timestamps']) == 0 or \
'values' not in report['parameters'].keys() or \
report['parameters']['values'] is None or \
len(report['parameters']['values']) == 0 or \
timestamps_data_all_equal_0(report['parameters']['timestamps']):
has_parameters_names_and_timestamps_and_values_data = False
if has_parameters_names_and_timestamps_and_values_data:
################################################################################################################
# new worksheet
################################################################################################################
parameters_data = report['parameters']
parameters_names_len = len(parameters_data['names'])
file_name = (re.sub(r'[^A-Z]', '', ws.title)) + 'aving_'
parameters_ws = wb.create_sheet(file_name + 'Parameters')
parameters_timestamps_data_max_len = \
get_parameters_timestamps_lists_max_len(list(parameters_data['timestamps']))
# Row height
parameters_ws.row_dimensions[1].height = 102
for i in range(2, 7 + 1):
parameters_ws.row_dimensions[i].height = 42
for i in range(8, parameters_timestamps_data_max_len + 10):
parameters_ws.row_dimensions[i].height = 60
# Col width
parameters_ws.column_dimensions['A'].width = 1.5
parameters_ws.column_dimensions['B'].width = 25.0
for i in range(3, 12+parameters_names_len*3):
parameters_ws.column_dimensions[format_cell.get_column_letter(i)].width = 15.0
# Img
img = Image("excelexporters/myems.png")
parameters_ws.add_image(img, 'A1')
# Title
parameters_ws['B3'].alignment = b_r_alignment
parameters_ws['B3'] = 'Name:'
parameters_ws['C3'].border = b_border
parameters_ws['C3'].alignment = b_c_alignment
parameters_ws['C3'] = name
parameters_ws['D3'].alignment = b_r_alignment
parameters_ws['D3'] = 'Period:'
parameters_ws['E3'].border = b_border
parameters_ws['E3'].alignment = b_c_alignment
parameters_ws['E3'] = period_type
parameters_ws['B4'].alignment = b_r_alignment
parameters_ws['B4'] = 'Reporting Start Datetime:'
parameters_ws['C4'].border = b_border
parameters_ws['C4'].alignment = b_c_alignment
parameters_ws['C4'] = reporting_start_datetime_local
parameters_ws['D4'].alignment = b_r_alignment
parameters_ws['D4'] = 'Reporting End Datetime:'
parameters_ws['E4'].border = b_border
parameters_ws['E4'].alignment = b_c_alignment
parameters_ws['E4'] = reporting_end_datetime_local
parameters_ws_current_row_number = 6
parameters_ws['B' + str(parameters_ws_current_row_number)].font = title_font
parameters_ws['B' + str(parameters_ws_current_row_number)] = name + ' ' + 'Parameters'
parameters_ws_current_row_number += 1
parameters_table_start_row_number = parameters_ws_current_row_number
parameters_ws.row_dimensions[parameters_ws_current_row_number].height = 80
parameters_ws_current_row_number += 1
table_current_col_number = 2
for i in range(0, parameters_names_len):
if len(parameters_data['timestamps'][i]) == 0:
continue
col = format_cell.get_column_letter(table_current_col_number)
parameters_ws[col + str(parameters_ws_current_row_number-1)].fill = table_fill
parameters_ws[col + str(parameters_ws_current_row_number-1)].border = f_border
col = format_cell.get_column_letter(table_current_col_number + 1)
parameters_ws[col + str(parameters_ws_current_row_number-1)].fill = table_fill
parameters_ws[col + str(parameters_ws_current_row_number-1)].border = f_border
parameters_ws[col + str(parameters_ws_current_row_number-1)].font = name_font
parameters_ws[col + str(parameters_ws_current_row_number-1)].alignment = c_c_alignment
parameters_ws[col + str(parameters_ws_current_row_number-1)] = parameters_data['names'][i]
table_current_row_number = parameters_ws_current_row_number
for j, value in enumerate(list(parameters_data['timestamps'][i])):
col = format_cell.get_column_letter(table_current_col_number)
parameters_ws[col + str(table_current_row_number)].border = f_border
parameters_ws[col + str(table_current_row_number)].font = title_font
parameters_ws[col + str(table_current_row_number)].alignment = c_c_alignment
parameters_ws[col + str(table_current_row_number)] = value
col = format_cell.get_column_letter(table_current_col_number + 1)
parameters_ws[col + str(table_current_row_number)].border = f_border
parameters_ws[col + str(table_current_row_number)].font = title_font
parameters_ws[col + str(table_current_row_number)].alignment = c_c_alignment
parameters_ws[col + str(table_current_row_number)] = round(parameters_data['values'][i][j], 2)
table_current_row_number += 1
table_current_col_number = table_current_col_number + 3
################################################################################################################
# parameters chart and parameters table
################################################################################################################
ws['B' + str(current_sheet_parameters_row_number)].font = title_font
ws['B' + str(current_sheet_parameters_row_number)] = name + ' ' + 'Parameters'
current_sheet_parameters_row_number += 1
chart_start_row_number = current_sheet_parameters_row_number
col_index = 0
for i in range(0, parameters_names_len):
if len(parameters_data['timestamps'][i]) == 0:
continue
line = LineChart()
data_col = 3+col_index*3
labels_col = 2+col_index*3
col_index += 1
line.title = 'Parameters - ' + \
parameters_ws.cell(row=parameters_table_start_row_number, column=data_col).value
labels = Reference(parameters_ws, min_col=labels_col, min_row=parameters_table_start_row_number + 1,
max_row=(len(parameters_data['timestamps'][i])+parameters_table_start_row_number))
line_data = Reference(parameters_ws, min_col=data_col, min_row=parameters_table_start_row_number,
max_row=(len(parameters_data['timestamps'][i])+parameters_table_start_row_number))
line.add_data(line_data, titles_from_data=True)
line.set_categories(labels)
line_data = line.series[0]
line_data.marker.symbol = "circle"
line_data.smooth = True
line.x_axis.crosses = 'min'
line.height = 8.25
line.width = 24
line.dLbls = DataLabelList()
line.dLbls.dLblPos = 't'
line.dLbls.showVal = False
line.dLbls.showPercent = False
chart_col = 'B'
chart_cell = chart_col + str(chart_start_row_number)
chart_start_row_number += 6
ws.add_chart(line, chart_cell)
current_sheet_parameters_row_number = chart_start_row_number
current_sheet_parameters_row_number += 1
####################################################################################################################
filename = str(uuid.uuid4()) + '.xlsx'
wb.save(filename)
return filename
#c1.y_axis.title = 'Size'
#c1.x_axis.title = 'Test Number'
data = Reference(ws2, min_col=1, min_row=2, max_col=13, max_row=6)
#print data
c1.add_data(data, titles_from_data=True,from_rows=True)
ws2.add_chart(c1, "A20")
#画饼图，先新建一个表单
ws3 = wb2.create_sheet()
_ = ws3.cell(column=1, row=1, value="项目")
_ = ws3.cell(column=2, row=1, value="总计")
for r in range(2,7):
_ = ws3.cell(column=1, row=r, value=ws2.rows[r-1][0].value)
_ = ws3.cell(column=2, row=r, value=ws2.rows[r-1][total_column-1].value)
pie = PieChart()
labels = Reference(ws3, min_col=1, min_row=2, max_row=6)
data = Reference(ws3, min_col=2, min_row=1, max_row=6)
pie.add_data(data, titles_from_data=True)
pie.set_categories(labels)
pie.title = "2015年%s构成图"%ws.rows[5][0].value
ws2.add_chart(pie, "H20")
wb2.save(filename = dest_filename)