0 dní v řadě · 0 bodů

Užitečné knihovny · 25 min

openpyxl: práce s Excelem

Vytvoříš a přečteš soubor .xlsx, doplníš vzorce, formátování a další listy a dozvíš se, co všechno knihovna neumí.

Hloubka
Úkoly
Lidsky řečeno

Excelový soubor je šanon. V šanonu jsou jednotlivé listy a na každém listu mřížka políček. openpyxl je asistent, který šanon otevře, přepíše políčka podle tvého diktátu a založí ho zpátky do skříně. Samotný Excel k tomu nepotřebuje, a proto ani nic nepočítá. To je důležité, ještě se k tomu vrátíme.

Sešit, list, buňka

Tři slova, která budeš potřebovat. Sešit (workbook) je celý soubor .xlsx. List (worksheet) je jedna záložka v něm. Buňka (cell) je políčko s adresou jako B3: sloupec B, řádek 3.

Doma knihovnu nainstaluješ takto:

bash
pip install openpyxl

Nejkratší cesta: vytvoř sešit, něco do něj zapiš, ulož ho a znovu načti. Soubor vznikne v aktuální složce.

from openpyxl import Workbook, load_workbook

sesit = Workbook()
list1 = sesit.active
list1.title = "Výdaje"

list1["A1"] = "Položka"
list1["B1"] = "Cena"
list1["A2"] = "Káva"
list1["B2"] = 89

sesit.save("vydaje.xlsx")

nacteny = load_workbook("vydaje.xlsx")
print(nacteny.sheetnames)

ws = nacteny["Výdaje"]
print(ws["A2"].value, ws["B2"].value)
print(type(ws["B2"].value).__name__)
Výstup
['Výdaje']
Káva 89
int

sesit.active je list, který je právě otevřený. Nový sešit má jeden. ws["A2"] vrátí objekt buňky a teprve jeho .value je hodnota. Číslo zůstalo číslem, typ se v souboru uchovává.

Na adresu se dá jít i čísly přes ws.cell(row=..., column=...). To se hodí v cyklech. Řádky a sloupce se tady počítají od jedničky, ne od nuly jako všude jinde v Pythonu.

from openpyxl import Workbook
from openpyxl.utils import get_column_letter, column_index_from_string

ws = Workbook().active

ws.cell(row=2, column=3, value="ahoj")
print(ws["C2"].value)
print(ws["C2"].row, ws["C2"].column, ws["C2"].coordinate)

print(get_column_letter(27))
print(column_index_from_string("AA"))
Výstup
ahoj
2 3 C2
AA
27

Celá tabulka najednou

Po jedné buňce se tabulka plnit nemusí. append přidá celý řádek pod poslední použitý. Čtení obstará iter_rows, s values_only=True dostaneš rovnou n-tice hodnot.

from openpyxl import Workbook, load_workbook

prodeje = [
    ("Chléb", 12, 68),
    ("Rohlík", 140, 4),
    ("Koláč", 25, 32),
]

sesit = Workbook()
ws = sesit.active
ws.append(["Produkt", "Kusů", "Cena"])
for radek in prodeje:
    ws.append(radek)
sesit.save("prodeje.xlsx")

ws = load_workbook("prodeje.xlsx").active
print(ws.max_row, ws.max_column, ws.dimensions)

for produkt, kusu, cena in ws.iter_rows(min_row=2, values_only=True):
    print(f"{produkt}: {kusu * cena} Kč")
Výstup
4 3 A1:C4
Chléb: 816 Kč
Rohlík: 560 Kč
Koláč: 800 Kč

Tabulka s hlavičkou se nejlépe čte jako seznam slovníků. Kód pak nezávisí na pořadí sloupců a radek["Cena"] je čitelnější než radek[2].

from openpyxl import Workbook, load_workbook

sesit = Workbook()
ws = sesit.active
for radek in [["Jméno", "Město"], ["Eva", "Brno"], ["Petr", None], ["Jana", "Cheb"]]:
    ws.append(radek)
sesit.save("lide.xlsx")

ws = load_workbook("lide.xlsx").active
radky = ws.iter_rows(values_only=True)
hlavicka = next(radky)
lide = [dict(zip(hlavicka, radek)) for radek in radky]

print(lide[0])
print(lide[1])
Výstup
{'Jméno': 'Eva', 'Město': 'Brno'}
{'Jméno': 'Petr', 'Město': None}

Prázdná buňka se čte jako None. Na to pamatuj při sčítání, None se k číslu přičíst nedá.

from openpyxl import Workbook

ws = Workbook().active
ws["A1"] = "data"
print(ws.max_row, ws.max_column)

if ws["Z100"].value is None:
    print("Buňka je prázdná.")

print(ws.max_row, ws.max_column)
Výstup
1 1
Buňka je prázdná.
100 26
Úkol

Ulož tabulku

Doplň funkci uloz_tabulku(cesta, hlavicka, radky). Vytvoří nový sešit, na první řádek dá hlavičku, pod ni všechny řádky a uloží ho do souboru cesta.

from openpyxl import Workbook

def uloz_tabulku(cesta, hlavicka, radky):
    sesit = Workbook()
    # doplň
Výstup
Úkol

Součet sloupce podle názvu

Doplň funkci soucet_sloupce(cesta, nazev). Otevře sešit, na prvním řádku najde sloupec s názvem nazev a vrátí součet jeho čísel. Prázdné buňky přeskočí. Když sloupec neexistuje, vyvolá KeyError.

from openpyxl import load_workbook

def soucet_sloupce(cesta, nazev):
    ws = load_workbook(cesta).active
    return 0
Výstup

Vzorce, data a proč je tam None

Vzorec zapíšeš jako text začínající =. Názvy funkcí musí být anglicky a argumenty se oddělují čárkou, i když český Excel ukazuje SUMA a středníky. Do souboru se ukládá jazykově neutrální podoba a Excel si ji přeloží sám.

from openpyxl import Workbook, load_workbook

sesit = Workbook()
ws = sesit.active
ws.append(["Kusů", "Cena", "Celkem"])
ws.append([2, 100, "=A2*B2"])
ws.append([5, 30, "=A3*B3"])
ws["C4"] = "=SUM(C2:C3)"
sesit.save("vzorce.xlsx")

print(load_workbook("vzorce.xlsx").active["C4"].value)
print(load_workbook("vzorce.xlsx", data_only=True).active["C4"].value)
Výstup
=SUM(C2:C3)
None

První řádek vypsal vzorec, druhý None. Parametr data_only=True žádá výsledek uložený v souboru, jenže openpyxl vzorce nepočítá a žádný výsledek tam tedy není. Doplní ho až Excel nebo LibreOffice, když soubor otevřou a uloží. Potřebuješ-li číslo v Pythonu, spočítej si ho v Pythonu.

Data a časy se ukládají jako čísla se speciálním formátem. openpyxl to zařídí za tebe a při čtení vrátí datetime, i když jsi zapsal jen date.

from datetime import date
from openpyxl import Workbook, load_workbook

sesit = Workbook()
ws = sesit.active
ws["A1"] = date(2026, 3, 1)
ws["B1"] = 1234.5
ws["B1"].number_format = '#,##0.00 "Kč"'
ws["C1"] = 0.21
ws["C1"].number_format = "0%"
sesit.save("formaty.xlsx")

ws = load_workbook("formaty.xlsx").active
print(repr(ws["A1"].value), ws["A1"].number_format, ws["A1"].is_date)
print(ws["B1"].value, ws["B1"].number_format)
print(ws["C1"].value, ws["C1"].number_format)
Výstup
datetime.datetime(2026, 3, 1, 0, 0) yyyy-mm-dd True
1234.5 #,##0.00 "Kč"
0.21 0%

number_format mění jen to, jak se číslo zobrazí. Hodnota v buňce zůstává 0.21, Excel ukáže 21%.

Kvíz

Skript zapíše ws["A1"] = "=1+1", soubor uloží a hned ho načte přes load_workbook(..., data_only=True). Co bude v A1?

Vzhled a práce s více listy

Formátování se skládá z malých objektů: Font, PatternFill a Alignment. Přiřadíš je buňce. Šířka sloupce a ukotvení záhlaví se nastavují na listu.

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

sesit = Workbook()
ws = sesit.active
ws.append(["Produkt", "Tržba"])
ws.append(["Chléb", 816])
ws.append(["Dlouhý název výrobku", 560])

for bunka in ws[1]:
    bunka.font = Font(bold=True, color="FFFFFF")
    bunka.fill = PatternFill("solid", fgColor="222222")
    bunka.alignment = Alignment(horizontal="center")

for sloupec in ws.columns:
    delka = max(len(str(b.value)) for b in sloupec)
    ws.column_dimensions[sloupec[0].column_letter].width = delka + 2

ws.freeze_panes = "A2"
ws.auto_filter.ref = ws.dimensions
sesit.save("hezka.xlsx")

ws = load_workbook("hezka.xlsx").active
print(ws["A1"].font.bold, ws["A1"].fill.fgColor.rgb, ws["A1"].alignment.horizontal)
print(ws.column_dimensions["A"].width, ws.freeze_panes, ws.auto_filter.ref)
Výstup
True 00222222 center
22.0 A2 A1:B3

Barvy se píšou jako šestice šestnáctkových číslic bez #. Při čtení přibude na začátek průhlednost, proto 00222222. ws[1] je celý první řádek, ws["A"] celý sloupec a ws["A1:B2"] výřez.

Další listy přidáš přes create_sheet, volitelné číslo určí jejich pozici. Stejně snadno upravíš soubor, který už existuje: načteš ho, změníš a uložíš.

from openpyxl import Workbook, load_workbook

sesit = Workbook()
sesit.active.title = "Leden"
sesit["Leden"].append(["Tržba", 1000])
sesit.save("rok.xlsx")

sesit = load_workbook("rok.xlsx")
unor = sesit.create_sheet("Únor")
unor.append(["Tržba", 1200])
sesit.create_sheet("Přehled", 0)
sesit["Leden"]["B1"] = 1100
sesit.save("rok.xlsx")

sesit = load_workbook("rok.xlsx")
print(sesit.sheetnames)
for ws in sesit.worksheets:
    print(ws.title, ws["B1"].value)

del sesit["Přehled"]
print(sesit.sheetnames)
Výstup
['Přehled', 'Leden', 'Únor']
Přehled None
Leden 1100
Únor 1200
['Leden', 'Únor']
Úkol

Hezké záhlaví

Doplň funkci uprav_zahlavi(ws). Všem buňkám prvního řádku nastaví tučné písmo a list ukotví tak, aby první řádek zůstal při posouvání vidět.

from openpyxl import Workbook
from openpyxl.styles import Font

def uprav_zahlavi(ws):
    pass
Výstup

Starý formát .xls openpyxl neumí, zvládá .xlsx a .xlsm. A pokud chceš tabulku jen načíst a analyzovat, je pohodlnější pandas.read_excel, který openpyxl používá uvnitř. Celý report s daty a formátováním si postavíš v misi na konci modulu.

Kvíz

Jak se dostaneš na buňku B1 přes ws.cell?

Pod kapotou · co je uvnitř .xlsx a kde to bolí

Formát. .xlsx je ZIP s XML podle standardu Office Open XML. Když se něco rozbije, rozbal ho a podívej se dovnitř (ukázka níže). Proto taky load_workbook na obyčejném textu s příponou .xlsx hlásí BadZipFile.

Velké soubory. Běžný režim drží každou buňku jako objekt, takže paměť roste s počtem buněk. load_workbook(f, read_only=True) čte XML proudově, Workbook(write_only=True) řádky rovnou zapisuje do dočasného souboru a dovolí jen append a jediné save. V režimu čtení max_row závisí na značce dimension v souboru a může být None, takže iteruj a sešit zavři přes wb.close(), jinak zůstane otevřený souborový deskriptor.

Injekce vzorců. Řetězec začínající = se uloží jako vzorec. Export uživatelských dat tak může podstrčit třeba =HYPERLINK(...), který po kliknutí vynese data. Řešení je nastavit cell.data_type = "s", pak Excel obsah ukáže jako text. Při exportu do CSV žádná taková pojistka není a riskantní jsou i začátky +, - a @.

Data. Excel počítá dny od konce roku 1899 a záměrně přebírá chybu z Lotusu 1-2-3: rok 1900 považuje za přestupný, takže existuje sériové číslo 60 pro neexistující 29. 2. 1900. Sešity z Macu mohou používat epochu 1904 (wb.epoch). Časová pásma xlsx nezná, proto datetime s tzinfo openpyxl odmítne a musíš ho převést na naivní.

Neplatné znaky. Řídicí znaky jako \x01 nejsou v XML 1.0 povolené a zápis vyvolá IllegalCharacterError. Data z logů a scrapingu nejdřív očisti, vzor je v openpyxl.cell.cell.ILLEGAL_CHARACTERS_RE.

import zipfile
from openpyxl import Workbook, load_workbook

sesit = Workbook()
ws = sesit.active
ws["A1"] = 2
ws["A2"] = "=A1*21"
ws["A3"] = "=A1*21"
ws["A3"].data_type = "s"
sesit.save("uvnitr.xlsx")

with zipfile.ZipFile("uvnitr.xlsx") as z:
    nazvy = z.namelist()
    xml = z.read("xl/worksheets/sheet1.xml").decode("utf-8")

print("xl/workbook.xml" in nazvy, "xl/worksheets/sheet1.xml" in nazvy)
print("<f>A1*21</f>" in xml, xml.count("<f>"))

nacteny = load_workbook("uvnitr.xlsx").active
print(nacteny["A2"].data_type, nacteny["A3"].data_type)
Výstup
True True
True 1
f s