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

Passed

Push — master ( b6899e...8a8add )

by Guangyu

created 2021-02-05 07:56 UTC

generate_excel() F

↳ Parent: excelexporters.metersubmetersbalance

Complexity

Conditions

Size

Total Lines	233
Code Lines	179

Duplication

Lines	0
Ratio	0 %

Importance

Changes

Metric	Value
cc	15
eloc	179
nop	5
dl	0
loc	233
rs	2.0999
c	0
b	0
f	0

How to fix Long Method Complexity

import base64
import uuid
import os
from openpyxl.chart import (
    BarChart,
    Reference,
)
from openpyxl.styles import PatternFill, Border, Side, Alignment, Protection, 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 excelexporters file
# Step 3: Encode the excelexporters file 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

    ####################################################################################################################
    # 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(report, name, reporting_start_datetime_local, reporting_end_datetime_local, period_type):
    wb = Workbook()
    ws = wb.active

    # Row height
    ws.row_dimensions[1].height = 118
    for i in range(2, 2000 + 1):
        ws.row_dimensions[i].height = 30

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

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

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

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

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

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

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

    ws['F3'].font = name_font
    ws['F3'].alignment = b_r_alignment
    ws['F3'] = 'Date:'
    ws.merge_cells("G3:J3")
    for i in range(ord('G'), ord('K')):
        ws[chr(i) + '3'].border = b_border
    ws['G3'].alignment = b_c_alignment
    ws['G3'].font = name_font
    ws['G3'] = reporting_start_datetime_local + "__" + reporting_end_datetime_local

    if "reporting_period" not in report.keys() or \
            "difference_values" not in report['reporting_period'].keys() or \
            len(report['reporting_period']['difference_values']) == 0:
        filename = str(uuid.uuid4()) + '.xlsx'

        wb.save(filename)

        return filename
    #################################################

    has_difference_values_data_flag = True
    if 'difference_values' not in report['reporting_period'].keys() or len(
            report['reporting_period']['difference_values']) == 0:
        has_difference_values_data_flag = False

    current_row_number = 6

    if has_difference_values_data_flag:
        reporting_period_data = report['reporting_period']
        category = report['meter']['energy_category_name']

        ws['B' + str(current_row_number)].font = title_font
        ws['B' + str(current_row_number)] = name + ' 报告期'

        current_row_number += 1

        ws['B' + str(current_row_number)].fill = table_fill
        if not isinstance(category, list):
            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)] = report['meter']['energy_category_name'] + " (" + report['meter'][
                'unit_of_measure'] + ")"

            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)] = '总表消耗'

            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['master_meter_consumption_in_category'], 2)

            current_row_number += 1

            ws['B' + str(current_row_number)].font = title_font
            ws['B' + str(current_row_number)].alignment = c_c_alignment
            ws['B' + str(current_row_number)].border = f_border
            ws['B' + str(current_row_number)] = '分表消耗'

            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['submeters_consumption_in_category'], 2)

            current_row_number += 1

            ws['B' + str(current_row_number)].font = title_font
            ws['B' + str(current_row_number)].alignment = c_c_alignment
            ws['B' + str(current_row_number)].border = f_border
            ws['B' + str(current_row_number)] = '差值'

            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['difference_in_category'], 2)

            current_row_number += 1

            ws['B' + str(current_row_number)].font = title_font
            ws['B' + str(current_row_number)].alignment = c_c_alignment
            ws['B' + str(current_row_number)].border = f_border
            ws['B' + str(current_row_number)] = '差值百分比'

            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)] = str(
                round(reporting_period_data['percentage_difference'] * 100, 2)) + '%'

            current_row_number += 2

            time = reporting_period_data['timestamps']
            has_time_data_flag = False
            if time is not None and len(time) > 0:
                has_time_data_flag = True

            if has_time_data_flag:

                ws['B' + str(current_row_number)].font = title_font
                ws['B' + str(current_row_number)] = name + ' 详细数据'

                current_row_number += 1
                chart_start_number = current_row_number
                current_row_number = current_row_number + 5
                table_start_number = current_row_number

                ws['B' + str(current_row_number)].fill = table_fill
                ws['B' + str(current_row_number)].font = title_font
                ws['B' + str(current_row_number)].border = f_border
                ws['B' + str(current_row_number)].alignment = c_c_alignment
                ws['B' + str(current_row_number)] = '日期时间'

                ws['C' + str(current_row_number)].fill = table_fill
                ws['C' + str(current_row_number)].font = title_font
                ws['C' + str(current_row_number)].border = f_border
                ws['C' + str(current_row_number)].alignment = c_c_alignment
                ws['C' + str(current_row_number)] = report['meter']['energy_category_name'] + " (" + report['meter'][
                    'unit_of_measure'] + ")"

                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)].border = f_border
                    ws['B' + str(current_row_number)].alignment = c_c_alignment
                    ws['B' + str(current_row_number)] = time[i]

                    ws['C' + str(current_row_number)].font = title_font
                    ws['C' + str(current_row_number)].border = f_border
                    ws['C' + str(current_row_number)].alignment = c_c_alignment
                    ws['C' + str(current_row_number)] = round(reporting_period_data['difference_values'][i], 2)

                    current_row_number += 1

                table_end_number = current_row_number - 1

                bar = BarChart()
                labels = Reference(ws, min_col=2, min_row=table_start_number + 1, max_row=table_end_number)
                bar_data = Reference(ws, min_col=3, min_row=table_start_number, max_row=table_end_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  # 数量显示
                ws.add_chart(bar, "B" + str(chart_start_number))

        else:
            pass

    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		BarChart,
6		Reference,
7		)
8		from openpyxl.styles import PatternFill, Border, Side, Alignment, Protection, Font
9		from openpyxl.drawing.image import Image
10		from openpyxl import Workbook
11		from openpyxl.chart.label import DataLabelList
12
13
14		####################################################################################################################
15		# PROCEDURES
16		# Step 1: Validate the report data
17		# Step 2: Generate excelexporters file
18		# Step 3: Encode the excelexporters file to Base64
19		####################################################################################################################
20
21	View Code Duplication	def export(result, name, reporting_start_datetime_local, reporting_end_datetime_local, period_type):
		0 ignored issues – show Duplication introduced 2021-02-05 07:58 UTC by Report Bug Copy Issue Report This code seems to be duplicated in your project. Loading history...
22		####################################################################################################################
23		# Step 1: Validate the report data
24		####################################################################################################################
25		if result is None:
26		return None
27
28		####################################################################################################################
29		# Step 2: Generate excel file from the report data
30		####################################################################################################################
31		filename = generate_excel(result,
32		name,
33		reporting_start_datetime_local,
34		reporting_end_datetime_local,
35		period_type)
36		####################################################################################################################
37		# Step 3: Encode the excel file to Base64
38		####################################################################################################################
39		try:
40		with open(filename, 'rb') as binary_file:
41		binary_file_data = binary_file.read()
42		except IOError as ex:
43		pass
44
45		# Base64 encode the bytes
46		base64_encoded_data = base64.b64encode(binary_file_data)
		0 ignored issues – show introduced 2021-02-05 07:58 UTC by Report Bug Copy Issue Report The variable `binary_file_data` does not seem to be defined for all execution paths. Loading history...
47		# get the Base64 encoded data using human-readable characters.
48		base64_message = base64_encoded_data.decode('utf-8')
49		# delete the file from server
50		try:
51		os.remove(filename)
52		except NotImplementedError as ex:
53		pass
54		return base64_message
55
56
57		def generate_excel(report, name, reporting_start_datetime_local, reporting_end_datetime_local, period_type):
58		wb = Workbook()
59		ws = wb.active
60
61		# Row height
62		ws.row_dimensions[1].height = 118
63		for i in range(2, 2000 + 1):
64		ws.row_dimensions[i].height = 30
65
66		# Col width
67		ws.column_dimensions['A'].width = 1.5
68
69		for i in range(ord('B'), ord('I')):
70		ws.column_dimensions[chr(i)].width = 15.0
71
72		# Font
73		name_font = Font(name='Constantia', size=15, bold=True)
74		title_font = Font(name='宋体', size=15, bold=True)
75		data_font = Font(name='Franklin Gothic Book', size=11)
76
77		table_fill = PatternFill(fill_type='solid', fgColor='1F497D')
78		f_border = Border(left=Side(border_style='medium', color='00000000'),
79		right=Side(border_style='medium', color='00000000'),
80		bottom=Side(border_style='medium', color='00000000'),
81		top=Side(border_style='medium', color='00000000')
82		)
83		b_border = Border(
84		bottom=Side(border_style='medium', color='00000000'),
85		)
86
87		b_c_alignment = Alignment(vertical='bottom',
88		horizontal='center',
89		text_rotation=0,
90		wrap_text=False,
91		shrink_to_fit=False,
92		indent=0)
93		c_c_alignment = Alignment(vertical='center',
94		horizontal='center',
95		text_rotation=0,
96		wrap_text=False,
97		shrink_to_fit=False,
98		indent=0)
99		b_r_alignment = Alignment(vertical='bottom',
100		horizontal='right',
101		text_rotation=0,
102		wrap_text=False,
103		shrink_to_fit=False,
104		indent=0)
105		c_r_alignment = Alignment(vertical='bottom',
106		horizontal='center',
107		text_rotation=0,
108		wrap_text=False,
109		shrink_to_fit=False,
110		indent=0)
111		# Img
112		img = Image("excelexporters/myems.png")
113		ws.add_image(img, 'B1')
114
115		# Title
116		ws['B3'].font = name_font
117		ws['B3'].alignment = b_r_alignment
118		ws['B3'] = 'Name:'
119		ws['C3'].border = b_border
120		ws['C3'].alignment = b_c_alignment
121		ws['C3'].font = name_font
122		ws['C3'] = name
123
124		ws['D3'].font = name_font
125		ws['D3'].alignment = b_r_alignment
126		ws['D3'] = 'Period:'
127		ws['E3'].border = b_border
128		ws['E3'].alignment = b_c_alignment
129		ws['E3'].font = name_font
130		ws['E3'] = period_type
131
132		ws['F3'].font = name_font
133		ws['F3'].alignment = b_r_alignment
134		ws['F3'] = 'Date:'
135		ws.merge_cells("G3:J3")
136		for i in range(ord('G'), ord('K')):
137		ws[chr(i) + '3'].border = b_border
138		ws['G3'].alignment = b_c_alignment
139		ws['G3'].font = name_font
140		ws['G3'] = reporting_start_datetime_local + "__" + reporting_end_datetime_local
141
142		if "reporting_period" not in report.keys() or \
143		"difference_values" not in report['reporting_period'].keys() or \
144		len(report['reporting_period']['difference_values']) == 0:
145		filename = str(uuid.uuid4()) + '.xlsx'
		0 ignored issues – show Comprehensibility Best Practice introduced 2021-02-05 07:58 UTC by Report Bug Copy Issue Report The variable `str` does not seem to be defined. Loading history...
146		wb.save(filename)
147
148		return filename
149		#################################################
150
151		has_difference_values_data_flag = True
152		if 'difference_values' not in report['reporting_period'].keys() or len(
153		report['reporting_period']['difference_values']) == 0:
154		has_difference_values_data_flag = False
155
156		current_row_number = 6
157
158		if has_difference_values_data_flag:
159		reporting_period_data = report['reporting_period']
160		category = report['meter']['energy_category_name']
161
162		ws['B' + str(current_row_number)].font = title_font
163		ws['B' + str(current_row_number)] = name + ' 报告期'
164
165		current_row_number += 1
166
167		ws['B' + str(current_row_number)].fill = table_fill
168		if not isinstance(category, list):
169		ws['C' + str(current_row_number)].fill = table_fill
170		ws['C' + str(current_row_number)].font = name_font
171		ws['C' + str(current_row_number)].alignment = c_c_alignment
172		ws['C' + str(current_row_number)].border = f_border
173		ws['C' + str(current_row_number)] = report['meter']['energy_category_name'] + " (" + report['meter'][
174		'unit_of_measure'] + ")"
175
176		current_row_number += 1
177
178		ws['B' + str(current_row_number)].font = title_font
179		ws['B' + str(current_row_number)].alignment = c_c_alignment
180		ws['B' + str(current_row_number)].border = f_border
181		ws['B' + str(current_row_number)] = '总表消耗'
182
183		ws['C' + str(current_row_number)].font = name_font
184		ws['C' + str(current_row_number)].alignment = c_c_alignment
185		ws['C' + str(current_row_number)].border = f_border
186		ws['C' + str(current_row_number)] = round(reporting_period_data['master_meter_consumption_in_category'], 2)
187
188		current_row_number += 1
189
190		ws['B' + str(current_row_number)].font = title_font
191		ws['B' + str(current_row_number)].alignment = c_c_alignment
192		ws['B' + str(current_row_number)].border = f_border
193		ws['B' + str(current_row_number)] = '分表消耗'
194
195		ws['C' + str(current_row_number)].font = name_font
196		ws['C' + str(current_row_number)].alignment = c_c_alignment
197		ws['C' + str(current_row_number)].border = f_border
198		ws['C' + str(current_row_number)] = round(reporting_period_data['submeters_consumption_in_category'], 2)
199
200		current_row_number += 1
201
202		ws['B' + str(current_row_number)].font = title_font
203		ws['B' + str(current_row_number)].alignment = c_c_alignment
204		ws['B' + str(current_row_number)].border = f_border
205		ws['B' + str(current_row_number)] = '差值'
206
207		ws['C' + str(current_row_number)].font = name_font
208		ws['C' + str(current_row_number)].alignment = c_c_alignment
209		ws['C' + str(current_row_number)].border = f_border
210		ws['C' + str(current_row_number)] = round(reporting_period_data['difference_in_category'], 2)
211
212		current_row_number += 1
213
214		ws['B' + str(current_row_number)].font = title_font
215		ws['B' + str(current_row_number)].alignment = c_c_alignment
216		ws['B' + str(current_row_number)].border = f_border
217		ws['B' + str(current_row_number)] = '差值百分比'
218
219		ws['C' + str(current_row_number)].font = name_font
220		ws['C' + str(current_row_number)].alignment = c_c_alignment
221		ws['C' + str(current_row_number)].border = f_border
222		ws['C' + str(current_row_number)] = str(
223		round(reporting_period_data['percentage_difference'] * 100, 2)) + '%'
224
225		current_row_number += 2
226
227		time = reporting_period_data['timestamps']
228		has_time_data_flag = False
229		if time is not None and len(time) > 0:
230		has_time_data_flag = True
231
232		if has_time_data_flag:
233
234		ws['B' + str(current_row_number)].font = title_font
235		ws['B' + str(current_row_number)] = name + ' 详细数据'
236
237		current_row_number += 1
238		chart_start_number = current_row_number
239		current_row_number = current_row_number + 5
240		table_start_number = current_row_number
241
242		ws['B' + str(current_row_number)].fill = table_fill
243		ws['B' + str(current_row_number)].font = title_font
244		ws['B' + str(current_row_number)].border = f_border
245		ws['B' + str(current_row_number)].alignment = c_c_alignment
246		ws['B' + str(current_row_number)] = '日期时间'
247
248		ws['C' + str(current_row_number)].fill = table_fill
249		ws['C' + str(current_row_number)].font = title_font
250		ws['C' + str(current_row_number)].border = f_border
251		ws['C' + str(current_row_number)].alignment = c_c_alignment
252		ws['C' + str(current_row_number)] = report['meter']['energy_category_name'] + " (" + report['meter'][
253		'unit_of_measure'] + ")"
254
255		current_row_number += 1
256
257		for i in range(0, len(time)):
258		ws['B' + str(current_row_number)].font = title_font
259		ws['B' + str(current_row_number)].border = f_border
260		ws['B' + str(current_row_number)].alignment = c_c_alignment
261		ws['B' + str(current_row_number)] = time[i]
262
263		ws['C' + str(current_row_number)].font = title_font
264		ws['C' + str(current_row_number)].border = f_border
265		ws['C' + str(current_row_number)].alignment = c_c_alignment
266		ws['C' + str(current_row_number)] = round(reporting_period_data['difference_values'][i], 2)
267
268		current_row_number += 1
269
270		table_end_number = current_row_number - 1
271
272		bar = BarChart()
273		labels = Reference(ws, min_col=2, min_row=table_start_number + 1, max_row=table_end_number)
274		bar_data = Reference(ws, min_col=3, min_row=table_start_number, max_row=table_end_number)
275		bar.add_data(bar_data, titles_from_data=True)
276		bar.set_categories(labels)
277		bar.height = 5.25
278		bar.width = len(time)
279		bar.dLbls = DataLabelList()
280		bar.dLbls.showVal = True # 数量显示
281		ws.add_chart(bar, "B" + str(chart_start_number))
282
283		else:
284		pass
285
286		filename = str(uuid.uuid4()) + '.xlsx'
287		wb.save(filename)
288
289		return filename
290

myems / myems-api

Push — master ( b6899e...8a8add )

generate_excel() F

Complexity

Size

Duplication

Importance

How to fix Long Method Complexity

Long Method

Complexity

Duplication Side-by-Side

Filter issues like