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

Passed

Push — master ( 05a995...b2a2f7 )

unknown

created 2025-11-03 04:21 UTC

excelexporters.powerquality.generate_excel() F

↳ Parent: excelexporters.powerquality

Complexity

Conditions

Size

Total Lines	299
Code Lines	228

Duplication

Lines	73
Ratio	24.41 %

Importance

Changes

Metric	Value
eloc	228
dl	73
loc	299
rs	0
c	0
b	0
f	0
cc	41
nop	6

How to fix Long Method Complexity

"""
Meter Trend Excel Exporter

This module provides functionality to export meter trend data to Excel format.
It generates comprehensive reports showing energy consumption trends for meters
with detailed analysis and visualizations.

Key Features:
- Meter energy consumption trend analysis
- Base period vs reporting period comparison
- Trend breakdown by energy categories
- Detailed data with line charts
- Multi-language support
- Base64 encoding for file transmission

The exported Excel file includes:
- Meter trend summary
- Base period comparison data
- Trend breakdown by energy categories
- Detailed time-series data with line charts
- Parameter data (if available)
"""

import base64
from core.utilities import get_translation
import os
import re
import uuid
import openpyxl.utils.cell as format_cell
from openpyxl import Workbook
from openpyxl.chart import LineChart, Reference
from openpyxl.drawing.image import Image
from openpyxl.styles import PatternFill, Border, Side, Alignment, Font
from core.utilities import round2

########################################################################################################################
# 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,
           language):
    ####################################################################################################################
    # 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,
                              language)

    ####################################################################################################################
    # 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:
        print(str(ex))

    # 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:
        print(str(ex))
    return base64_message


def generate_excel(report,
                   name,
                   reporting_start_datetime_local,
                   reporting_end_datetime_local,
                   period_type,
                   language):

    trans = get_translation(language)
    trans.install()
    _ = trans.gettext

    wb = Workbook()
    ws = wb.active
    ws.title = "MeterTrend"

    # Row height
    ws.row_dimensions[1].height = 102
    for i in range(2, 8):
        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('V')):
        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='90ee90')
    f_border = Border(left=Side(border_style='medium'),
                      right=Side(border_style='medium'),
                      bottom=Side(border_style='medium'),
                      top=Side(border_style='medium')
                      )
    b_border = Border(
        bottom=Side(border_style='medium'),
    )

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

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

    # Title
    ws['B3'].alignment = b_r_alignment
    ws['B3'] = _('Name') + ':'
    ws['C3'].border = b_border
    ws['C3'].alignment = b_c_alignment
    ws['C3'] = name

    ws['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['B5'].alignment = b_r_alignment
    ws['B5'] = _('Reporting End Datetime') + ':'
    ws['C5'].border = b_border
    ws['C5'].alignment = b_c_alignment
    ws['C5'] = reporting_end_datetime_local

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

        wb.save(filename)

        return filename
    ####################################################################################################################
    # First: Trend
    # 6: title
    # 7: table title
    # 8~ table_data
    ####################################################################################################################
    has_data_flag = True
    report['reporting_period'] = report['reporting_period']
    if "names" not in report['reporting_period'].keys() or \
            report['reporting_period']['names'] is None or \
            len(report['reporting_period']['names']) == 0:
        has_data_flag = False

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

    if "values" not in report['reporting_period'].keys() or \
            report['reporting_period']['values'] is None or \
            len(report['reporting_period']['values']) == 0:
        has_data_flag = False

    ca_len = len(report['reporting_period']['names'])
    times = report['reporting_period']['timestamps']
    category = report['meter']['energy_category_name']
    start_detail_data_row_num = 9 + ca_len * 6
    if has_data_flag:

        time = times[0]
        for time in times:
            if len(time) > 0:
                break
        has_data = False
        current_sheet_parameters_row_number = 7
        for i in range(8, len(time) + 6 + ca_len * 6 + len(category) * 6 + 2):
            ws.row_dimensions[i].height = 42
        if len(time) > 0:
            has_data = True
            current_sheet_parameters_row_number = 7 + ca_len * 6
        if has_data:

            max_row = start_detail_data_row_num + len(time)
            ws['B6'].font = title_font
            ws['B6'] = name + ' ' + _('Trend')

            ws.row_dimensions[start_detail_data_row_num - 1].height = 60
            ws['B' + str(start_detail_data_row_num - 1)].fill = table_fill
            ws['B' + str(start_detail_data_row_num - 1)].font = title_font
            ws['B' + str(start_detail_data_row_num - 1)].border = f_border
            ws['B' + str(start_detail_data_row_num - 1)].alignment = c_c_alignment
            ws['B' + str(start_detail_data_row_num - 1)] = _('Datetime')

            for i in range(0, len(time)):
                col = 'B'
                row = str(start_detail_data_row_num + 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 = format_cell.get_column_letter(3 + i)

                ws[col + str(start_detail_data_row_num - 1)].fill = table_fill
                ws[col + str(start_detail_data_row_num - 1)].font = title_font
                ws[col + str(start_detail_data_row_num - 1)].alignment = c_c_alignment
                ws[col + str(start_detail_data_row_num - 1)] = report['reporting_period']['names'][i]
                ws[col + str(start_detail_data_row_num - 1)].border = f_border

                for j in range(0, len(time)):

                    row = str(start_detail_data_row_num + j)
                    ws[col + row].font = title_font
                    ws[col + row].alignment = c_c_alignment
                    try:
                        ws[col + row] = round2(report['reporting_period']['values'][i][j], 3) if \
                            len(report['reporting_period']['values'][i]) > 0 and \
                            len(report['reporting_period']['values'][i]) > j and \
                            report['reporting_period']['values'][i][j] is not None else ''
                    except Exception as e:
                        print('error 1 in excelexporters\\metertrend: ' + str(e))

                    ws[col + row].border = f_border
            # line
            # 39~: line
                line = LineChart()
                line.title = report['reporting_period']['names'][i]
                labels = Reference(ws, min_col=2, min_row=start_detail_data_row_num, max_row=max_row-1)
                line_data = Reference(ws, min_col=3 + i, min_row=start_detail_data_row_num+1, max_row=max_row-1)
                line.add_data(line_data, titles_from_data=True)
                line.set_categories(labels)
                line_data.smooth = True
                line.x_axis.crosses = 'min'
                line.height = 8.25
                line.width = 36
                chart_col = chr(ord('B'))
                chart_cell = chart_col + str(7 + 6*i)

                ws.add_chart(line, chart_cell)

    ####################################################################################################################
    # Power Quality Analysis sheet
    ####################################################################################################################
    if 'analysis' in report and isinstance(report['analysis'], list) and len(report['analysis']) > 0:
        file_name = (re.sub(r'[^A-Z]', '', ws.title))+'_'
        analysis_ws = wb.create_sheet(file_name + _('Power Quality Analysis'))

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

        for i in range(8, 200):
            analysis_ws.row_dimensions[i].height = 24

        # Col width
        analysis_ws.column_dimensions['A'].width = 1.5
        analysis_ws.column_dimensions['B'].width = 28.0
        for i in range(3, 20):
            analysis_ws.column_dimensions[format_cell.get_column_letter(i)].width = 18.0

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

        # Title
        analysis_ws['B3'].alignment = b_r_alignment
        analysis_ws['B3'] = _('Name') + ':'
        analysis_ws['C3'].border = b_border
        analysis_ws['C3'].alignment = b_c_alignment
        analysis_ws['C3'] = name

        analysis_ws['B4'].alignment = b_r_alignment
        analysis_ws['B4'] = _('Reporting Start Datetime') + ':'
        analysis_ws['C4'].border = b_border
        analysis_ws['C4'].alignment = b_c_alignment
        analysis_ws['C4'] = reporting_start_datetime_local

        analysis_ws['B5'].alignment = b_r_alignment
        analysis_ws['B5'] = _('Reporting End Datetime') + ':'
        analysis_ws['C5'].border = b_border
        analysis_ws['C5'].alignment = b_c_alignment
        analysis_ws['C5'] = reporting_end_datetime_local

        current_row = 6
        analysis_ws['B' + str(current_row)].font = title_font
        analysis_ws['B' + str(current_row)] = name + ' ' + _('Power Quality Analysis')
        current_row += 1

        # Header
        headers = [
            _('Point'), _('Category'), _('Type'), _('Unit'),
            _('Limit'), _('Normal Limit'), _('Severe Limit'), _('Compliance'),
            _('Worst Deviation'), _('Worst Time')
        ]
        # plus dynamic metrics
        metrics_names = set()
        for item in report['analysis']:
            if isinstance(item.get('metrics'), list):
                for m in item['metrics']:
                    metrics_names.add(m.get('name'))
        metrics_names = list(metrics_names)
        table_headers = headers + metrics_names

        analysis_ws.row_dimensions[current_row].height = 28
        for idx, h in enumerate(table_headers):
            col = format_cell.get_column_letter(2 + idx)
            analysis_ws[col + str(current_row)].fill = table_fill
            analysis_ws[col + str(current_row)].border = f_border
            analysis_ws[col + str(current_row)].font = name_font
            analysis_ws[col + str(current_row)].alignment = c_c_alignment
            analysis_ws[col + str(current_row)] = h
        current_row += 1

        # Rows
        for item in report['analysis']:
            row_values = [
                item.get('point_name', ''),
                item.get('category', ''),
                item.get('type', ''),
                item.get('unit', ''),
                item.get('limit_pct', ''),
                item.get('limit_normal_hz', ''),
                item.get('limit_severe_hz', ''),
                item.get('compliance_pct', ''),
                item.get('worst_abs_deviation_pct', item.get('worst_unbalance_pct', item.get('worst_deviation_hz', ''))),
                item.get('worst_time', ''),
            ]
            # metrics map
            metrics_map = {}
            if isinstance(item.get('metrics'), list):
                for m in item['metrics']:
                    metrics_map[m.get('name')] = m.get('value')
            for mn in metrics_names:
                row_values.append(metrics_map.get(mn, ''))

            for idx, v in enumerate(row_values):
                col = format_cell.get_column_letter(2 + idx)
                analysis_ws[col + str(current_row)].border = f_border
                analysis_ws[col + str(current_row)].font = title_font
                analysis_ws[col + str(current_row)].alignment = c_c_alignment
                analysis_ws[col + str(current_row)] = v
            current_row += 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		"""
2		Meter Trend Excel Exporter
3
4		This module provides functionality to export meter trend data to Excel format.
5		It generates comprehensive reports showing energy consumption trends for meters
6		with detailed analysis and visualizations.
7
8		Key Features:
9		- Meter energy consumption trend analysis
10		- Base period vs reporting period comparison
11		- Trend breakdown by energy categories
12		- Detailed data with line charts
13		- Multi-language support
14		- Base64 encoding for file transmission
15
16		The exported Excel file includes:
17		- Meter trend summary
18		- Base period comparison data
19		- Trend breakdown by energy categories
20		- Detailed time-series data with line charts
21		- Parameter data (if available)
22		"""
23
24		import base64
25		from core.utilities import get_translation
26		import os
27		import re
28		import uuid
29		import openpyxl.utils.cell as format_cell
30		from openpyxl import Workbook
31		from openpyxl.chart import LineChart, Reference
32		from openpyxl.drawing.image import Image
33		from openpyxl.styles import PatternFill, Border, Side, Alignment, Font
34		from core.utilities import round2
35
36		########################################################################################################################
37		# PROCEDURES
38		# Step 1: Validate the report data
39		# Step 2: Generate excel file
40		# Step 3: Encode the excel file bytes to Base64
41		########################################################################################################################
42
43
44	View Code Duplication	def export(result,
		0 ignored issues – show Duplication introduced 2024-05-05 07:50 UTC by Report Bug Copy Issue Report This code seems to be duplicated in your project. Loading history...
45		name,
46		reporting_start_datetime_local,
47		reporting_end_datetime_local,
48		period_type,
49		language):
50		####################################################################################################################
51		# Step 1: Validate the report data
52		####################################################################################################################
53		if result is None:
54		return None
55
56		####################################################################################################################
57		# Step 2: Generate excel file from the report data
58		####################################################################################################################
59		filename = generate_excel(result,
60		name,
61		reporting_start_datetime_local,
62		reporting_end_datetime_local,
63		period_type,
64		language)
65
66		####################################################################################################################
67		# Step 3: Encode the excel file to Base64
68		####################################################################################################################
69		binary_file_data = b''
70		try:
71		with open(filename, 'rb') as binary_file:
72		binary_file_data = binary_file.read()
73		except IOError as ex:
74		print(str(ex))
75
76		# Base64 encode the bytes
77		base64_encoded_data = base64.b64encode(binary_file_data)
78		# get the Base64 encoded data using human-readable characters.
79		base64_message = base64_encoded_data.decode('utf-8')
80		# delete the file from server
81		try:
82		os.remove(filename)
83		except NotImplementedError as ex:
84		print(str(ex))
85		return base64_message
86
87
88		def generate_excel(report,
89		name,
90		reporting_start_datetime_local,
91		reporting_end_datetime_local,
92		period_type,
93		language):
94
95		trans = get_translation(language)
96		trans.install()
97		_ = trans.gettext
98
99		wb = Workbook()
100		ws = wb.active
101		ws.title = "MeterTrend"
102
103		# Row height
104		ws.row_dimensions[1].height = 102
105		for i in range(2, 8):
106		ws.row_dimensions[i].height = 42
107
108		# Col width
109		ws.column_dimensions['A'].width = 1.5
110
111		ws.column_dimensions['B'].width = 25.0
112
113		for i in range(ord('C'), ord('V')):
114		ws.column_dimensions[chr(i)].width = 15.0
115
116		# Font
117		name_font = Font(name='Arial', size=15, bold=True)
118		title_font = Font(name='Arial', size=15, bold=True)
119
120		table_fill = PatternFill(fill_type='solid', fgColor='90ee90')
121		f_border = Border(left=Side(border_style='medium'),
122		right=Side(border_style='medium'),
123		bottom=Side(border_style='medium'),
124		top=Side(border_style='medium')
125		)
126		b_border = Border(
127		bottom=Side(border_style='medium'),
128		)
129
130		b_c_alignment = Alignment(vertical='bottom',
131		horizontal='center',
132		text_rotation=0,
133		wrap_text=True,
134		shrink_to_fit=False,
135		indent=0)
136		c_c_alignment = Alignment(vertical='center',
137		horizontal='center',
138		text_rotation=0,
139		wrap_text=True,
140		shrink_to_fit=False,
141		indent=0)
142		b_r_alignment = Alignment(vertical='bottom',
143		horizontal='right',
144		text_rotation=0,
145		wrap_text=True,
146		shrink_to_fit=False,
147		indent=0)
148
149		# Img
150		img = Image("excelexporters/myems.png")
151		ws.add_image(img, 'A1')
152
153		# Title
154		ws['B3'].alignment = b_r_alignment
155		ws['B3'] = _('Name') + ':'
156		ws['C3'].border = b_border
157		ws['C3'].alignment = b_c_alignment
158		ws['C3'] = name
159
160		ws['B4'].alignment = b_r_alignment
161		ws['B4'] = _('Reporting Start Datetime') + ':'
162		ws['C4'].border = b_border
163		ws['C4'].alignment = b_c_alignment
164		ws['C4'] = reporting_start_datetime_local
165
166		ws['B5'].alignment = b_r_alignment
167		ws['B5'] = _('Reporting End Datetime') + ':'
168		ws['C5'].border = b_border
169		ws['C5'].alignment = b_c_alignment
170		ws['C5'] = reporting_end_datetime_local
171
172		if "reporting_period" not in report.keys() or \
173		"names" not in report['reporting_period'].keys() or len(report['reporting_period']['names']) == 0:
174		filename = str(uuid.uuid4()) + '.xlsx'
		0 ignored issues – show Comprehensibility Best Practice introduced 2025-03-02 16:05 UTC by Report Bug Copy Issue Report The variable `str` does not seem to be defined. Loading history...
175		wb.save(filename)
176
177		return filename
178		####################################################################################################################
179		# First: Trend
180		# 6: title
181		# 7: table title
182		# 8~ table_data
183		####################################################################################################################
184		has_data_flag = True
185		report['reporting_period'] = report['reporting_period']
186		if "names" not in report['reporting_period'].keys() or \
187		report['reporting_period']['names'] is None or \
188		len(report['reporting_period']['names']) == 0:
189		has_data_flag = False
190
191		if "timestamps" not in report['reporting_period'].keys() or \
192		report['reporting_period']['timestamps'] is None or \
193		len(report['reporting_period']['timestamps']) == 0:
194		has_data_flag = False
195
196		if "values" not in report['reporting_period'].keys() or \
197		report['reporting_period']['values'] is None or \
198		len(report['reporting_period']['values']) == 0:
199		has_data_flag = False
200
201		ca_len = len(report['reporting_period']['names'])
202		times = report['reporting_period']['timestamps']
203		category = report['meter']['energy_category_name']
204		start_detail_data_row_num = 9 + ca_len * 6
205	View Code Duplication	if has_data_flag:
		0 ignored issues – show Duplication introduced 2025-10-30 15:31 UTC by Report Bug Copy Issue Report This code seems to be duplicated in your project. Loading history...
206		time = times[0]
207		for time in times:
208		if len(time) > 0:
209		break
210		has_data = False
211		current_sheet_parameters_row_number = 7
212		for i in range(8, len(time) + 6 + ca_len * 6 + len(category) * 6 + 2):
213		ws.row_dimensions[i].height = 42
214		if len(time) > 0:
215		has_data = True
216		current_sheet_parameters_row_number = 7 + ca_len * 6
217		if has_data:
218
219		max_row = start_detail_data_row_num + len(time)
220		ws['B6'].font = title_font
221		ws['B6'] = name + ' ' + _('Trend')
222
223		ws.row_dimensions[start_detail_data_row_num - 1].height = 60
224		ws['B' + str(start_detail_data_row_num - 1)].fill = table_fill
225		ws['B' + str(start_detail_data_row_num - 1)].font = title_font
226		ws['B' + str(start_detail_data_row_num - 1)].border = f_border
227		ws['B' + str(start_detail_data_row_num - 1)].alignment = c_c_alignment
228		ws['B' + str(start_detail_data_row_num - 1)] = _('Datetime')
229
230		for i in range(0, len(time)):
231		col = 'B'
232		row = str(start_detail_data_row_num + i)
233		ws[col + row].font = title_font
234		ws[col + row].alignment = c_c_alignment
235		ws[col + row] = time[i]
236		ws[col + row].border = f_border
237
238		for i in range(0, ca_len):
239		# 38 title
240		col = format_cell.get_column_letter(3 + i)
241
242		ws[col + str(start_detail_data_row_num - 1)].fill = table_fill
243		ws[col + str(start_detail_data_row_num - 1)].font = title_font
244		ws[col + str(start_detail_data_row_num - 1)].alignment = c_c_alignment
245		ws[col + str(start_detail_data_row_num - 1)] = report['reporting_period']['names'][i]
246		ws[col + str(start_detail_data_row_num - 1)].border = f_border
247
248		for j in range(0, len(time)):
249
250		row = str(start_detail_data_row_num + j)
251		ws[col + row].font = title_font
252		ws[col + row].alignment = c_c_alignment
253		try:
254		ws[col + row] = round2(report['reporting_period']['values'][i][j], 3) if \
255		len(report['reporting_period']['values'][i]) > 0 and \
256		len(report['reporting_period']['values'][i]) > j and \
257		report['reporting_period']['values'][i][j] is not None else ''
258		except Exception as e:
259		print('error 1 in excelexporters\\metertrend: ' + str(e))
260
261		ws[col + row].border = f_border
262		# line
263		# 39~: line
264		line = LineChart()
265		line.title = report['reporting_period']['names'][i]
266		labels = Reference(ws, min_col=2, min_row=start_detail_data_row_num, max_row=max_row-1)
267		line_data = Reference(ws, min_col=3 + i, min_row=start_detail_data_row_num+1, max_row=max_row-1)
268		line.add_data(line_data, titles_from_data=True)
269		line.set_categories(labels)
270		line_data.smooth = True
271		line.x_axis.crosses = 'min'
272		line.height = 8.25
273		line.width = 36
274		chart_col = chr(ord('B'))
275		chart_cell = chart_col + str(7 + 6*i)
276
277		ws.add_chart(line, chart_cell)
278
279		####################################################################################################################
280		# Power Quality Analysis sheet
281		####################################################################################################################
282		if 'analysis' in report and isinstance(report['analysis'], list) and len(report['analysis']) > 0:
283		file_name = (re.sub(r'[^A-Z]', '', ws.title))+'_'
284		analysis_ws = wb.create_sheet(file_name + _('Power Quality Analysis'))
285
286		# Row height
287		analysis_ws.row_dimensions[1].height = 102
288		for i in range(2, 7 + 1):
289		analysis_ws.row_dimensions[i].height = 42
290
291		for i in range(8, 200):
292		analysis_ws.row_dimensions[i].height = 24
293
294		# Col width
295		analysis_ws.column_dimensions['A'].width = 1.5
296		analysis_ws.column_dimensions['B'].width = 28.0
297		for i in range(3, 20):
298		analysis_ws.column_dimensions[format_cell.get_column_letter(i)].width = 18.0
299
300		# Img
301		img = Image("excelexporters/myems.png")
302		analysis_ws.add_image(img, 'A1')
303
304		# Title
305		analysis_ws['B3'].alignment = b_r_alignment
306		analysis_ws['B3'] = _('Name') + ':'
307		analysis_ws['C3'].border = b_border
308		analysis_ws['C3'].alignment = b_c_alignment
309		analysis_ws['C3'] = name
310
311		analysis_ws['B4'].alignment = b_r_alignment
312		analysis_ws['B4'] = _('Reporting Start Datetime') + ':'
313		analysis_ws['C4'].border = b_border
314		analysis_ws['C4'].alignment = b_c_alignment
315		analysis_ws['C4'] = reporting_start_datetime_local
316
317		analysis_ws['B5'].alignment = b_r_alignment
318		analysis_ws['B5'] = _('Reporting End Datetime') + ':'
319		analysis_ws['C5'].border = b_border
320		analysis_ws['C5'].alignment = b_c_alignment
321		analysis_ws['C5'] = reporting_end_datetime_local
322
323		current_row = 6
324		analysis_ws['B' + str(current_row)].font = title_font
325		analysis_ws['B' + str(current_row)] = name + ' ' + _('Power Quality Analysis')
326		current_row += 1
327
328		# Header
329		headers = [
330		_('Point'), _('Category'), _('Type'), _('Unit'),
331		_('Limit'), _('Normal Limit'), _('Severe Limit'), _('Compliance'),
332		_('Worst Deviation'), _('Worst Time')
333		]
334		# plus dynamic metrics
335		metrics_names = set()
336		for item in report['analysis']:
337		if isinstance(item.get('metrics'), list):
338		for m in item['metrics']:
339		metrics_names.add(m.get('name'))
340		metrics_names = list(metrics_names)
341		table_headers = headers + metrics_names
342
343		analysis_ws.row_dimensions[current_row].height = 28
344		for idx, h in enumerate(table_headers):
345		col = format_cell.get_column_letter(2 + idx)
346		analysis_ws[col + str(current_row)].fill = table_fill
347		analysis_ws[col + str(current_row)].border = f_border
348		analysis_ws[col + str(current_row)].font = name_font
349		analysis_ws[col + str(current_row)].alignment = c_c_alignment
350		analysis_ws[col + str(current_row)] = h
351		current_row += 1
352
353		# Rows
354		for item in report['analysis']:
355		row_values = [
356		item.get('point_name', ''),
357		item.get('category', ''),
358		item.get('type', ''),
359		item.get('unit', ''),
360		item.get('limit_pct', ''),
361		item.get('limit_normal_hz', ''),
362		item.get('limit_severe_hz', ''),
363		item.get('compliance_pct', ''),
364		item.get('worst_abs_deviation_pct', item.get('worst_unbalance_pct', item.get('worst_deviation_hz', ''))),
365		item.get('worst_time', ''),
366		]
367		# metrics map
368		metrics_map = {}
369		if isinstance(item.get('metrics'), list):
370		for m in item['metrics']:
371		metrics_map[m.get('name')] = m.get('value')
372		for mn in metrics_names:
373		row_values.append(metrics_map.get(mn, ''))
374
375		for idx, v in enumerate(row_values):
376		col = format_cell.get_column_letter(2 + idx)
377		analysis_ws[col + str(current_row)].border = f_border
378		analysis_ws[col + str(current_row)].font = title_font
379		analysis_ws[col + str(current_row)].alignment = c_c_alignment
380		analysis_ws[col + str(current_row)] = v
381		current_row += 1
382
383		filename = str(uuid.uuid4()) + '.xlsx'
384		wb.save(filename)
385
386		return filename
387
388
389		def timestamps_data_all_equal_0(lists):
390		for i, value in enumerate(list(lists)):
391		if len(value) > 0:
392		return False
393
394		return True
395
396
397		def get_parameters_timestamps_lists_max_len(parameters_timestamps_lists):
398		max_len = 0
399		for i, value in enumerate(list(parameters_timestamps_lists)):
400		if len(value) > max_len:
401		max_len = len(value)
402
403		return max_len
404
405
406		def timestamps_data_not_equal_0(lists):
407		number = 0
408		for i, value in enumerate(list(lists)):
409		if len(value) > 0:
410		number += 1
411		return number
412

MyEMS / myems

Push — master ( 05a995...b2a2f7 )

excelexporters.powerquality.generate_excel() F

Complexity

Size

Duplication

Importance

How to fix Long Method Complexity

Long Method

Complexity

Duplication Side-by-Side

Filter issues like