"""Presentation layer for the viewership report. Formats only. Kept apart from vr_report so the numbers and the way they are dressed can be changed independently. House rules, applied to every sheet: * a branded band at the top carrying the EVENT name, so a sheet still identifies itself once it has been copied out of the book — which is what always happens to a good sheet. * one header row, frozen, with the filter already on. * banded rows, thin rules, right-aligned figures on a monospaced face so decimal points line up down a column. * print set up before anyone asks: landscape, fit to width, header row repeated on every page, page numbers in the footer. """ from __future__ import annotations # Palette lifted from the workbook the analysts settled on: a deep teal for # every header band and table head, a pale tint of it for banded rows, slate # for body text. Their book is the house style now, so the report matches it # rather than carrying a second identity. INK = "#1F2937" # slate body text BRAND = "#177F78" # header band and table headers BRAND2 = "#177F78" # one teal, not two INDIGO = "#177F78" # kept as a name; now the same teal TEAL = "#177F78" CRIMSON = "#C0392B" AMBER = "#B4690E" RULE = "#CCCCCC" RULE2 = "#D9D9D9" BAND = "#E7F3F1" # pale teal tint MUTED = "#5B6472" WHITE = "#FFFFFF" UI = "Aptos Display" # falls back cleanly if absent UI2 = "Calibri" MONO = "Consolas" def build(wb) -> dict: """Every format the book uses, made once.""" F = {} m = wb.add_format # --- header band ------------------------------------------------- F["band"] = m({"bg_color": BRAND, "font_color": WHITE, "font_name": UI2, "font_size": 13, "bold": True, "valign": "vcenter", "indent": 1}) # Their subtitle strip is the pale tint with slate text, not white-on-teal. F["band_sub"] = m({"bg_color": BAND, "font_color": INK, "font_name": MONO, "font_size": 9, "valign": "vcenter", "indent": 2}) F["band_r"] = m({"bg_color": BAND, "font_color": MUTED, "font_name": MONO, "font_size": 9, "valign": "vcenter", "align": "right"}) # The Summary sheet is the one people open first and the one that gets # screenshotted, so it wears a larger band -- 21pt against 13pt elsewhere, # matching the sample. F["band_big"] = m({"bg_color": BRAND, "font_color": WHITE, "font_name": UI2, "font_size": 21, "bold": True, "valign": "vcenter", "indent": 1}) F["rule"] = m({"bg_color": INDIGO}) # --- table ------------------------------------------------------- hdr = {"font_name": UI2, "font_size": 9.5, "bold": True, "font_color": WHITE, "bg_color": BRAND2, "valign": "vcenter", "text_wrap": True, "border": 1, "border_color": BRAND2} F["hdr"] = m({**hdr, "align": "left", "indent": 1}) F["hdrn"] = m({**hdr, "align": "right"}) F["hdrc"] = m({**hdr, "align": "center"}) big = {**hdr, "font_size": 12} F["hdr_big"] = m({**big, "align": "left", "indent": 1}) F["hdrn_big"] = m({**big, "align": "right"}) F["hdrc_big"] = m({**big, "align": "center"}) def cell(extra=None, banded=False): base = {"font_name": UI2, "font_size": 10, "border": 1, "border_color": RULE2, "valign": "vcenter"} if banded: base["bg_color"] = BAND return m({**base, **(extra or {})}) def num(fmt="#,##0.##", extra=None, banded=False): base = {"font_name": MONO, "font_size": 9.5, "num_format": fmt, "border": 1, "border_color": RULE2, "valign": "vcenter", "align": "right"} if banded: base["bg_color"] = BAND return m({**base, **(extra or {})}) for b in (0, 1): k = "_b" if b else "" F[f"t{k}"] = cell({"indent": 1}, b) F[f"tb{k}"] = cell({"bold": True, "indent": 1}, b) F[f"tm{k}"] = cell({"font_name": MONO, "font_size": 9, "font_color": MUTED, "indent": 1}, b) F[f"tw{k}"] = cell({"font_name": MONO, "font_size": 9, "font_color": CRIMSON, "bold": True, "indent": 1}, b) F[f"n{k}"] = num("#,##0.0#", None, b) F[f"nb{k}"] = num("#,##0.0#", {"bold": True, "font_color": INK}, b) F[f"nw{k}"] = num("#,##0", None, b) # whole values F[f"nwb{k}"] = num("#,##0", {"bold": True, "font_color": INK}, b) F[f"ni{k}"] = num("#,##0", None, b) F[f"np{k}"] = num("0.#", None, b) # Confidence as a share, so the Summary reads "77.7%" rather than a # bare 77.7 that could be anything out of anything. F[f"npct{k}"] = num("0.0%", None, b) F[f"nd{k}"] = num("yyyy-mm-dd hh:mm", {"align": "center"}, b) # Seconds included: a duration column reading "0:57" beside an hours # total reading "3050:45:00" looks like two different units. One format # for every span of time in the book. F[f"ndur{k}"] = num("[h]:mm:ss", {"align": "center"}, b) # [h] not h: a total that rolls over at 24 is not a total. F[f"nhms{k}"] = num("[h]:mm:ss", None, b) # --- totals ------------------------------------------------------ tot = {"font_name": MONO, "font_size": 10, "bold": True, "bg_color": "#D5E9E6", "top": 2, "border_color": INDIGO, "align": "right"} F["tot"] = m({**tot, "num_format": "#,##0.0#"}) F["totw"] = m({**tot, "num_format": "#,##0"}) # [h] not h: a total that rolls over at 24 is not a total. F["tothms"] = m({**tot, "num_format": "[h]:mm:ss"}) F["toti"] = m({**tot, "num_format": "#,##0"}) F["totl"] = m({"font_name": UI2, "font_size": 10, "bold": True, "bg_color": "#D5E9E6", "top": 2, "border_color": INDIGO, "indent": 1}) # --- cover ------------------------------------------------------- F["cov_ev"] = m({"font_name": UI2, "font_size": 30, "bold": True, "font_color": INK}) F["cov_sub"] = m({"font_name": MONO, "font_size": 10, "font_color": MUTED}) F["kpi_l"] = m({"font_name": MONO, "font_size": 8, "font_color": MUTED, "bg_color": BAND, "align": "center", "top": 2, "border_color": INDIGO}) F["kpi_v"] = m({"font_name": UI2, "font_size": 24, "bold": True, "font_color": INDIGO, "bg_color": BAND, "align": "center", "num_format": "#,##0.0#"}) F["kpi_vw"] = m({"font_name": UI2, "font_size": 24, "bold": True, "font_color": INDIGO, "bg_color": BAND, "align": "center", "num_format": "#,##0"}) F["kpi_n"] = m({"font_name": MONO, "font_size": 8, "font_color": MUTED, "bg_color": BAND, "align": "center", "bottom": 1, "border_color": RULE}) F["h2"] = m({"font_name": UI2, "font_size": 13, "bold": True, "font_color": INK, "bottom": 2, "border_color": INDIGO}) F["body"] = m({"font_name": UI2, "font_size": 10, "font_color": INK, "text_wrap": True, "valign": "top"}) F["bodym"] = m({"font_name": UI2, "font_size": 10, "font_color": MUTED, "text_wrap": True, "valign": "top"}) F["bodyw"] = m({"font_name": UI2, "font_size": 10, "font_color": CRIMSON, "bold": True, "text_wrap": True, "valign": "top"}) F["note"] = m({"font_name": UI2, "font_size": 9.5, "font_color": MUTED, "italic": True, "text_wrap": True, "valign": "top"}) F["pill_ok"] = m({"font_name": MONO, "font_size": 9, "font_color": TEAL, "bg_color": "#E6F4F1", "align": "center", "border": 1, "border_color": "#CBE7E1"}) F["pill_no"] = m({"font_name": MONO, "font_size": 9, "font_color": CRIMSON, "bg_color": "#FBE9E7", "align": "center", "border": 1, "border_color": "#F0C8C2"}) return F def numkey(v, base: str, banded: str = "") -> str: """Which format a number should wear. Excel prints "125." for the format #,##0.## — the decimal separator survives even when no digits follow it. Rather than fight that with one format, pick from the value: a whole number gets a whole-number format and a fraction keeps up to two decimals. """ try: whole = float(v).is_integer() except (TypeError, ValueError): whole = False if base == "n": return ("nw" if whole else "n") + banded if base == "nb": return ("nwb" if whole else "nb") + banded if base == "tot": return "totw" if whole else "tot" if base == "kpi_v": return "kpi_vw" if whole else "kpi_v" return base + banded def header(ws, F, width: int, event: str, sheet_title: str, note: str = "", tab: str = INDIGO, big: bool = False) -> int: """Brand band across the top of a sheet. Returns the first free row. The event name goes on EVERY sheet on purpose: sheets get copied out of books, and one that cannot say which tournament it belongs to is a liability the moment it lands in someone else's deck. """ width = max(width, 3) ws.set_row(0, 40 if big else 27) ws.set_row(1, 24 if big else 22) ws.merge_range(0, 0, 0, width - 1, f" {event}", F["band_big" if big else "band"]) right = f"{sheet_title} " if width >= 6: ws.merge_range(1, 0, 1, width - 4, f" {note}" if note else "", F["band_sub"]) ws.merge_range(1, width - 3, 1, width - 1, right, F["band_r"]) else: ws.merge_range(1, 0, 1, width - 1, f" {sheet_title}", F["band_sub"]) ws.set_row(2, 4) ws.set_tab_color(tab) return 3 def print_setup(ws, width: int, title_row: int, landscape: bool = True) -> None: if landscape: ws.set_landscape() ws.set_paper(9) # A4 ws.fit_to_pages(1, 0) # one page wide, many long ws.set_margins(0.3, 0.3, 0.45, 0.45) ws.repeat_rows(0, title_row) # band + header on every printed page ws.set_header('&L&"Calibri,Bold"&10&K177F78GSIQ Crystal&R&9&K5B6472&D') ws.set_footer('&L&9&K5B6472Estimates — not audited actuals&R&9&K5B6472Page &P of &N')