def _set_swiss7_chart(self, workbook):
ws = workbook['SWISS7_G']
ws_data = workbook['Data_category']
chart1 = BarChart()
chart1.type = "col"
# chart1.style = 12
chart1.width = 21
chart1.height = 12
chart1.grouping = "stacked"
chart1.overlap = 100
chart1.y_axis.title = 'Volume du trafic en %'
# chart1.x_axis.title = 'Heure'
chart1.gapWidth = 2
data = Reference(ws_data, min_col=2, min_row=4, max_row=28, max_col=8)
# cats = Reference(ws_data, min_col=2, min_row=4, max_row=28)
chart1.add_data(data, titles_from_data=True)
# chart1.set_categories(cats)
chart1.shape = 4
chart1.legend = None
chart1.series[0].graphicalProperties.solidFill = "00a9ff"
chart1.series[1].graphicalProperties.solidFill = "bce273"
chart1.series[2].graphicalProperties.solidFill = "ff708c"
chart1.series[3].graphicalProperties.solidFill = "003366"
chart1.series[4].graphicalProperties.solidFill = "ff00ff"
chart1.series[5].graphicalProperties.solidFill = "ff3399"
chart1.series[6].graphicalProperties.solidFill = "ff99cc"
ws.add_chart(chart1, "A11")
chart1 = BarChart()
chart1.type = "col"
# chart1.style = 12
chart1.width = 21
chart1.height = 12
chart1.grouping = "stacked"
chart1.overlap = 100
chart1.y_axis.title = 'Volume du trafic en %'
# chart1.x_axis.title = 'Heure'
chart1.gapWidth = 2
data = Reference(ws_data, min_col=2, min_row=32, max_row=56, max_col=8)
# cats = Reference(ws_data, min_col=2, min_row=32, max_row=56)
chart1.add_data(data, titles_from_data=True)
# chart1.set_categories(cats)
chart1.shape = 4
chart1.legend = None
chart1.series[0].graphicalProperties.solidFill = "00a9ff"
chart1.series[1].graphicalProperties.solidFill = "bce273"
chart1.series[2].graphicalProperties.solidFill = "ff708c"
chart1.series[3].graphicalProperties.solidFill = "003366"
chart1.series[4].graphicalProperties.solidFill = "ff00ff"
chart1.series[5].graphicalProperties.solidFill = "ff3399"
chart1.series[6].graphicalProperties.solidFill = "ff99cc"
ws.add_chart(chart1, "A46")
def chart_bar(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, show_legend=True
, **kw
):
if is_3d:
chart = BarChart3D()
else:
chart = BarChart()
chart.height = chart_height # default is 7.5
chart.width = chart_width # default is 15
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 not show_legend:
chart.legend = None
if add_chart:
ws.add_chart(chart, destination_cell_coordinate)
for seri in chart.series:
seri.label = True
return chart
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 _draw_bar_chart(self, row_):
"""
画垂直条形图
:param row_: 起始行
:return: None
"""
ws = self._wb['analysis']
bar = BarChart()
bar.type = 'bar'
bar.style = 11
bar.title = '失败接口概况图'
bar.y_axis.title = '通过或失败用例个数'
if row_ != 4:
data = Reference(ws,
min_col=2,
min_row=3,
max_row=row_ - 1,
max_col=3)
else:
data = Reference(ws, min_col=2, min_row=3, max_row=row_, max_col=3)
if row_ != 4:
cats = Reference(ws, min_col=1, min_row=4, max_row=row_ - 1)
else:
cats = Reference(ws, min_col=1, min_row=4, max_row=row_)
bar.add_data(data, titles_from_data=True)
bar.set_categories(cats)
bar.shape = 4
ws.add_chart(bar, "F12")
bar.width = 30
bar.height = 0.5 * (row_ + 20) # 根据行数计算自适应条形图高度
self._log.info('已生成失败接口概况条形图.')
def createGraph(excel_file, df):
sheet_name = "Data"
sheet_name_graphs = "Graphs"
writer = pd.ExcelWriter(excel_file, engine="openpyxl")
book = load_workbook(excel_file)
writer.book = book
writer.sheets = dict((ws.title, ws) for ws in book.worksheets)
ws_graph = writer.book.create_sheet(sheet_name_graphs)
df.to_excel(writer, sheet_name=sheet_name)
ws = writer.sheets[sheet_name]
chart1 = BarChart()
chart1.type = "col"
chart1.style = 10
chart1.title = "ETL Results"
chart1.y_axis.title = "Count"
chart1.x_axis.title = "Programm"
data = Reference(ws, min_col=2, min_row=1, max_row=7, max_col=3)
cats = Reference(ws, min_col=1, min_row=2, max_row=7)
chart1.add_data(data, titles_from_data=True)
chart1.set_categories(cats)
chart1.shape = 4
chart1.height = 12
chart1.width = 20
ws_graph.add_chart(chart1, "A10")
writer.save()
def _do_chi_square_graph(self, ws, title, y_axis, x_axis, labels):
chart = BarChart()
chart.type = 'col'
chart.style = 10
chart.height = 20
chart.width = 30
chart.title = title
chart.y_axis.title = y_axis
chart.x_axis.title = x_axis
# mas uno por la fila de titulos
chart_data = Reference(ws,
min_col=2,
min_row=1,
max_row=len(labels) + 1,
max_col=3)
categories = Reference(ws,
min_col=1,
min_row=2,
max_row=len(labels) + 1)
chart.x_axis.delete = False
chart.y_axis.delete = False
chart.add_data(chart_data, titles_from_data=True)
chart.set_categories(categories)
ws.add_chart(chart, 'I2')
def make_bar(sheet, left_col, top_row, bot_row, title, x_title, y_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)
bar = BarChart()
bar.type = "col"
bar.style = 10
bar.shape = 4
bar.title = title
bar.y_axis.title = y_title
bar.x_axis.title = x_title
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)
bar.add_data(data, titles_from_data=True)
bar.set_categories(labels)
bar.height = height
bar.width = width
sheet.add_chart(bar, print_cell)
def graph(purposes, averages, worksheet):
#writes the titles in the spreadsheet
worksheet.cell(row=1, column=1, value="purposes")
worksheet.cell(row=1, column=2, value="avg_rate")
#writes the purposes with the corresponding averages values in the spreadsheet
for index, item in enumerate(purposes):
worksheet.cell(row=(index + 2), column=1, value=item)
worksheet.cell(row=(index + 2), column=2, value=averages[index])
#creates the Excel bar graph
chart1 = BarChart()
chart1.type = "col"
chart1.style = 13
chart1.height = 20
chart1.width = 20
chart1.title = "Bar Chart"
chart1.y_axis.title = 'Average Rates'
chart1.x_axis.title = 'Purpose'
data = Reference(worksheet, min_col=2, max_col=2, min_row=2, max_row=13)
cats = Reference(worksheet, min_col=1, max_col=1, min_row=2, max_row=13)
chart1.add_data(data)
chart1.set_categories(cats)
chart1.shape = 4
worksheet.add_chart(chart1, "E4") #adds the graph to the spreadsheet
def implementChart(listOfDate, Errordate, save_excel_file_today):
wb_obj = openpyxl.load_workbook(save_excel_file_today)
wb_obj.create_sheet('Chart view')
sheet_obj = wb_obj["Chart view"]
Drows = 1
Erows = 1
cell_obj = sheet_obj.cell(row=1, column=1)
cell_obj.value = "listOfDate"
cell_obj = sheet_obj.cell(row=1, column=2)
cell_obj.value = "Error_data"
for i in listOfDate:
cell_obj = sheet_obj.cell(row=Drows + 1, column=1)
cell_obj.value = i
Drows += 1
for i in Errordate:
cell_obj = sheet_obj.cell(row=Erows + 1, column=2)
cell_obj.value = i
Erows += 1
dates = Reference(sheet_obj, min_col=1, min_row=2, max_col=1, max_row=16)
values = Reference(sheet_obj, min_col=2, min_row=2, max_col=2, max_row=16)
chart = BarChart()
chart.add_data(values)
chart.set_categories(dates)
chart.height = 15 # default is 7.5
chart.width = 30
chart.dataLabels = DataLabelList()
chart.dataLabels.showVal = True
chart.title = " 15 Day's Error Count "
chart.x_axis.title = " Dates "
chart.y_axis.title = " Number of Error "
sheet_obj.add_chart(chart, "A1")
wb_obj.save(save_excel_file_today)
def draw_bar_chart(writer, max_row):
sheet = writer.sheets['死亡率']
date = sheet['A2'].value
# 初始化我们的bar chart
chart = BarChart()
# 指定bar chart 的数据范围
chart_data = Reference(sheet, min_col=6, max_col=6, min_row=2, max_row=max_row-1)
chart_series = Series(chart_data, title='截止{}死亡率'.format(date))
chart_series.graphicalProperties.solidFill = '5DD092' # Silver Tree
# 指定x轴
x_axis_data = Reference(sheet, min_col=3, max_col=3, min_row=2, max_row=max_row-1)
# chart 添加到 sheet里
chart.append(chart_series)
chart.set_categories(x_axis_data)
# 设置chart的样式
chart.height, chart.width = 14, 21
chart.title, chart.y_axis.title = '主要疫情地区死亡率', '死亡率'
chart.y_axis.number_format = '0.0%'
chart.legend.position = 't'
# 画平均死亡率横线line chart
line_chart = LineChart()
line_data = Reference(sheet, min_col=7, max_col=7, min_row=2, max_row=max_row - 1)
line_series = Series(line_data, title='除湖北外死亡率')
line_chart.append(line_series)
# 合并图表
chart += line_chart
# 添加图表并保存
sheet.add_chart(chart, 'I1')
writer.save()
def build_advanced_report_for_ticker(self, wb: openpyxl.Workbook,
scenario_result, ticker: str,
scenario):
periods = scenario["total_periods"]
result = scenario_result.get(ticker)
sheet = wb.create_sheet(ticker)
total_revenue_hist = result["total_revenue_hist"][::-1]
total_revenue_growth = result["total_revenue_growth"][::-1]
total_revenue_growth_mean = result["total_revenue_growth_mean"]
hist_data_len = len(total_revenue_hist)
categories = [x for x in range(-hist_data_len + 1, periods + 1)]
sheet.append([" "] + categories)
sheet.append(["total_revenue"] + total_revenue_hist +
result["total_revenue_forward"])
tr_growth_rate_forward = []
revenue = [total_revenue_hist[-1]] + result["total_revenue_forward"]
for i in range(len(revenue) - 1):
tr_growth_rate_forward.append(revenue[i + 1] / revenue[i] - 1)
sheet.append(["total_revenue_growth"] + total_revenue_growth +
tr_growth_rate_forward)
sheet.append([""])
sheet.append(["prices_t"] + result["prices_t"])
chart = LineChart()
chart.y_axis.title = "Total Revenue"
chart.x_axis.title = "Year"
chart.height = 12
chart.width = 24
revenue_data = Reference(sheet,
min_col=2,
min_row=2,
max_row=2,
max_col=hist_data_len + periods + 1)
cat_data = Reference(sheet,
min_col=2,
min_row=1,
max_row=1,
max_col=hist_data_len + periods + 1)
chart.add_data(revenue_data, from_rows=True)
sheet.add_chart(chart, "F8")
chart.set_categories(cat_data)
bar_chart = BarChart()
bar_chart.y_axis.title = "Total Revenue Growth Rate"
bar_chart.x_axis.title = "Year"
bar_chart.height = 12
bar_chart.width = 24
growth_data = Reference(sheet,
min_col=2,
min_row=3,
max_row=3,
max_col=hist_data_len + periods + 1)
bar_chart.add_data(growth_data, from_rows=True)
bar_chart.set_categories(cat_data)
sheet.add_chart(bar_chart, "S8")
for i in range(scenario["total_periods"]):
pass
def _set_cv_lv_chart(self, workbook):
ws = workbook['CV_LV']
ws_data = workbook['Data_day']
chart1 = BarChart()
chart1.type = "col"
chart1.width = 19
chart1.height = 8
chart1.style = 10
chart1.y_axis.title = 'Vehicules à moteur en % du TJMO de la section'
chart1.x_axis.title = "Selon l'heure de la journée"
chart1.gapWidth = 0
#chart1.legend = None
data = Reference(ws_data,
min_col=11,
min_row=4,
max_row=28,
max_col=11)
cats = Reference(ws, min_col=1, min_row=5, max_row=28)
chart1.add_data(data, titles_from_data=True)
chart1.set_categories(cats)
# chart1.shape = 4
s = chart1.series[0]
s.graphicalProperties.line.solidFill = "000000"
s.graphicalProperties.solidFill = "66ffb2"
chart2 = LineChart()
data = Reference(ws_data,
min_col=11,
min_row=34,
max_row=58,
max_col=11)
chart2.add_data(data, titles_from_data=True)
data = Reference(ws_data,
min_col=11,
min_row=65,
max_row=89,
max_col=11)
chart2.add_data(data, titles_from_data=True)
# chart2.style = 12
s = chart2.series[0]
s.graphicalProperties.line.solidFill = "3333ff"
s.smooth = False
s = chart2.series[1]
s.graphicalProperties.line.solidFill = "ff3333"
s.graphicalProperties.line.dashStyle = "sysDash"
s.smooth = False
chart1 += chart2
ws.add_chart(chart1, "A15")
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 createChart(ws):
chart1 = BarChart()
chart1.type = "col"
chart1.style = 10
chart1.title = "Violation occurrence per area"
chart1.y_axis.title = 'Violation occurrence'
chart1.x_axis.title = 'Violation and Location'
cats = Reference(ws, min_col=1, max_col=2, min_row=2, max_row=ws.max_row)
data = Reference(ws, min_col=5, max_col=5, min_row=1, max_row=ws.max_row)
chart1.add_data(data, titles_from_data=True)
chart1.legend = None
chart1.set_categories(cats)
chart1.width = 30
chart1.height = 15
ws.add_chart(chart1, "K2")
def open_close_delta():
"""Create a sheet for Open/Close delta."""
ws2 = Workbook.wb.create_sheet('Open Close Delta')
mysel, connection = DB_Connection.connect_db()
cursor = connection.execute('SELECT * FROM {}'.format(stock))
names = list(map(lambda x: x[0], cursor.description))
idx = 1
for i in range(6):
if i == 0 or i == 1 or i == 4:
ws2.cell(row=1, column=idx).value = names[i]
idx += 1
counter = 1
"""Write relevant data to Excel."""
for i, row in enumerate(mysel):
post = row[0], row[1], row[4]
counter += 1
ws2.append(post)
Workbook.wb.save(Workbook.wbook_name)
"""Use pandas to create a new field, Open/Close Delta."""
ws2['D1'] = 'Delta'
df = pd.read_excel(Workbook.wbook_name, 'Open Close Delta')
df['Delta'] = df['OpenPrice'] - df['ClosePrice']
for dl in range(2, counter+1):
ws2['D{}'.format(dl)] = float(df['Delta'][dl-2])
"""Plot delta as bar chart."""
ws2.sheet_view.zoomScale = 85
bc_three_d = BarChart()
bc_three_d.type = 'col'
bc_three_d .style = 10
bc_three_d .title = 'Open/Close Delta for Past 30 Trading Days'
bc_three_d.y_axis.title = 'Price'
bc_three_d.x_axis.title = 'Date'
data = Reference(ws2, min_col=4, min_row=counter-29, max_row=counter, max_col=4)
cats = Reference(ws2, min_col=1, min_row=counter-28, max_row=counter)
bc_three_d.add_data(data, titles_from_data=True)
bc_three_d.set_categories(cats)
bc_three_d.height = 20
bc_three_d.width = 30
bc_three_d.legend = None
ws2.add_chart(bc_three_d, 'F2')
Workbook.wb.save(Workbook.wbook_name)
def genChart(self):
'''
Generates a bar graph to visually represent the data.
'''
chart = BarChart()
chart.type = "col"
chart.style = 1
chart.title = None
chart.y_axis.title = 'Force'
chart.x_axis.title = 'Operation'
chartData = Reference(self.workSheet, min_col=1, \
min_row=2, max_row=len(self.forceDataFrame)+1, max_col=1)
chart.add_data(chartData, titles_from_data=None)
chart.legend = None
chart.height = 15
chart.width = 30
self.workSheet.add_chart(chart, "G5")
def create_excel(data, filename):
wb = Workbook(write_only=True)
ws = wb.create_sheet('ChiCuadrado')
max_row = len(data.labels) + 1
labels_xls = ('Intervalo', 'Frecuencia Observada',
'Frecuencia Esperada', 'Estadístico de prueba (C)',
'Estadístico de prueba acumulado (CA)')
ws.append(labels_xls)
if type(data.freq_observ[0]) is tuple:
for i in range(len(data.labels)):
ws.append(
(str(data.labels[i][0][0]), data.freq_observ[i][1],
data.freq_esperadas[i], data.chi_2[i], data.chi_2_ac[i]))
else:
for i in range(len(data.labels)):
ws.append(
(str(data.labels[i][0]) + ' - ' + str(data.labels[i][1]),
data.freq_observ[i], data.freq_esperadas[i],
data.chi_2[i], data.chi_2_ac[i]))
chart = BarChart()
chart.type = 'col'
chart.style = 10
chart.height = 20
chart.width = 30
chart.title = 'Prueba Chi Cuadrado'
chart.y_axis.title = 'Frecuencia'
chart.x_axis.title = 'Intervalo'
chart_data = Reference(ws,
min_col=2,
min_row=1,
max_row=max_row,
max_col=3)
categories = Reference(ws, min_col=1, min_row=2, max_row=max_row)
chart.x_axis.delete = False
chart.y_axis.delete = False
chart.add_data(chart_data, titles_from_data=True)
chart.set_categories(categories)
ws.add_chart(chart, 'I2')
wb.save(f'exports/{filename}.xlsx')
def create_bar_chart(x_cells, y_cells, title, range=None, height=10, width=20):
"""
@fn create_bar_chart()
@brief
@param x_cells カテゴリ参照範囲(Reference)
@param y_cells データ参照範囲(Reference)
@param title タイトル
@param range 値域
@param height グラフの高さ
@param width グラフの幅
@retval chart グラフ
"""
chart = BarChart()
chart.title = title
chart.style = 2
chart.height = height
chart.width = width
chart.legend = None
chart.add_data(y_cells)
chart.set_categories(x_cells)
return chart
def A():
sheetA = wb.create_sheet('전체 출원동향', 0)
A그래프data = Data.전체출원동향()
for r in dataframe_to_rows(A그래프data, index=False, header=True):
sheetA.append(r)
sheetA.insert_cols(2)
for row, cellobj in enumerate(list(sheetA.columns)[1]):
n = '=right(A%d,2)' % (row + 1)
cellobj.value = n
chartA1 = BarChart()
dataA1 = Reference(sheetA, min_col=3, min_row=1, max_row=21)
catsA1 = Reference(sheetA, min_col=2, min_row=2, max_row=21)
chartA1.add_data(dataA1, titles_from_data=True)
chartA1.set_categories(catsA1)
chartA1.y_axis.majorGridlines = None
chartA2 = LineChart()
dataA2 = Reference(sheetA, min_col=4, min_row=1, max_row=21)
chartA2.add_data(dataA2, titles_from_data=True)
chartA2.y_axis.majorGridlines = None
chartA2.y_axis.axId = 2000
# y축 위치 변경
chartA2.y_axis.crosses = 'max'
# 그래프 합치기
chartA1 += chartA2
chartA1.width = 15
chartA1.height = 10
chartA1.legend.position = 't'
chartA1.graphical_properties = GraphicalProperties(
ln=LineProperties(noFill=True)) # 테두리 제거
sheetA.add_chart(chartA1, 'F2')
global savepath
savepath = Data.Save()
def generate_excel(report, name, reporting_start_datetime_local,
reporting_end_datetime_local, period_type):
wb = Workbook()
ws = wb.active
# Row height
ws.row_dimensions[1].height = 118
for i in range(2, 6 + 1):
ws.row_dimensions[i].height = 30
ws.row_dimensions[7].height = 60
ws.row_dimensions[3].height = 50
# Col width
ws.column_dimensions['A'].width = 1.5
ws.column_dimensions['B'].width = 20.0
for i in range(ord('C'), ord('C') + 16):
ws.column_dimensions[chr(i)].width = 15.0
# Font
name_font = Font(name='Constantia', size=15, bold=True)
title_font = Font(name='宋体', size=15, bold=True)
data_font = Font(name='Franklin Gothic Book', size=11)
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)
c_r_alignment = Alignment(vertical='bottom',
horizontal='center',
text_rotation=0,
wrap_text=True,
shrink_to_fit=False,
indent=0)
# Img
img = Image("excelexporters/myems.png")
ws.add_image(img, 'B1')
# Title
ws['B3'].font = name_font
ws['B3'].alignment = b_r_alignment
ws['B3'] = 'Name:'
ws['C3'].border = b_border
ws['C3'].alignment = b_c_alignment
ws['C3'].font = name_font
ws['C3'] = name
ws['D3'].font = name_font
ws['D3'].alignment = b_r_alignment
ws['D3'] = 'Period:'
ws['E3'].border = b_border
ws['E3'].alignment = b_c_alignment
ws['E3'].font = name_font
ws['E3'] = period_type
ws['F3'].font = name_font
ws['F3'].alignment = b_r_alignment
ws['F3'] = 'Date:'
ws['G3'].border = b_border
ws['G3'].alignment = b_c_alignment
ws['G3'].font = name_font
ws['G3'] = reporting_start_datetime_local + "__" + reporting_end_datetime_local
ws.merge_cells("G3:H3")
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
################################################
# First: 趋势
# 6: title
# 7: table title
# 8~ table_data
################################################
has_data_flag = True
reporting_period_data = report['reporting_period']
if "names" not in reporting_period_data.keys() or \
reporting_period_data['names'] is None or \
len(reporting_period_data['names']) == 0:
has_data_flag = False
if "timestamps" not in reporting_period_data.keys() or \
reporting_period_data['timestamps'] is None or \
len(reporting_period_data['timestamps']) == 0:
has_data_flag = False
if "values" not in reporting_period_data.keys() or \
reporting_period_data['values'] is None or \
len(reporting_period_data['values']) == 0:
has_data_flag = False
ca = reporting_period_data['names']
ca_len = len(ca)
temp_max_row = 0
times = reporting_period_data['timestamps']
if has_data_flag:
ws['B6'].font = title_font
ws['B6'] = name + ' 趋势'
ws['B7'].fill = table_fill
ws['B7'].border = f_border
ws['B7'].alignment = c_c_alignment
ws['B7'] = '时间'
time = times[0]
has_data = False
max_row = 0
if len(time) > 0:
has_data = True
max_row = 8 + len(time)
print("max_row", max_row)
temp_max_row = max_row
if has_data:
for i in range(0, len(time)):
col = 'B'
row = str(8 + i)
# col = chr(ord('B') + i)
ws[col + row].font = title_font
ws[col + row].alignment = c_c_alignment
ws[col + row] = time[i]
ws[col + row].border = f_border
for i in range(0, ca_len):
# 38 title
col = chr(ord('C') + i)
ws[col + '7'].fill = table_fill
ws[col + '7'].font = title_font
ws[col + '7'].alignment = c_c_alignment
ws[col + '7'] = reporting_period_data['names'][i]
ws[col + '7'].border = f_border
# 39 data
time = times[i]
time_len = len(time)
for j in range(0, time_len):
row = str(8 + j)
# col = chr(ord('B') + i)
ws[col + row].font = title_font
ws[col + row].alignment = c_c_alignment
ws[col + row] = round(
reporting_period_data['values'][i][j], 0)
ws[col + row].border = f_border
# bar
# 39~: bar
bar = BarChart()
labels = Reference(ws,
min_col=2,
min_row=8,
max_row=max_row + 1)
bar_data = Reference(ws,
min_col=3 + i,
min_row=7,
max_row=max_row + 1) # openpyxl bug
bar.add_data(bar_data, titles_from_data=True)
bar.set_categories(labels)
bar.height = 5.25 # cm 1.05*5 1.05cm = 30 pt
bar.width = 36
# pie.title = "Pies sold by category"
bar.dLbls = DataLabelList()
# bar.dLbls.showCatName = True # label show
bar.dLbls.showVal = True # val show
bar.dLbls.showPercent = True # percent show
# s1 = CharacterProperties(sz=1800) # font size *100
chart_col = chr(ord('B'))
chart_cell = chart_col + str(max_row + 2 + 10 * i)
print("chart_cell", chart_cell)
ws.add_chart(bar, chart_cell)
else:
pass
for i in range(8, temp_max_row + 1 + 1):
ws.row_dimensions[i].height = 20
filename = str(uuid.uuid4()) + '.xlsx'
wb.save(filename)
return filename
def lesfimi_excel_entire_country_stats():
ref_values = {
1: (20, 55, 75),
2: (40, 85, 100),
3: (55, 100, 120),
4: (80, 120, 145),
5: (90, 140, 160),
6: (105, 155, 175),
7: (120, 165, 190),
8: (130, 180, 210),
9: (140, 180, 210),
10: (145, 180, 210),
}
response = HttpResponse(
content_type=
'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet')
response[
'Content-Disposition'] = 'attachment; filename=Lesfimi - Allt Landið.xlsx'
wb = openpyxl.Workbook()
ws = wb.get_active_sheet()
wb.remove_sheet(ws)
tests = (
('b{}_LF_mai17', 'Maí 2017'),
('b{}_LF_jan17', 'Janúar 2017'),
('{}b_LF_sept', 'September 2016'),
)
for test in tests:
identifier = test[0]
title = test[1]
ws = wb.create_sheet(title=title)
ws['A1'] = 'Árgangur'
ws['B1'] = 'Fjöldi nemenda'
ws['C1'] = 'Fjöldi sem þreytti próf'
ws['D1'] = 'Hlutfall sem þreytti próf'
ws['E1'] = 'Hlutfall sem nær 90% viðmiðum'
ws['F1'] = 'Hlutfall sem nær 50% viðmiðum'
ws['G1'] = 'Hlutfall sem nær 25% viðmiðum'
index = 2
errors = []
for year in range(1, 11):
ws['A' + str(index)] = year
survey = Survey.objects.filter(
identifier=identifier.format(year)).first()
studentgroups = StudentGroup.objects.filter(
student_year=year).all()
this_year_result = {
'students': 0,
'students_who_took_test': 0,
'students_over_25pct': 0,
'students_over_50pct': 0,
'students_over_90pct': 0,
}
for studentgroup in studentgroups:
this_year_result['students'] += studentgroup.students.all(
).count()
groupsurveys = GroupSurvey.objects.filter(
studentgroup=studentgroup, survey=survey)
if groupsurveys.all().count() > 1:
errors.append(
'sama próf skráð {} sinnum fyrir {} í {}'.format(
groupsurveys.all().count(), studentgroup.name,
studentgroup.school.name))
for groupsurvey in groupsurveys.all():
for student in studentgroup.students.all():
surveyresults = SurveyResult.objects.filter(
survey=groupsurvey, student=student)
if surveyresults.all().count() > 1:
errors.append(
'{} niðurstöður í sama prófi skráðar fyrir nemanda {} í bekk {} í {}'
.format(
surveyresults.all().count(),
student.ssn,
studentgroup.name,
studentgroup.school.name,
))
for surveyresult in surveyresults.all():
try:
survey_student_result = surveyresult.calculated_results(
)
if not survey_student_result[0] == '':
this_year_result[
'students_who_took_test'] += 1
if int(survey_student_result[0]
) >= ref_values[year][2]:
this_year_result[
'students_over_25pct'] += 1
if int(survey_student_result[0]
) >= ref_values[year][1]:
this_year_result[
'students_over_50pct'] += 1
if int(survey_student_result[0]
) >= ref_values[year][0]:
this_year_result[
'students_over_90pct'] += 1
except:
pass
ws['B' + str(index)] = this_year_result['students']
ws['C' + str(index)] = this_year_result['students_who_took_test']
if this_year_result['students'] > 0 and this_year_result[
'students_who_took_test'] > 0:
ws['D' +
str(index)] = (this_year_result['students_who_took_test'] /
this_year_result['students']) * 100
pct_over_90pct = (
this_year_result['students_over_90pct'] /
this_year_result['students_who_took_test']) * 100
ws['E' + str(index)] = pct_over_90pct
pct_over_50pct = (
this_year_result['students_over_50pct'] /
this_year_result['students_who_took_test']) * 100
ws['F' + str(index)] = pct_over_50pct
pct_over_25pct = (
this_year_result['students_over_25pct'] /
this_year_result['students_who_took_test']) * 100
ws['G' + str(index)] = pct_over_25pct
else:
ws['D' + str(index)] = 0
ws['E' + str(index)] = 0
ws['F' + str(index)] = 0
ws['G' + str(index)] = 0
index += 1
dims = {}
for row in ws.rows:
for cell in row:
if cell.value:
dims[cell.column] = max(
(dims.get(cell.column, 0), len(str(cell.value))))
for col, value in dims.items():
ws.column_dimensions[col].width = int(value) + 2
chart = AreaChart()
chart.title = "Lesfimi í {} - Allt landið".format(title)
chart.style = 10
chart.width = 40
chart.height = 20
chart.layout = Layout(ManualLayout(
xMode="edge",
yMode="edge",
))
chart.x_axis.title = 'Árgangur'
chart.y_axis.title = 'Prósent'
chart.y_axis.scaling.min = 0
chart.y_axis.scaling.max = 100
cats = Reference(ws, min_col=1, min_row=2, max_row=index - 1)
data = Reference(ws, min_col=5, min_row=1, max_col=7, max_row=index)
chart.add_data(data, titles_from_data=True)
chart.set_categories(cats)
bchart = BarChart()
bchart.title = "Hlutfall nemenda sem þreyttu próf"
bchart.style = 10
bchart.width = 20
bchart.height = 10
bchart.x_axis.title = 'Árgangur'
bchart.y_axis.title = 'Prósent'
bchart.y_axis.scaling.min = 0
bchart.y_axis.scaling.max = 100
bdata = Reference(ws, min_col=4, max_col=4, min_row=2, max_row=index)
bchart.add_data(bdata)
bchart.legend = None
bchart.set_categories(cats)
ws.add_chart(bchart, "I1")
if index > 20:
ws.add_chart(chart, "A" + str(index + 2))
else:
ws.add_chart(chart, "A22")
if errors:
ws = wb.create_sheet(title='Villur')
index = 1
for error in errors:
ws['A' + str(index)] = "ATH: " + error
ws['A' + str(index)].fill = PatternFill(start_color='ff0000',
end_color='ff0000',
fill_type='solid')
ws.merge_cells('A' + str(index) + ':F' + str(index))
index += 1
wb.save(filename='/tmp/test.xlsx')
# wb.save(response)
return response
data_clean.append(i)
try:
jumlah_penduduk = data_clean[3]
luas_wilayah_per100 = data_clean[2] / 100
kepadatan = jumlah_penduduk / luas_wilayah_per100
data_clean.append(float(kepadatan))
except:
pass
ws.append(data_clean)
index += 1
len_row = len(data_clean)
#beri judul untuk E1
ws['E1'] = "Kepadatan Penduduk"
chart1 = BarChart()
chart1.type = "col"
chart1.style = 3
chart1.title = "Bar Chart"
chart1.y_axis.title = "Kepadatan per 100m2"
chart1.x_axis.title = "Kecamatan"
data = Reference(ws, min_col=5, min_row=1, max_row=index, max_col=5)
cats = Reference(ws, min_col=2, min_row=2, max_row=index)
chart1.height = 10
chart1.width = 30
chart1.add_data(data, titles_from_data=True)
chart1.set_categories(cats)
ws.add_chart(chart1, "G2")
wb.save("barPenduduk.xlsx")
def generate_excel(report,
name,
reporting_start_datetime_local,
reporting_end_datetime_local,
period_type):
wb = Workbook()
ws = wb.active
# Row height
ws.row_dimensions[1].height = 118
for i in range(2, 2000 + 1):
ws.row_dimensions[i].height = 30
# Col width
ws.column_dimensions['A'].width = 1.5
ws.column_dimensions['B'].width = 25.0
for i in range(ord('C'), ord('I')):
ws.column_dimensions[chr(i)].width = 25.0
# Font
name_font = Font(name='Constantia', size=15, bold=True)
name_small_font = Font(name='Constantia', size=10, bold=True)
title_font = Font(name='宋体', size=15, bold=True)
title_small_font = Font(name='宋体', size=10, bold=True)
data_font = Font(name='Franklin Gothic Book', size=11)
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=False,
shrink_to_fit=False,
indent=0)
c_c_alignment = Alignment(vertical='center',
horizontal='center',
text_rotation=0,
wrap_text=False,
shrink_to_fit=False,
indent=0)
b_r_alignment = Alignment(vertical='bottom',
horizontal='right',
text_rotation=0,
wrap_text=False,
shrink_to_fit=False,
indent=0)
c_r_alignment = Alignment(vertical='bottom',
horizontal='center',
text_rotation=0,
wrap_text=False,
shrink_to_fit=False,
indent=0)
# Img
img = Image("excelexporters/myems.png")
# img = Image("myems.png")
ws.add_image(img, 'B1')
# Title
ws['B3'].font = name_font
ws['B3'].alignment = b_r_alignment
ws['B3'] = 'Name:'
ws['C3'].border = b_border
ws['C3'].alignment = b_c_alignment
ws['C3'].font = name_font
ws['C3'] = name
ws['D3'].font = name_font
ws['D3'].alignment = b_r_alignment
ws['D3'] = 'Period:'
ws['E3'].border = b_border
ws['E3'].alignment = b_c_alignment
ws['E3'].font = name_font
ws['E3'] = period_type
ws['F3'].font = name_font
ws['F3'].alignment = b_r_alignment
ws['F3'] = 'Date:'
ws.merge_cells("G3:J3")
for i in range(ord('G'), ord('K')):
ws[chr(i) + '3'].border = b_border
ws['G3'].alignment = b_c_alignment
ws['G3'].font = name_font
ws['G3'] = reporting_start_datetime_local + "__" + 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 + ' 报告期节约'
current_row_number += 1
category = reporting_period_data['names']
ca_len = len(category)
ws['B' + str(current_row_number)].fill = table_fill
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_small_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)
ws[col + str(current_row_number)].fill = table_fill
ws[col + str(current_row_number)].font = name_small_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)] = '吨标准煤 (基线-实际) (TCE)'
col = chr(ord(col) + 1)
ws[col + str(current_row_number)].fill = table_fill
ws[col + str(current_row_number)].font = name_small_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)] = '吨二氧化碳排放 (基线-实际) (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)] = '节约'
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'], 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'], 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)] = '单位面积值'
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_per_unit_area_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_kgco2e_per_unit_area_saving'], 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_per_unit_area_saving'], 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)] = '环比'
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 + ' 吨标准煤(TCE)占比'
current_row_number += 1
table_start_row_number = current_row_number
chart_start_row_number = current_row_number
ws['B' + str(current_row_number)].fill = table_fill
ws['C' + str(current_row_number)].fill = table_fill
ws['C' + str(current_row_number)].font = name_small_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)] = '吨标准煤(TCE)占比'
current_row_number += 1
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], 2)
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 = '吨标准煤(TCE)占比'
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 = 5.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, 'D' + str(chart_start_row_number))
ws['B' + str(current_row_number)].font = title_font
ws['B' + str(current_row_number)] = name + ' 吨二氧化碳排放(TCO2E)占比'
current_row_number += 1
table_start_row_number = current_row_number
chart_start_row_number = current_row_number
ws['B' + str(current_row_number)].fill = table_fill
ws['C' + str(current_row_number)].fill = table_fill
ws['C' + str(current_row_number)].font = name_small_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)] = '吨二氧化碳排放(TCO2E)占比'
current_row_number += 1
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], 2)
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 = '吨二氧化碳排放(TCO2E)占比'
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 = 5.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, 'D' + 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]
ws['B' + str(current_row_number)].font = title_font
ws['B' + str(current_row_number)] = name + ' 详细数据'
current_row_number += 1
chart_start_row_number = current_row_number
current_row_number += ca_len * 5
table_start_row_number = current_row_number
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)] = '日期时间'
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)] = '小计'
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):
bar = BarChart()
bar.title = \
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)
bar_data = Reference(ws, min_col=3 + i, min_row=table_start_row_number, max_row=table_end_row_number)
bar.add_data(bar_data, titles_from_data=True)
bar.set_categories(labels)
bar.height = 5.25
bar.width = format_time_width_number * len(time) if len(time) > min_len_number else min_width_number
bar.dLbls = DataLabelList()
bar.dLbls.showVal = True
bar.dLbls.showPercent = True
chart_col = 'B'
chart_cell = chart_col + str(chart_start_row_number)
chart_start_row_number += 5
ws.add_chart(bar, chart_cell)
filename = str(uuid.uuid4()) + '.xlsx'
wb.save(filename)
return filename
def generate_excel(report,
name,
reporting_start_datetime_local,
reporting_end_datetime_local,
period_type):
wb = Workbook()
ws = wb.active
# Row height
ws.row_dimensions[1].height = 121
for i in range(2, 37 + 1):
ws.row_dimensions[i].height = 30
for i in range(38, 90 + 1):
ws.row_dimensions[i].height = 30
# Col width
ws.column_dimensions['A'].width = 1.5
ws.column_dimensions['B'].width = 20.0
for i in range(ord('C'), ord('I')):
ws.column_dimensions[chr(i)].width = 15.0
# Font
name_font = Font(name='Constantia', size=15, bold=True)
title_font = Font(name='宋体', size=15, bold=True)
# data_font = Font(name='Franklin Gothic Book', size=11)
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=False,
shrink_to_fit=False,
indent=0)
c_c_alignment = Alignment(vertical='center',
horizontal='center',
text_rotation=0,
wrap_text=False,
shrink_to_fit=False,
indent=0)
b_r_alignment = Alignment(vertical='bottom',
horizontal='right',
text_rotation=0,
wrap_text=False,
shrink_to_fit=False,
indent=0)
# c_r_alignment = Alignment(vertical='bottom',
# horizontal='center',
# text_rotation=0,
# wrap_text=False,
# shrink_to_fit=False,
# indent=0)
# Img
img = Image("excelexporters/myems.png")
# img = Image("myems.png")
ws.add_image(img, 'B1')
# Title
ws['B3'].font = name_font
ws['B3'].alignment = b_r_alignment
ws['B3'] = 'Name:'
ws['C3'].border = b_border
ws['C3'].alignment = b_c_alignment
ws['C3'].font = name_font
ws['C3'] = name
ws['D3'].font = name_font
ws['D3'].alignment = b_r_alignment
ws['D3'] = 'Period:'
ws['E3'].border = b_border
ws['E3'].alignment = b_c_alignment
ws['E3'].font = name_font
ws['E3'] = period_type
ws['F3'].font = name_font
ws['F3'].alignment = b_r_alignment
ws['F3'] = 'Date:'
ws['G3'].border = b_border
ws['G3'].alignment = b_c_alignment
ws['G3'].font = name_font
ws['G3'] = reporting_start_datetime_local + "__" + reporting_end_datetime_local
ws.merge_cells("G3:H3")
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
#################################################
# First: 统计分析
# 6: title
# 7: table title
# 8~11 table_data
# Total: 6 rows
# if has not energy data: set low height for rows
#################################################
reporting_period_data = report['reporting_period']
has_energy_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_energy_data_flag = False
filename = str(uuid.uuid4()) + '.xlsx'
wb.save(filename)
return filename
if has_energy_data_flag:
ws['B6'].font = title_font
ws['B6'] = name + ' 统计分析'
# ws['D6'].font = title_font
# ws['D6'] = '面积' +report['space']['area']
category = reporting_period_data['names']
# table_title
ws['B7'].fill = table_fill
ws['B7'].font = title_font
ws['B7'].alignment = c_c_alignment
ws['B7'] = '报告期'
ws['B7'].border = f_border
ws['C7'].font = title_font
ws['C7'].alignment = c_c_alignment
ws['C7'] = '算术平均数'
ws['C7'].border = f_border
ws['D7'].font = title_font
ws['D7'].alignment = c_c_alignment
ws['D7'] = '中位数'
ws['D7'].border = f_border
ws['E7'].font = title_font
ws['E7'].alignment = c_c_alignment
ws['E7'] = '最小值'
ws['E7'].border = f_border
ws['F7'].font = title_font
ws['F7'].alignment = c_c_alignment
ws['F7'] = '最大值'
ws['F7'].border = f_border
ws['G7'].font = title_font
ws['G7'].alignment = c_c_alignment
ws['G7'] = '样本标准差'
ws['G7'].border = f_border
ws['H7'].font = title_font
ws['H7'].alignment = c_c_alignment
ws['H7'] = '样本方差'
ws['H7'].border = f_border
# table_data
for i, value in enumerate(category):
row = i*2 + 8
ws['B' + str(row)].font = name_font
ws['B' + str(row)].alignment = c_c_alignment
ws['B' + str(row)] = reporting_period_data['names'][i] + " (" + reporting_period_data['units'][i] + " )"
ws['B' + str(row)].border = f_border
ws['B' + str(row + 1)].font = name_font
ws['B' + str(row + 1)].alignment = c_c_alignment
ws['B' + str(row + 1)] = "环比"
ws['B' + str(row + 1)].border = f_border
ws['C' + str(row)].font = name_font
ws['C' + str(row)].alignment = c_c_alignment
ws['C' + str(row)] = round(reporting_period_data['means'][i], 2) \
if reporting_period_data['means'][i] is not None else ''
ws['C' + str(row)].border = f_border
ws['C' + str(row + 1)].font = name_font
ws['C' + str(row + 1)].alignment = c_c_alignment
ws['C' + str(row + 1)] = str(round(reporting_period_data['means_increment_rate'][i] * 100, 2)) + "%" \
if reporting_period_data['means_increment_rate'][i] is not None else '0.00%'
ws['C' + str(row + 1)].border = f_border
ws['D' + str(row)].font = name_font
ws['D' + str(row)].alignment = c_c_alignment
ws['D' + str(row)] = round(reporting_period_data['medians'][i], 2) \
if reporting_period_data['medians'][i] is not None else ''
ws['D' + str(row)].border = f_border
ws['D' + str(row + 1)].font = name_font
ws['D' + str(row + 1)].alignment = c_c_alignment
ws['D' + str(row + 1)] = str(round(reporting_period_data['medians_increment_rate'][i] * 100, 2)) + "%" \
if reporting_period_data['medians_increment_rate'][i] is not None else '0.00%'
ws['D' + str(row + 1)].border = f_border
ws['E' + str(row)].font = name_font
ws['E' + str(row)].alignment = c_c_alignment
ws['E' + str(row)] = round(reporting_period_data['minimums'][i], 2) \
if reporting_period_data['minimums'][i] is not None else ''
ws['E' + str(row)].border = f_border
ws['E' + str(row + 1)].font = name_font
ws['E' + str(row + 1)].alignment = c_c_alignment
ws['E' + str(row + 1)] = str(round(reporting_period_data['minimums_increment_rate'][i] * 100, 2)) + "%" \
if reporting_period_data['minimums_increment_rate'][i] is not None else '0.00%'
ws['E' + str(row + 1)].border = f_border
ws['F' + str(row)].font = name_font
ws['F' + str(row)].alignment = c_c_alignment
ws['F' + str(row)] = round(reporting_period_data['maximums'][i], 2) \
if reporting_period_data['maximums'][i] is not None else ''
ws['F' + str(row)].border = f_border
ws['F' + str(row + 1)].font = name_font
ws['F' + str(row + 1)].alignment = c_c_alignment
ws['F' + str(row + 1)] = str(round(reporting_period_data['maximums_increment_rate'][i] * 100, 2)) + "%" \
if reporting_period_data['maximums_increment_rate'][i] is not None else '0.00%'
ws['F' + str(row + 1)].border = f_border
ws['G' + str(row)].font = name_font
ws['G' + str(row)].alignment = c_c_alignment
ws['G' + str(row)] = round(reporting_period_data['stdevs'][i], 2) \
if reporting_period_data['stdevs'][i] is not None else ''
ws['G' + str(row)].border = f_border
ws['G' + str(row + 1)].font = name_font
ws['G' + str(row + 1)].alignment = c_c_alignment
ws['G' + str(row + 1)] = str(round(reporting_period_data['stdevs_increment_rate'][i] * 100, 2)) + "%" \
if reporting_period_data['stdevs_increment_rate'][i] is not None else '0.00%'
ws['G' + str(row + 1)].border = f_border
ws['H' + str(row)].font = name_font
ws['H' + str(row)].alignment = c_c_alignment
ws['H' + str(row)] = round(reporting_period_data['variances'][i], 2) \
if reporting_period_data['variances'][i] is not None else ''
ws['H' + str(row)].border = f_border
ws['H' + str(row + 1)].font = name_font
ws['H' + str(row + 1)].alignment = c_c_alignment
ws['H' + str(row + 1)] = str(round(reporting_period_data['variances_increment_rate'][i] * 100, 2)) + "%" \
if reporting_period_data['variances_increment_rate'][i] is not None else '0.00%'
ws['H' + str(row + 1)].border = f_border
#################################################
# First: 统计分析
# 13: title
# 14: table title
# 15~18 table_data
# Total: 6 rows
# if has not energy data: set low height for rows
#################################################
if has_energy_data_flag:
ws['B13'].font = title_font
ws['B13'] = name + ' 单位面积值'
ws['D13'].font = title_font
ws['D13'] = str(report['space']['area']) + 'M²'
category = reporting_period_data['names']
# table_title
ws['B14'].fill = table_fill
ws['B14'].font = title_font
ws['B14'].alignment = c_c_alignment
ws['B14'] = '报告期'
ws['B14'].border = f_border
ws['C14'].font = title_font
ws['C14'].alignment = c_c_alignment
ws['C14'] = '算术平均数'
ws['C14'].border = f_border
ws['D14'].font = title_font
ws['D14'].alignment = c_c_alignment
ws['D14'] = '中位数'
ws['D14'].border = f_border
ws['E14'].font = title_font
ws['E14'].alignment = c_c_alignment
ws['E14'] = '最小值'
ws['E14'].border = f_border
ws['F14'].font = title_font
ws['F14'].alignment = c_c_alignment
ws['F14'] = '最大值'
ws['F14'].border = f_border
ws['G14'].font = title_font
ws['G14'].alignment = c_c_alignment
ws['G14'] = '样本标准差'
ws['G14'].border = f_border
ws['H14'].font = title_font
ws['H14'].alignment = c_c_alignment
ws['H14'] = '样本方差'
ws['H14'].border = f_border
# table_data
for i, value in enumerate(category):
row = i + 15
ws['B' + str(row)].font = name_font
ws['B' + str(row)].alignment = c_c_alignment
ws['B' + str(row)] = reporting_period_data['names'][i] + " (" + reporting_period_data['units'][
i] + "/M²)"
ws['B' + str(row)].border = f_border
ws['C' + str(row)].font = name_font
ws['C' + str(row)].alignment = c_c_alignment
if reporting_period_data['means_per_unit_area'][i] \
or reporting_period_data['means_per_unit_area'][i] == 0:
ws['C' + str(row)] = round(reporting_period_data['means_per_unit_area'][i], 2)
ws['C' + str(row)].border = f_border
ws['D' + str(row)].font = name_font
ws['D' + str(row)].alignment = c_c_alignment
if reporting_period_data['medians_per_unit_area'][i] \
or reporting_period_data['medians_per_unit_area'][i] == 0:
ws['D' + str(row)] = round(reporting_period_data['medians_per_unit_area'][i], 2)
ws['D' + str(row)].border = f_border
ws['E' + str(row)].font = name_font
ws['E' + str(row)].alignment = c_c_alignment
if reporting_period_data['minimums_per_unit_area'][i] \
or reporting_period_data['minimums_per_unit_area'][i] == 0:
ws['E' + str(row)] = round(reporting_period_data['minimums_per_unit_area'][i], 2)
ws['E' + str(row)].border = f_border
ws['F' + str(row)].font = name_font
ws['F' + str(row)].alignment = c_c_alignment
if reporting_period_data['maximums_per_unit_area'][i] \
or reporting_period_data['maximums_per_unit_area'][i] == 0:
ws['F' + str(row)] = round(reporting_period_data['maximums_per_unit_area'][i], 2)
ws['F' + str(row)].border = f_border
ws['G' + str(row)].font = name_font
ws['G' + str(row)].alignment = c_c_alignment
if (reporting_period_data['stdevs_per_unit_area'][i]) \
or reporting_period_data['stdevs_per_unit_area'][i] == 0:
ws['G' + str(row)] = round(reporting_period_data['stdevs_per_unit_area'][i], 2)
ws['G' + str(row)].border = f_border
ws['H' + str(row)].font = name_font
ws['H' + str(row)].alignment = c_c_alignment
if reporting_period_data['variances_per_unit_area'][i] \
or reporting_period_data['variances_per_unit_area'][i] == 0:
ws['H' + str(row)] = round(reporting_period_data['variances_per_unit_area'][i], 2)
ws['H' + str(row)].border = f_border
################################################
# Fourth:
# 20: title
# 21~ 26+ca_len*5-1: line
# 26+ca_len*5: table title
# 26+ca_len*5~: table_data
################################################
reporting_period_data = report['reporting_period']
times = reporting_period_data['timestamps']
has_detail_data_flag = True
ca_len = len(report['reporting_period']['names'])
table_row = 27
if "timestamps" not in reporting_period_data.keys() or \
reporting_period_data['timestamps'] is None or \
len(reporting_period_data['timestamps']) == 0:
has_detail_data_flag = False
if has_detail_data_flag:
ws['B20'].font = title_font
ws['B20'] = name + ' 报告期消耗'
ws['B26'].font = title_font
ws['B26'] = name + ' 详细数据'
# table
ws['B' + str(table_row)].fill = table_fill
ws['B' + str(table_row)].border = f_border
ws['B' + str(table_row)].alignment = c_c_alignment
ws['B' + str(table_row)] = '日期时间'
time = times[0]
has_data = False
max_row = 0
if len(time) > 0:
has_data = True
max_row = table_row + len(time)
# print("max_row", max_row)
if has_data:
# time
time_len = len(time)
for index in range(0, len(time)):
col = 'B'
row = str(table_row + 1 + index)
# col = chr(ord('B') + i)
ws[col + row].font = title_font
ws[col + row].alignment = c_c_alignment
ws[col + row] = time[index]
ws[col + row].border = f_border
if index == time_len - 1:
row = str(table_row + 2 + index)
ws['B' + row].font = title_font
ws['B' + row].alignment = c_c_alignment
ws['B' + row] = "小计"
ws['B' + row].border = f_border
# data
for index in range(0, ca_len):
# table_title
col = chr(ord('C') + index)
ws[col + str(table_row)].fill = table_fill
ws[col + str(table_row)].font = title_font
ws[col + str(table_row)].alignment = c_c_alignment
ws[col + str(table_row)] = (reporting_period_data['names'][index] + "(" +
reporting_period_data['units'][index] + ")")
ws[col + str(table_row)].border = f_border
# data
time = times[index]
time_len = len(time)
for j in range(0, time_len):
row = str(table_row + 1 + j)
# col = chr(ord('B') + i)
ws[col + row].font = title_font
ws[col + row].alignment = c_c_alignment
ws[col + row] = round(reporting_period_data['values'][index][j], 0)
ws[col + row].border = f_border
# subtotals
if j == time_len - 1:
row = str(table_row + 2 + j)
ws[col + row].font = title_font
ws[col + row].alignment = c_c_alignment
ws[col + row] = round(reporting_period_data['subtotals'][index], 0)
ws[col + row].border = f_border
# bar
bar = BarChart()
labels = Reference(ws, min_col=2, min_row=table_row + 1, max_row=max_row)
bar_data = Reference(ws, min_col=3 + index, min_row=table_row, max_row=max_row) # openpyxl bug
bar.add_data(bar_data, titles_from_data=True)
bar.set_categories(labels)
bar.height = 5.25 # cm 1.05*5 1.05cm = 30 pt
bar.width = 18
# pie.title = "Pies sold by category"
bar.dLbls = DataLabelList()
# bar.dLbls.showCatName = True # label show
bar.dLbls.showVal = True # val show
bar.dLbls.showPercent = True # percent show
# s1 = CharacterProperties(sz=1800) # font size *100
chart_col = 'B'
chart_cell = chart_col + str(21 + 5 * index)
ws.add_chart(bar, chart_cell)
# ################################################
# # Fourth: 相关参数
# # table_row+2: title
# # 21~ 26+ca_len*5-1: LineChart
# # 26+ca_len*5: table title
# # 26+ca_len*5~: table_data
# ################################################
#
# reporting_period_data = report['parameters']
# times = reporting_period_data['timestamps']
# has_detail_data_flag = True
# ca_len = len(reporting_period_data['names'])
filename = str(uuid.uuid4()) + '.xlsx'
wb.save(filename)
return filename
def sheet_partecipazione(filename, start_time, participants, workbook):
worksheet = workbook.create_sheet('Partecipazione')
titles = ['Partecipante']
max_interruptions = 0
for pp in participants:
if len(pp['periods']) > max_interruptions:
max_interruptions = len(pp['periods'])
for i in range(max_interruptions):
titles.append(f'Inizio {i+1}')
titles.append(f'Fine {i+1}')
worksheet.append(titles)
for cell in worksheet["1:1"]:
cell.font = Font(bold=True)
i = 1
for pp in participants:
i = i + 1
cur_start = start_time
row = [pp['name']]
for j in range(len(pp['periods'])):
p = pp['periods'][j]
row.append(p[0] - cur_start)
row.append(p[1] - p[0])
cur_start = p[1]
worksheet.append(row)
for ii in range(1, len(row) + 1):
cell = worksheet.cell(i, ii)
cell.number_format = 'hh "ore e" mm "minuti"'
column_widths = [30]
for _i in range(max_interruptions * 2):
column_widths.append(20)
for ii, column_width in enumerate(column_widths):
worksheet.column_dimensions[get_column_letter(ii +
1)].width = column_width
max_col = get_column_letter((max_interruptions * 2) + 1)
mediumStyle = TableStyleInfo(name='TableStyleMedium2', showRowStripes=True)
worksheet.add_table(
Table(ref=f'A1:{max_col}{i}',
displayName='RegistroPartecipazione',
tableStyleInfo=mediumStyle))
worksheet.sheet_view.showGridLines = False
report_id = os.path.basename(filename).replace('.json', '')
worksheet.append([f''])
worksheet.append([f'Report generato per il meeting con ID: {report_id}'])
data = Reference(worksheet,
min_col=2,
min_row=1,
max_row=i,
max_col=(max_interruptions * 2) + 1)
cats = Reference(worksheet, min_col=1, min_row=2, max_row=i)
chart = BarChart()
chart.add_data(data, titles_from_data=True)
chart.set_categories(cats)
chart.type = "bar"
chart.grouping = "stacked"
chart.overlap = 100
chart.legend = None
chart.height = 20
chart.width = 40
chart.x_axis.scaling.orientation = "maxMin"
for ii, s in enumerate(chart.series):
s.graphicalProperties.line.nofill = True
s.graphicalProperties.line.shadow = None
if ii % 2 == 0:
s.graphicalProperties.noFill = True
else:
s.graphicalProperties.solidFill = "008F00"
worksheet.add_chart(chart, "A43")
def pop_excl (sv_dict, ClusName):
wb = openpyxl.Workbook ()
sh = wb.active
count1 = 0
count2 = 2
alph = ['a', 'b', 'c', 'd']
f = sh['a1']
f.font = Font (bold=True)
f = sh['b1']
f.font = Font (bold=True)
sh.title = 'HighLevel'
sh['a1'] = 'StorageView'
sh['b1'] = 'Size(G)'
for i in sv_dict:
sh[alph[count1] + str (count2)] = i
count1 += 1
sh[alph[count1] + str (count2)] = float (sv_dict[i][-1][-1])
count2 += 1
count1 = 0
count2 = 2
for i in sv_dict:
sh = wb.create_sheet (i)
sh = wb.get_sheet_by_name (i)
f = sh['a1']
f.font = Font (bold=True)
f = sh['b1']
f.font = Font (bold=True)
f = sh['c1']
f.font = Font (bold=True)
f = sh['d1']
f.font = Font (bold=True)
sh['a1'] = 'LunID'
sh['b1'] = 'Name'
sh['c1'] = 'VPD'
sh['d1'] = 'Size(G/T)'
for j in range (len (sv_dict[i])):
for k in range (4):
sh[alph[count1] + str (count2)] = sv_dict[i][j][k]
count1 += 1
count2 += 1
count1 = 0
count2 = 2
logging.debug('Start of chart')
l = len(sv_dict)
sh = wb.get_sheet_by_name ('HighLevel')
logging.debug('sheets: %s' % (wb.get_sheet_names ()))
logging.debug('sh: %s' % (sh.title))
chart1 = BarChart()
chart1.type = "col"
chart1.style = 11
chart1.title = "VPlex Capacity Report"
chart1.y_axis.title = 'Size'
chart1.x_axis.title = 'View Name'
logging.debug('len of sv_dict: %d' % (l))
data = Reference(sh, min_col=2, min_row=2, max_row=l + 1, max_col=2)
cats = Reference(sh, min_col=1, min_row=2, max_row=l + 1)
chart1.add_data(data, titles_from_data=False)
chart1.set_categories(cats)
chart1.top = 100
chart1.left = 30
chart1.width = 27
chart1.height = 10
chart1.shape = sh.add_chart(chart1, "D2")
wb.save (ClusName)
return 0
def Excel(a, b, c, d):
#Create Data For Excel
PCount = b.count("Pass") #counts passes
FCount = b.count("Fail") #counts failures
FPerc = ((FCount / len(b)) * 100
) #creates a percentage of failures to total operations
FAvg = sum(a) / len(a) #calculates average forced required for press fit
#Start Workbook
wb = Workbook()
#Creating Sheet 1
ws1 = wb.active
ws1.title = d
#Entering Headers
ws1['A1'] = 'Force'
#Turning Data Into An Array of Vectors (Columns in Excel)
data_out1 = column_stack([a])
#Writing to Excel
for row in data_out1.tolist():
ws1.append(row)
ws1['C2'] = 'Total Pass'
ws1['C7'] = 'Total Fail'
ws1['C12'] = 'Percent Fail'
ws1['C17'] = 'Average Force'
ws1['E2'] = PCount
ws1['E7'] = FCount
ws1['E12'] = FPerc
ws1['E17'] = '%.4f' % FAvg
#Merging Cells
ws1.merge_cells('C2:D3')
ws1.merge_cells('E2:E3')
ws1.merge_cells('C7:D8')
ws1.merge_cells('E7:E8')
ws1.merge_cells('C12:D13')
ws1.merge_cells('E12:E13')
ws1.merge_cells('C17:D18')
ws1.merge_cells('E17:E18')
#Creating Alignment Properties of Cells
CELLS = ['A1', 'C2', 'E2', 'C7', 'E7', 'C12', 'E12', 'C17',
'E17'] #List of cells subject to alignment
#For Loop to Align the Cells in CELLS
for i in CELLS:
ws1[i].alignment = Alignment(horizontal='center', vertical='center')
#Defining Border Properties and Applying them with For Loops
thick_border = Border(left=Side(border_style=BORDER_THICK,
color='00000000'), )
for j in range(17):
ws1.cell(row=j + 2, column=3).border = thick_border
ws1.cell(row=j + 2, column=3).border = thick_border
j += 1
for j in range(17):
ws1.cell(row=j + 2, column=6).border = thick_border
ws1.cell(row=j + 2, column=6).border = thick_border
j += 1
#Creating Bar Graph
chart1 = BarChart()
chart1.type = "col"
chart1.style = 1
chart1.title = None
chart1.y_axis.title = 'Force'
chart1.x_axis.title = ''
ChartData = Reference(ws1,
min_col=1,
min_row=2,
max_row=len(a) + 1,
max_col=1)
chart1.add_data(ChartData, titles_from_data=None)
chart1.legend = None
chart1.height = 15
chart1.width = 30
ws1.add_chart(chart1, "G5")
#Saving/Closing
wb.save(c)
def ronava_bar_chart(writingSheet, dataSheet, params):
# TODO add dictionary in parameters to avoid overlapping
if params["use"] == "bars":
data = Reference(
dataSheet,
min_col=params["data_min_col"],
min_row=params["data_min_row"],
max_row=params["data_max_row"],
max_col=params["data_max_col"],
)
cats = Reference(
dataSheet,
min_col=params["cats_min_col"],
min_row=params["cats_min_row"],
max_row=params["cats_max_row"],
max_col=params["cats_max_col"],
)
chart = BarChart()
chart.type = params["type"]
chart.style = 12
# chart.grouping = "stacked"
chart.title = params["title"]
chart.y_axis.title = params["y_axis"]
chart.x_axis.title = params["x_axis"]
chart.add_data(data, titles_from_data=True)
chart.set_categories(cats)
chart.height = params["heigth"]
chart.width = params["width"]
writingSheet.add_chart(chart, "D2")
elif params["use"] == "single":
c1 = BarChart()
v1 = Reference(
dataSheet, min_col=params["data_min_col"], min_row=params["data_min_row"], max_col=params["data_max_col"]
)
cats = Reference(
dataSheet, min_col=params["cats_min_col"], min_row=params["cats_min_row"], max_col=params["cats_max_col"]
)
c1.series = [Series(v1, title_from_data=True)]
c1.style = 12
c1.set_categories(cats)
c1.x_axis.title = params["x_axis"]
c1.y_axis.title = params["y_axis"]
c1.height = params["heigth"]
c1.width = params["width"]
c1.title = params["title"]
writingSheet.add_chart(c1, "D4")
else:
c1 = BarChart()
v1 = Reference(
dataSheet, min_col=params["data_min_col"], min_row=params["data_min_row"], max_col=params["data_max_col"]
)
cats = Reference(
dataSheet, min_col=params["cats_min_col"], min_row=params["cats_min_row"], max_col=params["cats_max_col"]
)
c1.series = [Series(v1, title_from_data=True)]
c1.y_axis.majorGridlines = None
c1.set_categories(cats)
c1.x_axis.title = params["x_axis"]
c1.y_axis.title = params["y_axis"]
c1.height = params["heigth"]
c1.width = params["width"]
c1.title = params["title"]
c1.style = 12
# Create a second chart
c2 = LineChart()
v2 = Reference(
dataSheet,
min_col=params["data_min_col"],
min_row=params["data_min_row"] + 1,
max_col=params["data_max_col"],
)
c2.series = [Series(v2, title_from_data=True)]
c2.y_axis.axId = 20
c2.y_axis.title = "Porcentaje Produccion"
# Assign the y-axis of the second chart to the third axis of the first chart
c1.z_axis = c2.y_axis
c1.y_axis.crosses = "max"
c1 += c2
writingSheet.add_chart(c1, "D4")
def transcribe_client_data_to_workbooks(client):
report_path = r'C:\Program Files\Notepad++\reports' + '\\' + string.replace(
client, '/', '-') + '_report.csv'
wb = openpyxl.load_workbook(
'C:\Program Files\Notepad++\Bucket-Asset Allocation Model.xlsm',
read_only=False,
keep_vba=True)
wb.active = 5
ws = wb.active
with open(report_path, 'rU') as f:
reader = csv.reader(f)
for row_index, row in enumerate(reader):
for column_index, cell in enumerate(row):
column_letter = get_column_letter((column_index + 1))
if column_letter == 'A':
if row_index > 0:
ws[column_letter + str(row_index + 1)] = long(
string.replace(string.replace(cell, 'Z', '9'), 'X',
'0'))
else:
ws[column_letter + str(row_index + 1)] = cell
#Proposed allocation pie chart creation
#Close the file
f.close()
#Proposed Allocation pie chart creation
wb.active = 1
ws = wb.active
pie1 = PieChart()
labels1 = Reference(ws, min_col=14, min_row=19, max_row=21)
data1 = Reference(ws, min_col=15, min_row=18, max_row=21)
pie1.add_data(data1, titles_from_data=True)
pie1.set_categories(labels1)
pie1.title = 'Proposed Allocation'
pie1.height = 14
pie1.width = 18
ws.add_chart(pie1, "F20")
pie1.dataLabels = DataLabelList()
pie1.dataLabels.showPercent = True
#Current allocation pie chart creation
pie2 = PieChart()
labels2 = Reference(ws, min_col=14, min_row=29, max_row=31)
data2 = Reference(ws, min_col=15, min_row=28, max_row=31)
pie2.add_data(data2, titles_from_data=True)
pie2.set_categories(labels2)
pie2.title = 'Current Allocation'
pie2.height = 14
pie2.width = 18
ws.add_chart(pie2, 'B20')
pie2.dataLabels = DataLabelList()
pie2.dataLabels.showPercent = True
#Bar chart creation
#Change to allocations worksheet
wb.active = 3
ws = wb.active
#Short term bucket bar graph creation
chart1 = BarChart()
chart1.type = "col"
chart1.style = 12
chart1.title = "Bucket I \n Short - Term"
data = Reference(ws, min_col=14, min_row=61, max_row=62, max_col=15)
cats = Reference(ws, min_col=13, min_row=62)
chart1.add_data(data, titles_from_data=True)
chart1.set_categories(cats)
chart1.shape = 4
ws.add_chart(chart1, "B25")
chart1.dataLabels = DataLabelList()
chart1.dataLabels.showVal = True
#Intermediate term bucket bar graph creation
chart2 = BarChart()
chart2.type = "col"
chart2.style = 10
chart2.title = "Bucket II \n Intermediate - Term"
data2 = Reference(ws, min_col=14, min_row=63, max_row=64, max_col=15)
cats2 = Reference(ws, min_col=13, min_row=64)
chart2.add_data(data2, titles_from_data=True)
chart2.set_categories(cats2)
chart2.shape = 4
ws.add_chart(chart2, "I25")
chart2.dataLabels = DataLabelList()
chart2.dataLabels.showVal = True
#Long term bucket bar graph creation
chart3 = BarChart()
chart3.type = "col"
chart3.style = 13
chart3.title = "Bucket III \n Long - Term"
data3 = Reference(ws, min_col=14, min_row=65, max_row=66, max_col=15)
cats3 = Reference(ws, min_col=13, min_row=66)
chart3.add_data(data3, titles_from_data=True)
chart3.set_categories(cats3)
chart3.shape = 4
ws.add_chart(chart3, "P25")
chart3.dataLabels = DataLabelList()
chart3.dataLabels.showVal = True
#0 portfolio bar graph creation
chart4 = BarChart()
chart4.type = "col"
chart4.style = 10
chart4.height = 10
chart4.width = 20
chart4.title = "0 Portfolio"
data4 = Reference(ws, min_col=14, min_row=57, max_row=58, max_col=15)
cats4 = Reference(ws, min_col=14, min_row=57, max_col=15)
chart4.add_data(data4, titles_from_data=True)
chart4.set_categories(cats4)
chart4.shape = 4
ws.add_chart(chart4, "B3")
chart4.dataLabels = DataLabelList()
chart4.dataLabels.showVal = True
#Allocation Comparison Bar graph creation
chart5 = BarChart()
chart5.type = "col"
chart5.style = 10
chart5.title = "Allocation Comparison"
chart5.height = 10
chart5.width = 20
data5 = Reference(ws, min_col=10, min_row=62, max_row=64, max_col=12)
cats5 = Reference(ws, min_col=9, min_row=63, max_row=64)
chart5.add_data(data5, titles_from_data=True)
chart5.set_categories(cats5)
chart5.shape = 4
ws.add_chart(chart5, "M3")
chart5.dataLabels = DataLabelList()
chart5.dataLabels.showVal = True
print(len(set(cdict.values())))
#Attempt to save, move on if the file is open for reading.
try:
wb.save('C:\\Program Files\\Notepad++\\workbooks\\' +
string.replace(client, '/', '-') + '_workbook.xlsm')
print(string.replace(client, '/', '-') + ' workbook completed')
except IOError:
print("Workbook already open, cannot overwrite, moving on.")
def prepare_utilization_chart_worksheet():
"""
Objective: This function prepares a Bar chart in utilization chart worksheet
Input: None
Output : None
Use it: prepare_utilization_chart_worksheet()
"""
# inbound chart
inbound_chart = BarChart() # Bar chart
inbound_chart.height = 15 # default is 7.5
inbound_chart.width = 30 # default is 15
#inbound_chart.grouping = "stacked"
#inbound_chart.overlap = 100
#inbound_chart.type = "col"
#inbound_chart.style = 13
inbound_chart.title = "Bandwidth Utilization Chart - Inbound - Avg/Max (util %)"
inbound_chart.y_axis.title = 'Avg/Max Utilization(%)'
inbound_chart.x_axis.title = 'Device'
inbound_data = Reference(worksheet=utilization_chart_worksheet,
min_row=1,
min_col=3,
max_row=chart_rows - 1,
max_col=4)
inbound_chart.add_data(inbound_data, titles_from_data=True)
categories = Reference(worksheet=utilization_chart_worksheet,
min_row=2,
min_col=1,
max_row=chart_rows - 1,
max_col=1)
inbound_chart.set_categories(categories)
utilization_chart_worksheet.add_chart(inbound_chart, "H2")
#outbound chart
outbound_chart = BarChart() # Bar chart
outbound_chart.height = 15 # default is 7.5
outbound_chart.width = 30 # default is 15
#outbound_chart.grouping = "stacked"
#outbound_chart.overlap = 100
#outbound_chart.type = "col"
#outbound_chart.style = 13
outbound_chart.title = "Bandwidth Utilization Chart - Outbound - Avg/Max (util %)"
outbound_chart.y_axis.title = 'Avg/Max Utilization(%)'
outbound_chart.x_axis.title = 'Device'
outbound_data = Reference(worksheet=utilization_chart_worksheet,
min_row=1,
min_col=5,
max_row=chart_rows - 1,
max_col=6)
outbound_chart.add_data(outbound_data, titles_from_data=True)
categories = Reference(worksheet=utilization_chart_worksheet,
min_row=2,
min_col=1,
max_row=chart_rows - 1,
max_col=1)
outbound_chart.set_categories(categories)
utilization_chart_worksheet.add_chart(outbound_chart, "H32")
report_workbook.save(output_file)
def generate_excel(report, name, reporting_start_datetime_local,
reporting_end_datetime_local, period_type):
wb = Workbook()
# todo
ws = wb.active
# Row height
ws.row_dimensions[1].height = 118
for i in range(2, 2000 + 1):
ws.row_dimensions[i].height = 30
# Col width
ws.column_dimensions['A'].width = 1.5
for i in range(ord('B'), ord('I')):
ws.column_dimensions[chr(i)].width = 18
# Font
name_font = Font(name='Constantia', size=15, bold=True)
title_font = Font(name='宋体', size=15, bold=True)
table_title_font = Font(name='宋体', size=10, bold=True)
data_font = Font(name='Franklin Gothic Book', size=11)
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=False,
shrink_to_fit=False,
indent=0)
c_c_alignment = Alignment(vertical='center',
horizontal='center',
text_rotation=0,
wrap_text=False,
shrink_to_fit=False,
indent=0)
b_r_alignment = Alignment(vertical='bottom',
horizontal='right',
text_rotation=0,
wrap_text=False,
shrink_to_fit=False,
indent=0)
c_r_alignment = Alignment(vertical='bottom',
horizontal='center',
text_rotation=0,
wrap_text=False,
shrink_to_fit=False,
indent=0)
# Img
img = Image("excelexporters/myems.png")
ws.add_image(img, 'B1')
# Title
ws['B3'].font = name_font
ws['B3'].alignment = b_r_alignment
ws['B3'] = 'Name:'
ws['C3'].border = b_border
ws['C3'].alignment = b_c_alignment
ws['C3'].font = name_font
ws['C3'] = name
ws['D3'].font = name_font
ws['D3'].alignment = b_r_alignment
ws['D3'] = 'Period:'
ws['E3'].border = b_border
ws['E3'].alignment = b_c_alignment
ws['E3'].font = name_font
ws['E3'] = period_type
ws['F3'].font = name_font
ws['F3'].alignment = b_r_alignment
ws['F3'] = 'Date:'
ws.merge_cells("G3:J3")
for i in range(ord('G'), ord('K')):
ws[chr(i) + '3'].border = b_border
ws['G3'].alignment = b_c_alignment
ws['G3'].font = name_font
ws['G3'] = reporting_start_datetime_local + "__" + reporting_end_datetime_local
if "reporting_period" not in report.keys() or \
"timestamps" not in report['reporting_period'].keys() or len(report['reporting_period']['timestamps']) == 0:
filename = str(uuid.uuid4()) + '.xlsx'
wb.save(filename)
return filename
###############################
has_names_data_flag = True
if "names" not in report['reporting_period'].keys() or len(
report['reporting_period']['names']) == 0:
has_names_data_flag = False
current_row_number = 6
if has_names_data_flag:
reporting_period_data = report['reporting_period']
category = reporting_period_data['names']
ca_len = len(category)
ws['B' + str(current_row_number)].font = title_font
ws['B' + str(current_row_number)] = name + '报告期平均负荷'
current_row_number += 1
ws['B' + str(current_row_number)].fill = table_fill
for i in range(0, ca_len):
col = chr(ord('C') + i)
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] + \
" (" + reporting_period_data['units'][i] + "/H)"
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)] = '平均负荷'
for i in range(0, ca_len):
col = chr(ord('C') + i)
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['averages'][i], 2)
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)] = '单位面积值'
for i in range(0, ca_len):
col = chr(ord('C') + i)
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['averages_per_unit_area'][i], 2)
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)] = '环比'
for i in range(0, ca_len):
col = chr(ord('C') + i)
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['averages_increment_rate'][i] * 100, 2)) + "%" \
if reporting_period_data['averages_increment_rate'][i] is not None else "-"
current_row_number += 2
ws['B' + str(current_row_number)].font = title_font
ws['B' + str(current_row_number)] = name + '报告期最大负荷'
current_row_number += 1
ws['B' + str(current_row_number)].fill = table_fill
for i in range(0, ca_len):
col = chr(ord('C') + i)
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] + \
" (" + reporting_period_data['units'][i] + "/H)"
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)] = '最大负荷'
for i in range(0, ca_len):
col = chr(ord('C') + i)
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['maximums'][i], 2)
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)] = '单位面积值'
for i in range(0, ca_len):
col = chr(ord('C') + i)
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['maximums_per_unit_area'][i], 2)
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)] = '环比'
for i in range(0, ca_len):
col = chr(ord('C') + i)
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['maximums_increment_rate'][i] * 100, 2)) + "%" \
if reporting_period_data['maximums_increment_rate'][i] is not None else "-"
current_row_number += 2
ws['B' + str(current_row_number)].font = title_font
ws['B' + str(current_row_number)] = name + '报告期负荷系数'
current_row_number += 1
ws['B' + str(current_row_number)].fill = table_fill
for i in range(0, ca_len):
col = chr(ord('C') + i)
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]
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)] = '负荷系数'
for i in range(0, ca_len):
col = chr(ord('C') + i)
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['factors'][i], 2) \
if reporting_period_data['factors'][i] is not None else '-'
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)] = '环比'
for i in range(0, ca_len):
col = chr(ord('C') + i)
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['factors_increment_rate'][i] * 100, 2)) + "%" \
if reporting_period_data['factors_increment_rate'][i] is not None else "-"
current_row_number += 2
has_sub_averages_data_flag = True
has_sub_maximums_data_flag = True
if "sub_averages" not in report['reporting_period'].keys() or len(
report['reporting_period']['sub_averages']) == 0:
has_sub_averages_data_flag = False
if "sub_averages" not in report['reporting_period'].keys() or len(
report['reporting_period']['sub_averages']) == 0:
has_sub_maximums_data_flag = False
if has_sub_averages_data_flag or has_sub_maximums_data_flag:
reporting_period_data = report['reporting_period']
category = reporting_period_data['names']
ca_len = len(category)
times = reporting_period_data['timestamps']
time = times[0]
ws['B' + str(current_row_number)].font = title_font
ws['B' + str(current_row_number)] = name + '详细数据'
current_row_number += 1
chart_start_number = current_row_number
if has_sub_averages_data_flag:
current_row_number = (current_row_number + ca_len * 5)
if has_sub_maximums_data_flag:
current_row_number = (current_row_number + ca_len * 5)
table_start_number = current_row_number
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)] = '日期时间'
col = 'C'
for i in range(0, ca_len):
if has_sub_averages_data_flag:
ws[col + str(current_row_number)].fill = table_fill
ws[col + str(current_row_number)].font = table_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] + "/H)"
col = chr(ord(col) + 1)
if has_sub_maximums_data_flag:
ws[col + str(current_row_number)].fill = table_fill
ws[col + str(current_row_number)].font = table_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] + "/H)"
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):
if has_sub_averages_data_flag:
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['sub_averages'][j][i], 2) \
if reporting_period_data['sub_averages'][j][i] is not None else 0.00
col = chr(ord(col) + 1)
if has_sub_maximums_data_flag:
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['sub_maximums'][j][i], 2) \
if reporting_period_data['sub_maximums'][j][i] is not None else 0.00
col = chr(ord(col) + 1)
current_row_number += 1
table_end_number = current_row_number - 1
current_chart_col_number = 3
current_chart_row_number = chart_start_number
for i in range(0, ca_len):
labels = Reference(ws,
min_col=2,
min_row=table_start_number + 1,
max_row=table_end_number)
if has_sub_averages_data_flag:
bar = BarChart()
datas = Reference(ws,
min_col=current_chart_col_number,
min_row=table_start_number,
max_row=table_end_number)
bar.add_data(datas, titles_from_data=True)
bar.set_categories(labels)
bar.height = 5.25
bar.width = len(time)
bar.dLbls = DataLabelList()
bar.dLbls.showVal = True
ws.add_chart(bar, "B" + str(current_chart_row_number))
current_chart_row_number += 5
current_chart_col_number += 1
if has_sub_maximums_data_flag:
bar = BarChart()
datas = Reference(ws,
min_col=current_chart_col_number,
min_row=table_start_number,
max_row=table_end_number)
bar.add_data(datas, titles_from_data=True)
bar.set_categories(labels)
bar.height = 5.25
bar.width = len(time)
bar.dLbls = DataLabelList()
bar.dLbls.showVal = True
ws.add_chart(bar, "B" + str(current_chart_row_number))
current_chart_row_number += 5
current_chart_col_number += 1
filename = str(uuid.uuid4()) + '.xlsx'
wb.save(filename)
return filename
def generate_excel(report, name, reporting_start_datetime_local,
reporting_end_datetime_local, period_type):
wb = Workbook()
# todo
ws = wb.active
# Row height
ws.row_dimensions[1].height = 118
for i in range(2, 11 + 1):
ws.row_dimensions[i].height = 30
for i in range(12, 43 + 1):
ws.row_dimensions[i].height = 30
# Col width
ws.column_dimensions['A'].width = 1.5
for i in range(ord('B'), ord('I')):
ws.column_dimensions[chr(i)].width = 15.0
# Font
name_font = Font(name='Constantia', size=15, bold=True)
title_font = Font(name='宋体', size=15, bold=True)
data_font = Font(name='Franklin Gothic Book', size=11)
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=False,
shrink_to_fit=False,
indent=0)
c_c_alignment = Alignment(vertical='center',
horizontal='center',
text_rotation=0,
wrap_text=False,
shrink_to_fit=False,
indent=0)
b_r_alignment = Alignment(vertical='bottom',
horizontal='right',
text_rotation=0,
wrap_text=False,
shrink_to_fit=False,
indent=0)
c_r_alignment = Alignment(vertical='bottom',
horizontal='center',
text_rotation=0,
wrap_text=False,
shrink_to_fit=False,
indent=0)
# Img
img = Image("excelexporters/myems.png")
ws.add_image(img, 'B1')
# Title
ws['B3'].font = name_font
ws['B3'].alignment = b_r_alignment
ws['B3'] = 'Name:'
ws['C3'].border = b_border
ws['C3'].alignment = b_c_alignment
ws['C3'].font = name_font
ws['C3'] = name
ws['D3'].font = name_font
ws['D3'].alignment = b_r_alignment
ws['D3'] = 'Period:'
ws['E3'].border = b_border
ws['E3'].alignment = b_c_alignment
ws['E3'].font = name_font
ws['E3'] = period_type
ws['F3'].font = name_font
ws['F3'].alignment = b_r_alignment
ws['F3'] = 'Date:'
ws.merge_cells("G3:H3")
ws['G3'].border = b_border
ws['G3'].alignment = b_c_alignment
ws['G3'].font = name_font
ws['G3'] = reporting_start_datetime_local + "__" + reporting_end_datetime_local
if "reporting_period" not in report.keys() or \
"values" not in report['reporting_period'].keys() or len(report['reporting_period']['values']) == 0:
filename = str(uuid.uuid4()) + '.xlsx'
wb.save(filename)
return filename
###############################
has_cost_data_flag = True
if "values" not in report['reporting_period'].keys() or len(
report['reporting_period']['values']) == 0:
has_cost_data_flag = False
if has_cost_data_flag:
ws['B6'].font = title_font
ws['B6'] = name + '报告期成本'
reporting_period_data = report['reporting_period']
category = report['offline_meter']['energy_category_name']
ca_len = len(category)
ws['B7'].fill = table_fill
ws['B8'].font = title_font
ws['B8'].alignment = c_c_alignment
ws['B8'] = '成本'
ws['B8'].border = f_border
ws['B9'].font = title_font
ws['B9'].alignment = c_c_alignment
ws['B9'] = '环比'
ws['B9'].border = f_border
col = 'B'
for i in range(0, ca_len):
col = chr(ord('C') + i)
ws[col + '7'].fill = table_fill
ws[col + '7'].font = name_font
ws[col + '7'].alignment = c_c_alignment
ws[col + '7'] = report['offline_meter']['energy_category_name'] + \
" (" + report['offline_meter']['unit_of_measure'] + ")"
ws[col + '7'].border = f_border
ws[col + '8'].font = name_font
ws[col + '8'].alignment = c_c_alignment
ws[col + '8'] = round(reporting_period_data['total_in_category'],
0)
ws[col + '8'].border = f_border
ws[col + '9'].font = name_font
ws[col + '9'].alignment = c_c_alignment
ws[col + '9'] = str(round(reporting_period_data['increment_rate'] * 100, 2)) + "%" \
if reporting_period_data['increment_rate'] is not None else "-"
ws[col + '9'].border = f_border
# TCE TCO2E
end_col = col
# TCE
tce_col = chr(ord(end_col) + 1)
ws[tce_col + '7'].fill = table_fill
ws[tce_col + '7'].font = name_font
ws[tce_col + '7'].alignment = c_c_alignment
ws[tce_col + '7'] = "TCE"
ws[tce_col + '7'].border = f_border
ws[tce_col + '8'].font = name_font
ws[tce_col + '8'].alignment = c_c_alignment
ws[tce_col + '8'] = round(reporting_period_data['total_in_kgce'], 0)
ws[tce_col + '8'].border = f_border
ws[tce_col + '9'].font = name_font
ws[tce_col + '9'].alignment = c_c_alignment
ws[tce_col + '9'] = str(round(reporting_period_data['increment_rate'] * 100, 2)) + "%" \
if reporting_period_data['increment_rate'] is not None else "-"
ws[tce_col + '9'].border = f_border
# TCO2E
tco2e_col = chr(ord(end_col) + 2)
ws[tco2e_col + '7'].fill = table_fill
ws[tco2e_col + '7'].font = name_font
ws[tco2e_col + '7'].alignment = c_c_alignment
ws[tco2e_col + '7'] = "TCO2E"
ws[tco2e_col + '7'].border = f_border
ws[tco2e_col + '8'].font = name_font
ws[tco2e_col + '8'].alignment = c_c_alignment
ws[tco2e_col + '8'] = round(reporting_period_data['total_in_kgco2e'],
0)
ws[tco2e_col + '8'].border = f_border
ws[tco2e_col + '9'].font = name_font
ws[tco2e_col + '9'].alignment = c_c_alignment
ws[tco2e_col + '9'] = str(round(reporting_period_data['increment_rate'] * 100, 2)) + "%" \
if reporting_period_data['increment_rate'] is not None else "-"
ws[tco2e_col + '9'].border = f_border
else:
for i in range(6, 9 + 1):
ws.rows_dimensions[i].height = 0.1
######################################
has_cost_detail_flag = True
reporting_period_data = report['reporting_period']
category = report['offline_meter']['energy_category_name']
ca_len = len(category)
times = reporting_period_data['timestamps']
if "values" not in reporting_period_data.keys() or len(
reporting_period_data['values']) == 0:
has_cost_detail_flag = False
if has_cost_detail_flag:
ws['B11'].font = title_font
ws['B11'] = name + '详细数据'
ws['B18'].fill = table_fill
ws['B18'].font = title_font
ws['B18'].border = f_border
ws['B18'].alignment = c_c_alignment
ws['B18'] = '日期时间'
time = times
has_data = False
max_row = 0
if len(time) > 0:
has_data = True
max_row = 18 + len(time)
if has_data:
end_data_row_number = 19
for i in range(0, len(time)):
col = 'B'
end_data_row_number = 19 + i
row = str(end_data_row_number)
ws[col + row].font = title_font
ws[col + row].alignment = c_c_alignment
ws[col + row] = time[i]
ws[col + row].border = f_border
ws['B' + str(end_data_row_number + 1)].font = title_font
ws['B' + str(end_data_row_number + 1)].alignment = c_c_alignment
ws['B' + str(end_data_row_number + 1)] = '总计'
ws['B' + str(end_data_row_number + 1)].border = f_border
bar = BarChart()
for i in range(0, ca_len):
col = chr(ord('C') + i)
ws[col + '18'].fill = table_fill
ws[col + '18'].font = title_font
ws[col + '18'].alignment = c_c_alignment
ws[col + '18'] = report['offline_meter']['energy_category_name'] + \
" (" + report['offline_meter']['unit_of_measure'] + ")"
ws[col + '18'].border = f_border
time = times
time_len = len(time)
for j in range(0, time_len):
row = str(19 + j)
ws[col + row].font = title_font
ws[col + row].alignment = c_c_alignment
ws[col + row] = round(reporting_period_data['values'][j],
0)
ws[col + row].border = f_border
ws[col + str(end_data_row_number + 1)].font = title_font
ws[col +
str(end_data_row_number + 1)].alignment = c_c_alignment
ws[col + str(end_data_row_number + 1)] = round(
reporting_period_data['total_in_category'], 0)
ws[col + str(end_data_row_number + 1)].border = f_border
bar_data = Reference(ws,
min_col=3 + i,
min_row=18,
max_row=max_row)
bar.series.append(Series(bar_data, title_from_data=True))
labels = Reference(ws, min_col=2, min_row=19, max_row=max_row)
bar.set_categories(labels)
bar.dLbls = DataLabelList()
bar.dLbls.showVal = True
bar.height = 5.25
bar.width = len(time)
ws.add_chart(bar, "B12")
else:
for i in range(11, 43 + 1):
ws.row_dimensions[i].height = 0.0
filename = str(uuid.uuid4()) + '.xlsx'
wb.save(filename)
return filename
