excelexporters.combinedequipmentload.generate_excel() - Code Metrics - Inspection of "added excel exporter of combinedequipmentload repo..." - MyEMS/myems - Measure and Improve Code Quality continuously with Scrutinizer

Passed

Push — master ( 550535...2d8465 )

by Guangyu

created 2021-03-05 05:52 UTC

generate_excel() F

↳ Parent: excelexporters.combinedequipmentload

Complexity

Conditions

Size

Total Lines	307
Code Lines	227

Duplication

Lines	169
Ratio	55.05 %

Importance

Changes

Metric	Value
eloc	227
dl	169
loc	307
rs	0
c	0
b	0
f	0
cc	27
nop	5

How to fix Long Method Complexity

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


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


def export(report,

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

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

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

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


def generate_excel(report,
                   name,
                   reporting_start_datetime_local,
                   reporting_end_datetime_local,
                   period_type):
    wb = Workbook()
    ws = wb.active

    # Row height
    ws.row_dimensions[1].height = 102

    for i in range(2, 2000 + 1):
        ws.row_dimensions[i].height = 42

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

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

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

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

    b_c_alignment = Alignment(vertical='bottom',
                              horizontal='center',
                              text_rotation=0,
                              wrap_text=False,
                              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=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")
    # img = Image("myems.png")
    img.width = img.width * 0.85
    img.height = img.height * 0.85
    ws.add_image(img, 'B1')

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

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

    ws['F3'].font = name_font
    ws['F3'].alignment = b_r_alignment
    ws['F3'] = 'Date:'
    ws['G3'].border = b_border
    ws['G3'].alignment = b_c_alignment
    ws['G3'].font = name_font
    ws['G3'] = reporting_start_datetime_local + "__" + reporting_end_datetime_local
    ws.merge_cells("G3:H3")

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

        wb.save(filename)

        return filename
    #################################################
    # First: 统计分析
    # 6: title
    # 7: table title
    # 8~2*ca_len table_data
    #################################################
    reporting_period_data = report['reporting_period']

    has_energy_data_flag = True

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

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

        return filename

    if has_energy_data_flag:

        ws['B6'].font = title_font
        ws['B6'] = name + ' 统计分析'

        category = reporting_period_data['names']

        # table_title
        ws['B7'].fill = table_fill
        ws['B7'].font = title_font
        ws['B7'].alignment = c_c_alignment
        ws['B7'] = '报告期'
        ws['B7'].border = f_border

        ws['C7'].font = title_font
        ws['C7'].alignment = c_c_alignment
        ws['C7'] = '平均负荷'
        ws['C7'].border = f_border

        ws['D7'].font = title_font
        ws['D7'].alignment = c_c_alignment
        ws['D7'] = '最大负荷'
        ws['D7'].border = f_border

        ws['E7'].font = title_font
        ws['E7'].alignment = c_c_alignment
        ws['E7'] = '负荷系数'
        ws['E7'].border = f_border

        # table_data

        for i, value in enumerate(category):
            row = i * 2 + 8
            ws['B' + str(row)].font = name_font
            ws['B' + str(row)].alignment = c_c_alignment
            ws['B' + str(row)] = reporting_period_data['names'][i] + " (" + reporting_period_data['units'][i] + "/H )"
            ws['B' + str(row)].border = f_border

            ws['B' + str(row + 1)].font = name_font
            ws['B' + str(row + 1)].alignment = c_c_alignment
            ws['B' + str(row + 1)] = "环比"
            ws['B' + str(row + 1)].border = f_border

            ws['C' + str(row)].font = name_font
            ws['C' + str(row)].alignment = c_c_alignment
            ws['C' + str(row)] = round(reporting_period_data['averages'][i], 2) \
                if reporting_period_data['averages'][i] is not None else ''
            ws['C' + str(row)].border = f_border
            ws['C' + str(row)].number_format = '0.00'

            ws['C' + str(row + 1)].font = name_font
            ws['C' + str(row + 1)].alignment = c_c_alignment
            ws['C' + str(row + 1)] = str(round(reporting_period_data['averages_increment_rate'][i] * 100, 2)) + "%" \
                if reporting_period_data['averages_increment_rate'][i] is not None else '0.00%'
            ws['C' + str(row + 1)].border = f_border

            ws['D' + str(row)].font = name_font
            ws['D' + str(row)].alignment = c_c_alignment
            ws['D' + str(row)] = round(reporting_period_data['maximums'][i], 2) \
                if reporting_period_data['maximums'][i] is not None else ''
            ws['D' + str(row)].border = f_border
            ws['D' + str(row)].number_format = '0.00'

            ws['D' + str(row + 1)].font = name_font
            ws['D' + str(row + 1)].alignment = c_c_alignment
            ws['D' + str(row + 1)] = str(round(reporting_period_data['maximums_increment_rate'][i] * 100, 2)) + "%" \
                if reporting_period_data['maximums_increment_rate'][i] is not None else '0.00%'
            ws['D' + str(row + 1)].border = f_border

            ws['E' + str(row)].font = name_font
            ws['E' + str(row)].alignment = c_c_alignment
            ws['E' + str(row)] = round(reporting_period_data['factors'][i], 2) \
                if reporting_period_data['factors'][i] is not None else ''
            ws['E' + str(row)].border = f_border
            ws['E' + str(row)].number_format = '0.00'

            ws['E' + str(row + 1)].font = name_font
            ws['E' + str(row + 1)].alignment = c_c_alignment
            ws['E' + str(row + 1)] = str(round(reporting_period_data['factors_increment_rate'][i] * 100, 2)) + "%" \
                if reporting_period_data['factors_increment_rate'][i] is not None else '0.00%'
            ws['E' + str(row + 1)].border = f_border

    ########################################################
    # Third: 详细数据
    # row_sat~ row_sat + 6*cal_len: line
    # row_da: table title
    # row_da + 1~: table_data
    ########################################################
    has_timestamps_flag = True
    if "timestamps" not in reporting_period_data.keys() or \
            reporting_period_data['timestamps'] is None or \
            len(reporting_period_data['timestamps']) == 0:
        has_timestamps_flag = False

    if has_timestamps_flag:

        timestamps = reporting_period_data['timestamps'][0]
        names = reporting_period_data['names']
        ca_len = len(names)
        time_len = len(timestamps)
        # row_lines == the number of rows of lines
        row_lines = 6 * ca_len
        # row_sat == the number of rows of statistical analysis table
        row_sat = 9 + 2 * ca_len
        # row_da == the number of rows of Detailed data
        row_da = row_sat + row_lines + 1

        ws['B' + str(row_da)].font = title_font
        ws['B' + str(row_da)] = name + ' 详细数据'
        # table_title
        ws['B' + str(row_da + 1)].fill = table_fill
        ws['B' + str(row_da + 1)].font = name_font
        ws['B' + str(row_da + 1)].alignment = c_c_alignment
        ws['B' + str(row_da + 1)] = "日期时间"
        ws['B' + str(row_da + 1)].border = f_border

        for i in range(0, ca_len):
            col_average = chr(ord('C') + 2 * i)
            col_maximum = chr(ord('D') + 2 * i)

            ws[col_average + str(row_da + 1)].font = name_font
            ws[col_average + str(row_da + 1)].alignment = c_c_alignment
            ws[col_average + str(row_da + 1)] = names[i] + " 平均负荷(" + reporting_period_data['units'][
                i] + "/H)"
            ws[col_average + str(row_da + 1)].border = f_border

            ws[col_maximum + str(row_da + 1)].font = name_font
            ws[col_maximum + str(row_da + 1)].alignment = c_c_alignment
            ws[col_maximum + str(row_da + 1)] = names[i] + " 最大负荷(" + reporting_period_data['units'][
                i] + "/H)"
            ws[col_maximum + str(row_da + 1)].border = f_border
        # table_date
        for i in range(0, time_len):
            rows = i + row_da + 2

            ws['B' + str(rows)].font = name_font
            ws['B' + str(rows)].alignment = c_c_alignment
            ws['B' + str(rows)] = timestamps[i]
            ws['B' + str(rows)].border = f_border

            for index in range(0, ca_len):
                col_average = chr(ord('C') + 2 * index)
                col_maximum = chr(ord('D') + 2 * index)

                ws[col_average + str(rows)].font = name_font
                ws[col_average + str(rows)].alignment = c_c_alignment
                ws[col_average + str(rows)] = reporting_period_data['sub_averages'][index][i] \
                    if reporting_period_data['sub_maximums'][index] is not None else ''
                ws[col_average + str(rows)].number_format = '0.00'
                ws[col_average + str(rows)].border = f_border

                ws[col_maximum + str(rows)].font = name_font
                ws[col_maximum + str(rows)].alignment = c_c_alignment
                ws[col_maximum + str(rows)] = reporting_period_data['sub_maximums'][index][i] \
                    if reporting_period_data['sub_maximums'][index] is not None else ''
                ws[col_maximum + str(rows)].number_format = '0.00'
                ws[col_maximum + str(rows)].border = f_border

        # LineChart
        for i in range(0, ca_len):
            lc = LineChart()
            lc.title = "报告期 最大负荷"
            lc.style = 10
            lc.x_axis.majorTickMark = 'in'
            lc.y_axis.majorTickMark = 'in'
            lc.smooth = True
            lc.x_axis.crosses = 'min'
            lc.height = 8.25
            lc.width = 24
            lc.dLbls = DataLabelList()
            lc.dLbls.dLblPos = 't'
            lc.dLbls.showVal = True
            times = Reference(ws, min_col=2, min_row=row_da + 2,
                              max_row=row_da + 2 + time_len)
            lc_data = Reference(ws, min_col=2 + 2 * (i+1), min_row=row_da + 1,
                                max_row=row_da + 1 + time_len)
            lc.add_data(lc_data, titles_from_data=True)
            lc.set_categories(times)
            ser = lc.series[0]
            ser.marker.symbol = "diamond"
            ser.marker.size = 5
            chart_col = 'B'
            chart_cell = str(row_sat + 6 * i)
            ws.add_chart(lc, chart_col + chart_cell)

    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		LineChart,
6		Reference,
7		)
8		from openpyxl.chart.label import DataLabelList
9		from openpyxl.styles import PatternFill, Border, Side, Alignment, Font
10		from openpyxl.drawing.image import Image
11		from openpyxl import Workbook
12
13
14		####################################################################################################################
15		# PROCEDURES
16		# Step 1: Validate the report data
17		# Step 2: Generate excel file
18		# Step 3: Encode the excel file bytes to Base64
19		####################################################################################################################
20
21
22	View Code Duplication	def export(report,
		0 ignored issues – show Duplication introduced 2021-03-05 06:21 UTC by Report Bug Copy Issue Report This code seems to be duplicated in your project. Loading history...
23		name,
24		reporting_start_datetime_local,
25		reporting_end_datetime_local,
26		period_type):
27		####################################################################################################################
28		# Step 1: Validate the report data
29		####################################################################################################################
30		if report is None:
31		return None
32
33		####################################################################################################################
34		# Step 2: Generate excel file from the report data
35		####################################################################################################################
36		filename = generate_excel(report,
37		name,
38		reporting_start_datetime_local,
39		reporting_end_datetime_local,
40		period_type)
41		####################################################################################################################
42		# Step 3: Encode the excel file to Base64
43		####################################################################################################################
44		try:
45		with open(filename, 'rb') as binary_file:
46		binary_file_data = binary_file.read()
47		except IOError as ex:
48		pass
49
50		# Base64 encode the bytes
51		base64_encoded_data = base64.b64encode(binary_file_data)
		0 ignored issues – show introduced 2021-03-05 06:21 UTC by Report Bug Copy Issue Report The variable `binary_file_data` does not seem to be defined for all execution paths. Loading history...
52		# get the Base64 encoded data using human-readable characters.
53		base64_message = base64_encoded_data.decode('utf-8')
54		# delete the file from server
55		try:
56		os.remove(filename)
57		except NotImplementedError as ex:
58		pass
59		return base64_message
60
61
62		def generate_excel(report,
63		name,
64		reporting_start_datetime_local,
65		reporting_end_datetime_local,
66		period_type):
67		wb = Workbook()
68		ws = wb.active
69
70		# Row height
71		ws.row_dimensions[1].height = 102
72
73		for i in range(2, 2000 + 1):
74		ws.row_dimensions[i].height = 42
75
76		# Col width
77		ws.column_dimensions['A'].width = 1.5
78		ws.column_dimensions['B'].width = 25.0
79
80		for i in range(ord('C'), ord('L')):
81		ws.column_dimensions[chr(i)].width = 15.0
82
83		# Font
84		name_font = Font(name='Constantia', size=15, bold=True)
85		title_font = Font(name='宋体', size=15, bold=True)
86		# data_font = Font(name='Franklin Gothic Book', size=11)
87
88		table_fill = PatternFill(fill_type='solid', fgColor='1F497D')
89		f_border = Border(left=Side(border_style='medium', color='00000000'),
90		right=Side(border_style='medium', color='00000000'),
91		bottom=Side(border_style='medium', color='00000000'),
92		top=Side(border_style='medium', color='00000000')
93		)
94		b_border = Border(
95		bottom=Side(border_style='medium', color='00000000'),
96		)
97
98		b_c_alignment = Alignment(vertical='bottom',
99		horizontal='center',
100		text_rotation=0,
101		wrap_text=False,
102		shrink_to_fit=False,
103		indent=0)
104		c_c_alignment = Alignment(vertical='center',
105		horizontal='center',
106		text_rotation=0,
107		wrap_text=True,
108		shrink_to_fit=False,
109		indent=0)
110		b_r_alignment = Alignment(vertical='bottom',
111		horizontal='right',
112		text_rotation=0,
113		wrap_text=False,
114		shrink_to_fit=False,
115		indent=0)
116		# c_r_alignment = Alignment(vertical='bottom',
117		# horizontal='center',
118		# text_rotation=0,
119		# wrap_text=False,
120		# shrink_to_fit=False,
121		# indent=0)
122
123		# Img
124		img = Image("excelexporters/myems.png")
125		# img = Image("myems.png")
126		img.width = img.width * 0.85
127		img.height = img.height * 0.85
128		ws.add_image(img, 'B1')
129
130		# Title
131		ws['B3'].font = name_font
132		ws['B3'].alignment = b_r_alignment
133		ws['B3'] = 'Name:'
134		ws['C3'].border = b_border
135		ws['C3'].alignment = b_c_alignment
136		ws['C3'].font = name_font
137		ws['C3'] = name
138
139		ws['D3'].font = name_font
140		ws['D3'].alignment = b_r_alignment
141		ws['D3'] = 'Period:'
142		ws['E3'].border = b_border
143		ws['E3'].alignment = b_c_alignment
144		ws['E3'].font = name_font
145		ws['E3'] = period_type
146
147		ws['F3'].font = name_font
148		ws['F3'].alignment = b_r_alignment
149		ws['F3'] = 'Date:'
150		ws['G3'].border = b_border
151		ws['G3'].alignment = b_c_alignment
152		ws['G3'].font = name_font
153		ws['G3'] = reporting_start_datetime_local + "__" + reporting_end_datetime_local
154		ws.merge_cells("G3:H3")
155
156		if "reporting_period" not in report.keys() or \
157		"names" not in report['reporting_period'].keys() or len(report['reporting_period']['names']) == 0:
158		filename = str(uuid.uuid4()) + '.xlsx'
		0 ignored issues – show Comprehensibility Best Practice introduced 2021-03-05 06:21 UTC by Report Bug Copy Issue Report The variable `str` does not seem to be defined. Loading history...
159		wb.save(filename)
160
161		return filename
162		#################################################
163		# First: 统计分析
164		# 6: title
165		# 7: table title
166		# 8~2*ca_len table_data
167		#################################################
168		reporting_period_data = report['reporting_period']
169
170		has_energy_data_flag = True
171
172		if "names" not in reporting_period_data.keys() or \
173		reporting_period_data['names'] is None or \
174		len(reporting_period_data['names']) == 0:
175		has_energy_data_flag = False
176
177		filename = str(uuid.uuid4()) + '.xlsx'
178		wb.save(filename)
179
180		return filename
181
182	View Code Duplication	if has_energy_data_flag:
		0 ignored issues – show Duplication introduced 2021-03-05 06:21 UTC by Report Bug Copy Issue Report This code seems to be duplicated in your project. Loading history...
183		ws['B6'].font = title_font
184		ws['B6'] = name + ' 统计分析'
185
186		category = reporting_period_data['names']
187
188		# table_title
189		ws['B7'].fill = table_fill
190		ws['B7'].font = title_font
191		ws['B7'].alignment = c_c_alignment
192		ws['B7'] = '报告期'
193		ws['B7'].border = f_border
194
195		ws['C7'].font = title_font
196		ws['C7'].alignment = c_c_alignment
197		ws['C7'] = '平均负荷'
198		ws['C7'].border = f_border
199
200		ws['D7'].font = title_font
201		ws['D7'].alignment = c_c_alignment
202		ws['D7'] = '最大负荷'
203		ws['D7'].border = f_border
204
205		ws['E7'].font = title_font
206		ws['E7'].alignment = c_c_alignment
207		ws['E7'] = '负荷系数'
208		ws['E7'].border = f_border
209
210		# table_data
211
212		for i, value in enumerate(category):
213		row = i * 2 + 8
214		ws['B' + str(row)].font = name_font
215		ws['B' + str(row)].alignment = c_c_alignment
216		ws['B' + str(row)] = reporting_period_data['names'][i] + " (" + reporting_period_data['units'][i] + "/H )"
217		ws['B' + str(row)].border = f_border
218
219		ws['B' + str(row + 1)].font = name_font
220		ws['B' + str(row + 1)].alignment = c_c_alignment
221		ws['B' + str(row + 1)] = "环比"
222		ws['B' + str(row + 1)].border = f_border
223
224		ws['C' + str(row)].font = name_font
225		ws['C' + str(row)].alignment = c_c_alignment
226		ws['C' + str(row)] = round(reporting_period_data['averages'][i], 2) \
227		if reporting_period_data['averages'][i] is not None else ''
228		ws['C' + str(row)].border = f_border
229		ws['C' + str(row)].number_format = '0.00'
230
231		ws['C' + str(row + 1)].font = name_font
232		ws['C' + str(row + 1)].alignment = c_c_alignment
233		ws['C' + str(row + 1)] = str(round(reporting_period_data['averages_increment_rate'][i] * 100, 2)) + "%" \
234		if reporting_period_data['averages_increment_rate'][i] is not None else '0.00%'
235		ws['C' + str(row + 1)].border = f_border
236
237		ws['D' + str(row)].font = name_font
238		ws['D' + str(row)].alignment = c_c_alignment
239		ws['D' + str(row)] = round(reporting_period_data['maximums'][i], 2) \
240		if reporting_period_data['maximums'][i] is not None else ''
241		ws['D' + str(row)].border = f_border
242		ws['D' + str(row)].number_format = '0.00'
243
244		ws['D' + str(row + 1)].font = name_font
245		ws['D' + str(row + 1)].alignment = c_c_alignment
246		ws['D' + str(row + 1)] = str(round(reporting_period_data['maximums_increment_rate'][i] * 100, 2)) + "%" \
247		if reporting_period_data['maximums_increment_rate'][i] is not None else '0.00%'
248		ws['D' + str(row + 1)].border = f_border
249
250		ws['E' + str(row)].font = name_font
251		ws['E' + str(row)].alignment = c_c_alignment
252		ws['E' + str(row)] = round(reporting_period_data['factors'][i], 2) \
253		if reporting_period_data['factors'][i] is not None else ''
254		ws['E' + str(row)].border = f_border
255		ws['E' + str(row)].number_format = '0.00'
256
257		ws['E' + str(row + 1)].font = name_font
258		ws['E' + str(row + 1)].alignment = c_c_alignment
259		ws['E' + str(row + 1)] = str(round(reporting_period_data['factors_increment_rate'][i] * 100, 2)) + "%" \
260		if reporting_period_data['factors_increment_rate'][i] is not None else '0.00%'
261		ws['E' + str(row + 1)].border = f_border
262
263		########################################################
264		# Third: 详细数据
265		# row_sat~ row_sat + 6*cal_len: line
266		# row_da: table title
267		# row_da + 1~: table_data
268		########################################################
269		has_timestamps_flag = True
270		if "timestamps" not in reporting_period_data.keys() or \
271		reporting_period_data['timestamps'] is None or \
272		len(reporting_period_data['timestamps']) == 0:
273		has_timestamps_flag = False
274
275	View Code Duplication	if has_timestamps_flag:
		0 ignored issues – show Duplication introduced 2021-03-05 06:21 UTC by Report Bug Copy Issue Report This code seems to be duplicated in your project. Loading history...
276		timestamps = reporting_period_data['timestamps'][0]
277		names = reporting_period_data['names']
278		ca_len = len(names)
279		time_len = len(timestamps)
280		# row_lines == the number of rows of lines
281		row_lines = 6 * ca_len
282		# row_sat == the number of rows of statistical analysis table
283		row_sat = 9 + 2 * ca_len
284		# row_da == the number of rows of Detailed data
285		row_da = row_sat + row_lines + 1
286
287		ws['B' + str(row_da)].font = title_font
288		ws['B' + str(row_da)] = name + ' 详细数据'
289		# table_title
290		ws['B' + str(row_da + 1)].fill = table_fill
291		ws['B' + str(row_da + 1)].font = name_font
292		ws['B' + str(row_da + 1)].alignment = c_c_alignment
293		ws['B' + str(row_da + 1)] = "日期时间"
294		ws['B' + str(row_da + 1)].border = f_border
295
296		for i in range(0, ca_len):
297		col_average = chr(ord('C') + 2 * i)
298		col_maximum = chr(ord('D') + 2 * i)
299
300		ws[col_average + str(row_da + 1)].font = name_font
301		ws[col_average + str(row_da + 1)].alignment = c_c_alignment
302		ws[col_average + str(row_da + 1)] = names[i] + " 平均负荷(" + reporting_period_data['units'][
303		i] + "/H)"
304		ws[col_average + str(row_da + 1)].border = f_border
305
306		ws[col_maximum + str(row_da + 1)].font = name_font
307		ws[col_maximum + str(row_da + 1)].alignment = c_c_alignment
308		ws[col_maximum + str(row_da + 1)] = names[i] + " 最大负荷(" + reporting_period_data['units'][
309		i] + "/H)"
310		ws[col_maximum + str(row_da + 1)].border = f_border
311		# table_date
312		for i in range(0, time_len):
313		rows = i + row_da + 2
314
315		ws['B' + str(rows)].font = name_font
316		ws['B' + str(rows)].alignment = c_c_alignment
317		ws['B' + str(rows)] = timestamps[i]
318		ws['B' + str(rows)].border = f_border
319
320		for index in range(0, ca_len):
321		col_average = chr(ord('C') + 2 * index)
322		col_maximum = chr(ord('D') + 2 * index)
323
324		ws[col_average + str(rows)].font = name_font
325		ws[col_average + str(rows)].alignment = c_c_alignment
326		ws[col_average + str(rows)] = reporting_period_data['sub_averages'][index][i] \
327		if reporting_period_data['sub_maximums'][index] is not None else ''
328		ws[col_average + str(rows)].number_format = '0.00'
329		ws[col_average + str(rows)].border = f_border
330
331		ws[col_maximum + str(rows)].font = name_font
332		ws[col_maximum + str(rows)].alignment = c_c_alignment
333		ws[col_maximum + str(rows)] = reporting_period_data['sub_maximums'][index][i] \
334		if reporting_period_data['sub_maximums'][index] is not None else ''
335		ws[col_maximum + str(rows)].number_format = '0.00'
336		ws[col_maximum + str(rows)].border = f_border
337
338		# LineChart
339		for i in range(0, ca_len):
340		lc = LineChart()
341		lc.title = "报告期最大负荷"
342		lc.style = 10
343		lc.x_axis.majorTickMark = 'in'
344		lc.y_axis.majorTickMark = 'in'
345		lc.smooth = True
346		lc.x_axis.crosses = 'min'
347		lc.height = 8.25
348		lc.width = 24
349		lc.dLbls = DataLabelList()
350		lc.dLbls.dLblPos = 't'
351		lc.dLbls.showVal = True
352		times = Reference(ws, min_col=2, min_row=row_da + 2,
353		max_row=row_da + 2 + time_len)
354		lc_data = Reference(ws, min_col=2 + 2 * (i+1), min_row=row_da + 1,
355		max_row=row_da + 1 + time_len)
356		lc.add_data(lc_data, titles_from_data=True)
357		lc.set_categories(times)
358		ser = lc.series[0]
359		ser.marker.symbol = "diamond"
360		ser.marker.size = 5
361		chart_col = 'B'
362		chart_cell = str(row_sat + 6 * i)
363		ws.add_chart(lc, chart_col + chart_cell)
364
365		filename = str(uuid.uuid4()) + '.xlsx'
366		wb.save(filename)
367
368		return filename
369

MyEMS / myems

Push — master ( 550535...2d8465 )

generate_excel() F

Complexity

Size

Duplication

Importance

How to fix Long Method Complexity

Long Method

Complexity

Duplication Side-by-Side

Filter issues like