excelexporters.spaceenergycategory.generate_excel() - Code Metrics - myems/myems-api - Measure and Improve Code Quality continuously with Scrutinizer

generate_excel() F
last analyzed 2021-02-26 06:13 UTC

↳ Parent: excelexporters.spaceenergycategory

Complexity

Conditions

Size

Total Lines	495
Code Lines	361

Duplication

Lines	77
Ratio	15.56 %

Importance

Changes

Metric	Value
cc	39
eloc	361
nop	5
dl	77
loc	495
rs	0
c	0
b	0
f	0

How to fix Long Method Complexity

import base64
import uuid
import os
from openpyxl.chart import (
        PieChart,
        LineChart,
        BarChart,
        Reference,
    )
from openpyxl.styles import PatternFill, Border, Side, Alignment, Font
from openpyxl.drawing.image import Image
from openpyxl import Workbook
from openpyxl.chart.label import DataLabelList

####################################################################################################################
# PROCEDURES
# Step 1: Validate the report data
# Step 2: Generate excel file
# Step 3: Encode the excel file bytes to Base64
####################################################################################################################


def export(report,
           name,
           reporting_start_datetime_local,
           reporting_end_datetime_local,
           period_type):
    ####################################################################################################################
    # Step 1: Validate the report data
    ####################################################################################################################
    if report is None:
        return None
    print(report)

    ####################################################################################################################
    # Step 2: Generate excel file from the report data
    ####################################################################################################################
    filename = generate_excel(report,
                              name,
                              reporting_start_datetime_local,
                              reporting_end_datetime_local,
                              period_type)
    ####################################################################################################################
    # Step 3: Encode the excel file to Base64
    ####################################################################################################################
    try:
        with open(filename, 'rb') as binary_file:
            binary_file_data = binary_file.read()
    except IOError as ex:
        pass

    # Base64 encode the bytes
    base64_encoded_data = base64.b64encode(binary_file_data)

    # get the Base64 encoded data using human-readable characters.
    base64_message = base64_encoded_data.decode('utf-8')
    # delete the file from server
    try:
        os.remove(filename)
    except NotImplementedError as ex:
        pass
    return base64_message


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, 90 + 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
    #################################################
    # First: 能耗分析
    # 6: title
    # 7: table title
    # 8~10 table_data
    # Total: 5 rows
    # if has not energy data: set low height for rows
    #################################################
    reporting_period_data = report['reporting_period']

    has_energy_data_flag = True
    if "names" not in reporting_period_data.keys() or \
            reporting_period_data['names'] is None or \
            len(reporting_period_data['names']) == 0:
        has_energy_data_flag = False

    if has_energy_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)
            row = '7'
            cell = col + row
            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

        # 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'] = round(reporting_period_data['total_in_kgce_per_unit_area'] / 1000, 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'] / 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'] = round(reporting_period_data['total_in_kgco2e_per_unit_area'] / 1000, 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
    #################################################
    # Second: 分时电耗
    # 12: title
    # 13: table title
    # 14~17 table_data
    # Total: 6 rows
    ################################################
    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  # cm 1.05*5 1.05cm = 30 pt
        pie.width = 9
        # pie.title = "Pies sold by category"
        s1 = pie.series[0]
        s1.dLbls = DataLabelList()
        s1.dLbls.showCatName = False  # 标签显示
        s1.dLbls.showVal = True  # 数量显示
        s1.dLbls.showPercent = True  # 百分比显示
        # s1 = CharacterProperties(sz=1800)     # 图表中字体大小 *100

        ws.add_chart(pie, "D13")

    else:
        for i in range(12, 18 + 1):
            ws.row_dimensions[i].height = 0.1
        # end_row 10
        # start_row 12
    ################################################
    # Third: 子空间能耗
    # 19: title
    # 20: table title
    # 21~24 table_data
    # Total: 6 rows
    ################################################
    has_child_flag = True
    # Judge if the space has child space, if not, delete it.
    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:
        has_child_flag = False

    if has_child_flag:
        child = report['child_space']
        child_spaces = child['child_space_names_array'][0]
        child_subtotals = child['subtotals_array'][0]

        ws['B19'].font = title_font
        ws['B19'] = name+' 子空间能耗'

        ws.row_dimensions[20].height = 60
        ws['B20'].fill = table_fill
        ws['B20'].border = f_border
        ca_len = len(child['energy_category_names'])

        for i in range(0, ca_len):
            row = chr(ord('C') + i)
            ws[row + '20'].fill = table_fill
            ws[row + '20'].font = title_font
            ws[row + '20'].alignment = c_c_alignment
            ws[row + '20'].border = f_border
            ws[row + '20'] = child['energy_category_names'][i] + ' (' + child['units'][i] + ')'

        space_len = len(child['child_space_names_array'][0])
        for i in range(0, space_len):
            row = str(i + 21)

            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

            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
                ws[col + row] = round(child['subtotals_array'][j][i], 2)
                ws[col + row].border = f_border
                # pie
                # 25~30: pie
                pie = PieChart()
                pie.title = ws.cell(column=3 + j, row=20).value
                labels = Reference(ws, min_col=2, min_row=21, max_row=24)
                pie_data = Reference(ws, min_col=3 + j, min_row=20, max_row=24)
                pie.add_data(pie_data, titles_from_data=True)
                pie.set_categories(labels)
                pie.height = 6.6  # cm 1.05*5 1.05cm = 30 pt
                pie.width = 8
                # pie.title = "Pies sold by category"
                s1 = pie.series[0]
                s1.dLbls = DataLabelList()
                s1.dLbls.showCatName = True  # 标签显示
                s1.dLbls.showVal = True  # 数量显示
                s1.dLbls.showPercent = True  # 百分比显示
                # s1 = CharacterProperties(sz=1800)     # 图表中字体大小 *100
                chart_col = chr(ord('B') + 2 * j)
                chart_cell = chart_col + '25'
                ws.add_chart(pie, chart_cell)
    else:
        for i in range(19, 36 + 1):
            ws.row_dimensions[i].height = 0.1
    for i in range(30, 35 + 1):
        ws.row_dimensions[i].height = 0.1
    ################################################
    # Fourth: 能耗详情
    # 37: title
    # 38~ 38+ca_len*6-1: line
    # 38+ca_len*6: table title
    # 38+ca_len*6~: table_data
    ################################################
    reporting_period_data = report['reporting_period']
    times = reporting_period_data['timestamps']
    has_detail_data_flag = True
    ca_len = len(report['reporting_period']['names'])
    table_row = 38 + 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['B37'].font = title_font
        ws['B37'] = 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)
            print("max_row", max_row)

        if has_data:
            for i in range(0, len(time)):
                col = 'B'
                row = str(table_row+1 + i)
                # col = chr(ord('B') + i)
                ws[col + row].font = title_font
                ws[col + row].alignment = c_c_alignment
                ws[col + row] = time[i]
                ws[col + row].border = f_border

            for i in range(0, ca_len):
                # 38 title
                col = chr(ord('C') + i)

                ws[col + 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)
                    # col = chr(ord('B') + i)
                    ws[col + row].font = title_font
                    ws[col + row].alignment = c_c_alignment
                    ws[col + row] = round(reporting_period_data['values'][i][j], 2)
                    ws[col + row].border = f_border
                # line
                # 39~: line
                line = LineChart()
                line.title = '报告期消耗 - '
                labels = Reference(ws, min_col=2, min_row=table_row+1, max_row=max_row + 1)
                line_data = Reference(ws, min_col=3 + i, min_row=table_row, max_row=max_row + 1)  # openpyxl bug
                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 = False
                line.height = 8.25  # cm 1.05*5 1.05cm = 30 pt
                line.width = 24
                # pie.title = "Pies sold by category"
                line.dLbls = DataLabelList()
                # line.dLbls.showCatName = True  # label show
                line.dLbls.showVal = True  # val show
                line.dLbls.showPercent = True  # percent show
                # s1 = CharacterProperties(sz=1800)     # font size *100
                chart_col = 'B'
                chart_cell = chart_col + str(38 + 6*i)
                ws.add_chart(line, chart_cell)
    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


1		import base64
2		import uuid
3		import os
4		from openpyxl.chart import (
5		PieChart,
6		LineChart,
7		BarChart,
8		Reference,
9		)
10		from openpyxl.styles import PatternFill, Border, Side, Alignment, Font
11		from openpyxl.drawing.image import Image
12		from openpyxl import Workbook
13		from openpyxl.chart.label import DataLabelList
14
15		####################################################################################################################
16		# PROCEDURES
17		# Step 1: Validate the report data
18		# Step 2: Generate excel file
19		# Step 3: Encode the excel file bytes to Base64
20		####################################################################################################################
21
22
23		def export(report,
24		name,
25		reporting_start_datetime_local,
26		reporting_end_datetime_local,
27		period_type):
28		####################################################################################################################
29		# Step 1: Validate the report data
30		####################################################################################################################
31		if report is None:
32		return None
33		print(report)
34
35		####################################################################################################################
36		# Step 2: Generate excel file from the report data
37		####################################################################################################################
38		filename = generate_excel(report,
39		name,
40		reporting_start_datetime_local,
41		reporting_end_datetime_local,
42		period_type)
43		####################################################################################################################
44		# Step 3: Encode the excel file to Base64
45		####################################################################################################################
46		try:
47		with open(filename, 'rb') as binary_file:
48		binary_file_data = binary_file.read()
49		except IOError as ex:
50		pass
51
52		# Base64 encode the bytes
53		base64_encoded_data = base64.b64encode(binary_file_data)
		0 ignored issues – show introduced 2021-01-16 08:06 UTC by Report Bug Copy Issue Report The variable `binary_file_data` does not seem to be defined for all execution paths. Loading history...
54		# get the Base64 encoded data using human-readable characters.
55		base64_message = base64_encoded_data.decode('utf-8')
56		# delete the file from server
57		try:
58		os.remove(filename)
59		except NotImplementedError as ex:
60		pass
61		return base64_message
62
63
64		def generate_excel(report,
65		name,
66		reporting_start_datetime_local,
67		reporting_end_datetime_local,
68		period_type):
69
70		wb = Workbook()
71		ws = wb.active
72
73		# Row height
74		ws.row_dimensions[1].height = 102
75
76		for i in range(2, 2000 + 1):
77		ws.row_dimensions[i].height = 42
78		#
79		# for i in range(2, 37 + 1):
80		# ws.row_dimensions[i].height = 30
81		#
82		# for i in range(38, 90 + 1):
83		# ws.row_dimensions[i].height = 30
84
85		# Col width
86		ws.column_dimensions['A'].width = 1.5
87
88		ws.column_dimensions['B'].width = 25.0
89
90		for i in range(ord('C'), ord('L')):
91		ws.column_dimensions[chr(i)].width = 15.0
92
93		# Font
94		name_font = Font(name='Constantia', size=15, bold=True)
95		title_font = Font(name='宋体', size=15, bold=True)
96		data_font = Font(name='Franklin Gothic Book', size=11)
97
98		table_fill = PatternFill(fill_type='solid', fgColor='1F497D')
99		f_border = Border(left=Side(border_style='medium', color='00000000'),
100		right=Side(border_style='medium', color='00000000'),
101		bottom=Side(border_style='medium', color='00000000'),
102		top=Side(border_style='medium', color='00000000')
103		)
104		b_border = Border(
105		bottom=Side(border_style='medium', color='00000000'),
106		)
107
108		b_c_alignment = Alignment(vertical='bottom',
109		horizontal='center',
110		text_rotation=0,
111		wrap_text=True,
112		shrink_to_fit=False,
113		indent=0)
114		c_c_alignment = Alignment(vertical='center',
115		horizontal='center',
116		text_rotation=0,
117		wrap_text=True,
118		shrink_to_fit=False,
119		indent=0)
120		b_r_alignment = Alignment(vertical='bottom',
121		horizontal='right',
122		text_rotation=0,
123		wrap_text=True,
124		shrink_to_fit=False,
125		indent=0)
126		c_r_alignment = Alignment(vertical='bottom',
127		horizontal='center',
128		text_rotation=0,
129		wrap_text=True,
130		shrink_to_fit=False,
131		indent=0)
132
133		# Img
134		img = Image("excelexporters/myems.png")
135		img.width = img.width * 0.85
136		img.height = img.height * 0.85
137		# img = Image("myems.png")
138		ws.add_image(img, 'B1')
139
140		# Title
141		ws.row_dimensions[3].height = 60
142
143		ws['B3'].font = name_font
144		ws['B3'].alignment = b_r_alignment
145		ws['B3'] = 'Name:'
146		ws['C3'].border = b_border
147		ws['C3'].alignment = b_c_alignment
148		ws['C3'].font = name_font
149		ws['C3'] = name
150
151		ws['D3'].font = name_font
152		ws['D3'].alignment = b_r_alignment
153		ws['D3'] = 'Period:'
154		ws['E3'].border = b_border
155		ws['E3'].alignment = b_c_alignment
156		ws['E3'].font = name_font
157		ws['E3'] = period_type
158
159		ws['F3'].font = name_font
160		ws['F3'].alignment = b_r_alignment
161		ws['F3'] = 'Date:'
162		ws['G3'].border = b_border
163		ws['G3'].alignment = b_c_alignment
164		ws['G3'].font = name_font
165		ws['G3'] = reporting_start_datetime_local + "__" + reporting_end_datetime_local
166		ws.merge_cells("G3:H3")
167
168		if "reporting_period" not in report.keys() or \
169		"names" not in report['reporting_period'].keys() or len(report['reporting_period']['names']) == 0:
170		filename = str(uuid.uuid4()) + '.xlsx'
		0 ignored issues – show Comprehensibility Best Practice introduced 2021-01-27 05:25 UTC by Report Bug Copy Issue Report The variable `str` does not seem to be defined. Loading history...
171		wb.save(filename)
172
173		return filename
174		#################################################
175		# First: 能耗分析
176		# 6: title
177		# 7: table title
178		# 8~10 table_data
179		# Total: 5 rows
180		# if has not energy data: set low height for rows
181		#################################################
182		reporting_period_data = report['reporting_period']
183
184		has_energy_data_flag = True
185		if "names" not in reporting_period_data.keys() or \
186		reporting_period_data['names'] is None or \
187		len(reporting_period_data['names']) == 0:
188		has_energy_data_flag = False
189
190		if has_energy_data_flag:
191		ws['B6'].font = title_font
192		ws['B6'] = name+' 能耗分析'
193
194		category = reporting_period_data['names']
195		ca_len = len(category)
196
197		ws.row_dimensions[7].height = 60
198		ws['B7'].fill = table_fill
199		ws['B7'].border = f_border
200
201		ws['B8'].font = title_font
202		ws['B8'].alignment = c_c_alignment
203		ws['B8'] = '能耗'
204		ws['B8'].border = f_border
205
206		ws['B9'].font = title_font
207		ws['B9'].alignment = c_c_alignment
208		ws['B9'] = '单位面积能耗'
209		ws['B9'].border = f_border
210
211		ws['B10'].font = title_font
212		ws['B10'].alignment = c_c_alignment
213		ws['B10'] = '环比'
214		ws['B10'].border = f_border
215
216		col = ''
217
218		for i in range(0, ca_len):
219		col = chr(ord('C') + i)
220		row = '7'
221		cell = col + row
222		ws[col + '7'].fill = table_fill
223		ws[col + '7'].font = name_font
224		ws[col + '7'].alignment = c_c_alignment
225		ws[col + '7'] = reporting_period_data['names'][i] + " (" + reporting_period_data['units'][i] + ")"
226		ws[col + '7'].border = f_border
227
228		ws[col + '8'].font = name_font
229		ws[col + '8'].alignment = c_c_alignment
230		ws[col + '8'] = round(reporting_period_data['subtotals'][i], 2)
231		ws[col + '8'].border = f_border
232
233		ws[col + '9'].font = name_font
234		ws[col + '9'].alignment = c_c_alignment
235		ws[col + '9'] = round(reporting_period_data['subtotals_per_unit_area'][i], 2)
236		ws[col + '9'].border = f_border
237
238		ws[col + '10'].font = name_font
239		ws[col + '10'].alignment = c_c_alignment
240		ws[col + '10'] = str(round(reporting_period_data['increment_rates'][i] * 100, 2)) + "%" \
241		if reporting_period_data['increment_rates'][i] is not None else "-"
242		ws[col + '10'].border = f_border
243
244		# TCE TCO2E
245		end_col = col
246		# TCE
247		tce_col = chr(ord(end_col) + 1)
248		ws[tce_col + '7'].fill = table_fill
249		ws[tce_col + '7'].font = name_font
250		ws[tce_col + '7'].alignment = c_c_alignment
251		ws[tce_col + '7'] = "吨标准煤 (TCE)"
252		ws[tce_col + '7'].border = f_border
253
254		ws[tce_col + '8'].font = name_font
255		ws[tce_col + '8'].alignment = c_c_alignment
256		ws[tce_col + '8'] = round(reporting_period_data['total_in_kgce'] / 1000, 2)
257		ws[tce_col + '8'].border = f_border
258
259		ws[tce_col + '9'].font = name_font
260		ws[tce_col + '9'].alignment = c_c_alignment
261		ws[tce_col + '9'] = round(reporting_period_data['total_in_kgce_per_unit_area'] / 1000, 2)
262		ws[tce_col + '9'].border = f_border
263
264		ws[tce_col + '10'].font = name_font
265		ws[tce_col + '10'].alignment = c_c_alignment
266		ws[tce_col + '10'] = str(round(reporting_period_data['increment_rate_in_kgce'] * 100, 2)) + "%" \
267		if reporting_period_data['increment_rate_in_kgce'] is not None else "-"
268		ws[tce_col + '10'].border = f_border
269
270		# TCO2E
271		tco2e_col = chr(ord(end_col) + 2)
272		ws[tco2e_col + '7'].fill = table_fill
273		ws[tco2e_col + '7'].font = name_font
274		ws[tco2e_col + '7'].alignment = c_c_alignment
275		ws[tco2e_col + '7'] = "吨二氧化碳排放 (TCO2E)"
276		ws[tco2e_col + '7'].border = f_border
277
278		ws[tco2e_col + '8'].font = name_font
279		ws[tco2e_col + '8'].alignment = c_c_alignment
280		ws[tco2e_col + '8'] = round(reporting_period_data['total_in_kgco2e'] / 1000, 2)
281		ws[tco2e_col + '8'].border = f_border
282
283		ws[tco2e_col + '9'].font = name_font
284		ws[tco2e_col + '9'].alignment = c_c_alignment
285		ws[tco2e_col + '9'] = round(reporting_period_data['total_in_kgco2e_per_unit_area'] / 1000, 2)
286		ws[tco2e_col + '9'].border = f_border
287
288		ws[tco2e_col + '10'].font = name_font
289		ws[tco2e_col + '10'].alignment = c_c_alignment
290		ws[tco2e_col + '10'] = str(round(reporting_period_data['increment_rate_in_kgco2e'] * 100, 2)) + "%" \
291		if reporting_period_data['increment_rate_in_kgco2e'] is not None else "-"
292		ws[tco2e_col + '10'].border = f_border
293		else:
294		for i in range(6, 10 + 1):
295		ws.row_dimensions[i].height = 0.1
296		#################################################
297		# Second: 分时电耗
298		# 12: title
299		# 13: table title
300		# 14~17 table_data
301		# Total: 6 rows
302		################################################
303		has_ele_peak_flag = True
304		if "toppeaks" not in reporting_period_data.keys() or \
305		reporting_period_data['toppeaks'] is None or \
306		len(reporting_period_data['toppeaks']) == 0:
307		has_ele_peak_flag = False
308
309	View Code Duplication	if has_ele_peak_flag:
		0 ignored issues – show Duplication introduced 2021-01-16 08:06 UTC by Report Bug Copy Issue Report This code seems to be duplicated in your project. Loading history...
310		ws['B12'].font = title_font
311		ws['B12'] = name+' 分时电耗'
312
313		ws.row_dimensions[13].height = 60
314		ws['B13'].fill = table_fill
315		ws['B13'].font = name_font
316		ws['B13'].alignment = c_c_alignment
317		ws['B13'].border = f_border
318
319		ws['C13'].fill = table_fill
320		ws['C13'].font = name_font
321		ws['C13'].alignment = c_c_alignment
322		ws['C13'].border = f_border
323		ws['C13'] = '分时电耗'
324
325		ws['B14'].font = title_font
326		ws['B14'].alignment = c_c_alignment
327		ws['B14'] = '尖'
328		ws['B14'].border = f_border
329
330		ws['C14'].font = title_font
331		ws['C14'].alignment = c_c_alignment
332		ws['C14'].border = f_border
333		ws['C14'] = round(reporting_period_data['toppeaks'][0], 2)
334
335		ws['B15'].font = title_font
336		ws['B15'].alignment = c_c_alignment
337		ws['B15'] = '峰'
338		ws['B15'].border = f_border
339
340		ws['C15'].font = title_font
341		ws['C15'].alignment = c_c_alignment
342		ws['C15'].border = f_border
343		ws['C15'] = round(reporting_period_data['onpeaks'][0], 2)
344
345		ws['B16'].font = title_font
346		ws['B16'].alignment = c_c_alignment
347		ws['B16'] = '平'
348		ws['B16'].border = f_border
349
350		ws['C16'].font = title_font
351		ws['C16'].alignment = c_c_alignment
352		ws['C16'].border = f_border
353		ws['C16'] = round(reporting_period_data['midpeaks'][0], 2)
354
355		ws['B17'].font = title_font
356		ws['B17'].alignment = c_c_alignment
357		ws['B17'] = '谷'
358		ws['B17'].border = f_border
359
360		ws['C17'].font = title_font
361		ws['C17'].alignment = c_c_alignment
362		ws['C17'].border = f_border
363		ws['C17'] = round(reporting_period_data['offpeaks'][0], 2)
364
365		pie = PieChart()
366		pie.title = name+' 分时电耗'
367		labels = Reference(ws, min_col=2, min_row=14, max_row=17)
368		pie_data = Reference(ws, min_col=3, min_row=13, max_row=17)
369		pie.add_data(pie_data, titles_from_data=True)
370		pie.set_categories(labels)
371		pie.height = 7.25 # cm 1.05*5 1.05cm = 30 pt
372		pie.width = 9
373		# pie.title = "Pies sold by category"
374		s1 = pie.series[0]
375		s1.dLbls = DataLabelList()
376		s1.dLbls.showCatName = False # 标签显示
377		s1.dLbls.showVal = True # 数量显示
378		s1.dLbls.showPercent = True # 百分比显示
379		# s1 = CharacterProperties(sz=1800) # 图表中字体大小 *100
380
381		ws.add_chart(pie, "D13")
382
383		else:
384		for i in range(12, 18 + 1):
385		ws.row_dimensions[i].height = 0.1
386		# end_row 10
387		# start_row 12
388		################################################
389		# Third: 子空间能耗
390		# 19: title
391		# 20: table title
392		# 21~24 table_data
393		# Total: 6 rows
394		################################################
395		has_child_flag = True
396		# Judge if the space has child space, if not, delete it.
397		if "child_space" not in report.keys() or "energy_category_names" not in report['child_space'].keys() or \
398		len(report['child_space']["energy_category_names"]) == 0:
399		has_child_flag = False
400
401		if has_child_flag:
402		child = report['child_space']
403		child_spaces = child['child_space_names_array'][0]
404		child_subtotals = child['subtotals_array'][0]
405
406		ws['B19'].font = title_font
407		ws['B19'] = name+' 子空间能耗'
408
409		ws.row_dimensions[20].height = 60
410		ws['B20'].fill = table_fill
411		ws['B20'].border = f_border
412		ca_len = len(child['energy_category_names'])
413
414		for i in range(0, ca_len):
415		row = chr(ord('C') + i)
416		ws[row + '20'].fill = table_fill
417		ws[row + '20'].font = title_font
418		ws[row + '20'].alignment = c_c_alignment
419		ws[row + '20'].border = f_border
420		ws[row + '20'] = child['energy_category_names'][i] + ' (' + child['units'][i] + ')'
421
422		space_len = len(child['child_space_names_array'][0])
423		for i in range(0, space_len):
424		row = str(i + 21)
425
426		ws['B' + row].font = name_font
427		ws['B' + row].alignment = c_c_alignment
428		ws['B' + row] = child['child_space_names_array'][0][i]
429		ws['B' + row].border = f_border
430
431		for j in range(0, ca_len):
432		col = chr(ord('C') + j)
433		ws[col + row].font = name_font
434		ws[col + row].alignment = c_c_alignment
435		ws[col + row] = round(child['subtotals_array'][j][i], 2)
436		ws[col + row].border = f_border
437		# pie
438		# 25~30: pie
439		pie = PieChart()
440		pie.title = ws.cell(column=3 + j, row=20).value
441		labels = Reference(ws, min_col=2, min_row=21, max_row=24)
442		pie_data = Reference(ws, min_col=3 + j, min_row=20, max_row=24)
443		pie.add_data(pie_data, titles_from_data=True)
444		pie.set_categories(labels)
445		pie.height = 6.6 # cm 1.05*5 1.05cm = 30 pt
446		pie.width = 8
447		# pie.title = "Pies sold by category"
448		s1 = pie.series[0]
449		s1.dLbls = DataLabelList()
450		s1.dLbls.showCatName = True # 标签显示
451		s1.dLbls.showVal = True # 数量显示
452		s1.dLbls.showPercent = True # 百分比显示
453		# s1 = CharacterProperties(sz=1800) # 图表中字体大小 *100
454		chart_col = chr(ord('B') + 2 * j)
455		chart_cell = chart_col + '25'
456		ws.add_chart(pie, chart_cell)
457		else:
458		for i in range(19, 36 + 1):
459		ws.row_dimensions[i].height = 0.1
460		for i in range(30, 35 + 1):
461		ws.row_dimensions[i].height = 0.1
462		################################################
463		# Fourth: 能耗详情
464		# 37: title
465		# 38~ 38+ca_len*6-1: line
466		# 38+ca_len*6: table title
467		# 38+ca_len*6~: table_data
468		################################################
469		reporting_period_data = report['reporting_period']
470		times = reporting_period_data['timestamps']
471		has_detail_data_flag = True
472		ca_len = len(report['reporting_period']['names'])
473		table_row = 38 + ca_len*6
474		if "timestamps" not in reporting_period_data.keys() or \
475		reporting_period_data['timestamps'] is None or \
476		len(reporting_period_data['timestamps']) == 0:
477		has_detail_data_flag = False
478
479		if has_detail_data_flag:
480		ws['B37'].font = title_font
481		ws['B37'] = name+' 能耗详情'
482
483		ws.row_dimensions[table_row].height = 60
484		ws['B'+str(table_row)].fill = table_fill
485		ws['B' + str(table_row)].font = title_font
486		ws['B'+str(table_row)].border = f_border
487		ws['B'+str(table_row)].alignment = c_c_alignment
488		ws['B'+str(table_row)] = '时间'
489		time = times[0]
490		has_data = False
491		max_row = 0
492		if len(time) > 0:
493		has_data = True
494		max_row = table_row + len(time)
495		print("max_row", max_row)
496
497		if has_data:
498		for i in range(0, len(time)):
499		col = 'B'
500		row = str(table_row+1 + i)
501		# col = chr(ord('B') + i)
502		ws[col + row].font = title_font
503		ws[col + row].alignment = c_c_alignment
504		ws[col + row] = time[i]
505		ws[col + row].border = f_border
506
507		for i in range(0, ca_len):
508		# 38 title
509		col = chr(ord('C') + i)
510
511		ws[col + str(table_row)].fill = table_fill
512		ws[col + str(table_row)].font = title_font
513		ws[col + str(table_row)].alignment = c_c_alignment
514		ws[col + str(table_row)] = reporting_period_data['names'][i] + \
515		" (" + reporting_period_data['units'][i] + ")"
516		ws[col + str(table_row)].border = f_border
517
518		# 39 data
519		time = times[i]
520		time_len = len(time)
521
522		for j in range(0, time_len):
523		row = str(table_row+1 + j)
524		# col = chr(ord('B') + i)
525		ws[col + row].font = title_font
526		ws[col + row].alignment = c_c_alignment
527		ws[col + row] = round(reporting_period_data['values'][i][j], 2)
528		ws[col + row].border = f_border
529		# line
530		# 39~: line
531		line = LineChart()
532		line.title = '报告期消耗 - '
533		labels = Reference(ws, min_col=2, min_row=table_row+1, max_row=max_row + 1)
534		line_data = Reference(ws, min_col=3 + i, min_row=table_row, max_row=max_row + 1) # openpyxl bug
535		line.add_data(line_data, titles_from_data=True)
536		line.set_categories(labels)
537		line_data = line.series[0]
538		line_data.marker.symbol = "circle"
539		line_data.smooth = False
540		line.height = 8.25 # cm 1.05*5 1.05cm = 30 pt
541		line.width = 24
542		# pie.title = "Pies sold by category"
543		line.dLbls = DataLabelList()
544		# line.dLbls.showCatName = True # label show
545		line.dLbls.showVal = True # val show
546		line.dLbls.showPercent = True # percent show
547		# s1 = CharacterProperties(sz=1800) # font size *100
548		chart_col = 'B'
549		chart_cell = chart_col + str(38 + 6*i)
550		ws.add_chart(line, chart_cell)
551		else:
552		for i in range(37, 69 + 1):
553		ws.row_dimensions[i].height = 0.1
554
555		filename = str(uuid.uuid4()) + '.xlsx'
556		wb.save(filename)
557
558		return filename
559

myems / myems-api

generate_excel() F last analyzed 2021-02-26 06:13 UTC

Complexity

Size

Duplication

Importance

How to fix Long Method Complexity

Long Method

Complexity

Duplication Side-by-Side

Filter issues like

generate_excel() F
last analyzed 2021-02-26 06:13 UTC