0 dní v řadě · 0 bodů

Užitečné knihovny · 45 min

Mise: automatický report do Excelu

Ze špinavého exportu objednávek postavíš hotový sešit se souhrnem, vzorci, grafem, seznamem chyb a kontrolou výsledku.

Hloubka
Úkoly

Cílem je sešit se třemi listy:

text
report.xlsx
├── Souhrn   pobočky, součty ve vzorcích a graf
├── Data     očištěné objednávky
└── Chyby    co se nepodařilo zpracovat a proč

Export vypadá takhle. Prohlédni si ho pozorně, je v něm několik min.

text
objednavka;datum;pobocka;produkt;kusy;cena_kus
1001;03.03.2026;Brno;Espresso;2;59,00
1002;03.03.2026;praha ;Cappuccino;1;79,00
1003;04.03.2026;Brno;Zrnková káva 250 g;1;189,50
1004;04.03.2026;Ostrava;Espresso;x;59,00
1002;03.03.2026;Praha;Cappuccino;1;79,00
1005;05.03.2026;Praha;Flat white;3;85,00
1006;31.02.2026;Ostrava;Latte;1;82,00
1007;06.03.2026;Ostrava;Latte;2;82,00
1008;06.03.2026;Brno;Cappuccino;-1;79,00
1009;07.03.2026;Praha;Zrnková káva 250 g;2;189,50
1010;07.03.2026;Ostrava;=1+1;1;82,00

Malé praha s mezerou, kusy x, objednávka 1002 dvakrát, 31. února, záporné kusy a produkt, který vypadá jako vzorec. Přesně takhle vypadají data ze skutečného provozu.

Lidsky řečeno

Report je jako mytí nádobí v restauraci. Nejdřív vytřídíš, co se vůbec dá umýt, a rozbité talíře odložíš stranou, ale nezahodíš je potají. Pak teprve skládáš čisté na stůl. Kdo začne prostírat dřív, než třídí, servíruje na prasklém.

Krok 1: přečti a očisti jeden řádek

Soubor má středníky a desetinné čárky, protože tak ho ukládá české prostředí. Modul csv z lekce o JSON a CSV umí oddělovač změnit a DictReader z každého řádku udělá slovník. Hodnoty jsou ale pořád jen texty.

import csv
import io

text = """objednavka;datum;pobocka;produkt;kusy;cena_kus
1002;03.03.2026;praha ;Cappuccino;1;79,50
"""

radek = next(csv.DictReader(io.StringIO(text), delimiter=";"))
print(radek["pobocka"] + "|")
print(radek["cena_kus"], type(radek["cena_kus"]).__name__)
Výstup
praha |
79,50 str

io.StringIO se tváří jako otevřený soubor, jen čte z textu v paměti. Převody jsou snadné, když víš, co která funkce čeká. Datum rozebere datetime.strptime podle vzoru a nesmyslné datum odmítne výjimkou.

from datetime import datetime

print(float("79,50".replace(",", ".")))
print(" praha ".strip().title())
print(datetime.strptime("03.03.2026", "%d.%m.%Y").date())

try:
    datetime.strptime("31.02.2026", "%d.%m.%Y")
except ValueError:
    print("Takový den neexistuje.")
Výstup
79.5
Praha
2026-03-03
Takový den neexistuje.
Úkol

Očisti jeden řádek

Napiš funkci ocisti(radek). Dostane slovník textů z DictReader a vrátí nový slovník s klíči objednavka (int), datum (date), pobocka (bez mezer okolo, první písmeno velké), produkt (bez mezer okolo), kusy (int) a cena (float). Když kusy nejsou kladné, vyvolej ValueError. Špatné číslo nebo datum vyvolá ValueError samo.

from datetime import datetime

def ocisti(radek):
    return radek
Výstup

Krok 2: oddělit dobré od špatných

Program nesmí spadnout kvůli jednomu řádku, ale ani nesmí chybu tiše spolknout. Špatné řádky proto putují do zvláštního seznamu i s důvodem.

Úkol

Třídička

Napiš nacti(text), která vrátí dvojici (dobre, chyby). V dobre jsou výsledky funkce ocisti. V chyby jsou dvojice (cislo_objednavky, duvod) jako texty. Řádek selže, když ocisti vyvolá ValueError, nebo když číslo objednávky už bylo přijato. Důvodem u duplicity je duplicitní objednávka.

import csv
import io
from datetime import datetime

EXPORT = """objednavka;datum;pobocka;produkt;kusy;cena_kus
1001;03.03.2026;Brno;Espresso;2;59,00
1002;03.03.2026;praha ;Cappuccino;1;79,00
1003;04.03.2026;Brno;Zrnková káva 250 g;1;189,50
1004;04.03.2026;Ostrava;Espresso;x;59,00
1002;03.03.2026;Praha;Cappuccino;1;79,00
1005;05.03.2026;Praha;Flat white;3;85,00
1006;31.02.2026;Ostrava;Latte;1;82,00
1007;06.03.2026;Ostrava;Latte;2;82,00
1008;06.03.2026;Brno;Cappuccino;-1;79,00
1009;07.03.2026;Praha;Zrnková káva 250 g;2;189,50
1010;07.03.2026;Ostrava;=1+1;1;82,00
"""

def ocisti(radek):
    kusy = int(radek["kusy"])
    if kusy <= 0:
        raise ValueError("počet kusů musí být kladný")
    return {
        "objednavka": int(radek["objednavka"]),
        "datum": datetime.strptime(radek["datum"].strip(), "%d.%m.%Y").date(),
        "pobocka": radek["pobocka"].strip().title(),
        "produkt": radek["produkt"].strip(),
        "kusy": kusy,
        "cena": float(radek["cena_kus"].replace(",", ".")),
    }

def nacti(text):
    dobre = []
    chyby = []
    # doplň
    return dobre, chyby
Výstup

Krok 3: spočítej souhrn

Teď už jsou data čistá, takže součty jsou jednoduché. Tržba řádku je kusy × cena. Slovník s výchozí hodnotou defaultdict ušetří kontrolu, jestli pobočka už existuje.

Úkol

Souhrn poboček

Napiš souhrn(radky), která vrátí seznam slovníků s klíči pobocka, objednavky, kusy a trzba. Tržbu zaokrouhli na 2 místa a seznam seřaď od nejvyšší tržby.

from collections import defaultdict

RADKY = [
    {"pobocka": "Brno", "kusy": 2, "cena": 59.0},
    {"pobocka": "Praha", "kusy": 1, "cena": 79.0},
    {"pobocka": "Brno", "kusy": 1, "cena": 189.5},
    {"pobocka": "Praha", "kusy": 3, "cena": 85.0},
    {"pobocka": "Ostrava", "kusy": 2, "cena": 82.0},
]

def souhrn(radky):
    return []
Výstup
Kvíz

Proč špatné řádky ukládáme do seznamu chyby, a ne jen přeskočíme?

Krok 4: list s daty

Základy openpyxl znáš z lekce o Excelu. Než začneš zapisovat, je tu jedna záludnost, kvůli které je v exportu produkt =1+1. Text začínající rovnítkem openpyxl uloží jako vzorec.

from openpyxl import Workbook, load_workbook

wb = Workbook()
ws = wb.active
ws["A1"] = "=1+1"
ws["A2"] = "=1+1"
ws["A2"].data_type = "s"
wb.save("past.xlsx")

znovu = load_workbook("past.xlsx").active
for adresa in ("A1", "A2"):
    bunka = znovu[adresa]
    print(adresa, bunka.value, bunka.data_type)
Výstup
A1 =1+1 f
A2 =1+1 s

Typ f je vzorec a s je obyčejný text. Vzorec z cizích dat je bezpečnostní díra: útočník může do názvu produktu nebo jména zákazníka vložit výraz, který se spustí v Excelu účetní. Proto každému textu ze vstupu nastav data_type = "s".

Zbytek je formátování. Formát čísla určuje jen vzhled, hodnota v buňce zůstává číslem a dál se s ní dá počítat.

from datetime import date

from openpyxl import Workbook, load_workbook
from openpyxl.styles import Font, PatternFill

wb = Workbook()
ws = wb.active
ws.title = "Ukazka"
ws.append(["Datum", "Cena"])
ws.append([date(2026, 3, 3), 1234.5])

for bunka in ws[1]:
    bunka.font = Font(bold=True, color="F2EFE9")
    bunka.fill = PatternFill("solid", fgColor="0A0A0A")

ws["A2"].number_format = "DD.MM.YYYY"
ws["B2"].number_format = '#,##0.00 "Kč"'
ws.freeze_panes = "A2"
ws.auto_filter.ref = "A1:B2"
ws.column_dimensions["A"].width = 14
wb.save("ukazka.xlsx")

znovu = load_workbook("ukazka.xlsx")["Ukazka"]
print(repr(znovu["A2"].value))
print(znovu["B2"].value, znovu["B2"].number_format)
print(znovu.freeze_panes, znovu.auto_filter.ref)
Výstup
datetime.datetime(2026, 3, 3, 0, 0)
1234.5 #,##0.00 "Kč"
A2 A1:B2

Datum se vrátilo jako datetime, ne date. Excel rozdíl nezná, takže při porovnávání po načtení použij .date(). V zápisu #,##0.00 znamená čárka oddělovač tisíců a tečka desetinné místo bez ohledu na jazyk. Česky nastavený Excel je zobrazí po svém.

Úkol

List Data

Napiš zapis_data(wb, radky), která vytvoří list Data, na první řádek dá HLAVICKA a pod ni objednávky ve sloupcích A až F. Do sloupce G dej vzorec, například =E2*F2. Texty pobočky a produktu chraň přes data_type = "s". Ve sloupci B nastav formát DD.MM.YYYY, ve sloupcích F a G FORMAT_KC. Ukotvi první řádek (A2) a zapni filtr přes celou tabulku. Funkce list vrátí.

from datetime import date

from openpyxl import Workbook, load_workbook

HLAVICKA = ["Objednávka", "Datum", "Pobočka", "Produkt", "Kusů", "Cena za kus", "Tržba"]
FORMAT_KC = '#,##0.00 "Kč"'
RADKY = [
    {"objednavka": 1001, "datum": date(2026, 3, 3), "pobocka": "Brno", "produkt": "Espresso", "kusy": 2, "cena": 59.0},
    {"objednavka": 1010, "datum": date(2026, 3, 7), "pobocka": "Ostrava", "produkt": "=1+1", "kusy": 1, "cena": 82.0},
]

def zapis_data(wb, radky):
    ws = wb.create_sheet("Data")
    ws.append(HLAVICKA)
    # doplň
    return ws
Výstup

Krok 5: souhrn se vzorci a grafem

Souhrn by šlo zapsat jako hotová čísla z kroku 3. Se vzorci ale report ožije: když majitelka opraví řádek v listu Data, souhrn se přepočítá sám. Má to jeden háček, který překvapí skoro každého.

from openpyxl import Workbook, load_workbook

wb = Workbook()
ws = wb.active
ws["A1"] = 2
ws["A2"] = 3
ws["A3"] = "=SUM(A1:A2)"
wb.save("vzorec.xlsx")

print(load_workbook("vzorec.xlsx").active["A3"].value)
print(load_workbook("vzorec.xlsx", data_only=True).active["A3"].value)
Výstup
=SUM(A1:A2)
None

openpyxl vzorce nepočítá. Jen je zapíše a výsledek doplní až Excel při otevření. Volba data_only=True čte uložený výsledek, a ten u čerstvého souboru neexistuje, proto None. Správnost tedy v Pythonu nemůžeš ověřit čtením vzorců. Za chvíli to vyřešíme jinak.

Vzorce se píšou anglicky a s čárkami, i když je český Excel ukáže jako SUMA se středníky. V souboru je uložená anglická podoba a překládá ji až Excel.

Úkol

List Souhrn

Napiš zapis_souhrn(wb, pobocky). Vytvoří list Souhrn s hlavičkou Pobočka, Objednávky, Kusů, Tržba. Pro každou pobočku přidá řádek se vzorci, které čtou z listu Data. Pod ně přidá řádek Celkem se součty přes SUM. Tržby dostanou FORMAT_KC. Graf je už připravený ve funkci pridej_graf, zavolej ji s počtem poboček. Funkce list vrátí.

from openpyxl import Workbook
from openpyxl.chart import BarChart, Reference

FORMAT_KC = '#,##0.00 "Kč"'

def pridej_graf(ws, pocet):
    graf = BarChart()
    graf.title = "Tržba podle poboček"
    graf.legend = None
    graf.x_axis.delete = False
    graf.y_axis.delete = False
    graf.add_data(Reference(ws, min_col=4, min_row=1, max_row=pocet + 1), titles_from_data=True)
    graf.set_categories(Reference(ws, min_col=1, min_row=2, max_row=pocet + 1))
    ws.add_chart(graf, "F2")

def zapis_souhrn(wb, pobocky):
    ws = wb.create_sheet("Souhrn")
    ws.append(["Pobočka", "Objednávky", "Kusů", "Tržba"])
    for r, nazev in enumerate(pobocky, start=2):
        ws.cell(r, 1, nazev).data_type = "s"
        ws.cell(r, 2, f"=COUNTIF(Data!$C:$C,A{r})")
        # doplň kusy a tržbu přes SUMIF
    # doplň řádek Celkem a graf
    return ws
Výstup
Kvíz

Do buňky jsi přes openpyxl zapsal =SUM(A1:A2) a soubor uložil. Co vrátí load_workbook(cesta, data_only=True) pro tuhle buňku, když soubor ještě nikdo neotevřel v Excelu?

Krok 6: kontrola a hlavní funkce

Vzorce číst nejde, ale surové hodnoty ano. Po uložení proto soubor znovu otevřeš a z listu Data spočítáš tržbu jinou cestou. Když nesedí se součtem z Pythonu, report se nesmí odeslat. Tahle kontrola odhalí například posunutý sloupec.

from openpyxl import Workbook, load_workbook

def kontrola(cesta, ocekavano):
    ws = load_workbook(cesta)["Data"]
    radky = ws.iter_rows(min_row=2, min_col=5, max_col=6, values_only=True)
    soucet = sum(kusy * cena for kusy, cena in radky)
    if abs(soucet - ocekavano) > 0.005:
        raise ValueError(f"Nesedí tržba: v souboru {soucet}, čekáno {ocekavano}")
    return round(soucet, 2)

wb = Workbook()
ws = wb.active
ws.title = "Data"
ws.append(["Objednávka", "Datum", "Pobočka", "Produkt", "Kusů", "Cena"])
ws.append([1, None, "Brno", "Latte", 2, 82.0])
wb.save("test.xlsx")

print(kontrola("test.xlsx", 164.0))
try:
    kontrola("test.xlsx", 200.0)
except ValueError as chyba:
    print(chyba)
Výstup
164
Nesedí tržba: v souboru 164, čekáno 200.0

Porovnává se s malou tolerancí, protože desetinná čísla se v počítači ukládají nepřesně.

Úkol

Spusť celou výrobu

Všechny díly jsou hotové. Doplň vytvor_report(text, cesta). Ta načte export, vytvoří sešit jen s listy Souhrn, Data a Chyby v tomto pořadí, uloží ho, spustí kontrola a vrátí slovník s klíči radku, chyb a trzba. Pobočky předej do zapis_souhrn v pořadí podle tržby. Tržba je součet tržeb z funkce souhrn.

import csv
import io
from collections import defaultdict
from datetime import datetime

from openpyxl import Workbook, load_workbook
from openpyxl.chart import BarChart, Reference
from openpyxl.styles import Font, PatternFill

EXPORT = """objednavka;datum;pobocka;produkt;kusy;cena_kus
1001;03.03.2026;Brno;Espresso;2;59,00
1002;03.03.2026;praha ;Cappuccino;1;79,00
1003;04.03.2026;Brno;Zrnková káva 250 g;1;189,50
1004;04.03.2026;Ostrava;Espresso;x;59,00
1002;03.03.2026;Praha;Cappuccino;1;79,00
1005;05.03.2026;Praha;Flat white;3;85,00
1006;31.02.2026;Ostrava;Latte;1;82,00
1007;06.03.2026;Ostrava;Latte;2;82,00
1008;06.03.2026;Brno;Cappuccino;-1;79,00
1009;07.03.2026;Praha;Zrnková káva 250 g;2;189,50
1010;07.03.2026;Ostrava;=1+1;1;82,00
"""
FORMAT_KC = '#,##0.00 "Kč"'

def ocisti(radek):
    kusy = int(radek["kusy"])
    if kusy <= 0:
        raise ValueError("počet kusů musí být kladný")
    return {
        "objednavka": int(radek["objednavka"]),
        "datum": datetime.strptime(radek["datum"].strip(), "%d.%m.%Y").date(),
        "pobocka": radek["pobocka"].strip().title(),
        "produkt": radek["produkt"].strip(),
        "kusy": kusy,
        "cena": float(radek["cena_kus"].replace(",", ".")),
    }

def nacti(text):
    dobre, chyby, videno = [], [], set()
    for radek in csv.DictReader(io.StringIO(text), delimiter=";"):
        cislo = radek["objednavka"]
        if cislo in videno:
            chyby.append((cislo, "duplicitní objednávka"))
            continue
        try:
            zaznam = ocisti(radek)
        except ValueError as chyba:
            chyby.append((cislo, str(chyba)))
            continue
        videno.add(cislo)
        dobre.append(zaznam)
    return dobre, chyby

def souhrn(radky):
    pobocky = defaultdict(lambda: {"objednavky": 0, "kusy": 0, "trzba": 0.0})
    for radek in radky:
        p = pobocky[radek["pobocka"]]
        p["objednavky"] += 1
        p["kusy"] += radek["kusy"]
        p["trzba"] += radek["kusy"] * radek["cena"]
    vysledek = [{"pobocka": n, **p, "trzba": round(p["trzba"], 2)} for n, p in pobocky.items()]
    return sorted(vysledek, key=lambda p: p["trzba"], reverse=True)

def uprav_vzhled(ws):
    for bunka in ws[1]:
        bunka.font = Font(bold=True, color="F2EFE9")
        bunka.fill = PatternFill("solid", fgColor="0A0A0A")
    for sloupec in ws.columns:
        delky = [len(str(b.value)) for b in sloupec if b.value is not None and b.data_type != "f"]
        ws.column_dimensions[sloupec[0].column_letter].width = min(max(delky, default=10) + 3, 40)

def zapis_data(wb, radky):
    ws = wb.create_sheet("Data")
    ws.append(["Objednávka", "Datum", "Pobočka", "Produkt", "Kusů", "Cena za kus", "Tržba"])
    for r, radek in enumerate(radky, start=2):
        ws.cell(r, 1, radek["objednavka"])
        ws.cell(r, 2, radek["datum"]).number_format = "DD.MM.YYYY"
        ws.cell(r, 3, radek["pobocka"]).data_type = "s"
        ws.cell(r, 4, radek["produkt"]).data_type = "s"
        ws.cell(r, 5, radek["kusy"])
        ws.cell(r, 6, radek["cena"]).number_format = FORMAT_KC
        ws.cell(r, 7, f"=E{r}*F{r}").number_format = FORMAT_KC
    ws.freeze_panes = "A2"
    ws.auto_filter.ref = f"A1:G{len(radky) + 1}"
    uprav_vzhled(ws)

def zapis_souhrn(wb, pobocky):
    ws = wb.create_sheet("Souhrn")
    ws.append(["Pobočka", "Objednávky", "Kusů", "Tržba"])
    for r, nazev in enumerate(pobocky, start=2):
        ws.cell(r, 1, nazev).data_type = "s"
        ws.cell(r, 2, f"=COUNTIF(Data!$C:$C,A{r})")
        ws.cell(r, 3, f"=SUMIF(Data!$C:$C,A{r},Data!$E:$E)")
        ws.cell(r, 4, f"=SUMIF(Data!$C:$C,A{r},Data!$G:$G)").number_format = FORMAT_KC
    posledni = len(pobocky) + 1
    ws.cell(posledni + 1, 1, "Celkem").font = Font(bold=True)
    for sloupec, pismeno in enumerate("BCD", start=2):
        ws.cell(posledni + 1, sloupec, f"=SUM({pismeno}2:{pismeno}{posledni})").font = Font(bold=True)
    ws.cell(posledni + 1, 4).number_format = FORMAT_KC
    graf = BarChart()
    graf.title = "Tržba podle poboček"
    graf.legend = None
    graf.x_axis.delete = False
    graf.y_axis.delete = False
    graf.add_data(Reference(ws, min_col=4, min_row=1, max_row=posledni), titles_from_data=True)
    graf.set_categories(Reference(ws, min_col=1, min_row=2, max_row=posledni))
    ws.add_chart(graf, "F2")
    uprav_vzhled(ws)

def zapis_chyby(wb, chyby):
    ws = wb.create_sheet("Chyby")
    ws.append(["Objednávka", "Důvod"])
    for r, (cislo, duvod) in enumerate(chyby, start=2):
        ws.cell(r, 1, cislo).data_type = "s"
        ws.cell(r, 2, duvod).data_type = "s"
    uprav_vzhled(ws)

def kontrola(cesta, ocekavano):
    ws = load_workbook(cesta)["Data"]
    radky = ws.iter_rows(min_row=2, min_col=5, max_col=6, values_only=True)
    soucet = sum(kusy * cena for kusy, cena in radky)
    if abs(soucet - ocekavano) > 0.005:
        raise ValueError(f"Nesedí tržba: v souboru {soucet}, čekáno {ocekavano}")
    return round(soucet, 2)

def vytvor_report(text, cesta):
    dobre, chyby = nacti(text)
    prehled = souhrn(dobre)
    # doplň
Výstup

Mise je splněná. Doma z kódu uděláš skript, který dostane cesty v příkazové řádce:

python
# report.py
import sys
from pathlib import Path

def main():
    if len(sys.argv) != 3:
        sys.exit("Použití: python report.py export.csv report.xlsx")
    source, target = sys.argv[1], sys.argv[2]
    text = Path(source).read_text(encoding="utf-8-sig")
    result = vytvor_report(text, target)
    print(f"Hotovo: {result['radku']} objednávek, {result['chyb']} chyb, {result['trzba']:.2f} Kč")

if __name__ == "__main__":
    main()
bash
python -m pip install openpyxl
python report.py export_brezen.csv report_brezen.xlsx

Aby se spouštěl sám, nastav ho v Plánovači úloh ve Windows nebo přes cron na Linuxu a macOS.

Pod kapotou · jak z toho udělat spolehlivý nástroj

Nepočítané vzorce. openpyxl standardně nastavuje fullCalcOnLoad, takže desktopový Excel při otevření přepočítá vše. Náhled v poště, v telefonu nebo čtení přes pandas ale uvidí prázdno, protože výsledky v souboru nejsou. Pokud report čte i něco jiného než Excel, zapisuj hodnoty, nebo soubor prožeň přepočtem přes soffice --headless --convert-to xlsx.

Injekce vzorců. data_type = "s" chrání tenhle sešit. Když ale data exportuješ do CSV, ochrana se ztratí a tabulkový procesor text začínající =, +, - nebo @ může znovu vyhodnotit. OWASP to vede jako CSV Injection. ws.append i přiřazení do cell.value udělají z textu s = vzorec stejně, proto kód mise chrání každý text ze vstupu: produkt, pobočku i číslo objednávky v listu Chyby, které prošlo bez převodu.

Atomický zápis. Pád uprostřed wb.save nechá poškozený soubor. Ulož do dočasného souboru ve stejné složce a přejmenuj přes os.replace, které je v rámci jednoho svazku atomické. Čtenář pak vidí buď starou, nebo novou verzi. XLSX je ZIP, takže rozepsaný soubor se neotevře vůbec.

Peníze. float tu stačí, protože jde o přehled a porovnáváme s tolerancí. Pro účetnictví parsuj vstup přes decimal.Decimal a součty zaokrouhluj explicitně. openpyxl Decimal zapíše, ale Excel i tak počítá v IEEE 754 a při čtení dostaneš int nebo float.

Velké exporty. Běžný režim drží každou buňku v paměti jako objekt. Pro statisíce řádků je tu Workbook(write_only=True) a load_workbook(read_only=True). List pak zapisuješ jen přes append, případně s WriteOnlyCell pro styly. List má strop 1 048 576 řádků.

Grafy a verze. Od openpyxl 3.1 se u grafů v Excelu často ztrácejí osy, dokud nenastavíš x_axis.delete = False a y_axis.delete = False, proto jsou v kódu. load_workbook navíc neumí věrně zachovat vše, co v souboru je, takže šablony s makry nebo složitými objekty nepřepisuj a generuj raději nový soubor.

Názvy listů mají nejvýše 31 znaků a nesmí obsahovat \ / ? * [ ] :. Na zakázané znaky openpyxl reaguje ValueError, u příliš dlouhého názvu jen varuje a Excel pak může soubor označit za poškozený. Když list pojmenováváš podle dat, název vždy očisti.