def __create_borders():
border = Border()
border.border_style = Border.BORDER_THIN
b = {}
top_borders = Borders()
top_borders.left = border
top_borders.right= border
top_borders.top = border
bottom_borders = Borders()
bottom_borders.bottom=border
bottom_borders.left=border
bottom_borders.right=border
side_borders = Borders()
side_borders.right = border
side_borders.left = border
borders = Borders()
borders.left = border
borders.top=border
borders.right=border
borders.bottom=border
b['full'] = borders
b['top'] = top_borders
b['bottom'] = bottom_borders
b['side'] = side_borders
return b
def FlgCell(self,wsRow,wsCol):
pyws = self.pyws
c = pyws.cell(row=wsRow,column=wsCol)
red = openpyxl.styles.colors.RED
side = Side(style='medium',color=red)
border = Border(left=side,right=side,top=side,bottom=side)
c.border = border.copy()
def SetCell(ws,wsRow,wsCol,val,fmt):
align = None
fill = None
numFmt = None
border = None
c = ws.cell(row=wsRow,column=wsCol)
for i in fmt:
#logging.debug(i)
if (i == 'hAlign'):
if (not align): align = Alignment()
align.horizontal = alignType[fmt[i]]
if (i == 'vAlign'):
if (not align): align = Alignment()
align.vertical = alignType[fmt[i]]
if (i == 'wrap'):
if (not align): align = Alignment()
if (fmt[i] == '1'):
align.wrap_text = 1
else:
align.wrap_text = 0
if (i == 'border'):
if (not align): align = Alignment()
side = Side(style='thin')
border = Border(left=side,right=side,top=side,bottom=side)
if (i == 'fill'):
color = ColorTable[fmt[i]]
fill = PatternFill(start_color=color,end_color='FFFFFFFF',fill_type='solid')
if (i == 'orient'):
pass
if (i == 'bg'):
fill = PatternFill(start_color='FFEE1111',end_color='FFEE1111',fill_type='solid')
if (i == 'numFmt'):
numFmt = fmt[i]
if (align):
c.alignment = align.copy()
if (border):
c.border = border.copy()
if (fill):
c.fill = fill.copy()
if (numFmt):
c.number_format = numFmt
c.value = val
def FlgCell(ws,wsRow,wsCol): c = ws.cell(row=wsRow,column=wsCol) red = openpyxl.styles.colors.RED side = Side(style='medium',color=red) border = Border(left=side,right=side,top=side,bottom=side) #style = Style(border=border) #cell.style = style c.border = border.copy()
def set_allborder(ws, cell_range):
rows = list(ws.iter_rows(cell_range))
side = Side(border_style='thin', color="FF000000")
for pos_y, cells in enumerate(rows):
for pos_x, cell in enumerate(cells):
border = Border(
left=cell.border.left,
right=cell.border.right,
top=cell.border.top,
bottom=cell.border.bottom
)
border.left = side
border.right = side
border.top = side
border.bottom = side
cell.border = border
def compile_info(plot_objects, field=None):
"""
:plot_objects: List containing selected ObsPlot objects
:return: .xlsx containing a colored field map of the passed plots.
"""
wb = Workbook()
if field:
field_set = [field]
else:
field_set = get_field_object_set(plot_objects)
for field in field_set:
field_plots = get_plots_in_field(plot_objects, field)
worksheet = wb.create_sheet()
worksheet.title = field.field_name
experiment_current = field_plots[0].experiment
experiment_colors = iter(COLOR_SET)
cell_color = experiment_colors.next()
for plot in field_plots:
if plot.experiment != experiment_current:
experiment_current = plot.experiment
# Cycles through three experiment colors so the spreadsheet doesn't look incredibly dull
try:
cell_color = experiment_colors.next()
except StopIteration:
experiment_colors = iter(COLOR_SET)
cell_color = experiment_colors.next()
cell_fill = PatternFill(start_color=cell_color, end_color=cell_color, fill_type="solid")
side = Side(border_style="thin", color="003300")
for coordinate in plot.get_coordinates():
worksheet[coordinate] = plot.plot_id
worksheet[coordinate].fill = cell_fill
border = Border(bottom=worksheet[coordinate].border.bottom)
if check_row_even(coordinate): # Slips in a border every 2 rows
border.bottom = side
worksheet[coordinate].border = border
domain = get_plot_domains(field_plots)
add_axes(worksheet, domain, field_plots)
wb.remove_sheet(wb.get_sheet_by_name("Sheet"))
return wb
def SetCellFmt(self,cell,hAlign,vAlign,fmt,value):
if (align == 'C'):
align = Alignment(horizontal='center',vertical='center')
elif (align == 'L'):
align = Alignment(horizontal='left',vertical='center')
elif (align == 'R'):
align = Alignment(horizontal='right',vertical='center')
else:
align = Alignment(horizontal='right',vertical='center')
side = Side(style='thin')
border = Border(left=side,right=side,top=side,bottom=side)
if (fmt == 'F'):
fmt = '0.00'
cell.number_format = fmt
cell.alignment = align.copy()
cell.border = border.copy()
cell.value = value
def set_border(ws, cell_range):
rows = list(ws.iter_rows(cell_range))
side = Side(border_style='thin', color="FF000000")
max_y = len(rows) - 1 # index of the last row
for pos_y, cells in enumerate(rows):
max_x = len(cells) - 1 # index of the last cell
for pos_x, cell in enumerate(cells):
border = Border(
left=cell.border.left,
right=cell.border.right,
top=cell.border.top,
bottom=cell.border.bottom
)
if pos_x == 0:
border.left = side
if pos_x == max_x:
border.right = side
if pos_y == 0:
border.top = side
if pos_y == max_y:
border.bottom = side
# set new border only if it's one of the edge cells
if pos_x == 0 or pos_x == max_x or pos_y == 0 or pos_y == max_y:
cell.border = border
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=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)
c_r_alignment = Alignment(vertical='bottom',
horizontal='center',
text_rotation=0,
wrap_text=True,
shrink_to_fit=False,
indent=0)
# Img
img = Image("excelexporters/myems.png")
# img = Image("myems.png")
img.width = img.width * 1.06
img.height = img.height * 1.06
ws.add_image(img, 'B1')
# Title
ws.row_dimensions[3].height = 60
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[:
10] + "__" + reporting_end_datetime_local[:
10]
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~10 table_data
# Total: 5 rows
# if has not energy data: set low height for rows
#################################################
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
if has_energy_data_flag:
ws['B6'].font = title_font
ws['B6'] = name + ' 能耗分析'
category = reporting_period_data['names']
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'] = '能耗'
ws['B8'].border = f_border
ws['B9'].font = title_font
ws['B9'].alignment = c_c_alignment
ws['B9'] = '环比'
ws['B9'].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'] = reporting_period_data['names'][
i] + " (" + reporting_period_data['units'][i] + ")"
ws[col + '7'].border = f_border
ws[col + '8'].font = name_font
ws[col + '8'].alignment = c_c_alignment
ws[col + '8'] = round(reporting_period_data['subtotals'][i], 2)
ws[col + '8'].border = f_border
ws[col + '9'].font = name_font
ws[col + '9'].alignment = c_c_alignment
ws[col + '9'] = str(round(reporting_period_data['increment_rates'][i] * 100, 2)) + "%" \
if reporting_period_data['increment_rates'][i] is not None else "-"
ws[col + '9'].border = f_border
# TCE TCO2E
end_col = col
# TCE
tce_col = chr(ord(end_col) + 1)
ws[tce_col + '7'].fill = table_fill
ws[tce_col + '7'].font = name_font
ws[tce_col + '7'].alignment = c_c_alignment
ws[tce_col + '7'] = "吨标准煤 (TCE)"
ws[tce_col + '7'].border = f_border
ws[tce_col + '8'].font = name_font
ws[tce_col + '8'].alignment = c_c_alignment
ws[tce_col + '8'] = round(
reporting_period_data['total_in_kgce'] / 1000, 2)
ws[tce_col + '8'].border = f_border
ws[tce_col + '9'].font = name_font
ws[tce_col + '9'].alignment = c_c_alignment
ws[tce_col + '9'] = str(round(reporting_period_data['increment_rate_in_kgce'] * 100, 2)) + "%" \
if reporting_period_data['increment_rate_in_kgce'] is not None else "-"
ws[tce_col + '9'].border = f_border
# TCO2E
tco2e_col = chr(ord(end_col) + 2)
ws[tco2e_col + '7'].fill = table_fill
ws[tco2e_col + '7'].font = name_font
ws[tco2e_col + '7'].alignment = c_c_alignment
ws[tco2e_col + '7'] = "吨二氧化碳排放 (TCO2E)"
ws[tco2e_col + '7'].border = f_border
ws[tco2e_col + '8'].font = name_font
ws[tco2e_col + '8'].alignment = c_c_alignment
ws[tco2e_col + '8'] = round(
reporting_period_data['total_in_kgco2e'] / 1000, 2)
ws[tco2e_col + '8'].border = f_border
ws[tco2e_col + '9'].font = name_font
ws[tco2e_col + '9'].alignment = c_c_alignment
ws[tco2e_col + '9'] = str(round(reporting_period_data['increment_rate_in_kgco2e'] * 100, 2)) + "%" \
if reporting_period_data['increment_rate_in_kgco2e'] is not None else "-"
ws[tco2e_col + '9'].border = f_border
else:
for i in range(6, 9 + 1):
ws.row_dimensions[i].height = 0.1
#################################################
# Second: 分时电耗
# 12: title
# 13: table title
# 14~17 table_data
# Total: 6 rows
################################################
has_ele_peak_flag = True
if "toppeaks" not in reporting_period_data.keys() or \
reporting_period_data['toppeaks'] is None or \
len(reporting_period_data['toppeaks']) == 0:
has_ele_peak_flag = False
if has_ele_peak_flag:
ws['B12'].font = title_font
ws['B12'] = name + ' 分时电耗'
ws.row_dimensions[13].height = 60
ws['B13'].fill = table_fill
ws['B13'].font = name_font
ws['B13'].alignment = c_c_alignment
ws['B13'].border = f_border
ws['C13'].fill = table_fill
ws['C13'].font = name_font
ws['C13'].alignment = c_c_alignment
ws['C13'].border = f_border
ws['C13'] = '分时电耗'
ws['B14'].font = title_font
ws['B14'].alignment = c_c_alignment
ws['B14'] = '尖'
ws['B14'].border = f_border
ws['C14'].font = title_font
ws['C14'].alignment = c_c_alignment
ws['C14'].border = f_border
ws['C14'] = round(reporting_period_data['toppeaks'][0], 2)
ws['B15'].font = title_font
ws['B15'].alignment = c_c_alignment
ws['B15'] = '峰'
ws['B15'].border = f_border
ws['C15'].font = title_font
ws['C15'].alignment = c_c_alignment
ws['C15'].border = f_border
ws['C15'] = round(reporting_period_data['onpeaks'][0], 2)
ws['B16'].font = title_font
ws['B16'].alignment = c_c_alignment
ws['B16'] = '平'
ws['B16'].border = f_border
ws['C16'].font = title_font
ws['C16'].alignment = c_c_alignment
ws['C16'].border = f_border
ws['C16'] = round(reporting_period_data['midpeaks'][0], 2)
ws['B17'].font = title_font
ws['B17'].alignment = c_c_alignment
ws['B17'] = '谷'
ws['B17'].border = f_border
ws['C17'].font = title_font
ws['C17'].alignment = c_c_alignment
ws['C17'].border = f_border
ws['C17'] = round(reporting_period_data['offpeaks'][0], 2)
pie = PieChart()
pie.title = name + ' 分时电耗'
labels = Reference(ws, min_col=2, min_row=14, max_row=17)
pie_data = Reference(ws, min_col=3, min_row=13, max_row=17)
pie.add_data(pie_data, titles_from_data=True)
pie.set_categories(labels)
pie.height = 7.25 # cm 1.05*5 1.05cm = 30 pt
pie.width = 9
# pie.title = "Pies sold by category"
s1 = pie.series[0]
s1.dLbls = DataLabelList()
s1.dLbls.showCatName = False # 标签显示
s1.dLbls.showVal = True # 数量显示
s1.dLbls.showPercent = True # 百分比显示
# s1 = CharacterProperties(sz=1800) # 图表中字体大小 *100
ws.add_chart(pie, "D13")
else:
for i in range(12, 18 + 1):
ws.row_dimensions[i].height = 0.1
# end_row 10
# start_row 12
################################################
# Third: 子空间能耗
# 19: title
# 20: table title
# 21~24 table_data
# Total: 6 rows
################################################
has_child_flag = True
# Judge if the space has child space, if not, delete it.
if "child_space" not in report.keys() or "energy_category_names" not in report['child_space'].keys() or \
len(report['child_space']["energy_category_names"]) == 0 \
or 'child_space_names_array' not in report['child_space'].keys() \
or report['child_space']['energy_category_names'] is None \
or len(report['child_space']['child_space_names_array']) == 0 \
or len(report['child_space']['child_space_nchild_space_names_arrayames_array'][0]) == 0:
has_child_flag = False
current_row_number = 19
if has_child_flag:
child = report['child_space']
child_spaces = child['child_space_names_array'][0]
child_subtotals = child['subtotals_array'][0]
ws['B19'].font = title_font
ws['B19'] = name + ' 子空间能耗'
ws.row_dimensions[20].height = 60
ws['B20'].fill = table_fill
ws['B20'].border = f_border
ca_len = len(child['energy_category_names'])
table_start_row_number = 20
for i in range(0, ca_len):
row = chr(ord('C') + i)
ws[row + '20'].fill = table_fill
ws[row + '20'].font = title_font
ws[row + '20'].alignment = c_c_alignment
ws[row + '20'].border = f_border
ws[row + '20'] = child['energy_category_names'][i] + ' (' + child[
'units'][i] + ')'
space_len = len(child['child_space_names_array'][0])
for i in range(0, space_len):
row = str(i + 21)
ws['B' + row].font = name_font
ws['B' + row].alignment = c_c_alignment
ws['B' + row] = child['child_space_names_array'][0][i]
ws['B' + row].border = f_border
for j in range(0, ca_len):
col = chr(ord('C') + j)
ws[col + row].font = name_font
ws[col + row].alignment = c_c_alignment
ws[col + row] = round(child['subtotals_array'][j][i], 2)
ws[col + row].border = f_border
table_end_row_number = 20 + space_len
chart_start_row_number = 20 + space_len + 1
for i in range(0, ca_len):
# pie
# 25~30: pie
pie = PieChart()
pie.title = ws.cell(column=3 + i, row=table_start_row_number).value
labels = Reference(ws,
min_col=2,
min_row=table_start_row_number + 1,
max_row=table_end_row_number)
pie_data = Reference(ws,
min_col=3 + i,
min_row=table_start_row_number,
max_row=table_end_row_number)
pie.add_data(pie_data, titles_from_data=True)
pie.set_categories(labels)
pie.height = 6.6 # cm 1.05*5 1.05cm = 30 pt
pie.width = 8
# pie.title = "Pies sold by category"
s1 = pie.series[0]
s1.dLbls = DataLabelList()
s1.dLbls.showCatName = False # 标签显示
s1.dLbls.showVal = True # 数量显示
s1.dLbls.showPercent = True # 百分比显示
# s1 = CharacterProperties(sz=1800) # 图表中字体大小 *100
chart_cell = ''
if i % 2 == 0:
chart_cell = 'B' + str(chart_start_row_number)
else:
chart_cell = 'E' + str(chart_start_row_number)
chart_start_row_number += 5
# ws.add_chart(pie, chart_cell)
# chart_col = chr(ord('B') + 2 * j)
# chart_cell = chart_col + '25'
ws.add_chart(pie, chart_cell)
current_row_number = chart_start_row_number
if ca_len % 2 == 1:
current_row_number += 5
current_row_number += 1
################################################
# Fourth: 能耗详情
# current_row_number: title
# current_row_number+1 ~ current_row_number+1+ca_len*6-1: line
# current_row_number+1+ca_len*6: table title
# current_row_number+1+ca_len*6~: table_data
################################################
reporting_period_data = report['reporting_period']
times = reporting_period_data['timestamps']
has_detail_data_flag = True
ca_len = len(report['reporting_period']['names'])
real_timestamps_len = timestamps_data_not_equal_0(
report['parameters']['timestamps'])
table_row = current_row_number + ca_len * 6 + real_timestamps_len * 7 + 2
chart_start_row_number = current_row_number + 1
if "timestamps" not in reporting_period_data.keys() or \
reporting_period_data['timestamps'] is None or \
len(reporting_period_data['timestamps']) == 0:
has_detail_data_flag = False
if has_detail_data_flag:
ws['B' + str(current_row_number)].font = title_font
ws['B' + str(current_row_number)] = name + ' 详细数据'
ws.row_dimensions[table_row].height = 60
ws['B' + str(table_row)].fill = table_fill
ws['B' + str(table_row)].font = title_font
ws['B' + str(table_row)].border = f_border
ws['B' + str(table_row)].alignment = c_c_alignment
ws['B' + str(table_row)] = '日期时间'
time = times[0]
has_data = False
max_row = 0
if len(time) > 0:
has_data = True
max_row = table_row + len(time)
print("max_row", max_row)
if has_data:
for i in range(0, len(time)):
col = 'B'
row = str(table_row + 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):
# 38 title
col = chr(ord('C') + i)
ws[col + str(table_row)].fill = table_fill
ws[col + str(table_row)].font = title_font
ws[col + str(table_row)].alignment = c_c_alignment
ws[col + str(table_row)] = reporting_period_data['names'][i] + \
" (" + reporting_period_data['units'][i] + ")"
ws[col + str(table_row)].border = f_border
# 39 data
time = times[i]
time_len = len(time)
for j in range(0, time_len):
row = str(table_row + 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_data['values'][i][j], 2)
ws[col + row].border = f_border
current_row_number = table_row + 1 + len(times[0])
ws['B' + str(current_row_number)].font = title_font
ws['B' + str(current_row_number)].alignment = c_c_alignment
ws['B' + str(current_row_number)].border = f_border
ws['B' + str(current_row_number)] = '小计'
for i in range(0, ca_len):
col = chr(ord('C') + i)
ws[col + str(current_row_number)].font = title_font
ws[col + str(current_row_number)].alignment = c_c_alignment
ws[col + str(current_row_number)].border = f_border
ws[col + str(current_row_number)] = round(
reporting_period_data['subtotals'][i], 2)
# line
# 39~: line
line = LineChart()
line.title = '报告期消耗 - ' + ws.cell(column=3 + i,
row=table_row).value
labels = Reference(ws,
min_col=2,
min_row=table_row + 1,
max_row=max_row)
line_data = Reference(ws,
min_col=3 + i,
min_row=table_row,
max_row=max_row) # openpyxl bug
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 # cm 1.05*5 1.05cm = 30 pt
line.width = 24
# pie.title = "Pies sold by category"
line.dLbls = DataLabelList()
line.dLbls.dLblPos = 't'
# line.dLbls.showCatName = True # label show
line.dLbls.showVal = True # val show
line.dLbls.showPercent = True # percent show
# s1 = CharacterProperties(sz=1800) # font size *100
chart_col = 'B'
chart_cell = chart_col + str(chart_start_row_number + 6 * i)
ws.add_chart(line, chart_cell)
current_sheet_parameters_row_number = chart_start_row_number + ca_len * 6
##########################################
has_parameters_names_and_timestamps_and_values_data = True
if 'parameters' not in report.keys() or \
report['parameters'] is None or \
'names' not in report['parameters'].keys() or \
report['parameters']['names'] is None or \
len(report['parameters']['names']) == 0 or \
'timestamps' not in report['parameters'].keys() or \
report['parameters']['timestamps'] is None or \
len(report['parameters']['timestamps']) == 0 or \
'values' not in report['parameters'].keys() or \
report['parameters']['values'] is None or \
len(report['parameters']['values']) == 0 or \
timestamps_data_all_equal_0(report['parameters']['timestamps']):
has_parameters_names_and_timestamps_and_values_data = False
if has_parameters_names_and_timestamps_and_values_data:
###############################
# new worksheet
###############################
parameters_data = report['parameters']
parameters_names_len = len(parameters_data['names'])
parameters_ws = wb.create_sheet('相关参数')
parameters_timestamps_data_max_len = \
get_parameters_timestamps_lists_max_len(list(parameters_data['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")
img.width = img.width * 0.85
img.height = img.height * 0.85
# img = Image("myems.png")
parameters_ws.add_image(img, 'B1')
# Title
parameters_ws.row_dimensions[3].height = 60
parameters_ws['B3'].font = name_font
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'].font = name_font
parameters_ws['C3'] = name
parameters_ws['D3'].font = name_font
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'].font = name_font
parameters_ws['E3'] = period_type
parameters_ws['F3'].font = name_font
parameters_ws['F3'].alignment = b_r_alignment
parameters_ws['F3'] = 'Date:'
parameters_ws['G3'].border = b_border
parameters_ws['G3'].alignment = b_c_alignment
parameters_ws['G3'].font = name_font
parameters_ws[
'G3'] = reporting_start_datetime_local + "__" + reporting_end_datetime_local
parameters_ws.merge_cells("G3:H3")
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)] = name + ' 相关参数'
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 = 'B'
for i in range(0, parameters_names_len):
if len(parameters_data['timestamps'][i]) == 0:
continue
parameters_ws[table_current_col_number +
str(parameters_ws_current_row_number -
1)].fill = table_fill
parameters_ws[table_current_col_number +
str(parameters_ws_current_row_number -
1)].border = f_border
col = chr(ord(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_data['names'][i]
table_current_row_number = parameters_ws_current_row_number
for j, value in enumerate(list(parameters_data['timestamps'][i])):
col = 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 = chr(ord(col) + 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
parameters_ws[col + str(table_current_row_number)] = round(
parameters_data['values'][i][j], 2)
table_current_row_number += 1
table_current_col_number = chr(ord(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)] = name + ' 相关参数'
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_data['timestamps'][i]) == 0:
continue
line = LineChart()
data_col = 3 + col_index * 3
labels_col = 2 + col_index * 3
col_index += 1
line.title = '相关参数 - ' + \
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_data['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_data['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 generate_excel(report, space_name, reporting_start_datetime_local, reporting_end_datetime_local):
wb = Workbook()
ws = wb.active
ws.title = "StoreBatch"
# Row height
ws.row_dimensions[1].height = 102
for i in range(2, 5 + 1):
ws.row_dimensions[i].height = 42
for i in range(6, len(report['stores']) + 15):
ws.row_dimensions[i].height = 60
# 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)
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=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'] = 'Space:'
ws['C3'].border = b_border
ws['C3'].alignment = b_c_alignment
ws['C3'] = space_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
# Title
ws['B6'].border = f_border
ws['B6'].font = name_font
ws['B6'].alignment = c_c_alignment
ws['B6'].fill = table_fill
ws['B6'] = 'ID'
ws['C6'].border = f_border
ws['C6'].font = name_font
ws['C6'].alignment = c_c_alignment
ws['C6'].fill = table_fill
ws['C6'] = 'Name'
ws['D6'].border = f_border
ws['D6'].alignment = c_c_alignment
ws['D6'].font = name_font
ws['D6'].fill = table_fill
ws['D6'] = 'Space'
ca_len = len(report['energycategories'])
for i in range(0, ca_len):
col = chr(ord('E') + i)
ws[col + '6'].fill = table_fill
ws[col + '6'].font = name_font
ws[col + '6'].alignment = c_c_alignment
ws[col + '6'] = report['energycategories'][i]['name'] + \
" (" + report['energycategories'][i]['unit_of_measure'] + ")"
ws[col + '6'].border = f_border
current_row_number = 7
for i in range(0, len(report['stores'])):
ws['B' + str(current_row_number)].font = title_font
ws['B' + str(current_row_number)].border = f_border
ws['B' + str(current_row_number)].alignment = c_c_alignment
ws['B' + str(current_row_number)] = report['stores'][i]['id']
ws['C' + str(current_row_number)].font = title_font
ws['C' + str(current_row_number)].border = f_border
ws['C' + str(current_row_number)].alignment = c_c_alignment
ws['C' + str(current_row_number)] = report['stores'][i]['store_name']
ws['D' + str(current_row_number)].font = title_font
ws['D' + str(current_row_number)].border = f_border
ws['D' + str(current_row_number)].alignment = c_c_alignment
ws['D' + str(current_row_number)] = report['stores'][i]['space_name']
ca_len = len(report['stores'][i]['values'])
for j in range(0, ca_len):
col = chr(ord('E') + j)
ws[col + str(current_row_number)].font = data_font
ws[col + str(current_row_number)].border = f_border
ws[col + str(current_row_number)].alignment = c_c_alignment
ws[col + str(current_row_number)] = round(report['stores'][i]['values'][j], 2)
current_row_number += 1
filename = str(uuid.uuid4()) + '.xlsx'
wb.save(filename)
return filename
def generate_excel(report, name, reporting_start_datetime_local,
reporting_end_datetime_local, period_type):
wb = Workbook()
ws = wb.active
ws.title = "StoreStatistics"
# 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'] = name
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_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: Statistics
# 6: title
# 7: table title
# 8~ca_len table_data
####################################################################################################################
reporting_period_data = report['reporting_period']
if "names" not in reporting_period_data.keys() or \
reporting_period_data['names'] is None or \
len(reporting_period_data['names']) == 0:
filename = str(uuid.uuid4()) + '.xlsx'
wb.save(filename)
return filename
ws['B6'].font = title_font
ws['B6'] = name + ' ' + 'Statistics'
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'] = 'Reporting Period'
ws['B7'].border = f_border
ws['C7'].font = title_font
ws['C7'].alignment = c_c_alignment
ws['C7'] = 'Arithmetic Mean'
ws['C7'].border = f_border
ws['D7'].font = title_font
ws['D7'].alignment = c_c_alignment
ws['D7'] = 'Median (Middle Value)'
ws['D7'].border = f_border
ws['E7'].font = title_font
ws['E7'].alignment = c_c_alignment
ws['E7'] = 'Minimum Value'
ws['E7'].border = f_border
ws['F7'].font = title_font
ws['F7'].alignment = c_c_alignment
ws['F7'] = 'Maximum Value'
ws['F7'].border = f_border
ws['G7'].font = title_font
ws['G7'].alignment = c_c_alignment
ws['G7'] = 'Sample Standard Deviation'
ws['G7'].border = f_border
ws['H7'].font = title_font
ws['H7'].alignment = c_c_alignment
ws['H7'] = 'Sample Variance'
ws['H7'].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] + " )"
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)] = 'Increment Rate'
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['means'][i], 2) \
if reporting_period_data['means'][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['means_increment_rate'][i] * 100, 2)) + "%" \
if reporting_period_data['means_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['medians'][i], 2) \
if reporting_period_data['medians'][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['medians_increment_rate'][i] * 100, 2)) + "%" \
if reporting_period_data['medians_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['minimums'][i], 2) \
if reporting_period_data['minimums'][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['minimums_increment_rate'][i] * 100, 2)) + "%" \
if reporting_period_data['minimums_increment_rate'][i] is not None else '0.00%'
ws['E' + str(row + 1)].border = f_border
ws['F' + str(row)].font = name_font
ws['F' + str(row)].alignment = c_c_alignment
ws['F' + str(row)] = round(reporting_period_data['maximums'][i], 2) \
if reporting_period_data['maximums'][i] is not None else ''
ws['F' + str(row)].border = f_border
ws['F' + str(row)].number_format = '0.00'
ws['F' + str(row + 1)].font = name_font
ws['F' + str(row + 1)].alignment = c_c_alignment
ws['F' + 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['F' + str(row + 1)].border = f_border
ws['G' + str(row)].font = name_font
ws['G' + str(row)].alignment = c_c_alignment
ws['G' + str(row)] = round(reporting_period_data['stdevs'][i], 2) \
if reporting_period_data['stdevs'][i] is not None else ''
ws['G' + str(row)].border = f_border
ws['G' + str(row)].number_format = '0.00'
ws['G' + str(row + 1)].font = name_font
ws['G' + str(row + 1)].alignment = c_c_alignment
ws['G' + str(row + 1)] = str(round(reporting_period_data['stdevs_increment_rate'][i] * 100, 2)) + "%" \
if reporting_period_data['stdevs_increment_rate'][i] is not None else '0.00%'
ws['G' + str(row + 1)].border = f_border
ws['H' + str(row)].font = name_font
ws['H' + str(row)].alignment = c_c_alignment
ws['H' + str(row)] = round(reporting_period_data['variances'][i], 2) \
if reporting_period_data['variances'][i] is not None else ''
ws['H' + str(row)].border = f_border
ws['H' + str(row)].number_format = '0.00'
ws['H' + str(row + 1)].font = name_font
ws['H' + str(row + 1)].alignment = c_c_alignment
ws['H' + str(row + 1)] = str(round(reporting_period_data['variances_increment_rate'][i] * 100, 2)) + "%" \
if reporting_period_data['variances_increment_rate'][i] is not None else '0.00%'
ws['H' + str(row + 1)].border = f_border
####################################################################################################################
# Second: Reporting Period Consumption
# 9 + ca_len * 2: title
# 10 + ca_len * 2: table title
# per_unit_area_start_row_number + 2 ~ per_unit_area_start_row_number + 2 + ca_len : table_data
####################################################################################################################
names = reporting_period_data['names']
ca_len = len(names)
per_unit_area_start_row_number = 9 + ca_len * 2
ws['B' + str(per_unit_area_start_row_number)].font = title_font
ws['B' + str(
per_unit_area_start_row_number)] = name + ' ' + 'Per Unit Area' + str(
report['store']['area']) + 'M²'
category = reporting_period_data['names']
# table_title
ws['B' + str(per_unit_area_start_row_number + 1)].fill = table_fill
ws['B' + str(per_unit_area_start_row_number + 1)].font = title_font
ws['B' + str(per_unit_area_start_row_number + 1)].alignment = c_c_alignment
ws['B' + str(per_unit_area_start_row_number + 1)] = 'Reporting Period'
ws['B' + str(per_unit_area_start_row_number + 1)].border = f_border
ws['C' + str(per_unit_area_start_row_number + 1)].font = title_font
ws['C' + str(per_unit_area_start_row_number + 1)].alignment = c_c_alignment
ws['C' + str(per_unit_area_start_row_number + 1)] = 'Arithmetic Mean'
ws['C' + str(per_unit_area_start_row_number + 1)].border = f_border
ws['D' + str(per_unit_area_start_row_number + 1)].font = title_font
ws['D' + str(per_unit_area_start_row_number + 1)].alignment = c_c_alignment
ws['D' + str(per_unit_area_start_row_number + 1)] = 'Median (Middle Value)'
ws['D' + str(per_unit_area_start_row_number + 1)].border = f_border
ws['E' + str(per_unit_area_start_row_number + 1)].font = title_font
ws['E' + str(per_unit_area_start_row_number + 1)].alignment = c_c_alignment
ws['E' + str(per_unit_area_start_row_number + 1)] = 'Minimum Value'
ws['E' + str(per_unit_area_start_row_number + 1)].border = f_border
ws['F' + str(per_unit_area_start_row_number + 1)].font = title_font
ws['F' + str(per_unit_area_start_row_number + 1)].alignment = c_c_alignment
ws['F' + str(per_unit_area_start_row_number + 1)] = 'Maximum Value'
ws['F' + str(per_unit_area_start_row_number + 1)].border = f_border
ws['G' + str(per_unit_area_start_row_number + 1)].font = title_font
ws['G' + str(per_unit_area_start_row_number + 1)].alignment = c_c_alignment
ws['G' +
str(per_unit_area_start_row_number + 1)] = 'Sample Standard Deviation'
ws['G' + str(per_unit_area_start_row_number + 1)].border = f_border
ws['H' + str(per_unit_area_start_row_number + 1)].font = title_font
ws['H' + str(per_unit_area_start_row_number + 1)].alignment = c_c_alignment
ws['H' + str(per_unit_area_start_row_number + 1)] = 'Sample Variance'
ws['H' + str(per_unit_area_start_row_number + 1)].border = f_border
# table_data
for i, value in enumerate(category):
row_data = per_unit_area_start_row_number + 2 + i
ws['B' + str(row_data)].font = name_font
ws['B' + str(row_data)].alignment = c_c_alignment
ws['B' + str(row_data)] = reporting_period_data['names'][
i] + " (" + reporting_period_data['units'][i] + "/M²)"
ws['B' + str(row_data)].border = f_border
ws['C' + str(row_data)].font = name_font
ws['C' + str(row_data)].alignment = c_c_alignment
if reporting_period_data['means_per_unit_area'][i] \
or reporting_period_data['means_per_unit_area'][i] == 0:
ws['C' + str(row_data)] = round(
reporting_period_data['means_per_unit_area'][i], 2)
ws['C' + str(row_data)].border = f_border
ws['C' + str(row_data)].number_format = '0.00'
ws['D' + str(row_data)].font = name_font
ws['D' + str(row_data)].alignment = c_c_alignment
if reporting_period_data['medians_per_unit_area'][i] \
or reporting_period_data['medians_per_unit_area'][i] == 0:
ws['D' + str(row_data)] = round(
reporting_period_data['medians_per_unit_area'][i], 2)
ws['D' + str(row_data)].border = f_border
ws['D' + str(row_data)].number_format = '0.00'
ws['E' + str(row_data)].font = name_font
ws['E' + str(row_data)].alignment = c_c_alignment
if reporting_period_data['minimums_per_unit_area'][i] \
or reporting_period_data['minimums_per_unit_area'][i] == 0:
ws['E' + str(row_data)] = round(
reporting_period_data['minimums_per_unit_area'][i], 2)
ws['E' + str(row_data)].border = f_border
ws['E' + str(row_data)].number_format = '0.00'
ws['F' + str(row_data)].font = name_font
ws['F' + str(row_data)].alignment = c_c_alignment
if reporting_period_data['maximums_per_unit_area'][i] \
or reporting_period_data['maximums_per_unit_area'][i] == 0:
ws['F' + str(row_data)] = round(
reporting_period_data['maximums_per_unit_area'][i], 2)
ws['F' + str(row_data)].border = f_border
ws['F' + str(row_data)].number_format = '0.00'
ws['G' + str(row_data)].font = name_font
ws['G' + str(row_data)].alignment = c_c_alignment
if (reporting_period_data['stdevs_per_unit_area'][i]) \
or reporting_period_data['stdevs_per_unit_area'][i] == 0:
ws['G' + str(row_data)] = round(
reporting_period_data['stdevs_per_unit_area'][i], 2)
ws['G' + str(row_data)].border = f_border
ws['G' + str(row_data)].number_format = '0.00'
ws['H' + str(row_data)].font = name_font
ws['H' + str(row_data)].alignment = c_c_alignment
if reporting_period_data['variances_per_unit_area'][i] \
or reporting_period_data['variances_per_unit_area'][i] == 0:
ws['H' + str(row_data)] = round(
reporting_period_data['variances_per_unit_area'][i], 2)
ws['H' + str(row_data)].border = f_border
ws['H' + str(row_data)].number_format = '0.00'
####################################################################################################################
# Third: Detailed Data
# detailed_start_row_number~ detailed_start_row_number+time_len: line
# detailed_start_row_number+1: table title
# i + analysis_end_row_number + 2 + 6 * ca_len~: 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
timestamps = reporting_period_data['timestamps'][0]
names = reporting_period_data['names']
ca_len = len(names)
time_len = len(timestamps)
parameters_names_len = len(report['parameters']['names'])
parameters_parameters_datas_len = 0
analysis_end_row_number = 12 + 3 * ca_len
current_row_number = analysis_end_row_number
values = reporting_period_data['values']
if has_timestamps_flag:
for i in range(0, parameters_names_len):
if len(report['parameters']['timestamps'][i]) == 0:
continue
parameters_parameters_datas_len += 1
detail_data_table_start_row_number = current_row_number + (
ca_len + parameters_parameters_datas_len) * 6 + 2
ws['B' + str(current_row_number)].font = title_font
ws['B' + str(current_row_number)] = name + ' ' + 'Detailed Data'
# table_title
ws['B' + str(detail_data_table_start_row_number)].fill = table_fill
ws['B' + str(detail_data_table_start_row_number)].font = name_font
ws['B' +
str(detail_data_table_start_row_number)].alignment = c_c_alignment
ws['B' + str(detail_data_table_start_row_number)] = 'Datetime'
ws['B' + str(detail_data_table_start_row_number)].border = f_border
current_row_number += 1
for i in range(0, ca_len):
col = chr(ord('C') + i)
ws[col + str(detail_data_table_start_row_number)].font = name_font
ws[col + str(
detail_data_table_start_row_number)].alignment = c_c_alignment
ws[col + str(detail_data_table_start_row_number)] = \
names[i] + " - (" + reporting_period_data['units'][i] + ")"
ws[col + str(detail_data_table_start_row_number)].border = f_border
# table_date
for i in range(0, time_len):
rows = i + detail_data_table_start_row_number + 1
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 = chr(ord('C') + index)
ws[col + str(rows)].font = name_font
ws[col + str(rows)].alignment = c_c_alignment
ws[col + str(rows)] = round(values[index][i], 2)
ws[col + str(rows)].number_format = '0.00'
ws[col + str(rows)].border = f_border
# Subtotal
row_subtotals = detail_data_table_start_row_number + 1 + time_len
ws['B' + str(row_subtotals)].font = name_font
ws['B' + str(row_subtotals)].alignment = c_c_alignment
ws['B' + str(row_subtotals)] = 'Subtotal'
ws['B' + str(row_subtotals)].border = f_border
for i in range(0, ca_len):
col = chr(ord('C') + i)
ws[col + str(row_subtotals)].font = name_font
ws[col + str(row_subtotals)].alignment = c_c_alignment
ws[col + str(row_subtotals)] = round(
reporting_period_data['subtotals'][i], 2)
ws[col + str(row_subtotals)].border = f_border
ws[col + str(row_subtotals)].number_format = '0.00'
####################################################################################################################
# third: LineChart
# LineChart requires data from the detailed data table in the Excel file
# so print the detailed data table first and then print LineChart
####################################################################################################################
for i in range(0, ca_len):
line = LineChart()
line.title = "Reporting Period Consumption" + " - " + names[i] + \
"(" + reporting_period_data['units'][i] + ")"
line.style = 10
line.x_axis.majorTickMark = 'in'
line.y_axis.majorTickMark = 'in'
line.smooth = True
line.x_axis.crosses = 'min'
line.height = 8.25
line.width = 24
line.dLbls = DataLabelList()
line.dLbls.dLblPos = 't'
line.dLbls.showVal = True
times = Reference(ws,
min_col=2,
min_row=detail_data_table_start_row_number + 1,
max_row=detail_data_table_start_row_number + 1 +
time_len)
line_data = Reference(ws,
min_col=3 + i,
min_row=detail_data_table_start_row_number,
max_row=detail_data_table_start_row_number +
time_len)
line.add_data(line_data, titles_from_data=True)
line.set_categories(times)
ser = line.series[0]
ser.marker.symbol = "diamond"
ser.marker.size = 5
ws.add_chart(line, 'B' + str(current_row_number + 6 * i))
####################################################################################################################
has_parameters_names_and_timestamps_and_values_data = True
ca_len = len(report['reporting_period']['names'])
current_sheet_parameters_row_number = current_row_number + ca_len * 6
if 'parameters' not in report.keys() or \
report['parameters'] is None or \
'names' not in report['parameters'].keys() or \
report['parameters']['names'] is None or \
len(report['parameters']['names']) == 0 or \
'timestamps' not in report['parameters'].keys() or \
report['parameters']['timestamps'] is None or \
len(report['parameters']['timestamps']) == 0 or \
'values' not in report['parameters'].keys() or \
report['parameters']['values'] is None or \
len(report['parameters']['values']) == 0 or \
timestamps_data_all_equal_0(report['parameters']['timestamps']):
has_parameters_names_and_timestamps_and_values_data = False
if has_parameters_names_and_timestamps_and_values_data:
################################################################################################################
# new worksheet
################################################################################################################
parameters_data = report['parameters']
parameters_names_len = len(parameters_data['names'])
file_name = ws.title
parameters_ws = wb.create_sheet(file_name + 'Parameters')
parameters_timestamps_data_max_len = \
get_parameters_timestamps_lists_max_len(list(parameters_data['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'] = name
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)] = name + ' ' + '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_data['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_data['names'][i]
table_current_row_number = parameters_ws_current_row_number
for j, value in enumerate(list(parameters_data['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
parameters_ws[col + str(table_current_row_number)] = round(
parameters_data['values'][i][j], 2)
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)] = name + ' ' + '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_data['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_data['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_data['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 add_style_edi(edi_dataframe, PATH):
wb = load_workbook(PATH)
ws = wb['Export EDI']
nb_rows, nb_columns = edi_dataframe.shape
header_columns = [get_column_letter(col_idx) for col_idx in list(range(1, nb_columns + 2))]
medium = Side(border_style='thin', color='FF000000')
medium_borders = Border(top=medium, left=medium, right=medium, bottom=medium)
font_head_1 = Font(size=10, name='Arial', bold=True)
font_head = Font(size=20, name='Arial')
alignment_center = Alignment(horizontal='center', vertical='center', wrapText=True)
ws.row_dimensions[1].height=30
ws.freeze_panes = ws["C4"]
# Color
red = PatternFill(fill_type='solid', start_color='FF0000', end_color='FF0000')
orange_light = PatternFill(fill_type='solid', start_color='FED8B1', end_color='FED8B1')
orange_dark = PatternFill(fill_type='solid', start_color='FF8C00', end_color='FF8C00')
orange = PatternFill(fill_type='solid', start_color='FFA500', end_color='FFA500')
blue = PatternFill(fill_type='solid', start_color='87CEFA', end_color='87CEFA')
blue_light = PatternFill(fill_type='solid', start_color='B0E0E6', end_color='B0E0E6')
yellow_light = PatternFill(fill_type='solid', start_color='FFFFE0', end_color='FFFFE0')
yellow = PatternFill(fill_type='solid', start_color='FFFF00', end_color='FFFF00')
green_light = PatternFill(fill_type='solid', start_color='90EE90', end_color='90EE90')
for letter in [get_column_letter(col_idx) for col_idx in range(1, nb_columns+1)]:
for number in range(1, nb_rows+4):
ws[letter+str(number)].border = medium_borders
ws[letter+str(number)].alignment = alignment_center
## Head merge and precision
for index, letter in enumerate(header_columns[:12]):
ws[letter+"1"].font = font_head_1
ws[letter+"1"].value = ws[letter+"3"].value
if index < 8 :
ws[letter+"1"].fill = orange_light
elif index < 9 :
ws[letter+"1"].fill = orange_dark
else :
ws[letter+"1"].fill = orange
ws.merge_cells(letter+"1:"+letter+"3")
ws["M1"].value = "Encagement"
ws["M1"].font = font_head
ws["M1"].fill = blue
ws.merge_cells("M1:AL1")
for letter in ["M","N","O","P"]:
ws[letter+"2"].font = font_head_1
ws[letter+"2"].value = ws[letter+"3"].value
ws[letter+"2"].fill = blue
ws.merge_cells(letter+"2:"+letter+"3")
ws["Q2"].value = "Mesure sur site"
ws["Q2"].font = font_head_1
ws["Q2"].fill = blue
ws.merge_cells("Q2:U2")
ws["V2"].value = "Mesure Environnementale"
ws["V2"].font = font_head_1
ws["V2"].fill = blue
ws.merge_cells("V2:AL2")
ws["AM1"].value = "Prélèvement"
ws["AM1"].font = font_head
ws["AM1"].fill = blue
ws.merge_cells("AM1:BL1")
for letter in ["AM","AN","AO","AP"]:
ws[letter+"2"].value = ws[letter+"3"].value
ws[letter+"2"].font = font_head_1
ws[letter+"2"].fill = blue
ws.merge_cells(letter+"2:"+letter+"3")
ws["AQ2"].value = "Mesure sur site"
ws["AQ2"].font = font_head_1
ws["AQ2"].fill = blue
ws.merge_cells("AQ2:AU2")
ws["AV2"].value = "Mesure Environnementale"
ws["AV2"].font = font_head_1
ws["AV2"].fill = blue
ws.merge_cells("AV2:BL2")
for letter in ["BM","BN"] :
ws[letter+"1"].font = font_head
ws[letter+"1"].value = ws[letter+"3"].value
ws[letter+"1"].fill = red
ws.merge_cells(letter+"1:"+letter+"3")
for letter in ["BO","BP","BQ","BR","BS","BT","BU","BV"] :
ws[letter+"2"].font = font_head_1
ws[letter+"2"].value = ws[letter+"3"].value
ws[letter+"2"].fill = blue
ws.merge_cells(letter+"2:"+letter+"3")
ws["BO1"].value = "Echantillonage"
ws["BO1"].font = font_head
ws["BO1"].fill = blue
ws.merge_cells("BO1:BT1")
ws["BU1"].value = "Après Lyophilisation"
ws["BU1"].font = font_head
ws["BU1"].fill = blue
ws.merge_cells("BU1:BV1")
# column width
ws.column_dimensions["A"].width = 10
ws.column_dimensions["B"].width = 12
ws.column_dimensions["C"].width = 12
ws.column_dimensions["D"].width = 12
ws.column_dimensions["E"].width = 32
ws.column_dimensions["F"].width = 32
ws.column_dimensions["G"].width = 85
ws.column_dimensions["I"].width = 20
ws.column_dimensions["J"].width = 15
ws.column_dimensions["K"].width = 15
ws.column_dimensions["L"].width = 15
for letter in header_columns[12:64]:
ws.column_dimensions[letter].width = 15
ws[letter + "3"].fill = blue
ws.column_dimensions["BM"].width = 20
for letter in header_columns[65:74]:
ws.column_dimensions[letter].width = 30
ws[letter + "3"].fill = blue
# for letter in ["W","X","Y","Z","AA","AL","AM","AN","AO","AP","AQ"]:
# ws.column_dimensions[letter].width = 20
# if letter != "AQ":
# ws[letter + "3"].fill = blue
ws.column_dimensions["BN"].width = 85
for letter in ["H","N","AN"]:
ws.column_dimensions[letter].width = 0.01
# for letter in ["AS","AT","AU","AV","AW","AX"]:
# ws.column_dimensions[letter].width = 30
not_validated_fill = PatternFill(fill_type='solid', start_color='FF0000', end_color='FF0000')
not_validated_font = Font(size=10, name='Arial', bold=True, color="FFFFFF")
for row in range(4,nb_rows+4):
for index, letter in enumerate(header_columns):
if letter == "A":
ws[letter + str(row)].number_format="MMM-YY"
if letter in ["J","K","L"]:
ws[letter + str(row)].fill = yellow
if letter in ["D","M","AM"]:
ws[letter + str(row)].fill = red
if letter in ["I","O","P","AO","AP","BM","BN","BO","BP","BQ","BR","BS","BT","BU","BV"]:
ws[letter + str(row)].fill = blue_light
if letter in ["Q","R","S","T","U","AQ","AR","AS","AT","AU"]:
ws[letter + str(row)].fill = green_light
if index in range(21,38) or index in range(47,64):
ws[letter + str(row)].fill = yellow_light
if ws["BT"+str(row)].value :
if ws["BT"+str(row)].value < 3000:
ws["B"+str(row)].font= not_validated_font
ws["B"+str(row)].fill= not_validated_fill
ws["BT"+str(row)].font= not_validated_font
ws["BT"+str(row)].fill= not_validated_fill
if ws["BQ"+str(row)].value :
if ws["BQ"+str(row)].value < 500:
ws["B"+str(row)].font= not_validated_font
ws["B"+str(row)].fill= not_validated_fill
ws["BQ"+str(row)].font= not_validated_font
ws["BQ"+str(row)].fill= not_validated_fill
if ws["BM"+str(row)].value=="0%":
ws["B"+str(row)].font= not_validated_font
ws["B"+str(row)].fill= not_validated_fill
ws["BM"+str(row)].font= not_validated_font
ws["BM"+str(row)].fill= not_validated_fill
wb.save(PATH)
wb.close()
def generate_excel(report, name, reporting_start_datetime_local,
reporting_end_datetime_local, period_type):
wb = Workbook()
ws = wb.active
# Row height
ws.row_dimensions[1].height = 118
for i in range(2, 2000 + 1):
ws.row_dimensions[i].height = 30
# Col width
ws.column_dimensions['A'].width = 1.5
for i in range(ord('B'), ord('I')):
ws.column_dimensions[chr(i)].width = 15.0
# Font
name_font = Font(name='Constantia', size=15, bold=True)
title_font = Font(name='宋体', size=15, bold=True)
data_font = Font(name='Franklin Gothic Book', size=11)
table_fill = PatternFill(fill_type='solid', fgColor='1F497D')
f_border = Border(left=Side(border_style='medium', color='00000000'),
right=Side(border_style='medium', color='00000000'),
bottom=Side(border_style='medium', color='00000000'),
top=Side(border_style='medium', color='00000000'))
b_border = Border(bottom=Side(border_style='medium', color='00000000'), )
b_c_alignment = Alignment(vertical='bottom',
horizontal='center',
text_rotation=0,
wrap_text=False,
shrink_to_fit=False,
indent=0)
c_c_alignment = Alignment(vertical='center',
horizontal='center',
text_rotation=0,
wrap_text=False,
shrink_to_fit=False,
indent=0)
b_r_alignment = Alignment(vertical='bottom',
horizontal='right',
text_rotation=0,
wrap_text=False,
shrink_to_fit=False,
indent=0)
c_r_alignment = Alignment(vertical='bottom',
horizontal='center',
text_rotation=0,
wrap_text=False,
shrink_to_fit=False,
indent=0)
# Img
img = Image("excelexporters/myems.png")
ws.add_image(img, 'B1')
# Title
ws['B3'].font = name_font
ws['B3'].alignment = b_r_alignment
ws['B3'] = 'Name:'
ws['C3'].border = b_border
ws['C3'].alignment = b_c_alignment
ws['C3'].font = name_font
ws['C3'] = name
ws['D3'].font = name_font
ws['D3'].alignment = b_r_alignment
ws['D3'] = 'Period:'
ws['E3'].border = b_border
ws['E3'].alignment = b_c_alignment
ws['E3'].font = name_font
ws['E3'] = period_type
ws['F3'].font = name_font
ws['F3'].alignment = b_r_alignment
ws['F3'] = 'Date:'
ws.merge_cells("G3:J3")
for i in range(ord('G'), ord('K')):
ws[chr(i) + '3'].border = b_border
ws['G3'].alignment = b_c_alignment
ws['G3'].font = name_font
ws['G3'] = reporting_start_datetime_local + "__" + reporting_end_datetime_local
if "reporting_period" not in report.keys() or \
"difference_values" not in report['reporting_period'].keys() or \
len(report['reporting_period']['difference_values']) == 0:
filename = str(uuid.uuid4()) + '.xlsx'
wb.save(filename)
return filename
#################################################
has_difference_values_data_flag = True
if 'difference_values' not in report['reporting_period'].keys() or len(
report['reporting_period']['difference_values']) == 0:
has_difference_values_data_flag = False
current_row_number = 6
if has_difference_values_data_flag:
reporting_period_data = report['reporting_period']
category = report['meter']['energy_category_name']
ws['B' + str(current_row_number)].font = title_font
ws['B' + str(current_row_number)] = name + ' 报告期'
current_row_number += 1
ws['B' + str(current_row_number)].fill = table_fill
if not isinstance(category, list):
ws['C' + str(current_row_number)].fill = table_fill
ws['C' + str(current_row_number)].font = name_font
ws['C' + str(current_row_number)].alignment = c_c_alignment
ws['C' + str(current_row_number)].border = f_border
ws['C' + str(current_row_number)] = report['meter'][
'energy_category_name'] + " (" + report['meter'][
'unit_of_measure'] + ")"
current_row_number += 1
ws['B' + str(current_row_number)].font = title_font
ws['B' + str(current_row_number)].alignment = c_c_alignment
ws['B' + str(current_row_number)].border = f_border
ws['B' + str(current_row_number)] = '总表消耗'
ws['C' + str(current_row_number)].font = name_font
ws['C' + str(current_row_number)].alignment = c_c_alignment
ws['C' + str(current_row_number)].border = f_border
ws['C' + str(current_row_number)] = round(
reporting_period_data['master_meter_consumption_in_category'],
2)
current_row_number += 1
ws['B' + str(current_row_number)].font = title_font
ws['B' + str(current_row_number)].alignment = c_c_alignment
ws['B' + str(current_row_number)].border = f_border
ws['B' + str(current_row_number)] = '分表消耗'
ws['C' + str(current_row_number)].font = name_font
ws['C' + str(current_row_number)].alignment = c_c_alignment
ws['C' + str(current_row_number)].border = f_border
ws['C' + str(current_row_number)] = round(
reporting_period_data['submeters_consumption_in_category'], 2)
current_row_number += 1
ws['B' + str(current_row_number)].font = title_font
ws['B' + str(current_row_number)].alignment = c_c_alignment
ws['B' + str(current_row_number)].border = f_border
ws['B' + str(current_row_number)] = '差值'
ws['C' + str(current_row_number)].font = name_font
ws['C' + str(current_row_number)].alignment = c_c_alignment
ws['C' + str(current_row_number)].border = f_border
ws['C' + str(current_row_number)] = round(
reporting_period_data['difference_in_category'], 2)
current_row_number += 1
ws['B' + str(current_row_number)].font = title_font
ws['B' + str(current_row_number)].alignment = c_c_alignment
ws['B' + str(current_row_number)].border = f_border
ws['B' + str(current_row_number)] = '差值百分比'
ws['C' + str(current_row_number)].font = name_font
ws['C' + str(current_row_number)].alignment = c_c_alignment
ws['C' + str(current_row_number)].border = f_border
ws['C' + str(current_row_number)] = str(
round(reporting_period_data['percentage_difference'] * 100,
2)) + '%'
current_row_number += 2
time = reporting_period_data['timestamps']
has_time_data_flag = False
if time is not None and len(time) > 0:
has_time_data_flag = True
if has_time_data_flag:
ws['B' + str(current_row_number)].font = title_font
ws['B' + str(current_row_number)] = name + ' 详细数据'
current_row_number += 1
chart_start_number = current_row_number
current_row_number = current_row_number + 5
table_start_number = current_row_number
ws['B' + str(current_row_number)].fill = table_fill
ws['B' + str(current_row_number)].font = title_font
ws['B' + str(current_row_number)].border = f_border
ws['B' + str(current_row_number)].alignment = c_c_alignment
ws['B' + str(current_row_number)] = '日期时间'
ws['C' + str(current_row_number)].fill = table_fill
ws['C' + str(current_row_number)].font = title_font
ws['C' + str(current_row_number)].border = f_border
ws['C' + str(current_row_number)].alignment = c_c_alignment
ws['C' + str(current_row_number)] = report['meter'][
'energy_category_name'] + " (" + report['meter'][
'unit_of_measure'] + ")"
current_row_number += 1
for i in range(0, len(time)):
ws['B' + str(current_row_number)].font = title_font
ws['B' + str(current_row_number)].border = f_border
ws['B' + str(current_row_number)].alignment = c_c_alignment
ws['B' + str(current_row_number)] = time[i]
ws['C' + str(current_row_number)].font = title_font
ws['C' + str(current_row_number)].border = f_border
ws['C' + str(current_row_number)].alignment = c_c_alignment
ws['C' + str(current_row_number)] = round(
reporting_period_data['difference_values'][i], 2)
current_row_number += 1
table_end_number = current_row_number - 1
bar = BarChart()
labels = Reference(ws,
min_col=2,
min_row=table_start_number + 1,
max_row=table_end_number)
bar_data = Reference(ws,
min_col=3,
min_row=table_start_number,
max_row=table_end_number)
bar.add_data(bar_data, titles_from_data=True)
bar.set_categories(labels)
bar.height = 5.25
bar.width = len(time)
bar.dLbls = DataLabelList()
bar.dLbls.showVal = True # 数量显示
ws.add_chart(bar, "B" + str(chart_start_number))
else:
pass
filename = str(uuid.uuid4()) + '.xlsx'
wb.save(filename)
return filename
ws = wb.active
ws.title = "Main Sheet"
headers = ["Sl No", "City", "Temparature", "Update"]
ws.column_dimensions['B'].width = 20
ws.column_dimensions['C'].width = 17
#create a style
heading_style = NamedStyle(name="heading_style")
heading_style.font = Font(color=colors.BLACK, size=14, bold=True)
heading_style.alignment = Alignment(horizontal="center", vertical="center")
heading_style.fill = PatternFill("solid", fgColor="FFA500")
bd = Side("thin", color="000000")
heading_style.border = Border(top=bd, right=bd, bottom=bd, left=bd)
header_index = 0
for row in ws.iter_rows(min_row=1, max_row=1, min_col=1, max_col=4):
for cell in row:
cell.value = headers[header_index]
cell.style = heading_style
header_index += 1
#write serial numbers
_city_no = 1
for column in ws.iter_cols(min_col=1,
max_col=1,
min_row=2,
max_row=len(cities) + 1):
for cell in column:
cell.border = cell.border + top
for cell in rows[-1]:
cell.border = cell.border + bottom
for row in rows:
l = row[0]
r = row[-1]
l.border = l.border + left
r.border = r.border + right
if fill:
for c in row:
c.fill = fill
wb = Workbook()
ws = wb.active
my_cell = ws['B2']
my_cell.value = "My Cell"
my_cell = ws['B6']
my_cell.value = "My Cell"
thin = Side(border_style="thin", color="000000")
double = Side(border_style="double", color="ff0000")
border = Border(top=double, left=thin, right=thin, bottom=double)
fill = PatternFill("solid", fgColor="DDDDDD")
fill = GradientFill(stop=("000000", "FFFFFF"))
font = Font(b=True, color="FF0000")
al = Alignment(horizontal="center", vertical="center")
style_range(ws, 'B2:F4', border=border, fill=fill, font=font, alignment=al)
style_range(ws, 'B6:B6', border=border, alignment=al)
wb.save("styled.xlsx")
conn_eur.close()
conn_sib.close()
# Объединяем массивы данных по сверхбалансу и приводим дату в номальный вид
df_SverhBal = df_SverhBal_eur.append(df_SverhBal_sib)
df_SverhBal['ДАТА'] = df_SverhBal['ДАТА'].astype('str')
df_Fact_DV['ДАТА'] = df_Fact_DV['ДАТА'].astype('str')
# Экспортируем выгруженные данные в xlsx
df_SverhBal.to_excel(path_sverhbal, index=False)
df_Fact_DV.to_excel(path_fact_DV, index=False)
# Создаем шаблоны стилей
border = Border(left=Side(border_style='thin', color='FF000000'),
right=Side(border_style='thin', color='FF000000'),
top=Side(border_style='thin', color='FF000000'),
bottom=Side(border_style='thin', color='FF000000'))
align_head = Alignment(horizontal='center',
vertical='center',
text_rotation=0,
wrap_text=True,
shrink_to_fit=True,
indent=0)
align_cell = Alignment(horizontal='right',
vertical='center',
text_rotation=0,
wrap_text=False,
shrink_to_fit=False,
indent=0)
#fill_type 有如下的方式 一般纯色填充使用 solid， 其他样式自行尝试
#{'lightGrid', 'gray0625', 'lightTrellis', 'lightDown', 'lightVertical',
#'darkTrellis', 'darkHorizontal', 'darkVertical', 'darkGrid', 'darkGray',
#'solid', 'darkUp', 'lightGray', 'mediumGray', 'darkDown', 'lightHorizontal', 'lightUp', 'gray125'}
def PatternFill_modify(start, end, patternFill):
for tuples in sheet[start + ":" + end]:
for cells in tuples:
cells.fill = patternFill
#PatternFill_modify("A3","A3",patternObj)
borderObj = Border(left=Side(border_style='thin', color='00ff00'),
right=Side(border_style='dashDot', color='00ff00'),
top=Side(border_style='double', color='00ff00'),
bottom=Side(border_style='hair', color='00ff00'))
#border_style样式很多：‘dashDot’,‘dashDotDot’,‘dashed’,‘dotted’,‘double’,‘hair’,‘medium’,
#‘mediumDashDot’,‘mediumDashDotDot’,‘mediumDashed’,‘slantDashDot’,‘thick’,‘thin’
def border_modify(start, end, border):
for tuples in sheet[start + ":" + end]:
for cells in tuples:
cells.border = border
#border_modify("A3","B6",borderObj)
alignmentObj = Alignment(horizontal='left', vertical='top', wrap_text=True)
font = Font(size=12)
BUG_GRADE = ["阻塞缺陷", "一般缺陷", "显示缺陷", "建议缺陷"]
fill = PatternFill("solid", fgColor="00B050")
fill1 = PatternFill("solid", fgColor="FF0000")
fill2 = PatternFill("solid", fgColor="FFC000")
fill3 = PatternFill("solid", fgColor="FFFF00")
fill4 = PatternFill("solid", fgColor="A6A6A6")
column_width = 15
alignment = Alignment(horizontal='center', vertical='center')
now_date = datetime.datetime.now().date()
border = Border(left=Side(style='medium', color='A6A6A6'),
right=Side(style='medium', color='A6A6A6'),
top=Side(style='medium', color='A6A6A6'),
bottom=Side(style='medium', color='A6A6A6'),
diagonal=Side(style='medium', color='A6A6A6'),
diagonal_direction=0,
outline=Side(style='medium', color='A6A6A6'),
vertical=Side(style='medium', color='A6A6A6'),
horizontal=Side(style='medium', color='A6A6A6'))
world_farm = {2: '运营客服', 10: '世界农场', 14: '苗叔运营后台', 15: '苗叔用户端'}
def create_xlsx(file_name):
wb = openpyxl.Workbook()
ws = wb.active
ws.title = 'test_report'
wb.save(filename=file_name)
print("新建Excel：" + file_name + "成功")
class Content(object):
"""通过project实例创建目录"""
# 设置公用样式
title_font = Font(name='宋体', size=24, bold=True)
header_font = Font(name='仿宋_GB2312', size=14, bold=True)
normal_font = Font(name='仿宋_GB2312', size=14)
header_border = Border(bottom=Side(style='medium'))
normal_border = Border(bottom=Side(style='thin', color='80969696'))
ctr_alignment = Alignment(horizontal='center',
vertical='center',
wrap_text=True)
left_alignment = Alignment(horizontal='left',
vertical='center',
wrap_text=True,
indent=1)
margin = PageMargins()
def __init__(self, project):
self.project = project
self.wb = Workbook()
self.ws_lob = None
self.ws_tech = None
self.ws_qual = None
self.ws_eco = None
self.ws_com = None
def create_all(self):
"""生成目录总方法"""
self.create_qual()
self.create_com()
self.create_eco()
self.create_tech()
self.create_lob()
self.wb.save('目录—{}.xlsx'.format(self.project.name))
def create_lob(self):
"""创建投标函目录"""
self.ws_lob = self.wb.create_sheet('投标函', 0)
col_titles = ['序号', '内容', '页码']
content = [['一', '投标函'], ['二', '法定代表人身份证明书'], ['三', '法定代表人授权书'],
['四', '守法廉政承诺书'], ['五', '企业内控承诺'], ['六', '投标保证金银行保函']]
col_width = [10, 60, 10]
col_num = 3
row_num = 8
# 初始化表格
for i in range(row_num):
for j in range(col_num):
cell_now = self.ws_lob.cell(row=i + 1, column=j + 1)
self.ws_lob.row_dimensions[i + 1].height = 45 # 修改行高
if i > 0:
if i == 1:
cell_now.font = Content.header_font
cell_now.alignment = Content.ctr_alignment
cell_now.border = Content.header_border
cell_now.value = col_titles[j]
else:
cell_now.font = Content.normal_font
if j == 1:
cell_now.alignment = Content.left_alignment
cell_now.value = content[i - 2][1]
else:
cell_now.alignment = Content.ctr_alignment
if j == 0:
cell_now.value = content[i - 2][0]
elif j == 2:
cell_now.value = i - 1
if i != row_num - 1:
cell_now.border = Content.normal_border
letters = string.ascii_uppercase
for i in range(col_num): # 修改列宽
self.ws_lob.column_dimensions[letters[i]].width = col_width[i]
# 填写抬头
self.ws_lob.merge_cells('A1:C1')
header = self.ws_lob['A1']
header.font = Content.title_font
header.alignment = Content.ctr_alignment
header.value = '目 录'
self.ws_lob.row_dimensions[1].height = 50
# 打印设置
self.ws_lob.print_options.horizontalCentered = True
self.ws_lob.print_area = 'A1:C9'
self.ws_lob.page_setup.fitToWidth = 1
self.ws_lob.page_margins = Content.margin
def create_tech(self):
"""创建技术标目录"""
self.ws_tech = self.wb.create_sheet('技术标', 0)
col_titles = ['序号', '内容', '页码']
# 存放固定内容
content = [
'技术偏离表', '物资选型部分', '供货清单（一）中各项物资选型一览表', '供货清单（一）中各项物资相关资料', '包装方案',
'运输相关文件', '物资自检验收方案', '物资第三方检验相关文件', '对外实施工作主体落实承诺书',
'物资生产企业三体系认证相关资料', '物资节能产品认证相关资料', '物资环境标志产品认证相关资料'
]
# 存放中文序号
num = [
'一', '二', '三', '四', '五', '六', '七', '八', '九', '十', '十一', '十二', '十三'
]
col_width = [10, 60, 10]
col_num = 3
# 确定行数
com_num = len(self.project.commodities)
row_num = com_num + 14
if self.project.is_cc:
row_num += 1
content.insert(9, '来华培训方案及相关材料')
if self.project.is_qa:
row_num += 1
content.insert(9, '售后服务方案及相关材料')
if self.project.is_tech:
row_num += 1
content.insert(9, '技术服务方案及相关材料')
# 创建专用样式
third_alignment = Alignment(horizontal='left',
vertical='center',
wrap_text=True,
indent=3)
third_font = Font(name='仿宋_GB2312', size=12)
# 填写抬头
self.ws_tech.merge_cells('A1:C1')
header = self.ws_tech['A1']
header.font = Content.title_font
header.alignment = Content.ctr_alignment
header.value = '目 录'
self.ws_tech.row_dimensions[1].height = 50
# 初始化表格,双循环扫描先行后列扫描表格
for i in range(1, row_num):
for j in range(col_num):
cell_now = self.ws_tech.cell(row=i + 1, column=j + 1)
self.ws_tech.row_dimensions[i + 1].height = 30 # 修改行高
# 判断行数来确定应用的字体和样式
if i == 1: # 表头行样式填写
cell_now.font = Content.header_font
cell_now.alignment = Content.ctr_alignment
cell_now.border = Content.header_border
cell_now.value = col_titles[j]
elif 1 < i < 4: # 头两行
cell_now.font = Content.normal_font
cell_now.border = Content.normal_border
if j == 1:
cell_now.alignment = Content.left_alignment
cell_now.value = content[i - 2]
else:
cell_now.alignment = Content.ctr_alignment
if j == 0:
cell_now.value = num[i - 2]
elif i == 4 or i == 5: # 3、4行
cell_now.font = Content.normal_font
cell_now.border = Content.normal_border
if j == 1:
cell_now.alignment = Content.left_alignment
cell_now.value = content[i - 2]
else:
cell_now.alignment = Content.ctr_alignment
elif 5 < i < com_num + 6: # 填写物资名称
cell_now.font = third_font
cell_now.border = Content.normal_border
if j == 1:
cell_now.alignment = third_alignment
cell_now.value = '{}、{}'.format(
i - 5, self.project.commodities[i - 5][0])
else:
cell_now.alignment = Content.ctr_alignment
else: # 其余的一起填写
cell_now.font = Content.normal_font
if j == 1:
cell_now.alignment = Content.left_alignment
cell_now.value = content[i - com_num - 2]
else:
cell_now.alignment = Content.ctr_alignment
if j == 0:
cell_now.value = num[i - com_num - 4]
if i != row_num - 1:
cell_now.border = Content.normal_border
# for i in (9, 11): # 修改两处格式
# self.ws_tech.cell(row=com_num + i, column=2).font = third_font
# self.ws_tech.cell(
# row=com_num + i,
# column=2).alignment = third_alignment
letters = string.ascii_uppercase
for i in range(col_num): # 修改列宽
self.ws_tech.column_dimensions[letters[i]].width = col_width[i]
# 打印设置
self.ws_tech.print_options.horizontalCentered = True
self.ws_tech.print_area = 'A1:C{}'.format(row_num)
self.ws_tech.page_setup.fitToWidth = 1
self.ws_tech.page_margins = PageMargins(top=0.5,
bottom=0.5,
header=0.1,
footer=0.1)
def create_eco(self):
self.ws_eco = self.wb.create_sheet('经济标', 0)
col_titles = ['序号', '内容', '页码']
content = ['投标报价总表', '物资对内分项报价表', '《供货清单（一）》中各项物资增值税退抵税额表']
col_width = [10, 60, 10]
num = ['一', '二', '三', '四', '五', '六', '七', '八', '九', '十']
col_num = 3
# 确定行数
row_num = 5
if len(self.project.qc) == 0:
row_num += 1
content.insert(3, '非法检物资检验一览表')
else:
if len(self.project.qc) == len(self.project.commodities):
row_num += 1
content.insert(3, '法检物资检验一览表')
else:
row_num += 2
content.insert(3, '非法检物资检验一览表')
content.insert(3, '法检物资检验一览表')
if self.project.is_cc:
row_num += 1
content.insert(3, '来华培训费报价表')
if self.project.is_tech:
row_num += 1
content.insert(3, '技术服务费报价表')
# 初始化表格
for i in range(1, row_num):
for j in range(col_num):
cell_now = self.ws_eco.cell(row=i + 1, column=j + 1)
self.ws_eco.row_dimensions[i + 1].height = 45 # 修改行高
# 判断行数来确定应用的字体和样式
if i == 1: # 表头行样式填写
cell_now.font = Content.header_font
cell_now.alignment = Content.ctr_alignment
cell_now.border = Content.header_border
cell_now.value = col_titles[j]
else: # 其余的一起填写
cell_now.font = Content.normal_font
if j == 1:
cell_now.alignment = Content.left_alignment
cell_now.value = content[i - 2]
else:
cell_now.alignment = Content.ctr_alignment
if i != row_num - 1:
cell_now.border = Content.normal_border
letters = string.ascii_uppercase
for i in range(col_num): # 修改列宽
self.ws_eco.column_dimensions[letters[i]].width = col_width[i]
# 填写序号
# self.ws_eco['A3'] = '经济标部分'
# self.ws_eco['A3'].font = Content.header_font
# if not self.project.is_lowprice:
# self.ws_eco_com['A{}'.format(row_num - 4)] = '商务标部分'
# self.ws_eco_com['A{}'.format(
# row_num - 4)].font = Content.header_font
# 填写序号
for i in range(3, row_num + 1):
self.ws_eco['A{}'.format(i)] = num[i - 3]
# 合并小标题
# self.ws_eco.merge_cells('A3:C3')
# if not self.project.is_lowprice:
# self.ws_eco.merge_cells('A{0}:C{0}'.format(row_num - 4))
# 填写抬头
self.ws_eco.merge_cells('A1:C1')
header = self.ws_eco['A1']
header.font = Content.title_font
header.alignment = Content.ctr_alignment
header.value = '目 录'
self.ws_eco.row_dimensions[1].height = 50
# 打印设置
self.ws_eco.print_options.horizontalCentered = True
self.ws_eco.print_area = 'A1:C{}'.format(row_num)
self.ws_eco.page_setup.fitToWidth = 1
# self.ws_eco.page_margins = PageMargins(
# top=0.5, bottom=0.5, header=0.1, footer=0.1)
def create_com(self):
self.ws_com = self.wb.create_sheet('商务标', 0)
col_titles = ['序号', '内容', '页码']
content = [['一', '同类物资出口业绩一览表及报关单'], ['二', '向受援国出口货物业绩一览表及报关单']]
col_width = [10, 60, 10]
col_num = 3
row_num = 4
# # 创建专用样式
# special_alignment = Alignment(
# horizontal='left',
# vertical='center',
# wrap_text=True,
# indent=0)
# special_font = Font(name='仿宋_GB2312', size=12)
# 初始化表格
for i in range(1, row_num):
for j in range(col_num):
cell_now = self.ws_com.cell(row=i + 1, column=j + 1)
self.ws_com.row_dimensions[i + 1].height = 45 # 修改行高
# 判断行数来确定应用的字体和样式
if i == 1: # 表头行样式填写
cell_now.font = Content.header_font
cell_now.alignment = Content.ctr_alignment
cell_now.border = Content.header_border
cell_now.value = col_titles[j]
else: # 其余
cell_now.font = Content.normal_font
if i != row_num - 1:
cell_now.border = Content.normal_border
if j == 1:
cell_now.alignment = Content.left_alignment
cell_now.value = content[i - 2][1]
else:
cell_now.alignment = Content.ctr_alignment
if j == 0:
cell_now.value = content[i - 2][0]
letters = string.ascii_uppercase
for i in range(col_num): # 修改列宽
self.ws_com.column_dimensions[letters[i]].width = col_width[i]
# 填写抬头
self.ws_com.merge_cells('A1:C1')
header = self.ws_com['A1']
header.font = Content.title_font
header.alignment = Content.ctr_alignment
header.value = '目 录'
self.ws_com.row_dimensions[1].height = 50
# 打印设置
self.ws_com.print_options.horizontalCentered = True
self.ws_com.print_area = 'A1:C{}'.format(row_num)
self.ws_com.page_setup.fitToWidth = 1
self.ws_com.page_margins = PageMargins(top=0.5,
bottom=0.5,
header=0.1,
footer=0.1)
def create_qual(self):
self.ws_qual = self.wb.create_sheet('资格后审', 0)
col_titles = ['序号', '内容', '页码']
content = [['一', '资格后审申请函'], ['二', '证明文件']]
content2 = [
'投标人的法人营业执照（复印件）和援外物资项目实施企业资格证明文件（复印件）', '法定代表人证明书和授权书（复印件）',
'无重大违法记录的声明函', '财务审计报告（复印件）', '依法缴纳社会保障资金的证明和税收的证明（复印件）',
'特殊物资经营资格、资质许可证明文件（复印件）', '关联企业声明', '其它'
]
col_width = [10, 60, 10]
col_num = 3
row_num = 12
# 创建专用样式
special_alignment = Alignment(horizontal='left',
vertical='center',
wrap_text=True,
indent=0)
special_font = Font(name='仿宋_GB2312', size=12)
# 初始化表格
for i in range(1, row_num):
for j in range(col_num):
cell_now = self.ws_qual.cell(row=i + 1, column=j + 1)
self.ws_qual.row_dimensions[i + 1].height = 45 # 修改行高
# 判断行数来确定应用的字体和样式
if i == 1: # 表头行样式填写
cell_now.font = Content.header_font
cell_now.alignment = Content.ctr_alignment
cell_now.border = Content.header_border
cell_now.value = col_titles[j]
elif 1 < i < 4: # 头两行
cell_now.font = Content.normal_font
cell_now.border = Content.normal_border
if j == 1:
cell_now.alignment = Content.left_alignment
cell_now.value = content[i - 2][1]
else:
cell_now.alignment = Content.ctr_alignment
if j == 0:
cell_now.value = content[i - 2][0]
else: # 其余的一起填写
cell_now.font = special_font
if j == 1:
cell_now.alignment = special_alignment
cell_now.value = '{}、{}'.format(i - 3, content2[i - 4])
else:
cell_now.alignment = Content.ctr_alignment
if i != row_num - 1:
cell_now.border = Content.normal_border
letters = string.ascii_uppercase
for i in range(col_num): # 修改列宽
self.ws_qual.column_dimensions[letters[i]].width = col_width[i]
# 填写抬头
self.ws_qual.merge_cells('A1:C1')
header = self.ws_qual['A1']
header.font = Content.title_font
header.alignment = Content.ctr_alignment
header.value = '目 录'
self.ws_qual.row_dimensions[1].height = 50
# 打印设置
self.ws_qual.print_options.horizontalCentered = True
self.ws_qual.print_area = 'A1:C{}'.format(row_num)
self.ws_qual.page_setup.fitToWidth = 1
self.ws_qual.page_margins = PageMargins(top=0.5,
bottom=0.5,
header=0.1,
footer=0.1)
def apply_borders_and_colour(worksheet, heading_colour, fill_colour):
""" Apply Borders and Colour
Applies borders and coluring to the parsed openPyXl worksheet.
:param worksheet: openPyXl worksheet.
:param heading_colour: String describing the hex code of the colour used to fill spreadsheet headings.
:param fill_colour: String describing the hex code of the colour used to fill spreadsheet body.
:return:
"""
height = worksheet.max_row
width = worksheet.max_column
for style_col in range(width):
column = get_column_value(style_col)
style_cell_name = "{}{}".format(column, 1)
worksheet[style_cell_name].font = Font(size=11,
bold=True,
color='FF424242')
for style_col in range(width):
column = get_column_value(style_col)
for style_row in range(height):
style_cell_name = "{}{}".format(column, style_row + 1)
style_cell = worksheet[style_cell_name]
if style_row != 0:
if style_row % 2 == 0:
style_cell.fill = PatternFill("solid", fgColor=fill_colour)
else:
style_cell.fill = PatternFill("solid", fgColor=heading_colour)
if style_row == 0 and style_col == 0:
style_cell.border = Border(left=Side(border_style="thick",
color='FF000000'),
right=Side(border_style="thin",
color='FF000000'),
top=Side(border_style="thick",
color='FF000000'),
bottom=Side(border_style="thick",
color='FF000000'))
elif style_row == 0 and style_col == width - 1:
style_cell.border = Border(left=Side(border_style="thin",
color='FF000000'),
right=Side(border_style="thick",
color='FF000000'),
top=Side(border_style="thick",
color='FF000000'),
bottom=Side(border_style="thick",
color='FF000000'))
elif style_row == 0:
style_cell.border = Border(left=Side(border_style="thin",
color='FF000000'),
right=Side(border_style="thin",
color='FF000000'),
top=Side(border_style="thick",
color='FF000000'),
bottom=Side(border_style="thick",
color='FF000000'))
elif style_row == height - 1 and style_col == 0:
style_cell.border = Border(left=Side(border_style="thick",
color='FF000000'),
right=Side(border_style="thin",
color='FF000000'),
bottom=Side(border_style="thick",
color='FF000000'))
elif style_row == height - 1 and style_col == width - 1:
style_cell.border = Border(left=Side(border_style="thin",
color='FF000000'),
right=Side(border_style="thick",
color='FF000000'),
bottom=Side(border_style="thick",
color='FF000000'))
elif style_row == height - 1:
style_cell.border = Border(left=Side(border_style="thin",
color='FF000000'),
right=Side(border_style="thin",
color='FF000000'),
bottom=Side(border_style="thick",
color='FF000000'))
elif style_col == width - 1:
style_cell.border = Border(left=Side(border_style="thin",
color='FF000000'),
right=Side(border_style="thick",
color='FF000000'),
bottom=Side(border_style="dashed",
color='FF000000'))
elif style_col == 0:
style_cell.border = Border(left=Side(border_style="thick",
color='FF000000'),
right=Side(border_style="thin",
color='FF000000'),
bottom=Side(border_style="dashed",
color='FF000000'))
else:
style_cell.border = Border(left=Side(border_style="thin",
color='FF000000'),
right=Side(border_style="thin",
color='FF000000'),
bottom=Side(border_style="dashed",
color='FF000000'))
logger.info("Borders and colour successfully applied to Excel file.")
s['U4'].value = '距离退休月数'
s['V4'].value = '公司内部工龄（月数）'
s['W4'].value = '退休日期'
for i in range(5, 31):#根据人数设置
age_function(i)
#设置单元格格式
font1 = Font(name='黑体',size=24)
font2 = Font(size=12)
border2 = Border(left=Side('thin'),
right=Side('thin'),
top=Side('thin'),
bottom=Side('thin'))
alignment = Alignment(horizontal='center', vertical='center')
for cells in s['R4:w30']:#设置边框
for cell in cells:
cell.border = border2
cell.alignment = alignment
#按当前日期保存
wb.save('d:\\age\\age'+str(arrow.now().format('YYYY-MM-DD'))+ '.xlsx')
def gerar_pla(self, data, dir):
wb = Workbook()
ws1 = wb.active # work sheet
ws1.title = "Controle de Gasto"
listDesc = []
listValor = []
listData = []
listPag = []
listValorPag = []
listDev = []
listStatus = []
ws1["A1"] = 'Descrição'
ws1["B1"] = 'Valor'
ws1["C1"] = 'Data de Vencimento'
ws1["D1"] = 'Data de Pagamento'
ws1["E1"] = 'Valor que foi pago'
ws1["F1"] = 'Devendo'
ws1["G1"] = 'Status'
conection = ConexaoBD.cur
Double_border = Border(left=Side(border_style='dashed',
color='FF000000'),
right=Side(border_style='dashed',
color='FF000000'),
top=Side(border_style='double',
color='FF000000'),
bottom=Side(border_style='double',
color='FF000000'))
#Laços de interação para pegar as informações do banco e transformar em uma planilha
for i in conection.execute(
f"select desc from finance WHERE dataVenc LIKE '%{data}'"
).fetchall():
listDesc.append(i[0])
ws1.cell(column=1, row=len(listDesc) + 1,
value=i[0]).border = Double_border
for i in conection.execute(
f"SELECT valor FROM finance WHERE dataVenc LIKE '%{data}' "
).fetchall():
listValor.append(i[0])
ws1.cell(column=2, row=len(listValor) + 1,
value=i[0]).border = Double_border
for i in conection.execute(
f"SELECT dataVenc FROM finance WHERE dataVenc LIKE '%{data}' "
).fetchall():
listData.append(i[0])
ws1.cell(column=3, row=len(listData) + 1,
value=i[0]).border = Double_border
for i in conection.execute(
f"SELECT dataPag FROM finance WHERE dataVenc LIKE '%{data}' "
).fetchall():
listPag.append(i[0])
ws1.cell(column=4, row=len(listPag) + 1,
value=i[0]).border = Double_border
for i in conection.execute(
f"SELECT valorPag FROM finance WHERE dataVenc LIKE '%{data}' "
).fetchall():
listValorPag.append(i[0])
ws1.cell(column=5, row=len(listValorPag) + 1,
value=i[0]).border = Double_border
for i in conection.execute(
f"select devendo from finance WHERE dataVenc LIKE '%{data}' "
).fetchall():
listDev.append(i[0])
ws1.cell(column=6, row=len(listDev) + 1,
value=i[0]).border = Double_border
for i in conection.execute(
f"select status from finance WHERE dataVenc LIKE '%{data}' "
).fetchall():
listStatus.append(i[0])
ws1.cell(column=7, row=len(listStatus) + 1,
value=i[0]).border = Double_border
for i in range(2, 51):
ws1[f"F{i}"] = f'=SUM(B{i}-E{i})'
#Céluta total da Dívida
ws1["H12"] = 'TOTAL DE GASTO DESTE MÊS: '
ws1["I12"] = '=SUM(B2:B50)'
#Célula para mostrar o quanto falta pagar
ws1["H13"] = 'VALOR QUE RESTA À PAGAR: '
ws1["I13"] = '=SUM(F2:F50)'
ft_a = Font(name='Times New Roman',
color=colors.BLACK,
bold=True,
size=12)
a1 = ws1['A1']
b1 = ws1['B1']
c1 = ws1['C1']
d1 = ws1['D1']
e1 = ws1['E1']
f1 = ws1['F1']
g1 = ws1['G1']
h12 = ws1['H12']
h13 = ws1['H13']
h14 = ws1['H14']
a1.font = ft_a
b1.font = ft_a
c1.font = ft_a
d1.font = ft_a
e1.font = ft_a
f1.font = ft_a
g1.font = ft_a
h12.font = ft_a
h13.font = ft_a
h14.font = ft_a
wb.save(f'{dir}')
def get(self, request, *args, **kwargs):
today = datetime.datetime.now()
today = today.strftime('%Y-%m-%d')
query = TiempoMuertonDet.objects.all()
wb = Workbook()
ws = wb.active
ws.tittle = 'Tiempos Muertos'
#Establer el nombre del archivo
nombre_archivo = str(today) + "Reporte Tiempos Muertosw.xlsx"
ws['B1'].alignment = Alignment(horizontal='left', vertical='center')
ws['B1'].border = Border(left=Side(border_style='thin'),
right=Side(border_style='thin'),
top=Side(border_style='thin'),
bottom=Side(border_style='thin'))
ws['B1'].fill = PatternFill(start_color='66FFCC',
end_color='66FFCC',
fill_type='solid')
ws['B1'].font = Font(name='calibri', size=12, bold=True)
ws['B1'] = 'Mar Bran S.A. de C.V.'
ws.merge_cells('B1:F1')
ws['B2'].alignment = Alignment(horizontal='left', vertical='center')
ws['B2'].border = Border(left=Side(border_style='thin'),
right=Side(border_style='thin'),
top=Side(border_style='thin'),
bottom=Side(border_style='thin'))
ws['B2'].fill = PatternFill(start_color='66FFCC',
end_color='66FFCC',
fill_type='solid')
ws['B2'].font = Font(name='calibri', size=12, bold=True)
ws['B2'] = 'Innovación, Mejora Continua y Six Sigma'
ws.merge_cells('B2:F2')
ws['B3'].alignment = Alignment(horizontal='left', vertical='center')
ws['B3'].border = Border(left=Side(border_style='thin'),
right=Side(border_style='thin'),
top=Side(border_style='thin'),
bottom=Side(border_style='thin'))
ws['B3'].fill = PatternFill(start_color='66FFCC',
end_color='66FFCC',
fill_type='solid')
ws['B3'].font = Font(name='calibri', size=12, bold=True)
ws['B3'] = 'Reporte de Tiempos Muertos'
ws['G3'].alignment = Alignment(horizontal='left', vertical='center')
ws['G3'].border = Border(left=Side(border_style='thin'),
right=Side(border_style='thin'),
top=Side(border_style='thin'),
bottom=Side(border_style='thin'))
ws['G3'].fill = PatternFill(start_color='66FFCC',
end_color='66FFCC',
fill_type='solid')
ws['G3'].font = Font(name='calibri', size=12, bold=True)
ws['G3'] = 'FECHA'
ws['H3'].alignment = Alignment(horizontal='left', vertical='center')
ws['H3'].border = Border(left=Side(border_style='thin'),
right=Side(border_style='thin'),
top=Side(border_style='thin'),
bottom=Side(border_style='thin'))
ws['H3'].fill = PatternFill(start_color='66FFCC',
end_color='66FFCC',
fill_type='solid')
ws['H3'].font = Font(name='calibri', size=12, bold=True)
ws['H3'] = today
ws.merge_cells('B3:F3')
ws.row_dimensions[1].height = 20
ws.row_dimensions[2].height = 20
ws.row_dimensions[3].height = 20
ws.column_dimensions['B'].width = 20
ws.column_dimensions['C'].width = 20
ws.column_dimensions['D'].width = 20
ws.column_dimensions['E'].width = 30
ws.column_dimensions['F'].width = 20
ws.column_dimensions['G'].width = 60
ws.column_dimensions['H'].width = 60
ws.column_dimensions['G'].width = 20
ws.column_dimensions['J'].width = 60
ws['B6'].alignment = Alignment(horizontal='center', vertical='center')
ws['B6'].border = Border(left=Side(border_style='thin'),
right=Side(border_style='thin'),
top=Side(border_style='thin'),
bottom=Side(border_style='thin'))
ws['B6'].fill = PatternFill(start_color='66CFCC',
end_color='66CFCC',
fill_type='solid')
ws['B6'].font = Font(name='calibri', size=11, bold=True)
ws['B6'] = 'Fecha'
ws['C6'].alignment = Alignment(horizontal='center', vertical='center')
ws['C6'].border = Border(left=Side(border_style='thin'),
right=Side(border_style='thin'),
top=Side(border_style='thin'),
bottom=Side(border_style='thin'))
ws['C6'].fill = PatternFill(start_color='66CFCC',
end_color='66CFCC',
fill_type='solid')
ws['C6'].font = Font(name='calibri', size=11, bold=True)
ws['C6'] = 'Planta'
ws['D6'].alignment = Alignment(horizontal='center', vertical='center')
ws['D6'].border = Border(left=Side(border_style='thin'),
right=Side(border_style='thin'),
top=Side(border_style='thin'),
bottom=Side(border_style='thin'))
ws['D6'].fill = PatternFill(start_color='66CFCC',
end_color='66CFCC',
fill_type='solid')
ws['D6'].font = Font(name='calibri', size=11, bold=True)
ws['D6'] = 'Línea'
ws['E6'].alignment = Alignment(horizontal='center', vertical='center')
ws['E6'].border = Border(left=Side(border_style='thin'),
right=Side(border_style='thin'),
top=Side(border_style='thin'),
bottom=Side(border_style='thin'))
ws['E6'].fill = PatternFill(start_color='66CFCC',
end_color='66CFCC',
fill_type='solid')
ws['E6'].font = Font(name='calibri', size=11, bold=True)
ws['E6'] = 'Supervisor'
ws['F6'].alignment = Alignment(horizontal='center', vertical='center')
ws['F6'].border = Border(left=Side(border_style='thin'),
right=Side(border_style='thin'),
top=Side(border_style='thin'),
bottom=Side(border_style='thin'))
ws['F6'].fill = PatternFill(start_color='66CFCC',
end_color='66CFCC',
fill_type='solid')
ws['F6'].font = Font(name='calibri', size=11, bold=True)
ws['F6'] = 'Turno'
ws['G6'].alignment = Alignment(horizontal='center', vertical='center')
ws['G6'].border = Border(left=Side(border_style='thin'),
right=Side(border_style='thin'),
top=Side(border_style='thin'),
bottom=Side(border_style='thin'))
ws['G6'].fill = PatternFill(start_color='66CFCC',
end_color='66CFCC',
fill_type='solid')
ws['G6'].font = Font(name='calibri', size=11, bold=True)
ws['G6'] = 'Categoría'
ws['H6'].alignment = Alignment(horizontal='center', vertical='center')
ws['H6'].border = Border(left=Side(border_style='thin'),
right=Side(border_style='thin'),
top=Side(border_style='thin'),
bottom=Side(border_style='thin'))
ws['H6'].fill = PatternFill(start_color='66CFCC',
end_color='66CFCC',
fill_type='solid')
ws['H6'].font = Font(name='calibri', size=11, bold=True)
ws['H6'] = 'Causa'
ws['I6'].alignment = Alignment(horizontal='center', vertical='center')
ws['I6'].border = Border(left=Side(border_style='thin'),
right=Side(border_style='thin'),
top=Side(border_style='thin'),
bottom=Side(border_style='thin'))
ws['I6'].fill = PatternFill(start_color='66CFCC',
end_color='66CFCC',
fill_type='solid')
ws['I6'].font = Font(name='calibri', size=11, bold=True)
ws['I6'] = 'Tiempo (min)'
ws['J6'].alignment = Alignment(horizontal='center', vertical='center')
ws['J6'].border = Border(left=Side(border_style='thin'),
right=Side(border_style='thin'),
top=Side(border_style='thin'),
bottom=Side(border_style='thin'))
ws['J6'].fill = PatternFill(start_color='66CFCC',
end_color='66CFCC',
fill_type='solid')
ws['J6'].font = Font(name='calibri', size=11, bold=True)
ws['J6'] = 'Obs'
controlador = 7
for q in query:
causa = q.causa
query3 = CausaTM.objects.filter(descripcion=causa).first()
categoria = query3.categoriaTM
ws.cell(row=controlador,
column=7).alignment = Alignment(horizontal='center',
vertical='center')
ws.cell(row=controlador,
column=7).border = Border(left=Side(border_style='thin'),
right=Side(border_style='thin'),
top=Side(border_style='thin'),
bottom=Side(border_style='thin'))
ws.cell(row=controlador, column=7).font = Font(name='calibri',
size=11,
bold=True)
ws.cell(row=controlador, column=7).value = str(categoria)
ws.cell(row=controlador,
column=8).alignment = Alignment(horizontal='center',
vertical='center')
ws.cell(row=controlador,
column=8).border = Border(left=Side(border_style='thin'),
right=Side(border_style='thin'),
top=Side(border_style='thin'),
bottom=Side(border_style='thin'))
ws.cell(row=controlador, column=8).font = Font(name='calibri',
size=11,
bold=True)
ws.cell(row=controlador, column=8).value = str(q.causa)
ws.cell(row=controlador,
column=9).alignment = Alignment(horizontal='center',
vertical='center')
ws.cell(row=controlador,
column=9).border = Border(left=Side(border_style='thin'),
right=Side(border_style='thin'),
top=Side(border_style='thin'),
bottom=Side(border_style='thin'))
ws.cell(row=controlador, column=9).font = Font(name='calibri',
size=11,
bold=True)
ws.cell(row=controlador, column=9).value = q.cantidad
ws.cell(row=controlador,
column=10).alignment = Alignment(horizontal='center',
vertical='center')
ws.cell(row=controlador, column=10).border = Border(
left=Side(border_style='thin'),
right=Side(border_style='thin'),
top=Side(border_style='thin'),
bottom=Side(border_style='thin'))
ws.cell(row=controlador, column=10).font = Font(name='calibri',
size=11,
bold=True)
ws.cell(row=controlador, column=10).value = q.obs
id_enc = q.tiempo_muerto_id
query2 = TiempoMuertoEnc.objects.filter(id=id_enc)
for x in query2:
ws.cell(row=controlador,
column=2).alignment = Alignment(horizontal='center',
vertical='center')
ws.cell(row=controlador, column=2).border = Border(
left=Side(border_style='thin'),
right=Side(border_style='thin'),
top=Side(border_style='thin'),
bottom=Side(border_style='thin'))
ws.cell(row=controlador, column=2).font = Font(name='calibri',
size=11,
bold=True)
ws.cell(row=controlador, column=2).value = x.fecha_produccion
ws.cell(row=controlador,
column=3).alignment = Alignment(horizontal='center',
vertical='center')
ws.cell(row=controlador, column=3).border = Border(
left=Side(border_style='thin'),
right=Side(border_style='thin'),
top=Side(border_style='thin'),
bottom=Side(border_style='thin'))
ws.cell(row=controlador, column=3).font = Font(name='calibri',
size=11,
bold=True)
ws.cell(row=controlador, column=3).value = str(x.planta)
ws.cell(row=controlador,
column=4).alignment = Alignment(horizontal='center',
vertical='center')
ws.cell(row=controlador, column=4).border = Border(
left=Side(border_style='thin'),
right=Side(border_style='thin'),
top=Side(border_style='thin'),
bottom=Side(border_style='thin'))
ws.cell(row=controlador, column=4).font = Font(name='calibri',
size=11,
bold=True)
ws.cell(row=controlador, column=4).value = str(x.linea)
ws.cell(row=controlador,
column=5).alignment = Alignment(horizontal='center',
vertical='center')
ws.cell(row=controlador, column=5).border = Border(
left=Side(border_style='thin'),
right=Side(border_style='thin'),
top=Side(border_style='thin'),
bottom=Side(border_style='thin'))
ws.cell(row=controlador, column=5).font = Font(name='calibri',
size=11,
bold=True)
ws.cell(row=controlador, column=5).value = str(x.supervisor)
ws.cell(row=controlador,
column=6).alignment = Alignment(horizontal='center',
vertical='center')
ws.cell(row=controlador, column=6).border = Border(
left=Side(border_style='thin'),
right=Side(border_style='thin'),
top=Side(border_style='thin'),
bottom=Side(border_style='thin'))
ws.cell(row=controlador, column=6).font = Font(name='calibri',
size=11,
bold=True)
ws.cell(row=controlador, column=6).value = str(x.turno)
controlador += 1
response = HttpResponse(content_type='application/ms-excel')
contenido = "attachment; filename = {0}".format(nombre_archivo)
response["Content-Disposition"] = contenido
wb.save(response)
return response
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
# for i in range(2, 37 + 1):
# ws.row_dimensions[i].height = 30
#
# for i in range(38, 90 + 1):
# ws.row_dimensions[i].height = 30
# 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=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)
c_r_alignment = Alignment(vertical='bottom',
horizontal='center',
text_rotation=0,
wrap_text=True,
shrink_to_fit=False,
indent=0)
# Img
img = Image("excelexporters/myems.png")
img.width = img.width * 0.85
img.height = img.height * 0.85
# img = Image("myems.png")
ws.add_image(img, 'B1')
# Title
ws.row_dimensions[3].height = 60
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'].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
#################################################
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
if has_energy_data_flag:
ws['B6'].font = title_font
ws['B6'] = name + ' 报告期成本'
category = reporting_period_data['names']
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'] = '成本'
ws['B8'].border = f_border
ws['B9'].font = title_font
ws['B9'].alignment = c_c_alignment
ws['B9'] = '环比'
ws['B9'].border = f_border
col = 'B'
for i in range(0, ca_len):
col = chr(ord('C') + i)
ws[col + '7'].fill = table_fill
ws[col + '7'].font = name_font
ws[col + '7'].alignment = c_c_alignment
ws[col + '7'] = reporting_period_data['names'][
i] + " (" + reporting_period_data['units'][i] + ")"
ws[col + '7'].border = f_border
ws[col + '8'].font = name_font
ws[col + '8'].alignment = c_c_alignment
ws[col + '8'] = round(reporting_period_data['subtotals'][i], 2)
ws[col + '8'].border = f_border
ws[col + '9'].font = name_font
ws[col + '9'].alignment = c_c_alignment
ws[col + '9'] = str(round(reporting_period_data['increment_rates'][i] * 100, 2)) + "%" \
if reporting_period_data['increment_rates'][i] is not None else "-"
ws[col + '9'].border = f_border
end_col = chr(ord(col) + 1)
ws[end_col + '7'].fill = table_fill
ws[end_col + '7'].font = name_font
ws[end_col + '7'].alignment = c_c_alignment
ws[end_col + '7'] = "总计 (" + reporting_period_data['total_unit'] + ")"
ws[end_col + '7'].border = f_border
ws[end_col + '8'].font = name_font
ws[end_col + '8'].alignment = c_c_alignment
ws[end_col + '8'] = round(reporting_period_data['total'], 2)
ws[end_col + '8'].border = f_border
ws[end_col + '9'].font = name_font
ws[end_col + '9'].alignment = c_c_alignment
ws[end_col + '9'] = str(round(reporting_period_data['total_increment_rate'] * 100, 2)) + "%" \
if reporting_period_data['total_increment_rate'] is not None else "-"
ws[end_col + '9'].border = f_border
else:
for i in range(6, 9 + 1):
ws.row_dimensions[i].height = 0.1
#################################################
has_ele_peak_flag = True
if "toppeaks" not in reporting_period_data.keys() or \
reporting_period_data['toppeaks'] is None or \
len(reporting_period_data['toppeaks']) == 0:
has_ele_peak_flag = False
if has_ele_peak_flag:
ws['B12'].font = title_font
ws['B12'] = name + '分时用电成本'
ws['B13'].fill = table_fill
ws['B13'].font = name_font
ws['B13'].alignment = c_c_alignment
ws['B13'].border = f_border
ws['C13'].fill = table_fill
ws['C13'].font = name_font
ws['C13'].alignment = c_c_alignment
ws['C13'].border = f_border
ws['C13'] = '分时用电成本'
ws['D13'].fill = table_fill
ws['D13'].font = name_font
ws['D13'].alignment = c_c_alignment
ws['D13'].border = f_border
ws['D13'] = '分时用电成本占比'
costsum = round(reporting_period_data['toppeaks'][0], 2) + round(reporting_period_data['onpeaks'][0], 2) + \
round(reporting_period_data['midpeaks'][0], 2) + round(reporting_period_data['offpeaks'][0], 2)
ws['B14'].font = title_font
ws['B14'].alignment = c_c_alignment
ws['B14'] = '尖'
ws['B14'].border = f_border
ws['C14'].font = title_font
ws['C14'].alignment = c_c_alignment
ws['C14'].border = f_border
ws['C14'] = round(reporting_period_data['toppeaks'][0], 2)
ws['D14'].font = title_font
ws['D14'].alignment = c_c_alignment
ws['D14'].border = f_border
ws['D14'] = '{:.2%}'.format(round(reporting_period_data['toppeaks'][0], 2) / costsum) \
if costsum is not None and costsum != Decimal(0.0) else " "
ws['B15'].font = title_font
ws['B15'].alignment = c_c_alignment
ws['B15'] = '峰'
ws['B15'].border = f_border
ws['C15'].font = title_font
ws['C15'].alignment = c_c_alignment
ws['C15'].border = f_border
ws['C15'] = round(reporting_period_data['onpeaks'][0], 2)
ws['D15'].font = title_font
ws['D15'].alignment = c_c_alignment
ws['D15'].border = f_border
ws['D15'] = '{:.2%}'.format(round(reporting_period_data['onpeaks'][0], 2) / costsum) \
if costsum is not None and costsum != Decimal(0.0) else " "
ws['B16'].font = title_font
ws['B16'].alignment = c_c_alignment
ws['B16'] = '平'
ws['B16'].border = f_border
ws['C16'].font = title_font
ws['C16'].alignment = c_c_alignment
ws['C16'].border = f_border
ws['C16'] = round(reporting_period_data['midpeaks'][0], 2)
ws['D16'].font = title_font
ws['D16'].alignment = c_c_alignment
ws['D16'].border = f_border
ws['D16'] = '{:.2%}'.format(round(reporting_period_data['midpeaks'][0], 2) / costsum) \
if costsum is not None and costsum != Decimal(0.0) else " "
ws['B17'].font = title_font
ws['B17'].alignment = c_c_alignment
ws['B17'] = '谷'
ws['B17'].border = f_border
ws['C17'].font = title_font
ws['C17'].alignment = c_c_alignment
ws['C17'].border = f_border
ws['C17'] = round(reporting_period_data['offpeaks'][0], 2)
ws['D17'].font = title_font
ws['D17'].alignment = c_c_alignment
ws['D17'].border = f_border
ws['D17'] = '{:.2%}'.format(round(reporting_period_data['offpeaks'][0], 2) / costsum) \
if costsum is not None and costsum != Decimal(0.0) else " "
pie = PieChart()
pie.title = name + '分时用电成本'
labels = Reference(ws, min_col=2, min_row=14, max_row=17)
pie_data = Reference(ws, min_col=3, min_row=13, max_row=17)
pie.add_data(pie_data, titles_from_data=True)
pie.set_categories(labels)
pie.height = 6.6
pie.width = 9
s1 = pie.series[0]
s1.dLbls = DataLabelList()
s1.dLbls.showCatName = False
s1.dLbls.showVal = True
s1.dLbls.showPercent = True
ws.add_chart(pie, "E13")
else:
for i in range(12, 18 + 1):
ws.row_dimensions[i].height = 0.1
################################################
current_row_number = 19
has_subtotals_data_flag = True
if "subtotals" not in reporting_period_data.keys() or \
reporting_period_data['subtotals'] is None or \
len(reporting_period_data['subtotals']) == 0:
has_subtotals_data_flag = False
if has_subtotals_data_flag:
ws['B' + str(current_row_number)].font = title_font
ws['B' + str(current_row_number)] = name + ' 成本占比'
current_row_number += 1
table_start_row_number = current_row_number
ws['B' + str(current_row_number)].fill = table_fill
ws['B' + str(current_row_number)].font = name_font
ws['B' + str(current_row_number)].alignment = c_c_alignment
ws['B' + str(current_row_number)].border = f_border
ws['C' + str(current_row_number)].fill = table_fill
ws['C' + str(current_row_number)].font = name_font
ws['C' + str(current_row_number)].alignment = c_c_alignment
ws['C' + str(current_row_number)].border = f_border
ws['C' + str(current_row_number)] = '成本'
ws['D' + str(current_row_number)].fill = table_fill
ws['D' + str(current_row_number)].font = name_font
ws['D' + str(current_row_number)].alignment = c_c_alignment
ws['D' + str(current_row_number)].border = f_border
ws['D' + str(current_row_number)] = '成本占比'
current_row_number += 1
ca_len = len(reporting_period_data['names'])
costsum = Decimal(0.0)
for i in range(0, ca_len):
costsum = round(reporting_period_data['subtotals'][i], 2) + costsum
for i in range(0, ca_len):
ws['B' + str(current_row_number)].font = title_font
ws['B' + str(current_row_number)].alignment = c_c_alignment
ws['B' +
str(current_row_number)] = reporting_period_data['names'][i]
ws['B' + str(current_row_number)].border = f_border
ws['C' + str(current_row_number)].font = title_font
ws['C' + str(current_row_number)].alignment = c_c_alignment
ws['C' + str(current_row_number)].border = f_border
ws['C' + str(current_row_number)] = round(
reporting_period_data['subtotals'][i], 2)
ws['D' + str(current_row_number)].font = title_font
ws['D' + str(current_row_number)].alignment = c_c_alignment
ws['D' + str(current_row_number)].border = f_border
ws['D' + str(current_row_number)] = '{:.2%}'.format(round(
reporting_period_data['subtotals'][i], 2) / costsum) if costsum is not None and costsum != Decimal(0.0)\
else " "
current_row_number += 1
table_end_row_number = current_row_number - 1
pie = PieChart()
pie.title = name + ' 成本占比'
labels = Reference(ws,
min_col=2,
min_row=table_start_row_number + 1,
max_row=table_end_row_number)
pie_data = Reference(ws,
min_col=3,
min_row=table_start_row_number,
max_row=table_end_row_number)
pie.add_data(pie_data, titles_from_data=True)
pie.set_categories(labels)
pie.height = 6.6
pie.width = 9
s1 = pie.series[0]
s1.dLbls = DataLabelList()
s1.dLbls.showCatName = False
s1.dLbls.showVal = True
s1.dLbls.showPercent = True
table_cell = 'E' + str(table_start_row_number)
ws.add_chart(pie, table_cell)
if ca_len < 4:
current_row_number = current_row_number - ca_len + 4
else:
for i in range(21, 29 + 1):
current_row_number = 30
ws.row_dimensions[i].height = 0.1
###############################################
current_row_number += 1
has_detail_data_flag = True
table_start_draw_flag = current_row_number + 1
if "timestamps" not in reporting_period_data.keys() or \
reporting_period_data['timestamps'] is None or \
len(reporting_period_data['timestamps']) == 0:
has_detail_data_flag = False
if has_detail_data_flag:
reporting_period_data = report['reporting_period']
times = reporting_period_data['timestamps']
ca_len = len(report['reporting_period']['names'])
real_timestamps_len = timestamps_data_not_equal_0(
report['parameters']['timestamps'])
ws['B' + str(current_row_number)].font = title_font
ws['B' + str(current_row_number)] = name + ' 详细数据'
table_start_row_number = (current_row_number +
1) + ca_len * 6 + real_timestamps_len * 7
current_row_number = table_start_row_number
time = times[0]
has_data = False
if len(time) > 0:
has_data = True
if has_data:
ws.row_dimensions[current_row_number].height = 60
ws['B' + str(current_row_number)].fill = table_fill
ws['B' + str(current_row_number)].border = f_border
ws['B' + str(current_row_number)].font = title_font
ws['B' + str(current_row_number)].alignment = c_c_alignment
ws['B' + str(current_row_number)] = '日期时间'
col = 'B'
for i in range(0, ca_len):
col = chr(ord('C') + i)
ws[col + str(current_row_number)].fill = table_fill
ws[col + str(current_row_number)].font = title_font
ws[col + str(current_row_number)].alignment = c_c_alignment
ws[col + str(current_row_number)] = \
reporting_period_data['names'][i] + " (" + reporting_period_data['units'][i] + ")"
ws[col + str(current_row_number)].border = f_border
end_col = chr(ord(col) + 1)
ws[end_col + str(current_row_number)].fill = table_fill
ws[end_col + str(current_row_number)].font = title_font
ws[end_col + str(current_row_number)].alignment = c_c_alignment
ws[end_col +
str(current_row_number
)] = "总计 (" + reporting_period_data['total_unit'] + ")"
ws[end_col + str(current_row_number)].border = f_border
current_row_number += 1
for i in range(0, len(time)):
ws['B' + str(current_row_number)].font = title_font
ws['B' + str(current_row_number)].alignment = c_c_alignment
ws['B' + str(current_row_number)] = time[i]
ws['B' + str(current_row_number)].border = f_border
col = 'B'
every_day_total = Decimal(0.0)
for j in range(0, ca_len):
col = chr(ord('C') + j)
ws[col + str(current_row_number)].font = title_font
ws[col + str(current_row_number)].alignment = c_c_alignment
value = round(reporting_period_data['values'][j][i], 2)
every_day_total += value
ws[col + str(current_row_number)] = value
ws[col + str(current_row_number)].border = f_border
end_col = chr(ord(col) + 1)
ws[end_col + str(current_row_number)].font = title_font
ws[end_col + str(current_row_number)].alignment = c_c_alignment
ws[end_col + str(current_row_number)] = round(
every_day_total, 2)
ws[end_col + str(current_row_number)].border = f_border
current_row_number += 1
table_end_row_number = current_row_number - 1
ws['B' + str(current_row_number)].font = title_font
ws['B' + str(current_row_number)].alignment = c_c_alignment
ws['B' + str(current_row_number)] = '小计'
ws['B' + str(current_row_number)].border = f_border
col = 'B'
for i in range(0, ca_len):
col = chr(ord('C') + i)
ws[col + str(current_row_number)].font = title_font
ws[col + str(current_row_number)].alignment = c_c_alignment
ws[col + str(current_row_number)] = round(
reporting_period_data['subtotals'][i], 2)
ws[col + str(current_row_number)].border = f_border
# line
line = LineChart()
line.title = '报告期成本 - ' + ws.cell(
column=3 + i, row=table_start_row_number).value
labels = Reference(ws,
min_col=2,
min_row=table_start_row_number + 1,
max_row=table_end_row_number)
line_data = Reference(ws,
min_col=3 + i,
min_row=table_start_row_number,
max_row=table_end_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 = True
line.dLbls.showPercent = False
chart_col = 'B'
chart_cell = chart_col + str(table_start_draw_flag + 6 * i)
ws.add_chart(line, chart_cell)
end_col = chr(ord(col) + 1)
ws[end_col + str(current_row_number)].font = title_font
ws[end_col + str(current_row_number)].alignment = c_c_alignment
ws[end_col + str(current_row_number)] = round(
reporting_period_data['total'], 2)
ws[end_col + str(current_row_number)].border = f_border
current_row_number += 1
else:
for i in range(30, 69 + 1):
current_row_number = 70
ws.row_dimensions[i].height = 0.1
#####################################
has_associated_equipment_flag = True
if "associated_equipment" not in report.keys() or \
"energy_category_names" not in report['associated_equipment'].keys() or \
len(report['associated_equipment']["energy_category_names"]) == 0 \
or 'associated_equipment_names_array' not in report['associated_equipment'].keys() \
or report['associated_equipment']['associated_equipment_names_array'] is None \
or len(report['associated_equipment']['associated_equipment_names_array']) == 0 \
or len(report['associated_equipment']['associated_equipment_names_array'][0]) == 0:
has_associated_equipment_flag = False
if has_associated_equipment_flag:
associated_equipment = report['associated_equipment']
current_row_number += 1
ws['B' + str(current_row_number)].font = title_font
ws['B' + str(current_row_number)] = name + ' 相关设备数据'
current_row_number += 1
ws.row_dimensions[current_row_number].height = 60
ws['B' + str(current_row_number)].fill = table_fill
ws['B' + str(current_row_number)].font = name_font
ws['B' + str(current_row_number)].alignment = c_c_alignment
ws['B' + str(current_row_number)].border = f_border
ws['B' + str(current_row_number)] = '相关设备'
ca_len = len(associated_equipment['energy_category_names'])
for i in range(0, ca_len):
col = chr(ord('C') + i)
ws[col + str(current_row_number)].fill = table_fill
ws[col + str(current_row_number)].font = name_font
ws[col + str(current_row_number)].alignment = c_c_alignment
ws[col + str(current_row_number)].border = f_border
ws[col + str(current_row_number)] = \
reporting_period_data['names'][i] + " (" + reporting_period_data['units'][i] + ")"
end_col = chr(ord('B') + ca_len + 1)
ws[end_col + str(current_row_number)].fill = table_fill
ws[end_col + str(current_row_number)].font = name_font
ws[end_col + str(current_row_number)].alignment = c_c_alignment
ws[end_col + str(current_row_number)].border = f_border
ws[end_col +
str(current_row_number
)] = "总计" + " (" + reporting_period_data['units'][i] + ")"
associated_equipment_len = len(
associated_equipment['associated_equipment_names_array'][0])
for i in range(0, associated_equipment_len):
current_row_number += 1
row = str(current_row_number)
value = Decimal(0.0)
ws['B' + row].font = title_font
ws['B' + row].alignment = c_c_alignment
ws['B' + row] = associated_equipment[
'associated_equipment_names_array'][0][i]
ws['B' + row].border = f_border
for j in range(0, ca_len):
col = chr(ord('C') + j)
ws[col + row].font = title_font
ws[col + row].alignment = c_c_alignment
ws[col + row] = round(
associated_equipment['subtotals_array'][j][i], 2)
value += round(associated_equipment['subtotals_array'][j][i],
2)
ws[col + row].border = f_border
end_col = chr(ord(col) + 1)
ws[end_col + row].font = title_font
ws[end_col + row].alignment = c_c_alignment
ws[end_col + row] = round(value, 2)
ws[end_col + row].border = f_border
##########################################
current_sheet_parameters_row_number = table_start_draw_flag + ca_len * 6 + 1
has_parameters_names_and_timestamps_and_values_data = True
if 'parameters' not in report.keys() or \
report['parameters'] is None or \
'names' not in report['parameters'].keys() or \
report['parameters']['names'] is None or \
len(report['parameters']['names']) == 0 or \
'timestamps' not in report['parameters'].keys() or \
report['parameters']['timestamps'] is None or \
len(report['parameters']['timestamps']) == 0 or \
'values' not in report['parameters'].keys() or \
report['parameters']['values'] is None or \
len(report['parameters']['values']) == 0 or \
timestamps_data_all_equal_0(report['parameters']['timestamps']):
has_parameters_names_and_timestamps_and_values_data = False
if has_parameters_names_and_timestamps_and_values_data:
###############################
# new worksheet
###############################
parameters_data = report['parameters']
parameters_names_len = len(parameters_data['names'])
parameters_ws = wb.create_sheet('相关参数')
parameters_timestamps_data_max_len = \
get_parameters_timestamps_lists_max_len(list(parameters_data['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")
img.width = img.width * 0.85
img.height = img.height * 0.85
# img = Image("myems.png")
parameters_ws.add_image(img, 'B1')
# Title
parameters_ws.row_dimensions[3].height = 60
parameters_ws['B3'].font = name_font
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'].font = name_font
parameters_ws['C3'] = name
parameters_ws['D3'].font = name_font
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'].font = name_font
parameters_ws['E3'] = period_type
parameters_ws['F3'].font = name_font
parameters_ws['F3'].alignment = b_r_alignment
parameters_ws['F3'] = 'Date:'
parameters_ws['G3'].border = b_border
parameters_ws['G3'].alignment = b_c_alignment
parameters_ws['G3'].font = name_font
parameters_ws[
'G3'] = reporting_start_datetime_local + "__" + reporting_end_datetime_local
parameters_ws.merge_cells("G3:H3")
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)] = name + ' 相关参数'
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 = 'B'
for i in range(0, parameters_names_len):
if len(parameters_data['timestamps'][i]) == 0:
continue
parameters_ws[table_current_col_number +
str(parameters_ws_current_row_number -
1)].fill = table_fill
parameters_ws[table_current_col_number +
str(parameters_ws_current_row_number -
1)].border = f_border
col = decimal_to_column(
column_to_decimal(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_data['names'][i]
table_current_row_number = parameters_ws_current_row_number
for j, value in enumerate(list(parameters_data['timestamps'][i])):
col = 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 = decimal_to_column(column_to_decimal(col) + 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
parameters_ws[col + str(table_current_row_number)] = round(
parameters_data['values'][i][j], 2)
table_current_row_number += 1
table_current_col_number = decimal_to_column(
column_to_decimal(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)] = name + ' 相关参数'
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_data['timestamps'][i]) == 0:
continue
line = LineChart()
data_col = 3 + col_index * 3
labels_col = 2 + col_index * 3
col_index += 1
line.title = '相关参数 - ' + \
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_data['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_data['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 get(self, request, *args, **kwargs):
today = datetime.datetime.now()
today = today.strftime('%Y-%m-%d')
query = ProduccionDet.objects.all()
wb = Workbook()
ws = wb.active
ws.tittle = 'Producción Embolsados'
#Establer el nombre del archivo
nombre_archivo = str(today) + "Reporte Producción Embolslados.xlsx"
ws['B1'].alignment = Alignment(horizontal='left', vertical='center')
ws['B1'].border = Border(left=Side(border_style='thin'),
right=Side(border_style='thin'),
top=Side(border_style='thin'),
bottom=Side(border_style='thin'))
ws['B1'].fill = PatternFill(start_color='66FFCC',
end_color='66FFCC',
fill_type='solid')
ws['B1'].font = Font(name='calibri', size=12, bold=True)
ws['B1'] = 'Mar Bran S.A. de C.V.'
ws.merge_cells('B1:F1')
ws['B2'].alignment = Alignment(horizontal='left', vertical='center')
ws['B2'].border = Border(left=Side(border_style='thin'),
right=Side(border_style='thin'),
top=Side(border_style='thin'),
bottom=Side(border_style='thin'))
ws['B2'].fill = PatternFill(start_color='66FFCC',
end_color='66FFCC',
fill_type='solid')
ws['B2'].font = Font(name='calibri', size=12, bold=True)
ws['B2'] = 'Innovación, Mejora Continua y Six Sigma'
ws.merge_cells('B2:F2')
ws['B3'].alignment = Alignment(horizontal='left', vertical='center')
ws['B3'].border = Border(left=Side(border_style='thin'),
right=Side(border_style='thin'),
top=Side(border_style='thin'),
bottom=Side(border_style='thin'))
ws['B3'].fill = PatternFill(start_color='66FFCC',
end_color='66FFCC',
fill_type='solid')
ws['B3'].font = Font(name='calibri', size=12, bold=True)
ws['B3'] = 'Reporte de Producción Embolsados'
ws['G3'].alignment = Alignment(horizontal='left', vertical='center')
ws['G3'].border = Border(left=Side(border_style='thin'),
right=Side(border_style='thin'),
top=Side(border_style='thin'),
bottom=Side(border_style='thin'))
ws['G3'].fill = PatternFill(start_color='66FFCC',
end_color='66FFCC',
fill_type='solid')
ws['G3'].font = Font(name='calibri', size=12, bold=True)
ws['G3'] = 'FECHA'
ws['H3'].alignment = Alignment(horizontal='left', vertical='center')
ws['H3'].border = Border(left=Side(border_style='thin'),
right=Side(border_style='thin'),
top=Side(border_style='thin'),
bottom=Side(border_style='thin'))
ws['H3'].fill = PatternFill(start_color='66FFCC',
end_color='66FFCC',
fill_type='solid')
ws['H3'].font = Font(name='calibri', size=12, bold=True)
ws['H3'] = today
ws.merge_cells('B3:F3')
ws.row_dimensions[1].height = 20
ws.row_dimensions[2].height = 20
ws.row_dimensions[3].height = 20
ws.column_dimensions['B'].width = 20
ws.column_dimensions['C'].width = 20
ws.column_dimensions['D'].width = 20
ws.column_dimensions['E'].width = 30
ws.column_dimensions['F'].width = 20
ws.column_dimensions['G'].width = 60
ws.column_dimensions['H'].width = 60
ws.column_dimensions['G'].width = 20
ws.column_dimensions['J'].width = 60
ws['B6'].alignment = Alignment(horizontal='center', vertical='center')
ws['B6'].border = Border(left=Side(border_style='thin'),
right=Side(border_style='thin'),
top=Side(border_style='thin'),
bottom=Side(border_style='thin'))
ws['B6'].fill = PatternFill(start_color='66CFCC',
end_color='66CFCC',
fill_type='solid')
ws['B6'].font = Font(name='calibri', size=11, bold=True)
ws['B6'] = 'Fecha'
ws['C6'].alignment = Alignment(horizontal='center', vertical='center')
ws['C6'].border = Border(left=Side(border_style='thin'),
right=Side(border_style='thin'),
top=Side(border_style='thin'),
bottom=Side(border_style='thin'))
ws['C6'].fill = PatternFill(start_color='66CFCC',
end_color='66CFCC',
fill_type='solid')
ws['C6'].font = Font(name='calibri', size=11, bold=True)
ws['C6'] = 'Planta'
ws['D6'].alignment = Alignment(horizontal='center', vertical='center')
ws['D6'].border = Border(left=Side(border_style='thin'),
right=Side(border_style='thin'),
top=Side(border_style='thin'),
bottom=Side(border_style='thin'))
ws['D6'].fill = PatternFill(start_color='66CFCC',
end_color='66CFCC',
fill_type='solid')
ws['D6'].font = Font(name='calibri', size=11, bold=True)
ws['D6'] = 'Línea'
ws['E6'].alignment = Alignment(horizontal='center', vertical='center')
ws['E6'].border = Border(left=Side(border_style='thin'),
right=Side(border_style='thin'),
top=Side(border_style='thin'),
bottom=Side(border_style='thin'))
ws['E6'].fill = PatternFill(start_color='66CFCC',
end_color='66CFCC',
fill_type='solid')
ws['E6'].font = Font(name='calibri', size=11, bold=True)
ws['E6'] = 'Supervisor'
ws['F6'].alignment = Alignment(horizontal='center', vertical='center')
ws['F6'].border = Border(left=Side(border_style='thin'),
right=Side(border_style='thin'),
top=Side(border_style='thin'),
bottom=Side(border_style='thin'))
ws['F6'].fill = PatternFill(start_color='66CFCC',
end_color='66CFCC',
fill_type='solid')
ws['F6'].font = Font(name='calibri', size=11, bold=True)
ws['F6'] = 'Turno'
ws['G6'].alignment = Alignment(horizontal='center', vertical='center')
ws['G6'].border = Border(left=Side(border_style='thin'),
right=Side(border_style='thin'),
top=Side(border_style='thin'),
bottom=Side(border_style='thin'))
ws['G6'].fill = PatternFill(start_color='66CFCC',
end_color='66CFCC',
fill_type='solid')
ws['G6'].font = Font(name='calibri', size=11, bold=True)
ws['G6'] = 'Plantilla'
ws['H6'].alignment = Alignment(horizontal='center', vertical='center')
ws['H6'].border = Border(left=Side(border_style='thin'),
right=Side(border_style='thin'),
top=Side(border_style='thin'),
bottom=Side(border_style='thin'))
ws['H6'].fill = PatternFill(start_color='66CFCC',
end_color='66CFCC',
fill_type='solid')
ws['H6'].font = Font(name='calibri', size=11, bold=True)
ws['H6'] = 'Proc./term.'
ws['I6'].alignment = Alignment(horizontal='center', vertical='center')
ws['I6'].border = Border(left=Side(border_style='thin'),
right=Side(border_style='thin'),
top=Side(border_style='thin'),
bottom=Side(border_style='thin'))
ws['I6'].fill = PatternFill(start_color='66CFCC',
end_color='66CFCC',
fill_type='solid')
ws['I6'].font = Font(name='calibri', size=11, bold=True)
ws['I6'] = 'Producto'
ws['J6'].alignment = Alignment(horizontal='center', vertical='center')
ws['J6'].border = Border(left=Side(border_style='thin'),
right=Side(border_style='thin'),
top=Side(border_style='thin'),
bottom=Side(border_style='thin'))
ws['J6'].fill = PatternFill(start_color='66CFCC',
end_color='66CFCC',
fill_type='solid')
ws['J6'].font = Font(name='calibri', size=11, bold=True)
ws['J6'] = 'Peso (Lbs)'
ws['K6'].alignment = Alignment(horizontal='center', vertical='center')
ws['K6'].border = Border(left=Side(border_style='thin'),
right=Side(border_style='thin'),
top=Side(border_style='thin'),
bottom=Side(border_style='thin'))
ws['K6'].fill = PatternFill(start_color='66CFCC',
end_color='66CFCC',
fill_type='solid')
ws['K6'].font = Font(name='calibri', size=11, bold=True)
ws['K6'] = 'Cantidad (cajas)'
ws['L6'].alignment = Alignment(horizontal='center', vertical='center')
ws['L6'].border = Border(left=Side(border_style='thin'),
right=Side(border_style='thin'),
top=Side(border_style='thin'),
bottom=Side(border_style='thin'))
ws['L6'].fill = PatternFill(start_color='66CFCC',
end_color='66CFCC',
fill_type='solid')
ws['L6'].font = Font(name='calibri', size=11, bold=True)
ws['L6'] = 'Resto (lbs)'
ws['M6'].alignment = Alignment(horizontal='center', vertical='center')
ws['M6'].border = Border(left=Side(border_style='thin'),
right=Side(border_style='thin'),
top=Side(border_style='thin'),
bottom=Side(border_style='thin'))
ws['M6'].fill = PatternFill(start_color='66CFCC',
end_color='66CFCC',
fill_type='solid')
ws['M6'].font = Font(name='calibri', size=11, bold=True)
ws['M6'] = 'Producción (lbs)'
ws['N6'].alignment = Alignment(horizontal='center', vertical='center')
ws['N6'].border = Border(left=Side(border_style='thin'),
right=Side(border_style='thin'),
top=Side(border_style='thin'),
bottom=Side(border_style='thin'))
ws['N6'].fill = PatternFill(start_color='66CFCC',
end_color='66CFCC',
fill_type='solid')
ws['N6'].font = Font(name='calibri', size=11, bold=True)
ws['N6'] = 'Merma(%)'
controlador = 7
for q in query:
id_enc = q.produccion_id
query2 = ProduccionEnc.objects.filter(id=id_enc)
for x in query2:
ws.cell(row=controlador,
column=2).alignment = Alignment(horizontal='center',
vertical='center')
ws.cell(row=controlador, column=2).border = Border(
left=Side(border_style='thin'),
right=Side(border_style='thin'),
top=Side(border_style='thin'),
bottom=Side(border_style='thin'))
ws.cell(row=controlador, column=2).font = Font(name='calibri',
size=11,
bold=True)
ws.cell(row=controlador, column=2).value = x.fecha_produccion
ws.cell(row=controlador,
column=3).alignment = Alignment(horizontal='center',
vertical='center')
ws.cell(row=controlador, column=3).border = Border(
left=Side(border_style='thin'),
right=Side(border_style='thin'),
top=Side(border_style='thin'),
bottom=Side(border_style='thin'))
ws.cell(row=controlador, column=3).font = Font(name='calibri',
size=11,
bold=True)
ws.cell(row=controlador, column=3).value = str(x.planta)
ws.cell(row=controlador,
column=4).alignment = Alignment(horizontal='center',
vertical='center')
ws.cell(row=controlador, column=4).border = Border(
left=Side(border_style='thin'),
right=Side(border_style='thin'),
top=Side(border_style='thin'),
bottom=Side(border_style='thin'))
ws.cell(row=controlador, column=4).font = Font(name='calibri',
size=11,
bold=True)
ws.cell(row=controlador, column=4).value = str(x.linea)
ws.cell(row=controlador,
column=5).alignment = Alignment(horizontal='center',
vertical='center')
ws.cell(row=controlador, column=5).border = Border(
left=Side(border_style='thin'),
right=Side(border_style='thin'),
top=Side(border_style='thin'),
bottom=Side(border_style='thin'))
ws.cell(row=controlador, column=5).font = Font(name='calibri',
size=11,
bold=True)
ws.cell(row=controlador, column=5).value = str(x.supervisor)
response = HttpResponse(content_type='application/ms-excel')
contenido = "attachment; filename = {0}".format(nombre_archivo)
response["Content-Disposition"] = contenido
wb.save(response)
return response
def create_excel(self):
#创建新excel，加入格式字体，填充，颜色，预置名称等
letter = ['A', 'C', 'D', 'E', 'F', 'G', 'H', 'I']
self.excel_name = self.excel_addr + '\\' + self.report_time + '-Report.xlsx'
wb = Workbook()
ws = wb['Sheet']
ws.title = u'自动化测试报告'
ws = wb.create_sheet(u'用例执行情况')
#编辑第一个工作表格信息
ws = wb[u'自动化测试报告']
white = Font(size=12, bold=False, name=u'等线', color="FFFFFF") #白色字体
black = Font(size=12, bold=False, name=u'等线', color="000000") #黑色字体
#合并单元格
ws.merge_cells('A1:I1')
ws.merge_cells('A2:I2')
ws.merge_cells('A5:I5')
ws.merge_cells('A6:D6')
ws.merge_cells('F6:I6')
ws.merge_cells('A4:B4')
for i in range(7, 51):
ws.merge_cells('B%d:C%d' % (i, i))
#更改行高和列宽
ws.column_dimensions['A'].width = 5
ws.column_dimensions['B'].width = 15
ws.column_dimensions['C'].width = 18
ws.column_dimensions['D'].width = 25
ws.column_dimensions['E'].width = 15
ws.column_dimensions['F'].width = 20
ws.column_dimensions['G'].width = 15
ws.column_dimensions['H'].width = 25
ws.column_dimensions['I'].width = 25
for i in range(1, 51):
ws.row_dimensions[i].height = 15
#添加表格线条，
thin = Side(border_style="thin", color="000000")
border = Border(left=thin, right=thin, top=thin, bottom=thin)
for i in ['A', 'B', 'C', 'D', 'E', 'F', 'G', 'H', 'I']:
for j in range(1, 51):
ws[i + str(j)].border = border
alignment = Alignment(horizontal="center",
vertical="center",
wrap_text=True) #所有表格居中
ws[i + str(j)].alignment = alignment
ws[i + str(j)].font = black
#添加表格颜色
ws['A1'].fill = PatternFill(patternType='solid', start_color='333399')
ws['A2'].fill = PatternFill(patternType='solid', start_color='99CC00')
for i in ['A', 'B', 'C', 'D', 'E', 'F', 'G', 'H', 'I']:
ws[i + str(3)].fill = PatternFill(patternType='solid',
start_color='0066CC')
ws[i + str(3)].font = white
ws['C6'].fill = PatternFill(patternType='solid', start_color='0066CC')
for i in ['A', 'B', 'D']:
ws[i + str(7)].fill = PatternFill(patternType='solid',
start_color='0066CC')
ws['A6'].fill = PatternFill(patternType='solid', start_color='99CC00')
ws['E6'].fill = PatternFill(patternType='solid', start_color='FF6600')
ws['E7'].fill = PatternFill(patternType='solid', start_color='FF9900')
ws['F6'].fill = PatternFill(patternType='solid', start_color='FFCC99')
ws['F7'].fill = PatternFill(patternType='solid', start_color='FFCC00')
for i in ['F', 'G', 'H', 'I']:
ws[i + str(7)].fill = PatternFill(patternType='solid',
start_color='0066CC')
#写入字符
ws['A7'].font = ws['B7'].font = ws['D7'].font = ws['A1'].font = white
ws['A1'] = u'自动化测试报告'
ws['F6'].font = ws['E6'].font = ws['E7'].font = ws['A6'].font = ws[
'A2'].font = black
ws['F7'].font = ws['G7'].font = ws['H7'].font = ws['I7'].font = white
ws['A2'] = u'自动化测试用例总计'
ws['A6'] = u'测试模块列表'
ws['E6'] = u'是否执行'
ws['E7'] = u'执行标识符'
ws['F6'] = u'执行情况'
ws['A7'] = u'NO.'
ws['B7'] = u'模块'
ws['D7'] = u'自动化测试用例数量'
ws['F7'] = u'执行时间'
ws['G7'] = u'总计执行'
ws['H7'] = u'通过数目'
ws['I7'] = u'失败数目'
title1 = [
u'自动化测试项目', u'自动化测试用例数量', u'自动化测试总计执行', 'Pass', 'Fail', u'测试用时',
u'开始时间', u'结束时间'
]
ws.merge_cells('A3:B3')
for i in range(len(letter)):
ws[letter[i] + str(3)] = title1[i]
#编辑第二个表格信息
ws1 = wb[u'用例执行情况']
#更改单元格宽度
for i in range(1, 1000):
ws1.row_dimensions[i].height = 40 #更改行高为40
for i in ['A', 'B', 'C', 'D', 'E', 'F', 'G', 'H', 'I', 'J']:
for j in range(1, 1000):
ws1[i + str(j)].border = border
ws1[i + str(j)].font = black
alignment = Alignment(horizontal="center",
vertical="center",
wrap_text=True) #所有表格居中
ws1[i + str(j)].alignment = alignment
ws1.column_dimensions['A'].width = 25
ws1.column_dimensions['B'].width = 40
ws1.column_dimensions['C'].width = 13
ws1.column_dimensions['D'].width = 35
ws1.column_dimensions['E'].width = 25
ws1.column_dimensions['F'].width = 40
ws1.column_dimensions['G'].width = 15
ws1.column_dimensions['H'].width = 100
ws1.column_dimensions['I'].width = 25
ws1.column_dimensions['J'].width = 25
#合并单元格A1。添加颜色
ws1.merge_cells('A1:J1')
ws1['A1'].fill = PatternFill(patternType='solid', start_color='0066CC')
ws1['A1'].alignment = alignment
ws1['A1'].font = white
ws1['A1'] = u'自动化测试用例执行情况'
#添加A2-G2格式和字体
x1 = [
'Suite Name', 'Case Name', 'Tags', u'输入数据', u'实际结果', u'预期结果',
'Status', 'Message', 'Start-Time', 'End-Time'
]
x2 = ['A', 'B', 'C', 'D', 'E', 'F', 'G', 'H', 'I', 'J']
for i in ['A', 'B', 'C', 'D', 'E', 'F', 'G', 'H', 'I', 'J']:
ws1[i + str(2)].fill = PatternFill(patternType='solid',
start_color='C0C0C0') #设置字体
ws1['A2'] = x1[0]
ws1['B2'] = x1[1]
ws1['C2'] = x1[2]
ws1['D2'] = x1[3]
ws1['E2'] = x1[4]
ws1['F2'] = x1[5]
ws1['G2'] = x1[6]
ws1['H2'] = x1[7]
ws1['I2'] = x1[8]
ws1['J2'] = x1[9]
#wb.save(addr)#保存excel文件
return wb #excel对象
def Ping(sheet,sheet_title,ping_ip,num,file_name):
#workbook = Workbook()
#sheet = workbook.active
#workbook = Workbook()
#sheet = workbook.worksheets[int(num)]
#sheet = workbook.active
sheet = workbook.create_sheet(sheet_title,int(num))
#wb = Workbook()
#sheet = workbook.create_sheet("Sheet_A",int(num))
#sheet.title = sheet_title
#sheet = workbook.create_sheet("Sheet_B", 1)
#sheet.title = sheet_title
#wb.save(filename = 'sample_book.xlsx')
sheet["A1"] = "Management IP"
sheet["B1"] = "Hostname"
sheet["C1"] = "GBN ID"
sheet["D1"] = "Model"
sheet["E1"] = "Ping Status"
sheet["F1"] = "Success Rate (%)"
sheet["G1"] = "LAN Subnet"
sheet["H1"] = "LAN Status"
sheet["I1"] = "Date/Time"
username="******"
password="******"
def Date_time(start_row,start_column):
dt_object = datetime.now()
sheet.cell(row=start_row, column=start_column).value = dt_object
return
def Background_color(row,color,id1,id2):
fill = PatternFill(start_color=color, fill_type = "solid")
for cell in sheet[row][id1:id2]:
cell.fill = fill
return
Background_color(1,"009999FF",0,9)
with open(file_name) as f:
endpointIPs = f.readlines()
start_row = 2
start_column = 1
for endpoint in endpointIPs:
host = endpoint.strip()
print(host)
print ('Connecting to device: ' + host)
ios_device = {
'device_type': 'cisco_ios',
'ip': host,
'username': username,
'password': password
}
sheet.cell(row=start_row, column=start_column).value = host
start_column += 1
print(start_column)
print(start_row)
try:
remote_conn = ConnectHandler(**ios_device)
#sheet.cell(row=start_row, column=start_column).value = host
#start_column += 1
#print(start_column)
#print(start_row)
GBN_ID= remote_conn.send_command("dis current-configuration | i sysname ")
print (GBN_ID)
hostname = GBN_ID.split(" ")[2]
print(hostname)
sheet.cell(row=start_row, column=start_column).value = hostname
start_column += 1
print(hostname[0:8])
sheet.cell(row=start_row, column=start_column).value = hostname[0:8]
start_column += 1
sheet.cell(row=start_row, column=start_column).value = hostname[21:25]
start_column += 1
output= remote_conn.send_command("dis ip int brief ")
print (output)
match = re.compile(r'(10)\.(\d|\d\d|\d\d\d)\.(\d|\d\d|\d\d\d)\.(\d|\d\d|\d\d\d)')
match1 = re.compile(r'(10)\.(\d|\d\d|\d\d\d)\.(\d|\d\d|\d\d\d)\.(\d|\d\d|\d\d\d)/(\d\d)\s*(up|down|\*down)')
matches = match.finditer(output)
matches1 = match1.finditer(output)
#print(matches1)
# matches = pattern.finditer(urls)
List=[]
for match in matches:
#print(match)
List.append(match[0])
print(List[0])
List1=[]
for match1 in matches1:
#print(match2)
List1.append(match1[0])
print(List1)
#if List1.__contains__("up"):
status=[]
for i in List1:
status.append(i.split(' '))
#print(status)
#print("Interface is Up")
for item in List:
print(item)
output1= remote_conn.send_command("ping -a %s %s" % (item,ping_ip) )
print("ping -a %s %s" % (item,ping_ip))
print (output1)
match2 = re.compile(r'(0|25|50|75|100)\.(00\%)')
matches2 = match2.finditer(output1)
List2 = []
for match2 in matches2:
# print(match)
List2.append(match2[0])
#print(match2[0])
print(List2)
print("advit")
for id in List2:
if id == "0.00%":
sheet.cell(row=start_row, column=start_column).value = "Pingable"
Background_color(start_row,"00008000",4,5)
start_column += 1
id1 = 100
#sheet.cell(row=start_row, column=start_column).value = "=MIN(100-20)"
sheet.cell(row=start_row, column=start_column).value = id1
#print(start_column)
#Background_color(start_row,"00008000",7,8)
start_column += 1
print(start_column)
print(start_row)
elif id == "25.00%":
sheet.cell(row=start_row, column=start_column).value = "Pingable"
Background_color(start_row,"00008000",4,5)
start_column += 1
id1 = 75
#sheet.cell(row=start_row, column=start_column).value = "=MIN(100-20)"
sheet.cell(row=start_row, column=start_column).value = id1
#print(start_column)
#start_row += 1
start_column += 1
print(start_column)
print(start_row)
elif id == "50.00%":
sheet.cell(row=start_row, column=start_column).value = "Pingable"
Background_color(start_row,"00008000",4,5)
start_column += 1
id1 = 50
#sheet.cell(row=start_row, column=start_column).value = "=MIN(100-20)"
sheet.cell(row=start_row, column=start_column).value = id1
#print(start_column)
#start_row += 1
start_column += 1
print(start_column)
print(start_row)
elif id == "75.00%":
sheet.cell(row=start_row, column=start_column).value = "Pingable"
Background_color(start_row,"00008000",4,5)
start_column += 1
id1 = 25
#sheet.cell(row=start_row, column=start_column).value = "=MIN(100-20)"
sheet.cell(row=start_row, column=start_column).value = id1
#print(start_column)
#start_row += 1
start_column += 1
print(start_column)
print(start_row)
else:
sheet.cell(row=start_row, column=start_column).value = "Not Pingable"
Background_color(start_row,"00FF0000",4,5)
start_column += 1
sheet.cell(row=start_row, column=start_column).value = "Unsuccessfull"
#print(start_column)
#start_row += 1
start_column += 1
print(start_column)
print(start_row)
print("============================================================\n\n")
for ip in List:
Lan = i.split(" ")
print(Lan)
sheet.cell(row=start_row, column=start_column).value = Lan[0]
#start_row += 1
start_column += 1
print(start_column)
print(start_row)
if Lan.__contains__("up"):
sheet.cell(row=start_row, column=start_column).value = "UP"
Background_color(start_row,"00008000",7,8)
start_column += 1
#start_row += 1
#start_column -= 7
else:
sheet.cell(row=start_row, column=start_column).value = "Down"
Background_color(start_row,"00FF0000",7,8)
start_column += 1
#start_row += 1
#start_column -= 7
Date_time(start_row,start_column)
start_row += 1
start_column -= 8
except (AuthenticationException):
print ('Authentication failure: ' + host)
sheet.merge_cells(start_row=start_row,start_column=start_column,end_row=start_row,end_column=8)
sheet.cell(row=start_row, column=start_column).value = "Authentication failure"
Background_color(start_row,"00800000",1,2)
start_column += 1
Date_time(start_row,9)
start_row += 1
start_column -= 2
print(start_column)
print(start_row)
print("============================================================\n\n")
continue
except (EOFError):
print ('End of file while attempting device ' + host)
sheet.merge_cells(start_row=start_row,start_column=start_column,end_row=start_row,end_column=8)
sheet.cell(row=start_row, column=start_column).value = "End of file while attempting device"
Background_color(start_row,"00800000",1,2)
start_column += 1
Date_time(start_row,9)
start_row += 1
start_column -= 2
print(start_column)
print(start_row)
print("============================================================\n\n")
continue
except:
print ('Device is not reachable')
sheet.merge_cells(start_row=start_row,start_column=start_column,end_row=start_row,end_column=8)
sheet.cell(row=start_row, column=start_column).value = "Device is not reachable"
Background_color(start_row,"00FFFF00",1,2)
start_column += 1
Date_time(start_row,9)
start_row += 1
start_column -= 2
print(start_column)
print(start_row)
print("============================================================\n\n")
continue
# Create a few styles
font = Font(bold=True,
size=11,
color="00000000")
alignment=Alignment(horizontal='center',
vertical='center',
text_rotation=0,
wrap_text=False,
shrink_to_fit=False,
indent=0)
border = Border(left=Side(border_style="thin",
color='FF000000'),
right=Side(border_style="thin",
color='FF000000'),
top=Side(border_style="thin",
color='FF000000'),
bottom=Side(border_style="thin",
color='FF000000'),
diagonal=Side(border_style="thin",
color='FF000000'),
diagonal_direction=0,
outline=Side(border_style="thin",
color='FF000000'),
vertical=Side(border_style="thin",
color='FF000000'),
horizontal=Side(border_style="thin",
color='FF000000')
)
#fill = PatternFill(start_color="00008080", end_color="00008080", fill_type = "solid")
for cell in sheet[1:1]:
cell.font = font
print(start_row)
for i in range(1,start_row):
for cell in sheet[i]:
cell.alignment = alignment
cell.border = border
#cell.fill = fill
#sheet.title = sheet_title
print(workbook.sheetnames)
workbook.save(filename="GBN_Ping.xlsx")
return
def get (self, request, *args, **kwargs):
query = TiempoMuertonDet.objects.all()
wb = Workbook()
ws = wb.active
ws.tittle='Tiempos Muertos'
#Establer el nombre del archivo
nombre_archivo = "Reporte Tiempos Muertosw.xlsx"
ws['B1'].alignment= Alignment(horizontal='left', vertical='center')
ws['B1'].border =Border(left=Side(border_style='thin'),right=Side(border_style='thin'),
top=Side(border_style='thin'), bottom=Side(border_style='thin'))
ws['B1'].fill = PatternFill(start_color='66FFCC', end_color='66FFCC', fill_type='solid')
ws['B1'].font = Font(name='calibri', size=12, bold=True)
ws['B1']='Company'
ws.merge_cells('B1:F1')
ws['B2'].alignment= Alignment(horizontal='left', vertical='center')
ws['B2'].border =Border(left=Side(border_style='thin'),right=Side(border_style='thin'),
top=Side(border_style='thin'), bottom=Side(border_style='thin'))
ws['B2'].fill = PatternFill(start_color='66FFCC', end_color='66FFCC', fill_type='solid')
ws['B2'].font = Font(name='calibri', size=12, bold=True)
ws['B2']='Department'
ws.merge_cells('B2:F2')
ws['B3'].alignment= Alignment(horizontal='left', vertical='center')
ws['B3'].border =Border(left=Side(border_style='thin'),right=Side(border_style='thin'),
top=Side(border_style='thin'), bottom=Side(border_style='thin'))
ws['B3'].fill = PatternFill(start_color='66FFCC', end_color='66FFCC', fill_type='solid')
ws['B3'].font = Font(name='calibri', size=12, bold=True)
ws['B3']='Reporte de Tiempos Muertos'
ws.merge_cells('B3:F3')
ws.row_dimensions[1].height=20
ws.row_dimensions[2].height=20
ws.row_dimensions[3].height=20
ws.column_dimensions['B'].width=20
ws.column_dimensions['C'].width=20
ws.column_dimensions['D'].width=20
ws.column_dimensions['E'].width=20
ws['B6'].alignment= Alignment(horizontal='center', vertical='center')
ws['B6'].border =Border(left=Side(border_style='thin'),right=Side(border_style='thin'),
top=Side(border_style='thin'), bottom=Side(border_style='thin'))
ws['B6'].fill = PatternFill(start_color='66CFCC', end_color='66CFCC', fill_type='solid')
ws['B6'].font = Font(name='calibri', size=11, bold=True)
ws['B6']='Fecha'
ws['C6'].alignment= Alignment(horizontal='center', vertical='center')
ws['C6'].border =Border(left=Side(border_style='thin'),right=Side(border_style='thin'),
top=Side(border_style='thin'), bottom=Side(border_style='thin'))
ws['C6'].fill = PatternFill(start_color='66CFCC', end_color='66CFCC', fill_type='solid')
ws['C6'].font = Font(name='calibri', size=11, bold=True)
ws['C6']='Planta'
ws['D6'].alignment= Alignment(horizontal='center', vertical='center')
ws['D6'].border =Border(left=Side(border_style='thin'),right=Side(border_style='thin'),
top=Side(border_style='thin'), bottom=Side(border_style='thin'))
ws['D6'].fill = PatternFill(start_color='66CFCC', end_color='66CFCC', fill_type='solid')
ws['D6'].font = Font(name='calibri', size=11, bold=True)
ws['D6']='Línea'
ws['E6'].alignment= Alignment(horizontal='center', vertical='center')
ws['E6'].border =Border(left=Side(border_style='thin'),right=Side(border_style='thin'),
top=Side(border_style='thin'), bottom=Side(border_style='thin'))
ws['E6'].fill = PatternFill(start_color='66CFCC', end_color='66CFCC', fill_type='solid')
ws['E6'].font = Font(name='calibri', size=11, bold=True)
ws['E6']='supervisor'
ws['F6'].alignment= Alignment(horizontal='center', vertical='center')
ws['F6'].border =Border(left=Side(border_style='thin'),right=Side(border_style='thin'),
top=Side(border_style='thin'), bottom=Side(border_style='thin'))
ws['F6'].fill = PatternFill(start_color='66CFCC', end_color='66CFCC', fill_type='solid')
ws['F6'].font = Font(name='calibri', size=11, bold=True)
ws['F6']='Turno'
ws['G6'].alignment= Alignment(horizontal='center', vertical='center')
ws['G6'].border =Border(left=Side(border_style='thin'),right=Side(border_style='thin'),
top=Side(border_style='thin'), bottom=Side(border_style='thin'))
ws['G6'].fill = PatternFill(start_color='66CFCC', end_color='66CFCC', fill_type='solid')
ws['G6'].font = Font(name='calibri', size=11, bold=True)
ws['G6']='Causa'
controlador = 7
for q in query:
ws.cell(row=controlador,column=7).alignment= Alignment(horizontal='center', vertical='center')
ws.cell(row=controlador,column=7).border =Border(left=Side(border_style='thin'),right=Side(border_style='thin'),
top=Side(border_style='thin'), bottom=Side(border_style='thin'))
ws.cell(row=controlador,column=7).fill = PatternFill(start_color='66CFCC', end_color='66CFCC', fill_type='solid')
ws.cell(row=controlador,column=7).font = Font(name='calibri', size=11, bold=True)
ws.cell(row=controlador,column=7).value=str(q.causa)
ws.cell(row=controlador,column=8).alignment= Alignment(horizontal='center', vertical='center')
ws.cell(row=controlador,column=8).border =Border(left=Side(border_style='thin'),right=Side(border_style='thin'),
top=Side(border_style='thin'), bottom=Side(border_style='thin'))
ws.cell(row=controlador,column=8).fill = PatternFill(start_color='66CFCC', end_color='66CFCC', fill_type='solid')
ws.cell(row=controlador,column=8).font = Font(name='calibri', size=11, bold=True)
ws.cell(row=controlador,column=8).value=q.cantidad
ws.cell(row=controlador,column=8).alignment= Alignment(horizontal='center', vertical='center')
ws.cell(row=controlador,column=8).border =Border(left=Side(border_style='thin'),right=Side(border_style='thin'),
top=Side(border_style='thin'), bottom=Side(border_style='thin'))
ws.cell(row=controlador,column=8).fill = PatternFill(start_color='66CFCC', end_color='66CFCC', fill_type='solid')
ws.cell(row=controlador,column=8).font = Font(name='calibri', size=11, bold=True)
ws.cell(row=controlador,column=8).value=q.obs
id_enc= q.tiempo_muerto_id
query2 = TiempoMuertoEnc.objects.filter(id=id_enc)
for x in query2:
ws.cell(row=controlador,column=2).alignment= Alignment(horizontal='center', vertical='center')
ws.cell(row=controlador,column=2).border =Border(left=Side(border_style='thin'),right=Side(border_style='thin'),
top=Side(border_style='thin'), bottom=Side(border_style='thin'))
ws.cell(row=controlador,column=2).fill = PatternFill(start_color='66CFCC', end_color='66CFCC', fill_type='solid')
ws.cell(row=controlador,column=2).font = Font(name='calibri', size=11, bold=True)
ws.cell(row=controlador,column=2).value=x.fecha_produccion
ws.cell(row=controlador,column=3).alignment= Alignment(horizontal='center', vertical='center')
ws.cell(row=controlador,column=3).border =Border(left=Side(border_style='thin'),right=Side(border_style='thin'),
top=Side(border_style='thin'), bottom=Side(border_style='thin'))
ws.cell(row=controlador,column=3).fill = PatternFill(start_color='66CFCC', end_color='66CFCC', fill_type='solid')
ws.cell(row=controlador,column=3).font = Font(name='calibri', size=11, bold=True)
ws.cell(row=controlador,column=3).value=str(x.planta)
ws.cell(row=controlador,column=4).alignment= Alignment(horizontal='center', vertical='center')
ws.cell(row=controlador,column=4).border =Border(left=Side(border_style='thin'),right=Side(border_style='thin'),
top=Side(border_style='thin'), bottom=Side(border_style='thin'))
ws.cell(row=controlador,column=4).fill = PatternFill(start_color='66CFCC', end_color='66CFCC', fill_type='solid')
ws.cell(row=controlador,column=4).font = Font(name='calibri', size=11, bold=True)
ws.cell(row=controlador,column=4).value=str(x.linea)
ws.cell(row=controlador,column=5).alignment= Alignment(horizontal='center', vertical='center')
ws.cell(row=controlador,column=5).border =Border(left=Side(border_style='thin'),right=Side(border_style='thin'),
top=Side(border_style='thin'), bottom=Side(border_style='thin'))
ws.cell(row=controlador,column=5).fill = PatternFill(start_color='66CFCC', end_color='66CFCC', fill_type='solid')
ws.cell(row=controlador,column=5).font = Font(name='calibri', size=11, bold=True)
ws.cell(row=controlador,column=5).value=str(x.supervisor)
ws.cell(row=controlador,column=6).alignment= Alignment(horizontal='center', vertical='center')
ws.cell(row=controlador,column=6).border =Border(left=Side(border_style='thin'),right=Side(border_style='thin'),
top=Side(border_style='thin'), bottom=Side(border_style='thin'))
ws.cell(row=controlador,column=6).fill = PatternFill(start_color='66CFCC', end_color='66CFCC', fill_type='solid')
ws.cell(row=controlador,column=6).font = Font(name='calibri', size=11, bold=True)
ws.cell(row=controlador,column=6).value=str(x.turno)
controlador +=1
response = HttpResponse(content_type='application/ms-excel')
contenido = "attachment; filename = {0}".format(nombre_archivo)
response["Content-Disposition"] = contenido
wb.save(response)
return response
from openpyxl.styles import PatternFill, Alignment, Side, Border
# 定义表头颜色样式为橙色
header_fill = PatternFill('solid', fgColor='FF7F24')
# 定义表中颜色样式为淡黄色
content_fill = PatternFill('solid', fgColor='FFFFE0')
# 定义表尾颜色样式为淡桔红色
bottom_fill = PatternFill('solid', fgColor='EE9572')
# 定义对齐样式横向居中、纵向居中
align = Alignment(horizontal='center', vertical='center')
# 定义边样式为细条
side = Side('thin')
# 定义表头边框样式，有底边和右边
header_border = Border(bottom=side, right=side)
# 定义表中、表尾边框样式，有左边
content_border = Border(left=side)
# 设置文件夹路径
path = './各部门利润表汇总/'
# 返回当前目录下所有文件名
files = os.listdir(path)
# 循环文件名列表
for file in files:
# 拼接文件路径
file_path = path + file
# 打开工作簿
wb = load_workbook(file_path)
# 打开工作表
from PyQt5.QtWidgets import QFileDialog, QDialog
from PyQt5 import QtCore
from openpyxl import Workbook
from openpyxl.styles import Font, PatternFill, Border, Side
from openpyxl.styles.numbers import FORMAT_PERCENTAGE_00, FORMAT_CURRENCY_USD_SIMPLE
from StatisticsGenerator import StatisticsGenerator
FONT = Font(name='Calibri', size=12, bold=True)
FILL = PatternFill(fgColor='eeeeee')
DOUBLE_LINE = Side(border_style="double", color="000000")
BORDER = Border(bottom=DOUBLE_LINE)
VERT_BORDER = Border(right=DOUBLE_LINE)
class ExcelReportGenerator:
def __init__(self, data_store):
self._data_store = data_store
self._statistics_generator = StatisticsGenerator.create_with_data(
self._data_store)
self._workbook: Workbook = None
def generate(self):
dialog = QFileDialog()
dialog.setDefaultSuffix('xlsx')
path, ok = QFileDialog.getSaveFileName(None,
"Select Directory",
directory="Report.xlsx",
filter="Excel (*.xlsx)")
print(path)
if path != "":
def SetFormat(self,row,col,fmt):
pyws = self.pyws
font = None
color = None
align = None
fill = None
numFmt = None
border = None
c = pyws.cell(row=row,column=col)
#-------------------------------------------------------------------------
for i in fmt:
if (i == 'hAlign'):
if (not align): align = Alignment()
align.horizontal = alignType[fmt[i]]
elif (i == 'vAlign'):
if (not align): align = Alignment()
align.vertical = alignType[fmt[i]]
elif (i == 'tAlign'):
if (not align): align = Alignment()
align.text_rotation = fmt[i]
elif (i == 'wrap'):
if (not align): align = Alignment()
align.wrap_text = fmt[i]
elif (i == 'font'):
name = 'Calibri'
bold = False
size = 11
dict = fmt[i]
if ('emph' in dict):
if (dict['emph'] == 'B'):
bold = True
if ('size' in dict):
size = dict['size']
if (not font):
font = Font(name=name,size=size,bold=bold)
elif (i == 'border'):
dict = fmt[i]
color = None
style = None
if ('Color' in dict):
color = ColorTable[dict['Color']]
else:
color = ColorTable['Black']
if ('Style' in dict):
color = dict['Style']
if (c.border.top.style == None):
tSide = Side(color=color)
else:
tSide = c.border.top.copy()
if (c.border.bottom.style == None):
bSide = Side(color=color)
else:
bSide = c.border.bottom.copy()
if (c.border.left.style == None):
lSide = Side(color=color)
else:
lSide = c.border.left.copy()
if (c.border.right.style == None):
rSide = Side(color=color)
else:
rSide = c.border.right.copy()
if ((len(dict) ==1) and ('A' in dict)):
tSide.style = dict['A']
bSide.style = dict['A']
lSide.style = dict['A']
rSide.style = dict['A']
else:
for j in dict:
if (j == 'T'):
tSide.style = dict[j]
if (j == 'B'):
bSide.style = dict[j]
if (j == 'L'):
lSide.style = dict[j]
if (j == 'R'):
rSide.style = dict[j]
border = Border(left=lSide,right=rSide,top=tSide,bottom=bSide)
elif (i == 'fill'):
color = ColorTable[fmt[i]]
fill = PatternFill(start_color=color,end_color='FFFFFFFF',fill_type='solid')
elif (i == 'orient'):
pass
elif (i == 'numFmt'):
numFmt = fmt[i]
#-------------------------------------------------------------------------
if (font):
c.font = font.copy()
if (align):
c.alignment = align.copy()
if (border):
c.border = border.copy()
if (fill):
c.fill = fill.copy()
if (numFmt):
c.number_format = numFmt
write_wb = Workbook()
# 이름이 있는 시트를 생성
write_ws = write_wb.create_sheet('Sheet1')
# Sheet1에다 입력
write_ws = write_wb.active
grayFill = PatternFill(start_color='c0c0c0',
end_color='c0c0c0',
fill_type='solid')
box = Border(left=Side(border_style="thin", color='FF000000'),
right=Side(border_style="thin", color='FF000000'),
top=Side(border_style="thin", color='FF000000'),
bottom=Side(border_style="thin", color='FF000000'),
diagonal=Side(border_style="thin", color='FF000000'),
diagonal_direction=0,
outline=Side(border_style="thin", color='FF000000'),
vertical=Side(border_style="thin", color='FF000000'),
horizontal=Side(border_style="thin", color='FF000000'))
# Oracle 서버와 연결(Connection 맺기)
conn = cx_Oracle.connect(
'[User_name]/[Password]@[Server IP]:[Port]/[Service Name]')
cursor = conn.cursor() # cursor 객체 얻어오기
cursor.execute("""
SELECT
A.OWNER, A.table_name, B.comments, A.NUM_ROWS
FROM
all_tables A, all_tab_comments B
WHERE
def flagCell(self,cell): side = Side(style='medium',color=RED) border = Border(left=side,right=side,top=side,bottom=side) #style = Style(border=border) #cell.style = style cell.border = border.copy()
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
#
# for i in range(2, 37 + 1):
# ws.row_dimensions[i].height = 30
#
# for i in range(38, 90 + 1):
# ws.row_dimensions[i].height = 30
# 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=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)
c_r_alignment = Alignment(vertical='bottom',
horizontal='center',
text_rotation=0,
wrap_text=True,
shrink_to_fit=False,
indent=0)
# Img
img = Image("excelexporters/myems.png")
img.width = img.width * 0.85
img.height = img.height * 0.85
# img = Image("myems.png")
ws.add_image(img, 'B1')
# Title
ws.row_dimensions[3].height = 60
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~10 table_data
# Total: 5 rows
# if has not energy data: set low height for rows
#################################################
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
if has_energy_data_flag:
ws['B6'].font = title_font
ws['B6'] = name+' 能耗分析'
category = reporting_period_data['names']
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'] = '能耗'
ws['B8'].border = f_border
ws['B9'].font = title_font
ws['B9'].alignment = c_c_alignment
ws['B9'] = '单位面积能耗'
ws['B9'].border = f_border
ws['B10'].font = title_font
ws['B10'].alignment = c_c_alignment
ws['B10'] = '环比'
ws['B10'].border = f_border
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'] = reporting_period_data['names'][i] + " (" + reporting_period_data['units'][i] + ")"
ws[col + '7'].border = f_border
ws[col + '8'].font = name_font
ws[col + '8'].alignment = c_c_alignment
ws[col + '8'] = round(reporting_period_data['subtotals'][i], 2)
ws[col + '8'].border = f_border
ws[col + '9'].font = name_font
ws[col + '9'].alignment = c_c_alignment
ws[col + '9'] = round(reporting_period_data['subtotals_per_unit_area'][i], 2)
ws[col + '9'].border = f_border
ws[col + '10'].font = name_font
ws[col + '10'].alignment = c_c_alignment
ws[col + '10'] = str(round(reporting_period_data['increment_rates'][i] * 100, 2)) + "%" \
if reporting_period_data['increment_rates'][i] is not None else "-"
ws[col + '10'].border = f_border
# TCE TCO2E
end_col = col
# TCE
tce_col = chr(ord(end_col) + 1)
ws[tce_col + '7'].fill = table_fill
ws[tce_col + '7'].font = name_font
ws[tce_col + '7'].alignment = c_c_alignment
ws[tce_col + '7'] = "吨标准煤 (TCE)"
ws[tce_col + '7'].border = f_border
ws[tce_col + '8'].font = name_font
ws[tce_col + '8'].alignment = c_c_alignment
ws[tce_col + '8'] = round(reporting_period_data['total_in_kgce'] / 1000, 2)
ws[tce_col + '8'].border = f_border
ws[tce_col + '9'].font = name_font
ws[tce_col + '9'].alignment = c_c_alignment
ws[tce_col + '9'] = round(reporting_period_data['total_in_kgce_per_unit_area'] / 1000, 2)
ws[tce_col + '9'].border = f_border
ws[tce_col + '10'].font = name_font
ws[tce_col + '10'].alignment = c_c_alignment
ws[tce_col + '10'] = str(round(reporting_period_data['increment_rate_in_kgce'] * 100, 2)) + "%" \
if reporting_period_data['increment_rate_in_kgce'] is not None else "-"
ws[tce_col + '10'].border = f_border
# TCO2E
tco2e_col = chr(ord(end_col) + 2)
ws[tco2e_col + '7'].fill = table_fill
ws[tco2e_col + '7'].font = name_font
ws[tco2e_col + '7'].alignment = c_c_alignment
ws[tco2e_col + '7'] = "吨二氧化碳排放 (TCO2E)"
ws[tco2e_col + '7'].border = f_border
ws[tco2e_col + '8'].font = name_font
ws[tco2e_col + '8'].alignment = c_c_alignment
ws[tco2e_col + '8'] = round(reporting_period_data['total_in_kgco2e'] / 1000, 2)
ws[tco2e_col + '8'].border = f_border
ws[tco2e_col + '9'].font = name_font
ws[tco2e_col + '9'].alignment = c_c_alignment
ws[tco2e_col + '9'] = round(reporting_period_data['total_in_kgco2e_per_unit_area'] / 1000, 2)
ws[tco2e_col + '9'].border = f_border
ws[tco2e_col + '10'].font = name_font
ws[tco2e_col + '10'].alignment = c_c_alignment
ws[tco2e_col + '10'] = str(round(reporting_period_data['increment_rate_in_kgco2e'] * 100, 2)) + "%" \
if reporting_period_data['increment_rate_in_kgco2e'] is not None else "-"
ws[tco2e_col + '10'].border = f_border
else:
for i in range(6, 10 + 1):
ws.row_dimensions[i].height = 0.1
#################################################
# Second: 分时电耗
# 12: title
# 13: table title
# 14~17 table_data
# Total: 6 rows
################################################
has_ele_peak_flag = True
if "toppeaks" not in reporting_period_data.keys() or \
reporting_period_data['toppeaks'] is None or \
len(reporting_period_data['toppeaks']) == 0:
has_ele_peak_flag = False
if has_ele_peak_flag:
ws['B12'].font = title_font
ws['B12'] = name+' 分时电耗'
ws.row_dimensions[13].height = 60
ws['B13'].fill = table_fill
ws['B13'].font = name_font
ws['B13'].alignment = c_c_alignment
ws['B13'].border = f_border
ws['C13'].fill = table_fill
ws['C13'].font = name_font
ws['C13'].alignment = c_c_alignment
ws['C13'].border = f_border
ws['C13'] = '分时电耗'
ws['B14'].font = title_font
ws['B14'].alignment = c_c_alignment
ws['B14'] = '尖'
ws['B14'].border = f_border
ws['C14'].font = title_font
ws['C14'].alignment = c_c_alignment
ws['C14'].border = f_border
ws['C14'] = round(reporting_period_data['toppeaks'][0], 2)
ws['B15'].font = title_font
ws['B15'].alignment = c_c_alignment
ws['B15'] = '峰'
ws['B15'].border = f_border
ws['C15'].font = title_font
ws['C15'].alignment = c_c_alignment
ws['C15'].border = f_border
ws['C15'] = round(reporting_period_data['onpeaks'][0], 2)
ws['B16'].font = title_font
ws['B16'].alignment = c_c_alignment
ws['B16'] = '平'
ws['B16'].border = f_border
ws['C16'].font = title_font
ws['C16'].alignment = c_c_alignment
ws['C16'].border = f_border
ws['C16'] = round(reporting_period_data['midpeaks'][0], 2)
ws['B17'].font = title_font
ws['B17'].alignment = c_c_alignment
ws['B17'] = '谷'
ws['B17'].border = f_border
ws['C17'].font = title_font
ws['C17'].alignment = c_c_alignment
ws['C17'].border = f_border
ws['C17'] = round(reporting_period_data['offpeaks'][0], 2)
pie = PieChart()
pie.title = name+' 分时电耗'
labels = Reference(ws, min_col=2, min_row=14, max_row=17)
pie_data = Reference(ws, min_col=3, min_row=13, max_row=17)
pie.add_data(pie_data, titles_from_data=True)
pie.set_categories(labels)
pie.height = 7.25 # cm 1.05*5 1.05cm = 30 pt
pie.width = 9
# pie.title = "Pies sold by category"
s1 = pie.series[0]
s1.dLbls = DataLabelList()
s1.dLbls.showCatName = False # 标签显示
s1.dLbls.showVal = True # 数量显示
s1.dLbls.showPercent = True # 百分比显示
# s1 = CharacterProperties(sz=1800) # 图表中字体大小 *100
ws.add_chart(pie, "D13")
else:
for i in range(12, 18 + 1):
ws.row_dimensions[i].height = 0.1
# end_row 10
# start_row 12
################################################
# Third: 子空间能耗
# 19: title
# 20: table title
# 21~24 table_data
# Total: 6 rows
################################################
has_child_flag = True
# Judge if the space has child space, if not, delete it.
if "child_space" not in report.keys() or "energy_category_names" not in report['child_space'].keys() or \
len(report['child_space']["energy_category_names"]) == 0:
has_child_flag = False
if has_child_flag:
child = report['child_space']
child_spaces = child['child_space_names_array'][0]
child_subtotals = child['subtotals_array'][0]
ws['B19'].font = title_font
ws['B19'] = name+' 子空间能耗'
ws.row_dimensions[20].height = 60
ws['B20'].fill = table_fill
ws['B20'].border = f_border
ca_len = len(child['energy_category_names'])
for i in range(0, ca_len):
row = chr(ord('C') + i)
ws[row + '20'].fill = table_fill
ws[row + '20'].font = title_font
ws[row + '20'].alignment = c_c_alignment
ws[row + '20'].border = f_border
ws[row + '20'] = child['energy_category_names'][i] + ' (' + child['units'][i] + ')'
space_len = len(child['child_space_names_array'][0])
for i in range(0, space_len):
row = str(i + 21)
ws['B' + row].font = name_font
ws['B' + row].alignment = c_c_alignment
ws['B' + row] = child['child_space_names_array'][0][i]
ws['B' + row].border = f_border
for j in range(0, ca_len):
col = chr(ord('C') + j)
ws[col + row].font = name_font
ws[col + row].alignment = c_c_alignment
ws[col + row] = round(child['subtotals_array'][j][i], 2)
ws[col + row].border = f_border
# pie
# 25~30: pie
pie = PieChart()
pie.title = ws.cell(column=3 + j, row=20).value
labels = Reference(ws, min_col=2, min_row=21, max_row=24)
pie_data = Reference(ws, min_col=3 + j, min_row=20, max_row=24)
pie.add_data(pie_data, titles_from_data=True)
pie.set_categories(labels)
pie.height = 6.6 # cm 1.05*5 1.05cm = 30 pt
pie.width = 8
# pie.title = "Pies sold by category"
s1 = pie.series[0]
s1.dLbls = DataLabelList()
s1.dLbls.showCatName = True # 标签显示
s1.dLbls.showVal = True # 数量显示
s1.dLbls.showPercent = True # 百分比显示
# s1 = CharacterProperties(sz=1800) # 图表中字体大小 *100
chart_col = chr(ord('B') + 2 * j)
chart_cell = chart_col + '25'
ws.add_chart(pie, chart_cell)
else:
for i in range(19, 36 + 1):
ws.row_dimensions[i].height = 0.1
for i in range(30, 35 + 1):
ws.row_dimensions[i].height = 0.1
################################################
# Fourth: 能耗详情
# 37: title
# 38~ 38+ca_len*6-1: line
# 38+ca_len*6: table title
# 38+ca_len*6~: table_data
################################################
reporting_period_data = report['reporting_period']
times = reporting_period_data['timestamps']
has_detail_data_flag = True
ca_len = len(report['reporting_period']['names'])
table_row = 38 + ca_len*6
if "timestamps" not in reporting_period_data.keys() or \
reporting_period_data['timestamps'] is None or \
len(reporting_period_data['timestamps']) == 0:
has_detail_data_flag = False
if has_detail_data_flag:
ws['B37'].font = title_font
ws['B37'] = name+' 能耗详情'
ws.row_dimensions[table_row].height = 60
ws['B'+str(table_row)].fill = table_fill
ws['B' + str(table_row)].font = title_font
ws['B'+str(table_row)].border = f_border
ws['B'+str(table_row)].alignment = c_c_alignment
ws['B'+str(table_row)] = '时间'
time = times[0]
has_data = False
max_row = 0
if len(time) > 0:
has_data = True
max_row = table_row + len(time)
print("max_row", max_row)
if has_data:
for i in range(0, len(time)):
col = 'B'
row = str(table_row+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):
# 38 title
col = chr(ord('C') + i)
ws[col + str(table_row)].fill = table_fill
ws[col + str(table_row)].font = title_font
ws[col + str(table_row)].alignment = c_c_alignment
ws[col + str(table_row)] = reporting_period_data['names'][i] + \
" (" + reporting_period_data['units'][i] + ")"
ws[col + str(table_row)].border = f_border
# 39 data
time = times[i]
time_len = len(time)
for j in range(0, time_len):
row = str(table_row+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_data['values'][i][j], 2)
ws[col + row].border = f_border
# line
# 39~: line
line = LineChart()
line.title = '报告期消耗 - '
labels = Reference(ws, min_col=2, min_row=table_row+1, max_row=max_row + 1)
line_data = Reference(ws, min_col=3 + i, min_row=table_row, max_row=max_row + 1) # openpyxl bug
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 = False
line.height = 8.25 # cm 1.05*5 1.05cm = 30 pt
line.width = 24
# pie.title = "Pies sold by category"
line.dLbls = DataLabelList()
# line.dLbls.showCatName = True # label show
line.dLbls.showVal = True # val show
line.dLbls.showPercent = True # percent show
# s1 = CharacterProperties(sz=1800) # font size *100
chart_col = 'B'
chart_cell = chart_col + str(38 + 6*i)
ws.add_chart(line, chart_cell)
else:
for i in range(37, 69 + 1):
ws.row_dimensions[i].height = 0.1
filename = str(uuid.uuid4()) + '.xlsx'
wb.save(filename)
return filename
# The following are the default values >>> from openpyxl.styles import PatternFill, Border, Side, Alignment, Protection, Font >>> font = Font(name='Calibri', ... size=11, ... bold=False, ... italic=False, ... vertAlign=None, ... underline='none', ... strike=False, ... color='FF000000') >>> fill = PatternFill(fill_type=None, ... start_color='FFFFFFFF', ... end_color='FF000000') >>> border = Border(left=Side(border_style=None, ... color='FF000000'), ... right=Side(border_style=None, ... color='FF000000'), ... top=Side(border_style=None, ... color='FF000000'), ... bottom=Side(border_style=None, ... color='FF000000'), ... diagonal=Side(border_style=None, ... color='FF000000'), ... diagonal_direction=0, ... outline=Side(border_style=None, ... color='FF000000'), ... vertical=Side(border_style=None, ... color='FF000000'), ... horizontal=Side(border_style=None, ... color='FF000000')
bold=False,
italic=False,
vertAlign=None,
underline='none',
strike=False,
color='9C0006')
fillGreen = PatternFill("solid", fgColor="1F497D")
greenFont = Font(name='Calibri',
size=11,
bold=True,
italic=False,
vertAlign=None,
underline='none',
strike=False,
color='FFFFFF')
border = Border(top=thin, left=thin, right=thin, bottom=thin)
'''
top = Border(top=border.top)
left = Border(left=border.left)
right = Border(right=border.right)
bottom = Border(bottom=border.bottom)
'''
# BIM Style
def BIMStyle(cell, Fill, Font):
cell.fill = Fill
cell.font = Font
# CATEGORY IN WORKSETS CONTROL
def FormattingCategory(workSheets):
wsSix = workSheets
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
# for i in range(2, 37 + 1):
# ws.row_dimensions[i].height = 30
#
# for i in range(38, 90 + 1):
# ws.row_dimensions[i].height = 30
# 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=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)
c_r_alignment = Alignment(vertical='bottom',
horizontal='center',
text_rotation=0,
wrap_text=True,
shrink_to_fit=False,
indent=0)
# Img
img = Image("excelexporters/myems.png")
img.width = img.width * 0.85
img.height = img.height * 0.85
# img = Image("myems.png")
ws.add_image(img, 'B1')
# Title
ws.row_dimensions[3].height = 60
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'].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
#################################################
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
if has_energy_data_flag:
ws['B6'].font = title_font
ws['B6'] = name + ' 报告期成本'
category = reporting_period_data['names']
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'] = '成本'
ws['B8'].border = f_border
ws['B9'].font = title_font
ws['B9'].alignment = c_c_alignment
ws['B9'] = '单位面积能耗'
ws['B9'].border = f_border
ws['B10'].font = title_font
ws['B10'].alignment = c_c_alignment
ws['B10'] = '环比'
ws['B10'].border = f_border
col = 'B'
for i in range(0, ca_len):
col = chr(ord('C') + i)
ws[col + '7'].fill = table_fill
ws[col + '7'].font = name_font
ws[col + '7'].alignment = c_c_alignment
ws[col + '7'] = reporting_period_data['names'][
i] + " (" + reporting_period_data['units'][i] + ")"
ws[col + '7'].border = f_border
ws[col + '8'].font = name_font
ws[col + '8'].alignment = c_c_alignment
ws[col + '8'] = round(reporting_period_data['subtotals'][i], 2)
ws[col + '8'].border = f_border
ws[col + '9'].font = name_font
ws[col + '9'].alignment = c_c_alignment
ws[col + '9'] = round(
reporting_period_data['subtotals_per_unit_area'][i], 2)
ws[col + '9'].border = f_border
ws[col + '10'].font = name_font
ws[col + '10'].alignment = c_c_alignment
ws[col + '10'] = str(round(reporting_period_data['increment_rates'][i] * 100, 2)) + "%" \
if reporting_period_data['increment_rates'][i] is not None else "-"
ws[col + '10'].border = f_border
end_col = chr(ord(col) + 1)
ws[end_col + '7'].fill = table_fill
ws[end_col + '7'].font = name_font
ws[end_col + '7'].alignment = c_c_alignment
ws[end_col + '7'] = "总计 (" + reporting_period_data['total_unit'] + ")"
ws[end_col + '7'].border = f_border
ws[end_col + '8'].font = name_font
ws[end_col + '8'].alignment = c_c_alignment
ws[end_col + '8'] = round(reporting_period_data['total'], 2)
ws[end_col + '8'].border = f_border
ws[end_col + '9'].font = name_font
ws[end_col + '9'].alignment = c_c_alignment
ws[end_col + '9'] = round(reporting_period_data['total_per_unit_area'],
2)
ws[end_col + '9'].border = f_border
ws[end_col + '10'].font = name_font
ws[end_col + '10'].alignment = c_c_alignment
ws[end_col + '10'] = str(round(reporting_period_data['total_increment_rate'] * 100, 2)) + "%" \
if reporting_period_data['total_increment_rate'] is not None else "-"
ws[end_col + '10'].border = f_border
else:
for i in range(6, 10 + 1):
ws.row_dimensions[i].height = 0.1
#################################################
has_ele_peak_flag = True
if "toppeaks" not in reporting_period_data.keys() or \
reporting_period_data['toppeaks'] is None or \
len(reporting_period_data['toppeaks']) == 0:
has_ele_peak_flag = False
if has_ele_peak_flag:
ws['B12'].font = title_font
ws['B12'] = name + ' 分时电耗'
ws['B13'].fill = table_fill
ws['B13'].font = name_font
ws['B13'].alignment = c_c_alignment
ws['B13'].border = f_border
ws['C13'].fill = table_fill
ws['C13'].font = name_font
ws['C13'].alignment = c_c_alignment
ws['C13'].border = f_border
ws['C13'] = '分时电耗'
ws['B14'].font = title_font
ws['B14'].alignment = c_c_alignment
ws['B14'] = '尖'
ws['B14'].border = f_border
ws['C14'].font = title_font
ws['C14'].alignment = c_c_alignment
ws['C14'].border = f_border
ws['C14'] = round(reporting_period_data['toppeaks'][0], 2)
ws['B15'].font = title_font
ws['B15'].alignment = c_c_alignment
ws['B15'] = '峰'
ws['B15'].border = f_border
ws['C15'].font = title_font
ws['C15'].alignment = c_c_alignment
ws['C15'].border = f_border
ws['C15'] = round(reporting_period_data['onpeaks'][0], 2)
ws['B16'].font = title_font
ws['B16'].alignment = c_c_alignment
ws['B16'] = '平'
ws['B16'].border = f_border
ws['C16'].font = title_font
ws['C16'].alignment = c_c_alignment
ws['C16'].border = f_border
ws['C16'] = round(reporting_period_data['midpeaks'][0], 2)
ws['B17'].font = title_font
ws['B17'].alignment = c_c_alignment
ws['B17'] = '谷'
ws['B17'].border = f_border
ws['C17'].font = title_font
ws['C17'].alignment = c_c_alignment
ws['C17'].border = f_border
ws['C17'] = round(reporting_period_data['offpeaks'][0], 2)
pie = PieChart()
pie.title = name + ' 分时电耗'
labels = Reference(ws, min_col=2, min_row=14, max_row=17)
pie_data = Reference(ws, min_col=3, min_row=13, max_row=17)
pie.add_data(pie_data, titles_from_data=True)
pie.set_categories(labels)
pie.height = 6.6
pie.width = 9
s1 = pie.series[0]
s1.dLbls = DataLabelList()
s1.dLbls.showCatName = False
s1.dLbls.showVal = True
s1.dLbls.showPercent = True
ws.add_chart(pie, "D13")
else:
for i in range(12, 18 + 1):
ws.row_dimensions[i].height = 0.1
################################################
current_row_number = 19
has_subtotals_data_flag = True
if "subtotals" not in reporting_period_data.keys() or \
reporting_period_data['subtotals'] is None or \
len(reporting_period_data['subtotals']) == 0:
has_subtotals_data_flag = False
if has_subtotals_data_flag:
ws['B' + str(current_row_number)].font = title_font
ws['B' + str(current_row_number)] = name + ' 成本占比'
current_row_number += 1
table_start_row_number = current_row_number
ws['B' + str(current_row_number)].fill = table_fill
ws['B' + str(current_row_number)].font = name_font
ws['B' + str(current_row_number)].alignment = c_c_alignment
ws['B' + str(current_row_number)].border = f_border
ws['C' + str(current_row_number)].fill = table_fill
ws['C' + str(current_row_number)].font = name_font
ws['C' + str(current_row_number)].alignment = c_c_alignment
ws['C' + str(current_row_number)].border = f_border
ws['C' + str(current_row_number)] = '成本占比'
current_row_number += 1
ca_len = len(reporting_period_data['names'])
for i in range(0, ca_len):
ws['B' + str(current_row_number)].font = title_font
ws['B' + str(current_row_number)].alignment = c_c_alignment
ws['B' +
str(current_row_number)] = reporting_period_data['names'][i]
ws['B' + str(current_row_number)].border = f_border
ws['C' + str(current_row_number)].font = title_font
ws['C' + str(current_row_number)].alignment = c_c_alignment
ws['C' + str(current_row_number)].border = f_border
ws['C' + str(current_row_number)] = round(
reporting_period_data['subtotals'][i], 2)
current_row_number += 1
table_end_row_number = current_row_number - 1
pie = PieChart()
pie.title = name + ' 成本占比'
labels = Reference(ws,
min_col=2,
min_row=table_start_row_number + 1,
max_row=table_end_row_number)
pie_data = Reference(ws,
min_col=3,
min_row=table_start_row_number,
max_row=table_end_row_number)
pie.add_data(pie_data, titles_from_data=True)
pie.set_categories(labels)
pie.height = 6.6
pie.width = 9
s1 = pie.series[0]
s1.dLbls = DataLabelList()
s1.dLbls.showCatName = False
s1.dLbls.showVal = True
s1.dLbls.showPercent = True
table_cell = 'D' + str(table_start_row_number)
ws.add_chart(pie, table_cell)
if ca_len < 4:
current_row_number = current_row_number - ca_len + 4
else:
for i in range(21, 29 + 1):
current_row_number = 30
ws.row_dimensions[i].height = 0.1
###############################################
current_row_number += 1
has_detail_data_flag = True
table_start_draw_flag = current_row_number + 1
if "timestamps" not in reporting_period_data.keys() or \
reporting_period_data['timestamps'] is None or \
len(reporting_period_data['timestamps']) == 0:
has_detail_data_flag = False
if has_detail_data_flag:
reporting_period_data = report['reporting_period']
times = reporting_period_data['timestamps']
ca_len = len(report['reporting_period']['names'])
ws['B' + str(current_row_number)].font = title_font
ws['B' + str(current_row_number)] = name + ' 详细数据'
table_start_row_number = (current_row_number + 1) + ca_len * 6
current_row_number = table_start_row_number
time = times[0]
has_data = False
if len(time) > 0:
has_data = True
if has_data:
ws.row_dimensions[current_row_number].height = 60
ws['B' + str(current_row_number)].fill = table_fill
ws['B' + str(current_row_number)].border = f_border
ws['B' + str(current_row_number)].font = title_font
ws['B' + str(current_row_number)].alignment = c_c_alignment
ws['B' + str(current_row_number)] = '日期时间'
col = 'B'
for i in range(0, ca_len):
col = chr(ord('C') + i)
ws[col + str(current_row_number)].fill = table_fill
ws[col + str(current_row_number)].font = title_font
ws[col + str(current_row_number)].alignment = c_c_alignment
ws[col + str(current_row_number)] = reporting_period_data['names'][i] + \
" (" + reporting_period_data['units'][i] + ")"
ws[col + str(current_row_number)].border = f_border
end_col = chr(ord(col) + 1)
ws[end_col + str(current_row_number)].fill = table_fill
ws[end_col + str(current_row_number)].font = title_font
ws[end_col + str(current_row_number)].alignment = c_c_alignment
ws[end_col +
str(current_row_number
)] = "总计 (" + reporting_period_data['total_unit'] + ")"
ws[end_col + str(current_row_number)].border = f_border
current_row_number += 1
for i in range(0, len(time)):
ws['B' + str(current_row_number)].font = title_font
ws['B' + str(current_row_number)].alignment = c_c_alignment
ws['B' + str(current_row_number)] = time[i]
ws['B' + str(current_row_number)].border = f_border
col = 'B'
every_day_total = 0
for j in range(0, ca_len):
col = chr(ord('C') + j)
ws[col + str(current_row_number)].font = title_font
ws[col + str(current_row_number)].alignment = c_c_alignment
value = round(reporting_period_data['values'][j][i], 2)
every_day_total += value
ws[col + str(current_row_number)] = value
ws[col + str(current_row_number)].border = f_border
end_col = chr(ord(col) + 1)
ws[end_col + str(current_row_number)].font = title_font
ws[end_col + str(current_row_number)].alignment = c_c_alignment
ws[end_col + str(current_row_number)] = round(
every_day_total, 2)
ws[end_col + str(current_row_number)].border = f_border
current_row_number += 1
table_end_row_number = current_row_number - 1
ws['B' + str(current_row_number)].font = title_font
ws['B' + str(current_row_number)].alignment = c_c_alignment
ws['B' + str(current_row_number)] = '小计'
ws['B' + str(current_row_number)].border = f_border
col = 'B'
for i in range(0, ca_len):
col = chr(ord('C') + i)
ws[col + str(current_row_number)].font = title_font
ws[col + str(current_row_number)].alignment = c_c_alignment
ws[col + str(current_row_number)] = round(
reporting_period_data['subtotals'][i], 2)
ws[col + str(current_row_number)].border = f_border
# line
line = LineChart()
line.title = '报告期成本 - ' + ws.cell(
column=3 + i, row=table_start_row_number).value
labels = Reference(ws,
min_col=2,
min_row=table_start_row_number + 1,
max_row=table_end_row_number)
line_data = Reference(ws,
min_col=3 + i,
min_row=table_start_row_number,
max_row=table_end_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 = True
line.dLbls.showPercent = False
chart_col = 'B'
chart_cell = chart_col + str(table_start_draw_flag + 6 * i)
ws.add_chart(line, chart_cell)
end_col = chr(ord(col) + 1)
ws[end_col + str(current_row_number)].font = title_font
ws[end_col + str(current_row_number)].alignment = c_c_alignment
ws[end_col + str(current_row_number)] = round(
reporting_period_data['total'], 2)
ws[end_col + str(current_row_number)].border = f_border
current_row_number += 1
else:
for i in range(30, 69 + 1):
current_row_number = 70
ws.row_dimensions[i].height = 0.1
filename = str(uuid.uuid4()) + '.xlsx'
wb.save(filename)
return filename
def render_xlsx(self, outfd, data):
BoldStyle = Style(font=Font(name='Calibri',
size=11,
bold=True,
italic=False,
vertAlign=None,
underline='none',
strike=False,
color='FFFFFFFF'),
fill=PatternFill(fill_type="solid",
start_color='FF000000',
end_color='FF000000'))
RedStyle = Style(font=Font(name='Calibri',
size=11,
bold=False,
italic=False,
vertAlign=None,
underline='none',
strike=False,
color='FF000000'),
border=Border(left=Side(border_style="thick",
color='FF000000'),
right=Side(border_style="thick",
color='FF000000'),
top=Side(border_style="thick",
color='FF000000'),
bottom=Side(border_style="thick",
color='FF000000'),
diagonal=Side(border_style="thick",
color='FF000000'),
diagonal_direction=0,
outline=Side(border_style="thick",
color='FF000000'),
vertical=Side(border_style="thick",
color='FF000000'),
horizontal=Side(border_style="thick",
color='FF000000')),
fill=PatternFill(start_color='FFFF0000',
end_color='FFFF0000',
fill_type='solid'))
GreenStyle = Style(font=Font(name='Calibri',
size=11,
bold=False,
italic=False,
vertAlign=None,
underline='none',
strike=False,
color='FF000000'),
fill=PatternFill(start_color="FF00FF00",
end_color="FF00FF00",
fill_type="solid"))
wb = Workbook(optimized_write=True)
ws = wb.create_sheet()
ws.title = "Psxview Output"
ws.append([
"Offset (P)", "Name", "PID", "pslist", "psscan", "thrdproc",
"pspcid", "csrss", "session", "deskthrd", "Exit Time"
])
total = 1
for offset, process, ps_sources in data:
incsrss = ps_sources['csrss'].has_key(offset)
insession = ps_sources['session'].has_key(offset)
indesktop = ps_sources['deskthrd'].has_key(offset)
inpspcid = ps_sources['pspcid'].has_key(offset)
inpslist = ps_sources['pslist'].has_key(offset)
inthread = ps_sources['thrdproc'].has_key(offset)
if self._config.APPLY_RULES:
if not incsrss:
if str(process.ImageFileName).lower() in [
"system", "smss.exe", "csrss.exe"
]:
incsrss = "Okay"
elif process.ExitTime > 0:
incsrss = "Okay"
if not insession:
if str(process.ImageFileName).lower() in [
"system", "smss.exe"
]:
insession = "Okay"
elif process.ExitTime > 0:
insession = "Okay"
if not indesktop:
if str(process.ImageFileName).lower() in [
"system", "smss.exe"
]:
indesktop = "Okay"
elif process.ExitTime > 0:
indesktop = "Okay"
if not inpspcid:
if process.ExitTime > 0:
inpspcid = "Okay"
if not inpslist:
if process.ExitTime > 0:
inpslist = "Okay"
if not inthread:
if process.ExitTime > 0:
inthread = "Okay"
ws.append([
hex(offset),
str(
utils.remove_unprintable(str(process.ImageFileName))
or ""),
str(process.UniqueProcessId),
str(inpslist),
str(ps_sources['psscan'].has_key(offset)),
str(inthread),
str(inpspcid),
str(incsrss),
str(insession),
str(indesktop),
str(process.ExitTime or '')
])
total += 1
wb.save(filename=self._config.OUTPUT_FILE)
wb = load_workbook(filename=self._config.OUTPUT_FILE)
ws = wb.get_sheet_by_name(name="Psxview Output")
for col in xrange(1, 12):
ws.cell("{0}{1}".format(get_column_letter(col),
1)).style = BoldStyle
for row in xrange(2, total + 1):
for col in xrange(4, 11):
if ws.cell("{0}{1}".format(get_column_letter(col),
row)).value == "False":
ws.cell("{0}{1}".format(get_column_letter(col),
row)).style = RedStyle
else:
ws.cell("{0}{1}".format(get_column_letter(col),
row)).style = GreenStyle
wb.save(filename=self._config.OUTPUT_FILE)
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=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)
c_r_alignment = Alignment(vertical='bottom',
horizontal='center',
text_rotation=0,
wrap_text=True,
shrink_to_fit=False,
indent=0)
# Img
img = Image("excelexporters/myems.png")
img.width = img.width * 0.85
img.height = img.height * 0.85
# img = Image("myems.png")
ws.add_image(img, 'B1')
# Title
ws.row_dimensions[3].height = 60
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
##################################
current_row_number = 6
reporting_period_data = report['reporting_period']
has_names_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_names_data_flag = False
if has_names_data_flag:
ws['B' + str(current_row_number)].font = title_font
ws['B' + str(current_row_number)] = name + ' 报告期产出'
current_row_number += 1
category = reporting_period_data['names']
ca_len = len(category)
ws.row_dimensions[current_row_number].height = 60
ws['B' + str(current_row_number)].fill = table_fill
ws['B' + str(current_row_number)].border = f_border
col = 'C'
for i in range(0, ca_len):
ws[col + str(current_row_number)].fill = table_fill
ws[col + str(current_row_number)].font = name_font
ws[col + str(current_row_number)].alignment = c_c_alignment
ws[col + str(current_row_number)].border = f_border
ws[col + str(current_row_number)] = \
reporting_period_data['names'][i] + " (" + reporting_period_data['units'][i] + ")"
col = chr(ord(col) + 1)
current_row_number += 1
ws['B' + str(current_row_number)].font = title_font
ws['B' + str(current_row_number)].alignment = c_c_alignment
ws['B' + str(current_row_number)].border = f_border
ws['B' + str(current_row_number)] = '产出'
col = 'C'
for i in range(0, ca_len):
ws[col + str(current_row_number)].font = name_font
ws[col + str(current_row_number)].alignment = c_c_alignment
ws[col + str(current_row_number)].border = f_border
ws[col + str(current_row_number)] = round(
reporting_period_data['subtotals'][i], 2)
col = chr(ord(col) + 1)
current_row_number += 1
ws['B' + str(current_row_number)].font = title_font
ws['B' + str(current_row_number)].alignment = c_c_alignment
ws['B' + str(current_row_number)].border = f_border
ws['B' + str(current_row_number)] = '环比'
col = 'C'
for i in range(0, ca_len):
ws[col + str(current_row_number)].font = name_font
ws[col + str(current_row_number)].alignment = c_c_alignment
ws[col + str(current_row_number)].border = f_border
ws[col + str(current_row_number)] = str(
round(reporting_period_data['increment_rates'][i] * 100, 2)) + '%' \
if reporting_period_data['increment_rates'][i] is not None else '-'
col = chr(ord(col) + 1)
current_row_number += 2
####################################
has_values_data = True
has_timestamps_data = True
if 'values' not in reporting_period_data.keys() or \
reporting_period_data['values'] is None or \
len(reporting_period_data['values']) == 0:
has_values_data = False
if 'timestamps' not in reporting_period_data.keys() or \
reporting_period_data['timestamps'] is None or \
len(reporting_period_data['timestamps']) == 0 or \
len(reporting_period_data['timestamps'][0]) == 0:
has_timestamps_data = False
if has_values_data and has_timestamps_data:
ca_len = len(reporting_period_data['names'])
time = reporting_period_data['timestamps'][0]
ws['B' + str(current_row_number)].font = title_font
ws['B' + str(current_row_number)] = name + ' 详细数据'
current_row_number += 1
chart_start_row_number = current_row_number
real_timestamps_len = timestamps_data_not_equal_0(
report['parameters']['timestamps'])
current_row_number += ca_len * 6 + real_timestamps_len * 7 + 2
table_start_row_number = current_row_number
ws.row_dimensions[current_row_number].height = 60
ws['B' + str(current_row_number)].fill = table_fill
ws['B' + str(current_row_number)].font = title_font
ws['B' + str(current_row_number)].alignment = c_c_alignment
ws['B' + str(current_row_number)].border = f_border
ws['B' + str(current_row_number)] = '日期时间'
col = 'C'
for i in range(0, ca_len):
ws[col + str(current_row_number)].fill = table_fill
ws[col + str(current_row_number)].font = title_font
ws[col + str(current_row_number)].alignment = c_c_alignment
ws[col + str(current_row_number)].border = f_border
ws[col + str(current_row_number)] = \
reporting_period_data['names'][i] + " (" + reporting_period_data['units'][i] + ")"
col = chr(ord(col) + 1)
current_row_number += 1
for i in range(0, len(time)):
ws['B' + str(current_row_number)].font = title_font
ws['B' + str(current_row_number)].alignment = c_c_alignment
ws['B' + str(current_row_number)].border = f_border
ws['B' + str(current_row_number)] = time[i]
col = 'C'
for j in range(0, ca_len):
ws[col + str(current_row_number)].font = title_font
ws[col + str(current_row_number)].alignment = c_c_alignment
ws[col + str(current_row_number)].border = f_border
ws[col + str(current_row_number)] = round(reporting_period_data['values'][j][i], 2) \
if reporting_period_data['values'][j][i] is not None else 0.00
col = chr(ord(col) + 1)
current_row_number += 1
table_end_row_number = current_row_number - 1
ws['B' + str(current_row_number)].font = title_font
ws['B' + str(current_row_number)].alignment = c_c_alignment
ws['B' + str(current_row_number)].border = f_border
ws['B' + str(current_row_number)] = '小计'
col = 'C'
for i in range(0, ca_len):
ws[col + str(current_row_number)].font = title_font
ws[col + str(current_row_number)].alignment = c_c_alignment
ws[col + str(current_row_number)].border = f_border
ws[col + str(current_row_number)] = round(
reporting_period_data['subtotals'][i], 2)
col = chr(ord(col) + 1)
current_row_number += 2
format_time_width_number = 1.0
min_len_number = 1.0
min_width_number = 11.0 # format_time_width_number * min_len_number + 4 and min_width_number > 11.0
if period_type == 'hourly':
format_time_width_number = 4.0
min_len_number = 2
min_width_number = 12.0
elif period_type == 'daily':
format_time_width_number = 2.5
min_len_number = 4
min_width_number = 14.0
elif period_type == 'monthly':
format_time_width_number = 2.1
min_len_number = 4
min_width_number = 12.4
elif period_type == 'yearly':
format_time_width_number = 1.5
min_len_number = 5
min_width_number = 11.5
for i in range(0, ca_len):
line = LineChart()
line.title = '报告期产出 - ' + \
reporting_period_data['names'][i] + " (" + reporting_period_data['units'][i] + ")"
labels = Reference(ws,
min_col=2,
min_row=table_start_row_number + 1,
max_row=table_end_row_number)
line_data = Reference(ws,
min_col=3 + i,
min_row=table_start_row_number,
max_row=table_end_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 = format_time_width_number * len(time) if len(
time) > min_len_number else min_width_number
if line.width > 24:
line.width = 24
line.dLbls = DataLabelList()
line.dLbls.dLblPos = 't'
line.dLbls.showVal = True
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 + 1
has_parameters_names_and_timestamps_and_values_data = True
if 'parameters' not in report.keys() or \
report['parameters'] is None or \
'names' not in report['parameters'].keys() or \
report['parameters']['names'] is None or \
len(report['parameters']['names']) == 0 or \
'timestamps' not in report['parameters'].keys() or \
report['parameters']['timestamps'] is None or \
len(report['parameters']['timestamps']) == 0 or \
'values' not in report['parameters'].keys() or \
report['parameters']['values'] is None or \
len(report['parameters']['values']) == 0 or \
timestamps_data_all_equal_0(report['parameters']['timestamps']):
has_parameters_names_and_timestamps_and_values_data = False
if has_parameters_names_and_timestamps_and_values_data:
###############################
# new worksheet
###############################
parameters_data = report['parameters']
parameters_names_len = len(parameters_data['names'])
parameters_ws = wb.create_sheet('相关参数')
parameters_timestamps_data_max_len = \
get_parameters_timestamps_lists_max_len(list(parameters_data['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")
img.width = img.width * 0.85
img.height = img.height * 0.85
# img = Image("myems.png")
parameters_ws.add_image(img, 'B1')
# Title
parameters_ws.row_dimensions[3].height = 60
parameters_ws['B3'].font = name_font
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'].font = name_font
parameters_ws['C3'] = name
parameters_ws['D3'].font = name_font
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'].font = name_font
parameters_ws['E3'] = period_type
parameters_ws['F3'].font = name_font
parameters_ws['F3'].alignment = b_r_alignment
parameters_ws['F3'] = 'Date:'
parameters_ws['G3'].border = b_border
parameters_ws['G3'].alignment = b_c_alignment
parameters_ws['G3'].font = name_font
parameters_ws[
'G3'] = reporting_start_datetime_local + "__" + reporting_end_datetime_local
parameters_ws.merge_cells("G3:H3")
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)] = name + ' 相关参数'
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 = 'B'
for i in range(0, parameters_names_len):
if len(parameters_data['timestamps'][i]) == 0:
continue
parameters_ws[table_current_col_number +
str(parameters_ws_current_row_number -
1)].fill = table_fill
parameters_ws[table_current_col_number +
str(parameters_ws_current_row_number -
1)].border = f_border
col = chr(ord(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_data['names'][i]
table_current_row_number = parameters_ws_current_row_number
for j, value in enumerate(list(parameters_data['timestamps'][i])):
col = 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 = chr(ord(col) + 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
parameters_ws[col + str(table_current_row_number)] = round(
parameters_data['values'][i][j], 2)
table_current_row_number += 1
table_current_col_number = chr(ord(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)] = name + ' 相关参数'
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_data['timestamps'][i]) == 0:
continue
line = LineChart()
data_col = 3 + col_index * 3
labels_col = 2 + col_index * 3
col_index += 1
line.title = '相关参数 - ' + \
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_data['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_data['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 parse_borders(self):
"""Read in the boarders"""
borders = self.root.findall('{%s}borders/{%s}border' % (SHEET_MAIN_NS, SHEET_MAIN_NS))
for border_node in borders:
yield Border.from_tree(border_node)
def build_consolidated_bs(wb, dict_db, date_end):
wb_cur = wb
date_prior_ye = (date_end +
relativedelta.relativedelta(months=-date_end.month))
gl_data = retrieve_bs_data(dict_db)
assets = sorted(set([x[1] for x in gl_data if x[0] == 'Asset']))
liabilities = sorted(set([x[1] for x in gl_data if x[0] == 'Liability']))
equities = sorted(set([x[1] for x in gl_data if x[0] == 'Equity']))
ws_cur = wb_cur.create_sheet('BS_RAC')
c1 = ws_cur.cell(row=1, column=1, value='Balance Sheet - RAC')
c1.font = Font(bold='true')
c1.alignment = Alignment(horizontal='center')
ws_cur.merge_cells(start_row=1, end_row=1, start_column=1, end_column=3)
ws_cur.cell(row=3,
column=2,
value=datetime.strftime(date_end, '%B %d, %Y'))
ws_cur.cell(row=3,
column=3,
value=datetime.strftime(date_prior_ye, '%B %d, %Y'))
for c in range(2, 4):
c1 = ws_cur.cell(row=3, column=c)
c1.fill = PatternFill(start_color='1e90ff',
end_color='1e90ff',
fill_type='solid')
c1.font = Font(bold='true', color='f8f8ff')
c1.alignment = Alignment(horizontal='center')
r_next = 4
c1 = ws_cur.cell(row=r_next, column=1, value='ASSETS:')
c1.font = Font(bold='true')
r_next += 1
#Border Style
top_border = Border(top=Side(style='thin'))
#Assets
r = 0
for a in assets:
ws_cur.cell(row=r_next + r, column=1, value=a)
cur_period_bal = sum([
x[3] for x in gl_data
if x[0] == 'Asset' and x[1] == a and x[2] == date_end
])
ws_cur.cell(row=r_next + r, column=2, value=cur_period_bal)
py_end_bal = sum([
x[3] for x in gl_data
if x[0] == 'Asset' and x[1] == a and x[2] == date_prior_ye
])
ws_cur.cell(row=r_next + r, column=3, value=py_end_bal)
r += 1
r_next = r_next + r
r_asset = r_next
c1 = ws_cur.cell(row=r_next, column=1, value='Total Assets')
c1.font = Font(bold='true')
formula1 = '=sum(B{r1}:B{r2})'.format(r1=r_next - len(assets),
r2=r_next - 1)
c1 = ws_cur.cell(row=r_next, column=2, value=formula1)
c1.font = Font(bold='true')
c1.border = top_border
formula1 = '=sum(C{r1}:C{r2})'.format(r1=r_next - len(assets),
r2=r_next - 1)
c1 = ws_cur.cell(row=r_next, column=3, value=formula1)
c1.font = Font(bold='true')
c1.border = top_border
#Liabilities
r_next += 2
c1 = ws_cur.cell(row=r_next, column=1, value='LIABILITIES AND EQUITY:')
c1.font = Font(bold='true')
r_next += 1
r = 0
for liab in liabilities:
ws_cur.cell(row=r_next + r, column=1, value=liab)
cur_period_bal = -sum([
x[3] for x in gl_data
if x[0] == 'Liability' and x[1] == liab and x[2] == date_end
])
ws_cur.cell(row=r_next + r, column=2, value=cur_period_bal)
py_end_bal = -sum([
x[3] for x in gl_data
if x[0] == 'Liability' and x[1] == liab and x[2] == date_prior_ye
])
ws_cur.cell(row=r_next + r, column=3, value=py_end_bal)
r += 1
r_next = r_next + r
r_liability = r_next
c1 = ws_cur.cell(row=r_next, column=1, value='Total Liabilities')
c1.font = Font(bold='true')
formula1 = '=sum(B{r1}:B{r2})'.format(r1=r_next - len(liabilities),
r2=r_next - 1)
c1 = ws_cur.cell(row=r_next, column=2, value=formula1)
c1.font = Font(bold='true')
c1.border = top_border
formula1 = '=sum(C{r1}:C{r2})'.format(r1=r_next - len(liabilities),
r2=r_next - 1)
c1 = ws_cur.cell(row=r_next, column=3, value=formula1)
c1.font = Font(bold='true')
c1.border = top_border
#Equity
r_next += 2
r = 0
for e in equities:
ws_cur.cell(row=r_next + r, column=1, value=e)
cur_period_bal = -sum([
x[3] for x in gl_data
if x[0] == 'Equity' and x[1] == e and x[2] == date_end
])
ws_cur.cell(row=r_next + r, column=2, value=cur_period_bal)
py_end_bal = -sum([
x[3] for x in gl_data
if x[0] == 'Equity' and x[1] == e and x[2] == date_prior_ye
])
ws_cur.cell(row=r_next + r, column=3, value=py_end_bal)
r += 1
#Net Income
r_next = r_next + r
r_net_income = r_next
r_retained_earning = r_net_income - 1
ws_cur.cell(row=r_next, column=1, value='Net Income')
cur_period_bal = sum([
x[3] for x in gl_data
if x[0] == 'Net Income' and x[1] == 'Net Income' and x[2] == date_end
])
ws_cur.cell(row=r_next, column=2, value=cur_period_bal)
py_end_bal = sum([
x[3] for x in gl_data if x[0] == 'Net Income' and x[1] == 'Net Income'
and x[2] == date_prior_ye
])
ws_cur.cell(row=r_next, column=3, value=py_end_bal)
r_next += 1
r_equity = r_next
c1 = ws_cur.cell(row=r_next, column=1, value='Total Equity')
c1.font = Font(bold='true')
formula1 = '=sum(B{r1}:B{r2})'.format(r1=r_next - len(equities) - 1,
r2=r_next - 1)
c1 = ws_cur.cell(row=r_next, column=2, value=formula1)
c1.font = Font(bold='true')
c1.border = top_border
formula1 = '=sum(C{r1}:C{r2})'.format(r1=r_next - len(equities) - 1,
r2=r_next - 1)
c1 = ws_cur.cell(row=r_next, column=3, value=formula1)
c1.font = Font(bold='true')
c1.border = top_border
#Total Liabilities and Equity
r_next += 2
c1 = ws_cur.cell(row=r_next,
column=1,
value='Total Liabilities and Equity')
c1.font = Font(bold='true')
formula1 = '=B{r1}+B{r2}'.format(r1=r_liability, r2=r_equity)
c1 = ws_cur.cell(row=r_next, column=2, value=formula1)
c1.font = Font(bold='true')
formula1 = '=C{r1}+C{r2}'.format(r1=r_liability, r2=r_equity)
c1 = ws_cur.cell(row=r_next, column=3, value=formula1)
c1.font = Font(bold='true')
r_next += 2
c1 = ws_cur.cell(row=r_next, column=1, value='Accounting Equation')
c1.font = Font(bold='true')
formula1 = '=B{r1}-B{r2}'.format(r1=r_asset, r2=r_next - 2)
c1 = ws_cur.cell(row=r_next, column=2, value=formula1)
c1.font = Font(bold='true')
formula1 = '=C{r1}-C{r2}'.format(r1=r_asset, r2=r_next - 2)
c1 = ws_cur.cell(row=r_next, column=3, value=formula1)
c1.font = Font(bold='true')
#Reclass non YTD Net Income to Retained Earnings---------------------------
net_income_cur = ws_cur.cell(row=r_net_income, column=2).value
net_income_cur_ytd = sum([
x[3] for x in gl_data if x[0] == 'Net Income YTD'
and x[1] == 'Net Income YTD' and x[2] == date_end
])
net_income_remainder = net_income_cur - net_income_cur_ytd
retained_earning_cur = ws_cur.cell(row=r_retained_earning, column=2).value
retained_earning_new = retained_earning_cur + net_income_remainder
ws_cur.cell(row=r_retained_earning, column=2, value=retained_earning_new)
ws_cur.cell(row=r_net_income, column=2, value=net_income_cur_ytd)
net_income_pye = ws_cur.cell(row=r_net_income, column=3).value
net_income_py_ytd = sum([
x[3] for x in gl_data if x[0] == 'Net Income YTD'
and x[1] == 'Net Income YTD' and x[2] == date_prior_ye
])
net_income_remainder = net_income_pye - net_income_py_ytd
retained_earning_pye = ws_cur.cell(row=r_retained_earning, column=3).value
retained_earning_new = retained_earning_pye + net_income_remainder
ws_cur.cell(row=r_retained_earning, column=3, value=retained_earning_new)
ws_cur.cell(row=r_net_income, column=3, value=net_income_py_ytd)
#-------------------------------------------------------------------------
#Format Cells
format_number = '_(* #,##0_);_(* (#,##0);_(* ""-""??_);_(@_)'
for r in range(5, ws_cur.max_row + 1):
for c in range(2, 4):
ws_cur.cell(row=r, column=c).number_format = format_number
#Column widths
ws_cur.column_dimensions['A'].width = 44
ws_cur.column_dimensions['B'].width = 20
ws_cur.column_dimensions['C'].width = 20
#Page Setup
ws_cur.page_setup.orientation = ws_cur.ORIENTATION_PORTRAIT
ws_cur.page_setup.paper_size = ws_cur.PAPERSIZE_TABLOID
ws_cur.page_setup.fitToPage = True
ws_cur.page_setup.fitToHeight = False
ws_cur.page_setup.fitToWidth = 1
ws_cur.print_options.horizontalCentered = True
ws_cur.add_print_title(3)
ws_cur.page_margins = PageMargins(left=.5, right=.5, top=.5, bottom=.5)
#Freeze Panes
c1 = ws_cur.cell(row=4, column=1)
ws_cur.freeze_panes = c1
return wb_cur
