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

Passed

Push — master ( 7b66e6...501a6a )

by Guangyu

created 2022-09-03 03:36 UTC

excelexporters.metercomparison.generate_excel() F

↳ Parent: excelexporters.metercomparison

Complexity

Conditions

Size

Total Lines	443
Code Lines	318

Duplication

Lines	101
Ratio	22.8 %

Importance

Changes

Metric	Value
eloc	318
dl	101
loc	443
rs	0
c	0
b	0
f	0
cc	45
nop	6

How to fix Long Method Complexity

import base64
import uuid
import os
import re
from openpyxl.chart import LineChart, 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
import openpyxl.utils.cell as format_cell


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

def export(result, name1, name2, 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,
                              name1,
                              name2,
                              reporting_start_datetime_local,
                              reporting_end_datetime_local,
                              period_type)
    ####################################################################################################################
    # Step 3: Encode the excel file to Base64
    ####################################################################################################################
    binary_file_data = b''
    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, name1, name2, reporting_start_datetime_local, reporting_end_datetime_local, period_type):

    wb = Workbook()
    ws = wb.active
    ws.title = "MeterComaprison"
    # todo: add sheets for meter2
    # Row height
    ws.row_dimensions[1].height = 102
    for i in range(2, 2000 + 1):
        ws.row_dimensions[i].height = 42

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

    ws.column_dimensions['B'].width = 25.0

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

    # Font
    name_font = Font(name='Arial', size=15, bold=True)
    title_font = Font(name='Arial', size=15, bold=True)

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

    b_c_alignment = Alignment(vertical='bottom',
                              horizontal='center',
                              text_rotation=0,
                              wrap_text=True,
                              shrink_to_fit=False,
                              indent=0)
    c_c_alignment = Alignment(vertical='center',
                              horizontal='center',
                              text_rotation=0,
                              wrap_text=True,
                              shrink_to_fit=False,
                              indent=0)
    b_r_alignment = Alignment(vertical='bottom',
                              horizontal='right',
                              text_rotation=0,
                              wrap_text=True,
                              shrink_to_fit=False,
                              indent=0)

    # Img
    img = Image("excelexporters/myems.png")
    ws.add_image(img, 'A1')

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

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

    ws['B4'].alignment = b_r_alignment
    ws['B4'] = 'Reporting Start Datetime:'
    ws['C4'].border = b_border
    ws['C4'].alignment = b_c_alignment
    ws['C4'] = reporting_start_datetime_local

    ws['D4'].alignment = b_r_alignment
    ws['D4'] = 'Reporting End Datetime:'
    ws['E4'].border = b_border
    ws['E4'].alignment = b_c_alignment
    ws['E4'] = reporting_end_datetime_local

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

        wb.save(filename)

        return filename
    ####################################################################################################################
    # First: Consumption
    # 6: title
    # 7: table title
    # 8~9 table_data
    ####################################################################################################################
    has_energy_data_flag = True

    if "values" not in report['reporting_period1'].keys() or len(report['reporting_period1']['values']) == 0:
        has_energy_data_flag = False

    if has_energy_data_flag:
        ws['B6'].font = title_font
        ws['B6'] = name1 + ' ' + 'Consumption'

        reporting_period_data1 = report['reporting_period1']

        category = report['meter1']['energy_category_name']
        ca_len = len(category)

        ws.row_dimensions[7].height = 60

        ws['B7'].fill = table_fill
        ws['B7'].border = f_border

        ws['B8'].font = title_font
        ws['B8'].alignment = c_c_alignment
        ws['B8'] = 'Consumption'
        ws['B8'].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'] = report['meter1']['energy_category_name'] + " (" + report['meter1']['unit_of_measure'] + ")"
            ws[col + '7'].border = f_border

            ws[col + '8'].font = name_font
            ws[col + '8'].alignment = c_c_alignment
            ws[col + '8'] = round(reporting_period_data1['total_in_category'], 2)
            ws[col + '8'].border = f_border

        # TCE TCO2E
        end_col = col
    else:
        for i in range(6, 9 + 1):
            ws.row_dimensions[i].height = 0.1
    ####################################################################################################################
    # Second: Detailed Data
    # 11: title
    # 12 ~ 16: chart
    # 18: table title
    # 19~43: table_data
    ####################################################################################################################
    has_energy_detail_flag = True
    reporting_period_data1 = report['reporting_period1']
    times = reporting_period_data1['timestamps']

    if "values" not in report['reporting_period1'].keys() or len(report['reporting_period1']['values']) == 0:
        has_energy_detail_flag = False

    if has_energy_detail_flag:

        reporting_period_data1 = report['reporting_period1']
        category = report['meter1']['energy_category_name']
        ca_len = len(category)
        parameters_names_len = len(report['parameters1']['names'])
        parameters_data = report['parameters1']
        parameters_parameters_datas_len = 0
        for i in range(0, parameters_names_len):
            if len(parameters_data['timestamps'][i]) == 0:
                continue
            parameters_parameters_datas_len += 1
        start_detail_data_row_num = 13 + (parameters_parameters_datas_len + ca_len) * 6
        ws['B11'].font = title_font
        ws['B11'] = name1 + 'Detailed Data'

        ws.row_dimensions[start_detail_data_row_num].height = 60

        ws['B' + str(start_detail_data_row_num)].fill = table_fill
        ws['B' + str(start_detail_data_row_num)].font = title_font
        ws['B' + str(start_detail_data_row_num)].border = f_border
        ws['B' + str(start_detail_data_row_num)].alignment = c_c_alignment
        ws['B' + str(start_detail_data_row_num)] = 'Datetime'
        time = times
        has_data = False
        max_row = 0
        if len(time) > 0:
            has_data = True
            max_row = start_detail_data_row_num + len(time)

        if has_data:
            for i in range(0, len(time)):
                col = 'B'
                row = str(start_detail_data_row_num + 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):
                # 12 title
                col = chr(ord('C') + i)

                ws[col + str(start_detail_data_row_num)].fill = table_fill
                ws[col + str(start_detail_data_row_num)].font = title_font
                ws[col + str(start_detail_data_row_num)].alignment = c_c_alignment
                ws[col + str(start_detail_data_row_num)] = report['meter1']['energy_category_name'] + \
                    " (" + report['meter1']['unit_of_measure'] + ")"
                ws[col + str(start_detail_data_row_num)].border = f_border

                # 13 data
                time = times
                time_len = len(time)

                for j in range(0, time_len):
                    row = str(start_detail_data_row_num + 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_data1['values'][j], 2)
                    ws[col + row].border = f_border
            # line
            # 13~: line
            line = LineChart()
            line.title = 'Reporting Period Consumption - ' + report['meter1']['energy_category_name'] + \
                " (" + report['meter1']['unit_of_measure'] + ")"
            labels = Reference(ws, min_col=2, min_row=start_detail_data_row_num + 1, max_row=max_row)
            bar_data = Reference(ws, min_col=3, min_row=start_detail_data_row_num, max_row=max_row)
            line.add_data(bar_data, titles_from_data=True)
            line.set_categories(labels)
            line_data = line.series[0]
            line_data.marker.symbol = "circle"
            line_data.smooth = True
            line.x_axis.crosses = 'min'
            line.height = 8.25
            line.width = 24
            line.dLbls = DataLabelList()
            line.dLbls = DataLabelList()
            line.dLbls.dLblPos = 't'
            line.dLbls.showVal = True
            line.dLbls.showPercent = False
            ws.add_chart(line, "B12")

            col = 'B'
            row = str(start_detail_data_row_num + 1 + len(time))

            ws[col + row].font = title_font
            ws[col + row].alignment = c_c_alignment
            ws[col + row] = 'Total'
            ws[col + row].border = f_border

            for i in range(0, ca_len):
                col = chr(ord(col) + 1)
                ws[col + row].font = title_font
                ws[col + row].alignment = c_c_alignment
                ws[col + row] = round(reporting_period_data1['total_in_category'], 2)
                ws[col + row].border = f_border

    else:
        for i in range(11, 43 + 1):
            ws.row_dimensions[i].height = 0.0

    ####################################################################################################################
    has_parameters_names_and_timestamps_and_values_data = True
    # 12 is the starting line number of the last line chart in the report period
    category = report['meter1']['energy_category_name']
    time_len = len(reporting_period_data1['timestamps'])
    ca_len = len(category)
    current_sheet_parameters_row_number = 12 + ca_len * 6
    if 'parameters1' not in report.keys() or \
            report['parameters1'] is None or \
            'names' not in report['parameters1'].keys() or \
            report['parameters1']['names'] is None or \
            len(report['parameters1']['names']) == 0 or \
            'timestamps' not in report['parameters1'].keys() or \
            report['parameters1']['timestamps'] is None or \
            len(report['parameters1']['timestamps']) == 0 or \
            'values' not in report['parameters1'].keys() or \
            report['parameters1']['values'] is None or \
            len(report['parameters1']['values']) == 0 or \
            timestamps_data_all_equal_0(report['parameters1']['timestamps']):
        has_parameters_names_and_timestamps_and_values_data = False

    if has_parameters_names_and_timestamps_and_values_data:

        parameters_data1 = report['parameters1']

        parameters_names_len = len(parameters_data1['names'])

        file_name = (re.sub(r'[^A-Z]', '', ws.title))+'_'
        parameters_ws = wb.create_sheet(file_name + 'Parameters1')

        parameters_timestamps_data_max_len = \
            get_parameters_timestamps_lists_max_len(list(parameters_data1['timestamps']))

        # Row height
        parameters_ws.row_dimensions[1].height = 102
        for i in range(2, 7 + 1):
            parameters_ws.row_dimensions[i].height = 42

        for i in range(8, parameters_timestamps_data_max_len + 10):
            parameters_ws.row_dimensions[i].height = 60

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

        parameters_ws.column_dimensions['B'].width = 25.0

        for i in range(3, 12 + parameters_names_len * 3):
            parameters_ws.column_dimensions[format_cell.get_column_letter(i)].width = 15.0

        # Img
        img = Image("excelexporters/myems.png")
        parameters_ws.add_image(img, 'A1')

        # Title
        parameters_ws['B3'].alignment = b_r_alignment
        parameters_ws['B3'] = 'Name:'
        parameters_ws['C3'].border = b_border
        parameters_ws['C3'].alignment = b_c_alignment
        parameters_ws['C3'] = name1

        parameters_ws['D3'].alignment = b_r_alignment
        parameters_ws['D3'] = 'Period:'
        parameters_ws['E3'].border = b_border
        parameters_ws['E3'].alignment = b_c_alignment
        parameters_ws['E3'] = period_type

        parameters_ws['B4'].alignment = b_r_alignment
        parameters_ws['B4'] = 'Reporting Start Datetime:'
        parameters_ws['C4'].border = b_border
        parameters_ws['C4'].alignment = b_c_alignment
        parameters_ws['C4'] = reporting_start_datetime_local

        parameters_ws['D4'].alignment = b_r_alignment
        parameters_ws['D4'] = 'Reporting End Datetime:'
        parameters_ws['E4'].border = b_border
        parameters_ws['E4'].alignment = b_c_alignment
        parameters_ws['E4'] = reporting_end_datetime_local

        parameters_ws_current_row_number = 6

        parameters_ws['B' + str(parameters_ws_current_row_number)].font = title_font
        parameters_ws['B' + str(parameters_ws_current_row_number)] = name1 + ' ' + 'Parameters'

        parameters_ws_current_row_number += 1

        parameters_table_start_row_number = parameters_ws_current_row_number

        parameters_ws.row_dimensions[parameters_ws_current_row_number].height = 80

        parameters_ws_current_row_number += 1

        table_current_col_number = 2

        for i in range(0, parameters_names_len):

            if len(parameters_data1['timestamps'][i]) == 0:
                continue

            col = format_cell.get_column_letter(table_current_col_number)

            parameters_ws[col + str(parameters_ws_current_row_number - 1)].fill = table_fill
            parameters_ws[col + str(parameters_ws_current_row_number - 1)].border = f_border

            col = format_cell.get_column_letter(table_current_col_number + 1)

            parameters_ws[col + str(parameters_ws_current_row_number - 1)].fill = table_fill
            parameters_ws[col + str(parameters_ws_current_row_number - 1)].border = f_border
            parameters_ws[col + str(parameters_ws_current_row_number - 1)].font = name_font
            parameters_ws[col + str(parameters_ws_current_row_number - 1)].alignment = c_c_alignment
            parameters_ws[col + str(parameters_ws_current_row_number - 1)] = parameters_data1['names'][i]

            table_current_row_number = parameters_ws_current_row_number

            for j, value in enumerate(list(parameters_data1['timestamps'][i])):
                col = format_cell.get_column_letter(table_current_col_number)

                parameters_ws[col + str(table_current_row_number)].border = f_border
                parameters_ws[col + str(table_current_row_number)].font = title_font
                parameters_ws[col + str(table_current_row_number)].alignment = c_c_alignment
                parameters_ws[col + str(table_current_row_number)] = value

                col = format_cell.get_column_letter(table_current_col_number + 1)

                parameters_ws[col + str(table_current_row_number)].border = f_border
                parameters_ws[col + str(table_current_row_number)].font = title_font
                parameters_ws[col + str(table_current_row_number)].alignment = c_c_alignment
                try:
                    parameters_ws[col + str(table_current_row_number)] = round(parameters_data1['values'][i][j], 2)
                except Exception as e:
                    print('error 1 in excelexporters\meterenergy: ' + str(e))
                
                table_current_row_number += 1

            table_current_col_number = table_current_col_number + 3

        ################################################################################################################
        # parameters chart and parameters table
        ################################################################################################################

        ws['B' + str(current_sheet_parameters_row_number)].font = title_font
        ws['B' + str(current_sheet_parameters_row_number)] = name1 + ' ' + 'Parameters'

        current_sheet_parameters_row_number += 1

        chart_start_row_number = current_sheet_parameters_row_number

        col_index = 0

        for i in range(0, parameters_names_len):

            if len(parameters_data1['timestamps'][i]) == 0:
                continue

            line = LineChart()
            data_col = 3 + col_index * 3
            labels_col = 2 + col_index * 3
            col_index += 1
            line.title = 'Parameters - ' + \
                         parameters_ws.cell(row=parameters_table_start_row_number, column=data_col).value
            labels = Reference(parameters_ws, min_col=labels_col, min_row=parameters_table_start_row_number + 1,
                               max_row=(len(parameters_data1['timestamps'][i]) + parameters_table_start_row_number))
            line_data = Reference(parameters_ws, min_col=data_col, min_row=parameters_table_start_row_number,
                                  max_row=(len(parameters_data1['timestamps'][i]) + parameters_table_start_row_number))
            line.add_data(line_data, titles_from_data=True)
            line.set_categories(labels)
            line_data = line.series[0]
            line_data.marker.symbol = "circle"
            line_data.smooth = True
            line.x_axis.crosses = 'min'
            line.height = 8.25
            line.width = 24
            line.dLbls = DataLabelList()
            line.dLbls.dLblPos = 't'
            line.dLbls.showVal = False
            line.dLbls.showPercent = False
            chart_col = 'B'
            chart_cell = chart_col + str(chart_start_row_number)
            chart_start_row_number += 6
            ws.add_chart(line, chart_cell)

        current_sheet_parameters_row_number = chart_start_row_number

        current_sheet_parameters_row_number += 1

    filename = str(uuid.uuid4()) + '.xlsx'
    wb.save(filename)

    return filename


def timestamps_data_all_equal_0(lists):
    for i, value in enumerate(list(lists)):
        if len(value) > 0:
            return False

    return True


def get_parameters_timestamps_lists_max_len(parameters_timestamps_lists):
    max_len = 0
    for i, value in enumerate(list(parameters_timestamps_lists)):
        if len(value) > max_len:
            max_len = len(value)

    return max_len


def timestamps_data_not_equal_0(lists):
    number = 0
    for i, value in enumerate(list(lists)):
        if len(value) > 0:
            number += 1
    return number


1		import base64
2		import uuid
3		import os
4		import re
5		from openpyxl.chart import LineChart, Reference
6		from openpyxl.styles import PatternFill, Border, Side, Alignment, Font
7		from openpyxl.drawing.image import Image
8		from openpyxl import Workbook
9		from openpyxl.chart.label import DataLabelList
10		import openpyxl.utils.cell as format_cell
11
12
13		########################################################################################################################
14		# PROCEDURES
15		# Step 1: Validate the report data
16		# Step 2: Generate excelexporters file
17		# Step 3: Encode the excelexporters file to Base64
18		########################################################################################################################
19
20	View Code Duplication	def export(result, name1, name2, reporting_start_datetime_local, reporting_end_datetime_local, period_type):
		0 ignored issues – show Duplication introduced 2022-09-03 03:44 UTC by Report Bug Copy Issue Report This code seems to be duplicated in your project. Loading history...
21		####################################################################################################################
22		# Step 1: Validate the report data
23		####################################################################################################################
24		if result is None:
25		return None
26
27		####################################################################################################################
28		# Step 2: Generate excel file from the report data
29		####################################################################################################################
30		filename = generate_excel(result,
31		name1,
32		name2,
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		binary_file_data = b''
40		try:
41		with open(filename, 'rb') as binary_file:
42		binary_file_data = binary_file.read()
43		except IOError as ex:
44		pass
45
46		# Base64 encode the bytes
47		base64_encoded_data = base64.b64encode(binary_file_data)
48		# get the Base64 encoded data using human-readable characters.
49		base64_message = base64_encoded_data.decode('utf-8')
50		# delete the file from server
51		try:
52		os.remove(filename)
53		except NotImplementedError as ex:
54		pass
55		return base64_message
56
57
58		def generate_excel(report, name1, name2, reporting_start_datetime_local, reporting_end_datetime_local, period_type):
59
60		wb = Workbook()
61		ws = wb.active
62		ws.title = "MeterComaprison"
63		# todo: add sheets for meter2
64		# Row height
65		ws.row_dimensions[1].height = 102
66		for i in range(2, 2000 + 1):
67		ws.row_dimensions[i].height = 42
68
69		# Col width
70		ws.column_dimensions['A'].width = 1.5
71
72		ws.column_dimensions['B'].width = 25.0
73
74		for i in range(ord('C'), ord('L')):
75		ws.column_dimensions[chr(i)].width = 15.0
76
77		# Font
78		name_font = Font(name='Arial', size=15, bold=True)
79		title_font = Font(name='Arial', size=15, bold=True)
80
81		table_fill = PatternFill(fill_type='solid', fgColor='1F497D')
82		f_border = Border(left=Side(border_style='medium', color='00000000'),
83		right=Side(border_style='medium', color='00000000'),
84		bottom=Side(border_style='medium', color='00000000'),
85		top=Side(border_style='medium', color='00000000')
86		)
87		b_border = Border(
88		bottom=Side(border_style='medium', color='00000000'),
89		)
90
91		b_c_alignment = Alignment(vertical='bottom',
92		horizontal='center',
93		text_rotation=0,
94		wrap_text=True,
95		shrink_to_fit=False,
96		indent=0)
97		c_c_alignment = Alignment(vertical='center',
98		horizontal='center',
99		text_rotation=0,
100		wrap_text=True,
101		shrink_to_fit=False,
102		indent=0)
103		b_r_alignment = Alignment(vertical='bottom',
104		horizontal='right',
105		text_rotation=0,
106		wrap_text=True,
107		shrink_to_fit=False,
108		indent=0)
109
110		# Img
111		img = Image("excelexporters/myems.png")
112		ws.add_image(img, 'A1')
113
114		# Title
115		ws['B3'].alignment = b_r_alignment
116		ws['B3'] = 'Name:'
117		ws['C3'].border = b_border
118		ws['C3'].alignment = b_c_alignment
119		ws['C3'] = name1
120
121		ws['D3'].alignment = b_r_alignment
122		ws['D3'] = 'Period:'
123		ws['E3'].border = b_border
124		ws['E3'].alignment = b_c_alignment
125		ws['E3'] = period_type
126
127		ws['B4'].alignment = b_r_alignment
128		ws['B4'] = 'Reporting Start Datetime:'
129		ws['C4'].border = b_border
130		ws['C4'].alignment = b_c_alignment
131		ws['C4'] = reporting_start_datetime_local
132
133		ws['D4'].alignment = b_r_alignment
134		ws['D4'] = 'Reporting End Datetime:'
135		ws['E4'].border = b_border
136		ws['E4'].alignment = b_c_alignment
137		ws['E4'] = reporting_end_datetime_local
138
139		if "reporting_period1" not in report.keys() or \
140		"values" not in report['reporting_period1'].keys() or len(report['reporting_period1']['values']) == 0:
141		filename = str(uuid.uuid4()) + '.xlsx'
		0 ignored issues – show Comprehensibility Best Practice introduced 2022-09-03 03:44 UTC by Report Bug Copy Issue Report The variable `str` does not seem to be defined. Loading history...
142		wb.save(filename)
143
144		return filename
145		####################################################################################################################
146		# First: Consumption
147		# 6: title
148		# 7: table title
149		# 8~9 table_data
150		####################################################################################################################
151		has_energy_data_flag = True
152
153		if "values" not in report['reporting_period1'].keys() or len(report['reporting_period1']['values']) == 0:
154		has_energy_data_flag = False
155
156		if has_energy_data_flag:
157		ws['B6'].font = title_font
158		ws['B6'] = name1 + ' ' + 'Consumption'
159
160		reporting_period_data1 = report['reporting_period1']
161
162		category = report['meter1']['energy_category_name']
163		ca_len = len(category)
164
165		ws.row_dimensions[7].height = 60
166
167		ws['B7'].fill = table_fill
168		ws['B7'].border = f_border
169
170		ws['B8'].font = title_font
171		ws['B8'].alignment = c_c_alignment
172		ws['B8'] = 'Consumption'
173		ws['B8'].border = f_border
174
175		col = ''
176
177		for i in range(0, ca_len):
178		col = chr(ord('C') + i)
179		row = '7'
180		cell = col + row
181		ws[col + '7'].fill = table_fill
182		ws[col + '7'].font = name_font
183		ws[col + '7'].alignment = c_c_alignment
184		ws[col + '7'] = report['meter1']['energy_category_name'] + " (" + report['meter1']['unit_of_measure'] + ")"
185		ws[col + '7'].border = f_border
186
187		ws[col + '8'].font = name_font
188		ws[col + '8'].alignment = c_c_alignment
189		ws[col + '8'] = round(reporting_period_data1['total_in_category'], 2)
190		ws[col + '8'].border = f_border
191
192		# TCE TCO2E
193		end_col = col
194		else:
195		for i in range(6, 9 + 1):
196		ws.row_dimensions[i].height = 0.1
197		####################################################################################################################
198		# Second: Detailed Data
199		# 11: title
200		# 12 ~ 16: chart
201		# 18: table title
202		# 19~43: table_data
203		####################################################################################################################
204		has_energy_detail_flag = True
205		reporting_period_data1 = report['reporting_period1']
206		times = reporting_period_data1['timestamps']
207
208		if "values" not in report['reporting_period1'].keys() or len(report['reporting_period1']['values']) == 0:
209		has_energy_detail_flag = False
210
211	View Code Duplication	if has_energy_detail_flag:
		0 ignored issues – show Duplication introduced 2022-09-03 03:44 UTC by Report Bug Copy Issue Report This code seems to be duplicated in your project. Loading history...
212		reporting_period_data1 = report['reporting_period1']
213		category = report['meter1']['energy_category_name']
214		ca_len = len(category)
215		parameters_names_len = len(report['parameters1']['names'])
216		parameters_data = report['parameters1']
217		parameters_parameters_datas_len = 0
218		for i in range(0, parameters_names_len):
219		if len(parameters_data['timestamps'][i]) == 0:
220		continue
221		parameters_parameters_datas_len += 1
222		start_detail_data_row_num = 13 + (parameters_parameters_datas_len + ca_len) * 6
223		ws['B11'].font = title_font
224		ws['B11'] = name1 + 'Detailed Data'
225
226		ws.row_dimensions[start_detail_data_row_num].height = 60
227
228		ws['B' + str(start_detail_data_row_num)].fill = table_fill
229		ws['B' + str(start_detail_data_row_num)].font = title_font
230		ws['B' + str(start_detail_data_row_num)].border = f_border
231		ws['B' + str(start_detail_data_row_num)].alignment = c_c_alignment
232		ws['B' + str(start_detail_data_row_num)] = 'Datetime'
233		time = times
234		has_data = False
235		max_row = 0
236		if len(time) > 0:
237		has_data = True
238		max_row = start_detail_data_row_num + len(time)
239
240		if has_data:
241		for i in range(0, len(time)):
242		col = 'B'
243		row = str(start_detail_data_row_num + 1 + i)
244		# col = chr(ord('B') + i)
245		ws[col + row].font = title_font
246		ws[col + row].alignment = c_c_alignment
247		ws[col + row] = time[i]
248		ws[col + row].border = f_border
249
250		for i in range(0, ca_len):
251		# 12 title
252		col = chr(ord('C') + i)
253
254		ws[col + str(start_detail_data_row_num)].fill = table_fill
255		ws[col + str(start_detail_data_row_num)].font = title_font
256		ws[col + str(start_detail_data_row_num)].alignment = c_c_alignment
257		ws[col + str(start_detail_data_row_num)] = report['meter1']['energy_category_name'] + \
258		" (" + report['meter1']['unit_of_measure'] + ")"
259		ws[col + str(start_detail_data_row_num)].border = f_border
260
261		# 13 data
262		time = times
263		time_len = len(time)
264
265		for j in range(0, time_len):
266		row = str(start_detail_data_row_num + 1 + j)
267		# col = chr(ord('B') + i)
268		ws[col + row].font = title_font
269		ws[col + row].alignment = c_c_alignment
270		ws[col + row] = round(reporting_period_data1['values'][j], 2)
271		ws[col + row].border = f_border
272		# line
273		# 13~: line
274		line = LineChart()
275		line.title = 'Reporting Period Consumption - ' + report['meter1']['energy_category_name'] + \
276		" (" + report['meter1']['unit_of_measure'] + ")"
277		labels = Reference(ws, min_col=2, min_row=start_detail_data_row_num + 1, max_row=max_row)
278		bar_data = Reference(ws, min_col=3, min_row=start_detail_data_row_num, max_row=max_row)
279		line.add_data(bar_data, titles_from_data=True)
280		line.set_categories(labels)
281		line_data = line.series[0]
282		line_data.marker.symbol = "circle"
283		line_data.smooth = True
284		line.x_axis.crosses = 'min'
285		line.height = 8.25
286		line.width = 24
287		line.dLbls = DataLabelList()
288		line.dLbls = DataLabelList()
289		line.dLbls.dLblPos = 't'
290		line.dLbls.showVal = True
291		line.dLbls.showPercent = False
292		ws.add_chart(line, "B12")
293
294		col = 'B'
295		row = str(start_detail_data_row_num + 1 + len(time))
296
297		ws[col + row].font = title_font
298		ws[col + row].alignment = c_c_alignment
299		ws[col + row] = 'Total'
300		ws[col + row].border = f_border
301
302		for i in range(0, ca_len):
303		col = chr(ord(col) + 1)
304		ws[col + row].font = title_font
305		ws[col + row].alignment = c_c_alignment
306		ws[col + row] = round(reporting_period_data1['total_in_category'], 2)
307		ws[col + row].border = f_border
308
309		else:
310		for i in range(11, 43 + 1):
311		ws.row_dimensions[i].height = 0.0
312
313		####################################################################################################################
314		has_parameters_names_and_timestamps_and_values_data = True
315		# 12 is the starting line number of the last line chart in the report period
316		category = report['meter1']['energy_category_name']
317		time_len = len(reporting_period_data1['timestamps'])
318		ca_len = len(category)
319		current_sheet_parameters_row_number = 12 + ca_len * 6
320		if 'parameters1' not in report.keys() or \
321		report['parameters1'] is None or \
322		'names' not in report['parameters1'].keys() or \
323		report['parameters1']['names'] is None or \
324		len(report['parameters1']['names']) == 0 or \
325		'timestamps' not in report['parameters1'].keys() or \
326		report['parameters1']['timestamps'] is None or \
327		len(report['parameters1']['timestamps']) == 0 or \
328		'values' not in report['parameters1'].keys() or \
329		report['parameters1']['values'] is None or \
330		len(report['parameters1']['values']) == 0 or \
331		timestamps_data_all_equal_0(report['parameters1']['timestamps']):
332		has_parameters_names_and_timestamps_and_values_data = False
333
334		if has_parameters_names_and_timestamps_and_values_data:
335
336		parameters_data1 = report['parameters1']
337
338		parameters_names_len = len(parameters_data1['names'])
339
340		file_name = (re.sub(r'[^A-Z]', '', ws.title))+'_'
341		parameters_ws = wb.create_sheet(file_name + 'Parameters1')
342
343		parameters_timestamps_data_max_len = \
344		get_parameters_timestamps_lists_max_len(list(parameters_data1['timestamps']))
345
346		# Row height
347		parameters_ws.row_dimensions[1].height = 102
348		for i in range(2, 7 + 1):
349		parameters_ws.row_dimensions[i].height = 42
350
351		for i in range(8, parameters_timestamps_data_max_len + 10):
352		parameters_ws.row_dimensions[i].height = 60
353
354		# Col width
355		parameters_ws.column_dimensions['A'].width = 1.5
356
357		parameters_ws.column_dimensions['B'].width = 25.0
358
359		for i in range(3, 12 + parameters_names_len * 3):
360		parameters_ws.column_dimensions[format_cell.get_column_letter(i)].width = 15.0
361
362		# Img
363		img = Image("excelexporters/myems.png")
364		parameters_ws.add_image(img, 'A1')
365
366		# Title
367		parameters_ws['B3'].alignment = b_r_alignment
368		parameters_ws['B3'] = 'Name:'
369		parameters_ws['C3'].border = b_border
370		parameters_ws['C3'].alignment = b_c_alignment
371		parameters_ws['C3'] = name1
372
373		parameters_ws['D3'].alignment = b_r_alignment
374		parameters_ws['D3'] = 'Period:'
375		parameters_ws['E3'].border = b_border
376		parameters_ws['E3'].alignment = b_c_alignment
377		parameters_ws['E3'] = period_type
378
379		parameters_ws['B4'].alignment = b_r_alignment
380		parameters_ws['B4'] = 'Reporting Start Datetime:'
381		parameters_ws['C4'].border = b_border
382		parameters_ws['C4'].alignment = b_c_alignment
383		parameters_ws['C4'] = reporting_start_datetime_local
384
385		parameters_ws['D4'].alignment = b_r_alignment
386		parameters_ws['D4'] = 'Reporting End Datetime:'
387		parameters_ws['E4'].border = b_border
388		parameters_ws['E4'].alignment = b_c_alignment
389		parameters_ws['E4'] = reporting_end_datetime_local
390
391		parameters_ws_current_row_number = 6
392
393		parameters_ws['B' + str(parameters_ws_current_row_number)].font = title_font
394		parameters_ws['B' + str(parameters_ws_current_row_number)] = name1 + ' ' + 'Parameters'
395
396		parameters_ws_current_row_number += 1
397
398		parameters_table_start_row_number = parameters_ws_current_row_number
399
400		parameters_ws.row_dimensions[parameters_ws_current_row_number].height = 80
401
402		parameters_ws_current_row_number += 1
403
404		table_current_col_number = 2
405
406		for i in range(0, parameters_names_len):
407
408		if len(parameters_data1['timestamps'][i]) == 0:
409		continue
410
411		col = format_cell.get_column_letter(table_current_col_number)
412
413		parameters_ws[col + str(parameters_ws_current_row_number - 1)].fill = table_fill
414		parameters_ws[col + str(parameters_ws_current_row_number - 1)].border = f_border
415
416		col = format_cell.get_column_letter(table_current_col_number + 1)
417
418		parameters_ws[col + str(parameters_ws_current_row_number - 1)].fill = table_fill
419		parameters_ws[col + str(parameters_ws_current_row_number - 1)].border = f_border
420		parameters_ws[col + str(parameters_ws_current_row_number - 1)].font = name_font
421		parameters_ws[col + str(parameters_ws_current_row_number - 1)].alignment = c_c_alignment
422		parameters_ws[col + str(parameters_ws_current_row_number - 1)] = parameters_data1['names'][i]
423
424		table_current_row_number = parameters_ws_current_row_number
425
426		for j, value in enumerate(list(parameters_data1['timestamps'][i])):
427		col = format_cell.get_column_letter(table_current_col_number)
428
429		parameters_ws[col + str(table_current_row_number)].border = f_border
430		parameters_ws[col + str(table_current_row_number)].font = title_font
431		parameters_ws[col + str(table_current_row_number)].alignment = c_c_alignment
432		parameters_ws[col + str(table_current_row_number)] = value
433
434		col = format_cell.get_column_letter(table_current_col_number + 1)
435
436		parameters_ws[col + str(table_current_row_number)].border = f_border
437		parameters_ws[col + str(table_current_row_number)].font = title_font
438		parameters_ws[col + str(table_current_row_number)].alignment = c_c_alignment
439		try:
440		parameters_ws[col + str(table_current_row_number)] = round(parameters_data1['values'][i][j], 2)
441		except Exception as e:
442		print('error 1 in excelexporters\meterenergy: ' + str(e))
443
444		table_current_row_number += 1
445
446		table_current_col_number = table_current_col_number + 3
447
448		################################################################################################################
449		# parameters chart and parameters table
450		################################################################################################################
451
452		ws['B' + str(current_sheet_parameters_row_number)].font = title_font
453		ws['B' + str(current_sheet_parameters_row_number)] = name1 + ' ' + 'Parameters'
454
455		current_sheet_parameters_row_number += 1
456
457		chart_start_row_number = current_sheet_parameters_row_number
458
459		col_index = 0
460
461		for i in range(0, parameters_names_len):
462
463		if len(parameters_data1['timestamps'][i]) == 0:
464		continue
465
466		line = LineChart()
467		data_col = 3 + col_index * 3
468		labels_col = 2 + col_index * 3
469		col_index += 1
470		line.title = 'Parameters - ' + \
471		parameters_ws.cell(row=parameters_table_start_row_number, column=data_col).value
472		labels = Reference(parameters_ws, min_col=labels_col, min_row=parameters_table_start_row_number + 1,
473		max_row=(len(parameters_data1['timestamps'][i]) + parameters_table_start_row_number))
474		line_data = Reference(parameters_ws, min_col=data_col, min_row=parameters_table_start_row_number,
475		max_row=(len(parameters_data1['timestamps'][i]) + parameters_table_start_row_number))
476		line.add_data(line_data, titles_from_data=True)
477		line.set_categories(labels)
478		line_data = line.series[0]
479		line_data.marker.symbol = "circle"
480		line_data.smooth = True
481		line.x_axis.crosses = 'min'
482		line.height = 8.25
483		line.width = 24
484		line.dLbls = DataLabelList()
485		line.dLbls.dLblPos = 't'
486		line.dLbls.showVal = False
487		line.dLbls.showPercent = False
488		chart_col = 'B'
489		chart_cell = chart_col + str(chart_start_row_number)
490		chart_start_row_number += 6
491		ws.add_chart(line, chart_cell)
492
493		current_sheet_parameters_row_number = chart_start_row_number
494
495		current_sheet_parameters_row_number += 1
496
497		filename = str(uuid.uuid4()) + '.xlsx'
498		wb.save(filename)
499
500		return filename
501
502
503		def timestamps_data_all_equal_0(lists):
504		for i, value in enumerate(list(lists)):
505		if len(value) > 0:
506		return False
507
508		return True
509
510
511		def get_parameters_timestamps_lists_max_len(parameters_timestamps_lists):
512		max_len = 0
513		for i, value in enumerate(list(parameters_timestamps_lists)):
514		if len(value) > max_len:
515		max_len = len(value)
516
517		return max_len
518
519
520		def timestamps_data_not_equal_0(lists):
521		number = 0
522		for i, value in enumerate(list(lists)):
523		if len(value) > 0:
524		number += 1
525		return number
526

MyEMS / myems

Push — master ( 7b66e6...501a6a )

excelexporters.metercomparison.generate_excel() F

Complexity

Size

Duplication

Importance

How to fix Long Method Complexity

Long Method

Complexity

Duplication Side-by-Side

Filter issues like