excelexporters.spaceenergycategory.generate_excel() - Code Metrics - Inspection of "Merge branch 'develop'" - myems/myems-api - Measure and Improve Code Quality continuously with Scrutinizer

Passed

Push — master ( 17dcd8...c09fbc )

by Guangyu

created 2021-01-16 08:04 UTC

generate_excel() F

↳ Parent: excelexporters.spaceenergycategory

Complexity

Conditions

Size

Total Lines	447
Code Lines	326

Duplication

Lines	49
Ratio	10.96 %

Importance

Changes

Metric	Value
cc	32
eloc	326
nop	5
dl	49
loc	447
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,
        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(result,

           name,
           reporting_start_datetime_local,
           reporting_end_datetime_local,
           period_type):
    ####################################################################################################################
    # Step 1: Validate the report data
    ####################################################################################################################
    if result is None:
        return None

    if "reporting_period" not in result.keys() or \
            "names" not in result['reporting_period'].keys() or len(result['reporting_period']['names']) == 0:
        return None
    ####################################################################################################################
    # Step 2: Generate excel file from the report data
    ####################################################################################################################
    filename = generate_excel(result,
                              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(result,
                   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, 69 + 1):
        ws.row_dimensions[i].height = 30

    # Col width
    ws.column_dimensions['A'].width = 1.5

    for i in range(ord('B'), ord('I')):
        ws.column_dimensions[chr(i)].width = 15.0

    # Font
    name_font = Font(name='Constantia', size=15, bold=True)
    title_font = Font(name='宋体', size=15, bold=True)
    data_font = Font(name='Franklin Gothic Book', size=11)

    table_fill = PatternFill(fill_type='solid', fgColor='1F497D')
    f_border = Border(left=Side(border_style='medium', color='00000000'),
                      right=Side(border_style='medium', color='00000000'),
                      bottom=Side(border_style='medium', color='00000000'),
                      top=Side(border_style='medium', color='00000000')
                      )
    b_border = Border(
        bottom=Side(border_style='medium', color='00000000'),
    )

    b_c_alignment = Alignment(vertical='bottom',
                              horizontal='center',
                              text_rotation=0,
                              wrap_text=False,
                              shrink_to_fit=False,
                              indent=0)
    c_c_alignment = Alignment(vertical='center',
                              horizontal='center',
                              text_rotation=0,
                              wrap_text=False,
                              shrink_to_fit=False,
                              indent=0)
    b_r_alignment = Alignment(vertical='bottom',
                              horizontal='right',
                              text_rotation=0,
                              wrap_text=False,
                              shrink_to_fit=False,
                              indent=0)
    c_r_alignment = Alignment(vertical='bottom',
                              horizontal='center',
                              text_rotation=0,
                              wrap_text=False,
                              shrink_to_fit=False,
                              indent=0)
    # Img
    img = Image("excelexporters/myems.png")
    ws.add_image(img, 'B1')

    # Title
    ws['B3'].font = name_font
    ws['B3'].alignment = b_r_alignment
    ws['B3'] = 'Name:'
    ws['C3'].border = b_border
    ws['C3'].alignment = b_c_alignment
    ws['C3'].font = name_font
    ws['C3'] = name

    ws['D3'].font = name_font
    ws['D3'].alignment = b_r_alignment
    ws['D3'] = 'Period:'
    ws['E3'].border = b_border
    ws['E3'].alignment = b_c_alignment
    ws['E3'].font = name_font
    ws['E3'] = period_type

    ws['F3'].font = name_font
    ws['F3'].alignment = b_r_alignment
    ws['F3'] = 'Date:'
    ws['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")

    #################################################
    # First: 能耗分析
    # 6: title
    # 7: table title
    # 8~10 table_data
    # Total: 5 rows
    # if has not energy data: set low height for rows
    #################################################
    report = result['reporting_period']

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

    if has_energy_data_flag:
        ws['B6'].font = title_font
        ws['B6'] = name+' 能耗分析'

        category = report['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

        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'] = report['names'][i] + " (" + report['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(report['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(report['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(report['increment_rates'][i] * 100, 2)) + "%"
            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(report['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(report['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(report['increment_rate_in_kgce'] * 100, 2)) + "%"
        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(report['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(report['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(report['increment_rate_in_kgco2e'] * 100, 2)) + "%"
        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 row
    ################################################
    has_ele_peak_flag = True
    if "toppeaks" not in report.keys() or report['toppeaks'] is None or len(report['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(report['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(report['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(report['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(report['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=14, max_row=17)
        pie.add_data(pie_data, titles_from_data=True)
        pie.set_categories(labels)
        pie.height = 5.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 = True  # 标签显示
        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 result.keys() or "energy_category_names" not in result['child_space'].keys() or \
        len(result['child_space']["energy_category_names"]) == 0:
        has_child_flag = False

    if has_child_flag:
        child = result['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['B20'].fill = table_fill
        ws['B20'].border = f_border

        ws['C20'].fill = table_fill
        ws['C20'].font = title_font
        ws['C20'].alignment = c_c_alignment
        ws['C20'].border = f_border
        ws['C20'] = child['energy_category_names'][0]

        ca_len = len(child['energy_category_names'])
        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] = child['subtotals_array'][0][i]
                ws[col + row].border = f_border
                # pie
                # 25~30: pie
                pie = PieChart()
                labels = Reference(ws, min_col=2, min_row=21, max_row=23)
                pie_data = Reference(ws, min_col=3 + j, min_row=21, max_row=23)
                pie.add_data(pie_data, titles_from_data=True)
                pie.set_categories(labels)
                pie.height = 5.25  # 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 + '26'
                ws.add_chart(pie, chart_cell)
    else:
        for i in range(19, 36 + 1):
            ws.row_dimensions[i].height = 0.1
    ################################################
    # Fourth: 能耗详情
    # 37: title
    # 38: table title
    # 39~69: table_data
    ################################################
    report = result['reporting_period']
    times = report['timestamps']
    has_detail_data_flag = True

    if "timestamps" not in report.keys() or report['timestamps'] is None or len(report['timestamps']) == 0:
        has_detail_data_flag = False

    if has_detail_data_flag:
        ws['B37'].font = title_font
        ws['B37'] = name+' 能耗详情'

        ws['B38'].fill = table_fill
        ws['B38'].border = f_border
        ws['B38'].alignment = c_c_alignment
        ws['B38'] = '时间'
        time = times[0]
        has_data = False
        max_row = 0
        if len(time) > 0:
            has_data = True
            max_row = 38 + len(time)
            print("max_row", max_row)

        if has_data:

            for i in range(0, len(time)):
                col = 'B'
                row = str(39 + 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 + '38'].fill = table_fill
                ws[col + '38'].font = title_font
                ws[col + '38'].alignment = c_c_alignment
                ws[col + '38'] = report['names'][i] + " (" + report['units'][i] + ")"
                ws[col + '38'].border = f_border

                # 39 data
                time = times[i]
                time_len = len(time)

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

myems / myems-api

Push — master ( 17dcd8...c09fbc )

generate_excel() F

Complexity

Size

Duplication

Importance

How to fix Long Method Complexity

Long Method

Complexity

Duplication Side-by-Side

Filter issues like