def _setup(self):
self.row_dimensions = BoundDictionary("index", self._add_row)
self.column_dimensions = DimensionHolder(
worksheet=self, default_factory=self._add_column)
self.page_breaks = PageBreak()
self._charts = []
self._images = []
self._rels = RelationshipList()
self._drawing = None
self._comments = []
self._merged_cells = []
self._tables = []
self.data_validations = DataValidationList()
self._hyperlinks = []
self.sheet_state = 'visible'
self.page_setup = PrintPageSetup(worksheet=self)
self.print_options = PrintOptions()
self._print_rows = None
self._print_cols = None
self._print_area = None
self.page_margins = PageMargins()
self.views = SheetViewList()
self.protection = SheetProtection()
self._current_row = 0
self.auto_filter = AutoFilter()
self.sort_state = SortState()
self.paper_size = None
self.formula_attributes = {}
self.orientation = None
self.conditional_formatting = ConditionalFormattingList()
self.legacy_drawing = None
self.sheet_properties = WorksheetProperties()
self.sheet_format = SheetFormatProperties()
def adjust_cond_formats(self, n: int = 1, name: str = 'ActLog'):
"""Extend conditional formatting by one row
- have to delete then re-add cond formats"""
ws = self.wb[name]
orig_format = cp(ws.conditional_formatting)
ws.conditional_formatting = ConditionalFormattingList(
) # clear formats
for cond in orig_format:
ws.conditional_formatting.add(inc_rng(cond.sqref, n=n),
cond.cfRule[0])
def test_write_conditional_formatting(self):
ws = self.ws
cf = ConditionalFormattingList()
ws.conditional_formatting = cf
fill = PatternFill(start_color=Color('FFEE1111'),
end_color=Color('FFEE1111'),
patternType=fills.FILL_SOLID)
font = Font(name='Arial',
size=12,
bold=True,
underline=Font.UNDERLINE_SINGLE)
border = Border(top=Side(border_style=borders.BORDER_THIN,
color=Color(colors.DARKYELLOW)),
bottom=Side(border_style=borders.BORDER_THIN,
color=Color(colors.BLACK)))
cf.add(
'C1:C10',
FormulaRule(formula=['ISBLANK(C1)'],
font=font,
border=border,
fill=fill))
cf.add('D1:D10', FormulaRule(formula=['ISBLANK(D1)'], fill=fill))
from openpyxl.writer.worksheet import write_conditional_formatting
for _ in write_conditional_formatting(ws):
pass # exhaust generator
wb = ws.parent
assert len(wb._differential_styles.styles) == 2
ft1, ft2 = wb._differential_styles.styles
assert ft1.font == font
assert ft1.border == border
assert ft1.fill == fill
assert ft2.fill == fill
def test_conditional_font(self):
"""Test to verify font style written correctly."""
ws = self.ws
cf = ConditionalFormattingList()
ws.conditional_formatting = cf
# Create cf rule
redFill = PatternFill(start_color=Color('FFEE1111'),
end_color=Color('FFEE1111'),
patternType=fills.FILL_SOLID)
whiteFont = Font(color=Color("FFFFFFFF"))
ws.conditional_formatting.add(
'A1:A3',
CellIsRule(operator='equal',
formula=['"Fail"'],
stopIfTrue=False,
font=whiteFont,
fill=redFill))
from openpyxl.writer.worksheet import write_conditional_formatting
# First, verify conditional formatting xml
cfs = write_conditional_formatting(ws)
xml = b""
for cf in cfs:
xml += tostring(cf)
diff = compare_xml(
xml, """
<conditionalFormatting sqref="A1:A3">
<cfRule dxfId="0" operator="equal" priority="1" type="cellIs" stopIfTrue="0">
<formula>"Fail"</formula>
</cfRule>
</conditionalFormatting>
""")
assert diff is None, diff
def __init__(self):
self.conditional_formatting = ConditionalFormattingList()
self.parent = DummyWorkbook()
def write_report(computed_indicators,
ident,
type_ident,
exs,
libsiret,
model_name,
dps=None,
aggregate=False,
perimeter=None):
"""
Writes an open_anafi report into an excel file.
:param perimeter:
:param computed_indicators: A list of all the indicators and their values for each year to evaluate
:type computed_indicators: class:`pandas.DataFrame`
:param ident: The identifier(s) the report was evaluated on (A concatenation if multiple SIRETS)
:type ident: str
:param type_ident: The type of the identifier (SIRET, SIREN, FINESS)
:type type_ident: str
:param exs: The list of years the repport was calculated on
:type exs: list
:param libsiret: The establishment's label (#TODO check this, not sure)
:type libsiret: str
:param model_name: The name of the reference excel file (The model to fill)
:type model_name: str
:param dps: dictionary with the state of the account (Definitive or Provisoire)
:type dps: dict
:param aggregate: If the report was an aggreggation of multiple identifiers or not
:type aggregate: bool
:return: The name of the created Excel file
:rtype: str
"""
# put data in excel
global patterns, siret
wb = load_workbook('./open_anafi/templates/' + model_name)
double = Side(border_style="thin", color="000000")
border = Border(top=double, left=double, right=double, bottom=double)
font_header = Font(italic=True, color="000000", name='Arial', size=7)
al_header = Alignment(horizontal="center", vertical="center")
al_dps = Alignment(horizontal="center", vertical="bottom")
al_indic = Alignment(horizontal="right", vertical="center")
sheets = wb.worksheets
for sheet in sheets:
col5 = {
r: sheet['{}{}'.format(get_column_letter(5), r)].value
for r in range(1, sheet.max_row)
}
# Garder le formattage conditionnel de la colonne des totaux.
rules = copy(sheet.conditional_formatting)
cond_format_dict = {}
for r in list(rules._cf_rules.items()):
space = str(r[0]).split(" ")[1][:-1]
rule = r[1]
cond_format_dict[space] = rule
sheet.conditional_formatting = ConditionalFormattingList()
for key, rule in cond_format_dict.items():
if key[0] == 'D':
k2 = copy(key)
for rul in rule:
for i in range(len(exs)):
sheet.conditional_formatting.add(
k2.replace('D', get_column_letter(4 + i)), rul)
max_row = sheet.max_row
c = get_column_letter(2)
r = 1
total_type = None
while r < max_row + 1:
while sheet['{}{}'.format(
c, r)].value is None and r < max_row + 1:
r += 1
total_type = None
if total_type is None:
type_cell = sheet['{}{}'.format(get_column_letter(5),
r - 1)]
type_cell.alignment = Alignment(horizontal='center')
total_type = type_cell.value if type_cell.value is not None else "unknown"
indicator_name = sheet['{}{}'.format(c, r)].value
c = get_column_letter(4)
cell = sheet['{}{}'.format(c, r)]
style = copy(cell._style)
hyperlink = copy(cell.hyperlink)
number_format = copy(cell.number_format)
try:
for i in range(
computed_indicators[indicator_name].shape[0]):
cell = sheet['{}{}'.format(c, r)]
cell.value = computed_indicators[indicator_name][i]
cell._style = style
cell.hyperlink = hyperlink
cell.number_format = number_format
c = get_column_letter(5 + i)
current_indicator = computed_indicators[indicator_name]
if total_type == "$totalV":
n = len(current_indicator)
m = exs[-1] - exs[0]
if n == 1 or n == 0:
tvam = 0
else:
# solution de secour: SI rapport negatif on calcul pas sinon on calcul
ratio = current_indicator[-1] / current_indicator[0]
tvam = math.copysign(1.0, ratio) * math.pow(
abs(ratio), 1 / (m - 1)) - 1
all_same_sign = (-1 * numpy.sign(current_indicator[0])
not in [
numpy.sign(current_indicator[0]),
numpy.sign(current_indicator[n -
1])
])
copied_font = copy(sheet[f'{chr(ord(c) - 1)}{r}'].font)
copied_font.italic = True
if col5[r] != 'N' and all_same_sign:
sheet['{}{}'.format(c, r)].value = tvam
sheet['{}{}'.format(c, r)].font = copied_font
sheet['{}{}'.format(c, r)].number_format = '0.0%'
sheet['{}{}'.format(c, r)].border = copy(
sheet[f'{chr(ord(c) - 1)}{r}'].border)
sheet['{}{}'.format(c, r)].fill = copy(
sheet[f'{chr(ord(c) - 1)}{r}'].fill)
sheet['{}{}'.format(c, r)].alignment = al_indic
elif total_type == "$totalM":
mean = current_indicator.mean()
copied_font = copy(sheet[f'{chr(ord(c) - 1)}{r}'].font)
copied_format = copy(
sheet[f'{chr(ord(c) - 1)}{r}'].number_format)
copied_font.italic = True
sheet['{}{}'.format(
c, r
)].value = mean if not numpy.ma.is_masked(mean) else 0
sheet['{}{}'.format(c, r)].font = copied_font
sheet['{}{}'.format(c,
r)].number_format = copied_format
sheet['{}{}'.format(c, r)].border = copy(
sheet[f'{chr(ord(c) - 1)}{r}'].border)
sheet['{}{}'.format(c, r)].fill = copy(
sheet[f'{chr(ord(c) - 1)}{r}'].fill)
sheet['{}{}'.format(c, r)].alignment = al_indic
elif total_type == "$totalC":
copied_font = copy(sheet[f'{chr(ord(c) - 1)}{r}'].font)
copied_font.italic = True
sheet['{}{}'.format(c, r)].value = numpy.sum(
numpy.nan_to_num(current_indicator))
sheet['{}{}'.format(c, r)].font = copied_font
sheet['{}{}'.format(c, r)].number_format = '#,##0_-'
sheet['{}{}'.format(c, r)].border = copy(
sheet[f'{chr(ord(c) - 1)}{r}'].border)
sheet['{}{}'.format(c, r)].fill = copy(
sheet[f'{chr(ord(c) - 1)}{r}'].fill)
sheet['{}{}'.format(c, r)].alignment = al_indic
else:
pass
except KeyError:
pass
finally:
r += 1
c = get_column_letter(2)
c = get_column_letter(4)
r = 1
while r < max_row + 1:
while sheet['{}{}'.format(
c, r)].value != '$exges' and r < max_row + 1:
r += 1
c = get_column_letter(5)
total_type = sheet['{}{}'.format(c, r)].value
c = get_column_letter(4)
if r < max_row + 1:
cell = sheet['{}{}'.format(c, r)]
border = copy(cell.border)
fill = copy(cell.fill)
font = copy(cell.font)
for i in range(len(exs)):
cell = sheet['{}{}'.format(c, r)]
cell.value = exs[i]
cell.border = border
cell.fill = fill
cell.font = font
cell.alignment = Alignment(horizontal='center')
c = get_column_letter(5 + i)
if total_type == "$totalV":
sheet['{}{}'.format(c,
r)].value = "Var. annuelle moyenne"
sheet['{}{}'.format(c, r)].font = font_header
sheet['{}{}'.format(c, r)].border = copy(
sheet[f'{chr(ord(c) - 1)}{r}'].border)
sheet['{}{}'.format(c, r)].alignment = al_header
sheet.column_dimensions[f'{c}'].width = 17
elif total_type == "$totalM":
sheet['{}{}'.format(c, r)].font = font_header
sheet['{}{}'.format(c, r)].border = copy(
sheet[f'{chr(ord(c) - 1)}{r}'].border)
sheet['{}{}'.format(c, r)].alignment = al_header
sheet.column_dimensions[f'{c}'].width = 17
sheet['{}{}'.format(c, r)].value = "Moyenne"
elif total_type == "$totalC":
sheet['{}{}'.format(c,
r)].value = "Cumul sur les années"
sheet['{}{}'.format(c, r)].font = font_header
sheet['{}{}'.format(c, r)].border = copy(
sheet[f'{chr(ord(c) - 1)}{r}'].border)
sheet['{}{}'.format(c, r)].alignment = al_header
sheet.column_dimensions[f'{c}'].width = 17
c = get_column_letter(4)
# if the value is a string
siret = ident['ident'].tolist()
if len(siret) == 1:
patterns = {
'$libsiret':
f'{libsiret}'.replace('[', '').replace(']', '')[1:-1],
'$siret':
f'{siret}'.replace('[', '').replace(']', '').replace(
"'", "").replace(" ", "")
} # .replace(" ", "")
else:
patterns = {
'$libsiret': 'Etablissements multiples',
'$siret': 'Etablissements multiples'
}
# if the value is a list
patterns_list = {'$dp': dps}
for pattern in patterns:
coordinates = ExcelWriterTools._look_for_pattern(
sheet, pattern)
for coordinate in coordinates:
sheet[coordinate[0]].value = sheet[
coordinate[0]].value.replace(pattern,
patterns[pattern])
sheet[coordinate[0]].border = border
for pattern_list in patterns_list:
coordinates = ExcelWriterTools._look_for_pattern(
sheet, pattern_list)
for coordinate in coordinates:
col = coordinate[1]['col']
row = coordinate[1]['row']
for el in patterns_list[pattern_list]:
sheet[f'{get_column_letter(col)}{row}'].value = el
sheet[f'{get_column_letter(col)}{row}'].border = border
sheet[
f'{get_column_letter(col)}{row}'].alignment = al_dps
sheet[f'{get_column_letter(col)}{row}'].font = copy(
sheet[f'{get_column_letter(col - 1)}{row}'].font)
col += 1
coordinates = ExcelWriterTools._look_for_pattern(
sheet, '$perimeter')
if coordinates:
i = 2
sheet.delete_rows(coordinates[0][1]['row'])
ExcelWriterTools.write_perimeter_headers(sheet)
for r in dataframe_to_rows(ident, index=False, header=False):
sheet.cell(row=i, column=1).value = r[0]
sheet.cell(row=i, column=2).value = r[1]
sheet.cell(row=i, column=3).value = r[2]
sheet.cell(row=i, column=4).value = r[3]
sheet.cell(row=i, column=5).value = r[4]
sheet.cell(row=i, column=6).value = r[5]
sheet.cell(row=i, column=7).value = r[6]
i += 1
count = len(list(dataframe_to_rows(ident))) - 1
tab = Table(displayName="Table1", ref=f"A1:G{count}")
# Add a default style with striped rows and banded columns
style = TableStyleInfo(name="TableStyleMedium9",
showFirstColumn=False,
showLastColumn=False,
showRowStripes=True,
showColumnStripes=False)
tab.tableStyleInfo = style
sheet.add_table(tab)
sheet.column_dimensions['A'].width = 23
sheet.column_dimensions['B'].width = 23
sheet.column_dimensions['C'].width = 23
sheet.column_dimensions['D'].width = 23
sheet.column_dimensions['E'].width = 28
sheet.column_dimensions['F'].width = 28
sheet.column_dimensions['G'].width = 23
# for sheet in sheets:
# for merged_cell in sheet.merged_cells.ranges:
# ExcelWriterTools.border_maker(sheet, str(merged_cell), border=border)
if not aggregate:
siret_name = patterns['$siret'].replace(',', 'et')
else:
siret_name = str(randrange(1000, 9999))
if len(siret_name) > 20:
siret_name = siret_name[:20]
ExcelWriterTools.write_perimeter(wb, siret, exs, perimeter)
report_name = 'FRAME_{}_{}_{}_{}_{}.xlsx'.format(
siret_name, type_ident, exs[0], exs[-1],
datetime.now().strftime("%Y%m%d_%H%M%S"))
wb.save('{}/reports/{}'.format(
dirname(dirname(dirname(abspath(__file__)))), report_name))
return report_name
def worksheet_with_cf(worksheet):
from openpyxl.formatting.formatting import ConditionalFormattingList
worksheet.conditional_formating = ConditionalFormattingList()
return worksheet