def xl_set_col_width(sheet, column, cm):
""" change column width """
letter = column_to_letter(column)
if column not in sheet.column_dimensions.keys():
sheet.column_dimensions[letter] = \
ColumnDimension(worksheet=sheet)
sheet.column_dimensions[letter].width = xl_col_width(cm)
def xl_set_col_width(sheet, column, cm):
""" change column width """
letter = column_to_letter(column)
if column not in sheet.column_dimensions.keys():
sheet.column_dimensions[letter] = \
ColumnDimension(worksheet=sheet)
sheet.column_dimensions[letter].width = xl_col_width(cm)
def auto_calc_for(indicator, column, row):
calculation = ""
data = {
'num': "${l}{r}".format(l=column_to_letter(column - 2), r=row),
'denom': "${l}{r}".format(l=column_to_letter(column - 1), r=row),
'coef': indicator.TYPES_COEFFICIENT.get(indicator.itype),
'suffix': indicator.value_format.replace('{value}', '')}
if indicator.itype == indicator.PROPORTION:
calculation += "{num}/{denom}"
else:
try:
calculation += "({num}*{coef})/{denom}"
except ZeroDivisionError:
raise
formula = '=IF({num}<>"",IF({denom}<>"",' \
'CONCATENATE(ROUND(' + calculation + ',2),"{suffix}")' \
',"?"),"?")'
return formula.format(**data)
def auto_calc_for(indicator, column, row):
calculation = ""
data = {
'num': "${l}{r}".format(l=column_to_letter(column - 2), r=row),
'denom': "${l}{r}".format(l=column_to_letter(column - 1), r=row),
'coef': indicator.TYPES_COEFFICIENT.get(indicator.itype),
'suffix': indicator.value_format.replace('{value}', '')
}
if indicator.itype == indicator.PROPORTION:
calculation += "{num}/{denom}"
else:
try:
calculation += "({num}*{coef})/{denom}"
except ZeroDivisionError:
raise
formula = '=IF({num}<>"",IF({denom}<>"",' \
'CONCATENATE(ROUND(' + calculation + ',2),"{suffix}")' \
',"?"),"?")'
return formula.format(**data)
def generate_dataentry_for(dps, save_to=None):
is_all_dps = dps == Entity.get_root()
# colors
black = 'FF000000'
dark_gray = 'FFA6A6A6'
light_gray = 'FFDEDEDE'
yellow = 'F9FF00'
# styles
header_font = Font(name='Calibri',
size=12,
bold=True,
italic=False,
vertAlign=None,
underline='none',
strike=False,
color=black)
std_font = Font(name='Calibri',
size=12,
bold=False,
italic=False,
vertAlign=None,
underline='none',
strike=False,
color=black)
header_fill = PatternFill(fill_type=FILL_SOLID, start_color=dark_gray)
yellow_fill = PatternFill(fill_type=FILL_SOLID, start_color=yellow)
black_fill = PatternFill(fill_type=FILL_SOLID, start_color=black)
odd_fill = PatternFill(fill_type=FILL_SOLID, start_color=light_gray)
thin_black_side = Side(style='thin', color='FF000000')
thick_black_side = Side(style='thick', color='FF000000')
std_border = Border(
left=thin_black_side,
right=thin_black_side,
top=thin_black_side,
bottom=thin_black_side,
)
thick_left_border = Border(
left=thick_black_side,
right=thin_black_side,
top=thin_black_side,
bottom=thin_black_side,
)
thick_right_border = Border(
right=thick_black_side,
left=thin_black_side,
top=thin_black_side,
bottom=thin_black_side,
)
centered_alignment = Alignment(horizontal='center',
vertical='center',
text_rotation=0,
wrap_text=False,
shrink_to_fit=False,
indent=0)
left_alignment = Alignment(horizontal='left', vertical='center')
vertical_alignment = Alignment(horizontal='left',
vertical='bottom',
text_rotation=90,
wrap_text=True,
shrink_to_fit=False,
indent=0)
number_format = '# ### ### ##0'
protected = Protection(locked=True, hidden=False)
unprotected = Protection(locked=False, hidden=False)
header_style = {
'font': header_font,
'fill': header_fill,
'border': std_border,
'alignment': centered_alignment,
'protection': protected
}
vheader_style = {
'font': std_font,
'alignment': vertical_alignment,
'protection': protected
}
vheader_left_style = copy.copy(vheader_style)
vheader_left_style.update({'border': thick_left_border})
vheader_right_style = copy.copy(vheader_style)
vheader_right_style.update({'border': thick_right_border})
std_style = {
'font': std_font,
'border': std_border,
'alignment': centered_alignment,
}
names_style = {
'font': std_font,
'border': std_border,
'alignment': left_alignment,
}
def apply_style(target, style):
for key, value in style.items():
setattr(target, key, value)
# data validations
yv = DataValidation(type="list",
formula1='"{}"'.format(",".join(
[str(y) for y in range(2014, 2025)])),
allow_blank=True)
mv = DataValidation(type="list",
formula1='"{}"'.format(",".join(
[str(y) for y in range(1, 13)])),
allow_blank=True)
dv = DataValidation(type="whole",
operator="greaterThanOrEqual",
formula1='0')
wb = Workbook()
ws = wb.active
ws.title = "Données"
# sticky columns (DPS, ZS, YEAR, MONTH)
ws.freeze_panes = ws['E5']
ws.add_data_validation(yv)
ws.add_data_validation(mv)
ws.add_data_validation(dv)
# resize row height for 0, 1
xl_set_row_height(ws, 1, 2.2)
xl_set_row_height(ws, 2, 2.2)
# resize col A, B
xl_set_col_width(ws, 1, 5.5)
xl_set_col_width(ws, 2, 4.5)
# write partial metadata headers
ws.merge_cells("A3:A4")
ws.cell("A3").value = "DPS"
ws.merge_cells("B3:B4")
ws.cell("B3").value = "ZS"
ws.cell("C3").value = "ANNÉE"
ws.cell("D3").value = "MOIS"
indicator_column = 5
dps_row = 5
# zs_row = dps_row + 1
# header style
for sr in openpyxl.utils.cells_from_range("A3:D4"):
for coord in sr:
apply_style(ws.cell(coord), header_style)
for coord in ["C4", "D4"]:
ws.cell(coord).fill = yellow_fill
ws.cell(coord).protection = unprotected
# ZS of the selected DPS
children = [
child for child in dps.get_children()
if child.etype == Entity.ZONE or is_all_dps
]
def std_write(row, column, value, style=std_style):
cell = ws.cell(row=row, column=column)
cell.value = value
apply_style(cell, style)
def auto_calc_for(indicator, column, row):
calculation = ""
data = {
'num': "${l}{r}".format(l=column_to_letter(column - 2), r=row),
'denom': "${l}{r}".format(l=column_to_letter(column - 1), r=row),
'coef': indicator.TYPES_COEFFICIENT.get(indicator.itype),
'suffix': indicator.value_format.replace('{value}', '')
}
if indicator.itype == indicator.PROPORTION:
calculation += "{num}/{denom}"
else:
try:
calculation += "({num}*{coef})/{denom}"
except ZeroDivisionError:
raise
formula = '=IF({num}<>"",IF({denom}<>"",' \
'CONCATENATE(ROUND(' + calculation + ',2),"{suffix}")' \
',"?"),"?")'
return formula.format(**data)
# write indicator headers
column = indicator_column
for indicator in Indicator.get_all_sorted():
# write top header with indic name
row = 1
ws.merge_cells(start_row=row,
end_row=row + 1,
start_column=column,
end_column=column + 2)
std_write(row, column, indicator.name, vheader_style)
# write header with indic number
row = 3
num_str = "{n} - {t}".format(n=indicator.number,
t=indicator.verbose_collection_type)
ws.merge_cells(start_row=row,
end_row=row,
start_column=column,
end_column=column + 2)
std_write(row, column, num_str, header_style)
apply_style(ws.cell(row=row, column=column + 1), header_style)
# write sub header with NUM/DENOM
row = 4
if indicator.itype == Indicator.NUMBER:
ws.merge_cells(start_row=row,
end_row=row,
start_column=column,
end_column=column + 2)
std_write(row, column, "NOMBRE", std_style)
for r in range(row, row + len(children) + 2): # DPS + children
ws.merge_cells(start_row=r,
end_row=r,
start_column=column,
end_column=column + 2)
else:
std_write(row, column, "NUMERAT", std_style)
std_write(row, column + 1, "DÉNOM", std_style)
std_write(row, column + 2, "CALC", std_style)
row = dps_row + len(children)
nb_rows = row if is_all_dps else row + 1
# whether a row displays a ZS or not
row_is_zs = lambda row: False if is_all_dps else row > dps_row
# row-specific styles
for r in range(1, nb_rows):
left = ws.cell(row=r, column=column)
right = ws.cell(row=r, column=column + 1)
calc = ws.cell(row=r, column=column + 2)
# apply default style
if r >= dps_row:
apply_style(left, std_style)
apply_style(right, std_style)
apply_style(calc, std_style)
left.number_format = number_format
right.number_format = number_format
calc.number_format = number_format
# write formula for auto third calc
calc.set_explicit_value(value=auto_calc_for(
indicator=indicator, column=column + 2, row=r),
data_type=calc.TYPE_FORMULA)
# apply even/odd style
if r % 2 == 0:
if column == indicator_column:
for c in range(1, indicator_column):
ws.cell(row=r, column=c).fill = odd_fill
ws.cell(row=r, column=column).fill = odd_fill
ws.cell(row=r, column=column + 1).fill = odd_fill
ws.cell(row=r, column=column + 2).fill = odd_fill
# disable cell if data not expected at ZS
if row_is_zs(r) and indicator.collection_level != Entity.ZONE:
left.fill = black_fill
left.protection = protected
right.fill = black_fill
right.protection = protected
calc.fill = black_fill
elif not row_is_zs(r) \
and indicator.collection_type == indicator.ROUTINE:
left.fill = black_fill
left.protection = protected
right.fill = black_fill
right.protection = protected
calc.fill = black_fill
else:
left.protection = unprotected
right.protection = unprotected
# calc cell is always protected
calc.protection = protected
# apply thick borders
left.border = thick_left_border
# right.border = thick_right_border
calc.border = thick_right_border
# iterate over indicator
column += 3
last_row = dps_row + len(children)
# apply data validation for periods
yv.ranges.append('C4:C{}'.format(last_row))
mv.ranges.append('D4:D{}'.format(last_row))
# apply positive integer validation to all cells
last_column = indicator_column + len(Indicator.get_all_manual())
last_letter = column_to_letter(last_column)
dv.ranges.append('E4:{c}{r}'.format(c=last_letter, r=last_row))
row = dps_row
initial_row = [] if is_all_dps else [None]
# write names & periods
for child in initial_row + children:
if is_all_dps:
dps_name = child.std_name
zs_name = "-"
else:
dps_name = dps.std_name
zs_name = child.std_name if child else "-"
std_write(row, 1, dps_name, names_style)
std_write(row, 2, zs_name, names_style)
# set default value for period
year = ws.cell(row=row, column=3)
year.set_explicit_value(value="=$C$4", data_type=year.TYPE_FORMULA)
apply_style(year, std_style)
year.protection = unprotected
month = ws.cell(row=row, column=4)
month.set_explicit_value(value="=$D$4", data_type=month.TYPE_FORMULA)
apply_style(month, std_style)
month.protection = unprotected
row += 1
ws.protection.set_password("PNLP")
ws.protection.enable()
if save_to:
logger.info("saving to {}".format(save_to))
wb.save(save_to)
return
stream = StringIO.StringIO()
wb.save(stream)
return stream
def generate_dataentry_for(dps, save_to=None):
is_all_dps = dps == Entity.get_root()
# colors
black = 'FF000000'
dark_gray = 'FFA6A6A6'
light_gray = 'FFDEDEDE'
yellow = 'F9FF00'
# styles
header_font = Font(
name='Calibri',
size=12,
bold=True,
italic=False,
vertAlign=None,
underline='none',
strike=False,
color=black)
std_font = Font(
name='Calibri',
size=12,
bold=False,
italic=False,
vertAlign=None,
underline='none',
strike=False,
color=black)
header_fill = PatternFill(fill_type=FILL_SOLID, start_color=dark_gray)
yellow_fill = PatternFill(fill_type=FILL_SOLID, start_color=yellow)
black_fill = PatternFill(fill_type=FILL_SOLID, start_color=black)
odd_fill = PatternFill(fill_type=FILL_SOLID, start_color=light_gray)
thin_black_side = Side(style='thin', color='FF000000')
thick_black_side = Side(style='thick', color='FF000000')
std_border = Border(
left=thin_black_side,
right=thin_black_side,
top=thin_black_side,
bottom=thin_black_side,
)
thick_left_border = Border(
left=thick_black_side,
right=thin_black_side,
top=thin_black_side,
bottom=thin_black_side,)
thick_right_border = Border(
right=thick_black_side,
left=thin_black_side,
top=thin_black_side,
bottom=thin_black_side,)
centered_alignment = Alignment(
horizontal='center',
vertical='center',
text_rotation=0,
wrap_text=False,
shrink_to_fit=False,
indent=0)
left_alignment = Alignment(
horizontal='left',
vertical='center')
vertical_alignment = Alignment(
horizontal='left',
vertical='bottom',
text_rotation=90,
wrap_text=True,
shrink_to_fit=False,
indent=0)
number_format = '# ### ### ##0'
protected = Protection(locked=True, hidden=False)
unprotected = Protection(locked=False, hidden=False)
header_style = {
'font': header_font,
'fill': header_fill,
'border': std_border,
'alignment': centered_alignment,
'protection': protected
}
vheader_style = {
'font': std_font,
'alignment': vertical_alignment,
'protection': protected
}
vheader_left_style = copy.copy(vheader_style)
vheader_left_style.update({'border': thick_left_border})
vheader_right_style = copy.copy(vheader_style)
vheader_right_style.update({'border': thick_right_border})
std_style = {
'font': std_font,
'border': std_border,
'alignment': centered_alignment,
}
names_style = {
'font': std_font,
'border': std_border,
'alignment': left_alignment,
}
def apply_style(target, style):
for key, value in style.items():
setattr(target, key, value)
# data validations
yv = DataValidation(type="list",
formula1='"{}"'.format(
",".join([str(y) for y in range(2014, 2025)])),
allow_blank=True)
mv = DataValidation(type="list",
formula1='"{}"'.format(
",".join([str(y) for y in range(1, 13)])),
allow_blank=True)
dv = DataValidation(type="whole", operator="greaterThanOrEqual",
formula1='0')
wb = Workbook()
ws = wb.active
ws.title = "Données"
# sticky columns (DPS, ZS, YEAR, MONTH)
ws.freeze_panes = ws['E5']
ws.add_data_validation(yv)
ws.add_data_validation(mv)
ws.add_data_validation(dv)
# resize row height for 0, 1
xl_set_row_height(ws, 1, 2.2)
xl_set_row_height(ws, 2, 2.2)
# resize col A, B
xl_set_col_width(ws, 1, 5.5)
xl_set_col_width(ws, 2, 4.5)
# write partial metadata headers
ws.merge_cells("A3:A4")
ws.cell("A3").value = "DPS"
ws.merge_cells("B3:B4")
ws.cell("B3").value = "ZS"
ws.cell("C3").value = "ANNÉE"
ws.cell("D3").value = "MOIS"
indicator_column = 5
dps_row = 5
# zs_row = dps_row + 1
# header style
for sr in openpyxl.utils.cells_from_range("A3:D4"):
for coord in sr:
apply_style(ws.cell(coord), header_style)
for coord in ["C4", "D4"]:
ws.cell(coord).fill = yellow_fill
ws.cell(coord).protection = unprotected
# ZS of the selected DPS
children = [child for child in dps.get_children()
if child.etype == Entity.ZONE or is_all_dps]
def std_write(row, column, value, style=std_style):
cell = ws.cell(row=row, column=column)
cell.value = value
apply_style(cell, style)
def auto_calc_for(indicator, column, row):
calculation = ""
data = {
'num': "${l}{r}".format(l=column_to_letter(column - 2), r=row),
'denom': "${l}{r}".format(l=column_to_letter(column - 1), r=row),
'coef': indicator.TYPES_COEFFICIENT.get(indicator.itype),
'suffix': indicator.value_format.replace('{value}', '')}
if indicator.itype == indicator.PROPORTION:
calculation += "{num}/{denom}"
else:
try:
calculation += "({num}*{coef})/{denom}"
except ZeroDivisionError:
raise
formula = '=IF({num}<>"",IF({denom}<>"",' \
'CONCATENATE(ROUND(' + calculation + ',2),"{suffix}")' \
',"?"),"?")'
return formula.format(**data)
# write indicator headers
column = indicator_column
for indicator in Indicator.get_all_sorted():
# write top header with indic name
row = 1
ws.merge_cells(start_row=row, end_row=row + 1,
start_column=column, end_column=column + 2)
std_write(row, column, indicator.name, vheader_style)
# write header with indic number
row = 3
num_str = "{n} - {t}".format(n=indicator.number,
t=indicator.verbose_collection_type)
ws.merge_cells(start_row=row, end_row=row,
start_column=column, end_column=column + 2)
std_write(row, column, num_str, header_style)
apply_style(ws.cell(row=row, column=column + 1), header_style)
# write sub header with NUM/DENOM
row = 4
if indicator.itype == Indicator.NUMBER:
ws.merge_cells(start_row=row, end_row=row,
start_column=column, end_column=column + 2)
std_write(row, column, "NOMBRE", std_style)
for r in range(row, row + len(children) + 2): # DPS + children
ws.merge_cells(start_row=r, end_row=r,
start_column=column, end_column=column + 2)
else:
std_write(row, column, "NUMERAT", std_style)
std_write(row, column + 1, "DÉNOM", std_style)
std_write(row, column + 2, "CALC", std_style)
row = dps_row + len(children)
nb_rows = row if is_all_dps else row + 1
# whether a row displays a ZS or not
row_is_zs = lambda row: False if is_all_dps else row > dps_row
# row-specific styles
for r in range(1, nb_rows):
left = ws.cell(row=r, column=column)
right = ws.cell(row=r, column=column + 1)
calc = ws.cell(row=r, column=column + 2)
# apply default style
if r >= dps_row:
apply_style(left, std_style)
apply_style(right, std_style)
apply_style(calc, std_style)
left.number_format = number_format
right.number_format = number_format
calc.number_format = number_format
# write formula for auto third calc
calc.set_explicit_value(
value=auto_calc_for(indicator=indicator,
column=column + 2,
row=r),
data_type=calc.TYPE_FORMULA)
# apply even/odd style
if r % 2 == 0:
if column == indicator_column:
for c in range(1, indicator_column):
ws.cell(row=r, column=c).fill = odd_fill
ws.cell(row=r, column=column).fill = odd_fill
ws.cell(row=r, column=column + 1).fill = odd_fill
ws.cell(row=r, column=column + 2).fill = odd_fill
# disable cell if data not expected at ZS
if row_is_zs(r) and indicator.collection_level != Entity.ZONE:
left.fill = black_fill
left.protection = protected
right.fill = black_fill
right.protection = protected
calc.fill = black_fill
elif not row_is_zs(r) \
and indicator.collection_type == indicator.ROUTINE:
left.fill = black_fill
left.protection = protected
right.fill = black_fill
right.protection = protected
calc.fill = black_fill
else:
left.protection = unprotected
right.protection = unprotected
# calc cell is always protected
calc.protection = protected
# apply thick borders
left.border = thick_left_border
# right.border = thick_right_border
calc.border = thick_right_border
# iterate over indicator
column += 3
last_row = dps_row + len(children)
# apply data validation for periods
yv.ranges.append('C4:C{}'.format(last_row))
mv.ranges.append('D4:D{}'.format(last_row))
# apply positive integer validation to all cells
last_column = indicator_column + len(Indicator.get_all_manual())
last_letter = column_to_letter(last_column)
dv.ranges.append('E4:{c}{r}'.format(c=last_letter, r=last_row))
row = dps_row
initial_row = [] if is_all_dps else [None]
# write names & periods
for child in initial_row + children:
if is_all_dps:
dps_name = child.std_name
zs_name = "-"
else:
dps_name = dps.std_name
zs_name = child.std_name if child else "-"
std_write(row, 1, dps_name, names_style)
std_write(row, 2, zs_name, names_style)
# set default value for period
year = ws.cell(row=row, column=3)
year.set_explicit_value(value="=$C$4",
data_type=year.TYPE_FORMULA)
apply_style(year, std_style)
year.protection = unprotected
month = ws.cell(row=row, column=4)
month.set_explicit_value(value="=$D$4",
data_type=month.TYPE_FORMULA)
apply_style(month, std_style)
month.protection = unprotected
row += 1
ws.protection.set_password("PNLP")
ws.protection.enable()
if save_to:
logger.info("saving to {}".format(save_to))
wb.save(save_to)
return
stream = StringIO.StringIO()
wb.save(stream)
return stream