def setUp(self):
wb = Workbook()
ws = wb.get_active_sheet()
ws.title = 'reference'
for i in range(10):
ws.cell(row=i, column=0).value = i
self.sheet = ws
self.cell = Reference(self.sheet, (0, 0))
self.range = Reference(self.sheet, (0, 0), (9, 0))
def dates(wb):
ws = wb.create_sheet(3, "Dates")
for i in range(1, 10):
ws.append([date(2013, i, 1), i])
chart = BarChart()
values = Reference(ws, (0, 1), (8, 1))
labels = Reference(ws, (0, 0), (8, 0))
labels.number_format = 'd-mmm'
series = Series(values, labels=labels)
chart.append(series)
ws.add_chart(chart)
class TestReference(object):
def setup(self):
wb = Workbook()
ws = wb.get_active_sheet()
ws.title = 'reference'
for i in range(10):
ws.cell(row=i, column=0).value = i
self.sheet = ws
self.cell = Reference(self.sheet, (0, 0))
self.range = Reference(self.sheet, (0, 0), (9, 0))
def test_single_cell_ctor(self):
eq_(self.cell.pos1, (0, 0))
eq_(self.cell.pos2, None)
def test_range_ctor(self):
eq_(self.range.pos1, (0, 0))
eq_(self.range.pos2, (9, 0))
def test_type_validation(self):
pass
def test_caching_cell(self):
eq_(self.cell._get_cache(), [0])
def test_caching_range(self):
eq_(self.range._get_cache(), [0, 1, 2, 3, 4, 5, 6, 7, 8 , 9])
def test_ref_cell(self):
eq_(str(self.cell), "'reference'!$A$1")
def test_ref_range(self):
eq_(str(self.range), "'reference'!$A$1:$A$10")
def test_data_type(self):
assert_raises(ValueError, setattr, self.cell, 'data_type', 'f')
eq_(self.cell.data_type, 'n')
eq_(self.range.data_type, 'n')
def test_number_format(self):
assert_raises(ValueError, setattr, self.cell, 'number_format', 'YYYY')
self.cell.number_format = 'd-mmm'
def test_label_number_format(self):
ws = self.make_worksheet()
for i in range(10):
ws.append([i, i])
labels = Reference(ws, (0,0), (0,9))
labels.number_format = 'd-mmm'
values = Reference(ws, (0,0), (0,9))
serie = Serie(values=values, labels=labels)
c = BarChart()
c.add_serie(serie)
cw = BarChartWriter(c)
root = Element('test')
cw._write_serial(root, c._series[0].labels)
expected = """<?xml version='1.0' ?><test xmlns:c="http://schemas.openxmlformats.org/drawingml/2006/chart"><c:numRef><c:f>'data'!$A$1:$J$1</c:f><c:numCache><c:formatCode>d-mmm</c:formatCode><c:ptCount val="10" /><c:pt idx="0"><c:v>0</c:v></c:pt><c:pt idx="1"><c:v>1</c:v></c:pt><c:pt idx="2"><c:v>2</c:v></c:pt><c:pt idx="3"><c:v>3</c:v></c:pt><c:pt idx="4"><c:v>4</c:v></c:pt><c:pt idx="5"><c:v>5</c:v></c:pt><c:pt idx="6"><c:v>6</c:v></c:pt><c:pt idx="7"><c:v>7</c:v></c:pt><c:pt idx="8"><c:v>8</c:v></c:pt><c:pt idx="9"><c:v>9</c:v></c:pt></c:numCache></c:numRef></test>"""
xml = get_xml(root)
diff = compare_xml(xml, expected)
assert diff is None, diff
class TestReference(object):
def setUp(self):
wb = Workbook()
ws = wb.get_active_sheet()
ws.title = 'reference'
for i in range(10):
ws.cell(row=i, column=0).value = i
self.sheet = ws
self.cell = Reference(self.sheet, (0, 0))
self.range = Reference(self.sheet, (0, 0), (9, 0))
def test_single_cell_ctor(self):
eq_(self.cell.pos1, (0, 0))
eq_(self.cell.pos2, None)
def test_range_ctor(self):
eq_(self.range.pos1, (0, 0))
eq_(self.range.pos2, (9, 0))
def test_get_type(self):
eq_(self.cell.get_type(), 'num')
def test_caching_cell(self):
eq_(self.cell._get_cache(), [0])
def test_caching_range(self):
eq_(self.range._get_cache(), [0, 1, 2, 3, 4, 5, 6, 7, 8 , 9])
def test_ref_cell(self):
eq_(str(self.cell), "'reference'!$A$1")
eq_(self.cell._get_ref(), "'reference'!$A$1")
def test_ref_range(self):
eq_(str(self.range), "'reference'!$A$1:$A$10")
eq_(self.range._get_ref(), "'reference'!$A$1:$A$10")
# if __name__ == "__main__":
# sigs_dict_data = read_yaml_file("sigs.yaml")
# pkg_sig_dict = convert_sigs_dict_data_to_pkg_sig_dict(sigs_dict_data)
# dict_to_excel(pkg_sig_dict, "test.xlsx")
from openpyxl import Workbook
from openpyxl.chart import (
Reference,
Series,
BarChart3D,
)
wb = Workbook()
ws = wb.active
rows = [(None, 2013, 2014), ("Apples", 5, 4), ("Oranges", 6, 2),
("Pears", 8, 3)]
for row in rows:
ws.append(row)
data = Reference(ws, min_col=2, min_row=1, max_col=3, max_row=4)
titles = Reference(ws, min_col=1, min_row=2, max_row=4)
chart = BarChart3D()
chart.title = "3D Bar Chart"
chart.add_data(data=data, titles_from_data=True)
chart.set_categories(titles)
ws.add_chart(chart, "E5")
wb.save("bar3d.xlsx")
def generate_excel(report,
name,
reporting_start_datetime_local,
reporting_end_datetime_local,
period_type):
wb = Workbook()
ws = wb.active
ws.title = "EquipmentLoad"
# Row height
ws.row_dimensions[1].height = 102
for i in range(2, 2000 + 1):
ws.row_dimensions[i].height = 42
# Col width
ws.column_dimensions['A'].width = 1.5
ws.column_dimensions['B'].width = 25.0
for i in range(ord('C'), ord('L')):
ws.column_dimensions[chr(i)].width = 15.0
# Font
name_font = Font(name='Arial', size=15, bold=True)
title_font = Font(name='Arial', size=15, bold=True)
table_fill = PatternFill(fill_type='solid', fgColor='1F497D')
f_border = Border(left=Side(border_style='medium', color='00000000'),
right=Side(border_style='medium', color='00000000'),
bottom=Side(border_style='medium', color='00000000'),
top=Side(border_style='medium', color='00000000')
)
b_border = Border(bottom=Side(border_style='medium', color='00000000'), )
b_c_alignment = Alignment(vertical='bottom',
horizontal='center',
text_rotation=0,
wrap_text=True,
shrink_to_fit=False,
indent=0)
c_c_alignment = Alignment(vertical='center',
horizontal='center',
text_rotation=0,
wrap_text=True,
shrink_to_fit=False,
indent=0)
b_r_alignment = Alignment(vertical='bottom',
horizontal='right',
text_rotation=0,
wrap_text=True,
shrink_to_fit=False,
indent=0)
# Img
img = Image("excelexporters/myems.png")
ws.add_image(img, 'A1')
# Title
ws['B3'].alignment = b_r_alignment
ws['B3'] = 'Name:'
ws['C3'].border = b_border
ws['C3'].alignment = b_c_alignment
ws['C3'] = name
ws['D3'].alignment = b_r_alignment
ws['D3'] = 'Period:'
ws['E3'].border = b_border
ws['E3'].alignment = b_c_alignment
ws['E3'] = period_type
ws['B4'].alignment = b_r_alignment
ws['B4'] = 'Reporting Start Datetime:'
ws['C4'].border = b_border
ws['C4'].alignment = b_c_alignment
ws['C4'] = reporting_start_datetime_local
ws['D4'].alignment = b_r_alignment
ws['D4'] = 'Reporting End Datetime:'
ws['E4'].border = b_border
ws['E4'].alignment = b_c_alignment
ws['E4'] = reporting_end_datetime_local
if "reporting_period" not in report.keys() or \
"names" not in report['reporting_period'].keys() or len(report['reporting_period']['names']) == 0:
filename = str(uuid.uuid4()) + '.xlsx'
wb.save(filename)
return filename
####################################################################################################################
# First: Statistics
# 6: title
# 7: table title
# 8~2*ca_len table_data
####################################################################################################################
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:
filename = str(uuid.uuid4()) + '.xlsx'
wb.save(filename)
return filename
ws['B6'].font = title_font
ws['B6'] = name + ' ' + 'Statistics'
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'] = 'Reporting Period'
ws['B7'].border = f_border
ws['C7'].font = title_font
ws['C7'].alignment = c_c_alignment
ws['C7'] = 'Average Load'
ws['C7'].border = f_border
ws['D7'].font = title_font
ws['D7'].alignment = c_c_alignment
ws['D7'] = 'Maximum Load'
ws['D7'].border = f_border
ws['E7'].font = title_font
ws['E7'].alignment = c_c_alignment
ws['E7'] = 'Load Factor'
ws['E7'].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] + "/H )"
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)] = 'Increment Rate'
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['averages'][i], 2) \
if reporting_period_data['averages'][i] is not None else ''
ws['C' + str(row)].border = f_border
ws['C' + str(row)].number_format = '0.00'
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['averages_increment_rate'][i] * 100, 2)) + "%" \
if reporting_period_data['averages_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['maximums'][i], 2) \
if reporting_period_data['maximums'][i] is not None else ''
ws['D' + str(row)].border = f_border
ws['D' + str(row)].number_format = '0.00'
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['maximums_increment_rate'][i] * 100, 2)) + "%" \
if reporting_period_data['maximums_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['factors'][i], 2) \
if reporting_period_data['factors'][i] is not None else ''
ws['E' + str(row)].border = f_border
ws['E' + str(row)].number_format = '0.00'
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['factors_increment_rate'][i] * 100, 2)) + "%" \
if reporting_period_data['factors_increment_rate'][i] is not None else '0.00%'
ws['E' + str(row + 1)].border = f_border
####################################################################################################################
# Third: Detailed Data
# row_sat~ row_sat + 6*cal_len: line
# row_da: table title
# row_da + 1~: table_data
####################################################################################################################
has_timestamps_flag = True
if "timestamps" not in reporting_period_data.keys() or \
reporting_period_data['timestamps'] is None or \
len(reporting_period_data['timestamps']) == 0:
has_timestamps_flag = False
if has_timestamps_flag:
timestamps = reporting_period_data['timestamps'][0]
names = reporting_period_data['names']
ca_len = len(names)
real_timestamps_len = timestamps_data_not_equal_0(report['parameters']['timestamps'])
time_len = len(timestamps)
# row_lines == the number of rows of lines
row_lines = 6 * ca_len
# row_sat == the number of rows of statistical analysis table
row_sat = 9 + 2 * ca_len
# row_da == the number of rows of Detailed data
row_da = row_sat + row_lines + real_timestamps_len * 7 + 2
ws['B' + str(row_da)].font = title_font
ws['B' + str(row_da)] = name + ' ' + 'Detailed Data'
# table_title
ws['B' + str(row_da + 1)].fill = table_fill
ws['B' + str(row_da + 1)].font = name_font
ws['B' + str(row_da + 1)].alignment = c_c_alignment
ws['B' + str(row_da + 1)] = "Datetime"
ws['B' + str(row_da + 1)].border = f_border
for i in range(0, ca_len):
col_average = chr(ord('C') + 2 * i)
col_maximum = chr(ord('D') + 2 * i)
ws[col_average + str(row_da + 1)].font = name_font
ws[col_average + str(row_da + 1)].alignment = c_c_alignment
ws[col_average + str(row_da + 1)] = names[i] + " Average Load(" + reporting_period_data['units'][
i] + "/H)"
ws[col_average + str(row_da + 1)].border = f_border
ws[col_maximum + str(row_da + 1)].font = name_font
ws[col_maximum + str(row_da + 1)].alignment = c_c_alignment
ws[col_maximum + str(row_da + 1)] = names[i] + " Maximum Load(" + reporting_period_data['units'][
i] + "/H)"
ws[col_maximum + str(row_da + 1)].border = f_border
# table_date
for i in range(0, time_len):
rows = i + row_da + 2
ws['B' + str(rows)].font = name_font
ws['B' + str(rows)].alignment = c_c_alignment
ws['B' + str(rows)] = timestamps[i]
ws['B' + str(rows)].border = f_border
for index in range(0, ca_len):
col_average = chr(ord('C') + 2 * index)
col_maximum = chr(ord('D') + 2 * index)
ws[col_average + str(rows)].font = name_font
ws[col_average + str(rows)].alignment = c_c_alignment
ws[col_average + str(rows)] = reporting_period_data['sub_averages'][index][i] \
if reporting_period_data['sub_maximums'][index] is not None else ''
ws[col_average + str(rows)].number_format = '0.00'
ws[col_average + str(rows)].border = f_border
ws[col_maximum + str(rows)].font = name_font
ws[col_maximum + str(rows)].alignment = c_c_alignment
ws[col_maximum + str(rows)] = reporting_period_data['sub_maximums'][index][i] \
if reporting_period_data['sub_maximums'][index] is not None else ''
ws[col_maximum + str(rows)].number_format = '0.00'
ws[col_maximum + str(rows)].border = f_border
current_row_number = row_da + 1 + time_len
# LineChart
for i in range(0, ca_len):
lc = LineChart()
lc.title = "Reporting Period Maximum Load"
lc.style = 10
lc.x_axis.majorTickMark = 'in'
lc.y_axis.majorTickMark = 'in'
lc.smooth = True
lc.x_axis.crosses = 'min'
lc.height = 8.25
lc.width = 24
lc.dLbls = DataLabelList()
lc.dLbls.dLblPos = 't'
lc.dLbls.showVal = True
times = Reference(ws, min_col=2, min_row=row_da + 2,
max_row=row_da + 2 + time_len)
lc_data = Reference(ws, min_col=2 + 2 * (i+1), min_row=row_da + 1,
max_row=row_da + 1 + time_len)
lc.add_data(lc_data, titles_from_data=True)
lc.set_categories(times)
ser = lc.series[0]
ser.marker.symbol = "diamond"
ser.marker.size = 5
chart_col = 'B'
chart_cell = str(row_sat + 6 * i)
ws.add_chart(lc, chart_col + chart_cell)
####################################################################################################################
current_sheet_parameters_row_number = row_sat + 1 + 6 * ca_len
has_parameters_names_and_timestamps_and_values_data = True
if 'parameters' not in report.keys() or \
report['parameters'] is None or \
'names' not in report['parameters'].keys() or \
report['parameters']['names'] is None or \
len(report['parameters']['names']) == 0 or \
'timestamps' not in report['parameters'].keys() or \
report['parameters']['timestamps'] is None or \
len(report['parameters']['timestamps']) == 0 or \
'values' not in report['parameters'].keys() or \
report['parameters']['values'] is None or \
len(report['parameters']['values']) == 0 or \
timestamps_data_all_equal_0(report['parameters']['timestamps']):
has_parameters_names_and_timestamps_and_values_data = False
if has_parameters_names_and_timestamps_and_values_data:
################################################################################################################
# new worksheet
################################################################################################################
parameters_data = report['parameters']
parameters_names_len = len(parameters_data['names'])
file_name = (re.sub(r'[^A-Z]', '', ws.title))+'_'
parameters_ws = wb.create_sheet(file_name + 'Parameters')
parameters_timestamps_data_max_len = \
get_parameters_timestamps_lists_max_len(list(parameters_data['timestamps']))
# Row height
parameters_ws.row_dimensions[1].height = 102
for i in range(2, 7 + 1):
parameters_ws.row_dimensions[i].height = 42
for i in range(8, parameters_timestamps_data_max_len + 10):
parameters_ws.row_dimensions[i].height = 60
# Col width
parameters_ws.column_dimensions['A'].width = 1.5
parameters_ws.column_dimensions['B'].width = 25.0
for i in range(3, 12 + parameters_names_len * 3):
parameters_ws.column_dimensions[format_cell.get_column_letter(i)].width = 15.0
# Img
img = Image("excelexporters/myems.png")
parameters_ws.add_image(img, 'A1')
# Title
parameters_ws['B3'].alignment = b_r_alignment
parameters_ws['B3'] = 'Name:'
parameters_ws['C3'].border = b_border
parameters_ws['C3'].alignment = b_c_alignment
parameters_ws['C3'] = name
parameters_ws['D3'].alignment = b_r_alignment
parameters_ws['D3'] = 'Period:'
parameters_ws['E3'].border = b_border
parameters_ws['E3'].alignment = b_c_alignment
parameters_ws['E3'] = period_type
parameters_ws['B4'].alignment = b_r_alignment
parameters_ws['B4'] = 'Reporting Start Datetime:'
parameters_ws['C4'].border = b_border
parameters_ws['C4'].alignment = b_c_alignment
parameters_ws['C4'] = reporting_start_datetime_local
parameters_ws['D4'].alignment = b_r_alignment
parameters_ws['D4'] = 'Reporting End Datetime:'
parameters_ws['E4'].border = b_border
parameters_ws['E4'].alignment = b_c_alignment
parameters_ws['E4'] = reporting_end_datetime_local
parameters_ws_current_row_number = 6
parameters_ws['B' + str(parameters_ws_current_row_number)].font = title_font
parameters_ws['B' + str(parameters_ws_current_row_number)] = name + ' ' + 'Parameters'
parameters_ws_current_row_number += 1
parameters_table_start_row_number = parameters_ws_current_row_number
parameters_ws.row_dimensions[parameters_ws_current_row_number].height = 80
parameters_ws_current_row_number += 1
table_current_col_number = 2
for i in range(0, parameters_names_len):
if len(parameters_data['timestamps'][i]) == 0:
continue
col = format_cell.get_column_letter(table_current_col_number)
parameters_ws[col + str(parameters_ws_current_row_number - 1)].fill = table_fill
parameters_ws[col + str(parameters_ws_current_row_number - 1)].border = f_border
col = format_cell.get_column_letter(table_current_col_number + 1)
parameters_ws[col + str(parameters_ws_current_row_number - 1)].fill = table_fill
parameters_ws[col + str(parameters_ws_current_row_number - 1)].border = f_border
parameters_ws[col + str(parameters_ws_current_row_number - 1)].font = name_font
parameters_ws[col + str(parameters_ws_current_row_number - 1)].alignment = c_c_alignment
parameters_ws[col + str(parameters_ws_current_row_number - 1)] = parameters_data['names'][i]
table_current_row_number = parameters_ws_current_row_number
for j, value in enumerate(list(parameters_data['timestamps'][i])):
col = format_cell.get_column_letter(table_current_col_number)
parameters_ws[col + str(table_current_row_number)].border = f_border
parameters_ws[col + str(table_current_row_number)].font = title_font
parameters_ws[col + str(table_current_row_number)].alignment = c_c_alignment
parameters_ws[col + str(table_current_row_number)] = value
col = format_cell.get_column_letter(table_current_col_number + 1)
parameters_ws[col + str(table_current_row_number)].border = f_border
parameters_ws[col + str(table_current_row_number)].font = title_font
parameters_ws[col + str(table_current_row_number)].alignment = c_c_alignment
parameters_ws[col + str(table_current_row_number)] = round(parameters_data['values'][i][j], 2)
table_current_row_number += 1
table_current_col_number = table_current_col_number + 3
################################################################################################################
# parameters chart and parameters table
################################################################################################################
ws['B' + str(current_sheet_parameters_row_number)].font = title_font
ws['B' + str(current_sheet_parameters_row_number)] = name + ' ' + 'Parameters'
current_sheet_parameters_row_number += 1
chart_start_row_number = current_sheet_parameters_row_number
col_index = 0
for i in range(0, parameters_names_len):
if len(parameters_data['timestamps'][i]) == 0:
continue
line = LineChart()
data_col = 3 + col_index * 3
labels_col = 2 + col_index * 3
col_index += 1
line.title = 'Parameters - ' + \
parameters_ws.cell(row=parameters_table_start_row_number, column=data_col).value
labels = Reference(parameters_ws, min_col=labels_col, min_row=parameters_table_start_row_number + 1,
max_row=(len(parameters_data['timestamps'][i]) + parameters_table_start_row_number))
line_data = Reference(parameters_ws, min_col=data_col, min_row=parameters_table_start_row_number,
max_row=(len(parameters_data['timestamps'][i]) + parameters_table_start_row_number))
line.add_data(line_data, titles_from_data=True)
line.set_categories(labels)
line_data = line.series[0]
line_data.marker.symbol = "circle"
line_data.smooth = True
line.x_axis.crosses = 'min'
line.height = 8.25
line.width = 24
line.dLbls = DataLabelList()
line.dLbls.dLblPos = 't'
line.dLbls.showVal = False
line.dLbls.showPercent = False
chart_col = 'B'
chart_cell = chart_col + str(chart_start_row_number)
chart_start_row_number += 6
ws.add_chart(line, chart_cell)
current_sheet_parameters_row_number = chart_start_row_number
current_sheet_parameters_row_number += 1
####################################################################################################################
filename = str(uuid.uuid4()) + '.xlsx'
wb.save(filename)
return filename
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 = 102
for i in range(2, 2000 + 1):
ws.row_dimensions[i].height = 42
# 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
ws.column_dimensions['B'].width = 25.0
for i in range(ord('C'), ord('L')):
ws.column_dimensions[chr(i)].width = 15.0
# Font
name_font = Font(name='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")
img.width = img.width * 0.85
img.height = img.height * 0.85
# img = Image("myems.png")
ws.add_image(img, 'B1')
# Title
ws.row_dimensions[3].height = 60
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 \
"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['meter']['energy_category_name']
ca_len = len(category)
ws.row_dimensions[7].height = 60
ws['B7'].fill = table_fill
ws['B7'].border = f_border
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['meter']['energy_category_name'] + \
" (" + report['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'], 2)
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'] / 1000, 2)
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'] / 1000, 2)
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_datail_flag = True
reporting_period_data = report['reporting_period']
category = report['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_datail_flag = False
if has_cost_datail_flag:
ws['B11'].font = title_font
ws['B11'] = name + '详细数据'
ws.row_dimensions[18].height = 60
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
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['meter']['energy_category_name'] + \
" (" + report['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], 2)
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'], 2)
ws[col + str(end_data_row_number + 1)].border = f_border
line = LineChart()
line.title = '报告期成本 - ' + report['meter']['energy_category_name'] + \
" (" + report['meter']['unit_of_measure'] + ")"
line_data = Reference(ws, min_col=3, min_row=18, max_row=max_row)
line.series.append(Series(line_data, title_from_data=True))
labels = Reference(ws, min_col=2, min_row=19, max_row=max_row)
line.set_categories(labels)
line_data = line.series[0]
line_data.marker.symbol = "circle"
line_data.smooth = True
line.x_axis.crosses = 'min'
line.dLbls = DataLabelList()
line.dLbls.dLblPos = 't'
line.dLbls.showVal = True
line.height = 8.25
line.width = 24
ws.add_chart(line, "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
import openpyxl
from openpyxl.chart import PieChart, Reference, Series, PieChart3D
wb = openpyxl.Workbook()
ws = wb.active
data = [
['Flavor', 'Sold'],
['Vanilla', 1500],
['Chocolate', 1700],
['Pumpkin', 950],
['Examp', 950],
]
for rows in data:
ws.append(rows)
chart = PieChart()
labels = Reference(ws, min_col=1, min_row=2, max_row=5)
data = Reference(ws, min_col=2, min_row=1, max_row=5)
chart.add_data(data, titles_from_data=True)
chart.set_categories(labels)
chart.title = 'Ice cream by Flavor'
ws.add_chart(chart, 'C1')
wb.save('Pie.xlsx')
def excelReportIP(self, fname='ip_report.xlsx'):
"""create an excel with all packets and top IPs with graphs"""
def set_headers(hrs, sheet, r=1):
header_font = Font(color=colors.BLUE, bold=True)
for h in hrs:
cell = sheet.cell(row=r, column=hrs.index(h) + 1, value=h)
cell.font = header_font
return sheet
if self.packets:
wb = Workbook()
ws = wb.active
ws.title = 'packets'
# headers
headers = ['DateTime', 'Protocol', 'Source IP', 'Source Port', 'Destination IP', 'Destination Port']
ws = set_headers(headers, ws)
# values
row = 2
for pkt in self.packets:
if IP in pkt:
ws.cell(row=row, column=headers.index('DateTime') + 1, value=datetime.fromtimestamp(pkt.time))
ws.cell(row=row, column=headers.index('Protocol') + 1, value=pkt[IP].proto)
ws.cell(row=row, column=headers.index('Source IP') + 1, value=pkt[IP].src)
ws.cell(row=row, column=headers.index('Destination IP') + 1, value=pkt[IP].dst)
if TCP in pkt[IP]:
layer = TCP
elif UDP in pkt[IP]:
layer = UDP
else:
layer = None
if layer:
ws.cell(row=row, column=headers.index('Source Port') + 1, value=pkt[IP][layer].sport)
ws.cell(row=row, column=headers.index('Destination Port') + 1, value=pkt[IP][layer].dport)
row += 1
# charts
wb.create_sheet('Charts')
ws = wb['Charts']
# top 10 src ip
headers = ['Count', 'IP']
ws.cell(row=1, column=1, value='Top 10 Source IP')
ws = set_headers(headers, ws, r=2)
row = 3
for count, ip in self.topSrcIP():
ws.cell(row=row, column=1, value=count)
ws.cell(row=row, column=2, value=ip)
row += 1
# pie chart
pie_chart = PieChart()
labels = Reference(ws, min_col=2, min_row=3, max_row=row - 1)
chart_data = Reference(ws, min_col=1, min_row=3, max_row=row - 1)
pie_chart.add_data(chart_data, titles_from_data=True)
pie_chart.set_categories(labels)
pie_chart.title = 'Top 10 Source IP'
# Cut the first slice out of the pie
pie_slice = DataPoint(idx=0, explosion=20)
pie_chart.series[0].data_points = [pie_slice]
ws.add_chart(pie_chart, "F2")
# top 10 dst ip
headers = ['Count', 'IP']
ws.cell(row=19, column=1, value='Top 10 Destination IP')
ws = set_headers(headers, ws, r=20)
row = 21
for count, ip in self.topSrcIP():
ws.cell(row=row, column=1, value=count)
ws.cell(row=row, column=2, value=ip)
row += 1
# pie chart
bar_chart = BarChart()
bar_chart.type = "col"
bar_chart.style = 10
bar_chart.title = 'Top 10 Destination IP'
bar_chart.y_axis.title = 'count'
chart_data = Reference(ws, min_col=1, min_row=21, max_row=row - 1)
cats = Reference(ws, min_col=2, min_row=21, max_row=row - 1)
bar_chart.add_data(chart_data, titles_from_data=True)
bar_chart.set_categories(cats)
bar_chart.shape = 4
ws.add_chart(bar_chart, "F20")
wb.save(fname)
else:
print('no packets, no report')
break
while isAdd == 'Yes' or isAdd == 'yes':
product_id = product_id + 1
price = float(input('What is the price of the item?'))
new_trans_id = sheet.cell(row_counter, 1)
new_prod_id = sheet.cell(row_counter, 2)
new_price = sheet.cell(row_counter, 3)
new_trans_id.value = transaction_date
new_prod_id.value = product_id
new_price.value = price
sheet.cell(row_counter, 4).value = price * 1.13
add_or_stop = input(
'Will you keep adding or will you stop? (Yes or No)')
isAdd = add_or_stop
keep_Adding = add_or_stop
row_counter = row_counter + 1
# Initialize the parameters for the graph
values = Reference(sheet,
min_row=2,
max_row=sheet.max_row,
min_col=4,
max_col=4)
# Add the parameters and then create the bar graph representing the data
chart = BarChart()
chart.add_data(values)
sheet.add_chart(chart, 'e2')
# Save the info onto the excel sheet
wb.save('transactions.xlsx')
cs = wb.create_chartsheet(title)
chart = LineChart()
chart.x_axis = DateAxis(crossAx=100)
chart.y_axis.crossAx = 500
chart.x_axis.number_format = 'd-mmm-yy'
chart.x_axis.majorTimeUnit = "months"
chart.title = "Relative Performance {0}\n Source: httparchive.org - California USA".format(
title)
chart.y_axis.scaling.min = 0
chart.y_axis.scaling.max = 20000
chart.y_axis.dispUnits = DisplayUnitsLabelList(builtInUnit="thousands")
chart.y_axis.title = "Time in seconds"
data = Reference(ws,
min_col=1,
min_row=2,
max_col=len(row),
max_row=ws.max_row)
chart.add_data(
data,
from_rows=True,
titles_from_data=True,
)
dates = Reference(ws, min_col=2, min_row=1, max_col=len(row))
chart.set_categories(dates)
bayer = chart.series[bay_idx]
bayer.graphicalProperties.line.prstDash = "sysDot"
bayer.graphicalProperties.line.width = 50050
##bayer.graphicalProperties.ln.solidFill = "FF0000"
cs.add_chart(chart)
def place_in_excel(name, latest_fin_data, last_fin_data, baseline_fin_data):
'''
function places all data into excel spreadsheet and creates chart.
data is placed into sheet in reverse order (see how data_list is ordered) so that most recent
data is displayed on right hand side of the data table
'''
wb = Workbook()
ws = wb.active
data_list = [baseline_fin_data, last_fin_data, latest_fin_data]
count = 0
'''places in raw/reported data'''
for data in data_list:
for i, key in enumerate(capture_rdel):
try:
ws.cell(row=i + 3, column=2 + count, value=data[name][key])
except KeyError:
ws.cell(row=i + 3, column=2 + count, value=0)
for i, key in enumerate(capture_cdel):
try:
ws.cell(row=i + 3, column=3 + count, value=data[name][key])
except KeyError:
ws.cell(row=i + 3, column=3 + count, value=0)
for i, key in enumerate(capture_ng):
try:
ws.cell(row=i + 3, column=4 + count, value=data[name][key])
except KeyError:
ws.cell(row=i + 3, column=4 + count, value=0)
count += 4
'''places in totals'''
baseline_totals = calculate_totals(name, baseline_fin_data)
last_q_totals = calculate_totals(name, last_fin_data)
latest_q_totals = calculate_totals(name, latest_fin_data)
total_list = [baseline_totals, last_q_totals, latest_q_totals]
c = 0
for l in total_list:
for i, total in enumerate(l):
ws.cell(row=i + 3, column=5 + c, value=total)
c += 4
'''labeling data in table'''
labeling_list_quarter = ['Baseline', 'Last Quarter', 'Latest quarter']
ws.cell(row=1, column=2, value=labeling_list_quarter[0])
ws.cell(row=1, column=6, value=labeling_list_quarter[1])
ws.cell(row=1, column=10, value=labeling_list_quarter[2])
labeling_list_type = ['RDEL', 'CDEL', 'Non-Gov', 'Total']
repeat = 3
c = 0
while repeat > 0:
for i, label in enumerate(labeling_list_type):
ws.cell(row=2, column=2 + i + c, value=label)
c += 4
repeat -= 1
labeling_list_year = [
'Spend', '19/20', '20/21', '21/22', '22/23', '23/24', '24/25', '25/26',
'26/27', '27/28', '28/29', 'Unprofiled'
]
for i, label in enumerate(labeling_list_year):
ws.cell(row=2 + i, column=1, value=label)
'''process for showing total cost profile. starting with data'''
row_start = 16
for x, l in enumerate(total_list):
for i, total in enumerate(l):
ws.cell(row=i + row_start, column=x + 2, value=total)
'''data for graph labeling'''
for i, quarter in enumerate(labeling_list_quarter):
ws.cell(row=15, column=i + 2, value=quarter)
for i, label in enumerate(labeling_list_year):
ws.cell(row=15 + i, column=1, value=label)
chart = LineChart()
chart.title = str(name) + ' Cost Profile'
chart.style = 4
chart.x_axis.title = 'Financial Year'
chart.y_axis.title = 'Cost £m'
'''styling chart'''
# axis titles
font = Font(typeface='Calibri')
size = 1200 # 12 point size
cp = CharacterProperties(latin=font, sz=size, b=True) # Bold
pp = ParagraphProperties(defRPr=cp)
rtp = RichText(p=[Paragraph(pPr=pp, endParaRPr=cp)])
chart.x_axis.title.tx.rich.p[0].pPr = pp
chart.y_axis.title.tx.rich.p[0].pPr = pp
# chart.title.tx.rich.p[0].pPr = pp
# title
size_2 = 1400
cp_2 = CharacterProperties(latin=font, sz=size_2, b=True)
pp_2 = ParagraphProperties(defRPr=cp_2)
rtp_2 = RichText(p=[Paragraph(pPr=pp_2, endParaRPr=cp_2)])
chart.title.tx.rich.p[0].pPr = pp_2
'''unprofiled costs not included in the chart'''
data = Reference(ws, min_col=2, min_row=15, max_col=4, max_row=25)
chart.add_data(data, titles_from_data=True)
cats = Reference(ws, min_col=1, min_row=16, max_row=25)
chart.set_categories(cats)
s3 = chart.series[0]
s3.graphicalProperties.line.solidFill = "cfcfea" # light blue
s8 = chart.series[1]
s8.graphicalProperties.line.solidFill = "5097a4" # medium blue
s9 = chart.series[2]
s9.graphicalProperties.line.solidFill = "0e2f44" # dark blue'''
ws.add_chart(chart, "H15")
'''process for creating income chart'''
baseline_total_income = calculate_income_totals(name, baseline_fin_data)
last_q_total_income = calculate_income_totals(name, last_fin_data)
latest_q_total_income = calculate_income_totals(name, latest_fin_data)
total_income_list = [
baseline_total_income, last_q_total_income, latest_q_total_income
]
if sum(latest_q_total_income) is not 0:
for x, l in enumerate(total_income_list):
for i, total in enumerate(l):
ws.cell(row=i + 32, column=x + 2, value=total)
'''data for graph labeling'''
for i, quarter in enumerate(labeling_list_quarter):
ws.cell(row=32, column=i + 2, value=quarter)
for i, label in enumerate(labeling_list_year):
ws.cell(row=32 + i, column=1, value=label)
'''income graph'''
chart = LineChart()
chart.title = str(name) + ' Income Profile'
chart.style = 4
chart.x_axis.title = 'Financial Year'
chart.y_axis.title = 'Cost £m'
font = Font(typeface='Calibri')
size = 1200 # 12 point size
cp = CharacterProperties(latin=font, sz=size, b=True) # Bold
pp = ParagraphProperties(defRPr=cp)
rtp = RichText(p=[Paragraph(pPr=pp, endParaRPr=cp)])
chart.x_axis.title.tx.rich.p[0].pPr = pp
chart.y_axis.title.tx.rich.p[0].pPr = pp
# chart.title.tx.rich.p[0].pPr = pp
# title
size_2 = 1400
cp_2 = CharacterProperties(latin=font, sz=size_2, b=True)
pp_2 = ParagraphProperties(defRPr=cp_2)
rtp_2 = RichText(p=[Paragraph(pPr=pp_2, endParaRPr=cp_2)])
chart.title.tx.rich.p[0].pPr = pp_2
#unprofiled costs not included in the chart
data = Reference(ws, min_col=2, min_row=32, max_col=4, max_row=42)
chart.add_data(data, titles_from_data=True)
cats = Reference(ws, min_col=1, min_row=33, max_row=42)
chart.set_categories(cats)
'''
keeping as colour coding is useful
s1 = chart.series[0]
s1.graphicalProperties.line.solidFill = "cfcfea" #light blue
s2 = chart.series[1]
s2.graphicalProperties.line.solidFill = "e2f1bb" #light green
s3 = chart.series[2]
s3.graphicalProperties.line.solidFill = "eaba9d" #light red
s4 = chart.series[3]
s4.graphicalProperties.line.solidFil = "5097a4" #medium blue
s5 = chart.series[4]
s5.graphicalProperties.line.solidFill = "a0db8e" #medium green
s6 = chart.series[5]
s6.graphicalProperties.line.solidFill = "b77575" #medium red
s7 = chart.series[6]
s7.graphicalProperties.line.solidFil = "0e2f44" #dark blue
s8 = chart.series[7]
s8.graphicalProperties.line.solidFill = "29ab87" #dark green
s9 = chart.series[8]
s9.graphicalProperties.line.solidFill = "691c1c" #dark red
'''
s3 = chart.series[0]
s3.graphicalProperties.line.solidFill = "e2f1bb" # light green
s8 = chart.series[1]
s8.graphicalProperties.line.solidFill = "a0db8e" # medium green
s9 = chart.series[2]
s9.graphicalProperties.line.solidFill = "29ab87" # dark green
ws.add_chart(chart, "H31")
else:
pass
return wb
workbook.save("pyfrag.xlsx")
filename = os.path.join(currentPath, "pyfrag.xlsx")
wb = load_workbook(filename)
ws = wb.active
chart = ScatterChart()
chart.style = 13
chart.x_axis.title = "Bondlength"
chart.y_axis.title = "Energy"
xvalues = Reference(
ws, min_col=2, min_row=2, max_row=999
) # starting col including x xalue, starting data row and finishing data row
for i in [
3,
6,
7,
]: # cols including y values, noted final number is n+1 which is python range feature
values = Reference(
ws, min_col=i, min_row=1, max_row=999
) # rows of everything including first title row
series = Series(values, xvalues, title_from_data=True)
chart.series.append(series)
ws.add_chart(chart, "D10")
wb = Workbook()
ws = wb.active
rows = [
['TraineeId', 'Test1', 'Test2'],
[2324, 40, 30],
[345, 90, 25],
[4435, 80, 30],
[5435, 78, 65],
[6345, 90, 55],
[7435, 50, 10],
]
for row in rows:
ws.append(row)
chart = BarChart3D()
chart.title = "Score Graph"
chart.style = 13
chart.x_axis.title = 'Trainee Id'
chart.y_axis.title = 'Marks(%)'
Reference()
cats = Reference(ws, min_col=1, min_row=1, max_row=7)
data = Reference(ws, min_col=2, min_row=1, max_col=3, max_row=7)
chart.add_data(data, titles_from_data=True)
chart.set_categories(cats)
ws.add_chart(chart, "F12")
path = os.getcwd()
wb.save(path + "/BarChartData.xlsx")
def plot(ws_Excel_Unite_1_exp,
ws_Excel_Unite_2_exp,
ws_Excel_Unite_exp_path,
dir_file_Excel_Unite,
NameGenInvestigated):
global n, value_to_time, name_obj_gen, h
ws_Excel_Unite_exp_path.cell(
column=1,
row=1,
value=f"Загруженный файл {dir_file_Excel_Unite}"
)
num_time_to_row = 0
col_count = ws_Excel_Unite_1_exp.max_column - 1
col_gen = 0
for n in range(1, 50):
value_to_time = ws_Excel_Unite_1_exp[f"A{n}"].value
if value_to_time == ' TIME':
num_time_to_row = int(n) + 1
for h in range(2, col_count + 1):
name_obj_gen = ws_Excel_Unite_1_exp[f"{get_column_letter(h)}{str(num_time_to_row - 1)}"].value
if name_obj_gen == f"P/{NameGenInvestigated}":
col_gen = h
break
for g in range(2, col_count + 1):
nameObjExp1 = ws_Excel_Unite_1_exp[f"{get_column_letter(g)}{str(num_time_to_row - 1)}"].value
nameObjExp2 = ws_Excel_Unite_2_exp[f"{get_column_letter(g)}{str(num_time_to_row - 1)}"].value
nameObjExp1_dict = dict_set[str(nameObjExp1)]
nameObjExp2_dict = dict_set[str(nameObjExp2)]
ws_Excel_Unite_1_exp.cell(column=g,
row=num_time_to_row - 1,
value=f"с PSS {nameObjExp1_dict}")
ws_Excel_Unite_2_exp.cell(column=g,
row=num_time_to_row - 1,
value=f"без PSS {nameObjExp2_dict}")
max_Chart = max_var_object(ws_Excel_Unite_1_exp, col_gen, num_time_to_row)
min_Chart = min_var_object(ws_Excel_Unite_1_exp, col_gen, num_time_to_row)
maxCh60 = max_Ch60(ws_Excel_Unite_1_exp, col_gen, num_time_to_row)
minCh60 = min_Ch60(ws_Excel_Unite_1_exp, col_gen, num_time_to_row)
# График 1 активной мощности СГ на интервале от 0 до 15
ch4 = ScatterChart()
xvalues_exp1 = Reference(ws_Excel_Unite_1_exp,
min_col=1,
min_row=num_time_to_row,
max_row=ws_Excel_Unite_1_exp.max_row)
values_exp1 = Reference(ws_Excel_Unite_1_exp,
min_col=int(col_gen),
min_row=num_time_to_row - 1,
max_row=ws_Excel_Unite_1_exp.max_row)
series_exp1 = Series(values_exp1, xvalues_exp1, title_from_data=True)
name_object = ws_Excel_Unite_1_exp[f'{get_column_letter(col_gen)}{str(num_time_to_row - 1)}'].value
remove_list = ['с', 'PSS']
edit_str_as_list = name_object.split()
final_list = [word for word in edit_str_as_list if word not in remove_list]
final_str = ' '.join(final_list)
final_list.clear()
ch4.title = f"{final_str}"
ch4.x_axis.title = "Время, с" # название оси Х
ch4.y_axis.title = "Активная мощность, МВт" # название оси У
# ch4.style = 12 # стиль диаграммы
ch4.series.append(series_exp1)
ch4.x_axis.scaling.min = 49
ch4.y_axis.scaling.min = 0
ch4.y_axis.scaling.max = max_Chart + 10
ch4.x_axis.scaling.max = 65
ws_Excel_Unite_exp_path.add_chart(ch4, f'{get_column_letter(1)}{str(5)}')
# График 2 активной мощности СГ на интервале от 0 до 15
ch2 = ScatterChart()
xvalues_exp1 = Reference(ws_Excel_Unite_1_exp,
min_col=1,
min_row=num_time_to_row,
max_row=ws_Excel_Unite_1_exp.max_row)
values_exp1 = Reference(ws_Excel_Unite_1_exp,
min_col=int(col_gen),
min_row=num_time_to_row - 1,
max_row=ws_Excel_Unite_1_exp.max_row)
xvalues_exp2 = Reference(ws_Excel_Unite_2_exp,
min_col=1,
min_row=num_time_to_row,
max_row=ws_Excel_Unite_2_exp.max_row)
values_exp2 = Reference(ws_Excel_Unite_2_exp,
min_col=int(col_gen),
min_row=num_time_to_row - 1,
max_row=ws_Excel_Unite_2_exp.max_row)
series_exp1 = Series(values_exp1, xvalues_exp1, title_from_data=True)
series_exp2 = Series(values_exp2, xvalues_exp2, title_from_data=True)
name_object = ws_Excel_Unite_1_exp[f'{get_column_letter(col_gen)}{str(num_time_to_row - 1)}'].value
remove_list = ['с', 'PSS']
edit_str_as_list = name_object.split()
final_list1 = [word for word in edit_str_as_list if word not in remove_list]
final_str = ' '.join(final_list1)
final_list1.clear()
# ch2.style = 12 # стиль диаграммы
ch2.title = f"{final_str}"
ch2.x_axis.title = "Время, с" # название оси Х
ch2.y_axis.title = "Активная мощность, МВт" # название оси У
series_exp2.smooth = False # сграживание кривой
series_exp2.graphicalProperties.line.width = 350 # толщина кривой линии
series_exp2.graphicalProperties.line.solidFill = 'FF0000' # цвет кривой Red
ch2.series.append(series_exp1)
ch2.series.append(series_exp2)
ch2.x_axis.scaling.min = 49
ch2.y_axis.scaling.min = 0
ch2.x_axis.scaling.max = 65
ws_Excel_Unite_exp_path.add_chart(ch2, get_column_letter(11) + str(22))
# График 3 активной мощности СГ на интервале от 1 до 6
ch_PSS = ScatterChart()
xvalues_exp1_pss = Reference(ws_Excel_Unite_1_exp,
min_col=1,
min_row=num_time_to_row,
max_row=ws_Excel_Unite_1_exp.max_row)
values_exp1_pss = Reference(ws_Excel_Unite_1_exp,
min_col=int(col_gen),
min_row=num_time_to_row - 1,
max_row=ws_Excel_Unite_1_exp.max_row)
xvalues_exp2_pss = Reference(ws_Excel_Unite_2_exp,
min_col=1,
min_row=num_time_to_row,
max_row=ws_Excel_Unite_2_exp.max_row)
values_exp2_pss = Reference(ws_Excel_Unite_2_exp,
min_col=int(col_gen),
min_row=num_time_to_row - 1,
max_row=ws_Excel_Unite_2_exp.max_row)
series_exp1_pss = Series(values_exp1_pss,
xvalues_exp1_pss,
title_from_data=True)
series_exp2_pss = Series(values_exp2_pss,
xvalues_exp2_pss,
title_from_data=True)
name_object = ws_Excel_Unite_1_exp[f'{get_column_letter(col_gen)}{str(num_time_to_row - 1)}'].value
remove_list = ['с', 'PSS']
edit_str_as_list = name_object.split()
final_list2 = [word for word in edit_str_as_list if word not in remove_list]
final_str = ' '.join(final_list2)
final_list2.clear()
ch_PSS.title = f"Эффективность PSS(стаб.) {final_str}"
ch_PSS.x_axis.title = "Время, с" # название оси Х
ch_PSS.y_axis.title = "Активная мощность, МВт" # название оси У
series_exp2_pss.smooth = False # сграживание кривой
series_exp2_pss.graphicalProperties.line.width = 350 # сграживание кривой
series_exp2_pss.graphicalProperties.line.solidFill = 'FF0000' # цвет кривой Red
ch_PSS.series.append(series_exp1_pss)
ch_PSS.series.append(series_exp2_pss)
ch_PSS.x_axis.scaling.min = 51
ch_PSS.y_axis.scaling.min = int(min_Chart)
ch_PSS.y_axis.scaling.max = int(max_Chart + 4)
ch_PSS.x_axis.scaling.max = 56
ws_Excel_Unite_exp_path.add_chart(ch_PSS, f'{get_column_letter(21)}{str(22)}')
# График 4 активной мощности СГ на интервале от 11 до 26
ch3 = ScatterChart()
xvalues = Reference(ws_Excel_Unite_1_exp,
min_col=1,
min_row=num_time_to_row,
max_row=ws_Excel_Unite_1_exp.max_row)
values = Reference(ws_Excel_Unite_1_exp,
min_col=col_gen,
min_row=num_time_to_row - 1,
max_row=ws_Excel_Unite_1_exp.max_row)
series = Series(values, xvalues, title_from_data=True)
name_object = ws_Excel_Unite_1_exp[f'{get_column_letter(col_gen)}{str(num_time_to_row - 1)}'].value
remove_list = ['с', 'PSS']
edit_str_as_list = name_object.split()
final_list3 = [word for word in edit_str_as_list if word not in remove_list]
final_str = ' '.join(final_list3)
final_list3.clear()
# ch3.style = 12 # стиль диаграммы
ch3.title = f"{final_str}"
ch3.x_axis.title = "Время, с" # название оси Х
ch3.y_axis.title = "Активная мощность, МВт" # название оси У
ch3.series.append(series)
ch3.x_axis.scaling.min = 61
ch3.y_axis.scaling.min = int(minCh60)
ch3.y_axis.scaling.max = int(maxCh60 + 1)
ch3.x_axis.scaling.max = 76
ws_Excel_Unite_exp_path.add_chart(ch3, get_column_letter(1) + str(22))
for k in range(2, col_count + 1):
ch1 = ScatterChart()
xvalues_exp1 = Reference(ws_Excel_Unite_1_exp,
min_col=1,
min_row=num_time_to_row,
max_row=ws_Excel_Unite_1_exp.max_row)
values_exp1 = Reference(ws_Excel_Unite_1_exp,
min_col=k,
min_row=num_time_to_row - 1,
max_row=ws_Excel_Unite_1_exp.max_row)
xvalues_exp2 = Reference(ws_Excel_Unite_2_exp,
min_col=1,
min_row=num_time_to_row,
max_row=ws_Excel_Unite_2_exp.max_row)
values_exp2 = Reference(ws_Excel_Unite_2_exp,
min_col=k,
min_row=num_time_to_row - 1,
max_row=ws_Excel_Unite_2_exp.max_row)
series_exp1 = Series(values_exp1, xvalues_exp1, title_from_data=True)
series_exp2 = Series(values_exp2, xvalues_exp2, title_from_data=True)
name_object = ws_Excel_Unite_1_exp[get_column_letter(k) + str(num_time_to_row - 1)].value
remove_list = ['с', 'PSS']
edit_str_as_list = name_object.split()
final_list4 = [word for word in edit_str_as_list if word not in remove_list]
final_str = ' '.join(final_list4)
final_list4.clear()
# ch1.style = 12 # стиль диаграммы
ch1.title = f"{final_str}"
ch1.x_axis.title = "Время, с" # название оси Х
ch1.y_axis.title = "Активная мощность, МВт" # название оси У
series_exp2.smooth = False # сграживание кривой
series_exp2.graphicalProperties.line.width = 350 # сграживание кривой
series_exp2.graphicalProperties.line.solidFill = 'FF0000' # цвет кривой Red
series_exp2.smooth = False # сграживание кривой
series_exp2.graphicalProperties.line.width = 350 # сграживание кривой
series_exp2.graphicalProperties.line.solidFill = 'FF0000' # цвет кривой Red
ch1.series.append(series_exp1)
ch1.series.append(series_exp2)
ch1.x_axis.scaling.min = 48
ch1.y_axis.scaling.min = 0
ch1.x_axis.scaling.max = 80
# ch1.y_axis.scaling.max = 650 # диапазоны на графике
size_plot = 10
if k < int(col_count / 2):
if k == 2:
ws_Excel_Unite_exp_path.add_chart(ch1, get_column_letter(1) + str(40))
elif k != 2:
ws_Excel_Unite_exp_path.add_chart(ch1, get_column_letter((k - 2) * size_plot) + str(40))
else:
ws_Excel_Unite_exp_path.add_chart(ch1, get_column_letter((k - 2) * size_plot) + str(40))
def create_a_report(path_load, path_save, week):
workbook = xl.load_workbook(
path_load, data_only=True
) # открываем базу с отчетами, data_only=True - только данные (без формул)
sheet_1 = workbook.active # выбираем активный лист или sheet_1 = workbook['Отчет для АО НИПОМ'] # выбираем нужный лист
wb = Workbook(
) # создаем рабочую книгу, в которую будем сохранять данные из workbook
ws = wb.active # создаем рабочий лист
ws.sheet_properties.tabColor = "1072BA" # задаем цвет вкладки
ws.title = "Графики" # задаем имя вкладки
# задаем наименование столбцов
ws['A1'] = "Неделя"
ws['B1'] = "Wгпэг,\nкВт*ч"
ws['C1'] = "Wсм,\nкВт*ч"
ws['D1'] = "Wсумм,\nкВт*ч"
ws['E1'] = "Wпотр,\nкВт*ч"
ws['F1'] = "Wсн,\nкВт*ч"
ws['G1'] = "Моточасы\n(ГПЭГ)"
ws['H1'] = "Vст (Газ),\nм^3"
ws['I1'] = "Qкотла,\nкВт*ч"
ws['J1'] = "Vгпэг (Газ),\nм^3"
ws['K1'] = "Vкотел (Газ),\nм^3"
ws['L1'] = "Vгпэг/Wсумм,\nм3/кВт*ч"
ws['M1'] = "T_min,\n°C"
ws['N1'] = "T_max,\n°C"
ws['O1'] = "Дата начала"
ws['P1'] = "Дата\nокончания"
i = 2 # переменная для итерации строк в последующем цикле for
# for row in range(10073, sheet_1.max_row + 1): # цикл по строкам, начиная с нужной
week_OPE = [
50, 51, 52, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17,
18, 19, 20, 21, 22, 23, 24, 25, 26, 27, 28, 29, 30, 31, 32, 33, 34, 35,
36, 37, 38, 39, 40, 41, 42, 43, 44, 45, 46, 47, 48, 49, 50, 51, 52, 53
] # количество недель ОПЭ
count = 0
for row in range(5034, sheet_1.max_row +
1): # цикл по строкам с данными, начиная с 50-й недели
if sheet_1.cell(row,
46).value == week_OPE[i - 2]: # если значение в ячейке
# равно номеру недели из списка
t_min = ws.cell(
i, 13) # создаем переменную минимальной температуры АБ
t_max = ws.cell(
i, 14) # создаем переменную максимальной температуры АБ
data_start = ws.cell(i, 15)
if t_min.value is None: # проверка на наличии в ячейке значения
t_min.value = 100
if t_max.value is None: # проверка на наличии в ячейке значения
t_max.value = 0
if sheet_1.cell(
row,
23).value < t_min.value: # проверка на меньшее значение
t_min.value = sheet_1.cell(row, 23).value
if sheet_1.cell(
row,
23).value > t_max.value: # проверка на большее значение
t_max.value = sheet_1.cell(row, 23).value
if count == 0:
data_start.value = sheet_1.cell(row, 3).value
count = 1
if sheet_1.cell(row, 46).value == week_OPE[i - 2] and sheet_1.cell(
row, 47).value == 0: # если значение в ячейке
# равно номеру недели из списка и значение расхода не равно нулю
v_kotel = ws.cell(
i, 11
) # создаем переменную расхода котла без учета времени работы ГПЭГ
if v_kotel.value is None: # проверка на наличии в ячейке значения
v_kotel.value = 0
v_kotel.value += sheet_1.cell(
row, 48).value # суммирование значений в ячейках
if ws.cell(
i,
20).value is None: # проверка на наличии в ячейке значения
ws.cell(i, 20).value = 0
ws.cell(i, 20).value += 1 # суммирование количества ячеек
if sheet_1.cell(
row, 1
).value is not None: # проверяем пустая ячейка первого столбца или нет
# print(sheet_1.cell(row, 1).value)
week_cell = ws.cell(
i, 1) # создаем переменную строк для столбца с номерами недель
power_GPEG = ws.cell(
i, 2
) # создаем переменную строк для столбца с выработанной мощности ГПЭГ
power_Sun = ws.cell(
i, 3
) # создаем переменную строк для столбца с выработанной мощности солнечного модуля
power_Sum = ws.cell(
i, 4
) # создаем переменную строк для столбца с выработанной суммарной мощностью
power_Potr = ws.cell(
i, 5
) # создаем переменную строк для столбца с потребленной нагрузкой мощностью
power_SN = ws.cell(
i, 6
) # создаем переменную строк для столбца с собственными нуждами
mototime_GPEG = ws.cell(
i, 7) # создаем переменную строк для столбца с моточасами ГПЭГ
v_Sum = ws.cell(
i, 8
) # создаем переменную строк для столбца с общим потребленным объемом газа
data_end = ws.cell(i, 16)
week_cell.value = sheet_1.cell(
row,
1).value # присваиваем переменной значение из базовой таблицы
week_cell.number_format = openpyxl.styles.numbers.BUILTIN_FORMATS[
1] # формат ячейки 0
power_GPEG.value = sheet_1.cell(
row,
29).value # присваиваем переменной значение из базовой таблицы
power_GPEG.number_format = openpyxl.styles.numbers.BUILTIN_FORMATS[
2] # формат ячейки 0.00
power_Sun.value = sheet_1.cell(
row,
30).value # присваиваем переменной значение из базовой таблицы
power_Sun.number_format = openpyxl.styles.numbers.BUILTIN_FORMATS[
2] # формат ячейки 0.00
power_Sum.value = sheet_1.cell(
row,
31).value # присваиваем переменной значение из базовой таблицы
power_Sum.number_format = openpyxl.styles.numbers.BUILTIN_FORMATS[
2] # формат ячейки 0.00
power_Potr.value = sheet_1.cell(
row,
32).value # присваиваем переменной значение из базовой таблицы
power_Potr.number_format = openpyxl.styles.numbers.BUILTIN_FORMATS[
2] # формат ячейки 0.00
power_SN.value = sheet_1.cell(
row,
33).value # присваиваем переменной значение из базовой таблицы
power_SN.number_format = openpyxl.styles.numbers.BUILTIN_FORMATS[
2] # формат ячейки 0.00
mototime_GPEG.value = sheet_1.cell(
row,
34).value # присваиваем переменной значение из базовой таблицы
mototime_GPEG.number_format = openpyxl.styles.numbers.BUILTIN_FORMATS[
2] # формат ячейки 0.00
v_Sum.value = sheet_1.cell(
row,
35).value # присваиваем переменной значение из базовой таблицы
v_Sum.number_format = openpyxl.styles.numbers.BUILTIN_FORMATS[
2] # формат ячейки 0.00
data_end.value = sheet_1.cell(row, 3).value
count = 0
i += 1
for i in range(2, ws.max_row + 1):
if ws.cell(i, 1).value is not None:
ws.cell(i, 11).value = ws.cell(i, 11).value / (
ws.cell(i, 20).value /
30) * 168 # столбец с данными расхода газа котлом
ws.cell(
i, 11).number_format = openpyxl.styles.numbers.BUILTIN_FORMATS[
2] # формат ячейки 0.00
ws.cell(i, 10).value = ws.cell(i, 8).value - ws.cell(
i, 11).value # столбец с данными расхода газа ГПЭГ
ws.cell(
i, 10).number_format = openpyxl.styles.numbers.BUILTIN_FORMATS[
2] # формат ячейки 0.00
ws.cell(i, 9).value = ws.cell(
i, 11
).value * 9.5 # столбец с данными выработки тепловой энергии котла
ws.cell(i,
9).number_format = openpyxl.styles.numbers.BUILTIN_FORMATS[
1] # формат ячейки 0
ws.cell(i, 12).value = ws.cell(i, 10).value / ws.cell(
i, 4
).value # расчет эффективности выработки БКЭУ, выраженной через Vгпэг / Wсумм
ws.cell(
i, 12).number_format = openpyxl.styles.numbers.BUILTIN_FORMATS[
2] # формат ячейки 0.00
ws.cell(
i,
20).value = None # удаляем временное значение количества ячеек
else:
ws.cell(
i, 11
).value = None # удаляем значения выходящие за пределы расчетных недель
ws.cell(
i, 13
).value = None # удаляем значения выходящие за пределы расчетных недель
ws.cell(
i, 14
).value = None # удаляем значения выходящие за пределы расчетных недель
ws.cell(
i, 15
).value = None # удаляем значения выходящие за пределы расчетных недель
ws.cell(
i, 20
).value = None # удаляем значения выходящие за пределы расчетных недель
# задаем ширину столбцов
ws.column_dimensions['A'].width = 10
ws.column_dimensions['B'].width = 10
ws.column_dimensions['C'].width = 10
ws.column_dimensions['D'].width = 10
ws.column_dimensions['E'].width = 10
ws.column_dimensions['F'].width = 10
ws.column_dimensions['G'].width = 12
ws.column_dimensions['H'].width = 10
ws.column_dimensions['I'].width = 10
ws.column_dimensions['J'].width = 12
ws.column_dimensions['K'].width = 14
ws.column_dimensions['L'].width = 15
ws.column_dimensions['M'].width = 10
ws.column_dimensions['N'].width = 10
ws.column_dimensions['O'].width = 18
ws.column_dimensions['P'].width = 18
thin_border = Border( # выделение границ ячеек
left=Side(border_style=BORDER_THIN, color='00000000'),
right=Side(border_style=BORDER_THIN, color='00000000'),
top=Side(border_style=BORDER_THIN, color='00000000'),
bottom=Side(border_style=BORDER_THIN, color='00000000'))
# цикл для задания ячейкам заголовков свойств
for row in ws.iter_cols(min_col=1, max_col=16, min_row=1, max_row=1):
for cel in row:
cel.font = Font(size=12,
bold=True) # размер шрифта и жирное выделение
cel.alignment = Alignment(
horizontal="center", vertical="center", wrapText=True
) # выравнивание по центру и разрешение переноса строк
cel.fill = PatternFill(start_color="EEEEEE",
end_color="EEEEEE",
fill_type="solid")
# цикл для выделения границ ячеек
for row in ws.iter_cols(min_col=1,
max_col=16,
min_row=1,
max_row=ws.max_row):
for cel in row:
cel.border = thin_border
# print(week)
# print(ws.cell(ws.max_row-1, 1).value)
if ws.cell(ws.max_row - 1, 1).value < week:
return "Задана неделя вне диапазона текущего ОПЭ, равного {} недель в 2021 году".format(
ws.cell(ws.max_row - 1, 1).value)
# построение графиков
# график "ДИАГРАММА ИЗМЕРЯЕМЫХ ПРАМЕТРОВ ПО НЕДЕЛЯМ"
cats = Reference(ws,
min_row=2,
max_row=ws.max_row - 1,
min_col=1,
max_col=1)
values = Reference(ws,
min_row=1,
max_row=ws.max_row - 1,
min_col=2,
max_col=8)
# chart = LineChart()
chart = BarChart()
chart.y_axis.title = 'Параметры'
chart.x_axis.title = 'Недели'
chart.height = 10
chart.width = 30
chart.add_data(values, titles_from_data=True)
chart.set_categories(cats)
ws.add_chart(chart, "A{}".format(ws.max_row + 2))
# график выработки ГПЭГ, СМ и общий
ch1 = LineChart()
cats = Reference(ws,
min_row=2,
max_row=ws.max_row - 1,
min_col=1,
max_col=1)
values = Reference(ws,
min_row=1,
max_row=ws.max_row - 1,
min_col=2,
max_col=4)
ch1.title = "ВЫРАБОТКА ЭЛЕКТРОЭНЕРГИИ" # заголовок
ch1.style = 13 # шрифт
ch1.height = 10 # высота
ch1.width = 20 # ширина
ch1.x_axis.title = 'Недели' # подпись оси х
ch1.y_axis.title = 'кВт*ч' # подпись оси у
ch1.legend.position = 'r' # позиция подписей данных справа
ch1.add_data(
values,
titles_from_data=True) # загрузка данных с заголовками столбцов
ch1.set_categories(cats) # загрузка подписи оси х
ch1.series[0].graphicalProperties.line.solidFill = "85C1E9" # цвет синий
ch1.series[1].graphicalProperties.line.solidFill = "F7DC6F" # цвет желтый
ch1.series[2].graphicalProperties.line.solidFill = "EC7063" # цвет красный
ch1.series[0].graphicalProperties.solidFill = "85C1E9" # цвет синий
ch1.series[1].graphicalProperties.solidFill = "F7DC6F" # цвет желтый
ch1.series[2].graphicalProperties.solidFill = "EC7063" # цвет красный
ws.add_chart(ch1,
"A{}".format(ws.max_row + 22)) # загрузка графика в ячейку
# график ПОТРЕБЛЕНИЕ ЭЛЕКТРОЭНЕРГИИ
ch2 = LineChart()
cats = Reference(ws,
min_row=2,
max_row=ws.max_row - 1,
min_col=1,
max_col=1)
values = Reference(ws,
min_row=1,
max_row=ws.max_row - 1,
min_col=4,
max_col=6)
ch2.title = "ПОТРЕБЛЕНИЕ ЭЛЕКТРОЭНЕРГИИ" # заголовок
ch2.style = 13 # шрифт
ch2.height = 10 # высота
ch2.width = 20 # ширина
ch2.x_axis.title = 'Недели' # подпись оси х
ch2.y_axis.title = 'кВт*ч' # подпись оси у
ch2.legend.position = 'r' # позиция подписей данных справа
ch2.add_data(
values,
titles_from_data=True) # загрузка данных с заголовками столбцов
ch2.set_categories(cats) # загрузка подписи оси х
ch2.series[
0].graphicalProperties.line.solidFill = "EC7063" # цвет линии красный
ch2.series[
1].graphicalProperties.line.solidFill = "F7DC6F" # цвет линии желтый
ch2.series[
2].graphicalProperties.line.solidFill = "85C1E9" # цвет линии синий
ch2.series[
0].graphicalProperties.solidFill = "EC7063" # цвет заливки красный
ch2.series[
1].graphicalProperties.solidFill = "F7DC6F" # цвет заливки желтый
ch2.series[
2].graphicalProperties.solidFill = "85C1E9" # цвет заливки синий
ws.add_chart(ch2,
"A{}".format(ws.max_row + 42)) # загрузка графика в ячейку
# график ПОТРЕБЛЕНИЕ ГАЗА
ch3 = LineChart()
cats = Reference(ws,
min_row=2,
max_row=ws.max_row - 1,
min_col=1,
max_col=1)
values = Reference(ws,
min_row=1,
max_row=ws.max_row - 1,
min_col=10,
max_col=11)
ch3.title = "ПОТРЕБЛЕНИЕ ГАЗА" # заголовок
ch3.style = 13 # шрифт
ch3.height = 10 # высота
ch3.width = 20 # ширина
ch3.x_axis.title = 'Недели' # подпись оси х
ch3.y_axis.title = 'м3' # подпись оси у
ch3.legend.position = 'r' # позиция подписей данных справа
ch3.add_data(
values,
titles_from_data=True) # загрузка данных с заголовками столбцов
ch3.set_categories(cats) # загрузка подписи оси х
ch3.series[
0].graphicalProperties.line.solidFill = "EC7063" # цвет линии красный
ch3.series[
1].graphicalProperties.line.solidFill = "85C1E9" # цвет линии синий
ch3.series[
0].graphicalProperties.solidFill = "EC7063" # цвет заливки красный
ch3.series[
1].graphicalProperties.solidFill = "85C1E9" # цвет заливки синий
ch31 = LineChart()
cats = Reference(ws,
min_row=2,
max_row=ws.max_row - 1,
min_col=1,
max_col=1)
values1 = Reference(ws,
min_row=1,
max_row=ws.max_row - 1,
min_col=8,
max_col=8)
ch31.title = "ПОТРЕБЛЕНИЕ ГАЗА" # заголовок
ch31.style = 13 # шрифт
ch31.height = 10 # высота
ch31.width = 20 # ширина
ch31.x_axis.title = 'Недели' # подпись оси х
ch31.y_axis.title = 'м3' # подпись оси у
ch31.legend.position = 'r' # позиция подписей данных справа
ch31.add_data(
values1,
titles_from_data=True) # загрузка данных с заголовками столбцов
ch31.set_categories(cats) # загрузка подписи оси х
ch3 += ch31
ws.add_chart(ch3,
"A{}".format(ws.max_row + 62)) # загрузка графика в ячейку
# график ЭФФЕКТИВНОСТЬ ВЫРАБОТКИ ЭЛЕКТРОЭНЕРГИИ БКЭУ
ch4 = LineChart()
cats = Reference(ws,
min_row=2,
max_row=ws.max_row - 1,
min_col=1,
max_col=1)
values = Reference(ws,
min_row=1,
max_row=ws.max_row - 1,
min_col=12,
max_col=12)
ch4.title = "ЭФФЕКТИВНОСТЬ ВЫРАБОТКИ ЭЛЕКТРОЭНЕРГИИ БКЭУ" # заголовок
ch4.style = 13 # шрифт
ch4.height = 10 # высота
ch4.width = 20 # ширина
ch4.x_axis.title = 'Недели' # подпись оси х
ch4.y_axis.title = 'м3/кВт*ч' # подпись оси у
ch4.legend.position = 'r' # позиция подписей данных справа
ch4.add_data(
values,
titles_from_data=True) # загрузка данных с заголовками столбцов
ch4.set_categories(cats) # загрузка подписи оси х
ch4.series[
0].graphicalProperties.line.solidFill = "28B463" # цвет линии зеленый
ch4.series[
0].graphicalProperties.solidFill = "28B463" # цвет заливки зеленый
ws.add_chart(ch4,
"A{}".format(ws.max_row + 82)) # загрузка графика в ячейку
# график ВЫРАБОТКА ТЕПЛОВОЙ ЭНЕРГИИ
ch5 = LineChart()
cats = Reference(ws,
min_row=2,
max_row=ws.max_row - 1,
min_col=1,
max_col=1)
values = Reference(ws,
min_row=1,
max_row=ws.max_row - 1,
min_col=9,
max_col=9)
ch5.title = "ВЫРАБОТКА ТЕПЛОВОЙ ЭНЕРГИИ" # заголовок
ch5.style = 13 # шрифт
ch5.height = 10 # высота
ch5.width = 20 # ширина
ch5.x_axis.title = 'Недели' # подпись оси х
ch5.y_axis.title = 'кВт*ч' # подпись оси у
ch5.legend.position = 'r' # позиция подписей данных справа
ch5.add_data(
values,
titles_from_data=True) # загрузка данных с заголовками столбцов
ch5.set_categories(cats) # загрузка подписи оси х
ch5.series[
0].graphicalProperties.line.solidFill = "C0392B" # цвет линии красный
ch5.series[
0].graphicalProperties.solidFill = "C0392B" # цвет заливки красный
ws.add_chart(ch5,
"A{}".format(ws.max_row + 102)) # загрузка графика в ячейку
# расчет суммы выработки ГПЭГ и СМ для построения общей диаграммы
ws.cell(ws.max_row, 2).value = 0
ws.cell(ws.max_row, 3).value = 0
for i in range(2, ws.max_row + 1):
if ws.cell(i, 1).value is None:
ws.cell(i, 1).value = "Итого:"
break
if ws.cell(i, 2).value is not None:
ws.cell(ws.max_row, 2).value += ws.cell(i, 2).value
if ws.cell(i, 3).value is not None:
ws.cell(ws.max_row, 3).value += ws.cell(i, 3).value
ws.cell(ws.max_row,
3).number_format = openpyxl.styles.numbers.BUILTIN_FORMATS[1]
ws.cell(ws.max_row,
2).number_format = openpyxl.styles.numbers.BUILTIN_FORMATS[1]
# построение общей диаграммы выработки ГПЭГ и СМ за отчетный период
chart_itog = PieChart()
labels = Reference(ws, min_col=2, max_col=3, min_row=1, max_row=1)
data = Reference(ws,
min_col=2,
max_col=3,
min_row=ws.max_row,
max_row=ws.max_row)
chart_itog.title = "Выработка за отчетный период {} недель".format(
ws.max_row - 2)
chart_itog.style = 13 # шрифт
chart_itog.height = 10 # высота
chart_itog.width = 20 # ширина
chart_itog.add_data(
data, titles_from_data=True) # загрузка данных с заголовками столбцов
chart_itog.set_categories(labels) # загрузка подписи оси х
slice = DataPoint(idx=0,
explosion=15) # разделение пирога и сдвиг на 15 пунктов
chart_itog.series[0].data_points = [
slice
] # применение сдвига к первому значению
ws.add_chart(chart_itog, "A{}".format(ws.max_row + 122))
# график температур внутри блок-бокса
ch6 = LineChart()
cats = Reference(ws,
min_row=2,
max_row=ws.max_row - 1,
min_col=1,
max_col=1)
values = Reference(ws,
min_row=1,
max_row=ws.max_row - 1,
min_col=13,
max_col=14)
ch6.title = "ТЕМПЕРАТУРА ВНУТРИ БКЭУ" # заголовок
ch6.style = 13 # шрифт
ch6.height = 10 # высота
ch6.width = 20 # ширина
ch6.x_axis.title = 'Недели' # подпись оси х
ch6.y_axis.title = '°C' # подпись оси у
ch6.legend.position = 'r' # позиция подписей данных справа
ch6.add_data(
values,
titles_from_data=True) # загрузка данных с заголовками столбцов
ch6.set_categories(cats) # загрузка подписи оси х
ch6.series[0].graphicalProperties.line.solidFill = "85C1E9" # цвет синий
ch6.series[1].graphicalProperties.line.solidFill = "EC7063" # цвет красный
ch6.series[0].graphicalProperties.solidFill = "85C1E9" # цвет синий
ch6.series[1].graphicalProperties.solidFill = "EC7063" # цвет красный
ws.add_chart(ch6,
"A{}".format(ws.max_row + 142)) # загрузка графика в ячейку
wb.save(path_save +
'\\Графики.xlsx') # сохранение таблицы в указанную директорию
# -------Генерация автоматического отчета в WORD------------------------
template = DocxTemplate('temp6707.docx')
#week = 17 # задаем номер недели для отчета
# задаем начальные значения недельных параметров
Wfull = 0
Wcm = 0
Wgpeg = 0
Wcn = 0
Wnagr = 0
Qgvk = 0
moto = 0
Vgvk = 0
Vgpeg = 0
Vbkeu = 0
Tmin = 0
Tmax = 0
# задаем начальные значения суммы параметров за период с начала ОПЭ
Wfull_sum = 0
Wcm_sum = 0
Wgpeg_sum = 0
Wcn_sum = 0
Wnagr_sum = 0
Qgvk_sum = 0
Vgvk_sum = 0
Vgpeg_sum = 0
Vbkeu_sum = 0
moto_sum = 0
# задаем начальные значения дня и месяца начала и окончания недели
d_start = 0
m_start = 0
d_end = 0
m_end = 0
# рассчитываем значения за неделю и сумму с начала ОПЭ
for i in range(2, ws.max_row):
Wfull_sum += ws.cell(i, 4).value
Wcm_sum += ws.cell(i, 3).value
Wgpeg_sum += ws.cell(i, 2).value
Wcn_sum += ws.cell(i, 6).value
Wnagr_sum += ws.cell(i, 5).value
Qgvk_sum += ws.cell(i, 9).value
Vgvk_sum += ws.cell(i, 11).value
Vgpeg_sum += ws.cell(i, 10).value
Vbkeu_sum += ws.cell(i, 8).value
moto_sum += ws.cell(i, 7).value
if ws.cell(i, 1).value == week:
Wfull = ws.cell(i, 4).value
Wcm = ws.cell(i, 3).value
Wgpeg = ws.cell(i, 2).value
Wcn = ws.cell(i, 6).value
Wnagr = ws.cell(i, 5).value
Qgvk = ws.cell(i, 9).value
moto = ws.cell(i, 7).value
Vgvk = ws.cell(i, 11).value
Vgpeg = ws.cell(i, 10).value
Vbkeu = ws.cell(i, 8).value
Tmin = ws.cell(i, 13).value
Tmax = ws.cell(i, 14).value
d_start = ws.cell(i, 15).value
m_start = ws.cell(i, 15).value
d_end = ws.cell(i, 16).value
m_end = ws.cell(i, 16).value
break
def month_name(num): # функция возврата названия месяца по его номеру
ru = [
'января', 'февраля', 'марта', 'апреля', 'мая', 'июня', 'июля',
'августа', 'сентября', 'октября', 'ноября', 'декабря'
]
return ru[int(num) - 1]
# Объявляем значения переменных, идентичных шаблону в документе word
context = {
'week': week,
'year': d_end.strftime('%Y'),
'Wfull': round(Wfull, 2),
'Wcm': round(Wcm, 2),
'Wgpeg': round(Wgpeg, 2),
'Wcn': round(Wcn, 2),
'Wnagr': round(Wnagr, 2),
'Qgvk': round(Qgvk, 1),
'moto': moto,
'Vgvk': round(Vgvk, 2),
'Vgpeg': round(Vgpeg, 2),
'Vbkeu': round(Vbkeu, 2),
'Tmin': Tmin,
'Tmax': Tmax,
'Wfull_sum': round(Wfull_sum, 1),
'Wcm_sum': round(Wcm_sum, 1),
'Wgpeg_sum': round(Wgpeg_sum, 1),
'Wcn_sum': round(Wcn_sum, 1),
'Wnagr_sum': round(Wnagr_sum, 1),
'Qgvk_sum': round(Qgvk_sum),
'Vgvk_sum': round(Vgvk_sum, 1),
'Vgpeg_sum': round(Vgpeg_sum, 1),
'Vbkeu_sum': round(Vbkeu_sum, 1),
'moto_sum': moto_sum,
'd_start': d_start.strftime('%d'),
'm_start': month_name(m_start.strftime('%m')),
'd_end': d_end.strftime('%d'),
'm_end': month_name(m_end.strftime('%m'))
}
# создаем автоматизированный отчет
template.render(context)
template.save(path_save +
'\\Еженедельный отчет по ОПЭ БКЭУ-{}.docx'.format(week)
) # сохранение отчета с атоприсвоением номера недели
file_path = path_save + '\\Еженедельный отчет по ОПЭ БКЭУ-{}.docx'.format(
week)
os.startfile(file_path)
return "Отчет успешно сформирован"
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~ca_len table_data
#################################################
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)].number_format = '0.00'
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)].number_format = '0.00'
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)].number_format = '0.00'
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)].number_format = '0.00'
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)].number_format = '0.00'
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)].number_format = '0.00'
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
#################################################
# Second: 报告期消耗
# 9 + ca_len * 2: title
# 10 + ca_len * 2: table title
# row_title + 2 ~ row_title + 2 + ca_len : table_data
#################################################
if has_energy_data_flag:
names = reporting_period_data['names']
ca_len = len(names)
row_title = 9 + ca_len * 2
ws['B' + str(row_title)].font = title_font
ws['B' + str(row_title)] = name + ' 单位面积值'
ws['D' + str(row_title)].font = title_font
ws['D' + str(row_title)] = str(report['tenant']['area']) + 'M²'
category = reporting_period_data['names']
# table_title
ws['B' + str(row_title + 1)].fill = table_fill
ws['B' + str(row_title + 1)].font = title_font
ws['B' + str(row_title + 1)].alignment = c_c_alignment
ws['B' + str(row_title + 1)] = '报告期'
ws['B' + str(row_title + 1)].border = f_border
ws['C' + str(row_title + 1)].font = title_font
ws['C' + str(row_title + 1)].alignment = c_c_alignment
ws['C' + str(row_title + 1)] = '算术平均数'
ws['C' + str(row_title + 1)].border = f_border
ws['D' + str(row_title + 1)].font = title_font
ws['D' + str(row_title + 1)].alignment = c_c_alignment
ws['D' + str(row_title + 1)] = '中位数'
ws['D' + str(row_title + 1)].border = f_border
ws['E' + str(row_title + 1)].font = title_font
ws['E' + str(row_title + 1)].alignment = c_c_alignment
ws['E' + str(row_title + 1)] = '最小值'
ws['E' + str(row_title + 1)].border = f_border
ws['F' + str(row_title + 1)].font = title_font
ws['F' + str(row_title + 1)].alignment = c_c_alignment
ws['F' + str(row_title + 1)] = '最大值'
ws['F' + str(row_title + 1)].border = f_border
ws['G' + str(row_title + 1)].font = title_font
ws['G' + str(row_title + 1)].alignment = c_c_alignment
ws['G' + str(row_title + 1)] = '样本标准差'
ws['G' + str(row_title + 1)].border = f_border
ws['H' + str(row_title + 1)].font = title_font
ws['H' + str(row_title + 1)].alignment = c_c_alignment
ws['H' + str(row_title + 1)] = '样本方差'
ws['H' + str(row_title + 1)].border = f_border
# table_data
for i, value in enumerate(category):
row_data = row_title + 2 + i
ws['B' + str(row_data)].font = name_font
ws['B' + str(row_data)].alignment = c_c_alignment
ws['B' + str(row_data)] = reporting_period_data['names'][
i] + " (" + reporting_period_data['units'][i] + "/M²)"
ws['B' + str(row_data)].border = f_border
ws['C' + str(row_data)].font = name_font
ws['C' + str(row_data)].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_data)] = round(
reporting_period_data['means_per_unit_area'][i], 2)
ws['C' + str(row_data)].border = f_border
ws['C' + str(row_data)].number_format = '0.00'
ws['D' + str(row_data)].font = name_font
ws['D' + str(row_data)].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_data)] = round(
reporting_period_data['medians_per_unit_area'][i], 2)
ws['D' + str(row_data)].border = f_border
ws['D' + str(row_data)].number_format = '0.00'
ws['E' + str(row_data)].font = name_font
ws['E' + str(row_data)].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_data)] = round(
reporting_period_data['minimums_per_unit_area'][i], 2)
ws['E' + str(row_data)].border = f_border
ws['E' + str(row_data)].number_format = '0.00'
ws['F' + str(row_data)].font = name_font
ws['F' + str(row_data)].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_data)] = round(
reporting_period_data['maximums_per_unit_area'][i], 2)
ws['F' + str(row_data)].border = f_border
ws['F' + str(row_data)].number_format = '0.00'
ws['G' + str(row_data)].font = name_font
ws['G' + str(row_data)].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_data)] = round(
reporting_period_data['stdevs_per_unit_area'][i], 2)
ws['G' + str(row_data)].border = f_border
ws['G' + str(row_data)].number_format = '0.00'
ws['H' + str(row_data)].font = name_font
ws['H' + str(row_data)].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_data)] = round(
reporting_period_data['variances_per_unit_area'][i], 2)
ws['H' + str(row_data)].border = f_border
ws['H' + str(row_data)].number_format = '0.00'
########################################################
# Third: 详细数据
# row_sat+row_title~ row_sat+row_title+time_len: line
# row_sat+1+row_title: table title
# i + row_sat + 2 + 10 * ca_len~: table_data
########################################################
has_timestamps_flag = True
if "timestamps" not in reporting_period_data.keys() or \
reporting_period_data['timestamps'] is None or \
len(reporting_period_data['timestamps']) == 0:
has_timestamps_flag = False
if has_timestamps_flag:
timestamps = reporting_period_data['timestamps'][0]
values = reporting_period_data['values']
names = reporting_period_data['names']
ca_len = len(names)
time_len = len(timestamps)
# title
row_title = 10 * ca_len
# row_st == row_statistical analysis table
row_sat = 12 + 3 * ca_len
ws['B' + str(row_sat + row_title)].font = title_font
ws['B' + str(row_sat + row_title)] = name + ' 详细数据'
# table_title
ws['B' + str(row_sat + 1 + row_title)].fill = table_fill
ws['B' + str(row_sat + 1 + row_title)].font = name_font
ws['B' + str(row_sat + 1 + row_title)].alignment = c_c_alignment
ws['B' + str(row_sat + 1 + row_title)] = "时间"
ws['B' + str(row_sat + 1 + row_title)].border = f_border
for i in range(0, ca_len):
col = chr(ord('C') + i)
ws[col + str(row_sat + 1 + row_title)].font = name_font
ws[col + str(row_sat + 1 + row_title)].alignment = c_c_alignment
ws[col + str(row_sat + 1 + row_title)] = names[
i] + " - (" + reporting_period_data['units'][i] + ")"
ws[col + str(row_sat + 1 + row_title)].border = f_border
# table_date
for i in range(0, time_len):
rows = i + row_sat + 2 + 10 * ca_len
ws['B' + str(rows)].font = name_font
ws['B' + str(rows)].alignment = c_c_alignment
ws['B' + str(rows)] = timestamps[i]
ws['B' + str(rows)].border = f_border
for index in range(0, ca_len):
col = chr(ord('C') + index)
ws[col + str(rows)].font = name_font
ws[col + str(rows)].alignment = c_c_alignment
ws[col + str(rows)] = round(values[index][i], 2)
ws[col + str(rows)].number_format = '0.00'
ws[col + str(rows)].border = f_border
# 小计
row_subtotals = row_sat + 2 + time_len + 10 * ca_len
ws['B' + str(row_subtotals)].font = name_font
ws['B' + str(row_subtotals)].alignment = c_c_alignment
ws['B' + str(row_subtotals)] = "小计"
ws['B' + str(row_subtotals)].border = f_border
for i in range(0, ca_len):
col = chr(ord('C') + i)
ws[col + str(row_subtotals)].font = name_font
ws[col + str(row_subtotals)].alignment = c_c_alignment
ws[col + str(row_subtotals)] = round(
reporting_period_data['subtotals'][i], 2)
ws[col + str(row_subtotals)].border = f_border
ws[col + str(row_subtotals)].number_format = '0.00'
# LineChart
for i in range(0, ca_len):
lc = LineChart()
lc.title = "报告期消耗" + " - " + names[
i] + "(" + reporting_period_data['units'][i] + ")"
lc.style = 10
lc.height = 8.40 # cm 1.05*8 1.05cm = 30 pt
lc.width = 31
lc.x_axis.majorTickMark = 'in'
lc.y_axis.majorTickMark = 'in'
times = Reference(ws,
min_col=2,
min_row=row_sat + 2 + row_title,
max_row=row_sat + 2 + row_title + time_len)
lc_data = Reference(ws,
min_col=3 + i,
min_row=row_sat + 1 + row_title,
max_row=row_sat + 1 + row_title + time_len)
lc.add_data(lc_data, titles_from_data=True)
lc.set_categories(times)
ser = lc.series[0]
ser.marker.symbol = "diamond"
ser.marker.size = 5
ws.add_chart(lc, 'B' + str(row_sat + 10 * i))
filename = str(uuid.uuid4()) + '.xlsx'
wb.save(filename)
return filename
for x in range(Ders[1], Ders[2] + 1):
Sheet.cell(x - Ders[1] + 3, Ders[1] + 1).value = Sheet.cell(2, x).value
Pos = "A15"
number = 1
numb2 = 15
Alfa = [[1, "A"], [2, "B"], [3, "C"], [4, "D"], [5, "E"], [6, "F"], [7, "G"],
[8, "H"], [9, "I"], [10, "J"], [11, "K"], [12, "L"], [13, "M"],
[14, "N"], [15, "O"], [16, "P"], [17, "Q"], [18, "R"], [19, "S"],
[20, "T"], [21, "U"], [22, "V"], [23, "W"], [24, "X"], [25, "Y"],
[26, "Z"]]
for Ders in Aralıklar:
if BarGrafiği == False:
values = Reference(Sheet,
min_col=Ders[1],
min_row=3,
max_row=Ders[2] - Ders[1] + 3)
Dates = Reference(Sheet,
min_col=Ders[1] + 1,
min_row=3,
max_row=Ders[2] - Ders[1] + 3)
# chart = LineChart()
chart = LineChart()
Sheet.add_chart(chart, Pos)
chart.title = Ders[0]
chart.y_axis.title = Y_GrafiğiBaşlık
chart.x_axis.title = X_GrafiğiBaşlık
chart.add_data(values)
s1 = chart.series[0]
s1.marker.symbol = "triangle"
chart.set_categories(Dates)
data[j][2] = float(Mag_orth_buf[j - 1])
data[j][3] = float(Board_temp_buf[j - 1])
data[j][4] = float(Ambient_temp_buf[j - 1])
data[j][5] = float(Humidity_buf[j - 1])
#print ( str( data ) )
for row in data:
ws.append(row)
chart = ScatterChart()
chart.title = "Magnitude vs Time"
chart.style = 13
chart.y_axis.title = 'Magnitude'
chart.x_axis.title = 'Time delta(seconds)'
xvalues = Reference(ws, min_col=1, min_row=2, max_row=row_size)
for i in range(2, 4):
values = Reference(ws, min_col=i, min_row=1, max_row=row_size)
series = Series(values, xvalues, title_from_data=True)
chart.series.append(series)
ws.add_chart(chart, "H1")
chart2 = ScatterChart()
chart2.title = "Temperature vs Time"
chart2.style = 13
chart2.y_axis.title = 'Degree(C)'
chart2.x_axis.title = 'Time delta(seconds)'
xvalues = Reference(ws, min_col=1, min_row=2, max_row=row_size)
values = Reference(ws, min_col=4, min_row=1, max_row=row_size)
def excel_create_chart(excel_file):
"""
生成图表
:param excel_file:
:return:
"""
wb = load_workbook(excel_file)
print(f'{datetime.now()} | 信息 | 开始Excel图表渲染')
# 进行净值走势图渲染
net_worth_sheet = wb['账户净值']
net_worth_chart_sheet = wb.create_chartsheet(title='净值走势图')
chart1 = LineChart()
dates1 = Reference(net_worth_sheet,
min_col=1,
min_row=2,
max_row=len(net_worth_sheet['A']))
data1 = Reference(net_worth_sheet,
min_col=3,
min_row=1,
max_row=len(net_worth_sheet['C']))
chart1.add_data(data1, titles_from_data=True)
chart1.y_axis = NumericAxis(title='净值', majorTickMark='out')
chart1.x_axis = TextAxis(majorTickMark='out',
tickLblSkip=10,
tickMarkSkip=10,
noMultiLvlLbl=True,
numFmt='yyyy-mm-dd')
chart1.legend = None
chart1.title = str()
chart1.style = 1
chart1.set_categories(dates1)
net_worth_chart_sheet.add_chart(chart1)
# 进行权益走势图渲染
account_sheet = wb['账户统计']
account_chart_sheet = wb.create_chartsheet(title='权益走势图')
dates2 = Reference(account_sheet,
min_col=1,
min_row=2,
max_row=len(account_sheet['A']))
chart2 = LineChart(varyColors=True)
data2 = Reference(account_sheet,
min_col=8,
min_row=1,
max_row=len(account_sheet['H']))
chart2.add_data(data2, titles_from_data=True)
chart2.y_axis = NumericAxis(title='权益', majorTickMark='out')
chart2.legend = None
chart2.set_categories(dates2)
chart3 = BarChart()
data3 = Reference(account_sheet,
min_col=10,
min_row=1,
max_row=len(account_sheet['J']))
chart3.add_data(data3, titles_from_data=True)
chart3.y_axis = NumericAxis(axId=200,
title='风险度',
majorGridlines=None,
majorTickMark='out',
crosses='max')
chart3.x_axis = TextAxis(majorTickMark='out',
tickLblSkip=10,
tickMarkSkip=10,
noMultiLvlLbl=True,
numFmt='yyyy-mm-dd')
chart3.legend = None
chart3.set_categories(dates2)
chart2 += chart3
account_chart_sheet.add_chart(chart2)
# 进行交易分布图的渲染
categories_analysis_sheet = wb['交易分析(按品种)']
trading_frequency_analysis_sheet = wb.create_chartsheet(title='交易分布图')
labels = Reference(categories_analysis_sheet,
min_col=1,
min_row=2,
max_row=len(categories_analysis_sheet['A']))
chart4 = PieChart(varyColors=True)
chart4.style = 34
data4 = Reference(categories_analysis_sheet,
min_col=4,
min_row=2,
max_row=len(categories_analysis_sheet['D']))
chart4.add_data(data4)
chart4.set_categories(labels)
chart4.legend = None
# chart4.series[0].data_points = [DataPoint(idx=i, explosion=8)
# for i in range(len(categories_analysis_sheet['D']) - 1)]
chart4.series[0].dLbls = DataLabelList(dLblPos='bestFit',
showPercent=True,
showCatName=True,
showVal=True,
showLeaderLines=True)
chart4.layout = Layout(manualLayout=ManualLayout(
x=0, y=0, h=0.75, w=0.75, xMode='factor', yMode='factor'))
trading_frequency_analysis_sheet.add_chart(chart4)
# 进行品种盈亏图的渲染
categories_win_and_loss_chart_sheet = wb.create_chartsheet(title='品种盈亏图')
chart5 = BarChart(barDir='col')
chart5.style = 18
data5 = Reference(categories_analysis_sheet,
min_col=2,
min_row=2,
max_row=len(categories_analysis_sheet['B']))
chart5.add_data(data5)
chart5.set_categories(labels)
chart5.legend = None
chart5.series[0].dLbls = DataLabelList(showVal=True)
chart5.y_axis = NumericAxis(title='平仓盈亏',
majorTickMark='out',
minorTickMark='out')
categories_win_and_loss_chart_sheet.add_chart(chart5)
# 进行交易盈亏图的渲染
trading_win_and_loss_chart_sheet = wb.create_chartsheet(title='交易盈亏图')
chart6 = RadarChart()
chart6.style = 24
data6 = Reference(categories_analysis_sheet,
min_col=8,
max_col=9,
min_row=1,
max_row=categories_analysis_sheet.max_row)
chart6.add_data(data6, titles_from_data=True)
chart6.set_categories(labels)
trading_win_and_loss_chart_sheet.add_chart(chart6)
# 图表保存
wb.save(excel_file)
wb.close()
# 输出信息
print(f'{datetime.now()} | 信息 | 已生成Excel图表')
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, 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
ws.column_dimensions['C'].width = 35.0
for i in range(ord('D'), 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.merge_cells("G3:J3")
for i in range(ord('G'), ord('J') + 1):
ws[chr(i) + '3'].border = b_border
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 \
"names" not in report['reporting_period'].keys() or len(report['reporting_period']['names']) == 0:
filename = str(uuid.uuid4()) + '.xlsx'
wb.save(filename)
return filename
#################################################
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
if has_energy_data_flag:
ws['B6'].font = title_font
ws['B6'] = name + ' 报告期消耗'
category = reporting_period_data['names']
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
ws['B10'].font = title_font
ws['B10'].alignment = c_c_alignment
ws['B10'] = '环比'
ws['B10'].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'] = reporting_period_data['names'][
i] + " (" + reporting_period_data['units'][i] + ")"
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['subtotals'][i], 0)
ws[col + '8'].border = f_border
ws[col + '9'].font = name_font
ws[col + '9'].alignment = c_c_alignment
ws[col + '9'] = round(
reporting_period_data['subtotals_per_unit_area'][i], 2)
ws[col + '9'].border = f_border
ws[col + '10'].font = name_font
ws[col + '10'].alignment = c_c_alignment
ws[col + '10'] = str(round(reporting_period_data['increment_rates'][i] * 100, 2)) + "%" \
if reporting_period_data['increment_rates'][i] is not None else "-"
ws[col + '10'].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'] = round(
reporting_period_data['total_in_kgce_per_unit_area'], 2)
ws[tce_col + '9'].border = f_border
ws[tce_col + '10'].font = name_font
ws[tce_col + '10'].alignment = c_c_alignment
ws[tce_col + '10'] = str(round(reporting_period_data['increment_rate_in_kgce'] * 100, 2)) + "%" \
if reporting_period_data['increment_rate_in_kgce'] is not None else "-"
ws[tce_col + '10'].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'] = round(
reporting_period_data['total_in_kgco2e_per_unit_area'], 2)
ws[tco2e_col + '9'].border = f_border
ws[tco2e_col + '10'].font = name_font
ws[tco2e_col + '10'].alignment = c_c_alignment
ws[tco2e_col + '10'] = str(round(reporting_period_data['increment_rate_in_kgco2e'] * 100, 2)) + "%" \
if reporting_period_data['increment_rate_in_kgco2e'] is not None else "-"
ws[tco2e_col + '10'].border = f_border
else:
for i in range(6, 10 + 1):
ws.row_dimensions[i].height = 0.1
#################################################
has_ele_peak_flag = True
if "toppeaks" not in reporting_period_data.keys() or \
reporting_period_data['toppeaks'] is None or \
len(reporting_period_data['toppeaks']) == 0:
has_ele_peak_flag = False
if has_ele_peak_flag:
ws['B12'].font = title_font
ws['B12'] = name + ' 分时电耗'
ws['B13'].fill = table_fill
ws['B13'].font = name_font
ws['B13'].alignment = c_c_alignment
ws['B13'].border = f_border
ws['C13'].fill = table_fill
ws['C13'].font = name_font
ws['C13'].alignment = c_c_alignment
ws['C13'].border = f_border
ws['C13'] = '分时电耗'
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'].border = f_border
ws['C14'] = round(reporting_period_data['toppeaks'][0], 0)
ws['B15'].font = title_font
ws['B15'].alignment = c_c_alignment
ws['B15'] = '峰'
ws['B15'].border = f_border
ws['C15'].font = title_font
ws['C15'].alignment = c_c_alignment
ws['C15'].border = f_border
ws['C15'] = round(reporting_period_data['onpeaks'][0], 0)
ws['B16'].font = title_font
ws['B16'].alignment = c_c_alignment
ws['B16'] = '平'
ws['B16'].border = f_border
ws['C16'].font = title_font
ws['C16'].alignment = c_c_alignment
ws['C16'].border = f_border
ws['C16'] = round(reporting_period_data['midpeaks'][0], 0)
ws['B17'].font = title_font
ws['B17'].alignment = c_c_alignment
ws['B17'] = '谷'
ws['B17'].border = f_border
ws['C17'].font = title_font
ws['C17'].alignment = c_c_alignment
ws['C17'].border = f_border
ws['C17'] = round(reporting_period_data['offpeaks'][0], 0)
pie = PieChart()
labels = Reference(ws, min_col=2, min_row=14, max_row=17)
pie_data = Reference(ws, min_col=3, min_row=13, max_row=17)
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, "D13")
else:
for i in range(12, 18 + 1):
ws.row_dimensions[i].height = 0.1
################################################
current_row_number = 19
has_kgce_data_flag = True
if "subtotals_in_kgce" not in reporting_period_data.keys() or \
reporting_period_data['subtotals_in_kgce'] is None or \
len(reporting_period_data['subtotals_in_kgce']) == 0:
has_kgce_data_flag = False
if has_kgce_data_flag:
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
ws['B' + str(current_row_number)].fill = table_fill
ws['B' + str(current_row_number)].font = name_font
ws['B' + str(current_row_number)].alignment = c_c_alignment
ws['B' + str(current_row_number)].border = f_border
ws['C' + str(current_row_number)].fill = table_fill
ws['C' + str(current_row_number)].font = name_font
ws['C' + str(current_row_number)].alignment = c_c_alignment
ws['C' + str(current_row_number)].border = f_border
ws['C' + str(current_row_number)] = '吨标准煤 (TCE) 占比'
current_row_number += 1
ca_len = len(reporting_period_data['names'])
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)] = reporting_period_data['names'][i]
ws['B' + str(current_row_number)].border = f_border
ws['C' + str(current_row_number)].font = title_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'][i], 3)
current_row_number += 1
table_end_row_number = current_row_number - 1
pie = PieChart()
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
table_cell = 'D' + str(table_start_row_number)
ws.add_chart(pie, table_cell)
if ca_len < 4:
current_row_number = current_row_number - ca_len + 4
else:
for i in range(21, 29 + 1):
current_row_number = 30
ws.row_dimensions[i].height = 0.1
#####################################################
current_row_number += 1
has_kgco2e_data_flag = True
if "subtotals_in_kgco2e" not in reporting_period_data.keys() or \
reporting_period_data['subtotals_in_kgco2e'] is None or \
len(reporting_period_data['subtotals_in_kgco2e']) == 0:
has_kgco2e_data_flag = False
if has_kgco2e_data_flag:
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
ws['B' + str(current_row_number)].fill = table_fill
ws['B' + str(current_row_number)].font = name_font
ws['B' + str(current_row_number)].alignment = c_c_alignment
ws['B' + str(current_row_number)].border = f_border
ws['C' + str(current_row_number)].fill = table_fill
ws['C' + str(current_row_number)].font = name_font
ws['C' + str(current_row_number)].alignment = c_c_alignment
ws['C' + str(current_row_number)].border = f_border
ws['C' + str(current_row_number)] = '吨二氧化碳排放 (TCO2E) 占比'
current_row_number += 1
ca_len = len(reporting_period_data['names'])
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)] = reporting_period_data['names'][i]
ws['B' + str(current_row_number)].border = f_border
ws['C' + str(current_row_number)].font = title_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'][i], 3)
current_row_number += 1
table_end_row_number = current_row_number - 1
pie = PieChart()
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
table_cell = 'D' + str(table_start_row_number)
ws.add_chart(pie, table_cell)
if ca_len < 4:
current_row_number = current_row_number - ca_len + 4
else:
for i in range(30, 39 + 1):
current_row_number = 40
ws.row_dimensions[i].height = 0.1
###############################################
current_row_number += 1
has_detail_data_flag = True
table_start_draw_flag = current_row_number + 1
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:
reporting_period_data = report['reporting_period']
times = reporting_period_data['timestamps']
ca_len = len(report['reporting_period']['names'])
ws['B' + str(current_row_number)].font = title_font
ws['B' + str(current_row_number)] = name + ' 详细数据'
table_start_row_number = (current_row_number + 1) + ca_len * 5
current_row_number = table_start_row_number
time = times[0]
has_data = False
if len(time) > 0:
has_data = True
if has_data:
ws['B' + str(current_row_number)].fill = table_fill
ws['B' + str(current_row_number)].font = title_font
ws['B' + str(current_row_number)].border = f_border
ws['B' + str(current_row_number)].alignment = c_c_alignment
ws['B' + str(current_row_number)] = '日期时间'
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 = title_font
ws[col + str(current_row_number)].alignment = c_c_alignment
ws[col + str(current_row_number)] = reporting_period_data['names'][i] + \
" (" + reporting_period_data['units'][i] + ")"
ws[col + str(current_row_number)].border = f_border
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)] = time[i]
ws['B' + str(current_row_number)].border = f_border
for j in range(0, ca_len):
col = chr(ord('C') + j)
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)] = round(
reporting_period_data['values'][j][i], 0)
ws[col + str(current_row_number)].border = f_border
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)] = '小计'
ws['B' + str(current_row_number)].border = f_border
for i in range(0, ca_len):
col = chr(ord('C') + i)
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)] = round(
reporting_period_data['subtotals'][i], 0)
ws[col + str(current_row_number)].border = f_border
# bar
bar = BarChart()
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 = len(time)
bar.dLbls = DataLabelList()
bar.dLbls.showVal = True
bar.dLbls.showPercent = True
chart_col = 'B'
chart_cell = chart_col + str(table_start_draw_flag + 5 * i)
ws.add_chart(bar, chart_cell)
current_row_number += 1
else:
for i in range(40, 69 + 1):
current_row_number = 70
ws.row_dimensions[i].height = 0.1
filename = str(uuid.uuid4()) + '.xlsx'
wb.save(filename)
return filename
def session_excel(db_name='session.db',
excel_name='session.xlsx',
include_charts=True):
"""Converts the sqlite3 db generated from session.json to an
Excel spreadsheet"""
# Define styles and borders to use within the spreadsheet.
gray_background = PatternFill(start_color='ededed', fill_type='solid')
white_background = PatternFill(start_color=WHITE, fill_type=None)
highlighted_background = PatternFill(start_color=YELLOW, fill_type='solid')
backgrounds_iter = itertools.cycle([gray_background, white_background])
thin_gray = Side(border_style=BORDER_THIN, color='d3d3d3')
default_border = Border(left=thin_gray,
top=thin_gray,
right=thin_gray,
bottom=thin_gray)
thick_black = Side(border_style=BORDER_THIN, color=BLACK)
new_series_border = Border(left=thin_gray,
top=thick_black,
right=thin_gray,
bottom=thin_gray)
# Create the workbook
wb = openpyxl.Workbook()
sheet = wb.active
sheet.title = 'session'
# Get the data from the generated sqlite3 database.
cursor = sqlite3.connect(db_name).cursor()
cursor.execute("select * from evidence;")
columns = [description[0] for description in cursor.description]
series_column = columns.index('series')
inquiry_column = columns.index('inquiry')
is_target_column = columns.index('is_target')
series, inquiry, is_target = None, None, None
# Write header
write_row(sheet, 1, columns)
# Maps the chart title to the starting row for the data.
chart_data = {}
# Write rows
inq_background = next(backgrounds_iter)
alp_len = None
for i, row in enumerate(cursor):
rownum = i + 2 # Excel is 1-indexed; also account for column row.
is_target = int(row[is_target_column]) == 1
border = default_border
if row[series_column] != series:
# Place a thick top border before each new series to make it
# easier to visually scan the spreadsheet.
series = row[series_column]
border = new_series_border
if row[inquiry_column] != inquiry:
inquiry = row[inquiry_column]
# Set the alphabet length used for chart data ranges.
if not alp_len and i > 0:
alp_len = i
chart_data[f"Series {series} inquiry {inquiry}"] = rownum
# Toggle the background for each inquiry for easier viewing.
inq_background = next(backgrounds_iter)
# write to spreadsheet
write_row(
sheet,
rownum,
row,
background=highlighted_background if is_target else inq_background,
border=border)
# Add chart for each inquiry
if include_charts:
stim_col = columns.index('stim') + 1
lm_col = columns.index('lm') + 1
likelihood_col = columns.index('cumulative') + 1
for title, min_row in chart_data.items():
max_row = min_row + (alp_len - 1)
chart = BarChart()
chart.type = "col"
chart.title = title
chart.y_axis.title = 'likelihood'
chart.x_axis.title = 'stimulus'
data = Reference(sheet,
min_col=lm_col,
min_row=min_row,
max_row=max_row,
max_col=likelihood_col)
categories = Reference(sheet,
min_col=stim_col,
min_row=min_row,
max_row=max_row)
chart.add_data(data, titles_from_data=False)
chart.series[0].title = openpyxl.chart.series.SeriesLabel(v="lm")
chart.series[1].title = openpyxl.chart.series.SeriesLabel(v="eeg")
chart.series[2].title = openpyxl.chart.series.SeriesLabel(
v="combined")
chart.set_categories(categories)
sheet.add_chart(chart, f'M{min_row + 1}')
# Freeze header row
sheet.freeze_panes = 'A2'
wb.save(excel_name)
print("Wrote output to " + excel_name)
#импортируем все необходимое
from openpyxl import Workbook
from openpyxl.chart import BarChart, Reference, Series, LineChart, ScatterChart
from openpyxl.styles import Font, Color, colors
wb = Workbook()
ws = wb.active
for i in range(10):
ws.append([i])
#строим график
values = Reference(ws, min_col=1, min_row=1, max_col=1, max_row=10)
#chart = LineChart() #получается линия
chart = BarChart() #получается столбики
ws.add_chart(chart, "A15")
chart.title = "Line Chart"
chart.y_axis.title = "Size"
chart.x_axis.title = "Test Number"
chart.add_data(values)
s1 = chart.series[0]
s1.marker.symbol = "triangle"
wb.save("МойТест_4.xlsx")
def makeReport(data):
# Создаем workbook excel и забираем активный worksheet
wb = Workbook()
ws = wb.active
country = ''
rows = []
rows.append((
'Date:',
'today confirmed cases',
'new confirmed cases:',
'today active cases:',
'new active cases:',
'today recovered cases:',
'new recovered cases:',
'today deaths:',
'new deaths:',
))
for date in data['dates']:
if (data['dates'][date]['countries'] and country == ''):
country = list(data['dates'][date]['countries'].keys())[0]
elif (country == ''):
print('Указаная страна не найдена')
exit()
statistic = data['dates'][date]['countries'][country]
rows.append(
(date, statistic['today_confirmed'],
statistic['today_new_confirmed'], statistic['today_open_cases'],
statistic['today_new_open_cases'], statistic['today_recovered'],
statistic['today_new_recovered'], statistic['today_deaths'],
statistic['today_new_deaths']))
for row in rows:
ws.append(row)
# Формируем графики:
dates_count = len(list(data['dates'].keys()))
date_from = list(data['dates'].keys())[0]
date_to = list(data['dates'].keys())[-1]
# Указываем что даты лежат в col1
dates = Reference(ws, min_col=1, min_row=1, max_row=dates_count + 1)
# График 1
data = Reference(ws,
min_col=2,
max_col=3,
min_row=1,
max_row=dates_count + 1)
chart = LineChart()
chart.title = "Confirmed cases in " + country + " from " + date_from + " to " + date_to
chart.style = 15
chart.y_axis.title = "count"
chart.y_axis.crossAx = 100
chart.add_data(data, titles_from_data=True)
chart.set_categories(dates)
# Добавляем График 1 отчет
ws.add_chart(chart, "K1")
# График 2
data = Reference(ws,
min_col=4,
max_col=5,
min_row=1,
max_row=dates_count + 1)
chart = LineChart()
chart.title = "Active cases in " + country + " from " + date_from + " to " + date_to
chart.style = 14
chart.y_axis.title = "count"
chart.y_axis.crossAx = 100
chart.add_data(data, titles_from_data=True)
chart.set_categories(dates)
# Добавляем График 2 отчет
ws.add_chart(chart, "K15")
# График 3
data = Reference(ws,
min_col=6,
max_col=7,
min_row=1,
max_row=dates_count + 1)
chart = LineChart()
chart.title = "Recovered cases " + country + " from " + date_from + " to " + date_to
chart.style = 13
chart.y_axis.title = "count"
chart.y_axis.crossAx = 100
chart.add_data(data, titles_from_data=True)
chart.set_categories(dates)
# Добавляем График 3 отчет
ws.add_chart(chart, "T1")
# График 4
data = Reference(ws,
min_col=8,
max_col=9,
min_row=1,
max_row=dates_count + 1)
chart = LineChart()
chart.title = "Death cases in " + country + " from " + date_from + " to " + date_to
chart.style = 12
chart.y_axis.title = "count"
chart.y_axis.crossAx = 100
chart.add_data(data, titles_from_data=True)
chart.set_categories(dates)
# Добавляем График 4 отчет
ws.add_chart(chart, "T15")
timestamp = datetime.datetime.now().strftime("%d-%m-%Y %H.%M.%S")
report = timestamp + " " + country + ".xlsx"
# Сохраняем отчет
wb.save(report)
return report
from openpyxl import load_workbook
wb = load_workbook("sample.xlsx")
ws = wb.active
from openpyxl.chart import BarChart, Reference, LineChart
# B2:C11 까지의 데이터 차트로 생성
""" bar_value = Reference(ws, min_row = 2, max_row = 11, min_col = 2, max_col = 3) # min,max 같은 인자 값 생략 불가
bar_chart = BarChart() # 차트 종류 설정 ( Bar, Line, pIE ..)
bar_chart.add_data(bar_value) #차트 데이터 추가
ws.add_chart(bar_chart, "E1") # 차트 넣을 위치 정의 """
# B1:C11 까지의 데이터
line_value = Reference(ws, min_row = 1, max_row = 11, min_col = 2, max_col = 3)
line_chart = LineChart()
line_chart.add_data(line_value, titles_from_data = True) # 계열 > 영어, 수학 제목에서 가져옴
line_chart.title = "성적표" # 제목
line_chart.style = 20 # 미리 정의된 스타일 적용하는 것임
line_chart.y_axis.title = "점수" # Y축의 제목
line_chart.x_axis.title = "번호" # X축의 제목
ws.add_chart(line_chart, "E1")
wb.save("sample_chart.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 = 102
for i in range(2, 2000 + 1):
ws.row_dimensions[i].height = 42
# for i in range(2, 37 + 1):
# ws.row_dimensions[i].height = 30
#
# for i in range(38, 69 + 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('L')):
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")
img.width = img.width * 0.85
img.height = img.height * 0.85
# img = Image("myems.png")
ws.add_image(img, 'B1')
# Title
ws.row_dimensions[3].height = 60
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
##################################
reporting_period_data = report['reporting_period']
has_cost_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_cost_data_flag = False
if has_cost_data_flag:
ws['B6'].font = title_font
ws['B6'] = name + ' 报告期成本'
category = reporting_period_data['names']
ca_len = len(category)
ws.row_dimensions[7].height = 60
ws['B7'].fill = table_fill
ws['B7'].border = f_border
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
ws['B10'].font = title_font
ws['B10'].alignment = c_c_alignment
ws['B10'] = '环比'
ws['B10'].border = f_border
col = ''
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'] = reporting_period_data['names'][
i] + " (" + reporting_period_data['units'][i] + ")"
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['subtotals'][i], 2)
ws[col + '8'].border = f_border
ws[col + '9'].font = name_font
ws[col + '9'].alignment = c_c_alignment
ws[col + '9'] = round(
reporting_period_data['subtotals_per_unit_area'][i], 2)
ws[col + '9'].border = f_border
ws[col + '10'].font = name_font
ws[col + '10'].alignment = c_c_alignment
ws[col + '10'] = str(round(reporting_period_data['increment_rates'][i] * 100, 2)) + "%" \
if reporting_period_data['increment_rates'][i] is not None else "-"
ws[col + '10'].border = f_border
col = chr(ord(col) + 1)
ws[col + '7'].fill = table_fill
ws[col + '7'].font = name_font
ws[col + '7'].alignment = c_c_alignment
ws[col + '7'] = "总计 (" + reporting_period_data['total_unit'] + ")"
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'], 2)
ws[col + '8'].border = f_border
ws[col + '9'].font = name_font
ws[col + '9'].alignment = c_c_alignment
ws[col + '9'] = round(reporting_period_data['total_per_unit_area'], 2)
ws[col + '9'].border = f_border
ws[col + '10'].font = name_font
ws[col + '10'].alignment = c_c_alignment
ws[col + '10'] = str(round(reporting_period_data['total_increment_rate'] * 100, 2)) + "%" \
if reporting_period_data['total_increment_rate'] is not None else "-"
ws[col + '10'].border = f_border
else:
for i in range(6, 10 + 1):
ws.row_dimensions[i].height = 0.1
##############################
has_ele_peak_flag = True
if "toppeaks" not in reporting_period_data.keys() or \
reporting_period_data['toppeaks'] is None or \
len(reporting_period_data['toppeaks']) == 0:
has_ele_peak_flag = False
if has_ele_peak_flag:
ws['B12'].font = title_font
ws['B12'] = name + '分时用电成本'
ws.row_dimensions[13].height = 60
ws['B13'].fill = table_fill
ws['B13'].font = name_font
ws['B13'].alignment = c_c_alignment
ws['B13'].border = f_border
ws['C13'].fill = table_fill
ws['C13'].font = name_font
ws['C13'].alignment = c_c_alignment
ws['C13'].border = f_border
ws['C13'] = '分时用电成本'
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'].border = f_border
ws['C14'] = round(reporting_period_data['toppeaks'][0], 2)
ws['B15'].font = title_font
ws['B15'].alignment = c_c_alignment
ws['B15'] = '峰'
ws['B15'].border = f_border
ws['C15'].font = title_font
ws['C15'].alignment = c_c_alignment
ws['C15'].border = f_border
ws['C15'] = round(reporting_period_data['onpeaks'][0], 2)
ws['B16'].font = title_font
ws['B16'].alignment = c_c_alignment
ws['B16'] = '平'
ws['B16'].border = f_border
ws['C16'].font = title_font
ws['C16'].alignment = c_c_alignment
ws['C16'].border = f_border
ws['C16'] = round(reporting_period_data['midpeaks'][0], 2)
ws['B17'].font = title_font
ws['B17'].alignment = c_c_alignment
ws['B17'] = '谷'
ws['B17'].border = f_border
ws['C17'].font = title_font
ws['C17'].alignment = c_c_alignment
ws['C17'].border = f_border
ws['C17'] = round(reporting_period_data['offpeaks'][0], 2)
pie = PieChart()
pie.title = name + '分时用电成本'
labels = Reference(ws, min_col=2, min_row=14, max_row=17)
pie_data = Reference(ws, min_col=3, min_row=13, max_row=17)
pie.add_data(pie_data, titles_from_data=True)
pie.set_categories(labels)
pie.height = 7.25
pie.width = 9
s1 = pie.series[0]
s1.dLbls = DataLabelList()
s1.dLbls.showCatName = False
s1.dLbls.showVal = True
s1.dLbls.showPercent = True
ws.add_chart(pie, "D13")
else:
for i in range(12, 18 + 1):
ws.row_dimensions[i].height = 0.1
##################################
current_row_number = 19
has_child_flag = True
if "child_space" not in report.keys() or "energy_category_names" not in report['child_space'].keys() or \
len(report['child_space']["energy_category_names"]) == 0 \
or 'child_space_names_array' not in report['child_space'].keys() \
or report['child_space']['energy_category_names'] is None \
or len(report['child_space']['child_space_names_array']) == 0 \
or len(report['child_space']['child_space_names_array'][0]) == 0:
has_child_flag = False
if has_child_flag:
child = report['child_space']
ws['B' + str(current_row_number)].font = title_font
ws['B' + str(current_row_number)] = name + ' 子空间数据'
current_row_number += 1
table_start_row_number = current_row_number
ws.row_dimensions[current_row_number].height = 60
ws['B' + str(current_row_number)].fill = table_fill
ws['B' + str(current_row_number)].border = f_border
ca_len = len(child['energy_category_names'])
col = ''
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 = 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)] = child['energy_category_names'][
i] + ' (' + child['units'][i] + ')'
col = chr(ord(col) + 1)
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
)] = '总计 (' + report['reporting_period']['total_unit'] + ')'
space_len = len(child['child_space_names_array'][0])
for i in range(0, space_len):
current_row_number += 1
row = str(current_row_number)
ws['B' + row].font = name_font
ws['B' + row].alignment = c_c_alignment
ws['B' + row] = child['child_space_names_array'][0][i]
ws['B' + row].border = f_border
col = ''
every_day_sum = 0
for j in range(0, ca_len):
col = chr(ord('C') + j)
ws[col + row].font = name_font
ws[col + row].alignment = c_c_alignment
every_day_sum += child['subtotals_array'][j][i]
ws[col + row] = round(child['subtotals_array'][j][i], 2)
ws[col + row].border = f_border
col = chr(ord(col) + 1)
ws[col + row].font = name_font
ws[col + row].alignment = c_c_alignment
ws[col + row] = round(every_day_sum, 2)
ws[col + row].border = f_border
table_end_row_number = current_row_number
current_row_number += 1
chart_start_row_number = current_row_number
# Pie
for i in range(0, ca_len):
pie = PieChart()
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 + i,
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 = 6.6
pie.width = 8
pie.title = ws.cell(column=3 + i, row=table_start_row_number).value
s1 = pie.series[0]
s1.dLbls = DataLabelList()
s1.dLbls.showCatName = False
s1.dLbls.showVal = True
s1.dLbls.showPercent = True
chart_cell = ''
if i % 2 == 0:
chart_cell = 'B' + str(chart_start_row_number)
else:
chart_cell = 'E' + str(chart_start_row_number)
chart_start_row_number += 5
ws.add_chart(pie, chart_cell)
# chart_col = chr(ord('B') + 2 * i)
# chart_cell = chart_col + str(current_row_number)
# ws.add_chart(pie, chart_cell)
current_row_number = chart_start_row_number
if ca_len % 2 == 1:
current_row_number += 5
# else:
# for i in range(19, 36 + 1):
# current_row_number = 36
# ws.row_dimensions[i].height = 0.1
current_row_number += 1
#############################################
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 = (current_row_number + 1) + ca_len * 6
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['B' + str(current_row_number)].font = title_font
ws['B' + str(current_row_number)] = name + ' 详细数据'
ws.row_dimensions[table_row].height = 60
ws['B' + str(table_row)].fill = table_fill
ws['B' + str(table_row)].font = title_font
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)
if has_data:
for i in range(0, len(time)):
col = 'B'
row = str(table_row + 1 + 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):
col = chr(ord('C') + i)
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'][
i] + " (" + reporting_period_data['units'][i] + ")"
ws[col + str(table_row)].border = f_border
# 39 data
time = times[i]
time_len = len(time)
for j in range(0, time_len):
row = str(table_row + 1 + j)
ws[col + row].font = title_font
ws[col + row].alignment = c_c_alignment
ws[col + row] = round(
reporting_period_data['values'][i][j], 2)
ws[col + row].border = f_border
line = LineChart()
line.title = \
'报告期成本 - ' + reporting_period_data['names'][i] + " (" + reporting_period_data['units'][i] + ")"
labels = Reference(ws,
min_col=2,
min_row=table_row + 1,
max_row=max_row)
line_data = Reference(ws,
min_col=3 + i,
min_row=table_row,
max_row=max_row)
line.add_data(line_data, titles_from_data=True)
line.set_categories(labels)
line_data = line.series[0]
line_data.marker.symbol = "circle"
line_data.smooth = True
line.x_axis.crosses = 'min'
line.height = 8.25
line.width = 24
line.dLbls = DataLabelList()
line.dLbls.dLblPos = 't'
line.dLbls.showVal = True
line.dLbls.showPercent = False
chart_col = 'B'
chart_cell = chart_col + str(current_row_number + 1 + 6 * i)
ws.add_chart(line, chart_cell)
row = str(max_row + 1)
ws['B' + row].font = title_font
ws['B' + row].alignment = c_c_alignment
ws['B' + row] = '小计'
ws['B' + row].border = f_border
col = ''
for i in range(0, ca_len):
col = chr(ord('C') + i)
row = str(max_row + 1)
ws[col + row].font = title_font
ws[col + row].alignment = c_c_alignment
ws[col + row] = round(reporting_period_data['subtotals'][i], 2)
ws[col + row].border = f_border
col = chr(ord(col) + 1)
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
)] = '总计 (' + report['reporting_period']['total_unit'] + ')'
ws[col + str(table_row)].border = f_border
total_sum = 0
for j in range(0, len(time)):
row = str(table_row + 1 + j)
ws[col + row].font = title_font
ws[col + row].alignment = c_c_alignment
every_day_sum = reporting_period_values_every_day_sum(
reporting_period_data, j, ca_len)
total_sum += every_day_sum
ws[col + row] = round(every_day_sum, 2)
ws[col + row].border = f_border
row = str(table_row + 1 + len(time))
ws[col + row].font = title_font
ws[col + row].alignment = c_c_alignment
ws[col + row] = round(total_sum, 2)
ws[col + row].border = f_border
else:
for i in range(37, 69 + 1):
ws.row_dimensions[i].height = 0.1
filename = str(uuid.uuid4()) + '.xlsx'
wb.save(filename)
return filename
sheet.cell(row=row_index, column=19).value = one_price * one_num
wb.save('E:\\study\\全栈数据分析\\0812\\excle/案例.xlsx')
#根据各个省的订单量绘制柱状图
from openpyxl import Workbook
from openpyxl import load_workbook
from openpyxl.chart import BarChart, Reference
wb = load_workbook('E:\\study\\全栈数据分析\\0812\\excle/案例.xlsx')
sheet = wb['各省销量']
#创建柱状图对象
bar_chart = BarChart()
labels = Reference(sheet, min_row=2, max_row=32, min_col=1, max_col=1)
data = Reference(sheet, min_row=2, max_row=32, min_col=2, max_col=2)
bar_chart.add_data(data)
bar_chart.set_categories(labels) #设置图例
sheet.add_chart(bar_chart, 'D10')
wb.save('E:\\study\\全栈数据分析\\0812\\excle/案例.xlsx')
#将本地图片添加到指定位置
from openpyxl.drawing.image import Image
wb = load_workbook('E:\\study\\全栈数据分析\\0812\\excle/案例.xlsx')
wb.create_sheet('image')
sheet = wb['image']
img = Image('D:/BaiduNetdiskDownload/壁纸/1418704224888.jpeg')
def graph_excel_means(lst_year, title_excel, yaxis_title_excel):
"""
В итоговом файле xlsx, создает диаграмму\n
lst_year - для количества столбцов, по которым строится график\n
title_excel - название диаграммы\n
yaxis_title_excel - название оси Y\n
"""
from openpyxl import Workbook
from openpyxl.chart import (
LineChart,
ScatterChart,
Reference,
Series,
)
wb = openpyxl.load_workbook(
f'{path_project}{filename_means_inter}/result_{filename_means_inter}.xlsx'
)
ws = wb['all']
chart = ScatterChart()
chart.title = title_excel
# chart.style = 2
chart.x_axis.title = 'Года'
chart.y_axis.title = yaxis_title_excel
# chart.x_axis.scaling.min = 0
# chart.y_axis.scaling.min = 1
# # chart.x_axis.scaling.max = 11
# chart.y_axis.scaling.max = 2.7
#========================================================
# Расчет максимальных и минимальных значений для осей
df_excel = pd.DataFrame(ws.values)
num_of_cols = df_excel.iloc[:, 1:].shape[1]
lst_max = []
lst_min = []
for i in range(1, 1 + num_of_cols):
max_of_series = df_excel.iloc[1:, i].max()
min_of_series = df_excel.iloc[1:, i].min()
lst_max.append(max_of_series)
lst_min.append(min_of_series)
max_y = np.round(max(lst_max) + 1)
min_y = np.round(min(lst_min) - 1)
max_x = max(lst_year) + 1
min_x = min(lst_year) - 1
#=============================================================
num = 2
max_rows = len(lst_year)
for i in range(1, 3):
if i == 1:
dct_means_lvl = dct_means_1
else:
dct_means_lvl = dct_means_2
for k, v in dct_means_lvl.items():
xvalues = Reference(ws, min_col=1, min_row=2, max_row=1 + max_rows)
values = Reference(ws,
min_col=num,
min_row=1,
max_row=1 + max_rows)
# print('values')
# print(values)
num += 1
series = Series(values, xvalues, title_from_data=True)
chart.series.append(series)
# Установление макс и мин значений осей
chart.y_axis.scaling.max = max_y
chart.y_axis.scaling.min = min_y
chart.x_axis.scaling.max = max_x
chart.x_axis.scaling.min = min_x
ws.add_chart(chart, "K02")
wb.save(
f'{path_project}{filename_means_inter}/result_{filename_means_inter}.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 = 102
for i in range(2, 2000 + 1):
ws.row_dimensions[i].height = 42
# Col width
ws.column_dimensions['A'].width = 1.5
ws.column_dimensions['B'].width = 25.0
for i in range(ord('C'), ord('L')):
ws.column_dimensions[chr(i)].width = 15.0
# Font
name_font = Font(name='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")
img.width = img.width * 0.85
img.height = img.height * 0.85
# img = Image("myems.png")
ws.add_image(img, 'B1')
# Title
ws.row_dimensions[3].height = 60
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
##################################
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.row_dimensions[current_row_number].height = 75.0
ws['B' + str(current_row_number)].fill = table_fill
ws['B' + str(current_row_number)].border = f_border
col = 'C'
for i in range(0, ca_len):
ws[col + str(current_row_number)].fill = table_fill
ws[col + str(current_row_number)].font = name_font
ws[col + str(current_row_number)].alignment = c_c_alignment
ws[col + str(current_row_number)].border = f_border
ws[col + str(current_row_number)] = \
reporting_period_data['names'][i] + " (基线-实际) (" + reporting_period_data['units'][i] + ")"
col = chr(ord(col) + 1)
ws[col + str(current_row_number)].fill = table_fill
ws[col + str(current_row_number)].font = name_font
ws[col + str(current_row_number)].alignment = c_c_alignment
ws[col + str(current_row_number)].border = f_border
ws[col + str(current_row_number)] = '吨标准煤 (基线-实际) (TCE)'
col = chr(ord(col) + 1)
ws[col + str(current_row_number)].fill = table_fill
ws[col + str(current_row_number)].font = name_font
ws[col + str(current_row_number)].alignment = c_c_alignment
ws[col + str(current_row_number)].border = f_border
ws[col + str(current_row_number)] = '吨二氧化碳排放 (基线-实际) (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'] / 1000, 2)
col = chr(ord(col) + 1)
ws[col + str(current_row_number)].font = name_font
ws[col + str(current_row_number)].alignment = c_c_alignment
ws[col + str(current_row_number)].border = f_border
ws[col + str(current_row_number)] = round(
reporting_period_data['total_in_kgco2e_saving'] / 1000, 2)
col = chr(ord(col) + 1)
current_row_number += 1
ws['B' + str(current_row_number)].font = title_font
ws['B' + str(current_row_number)].alignment = c_c_alignment
ws['B' + str(current_row_number)].border = f_border
ws['B' + str(current_row_number)] = '单位面积值'
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'] /
1000, 2)
col = chr(ord(col) + 1)
ws[col + str(current_row_number)].font = name_font
ws[col + str(current_row_number)].alignment = c_c_alignment
ws[col + str(current_row_number)].border = f_border
ws[col + str(current_row_number)] = round(
reporting_period_data['total_in_kgce_per_unit_area_saving'] / 1000,
2)
col = chr(ord(col) + 1)
current_row_number += 1
ws['B' + str(current_row_number)].font = title_font
ws['B' + str(current_row_number)].alignment = c_c_alignment
ws['B' + str(current_row_number)].border = f_border
ws['B' + str(current_row_number)] = '环比'
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.row_dimensions[current_row_number].height = 60
ws['B' + str(current_row_number)].fill = table_fill
ws['B' + str(current_row_number)].border = f_border
ws['C' + str(current_row_number)].fill = table_fill
ws['C' + str(current_row_number)].font = name_font
ws['C' + str(current_row_number)].alignment = c_c_alignment
ws['C' + str(current_row_number)].border = f_border
ws['C' + str(current_row_number)] = '节约'
ws['D' + str(current_row_number)].fill = table_fill
ws['D' + str(current_row_number)].font = name_font
ws['D' + str(current_row_number)].alignment = c_c_alignment
ws['D' + str(current_row_number)].border = f_border
ws['D' + str(current_row_number)] = '吨标准煤(TCE) 节约占比'
current_row_number += 1
subtotals_in_kgce_saving_sum = sum_list(
reporting_period_data['subtotals_in_kgce_saving'])
for i in range(0, ca_len):
ws['B' + str(current_row_number)].font = title_font
ws['B' + str(current_row_number)].alignment = c_c_alignment
ws['B' + str(current_row_number)].border = f_border
ws['B' +
str(current_row_number)] = reporting_period_data['names'][i]
ws['C' + str(current_row_number)].font = name_font
ws['C' + str(current_row_number)].alignment = c_c_alignment
ws['C' + str(current_row_number)].border = f_border
ws['C' + str(current_row_number)] = round(
reporting_period_data['subtotals_in_kgce_saving'][i] / 1000, 3)
ws['D' + str(current_row_number)].font = name_font
ws['D' + str(current_row_number)].alignment = c_c_alignment
ws['D' + str(current_row_number)].border = f_border
ws['D' + str(current_row_number)] = str(round(reporting_period_data['subtotals_in_kgce_saving'][i] /
subtotals_in_kgce_saving_sum * 100, 2)) + '%'\
if abs(subtotals_in_kgce_saving_sum) > 0 else '-'
current_row_number += 1
table_end_row_number = current_row_number - 1
if ca_len < 4:
current_row_number = current_row_number - ca_len + 4
current_row_number += 1
pie = PieChart()
pie.title = name + ' 吨标准煤(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 = 7.25
pie.width = 9
s1 = pie.series[0]
s1.dLbls = DataLabelList()
s1.dLbls.showCatName = False
s1.dLbls.showVal = True
s1.dLbls.showPercent = True
ws.add_chart(pie, 'E' + str(chart_start_row_number))
ws['B' + str(current_row_number)].font = title_font
ws['B' + str(current_row_number)] = name + ' 吨二氧化碳排放(TCO2E)占比'
current_row_number += 1
table_start_row_number = current_row_number
chart_start_row_number = current_row_number
ws.row_dimensions[current_row_number].height = 60
ws['B' + str(current_row_number)].fill = table_fill
ws['B' + str(current_row_number)].border = f_border
ws['C' + str(current_row_number)].fill = table_fill
ws['C' + str(current_row_number)].font = name_font
ws['C' + str(current_row_number)].alignment = c_c_alignment
ws['C' + str(current_row_number)].border = f_border
ws['C' + str(current_row_number)] = '节约'
ws['D' + str(current_row_number)].fill = table_fill
ws['D' + str(current_row_number)].font = name_font
ws['D' + str(current_row_number)].alignment = c_c_alignment
ws['D' + str(current_row_number)].border = f_border
ws['D' + str(current_row_number)] = '吨二氧化碳排放(TCO2E) 节约占比'
current_row_number += 1
subtotals_in_kgco2e_saving_sum = sum_list(
reporting_period_data['subtotals_in_kgco2e_saving'])
for i in range(0, ca_len):
ws['B' + str(current_row_number)].font = title_font
ws['B' + str(current_row_number)].alignment = c_c_alignment
ws['B' + str(current_row_number)].border = f_border
ws['B' +
str(current_row_number)] = reporting_period_data['names'][i]
ws['C' + str(current_row_number)].font = name_font
ws['C' + str(current_row_number)].alignment = c_c_alignment
ws['C' + str(current_row_number)].border = f_border
ws['C' + str(current_row_number)] = round(
reporting_period_data['subtotals_in_kgco2e_saving'][i] / 1000,
3)
ws['D' + str(current_row_number)].font = name_font
ws['D' + str(current_row_number)].alignment = c_c_alignment
ws['D' + str(current_row_number)].border = f_border
ws['D' + str(current_row_number)] = str(round(reporting_period_data['subtotals_in_kgco2e_saving'][i] /
subtotals_in_kgco2e_saving_sum * 100, 2)) + '%'\
if abs(subtotals_in_kgco2e_saving_sum) > 0 else '-'
current_row_number += 1
table_end_row_number = current_row_number - 1
if ca_len < 4:
current_row_number = current_row_number - ca_len + 4
current_row_number += 1
pie = PieChart()
pie.title = name + ' 吨二氧化碳排放(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 = 7.25
pie.width = 9
s1 = pie.series[0]
s1.dLbls = DataLabelList()
s1.dLbls.showCatName = False
s1.dLbls.showVal = True
s1.dLbls.showPercent = True
ws.add_chart(pie, 'E' + str(chart_start_row_number))
#############################################
has_values_saving_data = True
has_timestamps_data = True
if 'values_saving' not in reporting_period_data.keys() or \
reporting_period_data['values_saving'] is None or \
len(reporting_period_data['values_saving']) == 0:
has_values_saving_data = False
if 'timestamps' not in reporting_period_data.keys() or \
reporting_period_data['timestamps'] is None or \
len(reporting_period_data['timestamps']) == 0 or \
len(reporting_period_data['timestamps'][0]) == 0:
has_timestamps_data = False
if has_values_saving_data and has_timestamps_data:
ca_len = len(reporting_period_data['names'])
time = reporting_period_data['timestamps'][0]
real_timestamps_len = timestamps_data_not_equal_0(
report['parameters']['timestamps'])
ws['B' + str(current_row_number)].font = title_font
ws['B' + str(current_row_number)] = name + ' 详细数据'
current_row_number += 1
chart_start_row_number = current_row_number
current_row_number += ca_len * 6 + real_timestamps_len * 7 + 1
table_start_row_number = current_row_number
ws.row_dimensions[current_row_number].height = 60
ws['B' + str(current_row_number)].fill = table_fill
ws['B' + str(current_row_number)].font = title_font
ws['B' + str(current_row_number)].alignment = c_c_alignment
ws['B' + str(current_row_number)].border = f_border
ws['B' + str(current_row_number)] = '日期时间'
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):
line = LineChart()
line.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)
line_data = Reference(ws,
min_col=3 + i,
min_row=table_start_row_number,
max_row=table_end_row_number)
line.add_data(line_data, titles_from_data=True)
line.set_categories(labels)
line_data = line.series[0]
line_data.marker.symbol = "circle"
line_data.smooth = True
line.x_axis.crosses = 'min'
line.height = 8.25
line.width = format_time_width_number * len(time) if len(
time) > min_len_number else min_width_number
if line.width > 24:
line.width = 24
line.dLbls = DataLabelList()
line.dLbls.dLblPos = 't'
line.dLbls.showVal = True
line.dLbls.showPercent = False
chart_col = 'B'
chart_cell = chart_col + str(chart_start_row_number)
chart_start_row_number += 6
ws.add_chart(line, chart_cell)
##########################################
current_sheet_parameters_row_number = chart_start_row_number + 1
has_parameters_names_and_timestamps_and_values_data = True
if 'parameters' not in report.keys() or \
report['parameters'] is None or \
'names' not in report['parameters'].keys() or \
report['parameters']['names'] is None or \
len(report['parameters']['names']) == 0 or \
'timestamps' not in report['parameters'].keys() or \
report['parameters']['timestamps'] is None or \
len(report['parameters']['timestamps']) == 0 or \
'values' not in report['parameters'].keys() or \
report['parameters']['values'] is None or \
len(report['parameters']['values']) == 0 or \
timestamps_data_all_equal_0(report['parameters']['timestamps']):
has_parameters_names_and_timestamps_and_values_data = False
if has_parameters_names_and_timestamps_and_values_data:
###############################
# new worksheet
###############################
parameters_data = report['parameters']
parameters_names_len = len(parameters_data['names'])
parameters_ws = wb.create_sheet('相关参数')
parameters_timestamps_data_max_len = \
get_parameters_timestamps_lists_max_len(list(parameters_data['timestamps']))
# Row height
parameters_ws.row_dimensions[1].height = 102
for i in range(2, 7 + 1):
parameters_ws.row_dimensions[i].height = 42
for i in range(8, parameters_timestamps_data_max_len + 10):
parameters_ws.row_dimensions[i].height = 60
# Col width
parameters_ws.column_dimensions['A'].width = 1.5
parameters_ws.column_dimensions['B'].width = 25.0
for i in range(3, 12 + parameters_names_len * 3):
parameters_ws.column_dimensions[format_cell.get_column_letter(
i)].width = 15.0
# Img
img = Image("excelexporters/myems.png")
img.width = img.width * 0.85
img.height = img.height * 0.85
# img = Image("myems.png")
parameters_ws.add_image(img, 'B1')
# Title
parameters_ws.row_dimensions[3].height = 60
parameters_ws['B3'].font = name_font
parameters_ws['B3'].alignment = b_r_alignment
parameters_ws['B3'] = 'Name:'
parameters_ws['C3'].border = b_border
parameters_ws['C3'].alignment = b_c_alignment
parameters_ws['C3'].font = name_font
parameters_ws['C3'] = name
parameters_ws['D3'].font = name_font
parameters_ws['D3'].alignment = b_r_alignment
parameters_ws['D3'] = 'Period:'
parameters_ws['E3'].border = b_border
parameters_ws['E3'].alignment = b_c_alignment
parameters_ws['E3'].font = name_font
parameters_ws['E3'] = period_type
parameters_ws['F3'].font = name_font
parameters_ws['F3'].alignment = b_r_alignment
parameters_ws['F3'] = 'Date:'
parameters_ws['G3'].border = b_border
parameters_ws['G3'].alignment = b_c_alignment
parameters_ws['G3'].font = name_font
parameters_ws[
'G3'] = reporting_start_datetime_local + "__" + reporting_end_datetime_local
parameters_ws.merge_cells("G3:H3")
parameters_ws_current_row_number = 6
parameters_ws['B' +
str(parameters_ws_current_row_number)].font = title_font
parameters_ws['B' +
str(parameters_ws_current_row_number)] = name + ' 相关参数'
parameters_ws_current_row_number += 1
parameters_table_start_row_number = parameters_ws_current_row_number
parameters_ws.row_dimensions[
parameters_ws_current_row_number].height = 80
parameters_ws_current_row_number += 1
table_current_col_number = 2
for i in range(0, parameters_names_len):
if len(parameters_data['timestamps'][i]) == 0:
continue
col = format_cell.get_column_letter(table_current_col_number)
parameters_ws[col + str(parameters_ws_current_row_number -
1)].fill = table_fill
parameters_ws[col + str(parameters_ws_current_row_number -
1)].border = f_border
col = format_cell.get_column_letter(table_current_col_number + 1)
parameters_ws[col + str(parameters_ws_current_row_number -
1)].fill = table_fill
parameters_ws[col + str(parameters_ws_current_row_number -
1)].border = f_border
parameters_ws[col + str(parameters_ws_current_row_number -
1)].font = name_font
parameters_ws[col + str(parameters_ws_current_row_number -
1)].alignment = c_c_alignment
parameters_ws[col + str(parameters_ws_current_row_number -
1)] = parameters_data['names'][i]
table_current_row_number = parameters_ws_current_row_number
for j, value in enumerate(list(parameters_data['timestamps'][i])):
col = format_cell.get_column_letter(table_current_col_number)
parameters_ws[col +
str(table_current_row_number)].border = f_border
parameters_ws[col +
str(table_current_row_number)].font = title_font
parameters_ws[
col +
str(table_current_row_number)].alignment = c_c_alignment
parameters_ws[col + str(table_current_row_number)] = value
col = format_cell.get_column_letter(table_current_col_number +
1)
parameters_ws[col +
str(table_current_row_number)].border = f_border
parameters_ws[col +
str(table_current_row_number)].font = title_font
parameters_ws[
col +
str(table_current_row_number)].alignment = c_c_alignment
parameters_ws[col + str(table_current_row_number)] = round(
parameters_data['values'][i][j], 2)
table_current_row_number += 1
table_current_col_number = table_current_col_number + 3
########################################################
# parameters chart and parameters table
########################################################
ws['B' + str(current_sheet_parameters_row_number)].font = title_font
ws['B' + str(current_sheet_parameters_row_number)] = name + ' 相关参数'
current_sheet_parameters_row_number += 1
chart_start_row_number = current_sheet_parameters_row_number
col_index = 0
for i in range(0, parameters_names_len):
if len(parameters_data['timestamps'][i]) == 0:
continue
line = LineChart()
data_col = 3 + col_index * 3
labels_col = 2 + col_index * 3
col_index += 1
line.title = '相关参数 - ' + \
parameters_ws.cell(row=parameters_table_start_row_number, column=data_col).value
labels = Reference(parameters_ws,
min_col=labels_col,
min_row=parameters_table_start_row_number + 1,
max_row=(len(parameters_data['timestamps'][i]) +
parameters_table_start_row_number))
line_data = Reference(
parameters_ws,
min_col=data_col,
min_row=parameters_table_start_row_number,
max_row=(len(parameters_data['timestamps'][i]) +
parameters_table_start_row_number))
line.add_data(line_data, titles_from_data=True)
line.set_categories(labels)
line_data = line.series[0]
line_data.marker.symbol = "circle"
line_data.smooth = True
line.x_axis.crosses = 'min'
line.height = 8.25
line.width = 24
line.dLbls = DataLabelList()
line.dLbls.dLblPos = 't'
line.dLbls.showVal = False
line.dLbls.showPercent = False
chart_col = 'B'
chart_cell = chart_col + str(chart_start_row_number)
chart_start_row_number += 6
ws.add_chart(line, chart_cell)
current_sheet_parameters_row_number = chart_start_row_number
current_sheet_parameters_row_number += 1
##########################################
filename = str(uuid.uuid4()) + '.xlsx'
wb.save(filename)
return filename
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 = 102
for i in range(2, 2000 + 1):
ws.row_dimensions[i].height = 42
# Col width
ws.column_dimensions['A'].width = 1.5
ws.column_dimensions['B'].width = 25.0
for i in range(ord('C'), ord('L')):
ws.column_dimensions[chr(i)].width = 15.0
# Font
name_font = Font(name='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)
# 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~ca_len table_data
#################################################
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 + ' 统计分析'
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)].number_format = '0.00'
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)].number_format = '0.00'
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)].number_format = '0.00'
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)].number_format = '0.00'
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)].number_format = '0.00'
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)].number_format = '0.00'
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
#################################################
# Second: 报告期消耗
# 9 + ca_len * 2: title
# 10 + ca_len * 2: table title
# per_unit_area_start_row_number + 2 ~ per_unit_area_start_row_number + 2 + ca_len : table_data
#################################################
if has_energy_data_flag:
names = reporting_period_data['names']
ca_len = len(names)
per_unit_area_start_row_number = 9 + ca_len * 2
ws['B' + str(per_unit_area_start_row_number)].font = title_font
ws['B' + str(per_unit_area_start_row_number)] = name + ' 单位面积值'
ws['D' + str(per_unit_area_start_row_number)].font = title_font
ws['D' + str(per_unit_area_start_row_number)] = str(
report['space']['area']) + 'M²'
category = reporting_period_data['names']
# table_title
ws['B' + str(per_unit_area_start_row_number + 1)].fill = table_fill
ws['B' + str(per_unit_area_start_row_number + 1)].font = title_font
ws['B' +
str(per_unit_area_start_row_number + 1)].alignment = c_c_alignment
ws['B' + str(per_unit_area_start_row_number + 1)] = '报告期'
ws['B' + str(per_unit_area_start_row_number + 1)].border = f_border
ws['C' + str(per_unit_area_start_row_number + 1)].font = title_font
ws['C' +
str(per_unit_area_start_row_number + 1)].alignment = c_c_alignment
ws['C' + str(per_unit_area_start_row_number + 1)] = '算术平均数'
ws['C' + str(per_unit_area_start_row_number + 1)].border = f_border
ws['D' + str(per_unit_area_start_row_number + 1)].font = title_font
ws['D' +
str(per_unit_area_start_row_number + 1)].alignment = c_c_alignment
ws['D' + str(per_unit_area_start_row_number + 1)] = '中位数'
ws['D' + str(per_unit_area_start_row_number + 1)].border = f_border
ws['E' + str(per_unit_area_start_row_number + 1)].font = title_font
ws['E' +
str(per_unit_area_start_row_number + 1)].alignment = c_c_alignment
ws['E' + str(per_unit_area_start_row_number + 1)] = '最小值'
ws['E' + str(per_unit_area_start_row_number + 1)].border = f_border
ws['F' + str(per_unit_area_start_row_number + 1)].font = title_font
ws['F' +
str(per_unit_area_start_row_number + 1)].alignment = c_c_alignment
ws['F' + str(per_unit_area_start_row_number + 1)] = '最大值'
ws['F' + str(per_unit_area_start_row_number + 1)].border = f_border
ws['G' + str(per_unit_area_start_row_number + 1)].font = title_font
ws['G' +
str(per_unit_area_start_row_number + 1)].alignment = c_c_alignment
ws['G' + str(per_unit_area_start_row_number + 1)] = '样本标准差'
ws['G' + str(per_unit_area_start_row_number + 1)].border = f_border
ws['H' + str(per_unit_area_start_row_number + 1)].font = title_font
ws['H' +
str(per_unit_area_start_row_number + 1)].alignment = c_c_alignment
ws['H' + str(per_unit_area_start_row_number + 1)] = '样本方差'
ws['H' + str(per_unit_area_start_row_number + 1)].border = f_border
# table_data
for i, value in enumerate(category):
row_data = per_unit_area_start_row_number + 2 + i
ws['B' + str(row_data)].font = name_font
ws['B' + str(row_data)].alignment = c_c_alignment
ws['B' + str(row_data)] = reporting_period_data['names'][
i] + " (" + reporting_period_data['units'][i] + "/M²)"
ws['B' + str(row_data)].border = f_border
ws['C' + str(row_data)].font = name_font
ws['C' + str(row_data)].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_data)] = round(
reporting_period_data['means_per_unit_area'][i], 2)
ws['C' + str(row_data)].border = f_border
ws['C' + str(row_data)].number_format = '0.00'
ws['D' + str(row_data)].font = name_font
ws['D' + str(row_data)].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_data)] = round(
reporting_period_data['medians_per_unit_area'][i], 2)
ws['D' + str(row_data)].border = f_border
ws['D' + str(row_data)].number_format = '0.00'
ws['E' + str(row_data)].font = name_font
ws['E' + str(row_data)].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_data)] = round(
reporting_period_data['minimums_per_unit_area'][i], 2)
ws['E' + str(row_data)].border = f_border
ws['E' + str(row_data)].number_format = '0.00'
ws['F' + str(row_data)].font = name_font
ws['F' + str(row_data)].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_data)] = round(
reporting_period_data['maximums_per_unit_area'][i], 2)
ws['F' + str(row_data)].border = f_border
ws['F' + str(row_data)].number_format = '0.00'
ws['G' + str(row_data)].font = name_font
ws['G' + str(row_data)].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_data)] = round(
reporting_period_data['stdevs_per_unit_area'][i], 2)
ws['G' + str(row_data)].border = f_border
ws['G' + str(row_data)].number_format = '0.00'
ws['H' + str(row_data)].font = name_font
ws['H' + str(row_data)].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_data)] = round(
reporting_period_data['variances_per_unit_area'][i], 2)
ws['H' + str(row_data)].border = f_border
ws['H' + str(row_data)].number_format = '0.00'
########################################################
# Third: 详细数据
# analysis_end_row_number~ analysis_end_row_number+time_len: line
# analysis_end_row_number+1+line_charts_row_number: table title
# i + analysis_end_row_number + 2 + 10 * ca_len~: table_data
########################################################
has_timestamps_flag = True
if "timestamps" not in reporting_period_data.keys() or \
reporting_period_data['timestamps'] is None or \
len(reporting_period_data['timestamps']) == 0:
has_timestamps_flag = False
if has_timestamps_flag:
timestamps = reporting_period_data['timestamps'][0]
values = reporting_period_data['values']
names = reporting_period_data['names']
ca_len = len(names)
time_len = len(timestamps)
real_timestamps_len = timestamps_data_not_equal_0(
report['parameters']['timestamps'])
# title
line_charts_row_number = 6 * ca_len + real_timestamps_len * 7
analysis_end_row_number = 12 + 3 * ca_len
detailed_start_row_number = analysis_end_row_number + line_charts_row_number + 1
ws['B' + str(detailed_start_row_number)].font = title_font
ws['B' + str(detailed_start_row_number)] = name + ' 详细数据'
# table_title
ws['B' + str(detailed_start_row_number + 1)].fill = table_fill
ws['B' + str(detailed_start_row_number + 1)].font = name_font
ws['B' + str(detailed_start_row_number + 1)].alignment = c_c_alignment
ws['B' + str(detailed_start_row_number + 1)] = "时间"
ws['B' + str(detailed_start_row_number + 1)].border = f_border
for i in range(0, ca_len):
col = chr(ord('C') + i)
ws[col + str(detailed_start_row_number + 1)].font = name_font
ws[col +
str(detailed_start_row_number + 1)].alignment = c_c_alignment
ws[col + str(detailed_start_row_number + 1)] = names[
i] + " - (" + reporting_period_data['units'][i] + ")"
ws[col + str(detailed_start_row_number + 1)].border = f_border
# table_date
for i in range(0, time_len):
rows = i + detailed_start_row_number + 2
ws['B' + str(rows)].font = name_font
ws['B' + str(rows)].alignment = c_c_alignment
ws['B' + str(rows)] = timestamps[i]
ws['B' + str(rows)].border = f_border
for index in range(0, ca_len):
col = chr(ord('C') + index)
ws[col + str(rows)].font = name_font
ws[col + str(rows)].alignment = c_c_alignment
ws[col + str(rows)] = round(values[index][i], 2)
ws[col + str(rows)].number_format = '0.00'
ws[col + str(rows)].border = f_border
# 小计
row_subtotals = detailed_start_row_number + 2 + time_len
ws['B' + str(row_subtotals)].font = name_font
ws['B' + str(row_subtotals)].alignment = c_c_alignment
ws['B' + str(row_subtotals)] = "小计"
ws['B' + str(row_subtotals)].border = f_border
for i in range(0, ca_len):
col = chr(ord('C') + i)
ws[col + str(row_subtotals)].font = name_font
ws[col + str(row_subtotals)].alignment = c_c_alignment
ws[col + str(row_subtotals)] = round(
reporting_period_data['subtotals'][i], 2)
ws[col + str(row_subtotals)].border = f_border
ws[col + str(row_subtotals)].number_format = '0.00'
########################################################
# third: LineChart
# LineChart requires data from the detailed data table in the Excel file
# so print the detailed data table first and then print LineChart
########################################################
for i in range(0, ca_len):
line = LineChart()
line.title = "报告期消耗" + " - " + names[
i] + "(" + reporting_period_data['units'][i] + ")"
line.style = 10
line.x_axis.majorTickMark = 'in'
line.y_axis.majorTickMark = 'in'
line.smooth = True
line.x_axis.crosses = 'min'
line.height = 8.25
line.width = 24
line.dLbls = DataLabelList()
line.dLbls.dLblPos = 't'
line.dLbls.showVal = True
times = Reference(ws,
min_col=2,
min_row=detailed_start_row_number + 2,
max_row=detailed_start_row_number + 2 + time_len)
line_data = Reference(ws,
min_col=3 + i,
min_row=detailed_start_row_number + 1,
max_row=detailed_start_row_number + 1 +
time_len)
line.add_data(line_data, titles_from_data=True)
line.set_categories(times)
ser = line.series[0]
ser.marker.symbol = "diamond"
ser.marker.size = 5
ws.add_chart(line, 'B' + str(analysis_end_row_number + 6 * i))
##########################################
current_sheet_parameters_row_number = analysis_end_row_number + ca_len * 6 + 1
has_parameters_names_and_timestamps_and_values_data = True
if 'parameters' not in report.keys() or \
report['parameters'] is None or \
'names' not in report['parameters'].keys() or \
report['parameters']['names'] is None or \
len(report['parameters']['names']) == 0 or \
'timestamps' not in report['parameters'].keys() or \
report['parameters']['timestamps'] is None or \
len(report['parameters']['timestamps']) == 0 or \
'values' not in report['parameters'].keys() or \
report['parameters']['values'] is None or \
len(report['parameters']['values']) == 0 or \
timestamps_data_all_equal_0(report['parameters']['timestamps']):
has_parameters_names_and_timestamps_and_values_data = False
if has_parameters_names_and_timestamps_and_values_data:
###############################
# new worksheet
###############################
parameters_data = report['parameters']
parameters_names_len = len(parameters_data['names'])
parameters_ws = wb.create_sheet('相关参数')
parameters_timestamps_data_max_len = \
get_parameters_timestamps_lists_max_len(list(parameters_data['timestamps']))
# Row height
parameters_ws.row_dimensions[1].height = 102
for i in range(2, 7 + 1):
parameters_ws.row_dimensions[i].height = 42
for i in range(8, parameters_timestamps_data_max_len + 10):
parameters_ws.row_dimensions[i].height = 60
# Col width
parameters_ws.column_dimensions['A'].width = 1.5
parameters_ws.column_dimensions['B'].width = 25.0
for i in range(3, 12 + parameters_names_len * 3):
parameters_ws.column_dimensions[format_cell.get_column_letter(
i)].width = 15.0
# Img
img = Image("excelexporters/myems.png")
img.width = img.width * 0.85
img.height = img.height * 0.85
# img = Image("myems.png")
parameters_ws.add_image(img, 'B1')
# Title
parameters_ws.row_dimensions[3].height = 60
parameters_ws['B3'].font = name_font
parameters_ws['B3'].alignment = b_r_alignment
parameters_ws['B3'] = 'Name:'
parameters_ws['C3'].border = b_border
parameters_ws['C3'].alignment = b_c_alignment
parameters_ws['C3'].font = name_font
parameters_ws['C3'] = name
parameters_ws['D3'].font = name_font
parameters_ws['D3'].alignment = b_r_alignment
parameters_ws['D3'] = 'Period:'
parameters_ws['E3'].border = b_border
parameters_ws['E3'].alignment = b_c_alignment
parameters_ws['E3'].font = name_font
parameters_ws['E3'] = period_type
parameters_ws['F3'].font = name_font
parameters_ws['F3'].alignment = b_r_alignment
parameters_ws['F3'] = 'Date:'
parameters_ws['G3'].border = b_border
parameters_ws['G3'].alignment = b_c_alignment
parameters_ws['G3'].font = name_font
parameters_ws[
'G3'] = reporting_start_datetime_local + "__" + reporting_end_datetime_local
parameters_ws.merge_cells("G3:H3")
parameters_ws_current_row_number = 6
parameters_ws['B' +
str(parameters_ws_current_row_number)].font = title_font
parameters_ws['B' +
str(parameters_ws_current_row_number)] = name + ' 相关参数'
parameters_ws_current_row_number += 1
parameters_table_start_row_number = parameters_ws_current_row_number
parameters_ws.row_dimensions[
parameters_ws_current_row_number].height = 80
parameters_ws_current_row_number += 1
table_current_col_number = 'B'
for i in range(0, parameters_names_len):
if len(parameters_data['timestamps'][i]) == 0:
continue
parameters_ws[table_current_col_number +
str(parameters_ws_current_row_number -
1)].fill = table_fill
parameters_ws[table_current_col_number +
str(parameters_ws_current_row_number -
1)].border = f_border
col = chr(ord(table_current_col_number) + 1)
parameters_ws[col + str(parameters_ws_current_row_number -
1)].fill = table_fill
parameters_ws[col + str(parameters_ws_current_row_number -
1)].border = f_border
parameters_ws[col + str(parameters_ws_current_row_number -
1)].font = name_font
parameters_ws[col + str(parameters_ws_current_row_number -
1)].alignment = c_c_alignment
parameters_ws[col + str(parameters_ws_current_row_number -
1)] = parameters_data['names'][i]
table_current_row_number = parameters_ws_current_row_number
for j, value in enumerate(list(parameters_data['timestamps'][i])):
col = table_current_col_number
parameters_ws[col +
str(table_current_row_number)].border = f_border
parameters_ws[col +
str(table_current_row_number)].font = title_font
parameters_ws[
col +
str(table_current_row_number)].alignment = c_c_alignment
parameters_ws[col + str(table_current_row_number)] = value
col = chr(ord(col) + 1)
parameters_ws[col +
str(table_current_row_number)].border = f_border
parameters_ws[col +
str(table_current_row_number)].font = title_font
parameters_ws[
col +
str(table_current_row_number)].alignment = c_c_alignment
parameters_ws[col + str(table_current_row_number)] = round(
parameters_data['values'][i][j], 2)
table_current_row_number += 1
table_current_col_number = chr(ord(table_current_col_number) + 3)
########################################################
# parameters chart and parameters table
########################################################
ws['B' + str(current_sheet_parameters_row_number)].font = title_font
ws['B' + str(current_sheet_parameters_row_number)] = name + ' 相关参数'
current_sheet_parameters_row_number += 1
chart_start_row_number = current_sheet_parameters_row_number
col_index = 0
for i in range(0, parameters_names_len):
if len(parameters_data['timestamps'][i]) == 0:
continue
line = LineChart()
data_col = 3 + col_index * 3
labels_col = 2 + col_index * 3
col_index += 1
line.title = '相关参数 - ' + \
parameters_ws.cell(row=parameters_table_start_row_number, column=data_col).value
labels = Reference(parameters_ws,
min_col=labels_col,
min_row=parameters_table_start_row_number + 1,
max_row=(len(parameters_data['timestamps'][i]) +
parameters_table_start_row_number))
line_data = Reference(
parameters_ws,
min_col=data_col,
min_row=parameters_table_start_row_number,
max_row=(len(parameters_data['timestamps'][i]) +
parameters_table_start_row_number))
line.add_data(line_data, titles_from_data=True)
line.set_categories(labels)
line_data = line.series[0]
line_data.marker.symbol = "circle"
line_data.smooth = True
line.x_axis.crosses = 'min'
line.height = 8.25
line.width = 24
line.dLbls = DataLabelList()
line.dLbls.dLblPos = 't'
line.dLbls.showVal = False
line.dLbls.showPercent = False
chart_col = 'B'
chart_cell = chart_col + str(chart_start_row_number)
chart_start_row_number += 6
ws.add_chart(line, chart_cell)
current_sheet_parameters_row_number = chart_start_row_number
current_sheet_parameters_row_number += 1
##########################################
filename = str(uuid.uuid4()) + '.xlsx'
wb.save(filename)
return filename
[
0.8,
35,
105,
170,
105,
35,
],
[0.9, 15, 65, 105, 65, 15],
]
for row in data:
ws.append(row)
c1 = SurfaceChart()
ref = Reference(ws, min_col=2, max_col=6, min_row=1, max_row=10)
labels = Reference(ws, min_col=1, min_row=2, max_row=10)
c1.add_data(ref, titles_from_data=True)
c1.set_categories(labels)
c1.title = "Contour"
ws.add_chart(c1, "A12")
from copy import deepcopy
# wireframe
c2 = deepcopy(c1)
c2.wireframe = True
c2.title = "2D Wireframe"
ws.add_chart(c2, "G12")
# from PIL import Image
# img2 = Image.open(imgFile)
# new_img = img2.resize((100, 100))
# new_img.save('new.png')
# img3 = openpyxl.drawing.image.Image(new.png)
# sheet1.add_image(img3, 'A5')
rows = [
['김일수', 11],
['김이수', 22],
['김삼수', 33],
['김사수', 15],
['김오수', 11],
]
for row in rows:
sheet1.append(row)
datax = Reference(sheet1, min_col=2,
min_row=1, max_col=2, max_row=5)
categs = Reference(sheet1, min_col=1,
min_row=1, max_row=5)
chart = BarChart()
chart.add_data(data=datax)
chart.set_categories(categs)
chart.legend = None # 범례
chart.varyColors = True
chart.title = "차트 타이틀"
sheet1.add_chart(chart, "A8")
green_column_startend_ecell_list = []
blue_column_startend_ecell_list = []
m = 0
color_list = ['red', 'green', 'blue']
for element in dic:
if element.startswith('R') and element.endswith(
'G') and 'Start' not in element and 'End' not in element:
red_column_gcell_list.append(dic[element])
chart = ScatterChart()
chart.title = 'Guard Cell, Red Pixels'
chart.style = 13
chart.x_axis.title = 'Pixel Bin'
chart.y_axis.title = 'Intensity'
xvalues = Reference(ws, min_col=1, min_row=5, max_row=261)
for i in red_column_gcell_list:
values = Reference(ws, min_col=i, min_row=4, max_row=261)
series = Series(values, xvalues, title_from_data=True)
chart.series.append(series)
lineProp = drawing.line.LineProperties(
solidFill=drawing.colors.ColorChoice(prstClr=color_list[m]))
series.graphicalProperties.line = lineProp
m += 1
ws.add_chart(chart, "A10")
m = 0
elif element.startswith('R') and element.endswith(
'E') and 'Start' not in element and 'End' not in element:
red_column_ecell_list.append(dic[element])
chart = ScatterChart()
chart.title = 'Epidermal Cell, Red Pixels'
def graph(string: str):
jsonOBJ = json.loads(string[string.find(BeginJSON) + len(BeginJSON) : string.find(EndJSON)])
dataStream = string[string.find(EndJSON) + len(EndJSON) :]
StrDict = {v: k for k, v in jsonOBJ[1].items()}
data = dict()
dataTitles = list(["Elapsed Time (s)"])
epochSmol = -1
for line in dataStream.splitlines():
try:
msg = line.split(" ")
if len(msg) != 4 or not StrDict.get(int(msg[2])):
continue
msgID = StrDict[int(msg[2])]
if not data.get(msgID):
data[msgID] = list()
dataTitles.append(msgID)
epoch = int(msg[0])
data[msgID].append(list((epoch, int(msg[3]), data[msgID])))
if epochSmol == -1 or epoch < epochSmol:
epochSmol = epoch
except KeyError as e:
print("KeyError: ", e)
pass
for _, value in data.items():
value.sort(key=lambda x: x[0])
for lst in value:
lst[0] -= epochSmol
lst[0] /= 1000000000
wb = openpyxl.Workbook()
ws = wb.active
ws.append(dataTitles)
rows = list()
def getRow(): # IMPROVE: better series generation
row = list()
for title in dataTitles:
column = data.get(title)
if not column or len(column) == 0:
row.append((epochSmol + 1, 0))
else:
row.append(column[0])
smallestEpoch = epochSmol
for item in row:
if item[0] < smallestEpoch:
smallestEpoch = item[0]
if smallestEpoch == epochSmol:
return
finalRow = list([smallestEpoch])
row.pop(0)
for item in row:
if item[0] != smallestEpoch:
finalRow.append(item[1])
else:
finalRow.append(item[2].pop(0)[1])
if len(finalRow) == 1:
return
return finalRow
while True:
row = getRow()
if row:
rows.append(row)
else:
break
for row in rows:
ws.append(row)
chart = ScatterChart()
chart.title = "Interpreted Data"
chart.style = 2
chart.x_axis.title = "Elapsed Time (s)"
chart.y_axis.title = "Value"
chart.height = 20
chart.width = 45
xvalues = Reference(ws, min_col=1, min_row=2, max_row=len(rows) + 1)
for i in range(2, len(dataTitles) + 1):
values = Reference(ws, min_col=i, min_row=1, max_row=len(rows) + 1)
series = Series(values, xvalues, title_from_data=True)
# series.marker.symbol = "circle"
# series.marker.size = 3
# series.graphicalProperties.line.noFill = True
series.smooth = True
series.graphicalProperties.line.width = 10000 # width in EMUs
chart.series.append(series)
ws.add_chart(chart, "A1")
wb.save("{}.xlsx".format(SaveName))
ws.add_chart(chart)
ws = wb.create_sheet(1, "Negative")
for i in range(-5, 5):
ws.append([i])
chart = BarChart()
values = Reference(ws, (0, 0), (9, 0))
series = Serie(values)
chart.add_serie(series)
ws.add_chart(chart)
ws = wb.create_sheet(2, "Letters")
for idx, l in enumerate("ABCDEFGHIJ"):
ws.append([l, idx, idx])
chart = BarChart()
labels = Reference(ws, (0, 0), (9, 0))
values = Reference(ws, (0, 1), (9, 1))
series = Serie(values, labels=labels)
chart.add_serie(series)
# add second series
values = Reference(ws, (0, 2), (9, 2))
series = Serie(values, labels=labels)
chart.add_serie(series)
ws.add_chart(chart)
ws = wb.create_sheet(3, "Dates")
for i in range(1, 10):
ws.append([date(2013, i, 1), i])
chart = BarChart()
values = Reference(ws, (0, 1), (9, 1))
labels = Reference(ws, (0, 0), (9, 0))