food-market/scripts/make_supplier_template.py

#!/usr/bin/env python3
"""Генерация шаблона накладной для поставщиков (handoff §7).

Создаёт docs/supplier-invoice-template.xlsx: 9-строчная шапка (Поставщик /
Номер / Дата / пустые строки с подсказкой «← заполните» ) и таблица товаров
на 9-й строке (заголовок) с колонками Штрихкод | Наименование товара |
Количество | Ед. изм. | Цена за единицу | Сумма.

TableInvoiceParser (Infrastructure/Invoices/TableReader.cs) ищет первую
строку, где есть заголовки «Штрихкод» и «Количество» — поэтому порядок
колонок свободный, но сами слова нужны именно эти или похожие.
"""
import io
import os
import zipfile
from pathlib import Path

ROOT = Path(__file__).resolve().parents[1]
OUT = ROOT / "docs" / "supplier-invoice-template.xlsx"

# ─── XML parts ───────────────────────────────────────────────────────

CONTENT_TYPES = """<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<Types xmlns="http://schemas.openxmlformats.org/package/2006/content-types">
  <Default Extension="rels" ContentType="application/vnd.openxmlformats-package.relationships+xml"/>
  <Default Extension="xml" ContentType="application/xml"/>
  <Override PartName="/xl/workbook.xml" ContentType="application/vnd.openxmlformats-officedocument.spreadsheetml.sheet.main+xml"/>
  <Override PartName="/xl/worksheets/sheet1.xml" ContentType="application/vnd.openxmlformats-officedocument.spreadsheetml.worksheet+xml"/>
  <Override PartName="/xl/sharedStrings.xml" ContentType="application/vnd.openxmlformats-officedocument.spreadsheetml.sharedStrings+xml"/>
  <Override PartName="/xl/styles.xml" ContentType="application/vnd.openxmlformats-officedocument.spreadsheetml.styles+xml"/>
</Types>
"""

RELS = """<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<Relationships xmlns="http://schemas.openxmlformats.org/package/2006/relationships">
  <Relationship Id="rId1" Type="http://schemas.openxmlformats.org/officeDocument/2006/relationships/officeDocument" Target="xl/workbook.xml"/>
</Relationships>
"""

WB_RELS = """<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<Relationships xmlns="http://schemas.openxmlformats.org/package/2006/relationships">
  <Relationship Id="rId1" Type="http://schemas.openxmlformats.org/officeDocument/2006/relationships/worksheet" Target="worksheets/sheet1.xml"/>
  <Relationship Id="rId2" Type="http://schemas.openxmlformats.org/officeDocument/2006/relationships/sharedStrings" Target="sharedStrings.xml"/>
  <Relationship Id="rId3" Type="http://schemas.openxmlformats.org/officeDocument/2006/relationships/styles" Target="styles.xml"/>
</Relationships>
"""

WORKBOOK = """<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<workbook xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main"
          xmlns:r="http://schemas.openxmlformats.org/officeDocument/2006/relationships">
  <sheets>
    <sheet name="Накладная" sheetId="1" r:id="rId1"/>
  </sheets>
</workbook>
"""

STYLES = """<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<styleSheet xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main">
  <fonts count="2">
    <font><sz val="11"/><name val="Calibri"/></font>
    <font><b/><sz val="11"/><name val="Calibri"/></font>
  </fonts>
  <fills count="2"><fill><patternFill patternType="none"/></fill><fill><patternFill patternType="gray125"/></fill></fills>
  <borders count="1"><border/></borders>
  <cellStyleXfs count="1"><xf numFmtId="0" fontId="0" fillId="0" borderId="0"/></cellStyleXfs>
  <cellXfs count="2">
    <xf numFmtId="0" fontId="0" fillId="0" borderId="0" xfId="0"/>
    <xf numFmtId="0" fontId="1" fillId="0" borderId="0" xfId="0" applyFont="1"/>
  </cellXfs>
</styleSheet>
"""

# Содержимое шаблона: 8 строк шапки + заголовок таблицы на 9-й строке.
ROWS = [
    # row 1 — название
    ["Накладная поставщика", ""],
    [],
    ["Поставщик", "← ТОО/ИП «...»"],
    ["Номер накладной", "← например, 1234"],
    ["Дата", "← ГГГГ-ММ-ДД"],
    [],
    ["", "Заполните эту таблицу и пришлите файл: система создаст приёмку автоматически."],
    [],
    # row 9 — заголовок таблицы, который ищет TableInvoiceParser
    ["Штрихкод", "Наименование товара", "Количество", "Ед. изм.", "Цена за единицу", "Сумма"],
    # примеры (можно удалить при реальном заполнении)
    ["4870123456789", "Молоко 3.2%, 1 л", 10, "шт", 450, 4500],
    ["4870987654321", "Хлеб серый, 400 г", 20, "шт", 180, 3600],
]

def _xl_col(n: int) -> str:
    s = ""
    while n >= 0:
        s = chr(ord("A") + n % 26) + s
        n = n // 26 - 1
    return s

def _escape(s: str) -> str:
    return (s.replace("&", "&amp;").replace("<", "&lt;").replace(">", "&gt;")
             .replace('"', "&quot;"))

def build_shared_strings(rows):
    """Собрать уникальные строки, вернуть (xml, index-map)."""
    seen = {}
    order = []
    for row in rows:
        for c in row:
            if isinstance(c, str) and c:
                if c not in seen:
                    seen[c] = len(order); order.append(c)
    body = "".join(f"<si><t xml:space=\"preserve\">{_escape(s)}</t></si>" for s in order)
    xml = (
        '<?xml version="1.0" encoding="UTF-8" standalone="yes"?>\n'
        f'<sst xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main" count="{sum(1 for r in rows for c in r if isinstance(c, str) and c)}" uniqueCount="{len(order)}">'
        f'{body}</sst>'
    )
    return xml, seen

def build_sheet(rows, strings_idx):
    out = io.StringIO()
    out.write('<?xml version="1.0" encoding="UTF-8" standalone="yes"?>\n')
    out.write('<worksheet xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main">')
    out.write('<cols>')
    out.write('<col min="1" max="1" width="18"/>')
    out.write('<col min="2" max="2" width="42"/>')
    out.write('<col min="3" max="3" width="12"/>')
    out.write('<col min="4" max="4" width="10"/>')
    out.write('<col min="5" max="5" width="18"/>')
    out.write('<col min="6" max="6" width="14"/>')
    out.write('</cols>')
    out.write('<sheetData>')
    for r, row in enumerate(rows, start=1):
        if not row:
            out.write(f'<row r="{r}"/>')
            continue
        is_header_row = (r == 1 or r == 9)
        out.write(f'<row r="{r}">')
        for c_idx, val in enumerate(row):
            ref = f"{_xl_col(c_idx)}{r}"
            style = ' s="1"' if is_header_row else ''
            if isinstance(val, (int, float)):
                out.write(f'<c r="{ref}"{style}><v>{val}</v></c>')
            elif isinstance(val, str) and val:
                out.write(f'<c r="{ref}"{style} t="s"><v>{strings_idx[val]}</v></c>')
            else:
                out.write(f'<c r="{ref}"{style}/>')
        out.write('</row>')
    out.write('</sheetData>')
    out.write('</worksheet>')
    return out.getvalue()

def main() -> None:
    ss_xml, idx = build_shared_strings(ROWS)
    sheet_xml = build_sheet(ROWS, idx)
    OUT.parent.mkdir(parents=True, exist_ok=True)
    with zipfile.ZipFile(OUT, "w", zipfile.ZIP_DEFLATED) as z:
        z.writestr("[Content_Types].xml", CONTENT_TYPES)
        z.writestr("_rels/.rels", RELS)
        z.writestr("xl/_rels/workbook.xml.rels", WB_RELS)
        z.writestr("xl/workbook.xml", WORKBOOK)
        z.writestr("xl/styles.xml", STYLES)
        z.writestr("xl/sharedStrings.xml", ss_xml)
        z.writestr("xl/worksheets/sheet1.xml", sheet_xml)
    print(f"wrote {OUT} ({OUT.stat().st_size} bytes)")

if __name__ == "__main__":
    main()