197 lines
8.5 KiB
Python
197 lines
8.5 KiB
Python
"""Excel exports with live formulas, so totals keep working when people edit the file."""
|
|
import re
|
|
from datetime import datetime
|
|
from io import BytesIO
|
|
|
|
from openpyxl import Workbook
|
|
from openpyxl.styles import Alignment, Border, Font, PatternFill, Side
|
|
from openpyxl.utils import get_column_letter
|
|
|
|
from ..summary import YearView
|
|
from .i18n import T
|
|
|
|
MONEY = '#,##0.00;[Red]-#,##0.00'
|
|
PCT = '0.00%;[Red]-0.00%'
|
|
HEAD_FILL = PatternFill("solid", fgColor="1F3B63")
|
|
CAT_FILL = PatternFill("solid", fgColor="EEF2F8")
|
|
WHITE_BOLD = Font(bold=True, color="FFFFFF")
|
|
BOLD = Font(bold=True)
|
|
THIN_TOP = Border(top=Side(style="thin", color="999999"))
|
|
|
|
|
|
def _safe_sheet_name(name: str, used: set[str]) -> str:
|
|
base = re.sub(r"[\[\]:*?/\\]", "-", name)[:31] or "Sheet"
|
|
candidate, n = base, 2
|
|
while candidate.lower() in used:
|
|
suffix = f" ({n})"
|
|
candidate, n = base[: 31 - len(suffix)] + suffix, n + 1
|
|
used.add(candidate.lower())
|
|
return candidate
|
|
|
|
|
|
def _header(ws, row: int, labels: list[str]) -> None:
|
|
for i, label in enumerate(labels, start=1):
|
|
c = ws.cell(row, i, label)
|
|
c.font, c.fill = WHITE_BOLD, HEAD_FILL
|
|
c.alignment = Alignment(horizontal="left" if i <= 2 else "right", vertical="center")
|
|
|
|
|
|
def _title(ws, title: str, sub: str) -> None:
|
|
ws["A1"] = title
|
|
ws["A1"].font = Font(bold=True, size=14)
|
|
ws["A2"] = sub
|
|
ws["A2"].font = Font(color="666666")
|
|
|
|
|
|
def _out(wb: Workbook) -> bytes:
|
|
buf = BytesIO()
|
|
wb.save(buf)
|
|
return buf.getvalue()
|
|
|
|
|
|
def _meta(view: YearView, t: T, site: str | None) -> str:
|
|
bits = [f"{t('status')}: {t(view.year.status)}", f"{t('exported')}: {datetime.now():%Y-%m-%d %H:%M}"]
|
|
if site:
|
|
bits.insert(0, t("site_filter", site=site))
|
|
return " · ".join(bits)
|
|
|
|
|
|
def budget_xlsx(view: YearView, lang: str, site: str | None = None, include_empty: bool = True) -> bytes:
|
|
t = T(lang, view.year.year, view.year.department.name)
|
|
wb = Workbook()
|
|
ws = wb.active
|
|
ws.title = _safe_sheet_name(t("budget_title"), set())
|
|
_title(ws, t("budget_title"), t("budget_sub") + " · " + _meta(view, t, site))
|
|
months = t("months")
|
|
_header(ws, 4, [t("line"), t("site"), *months, t("total")])
|
|
row = 5
|
|
cost_subtotals = []
|
|
cats = sorted([c for c in view.categories if c.lines or include_empty],
|
|
key=lambda c: 0 if c.category.section == "revenue" else 1)
|
|
for cv in cats:
|
|
ws.cell(row, 1, cv.name(lang)).font = BOLD
|
|
for col in range(1, 16):
|
|
ws.cell(row, col).fill = CAT_FILL
|
|
row += 1
|
|
first = row
|
|
for lv in cv.lines:
|
|
ws.cell(row, 1, lv.line.name)
|
|
ws.cell(row, 2, lv.line.site.name if lv.line.site else t("group"))
|
|
for m, value in enumerate(lv.figures.months):
|
|
ws.cell(row, 3 + m, float(round(value, 2))).number_format = MONEY
|
|
ws.cell(row, 15, f"=SUM(C{row}:N{row})").number_format = MONEY
|
|
row += 1
|
|
label = t("revenue_total") if cv.category.section == "revenue" else t("sub_total", name=cv.name(lang))
|
|
ws.cell(row, 1, label).font = BOLD
|
|
for col in range(3, 16):
|
|
letter = get_column_letter(col)
|
|
formula = f"=SUM({letter}{first}:{letter}{row - 1})" if cv.lines else 0
|
|
c = ws.cell(row, col, formula)
|
|
c.number_format, c.font, c.border = MONEY, BOLD, THIN_TOP
|
|
if cv.category.section != "revenue":
|
|
cost_subtotals.append(row)
|
|
row += 2
|
|
ws.cell(row, 1, t("total_costs")).font = WHITE_BOLD
|
|
for col in range(1, 16):
|
|
ws.cell(row, col).fill = HEAD_FILL
|
|
for col in range(3, 16):
|
|
letter = get_column_letter(col)
|
|
formula = "=" + "+".join(f"{letter}{r}" for r in cost_subtotals) if cost_subtotals else 0
|
|
c = ws.cell(row, col, formula)
|
|
c.number_format, c.font = MONEY, WHITE_BOLD
|
|
ws.column_dimensions["A"].width = 52
|
|
ws.column_dimensions["B"].width = 12
|
|
for col in range(3, 16):
|
|
ws.column_dimensions[get_column_letter(col)].width = 13
|
|
ws.freeze_panes = "C5"
|
|
_inputs_sheet(wb.create_sheet(_safe_sheet_name(t("inputs"), {ws.title.lower()})), view, t, site)
|
|
return _out(wb)
|
|
|
|
|
|
def _inputs_sheet(ws, view: YearView, t: T, site: str | None) -> None:
|
|
"""The Sheet1 layout: actual, base, factor, adjusted, change, remarks."""
|
|
_title(ws, t("inputs") + f" {view.year.year}", _meta(view, t, site))
|
|
_header(ws, 4, [t("category"), t("line"), t("site"), t("prior_actual"), t("base"), t("factor"),
|
|
t("adjusted"), t("change_eur"), t("change_pct"), t("reason"), t("remarks")])
|
|
row = 5
|
|
for cv in view.with_lines():
|
|
for lv in cv.lines:
|
|
f, line = lv.figures, lv.line
|
|
ws.cell(row, 1, cv.name(t.lang))
|
|
ws.cell(row, 2, line.name)
|
|
ws.cell(row, 3, line.site.name if line.site else t("group"))
|
|
ws.cell(row, 4, float(f.prior)).number_format = MONEY
|
|
ws.cell(row, 5, float(f.base)).number_format = MONEY
|
|
ws.cell(row, 6, float(f.factor)).number_format = "0.0000"
|
|
ws.cell(row, 7, f"=ROUND(E{row}*F{row},2)").number_format = MONEY
|
|
ws.cell(row, 8, f"=G{row}-D{row}").number_format = MONEY
|
|
ws.cell(row, 9, f'=IF(D{row}=0,"",H{row}/D{row})').number_format = PCT
|
|
ws.cell(row, 10, line.adj_reason)
|
|
ws.cell(row, 11, line.remarks)
|
|
row += 1
|
|
ws.cell(row, 1, t("total")).font = BOLD
|
|
for col, letter in ((4, "D"), (5, "E"), (7, "G"), (8, "H")):
|
|
c = ws.cell(row, col, f"=SUM({letter}5:{letter}{row - 1})")
|
|
c.number_format, c.font, c.border = MONEY, BOLD, THIN_TOP
|
|
c = ws.cell(row, 9, f'=IF(D{row}=0,"",H{row}/D{row})')
|
|
c.number_format, c.font = PCT, BOLD
|
|
for letter, width in zip("ABCDEFGHIJK", (34, 46, 12, 16, 16, 9, 18, 15, 12, 30, 40)):
|
|
ws.column_dimensions[letter].width = width
|
|
ws.freeze_panes = "C5"
|
|
|
|
|
|
def cost_report_xlsx(view: YearView, lang: str, site: str | None = None) -> bytes:
|
|
t = T(lang, view.year.year, view.year.department.name)
|
|
wb = Workbook()
|
|
ws = wb.active
|
|
used: set[str] = set()
|
|
ws.title = _safe_sheet_name(t("summary"), used)
|
|
_title(ws, t("cost_report_title"), t("summary_sub") + " · " + _meta(view, t, site))
|
|
_header(ws, 4, [t("category"), t("prior_actual"), t("adjusted"), t("change_eur"), t("change_pct")])
|
|
cats = view.with_lines("cost")
|
|
row = 5
|
|
for cv in cats:
|
|
sheet = wb.create_sheet(_safe_sheet_name(cv.name(lang), used))
|
|
total_row = _category_sheet(sheet, cv, t)
|
|
ref = f"'{sheet.title}'"
|
|
ws.cell(row, 1, cv.name(lang))
|
|
ws.cell(row, 2, f"={ref}!B{total_row}").number_format = MONEY
|
|
ws.cell(row, 3, f"={ref}!C{total_row}").number_format = MONEY
|
|
ws.cell(row, 4, f"=C{row}-B{row}").number_format = MONEY
|
|
ws.cell(row, 5, f'=IF(B{row}=0,"",D{row}/B{row})').number_format = PCT
|
|
row += 1
|
|
ws.cell(row, 1, t("total")).font = BOLD
|
|
for col, letter in ((2, "B"), (3, "C")):
|
|
c = ws.cell(row, col, f"=SUM({letter}5:{letter}{row - 1})")
|
|
c.number_format, c.font, c.border = MONEY, BOLD, THIN_TOP
|
|
ws.cell(row, 4, f"=C{row}-B{row}").number_format = MONEY
|
|
ws.cell(row, 5, f'=IF(B{row}=0,"",D{row}/B{row})').number_format = PCT
|
|
ws.cell(row, 4).font = ws.cell(row, 5).font = BOLD
|
|
for letter, width in zip("ABCDE", (40, 20, 22, 18, 14)):
|
|
ws.column_dimensions[letter].width = width
|
|
return _out(wb)
|
|
|
|
|
|
def _category_sheet(ws, cv, t: T) -> int:
|
|
_title(ws, cv.name(t.lang), t("cost_lines", n=len(cv.lines)))
|
|
_header(ws, 4, [t("line"), t("prior_actual"), t("adjusted"), t("change_eur"), t("change_pct"), t("site"), t("remarks")])
|
|
row = 5
|
|
for lv in cv.lines:
|
|
ws.cell(row, 1, lv.line.name)
|
|
ws.cell(row, 2, float(lv.figures.prior)).number_format = MONEY
|
|
ws.cell(row, 3, float(lv.figures.adjusted)).number_format = MONEY
|
|
ws.cell(row, 4, f"=C{row}-B{row}").number_format = MONEY
|
|
ws.cell(row, 5, f'=IF(B{row}=0,"",D{row}/B{row})').number_format = PCT
|
|
ws.cell(row, 6, lv.line.site.name if lv.line.site else t("group"))
|
|
ws.cell(row, 7, lv.line.remarks)
|
|
row += 1
|
|
ws.cell(row, 1, t("total")).font = BOLD
|
|
for col, letter in ((2, "B"), (3, "C")):
|
|
c = ws.cell(row, col, f"=SUM({letter}5:{letter}{row - 1})")
|
|
c.number_format, c.font, c.border = MONEY, BOLD, THIN_TOP
|
|
ws.cell(row, 4, f"=C{row}-B{row}").number_format = MONEY
|
|
ws.cell(row, 5, f'=IF(B{row}=0,"",D{row}/B{row})').number_format = PCT
|
|
for letter, width in zip("ABCDEFG", (46, 18, 20, 16, 12, 12, 40)):
|
|
ws.column_dimensions[letter].width = width
|
|
return row
|