84 lines
3.2 KiB
Python
84 lines
3.2 KiB
Python
"""Render extracted supplier-offer data into the Excel form the CFO defined:
|
|
|
|
Supplier header (Jméno, DIČ/IČO, kontaktní email, adresa)
|
|
then a table:
|
|
Název zboží | ID zboží dodavatele | Množství | Jednotka | Jednotková cena | Cena celkem
|
|
"""
|
|
from openpyxl import Workbook
|
|
from openpyxl.styles import Alignment, Border, Font, PatternFill, Side
|
|
from openpyxl.utils import get_column_letter
|
|
|
|
HEADER_FILL = PatternFill("solid", fgColor="2563EB")
|
|
HEADER_FONT = Font(bold=True, color="FFFFFF", size=11)
|
|
LABEL_FONT = Font(bold=True, size=10)
|
|
THIN = Side(style="thin", color="D0D5DC")
|
|
BORDER = Border(left=THIN, right=THIN, top=THIN, bottom=THIN)
|
|
|
|
COLUMNS = [
|
|
("Název zboží", "name", 46),
|
|
("ID zboží dodavatele", "supplier_id", 20),
|
|
("Množství", "quantity", 12),
|
|
("Jednotka", "unit", 12),
|
|
("Jednotková cena", "unit_price", 16),
|
|
("Cena celkem", "total_price", 16),
|
|
]
|
|
|
|
|
|
def write_offer_xlsx(data: dict, out_path: str) -> None:
|
|
wb = Workbook()
|
|
ws = wb.active
|
|
ws.title = "Nabídka"
|
|
|
|
sup = data.get("supplier") or {}
|
|
ico_dic = " / ".join(x for x in (sup.get("ico"), sup.get("dic")) if x) or ""
|
|
|
|
# ── Supplier header block ──
|
|
ws.cell(row=1, column=1, value="Dodavatel").font = Font(bold=True, size=13)
|
|
header_pairs = [
|
|
("Jméno", sup.get("name")),
|
|
("DIČ / IČO", ico_dic),
|
|
("Kontaktní email", sup.get("email")),
|
|
("Adresa", sup.get("address")),
|
|
]
|
|
row = 2
|
|
for label, val in header_pairs:
|
|
ws.cell(row=row, column=1, value=label).font = LABEL_FONT
|
|
ws.cell(row=row, column=2, value=val if val is not None else "")
|
|
row += 1
|
|
|
|
row += 1 # spacer
|
|
|
|
# ── Line-items table ──
|
|
for c, (title, _key, _w) in enumerate(COLUMNS, 1):
|
|
cell = ws.cell(row=row, column=c, value=title)
|
|
cell.font = HEADER_FONT
|
|
cell.fill = HEADER_FILL
|
|
cell.alignment = Alignment(horizontal="center", wrap_text=True)
|
|
cell.border = BORDER
|
|
row += 1
|
|
|
|
for item in data.get("line_items") or []:
|
|
for c, (_title, key, _w) in enumerate(COLUMNS, 1):
|
|
v = item.get(key)
|
|
cell = ws.cell(row=row, column=c, value=v if v is not None else "")
|
|
cell.border = BORDER
|
|
if key in ("unit_price", "total_price") and isinstance(v, (int, float)):
|
|
cell.number_format = "#,##0.00"
|
|
elif key == "quantity" and isinstance(v, (int, float)):
|
|
# Whole numbers get a pure integer format with NO decimal
|
|
# section — some viewers render the decimal separator even
|
|
# when "0.###" has no fractional digits, leaving a stray
|
|
# comma. Only show decimals when the value actually has them.
|
|
cell.number_format = "0" if float(v).is_integer() else "0.###"
|
|
cell.alignment = Alignment(
|
|
horizontal="left" if key in ("name", "supplier_id", "unit")
|
|
else "right",
|
|
vertical="top", wrap_text=(key == "name"))
|
|
row += 1
|
|
|
|
for c, (_title, _key, w) in enumerate(COLUMNS, 1):
|
|
ws.column_dimensions[get_column_letter(c)].width = w
|
|
|
|
ws.freeze_panes = ws.cell(row=7, column=1) # keep header + table head visible
|
|
wb.save(out_path)
|