def create_dailyreport(self):
''' method to create excel daily report
'''
fontname = 'Tahoma'
red = '00FF0000'
orange = 'FFA500'
green = '0000FF00'
font_large_bold = Font(name=fontname, bold=True, size=11)
font_normal = Font(name=fontname, size=9)
font_bold = Font(name=fontname, bold=True, size=9)
self.ws.column_dimensions['A'].width = 0.94
self.ws.column_dimensions['B'].width = 0.75
self.ws.column_dimensions['C'].width = 11.78
self.ws.column_dimensions['D'].width = 10.89
self.ws.column_dimensions['E'].width = 20.89
self.ws.column_dimensions['F'].width = 0.56
self.ws.column_dimensions['G'].width = 0.75
self.ws.column_dimensions['H'].width = 14.11
self.ws.column_dimensions['I'].width = 10.56
self.ws.column_dimensions['J'].hidden = True
self.ws.column_dimensions['K'].width = 13.22
self.ws.column_dimensions['L'].width = 11.00
# set logo
img_logo = drawing.image.Image(self.static_root /
'img/client_icon.png')
img_logo.width = 75
img_logo.height = 75
self.ws.add_image(img_logo, 'C4')
# set title
self.ws.merge_cells('B2:K2')
self.ws['B2'].value = 'CSR DAILY REPORT'
self.ws['B2'].alignment = Alignment(horizontal='center')
self.ws['B2'].font = font_large_bold
# set date
self.ws.merge_cells('H3:I3')
self.ws.merge_cells('K3:L3')
set_vertical_cells(self.ws, 'H3', ['DATE'], font_large_bold,
Alignment(horizontal='right'))
set_vertical_cells(self.ws, 'K3', [self.report_date], font_large_bold,
Alignment())
self.ws['K3'].font = Font(name=fontname, bold=True, size=11, color=red)
# general project info
set_vertical_cells(self.ws, 'D4', [key for key in self.project_table],
font_bold, Alignment())
set_vertical_cells(self.ws, 'E4',
[val for _, val in self.project_table.items()],
font_normal, Alignment(horizontal='right'))
self.ws['E4'].font = font_bold
self.ws['E5'].number_format = '#,##0'
# daily stats
set_vertical_cells(self.ws, 'H4', [key for key in self.daily_table],
font_bold, Alignment())
set_vertical_cells(self.ws, 'I4',
[val for _, val in self.daily_table.items()],
font_normal, Alignment(horizontal='right'))
self.ws['I5'].number_format = '#,##0'
self.ws['I6'].number_format = '#,##0'
self.ws['I7'].number_format = '0.00%'
self.ws['I8'].number_format = '0.00'
self.ws['I9'].number_format = '0.00'
self.ws['I10'].number_format = '0.00'
self.ws['I11'].number_format = '0.00'
self.ws['I12'].number_format = '#,##0'
conditional_format(self.ws, 'I7', PROD_TARGET, (red, orange, green))
conditional_format(self.ws, 'I8', REC_TARGET, (red, orange, green))
# receiver stats
set_vertical_cells(self.ws, 'H13', [key for key in self.recvr_table],
font_bold, Alignment())
set_vertical_cells(self.ws, 'I13',
[val for key, val in self.recvr_table.items()],
font_normal, Alignment(horizontal='right'))
self.ws['I13'].number_format = '#,##0'
self.ws['I14'].number_format = '#,##0'
self.ws['I15'].number_format = '#,##0'
self.ws['I16'].number_format = '#,##0'
self.ws['I17'].number_format = '0.0%'
# XGO and CSR
set_vertical_cells(self.ws, 'D10',
[key[:-3] for key in self.csr_table], font_bold,
Alignment(horizontal='right'))
set_vertical_cells(self.ws, 'E10',
[val for _, val in self.csr_table.items()],
font_normal, Alignment())
# project stats
self.ws.merge_cells('K4:L4')
set_vertical_cells(self.ws, 'K4', ['Project Statistics'],
font_large_bold, Alignment(horizontal='center'))
set_vertical_cells(self.ws, 'K5',
[key for key in self.proj_stats_table], font_bold,
Alignment())
set_vertical_cells(self.ws, 'L5',
[val for _, val in self.proj_stats_table.items()],
font_normal, Alignment(horizontal='right'))
self.ws['L5'].number_format = '#,##0'
self.ws['L6'].number_format = '0.00'
self.ws['L7'].number_format = '#,##0'
self.ws['L8'].number_format = '0.00%'
# block stats
self.ws.merge_cells('K10:L10')
set_vertical_cells(self.ws, 'K10', ['Block Statistics'],
font_large_bold, Alignment(horizontal='center'))
set_vertical_cells(self.ws, 'K11',
[key for key in self.block_stats_table], font_bold,
Alignment())
set_vertical_cells(self.ws, 'L11',
[val for _, val in self.block_stats_table.items()],
font_normal, Alignment(horizontal='right'))
self.ws['L12'].number_format = '0.00'
self.ws['L13'].number_format = '0.00%'
# resources stats
self.ws.merge_cells('K15:L15')
set_vertical_cells(self.ws, 'K15', ['Resources'], font_large_bold,
Alignment(horizontal='center'))
set_vertical_cells(self.ws, 'K16',
[key for key in self.resources_table], font_bold,
Alignment())
set_vertical_cells(self.ws, 'L16',
[val for _, val in self.resources_table.items()],
font_normal, Alignment(horizontal='right'))
# hse stats
self.ws.merge_cells('K20:L20')
set_vertical_cells(self.ws, 'K20', ['HSE Statistics'], font_large_bold,
Alignment(horizontal='center'))
set_vertical_cells(self.ws, 'K21',
[key for key in self.hse_stats_table], font_bold,
Alignment())
set_vertical_cells(self.ws, 'L21',
[val for _, val in self.hse_stats_table.items()],
font_normal, Alignment(horizontal='right'))
for i in range(21, 32):
self.ws['L' + str(i)].number_format = '0;-0;;@'
conditional_format(self.ws, 'L27', STOP_TARGET, (None, None, green))
# csr comment
self.ws.merge_cells('B18:I34')
set_vertical_cells(self.ws, 'B17',
[key for key in self.csr_comment_table], font_bold,
Alignment())
set_vertical_cells(self.ws, 'B18',
[val for _, val in self.csr_comment_table.items()],
font_normal,
Alignment(vertical='top', wrap_text=True))
# add graphs
width, height = IMG_SIZE
img_daily_prod = drawing.image.Image(self.media_root /
'images/daily_prod.png')
img_daily_prod.width = width
img_daily_prod.height = height
self.ws.add_image(img_daily_prod, 'C36')
img_cumul_prod = drawing.image.Image(self.media_root /
'images/cumul_prod.png')
img_cumul_prod.width = width
img_cumul_prod.height = height
self.ws.add_image(img_cumul_prod, 'H36')
img_rec_hours = drawing.image.Image(self.media_root /
'images/rec_hours.png')
img_rec_hours.width = width
img_rec_hours.height = height
self.ws.add_image(img_rec_hours, 'C49')
img_app_ctm = drawing.image.Image(self.media_root /
'images/app_ctm_ratio.png')
img_app_ctm.width = width
img_app_ctm.height = height
self.ws.add_image(img_app_ctm, 'H49')
# set borders
set_outer_border(self.ws, 'B2:L62')
set_outer_border(self.ws, 'B2:L2')
set_outer_border(self.ws, 'B3:I17')
set_outer_border(self.ws, 'H4:I12')
set_outer_border(self.ws, 'H13:I17')
set_outer_border(self.ws, 'K4:L4')
set_outer_border(self.ws, 'K4:L9')
set_outer_border(self.ws, 'K10:L10')
set_outer_border(self.ws, 'K10:L14')
set_outer_border(self.ws, 'K15:L15')
set_outer_border(self.ws, 'K15:L19')
set_outer_border(self.ws, 'K20:L20')
set_outer_border(self.ws, 'K20:L34')
set_outer_border(self.ws, 'B18:I34')
self.ws['I3'].border = Border(top=Side(style='thin'),
bottom=Side(style='thin'))
return save_excel(self.wb)
def create_tab_weekly(self):
''' method to create excel weekly report main tab
'''
set_column_widths(self.wsw, 'A', [
0.94, 0.75, 16.80, 10.24, 10.22, 9.78, 0.44, 11.00, 9.56, 11.78,
11.78, 0.44
])
# set title
self.wsw.merge_cells('B2:L2')
self.wsw['B2'].value = 'CSR WEEKLY REPORT'
self.wsw['B2'].alignment = Alignment(horizontal='center')
self.wsw['B2'].font = font_large_bold
# set logo
img_logo = drawing.image.Image(self.static_dir / 'img/client_icon.png')
img_logo.width = 75
img_logo.height = 75
self.wsw.add_image(img_logo, 'C4')
# set project general
set_vertical_cells(self.wsw, 'D4', [k for k in self.project],
font_bold, Alignment())
set_vertical_cells(self.wsw, 'F4', [v for v in self.project.values()],
font_normal, Alignment(horizontal='right'))
self.wsw['F5'].number_format = '#,##0'
self.wsw['F6'].number_format = '0.00'
# set author
self.wsw.merge_cells('H4:I4')
self.wsw.merge_cells('H5:I5')
set_vertical_cells(self.wsw, 'H4', [k for k in self.author], font_bold,
Alignment(horizontal='center'))
set_vertical_cells(self.wsw, 'H5', [v for v in self.author.values()],
font_normal, Alignment(horizontal='center'))
# set date
self.wsw.merge_cells('H3:I3')
self.wsw.merge_cells('J3:K3')
set_vertical_cells(self.wsw, 'H3', ['DATE'], font_large_bold,
Alignment(horizontal='right'))
set_vertical_cells(self.wsw, 'J3', [self.report_date], font_large_bold,
Alignment())
self.wsw['J3'].font = Font(name=fontname,
bold=True,
size=11,
color=red)
# set project statistics
self.wsw.merge_cells('J4:K4')
set_vertical_cells(self.wsw, 'J4', ['Project Statistics'], font_bold,
Alignment(horizontal='center'))
set_vertical_cells(self.wsw, 'J5', [k for k in self.proj_stats],
font_bold, Alignment())
set_vertical_cells(self.wsw, 'K5',
[v for v in self.proj_stats.values()], font_normal,
Alignment(horizontal='right'))
self.wsw['K5'].number_format = '#,##0'
self.wsw['K6'].number_format = '0.00'
self.wsw['K7'].number_format = '#,##0'
self.wsw['K8'].number_format = '0.0%'
# set hse statistic
self.wsw.merge_cells('B11:F11')
set_vertical_cells(self.wsw, 'B11', ['HSE Statistics'], font_bold,
Alignment(horizontal='center'))
set_horizontal_cells(self.wsw, 'D12', ['Week', 'Month', 'Project'],
font_bold, Alignment(horizontal='center'))
set_vertical_cells(self.wsw, 'C13', [v[0] for v in self.hse_stats],
font_normal, Alignment())
for row, item in enumerate(self.hse_stats):
set_horizontal_cells(self.wsw, f'D{row+13}',
[item[1], item[2], item[3]], font_normal,
Alignment(horizontal='right'))
format_horizontal(self.wsw, 'D23:F23', '#,##0')
format_horizontal(self.wsw, 'D24:F24', '#,##0')
f_vals = np.array(STOP_TARGET) * 7
conditional_format(self.wsw, 'D19', f_vals, (None, None, green))
f_vals = np.array(STOP_TARGET) * self.month_days
conditional_format(self.wsw, 'E19', f_vals, (None, None, green))
f_vals = np.array(STOP_TARGET) * self.proj_days
conditional_format(self.wsw, 'F19', f_vals, (None, None, green))
# set production statistic
self.wsw.merge_cells('H11:L11')
set_vertical_cells(self.wsw, 'H11', ['Production Statistics'],
font_bold, Alignment(horizontal='center'))
set_horizontal_cells(self.wsw, 'I12', ['Week', 'Month', 'Project'],
font_bold, Alignment(horizontal='center'))
set_vertical_cells(self.wsw, 'H13', [v[0] for v in self.prod_stats],
font_normal, Alignment())
for row, item in enumerate(self.prod_stats):
set_horizontal_cells(self.wsw, f'I{row+13}',
[item[1], item[2], item[3]], font_normal,
Alignment(horizontal='right'))
format_horizontal(self.wsw, 'I13:K13', '#,##0')
format_horizontal(self.wsw, 'I14:K47', '#,##0')
format_horizontal(self.wsw, 'I15:K15', '0.0%')
format_horizontal(self.wsw, 'I16:K16', '0.00')
format_horizontal(self.wsw, 'I17:K17', '0.00')
format_horizontal(self.wsw, 'I18:K18', '0.00')
format_horizontal(self.wsw, 'I19:K19', '#,##0')
format_horizontal(self.wsw, 'I20:K20', '#,##0')
format_horizontal(self.wsw, 'I21:K21', '0.0%')
format_horizontal(self.wsw, 'I22:K22', '#,##0')
format_horizontal(self.wsw, 'I23:K23', '0.0%')
f_vals = np.array(PROD_TARGET) * 1
conditional_format(self.wsw, 'I15', f_vals, (red, orange, green))
conditional_format(self.wsw, 'J15', f_vals, (red, orange, green))
conditional_format(self.wsw, 'K15', f_vals, (red, orange, green))
f_vals = np.array(REC_TARGET) * 7
conditional_format(self.wsw, 'I16', f_vals, (red, orange, green))
f_vals = np.array(REC_TARGET) * self.month_days
conditional_format(self.wsw, 'J16', f_vals, (red, orange, green))
f_vals = np.array(REC_TARGET) * self.proj_days
conditional_format(self.wsw, 'K16', f_vals, (red, orange, green))
# set comments
self.wsw.merge_cells('B28:L28')
self.wsw.merge_cells('B29:L52')
set_vertical_cells(self.wsw, 'B28', [k for k in self.comment],
font_bold, Alignment())
set_vertical_cells(self.wsw, 'B29', [v for v in self.comment.values()],
font_normal,
Alignment(vertical='top', wrap_text=True))
# add graphs
width, height = IMG_SIZE
img_daily_prod = drawing.image.Image(self.media_dir /
'images/cumul_app_ctm.png')
img_daily_prod.width = width
img_daily_prod.height = height
self.wsw.add_image(img_daily_prod, 'C54')
img_daily_prod = drawing.image.Image(self.media_dir /
'images/pie_proj_terrain.png')
img_daily_prod.width = width
img_daily_prod.height = height
self.wsw.add_image(img_daily_prod, 'H54')
img_daily_prod = drawing.image.Image(self.media_dir /
'images/rec_hours.png')
img_daily_prod.width = width
img_daily_prod.height = height
self.wsw.add_image(img_daily_prod, 'C68')
img_daily_prod = drawing.image.Image(self.media_dir /
'images/app_ctm_ratio.png')
img_daily_prod.width = width
img_daily_prod.height = height
self.wsw.add_image(img_daily_prod, 'H68')
# set borders
set_outer_border(self.wsw, 'B2:L81')
set_outer_border(self.wsw, 'B2:L2')
set_outer_border(self.wsw, 'J4:L4')
set_outer_border(self.wsw, 'J5:L9')
set_outer_border(self.wsw, 'B11:F11')
set_outer_border(self.wsw, 'B12:F27')
set_outer_border(self.wsw, 'G11:L11')
set_outer_border(self.wsw, 'G12:L27')
set_outer_border(self.wsw, 'B28:L28')
set_outer_border(self.wsw, 'B29:L52')
def create_tab_production(self):
''' method to create excel weekly tab for production
'''
set_column_widths(self.wsp, 'A', [
0.94,
23.50,
12.33,
12.33,
12.33,
12.33,
12.33,
12.33,
12.33,
12.33,
12.33,
])
# set day production
self.wsp.merge_cells('B1:K1')
set_vertical_cells(self.wsp, 'B1', ['Daily production'], font_bold,
Alignment(horizontal='center'))
set_horizontal_cells(
self.wsp, 'B2', self.days_prod['header'], font_bold,
Alignment(
horizontal='center',
vertical='top',
wrap_text=True,
))
row, col = get_row_column('B3')
weeks_total = np.zeros(len(self.days_prod[0]) - 2)
for key in range(0, 7):
vals = self.days_prod[key]
loc = col + str(row + key)
set_horizontal_cells(self.wsp, loc, vals, font_normal,
Alignment(horizontal='right'))
self.wsp[f'B{row + key}'].alignment = Alignment(
horizontal='center')
format_range = f'C{row + key}:C{row + key}'
format_horizontal(self.wsp, format_range, int_hide_zero)
format_range = f'D{row + key}:D{row + key}'
format_horizontal(self.wsp, format_range, float_hide_zero)
format_range = f'E{row + key}:L{row + key}'
format_horizontal(self.wsp, format_range, int_hide_zero)
format_range = f'M{row + key}:M{row + key}'
format_horizontal(self.wsp, format_range, '0.000;-0;;@')
# sum for indexes 1..10, skip index 0 date and 11 qc_field
weeks_total += nan_array(vals[1:-1]).astype(np.float)
# skip the 3rd and 6th elements
vals = [*weeks_total[0:2], '', *weeks_total[3:]]
set_vertical_cells(self.wsp, 'B10', ['Weeks total'], font_bold,
Alignment(horizontal='center'))
set_horizontal_cells(self.wsp, 'C10', vals, font_bold,
Alignment(horizontal='right'))
# average vp/ hr
set_horizontal_cells(self.wsp, 'E10', [self.weeks_prod[5][3]],
font_bold, Alignment(horizontal='right'))
# average qc_field
set_horizontal_cells(self.wsp, 'M10', [self.weeks_prod[5][11]],
font_bold, Alignment(horizontal='right'))
format_horizontal(self.wsp, 'C10:C10', '#,##0')
format_horizontal(self.wsp, 'D10:D10', '0.00')
format_horizontal(self.wsp, 'E10:K10', '#,##0')
format_horizontal(self.wsp, 'M10:M10', '0.000')
# set borders
set_border(self.wsp, 'B2:M10')
# set week production
self.wsp.merge_cells('B13:K13')
set_vertical_cells(self.wsp, 'B13', ['Weekly production'], font_bold,
Alignment(horizontal='center'))
set_horizontal_cells(
self.wsp, 'B14', self.weeks_prod['header'], font_bold,
Alignment(
horizontal='center',
vertical='top',
wrap_text=True,
))
row, col = get_row_column('B15')
for key in range(0, 6):
vals = self.weeks_prod[key]
loc = col + str(row + key)
set_horizontal_cells(self.wsp, loc, vals, font_normal,
Alignment(horizontal='right'))
self.wsp[f'B{row + key}'].alignment = Alignment(
horizontal='center')
format_range = f'C{row + key}:C{row + key}'
format_horizontal(self.wsp, format_range, int_hide_zero)
format_range = f'D{row + key}:D{row + key}'
format_horizontal(self.wsp, format_range, float_hide_zero)
format_range = f'E{row + key}:L{row + key}'
format_horizontal(self.wsp, format_range, int_hide_zero)
format_range = f'M{row + key}:M{row + key}'
format_horizontal(self.wsp, format_range, '0.000;-0;;@')
# set borders
set_border(self.wsp, 'B14:M20')
set_color(self.wsp, 'B20:M20', color=lightblue)
# set terrain types for week
self.wsp.merge_cells('C22:E22')
set_vertical_cells(self.wsp, 'C22', ['Week terrain'], font_bold,
Alignment(horizontal='center'))
set_horizontal_cells(self.wsp, 'D23', ['VPs', 'Percentage'], font_bold,
Alignment(horizontal='center'))
set_vertical_cells(self.wsp, 'C24', [key for key in self.week_terrain],
font_normal, Alignment(horizontal='left'))
vals = []
percs = []
for val in self.week_terrain.values():
vals.append(val)
if self.week_terrain['total'] > 0:
perc = val / self.week_terrain['total']
else:
perc = np.nan
percs.append(perc)
set_vertical_cells(self.wsp, 'D24', vals, font_normal,
Alignment(horizontal='right'))
set_vertical_cells(self.wsp, 'E24', percs, font_normal,
Alignment(horizontal='right'))
format_vertical(self.wsp, 'D24:D30', '#,##0')
format_vertical(self.wsp, 'E24:E30', '0.00%')
set_border(self.wsp, 'D23:E23')
set_border(self.wsp, 'C24:E30')
# set terrain types for project
self.wsp.merge_cells('G22:I22')
set_vertical_cells(self.wsp, 'G22', ['Project terrain'], font_bold,
Alignment(horizontal='center'))
set_horizontal_cells(self.wsp, 'H23', ['VPs', 'Percentage'], font_bold,
Alignment(horizontal='center'))
set_vertical_cells(self.wsp, 'G24', [key for key in self.proj_terrain],
font_normal, Alignment(horizontal='left'))
vals = []
percs = []
for val in self.proj_terrain.values():
vals.append(val)
if self.proj_terrain['total'] > 0:
perc = val / self.proj_terrain['total']
else:
perc = np.nan
percs.append(perc)
set_vertical_cells(self.wsp, 'H24', vals, font_normal,
Alignment(horizontal='right'))
set_vertical_cells(self.wsp, 'I24', percs, font_normal,
Alignment(horizontal='right'))
format_vertical(self.wsp, 'H24:H30', '#,##0')
format_vertical(self.wsp, 'I24:I30', '0.00%')
set_border(self.wsp, 'H23:I23')
set_border(self.wsp, 'G24:I30')
def create_tab_times(self):
''' method to create excel weekly tab for times
'''
set_column_widths(self.wst, 'A', [
0.94,
21.56,
9.50,
8.33,
8.33,
8.33,
8.33,
8.33,
10.11,
12.33,
9.89,
8.33,
8.33,
8.33,
8.80,
8.33,
10.00,
10.00,
8.50,
8.33,
8.33,
9.80,
])
# set day times
self.wst.merge_cells('B1:V1')
set_vertical_cells(self.wst, 'B1', ['Daily times'], font_bold,
Alignment(horizontal='center'))
self.wst.merge_cells('C2:H2')
set_vertical_cells(self.wst, 'C2', ['Operational time'], font_bold,
Alignment(horizontal='center'))
self.wst.merge_cells('I2:L2')
set_vertical_cells(self.wst, 'I2', ['Standby time'], font_bold,
Alignment(horizontal='center'))
self.wst.merge_cells('M2:S2')
set_vertical_cells(self.wst, 'M2', ['Downtime'], font_bold,
Alignment(horizontal='center'))
self.wst.merge_cells('T2:V2')
set_vertical_cells(self.wst, 'T2', ['Totals'], font_bold,
Alignment(horizontal='center'))
set_horizontal_cells(
self.wst, 'B3', self.days_times['header'], font_bold,
Alignment(
horizontal='center',
vertical='top',
wrap_text=True,
))
row, col = get_row_column('B4')
weeks_total = np.zeros(len(self.days_times[0]) - 1)
for key in range(0, 7):
vals = self.days_times[key]
loc = col + str(row + key)
set_horizontal_cells(self.wst, loc, vals, font_normal,
Alignment(horizontal='right'))
self.wst[f'B{row + key}'].alignment = Alignment(
horizontal='center')
format_range = f'C{row + key}:V{row + key}'
format_horizontal(self.wst, format_range, float_hide_zero)
weeks_total += nan_array(vals[1:]).astype(np.float)
set_vertical_cells(self.wst, 'B11', ['Weeks total'], font_bold,
Alignment(horizontal='center'))
set_horizontal_cells(self.wst, 'C11', weeks_total, font_bold,
Alignment(horizontal='right'))
format_horizontal(self.wst, 'C11:V11', '0.00')
# set borders day times
set_outer_border(self.wst, 'C2:H2')
set_outer_border(self.wst, 'I2:L2')
set_outer_border(self.wst, 'M2:S2')
set_outer_border(self.wst, 'T2:V2')
set_border(self.wst, 'B3:v11')
# set week times
self.wst.merge_cells('B13:V13')
set_vertical_cells(self.wst, 'B13', ['Weekly times'], font_bold,
Alignment(horizontal='center'))
self.wst.merge_cells('C14:H14')
set_vertical_cells(self.wst, 'C14', ['Operational time'], font_bold,
Alignment(horizontal='center'))
self.wst.merge_cells('I14:L14')
set_vertical_cells(self.wst, 'I14', ['Standby time'], font_bold,
Alignment(horizontal='center'))
self.wst.merge_cells('M14:S14')
set_vertical_cells(self.wst, 'M14', ['Downtime'], font_bold,
Alignment(horizontal='center'))
self.wst.merge_cells('T14:V14')
set_vertical_cells(self.wst, 'T14', ['Totals'], font_bold,
Alignment(horizontal='center'))
set_horizontal_cells(
self.wst, 'B15', self.weeks_times['header'], font_bold,
Alignment(
horizontal='center',
vertical='top',
wrap_text=True,
))
row, col = get_row_column('B16')
for key in range(0, 6):
vals = self.weeks_times[key]
loc = col + str(row + key)
set_horizontal_cells(self.wst, loc, vals, font_normal,
Alignment(horizontal='right'))
self.wst[f'B{row + key}'].alignment = Alignment(
horizontal='center')
format_range = f'C{row + key}:V{row + key}'
format_horizontal(self.wst, format_range, float_hide_zero)
# set borders week times
set_outer_border(self.wst, 'C14:H14')
set_outer_border(self.wst, 'I14:L14')
set_outer_border(self.wst, 'M14:S14')
set_outer_border(self.wst, 'T14:V14')
set_border(self.wst, 'B15:v21')
set_color(self.wst, 'B21:V21', color=lightblue)
def create_dailyreport(self):
''' method to create excel daily report
'''
fontname = 'Tahoma'
red = '00FF0000'
orange = 'FFA500'
green = '0000FF00'
font_large_bold = Font(name=fontname, bold=True, size=11)
font_normal = Font(name=fontname, size=9)
font_bold = Font(name=fontname, bold=True, size=9)
self.ws.column_dimensions['A'].width = 0.94
self.ws.column_dimensions['B'].width = 0.75
self.ws.column_dimensions['C'].width = 11.78
self.ws.column_dimensions['D'].width = 14.23
self.ws.column_dimensions['E'].width = 17.89
self.ws.column_dimensions['F'].width = 14.11
self.ws.column_dimensions['G'].width = 8.57
self.ws.column_dimensions['H'].width = 20.71
self.ws.column_dimensions['I'].width = 10.86
# set logo
img_logo = drawing.image.Image(self.static_root /
'img/client_icon.png')
img_logo.width = 75
img_logo.height = 75
self.ws.add_image(img_logo, 'C4')
# set title
self.ws.merge_cells('B2:I2')
self.ws['B2'].value = 'CSR DAILY REPORT'
self.ws['B2'].alignment = Alignment(horizontal='center')
self.ws['B2'].font = font_large_bold
# set date
self.ws.merge_cells('F3:G3')
self.ws.merge_cells('H3:I3')
set_vertical_cells(self.ws, 'F3', ['DATE'], font_large_bold,
Alignment(horizontal='right'))
set_vertical_cells(self.ws, 'H3', [self.report_date], font_large_bold,
Alignment())
self.ws['H3'].font = Font(name=fontname, bold=True, size=11, color=red)
# general project info
set_vertical_cells(self.ws, 'D4', [key for key in self.project_table],
font_bold, Alignment())
set_vertical_cells(self.ws, 'E4',
[val for _, val in self.project_table.items()],
font_normal, Alignment(horizontal='left'))
self.ws['E4'].font = font_bold
self.ws['E5'].number_format = '#,##0'
# daily stats
self.ws.merge_cells('F4:G4')
set_vertical_cells(self.ws, 'F4', ['Recording Statistics'],
font_large_bold, Alignment(horizontal='center'))
set_vertical_cells(self.ws, 'F5', [key for key in self.daily_table],
font_bold, Alignment())
set_vertical_cells(self.ws, 'G5',
[val for _, val in self.daily_table.items()],
font_normal, Alignment(horizontal='right'))
self.ws['G5'].number_format = '#,##0'
self.ws['G6'].number_format = '#,##0'
self.ws['G7'].number_format = '#,##0'
self.ws['G8'].number_format = '0.00%'
self.ws['G9'].number_format = '0.00'
self.ws['G10'].number_format = '0.00'
self.ws['G11'].number_format = '0.00'
self.ws['G12'].number_format = '0.00'
self.ws['G13'].number_format = '#,##0'
conditional_format(self.ws, 'I8', PROD_TARGET, (red, orange, green))
conditional_format(self.ws, 'I9', REC_TARGET, (red, orange, green))
# receiver stats
self.ws.merge_cells('F14:G14')
set_vertical_cells(self.ws, 'F14', ['Nodes'], font_large_bold,
Alignment(horizontal='center'))
set_vertical_cells(self.ws, 'F15', [key for key in self.recvr_table],
font_bold, Alignment())
set_vertical_cells(self.ws, 'G15',
[val for key, val in self.recvr_table.items()],
font_normal, Alignment(horizontal='right'))
self.ws['G15'].number_format = '#,##0'
self.ws['G16'].number_format = '#,##0'
self.ws['G17'].number_format = '0.0%'
self.ws['G18'].number_format = '#,##0'
self.ws['G19'].number_format = '0.0%'
self.ws['G20'].number_format = '#,##0'
# XGO and CSR
set_vertical_cells(self.ws, 'D10',
[key[:-3] for key in self.csr_table], font_bold,
Alignment(horizontal='left'))
set_vertical_cells(self.ws, 'E10',
[val for _, val in self.csr_table.items()],
font_normal, Alignment('left'))
# project stats
self.ws.merge_cells('H4:I4')
set_vertical_cells(self.ws, 'H4', ['Project Statistics'],
font_large_bold, Alignment(horizontal='center'))
set_vertical_cells(self.ws, 'H5',
[key for key in self.proj_stats_table], font_bold,
Alignment())
set_vertical_cells(self.ws, 'I5',
[val for _, val in self.proj_stats_table.items()],
font_normal, Alignment(horizontal='right'))
self.ws['I5'].number_format = '#,##0'
self.ws['I6'].number_format = '0.00'
self.ws['I7'].number_format = '#,##0'
self.ws['I8'].number_format = '#,##0'
self.ws['I9'].number_format = '0.00%'
# resources stats
self.ws.merge_cells('H11:I11')
set_vertical_cells(self.ws, 'H11', ['Resources'], font_large_bold,
Alignment(horizontal='center'))
set_vertical_cells(self.ws, 'H12',
[key for key in self.resources_table], font_bold,
Alignment())
set_vertical_cells(self.ws, 'I12',
[val for _, val in self.resources_table.items()],
font_normal, Alignment(horizontal='right'))
# hse stats
self.ws.merge_cells('H16:I16')
set_vertical_cells(self.ws, 'H16', ['HSE Statistics'], font_large_bold,
Alignment(horizontal='center'))
set_vertical_cells(self.ws, 'H17',
[key for key in self.hse_stats_table], font_bold,
Alignment())
set_vertical_cells(self.ws, 'I17',
[val for _, val in self.hse_stats_table.items()],
font_normal, Alignment(horizontal='right'))
for i in range(17, 36):
self.ws['I' + str(i)].number_format = '0;-0;;@'
conditional_format(self.ws, 'I23', STOP_TARGET, (None, None, green))
# csr comment
self.ws.merge_cells('B20:G36')
set_vertical_cells(self.ws, 'B19',
[key for key in self.csr_comment_table], font_bold,
Alignment())
set_vertical_cells(self.ws, 'B20',
[val for _, val in self.csr_comment_table.items()],
font_normal,
Alignment(vertical='top', wrap_text=True))
# add graphs
width, height = IMG_SIZE
img_daily_prod = drawing.image.Image(self.media_root /
'images/daily_prod.png')
img_daily_prod.width = width
img_daily_prod.height = height
self.ws.add_image(img_daily_prod, 'C39')
img_cumul_prod = drawing.image.Image(self.media_root /
'images/cumul_prod.png')
img_cumul_prod.width = width
img_cumul_prod.height = height
self.ws.add_image(img_cumul_prod, 'F39')
img_rec_hours = drawing.image.Image(self.media_root /
'images/rec_hours.png')
img_rec_hours.width = width
img_rec_hours.height = height
self.ws.add_image(img_rec_hours, 'C52')
img_app_ctm = drawing.image.Image(self.media_root /
'images/app_ctm_ratio.png')
img_app_ctm.width = width
img_app_ctm.height = height
self.ws.add_image(img_app_ctm, 'F52')
# set borders
set_outer_border(self.ws, 'B2:I66')
set_outer_border(self.ws, 'B2:I2')
set_outer_border(self.ws, 'F4:G4')
set_outer_border(self.ws, 'F5:G13')
set_outer_border(self.ws, 'F14:G14')
set_outer_border(self.ws, 'F15:G19')
set_outer_border(self.ws, 'H4:I4')
set_outer_border(self.ws, 'H5:I10')
set_outer_border(self.ws, 'H11:I11')
set_outer_border(self.ws, 'H12:I15')
set_outer_border(self.ws, 'H16:I16')
set_outer_border(self.ws, 'H17:I36')
set_outer_border(self.ws, 'B20:G36')
self.ws['G3'].border = Border(top=Side(style='thin'),
bottom=Side(style='thin'))
return save_excel(self.wb)
def create_tab_services(ws, project, year, month):
dayrange = range(1, calendar.monthrange(year, month)[1] + 1)
days_in_month = len(dayrange)
last_col = {31: 'AG', 30: 'AF', 29: 'AE', 28: 'AD'}
monthyear = f'{calendar.month_name[month]}, {year}'
# set column widths
set_column_widths(ws, 'A', [42.0])
set_column_widths(ws, 'B', [4.0] * days_in_month + [8])
# set the titles
ws.merge_cells('P1:T1')
ws['P1'].value = 'Monthly services'
ws['P1'].font = font_large_bold
for r in range(2, 6):
ws.merge_cells(f'B{r}:E{r}')
set_vertical_cells(ws, 'A2', ['Contract'], font_bold,
Alignment(horizontal='left'))
set_vertical_cells(ws, 'A3', ['Project'], font_bold,
Alignment(horizontal='left'))
set_vertical_cells(ws, 'A4', ['Crew'], font_bold,
Alignment(horizontal='left'))
set_vertical_cells(ws, 'A5', ['Month, Year'], font_bold,
Alignment(horizontal='left'))
set_vertical_cells(ws, 'B2', [CONTRACT], font_normal,
Alignment(horizontal='right'))
set_vertical_cells(ws, 'B3', [project.project_name], font_normal,
Alignment(horizontal='right'))
set_vertical_cells(ws, 'B4', [project.crew_name], font_normal,
Alignment(horizontal='right'))
set_vertical_cells(ws, 'B5', [monthyear], font_normal,
Alignment(horizontal='right'))
# set the header
set_horizontal_cells(ws, 'A7', ['Item'], font_bold, Alignment())
set_horizontal_cells(ws, 'B7', [*dayrange, 'Total'], font_bold,
Alignment(horizontal='center'))
set_outer_border(ws, f'A7:{last_col[days_in_month]}7')
# set the services data
r = 8
for service in project.services.all():
service_start_row = r
set_horizontal_cells(
ws, f'A{r}',
[": ".join([service.service_contract, service.description])],
font_bold, Alignment())
for task in service.tasks.all():
r += 1
set_horizontal_cells(
ws, f'A{r}',
[": ".join([task.task_name, task.task_description[:40]])],
font_normal, Alignment())
qties = {f'{d:02}': 0.0 for d in dayrange}
task_qties = task.get_monthly_task_quantities(year, month)
total = 0.0
for dq in task_qties:
total += dq.quantity
qties[f'{dq.date.day:02}'] = dq.quantity
row_values = list(qties.values()) + [total]
set_horizontal_cells(ws, f'B{r}', row_values, font_normal,
Alignment(horizontal='right'))
format_horizontal(ws, f'B{r}:{last_col[days_in_month]}{r}',
'0.0')
set_outer_border(
ws, f'A{service_start_row}:{last_col[days_in_month]}{r+1}')
r += 2