Hlavní navigace

Tvorba sešitů ve formátu XLSX v Pythonu: knihovna XlsxWriter (pokračování)

30. 11. 2021
Doba čtení: 37 minut

Sdílet

 Autor: Depositphotos
Dnes se zaměříme na některé pokročilejší vlastnosti knihovny XlsxWriter: na vytváření sloučených buněk, podmíněné formáty buněk, definovaná jména buněk, podporu pro tvorbu automatických filtrů atd.

Obsah

1. Tvorba sešitů ve formátu XLSX v Pythonu: knihovna XlsxWriter (pokračování)

2. Komentáře přidané k buňkám

3. Alternativní adresování komentářů

4. Větší množství listů v sešitu

5. Pojmenování listů, testování unikátního jména listů

6. Sloučení buněk

7. Překryv sloučených oblastí?

8. Sloučení buněk přes více sloupců a řádků

9. Alternativní určení oblasti buněk, které se mají spojit

10. Podmíněné formátování buněk

11. Další možnosti podmíněného formátování: hodnoty ležící v daném intervalu

12. Podmíněný formát vztažený k více buňkám

13. Automatické filtry

14. Další možnosti automatických filtrů

15. Uzamčení buněk

16. Validace dat zadávaných do buněk

17. Sešit obsahující buňku s validátorem

18. Další objekty vkládané do sešitů

19. Repositář s demonstračními příklady

20. Odkazy na Internetu

1. Tvorba sešitů ve formátu XLSX v Pythonu: knihovna XlsxWriter (pokračování)

V prvním článku jsme se seznámili pouze se základními možnostmi poskytovanými knihovnou XlsxWriter určenou pro tvorbu souborů ve formátu XLSX. Z tohoto důvodu se dnes zaměříme na některé další vybrané (pokročilejší) vlastnosti, například na vytváření sloučených buněk (což je velmi často požadováno) v sešitech, podmíněné formáty buněk, definovaná jména buněk, podporu pro validaci vstupních dat a samozřejmě nezapomeneme ani na podporu při vkládání různých objektů do sešitů – týká se to například obrázků, formátovaných textů (což znamená, že část textu v buňce může mít odlišný formát), přidávání komentářů k buňkám atd. Sešity vytvořené dnes ukázanými demonstračními příklady je možné otevřít jak v LibreOffice (Calc), tak i v Microsoft Office (Excel), ale například i v Gnumericu (který ovšem neumí zobrazit například formát komentářů, některé varianty podmíněného formátování apod.).

Poznámka: v dnešním článku se bude používat následující terminologie: workbook=sešit, worksheet=list, cell=buňka.

Demonstrační příklady, které si ukážeme v rámci navazujících kapitol, budou založeny na skriptu, po jehož spuštění se vytvoří nový sešit nazvaný „example14.xlsx“. Tento sešit by měl obsahovat jediný list, v němž bude uložena tabulka s ostylovanými buňkami – nastaven bude styl prvních řádků s popisem tabulky a poté první i druhý sloupec (červená barva resp. modifikovaný numerický styl). Zdrojový kód tohoto příkladu vypadá následovně:

#!/usr/bin/env python3
# vim: set fileencoding=utf-8
 
"""Vytvoření sešitu s delší tabulkou, buňky se specifikací číselného formátu."""
 
import xlsxwriter
 
# vytvoření objektu reprezentujícího celý sešit
with xlsxwriter.Workbook('example14.xlsx') as workbook:
    # vložení nového listu do sešitu
    worksheet = workbook.add_worksheet()
 
    # definice nového stylu
    bold_style = workbook.add_format()
    bold_style.set_bold()
    bold_style.set_font_color('blue')
 
    # definice dalšího nového stylu
    red_style = workbook.add_format()
    red_style.set_font_color('red')
 
    # definice formátu čísel
    numeric_format = workbook.add_format({'num_format': '0.0000'})
 
    # nastavení šířky sloupců a stylu
    worksheet.set_column('A:A', 8, red_style)
    worksheet.set_column('B:B', 14, numeric_format)
    worksheet.set_column('C:Z', 2)
 
    # styl pro první řádek
    worksheet.set_row(0, 20, bold_style)
 
    # buňky s textem
    worksheet.write_string('A1', 'x')
    worksheet.write_string('B1', '1/x')
 
    # buňky s numerickými hodnotami
    for x in range(1, 21):
        worksheet.write_number(x, 0, x)
        worksheet.write_number(x, 1, 1.0/x)
 
    # sešit bude uzavřen automaticky

Výsledek po otevření v Gnumericu:

Obrázek 1: Vygenerovaný sešit po otevření v tabulkovém procesoru.

2. Komentáře přidané k buňkám

K jednotlivým buňkám v sešitu je možné přidat komentáře. Takové buňky bývají po otevření v tabulkovém procesoru vizuálně označeny, typicky červeným trojúhelníkem v jednom z rohů buňky. Komentáře se k buňkám přidávají metodou Worksheet.write_comment, které lze předat (v první variantě) tyto argumenty:

worksheet.write_comment(řádek, sloupec, "text komentáře")

V následujícím skriptu jsou přidány komentáře ke všem buňkám v pravém (druhém) sloupci tabulky:

#!/usr/bin/env python3
# vim: set fileencoding=utf-8
 
"""Tabulka s komentáři přiřazenými k vybraným buňkám."""
 
import xlsxwriter
 
# vytvoření objektu reprezentujícího celý sešit
with xlsxwriter.Workbook('example21.xlsx') as workbook:
    # vložení nového listu do sešitu
    worksheet = workbook.add_worksheet()
 
    # definice nového stylu
    bold_style = workbook.add_format()
    bold_style.set_bold()
    bold_style.set_font_color('blue')
 
    # definice dalšího nového stylu
    red_style = workbook.add_format()
    red_style.set_font_color('red')
 
    # definice formátu čísel
    numeric_format = workbook.add_format({'num_format': '0.0000'})
 
    # nastavení šířky sloupců a stylu
    worksheet.set_column('A:A', 8, red_style)
    worksheet.set_column('B:B', 14, numeric_format)
    worksheet.set_column('C:Z', 2)
 
    # styl pro první řádek
    worksheet.set_row(0, 20, bold_style)
 
    # buňky s textem
    worksheet.write_string('A1', 'x')
    worksheet.write_string('B1', '1/x')
 
    # buňky s numerickými hodnotami
    for x in range(1, 21):
        worksheet.write_number(x, 0, x)
        worksheet.write_number(x, 1, 1.0/x)
        worksheet.write_comment(x, 1, "Výsledek výrazu 1/{}".format(x))
 
    # sešit bude uzavřen automaticky

Výsledek po zobrazení v tabulkovém procesoru:

Obrázek 2: Vygenerovaný sešit po otevření v tabulkovém procesoru.

Navíc je možné určit, že se komentáře mají zobrazit ihned po otevření sešitu v tabulkovém procesoru. Tuto vlastnost sešitu (která však není podporována všemi tabulkovými procesory) můžeme zapnout následující metodou:

worksheet.show_comments()

Výsledný skript se od předchozího demonstračního příkladu liší jen nepatrně – přidáním volání výše uvedené metody:

#!/usr/bin/env python3
# vim: set fileencoding=utf-8
 
"""Tabulka s komentáři přiřazenými k vybraným buňkám."""
 
import xlsxwriter
 
# vytvoření objektu reprezentujícího celý sešit
with xlsxwriter.Workbook('example22.xlsx') as workbook:
    # vložení nového listu do sešitu
    worksheet = workbook.add_worksheet()
 
    # definice nového stylu
    bold_style = workbook.add_format()
    bold_style.set_bold()
    bold_style.set_font_color('blue')
 
    # definice dalšího nového stylu
    red_style = workbook.add_format()
    red_style.set_font_color('red')
 
    # definice formátu čísel
    numeric_format = workbook.add_format({'num_format': '0.0000'})
 
    # nastavení šířky sloupců a stylu
    worksheet.set_column('A:A', 8, red_style)
    worksheet.set_column('B:B', 14, numeric_format)
    worksheet.set_column('C:Z', 2)
 
    # styl pro první řádek
    worksheet.set_row(0, 20, bold_style)
 
    # buňky s textem
    worksheet.write_string('A1', 'x')
    worksheet.write_string('B1', '1/x')
 
    # buňky s numerickými hodnotami
    for x in range(1, 21):
        worksheet.write_number(x, 0, x)
        worksheet.write_number(x, 1, 1.0/x)
        worksheet.write_comment(x, 1, "Výsledek výrazu 1/{}".format(x))
 
    # komentáře by měly být zobrazeny ihned po otevření sešitu
    worksheet.show_comments()
 
    # sešit bude uzavřen automaticky

Opět se podívejme, jak vypadá výsledek po zobrazení v tabulkovém procesoru:

Obrázek 3: Vygenerovaný sešit po otevření v tabulkovém procesoru (Gnumeric tuto vlastnost nepodporuje).

3. Alternativní adresování komentářů

V předchozí kapitole se pro přidání komentářů k buňkám použila následující varianta metody Worksheet.write_comment. V této variantě bylo nutné specifikovat řádek a sloupec buňky, do které se měl komentář přidat. Adresa buňky tedy byla určena dvojicí celočíselných indexů, přičemž tyto indexy začínaly od nuly:

worksheet.write_comment(řádek, sloupec, "text komentáře")

Podobně jako u mnoha dalších metod, které nějakým způsobem pracují s adresami buněk, je i metodu Worksheet.write_comment možné použít v alternativní podobě, v níž je adresa buňky specifikována stylem „A1“, tedy stejně, jako je tomu v tabulkových procesorech (ty ovšem většinou podporují i styl R1C1):

worksheet.write_comment('A1', "text komentáře")

Tento druhý způsob adresace se hodí pouze v některých případech, například pro přidání komentářů k buňkám, které tvoří titulek tabulky. A právě tento způsob je ukázán v dalším demonstračním příkladu, v němž jsou komentáře přidány k oběma „titulkovým“ buňkám:

#!/usr/bin/env python3
# vim: set fileencoding=utf-8
 
"""Tabulka s komentáři přiřazenými k vybraným buňkám."""
 
import xlsxwriter
 
# vytvoření objektu reprezentujícího celý sešit
with xlsxwriter.Workbook('example23.xlsx') as workbook:
    # vložení nového listu do sešitu
    worksheet = workbook.add_worksheet()
 
    # definice nového stylu
    bold_style = workbook.add_format()
    bold_style.set_bold()
    bold_style.set_font_color('blue')
 
    # definice dalšího nového stylu
    red_style = workbook.add_format()
    red_style.set_font_color('red')
 
    # definice formátu čísel
    numeric_format = workbook.add_format({'num_format': '0.0000'})
 
    # nastavení šířky sloupců a stylu
    worksheet.set_column('A:A', 8, red_style)
    worksheet.set_column('B:B', 14, numeric_format)
    worksheet.set_column('C:Z', 2)
 
    # styl pro první řádek
    worksheet.set_row(0, 20, bold_style)
 
    # buňky s textem
    worksheet.write_string('A1', 'x')
    worksheet.write_string('B1', '1/x')
 
    worksheet.write_comment('A1', "Vstupní hodnoty")
    worksheet.write_comment('B1', "Vypočtené hodnoty")
 
    # buňky s numerickými hodnotami
    for x in range(1, 21):
        worksheet.write_number(x, 0, x)
        worksheet.write_number(x, 1, 1.0/x)
        worksheet.write_comment(x, 1, "Výsledek výrazu 1/{}".format(x))
 
    # komentáře by měly být zobrazeny ihned po otevření sešitu
    worksheet.show_comments()
 
    # sešit bude uzavřen automaticky

Výsledek:

Obrázek 4: Vygenerovaný sešit po otevření v tabulkovém procesoru.

4. Větší množství listů v sešitu

Všechny sešity, které jsme prozatím vytvořili, obsahovaly pouze jediný list. V praxi je ovšem mnohdy vyžadováno, aby se do sešitu vložilo větší množství listů. To pochopitelně není žádný nepřekonatelný problém, protože nový list lze vytvořit metodou Workbook.add_worksheet(), která vrací objekt využívaný pro přidání objektů do listu, popř. pro zápis dat do jednotlivých buněk, které list tvoří. Sešit s větším množstvím listů lze tedy vytvořit naprosto triviálním způsobem:

#!/usr/bin/env python3
# vim: set fileencoding=utf-8
 
"""Vytvoření sešitu s několika listy."""
 
import xlsxwriter
 
# vytvoření objektu reprezentujícího celý sešit
with xlsxwriter.Workbook('example24.xlsx') as workbook:
    # vytvoření několika listů
    for name in range(4):
        # vložení nového listu do sešitu
        worksheet = workbook.add_worksheet()
 
    # sešit bude uzavřen automaticky

Obrázek 5: Sešit s větším množstvím listů.

Poznámka: na tomto místě je vhodné upozornit na to, že ačkoli má například Microsoft Excel některá (mnohdy zásadní) omezení, tak počet listů není limitován. Resp. přesněji řečeno omezeni jsme pouze volnou kapacitou operační paměti.

5. Pojmenování listů, testování unikátního jména listů

Listy, které tvoří celý sešit, je vhodné pojmenovat. Toho docílíme snadno, protože nám již známá metoda Worksheet.add_worksheet() akceptuje i jméno listu, což je řetězec, jehož obsah je však limitován (viz další text). Toto chování metody Worksheet.add_worksheet() si můžeme snadno ověřit:

#!/usr/bin/env python3
# vim: set fileencoding=utf-8
 
"""Vytvoření sešitu s několika listy."""
 
import xlsxwriter
 
# vytvoření objektu reprezentujícího celý sešit
with xlsxwriter.Workbook('example25.xlsx') as workbook:
    # vytvoření několika listů
    for name in ("První", "Druhý", "Třetí", "Čtvrtý"):
        # vložení nového listu do sešitu
        worksheet = workbook.add_worksheet(name)
 
    # sešit bude uzavřen automaticky

Obrázek 6: Sešit s větším množstvím explicitně pojmenovaných listů.

Poznámka: je vhodné, aby jména listů obsahovala maximálně 31 znaků (i když tento limit ve vlastním formátu neexistuje, jedná se o omezení GUI). Dále je nutné si uvědomit, že jména listů jsou použita jako identifikátory, například při adresaci buněk atd. Z tohoto důvodu existují i další omezení na jména listů. Například by se ve jménu neměly vyskytovat znaky „\“, „/“, „?“, „*“, „[“ a „]“. Dále by jména neměla začínat nebo končit apostrofem (protože jména sešitů s mezerami se zapisují do apostrofů) a konečně (neověřeno) by jméno sešitu nemělo znít „history“, „History“ ani obsahovat další varianty.

Knihovna XlsxWriter taktéž kontroluje, zda jsou jména listů unikátní, tj. zda se nesnažíme vytvořit dva listy se shodným jménem. Toto chování si můžeme snadno ověřit spuštěním následujícího demonstračního příkladu:

#!/usr/bin/env python3
# vim: set fileencoding=utf-8
 
"""Vytvoření sešitu s několika listy."""
 
import xlsxwriter
 
# vytvoření objektu reprezentujícího celý sešit
with xlsxwriter.Workbook('example26.xlsx') as workbook:
    # vytvoření několika listů
    for name in ("První", "Druhý", "Třetí", "Čtvrtý", "První"):
        # vložení nového listu do sešitu
        worksheet = workbook.add_worksheet(name)
 
    # sešit bude uzavřen automaticky

Po spuštění tohoto skriptu se bude detekovat pokus o vytvoření dvou listů se stejným jménem:

Traceback (most recent call last):
  File "26_more_worksheets_names.py", line 13, in <module>
    worksheet = workbook.add_worksheet(name)
  File "/home/ptisnovs/.local/lib/python3.6/site-packages/xlsxwriter/workbook.py", line 189, in add_worksheet
    return self._add_sheet(name, worksheet_class=worksheet_class)
  File "/home/ptisnovs/.local/lib/python3.6/site-packages/xlsxwriter/workbook.py", line 759, in _add_sheet
    name = self._check_sheetname(name, isinstance(worksheet, Chartsheet))
  File "/home/ptisnovs/.local/lib/python3.6/site-packages/xlsxwriter/workbook.py", line 831, in _check_sheetname
    sheetname)
xlsxwriter.exceptions.DuplicateWorksheetName: Sheetname 'První', with case ignored, is already in use.

6. Sloučení buněk

V sešitech je možné sloučit buňky ležící vedle sebe na stejném řádku, pod sebou ve stejném sloupci, nebo v případě, že tvoří spojitou obdélníkovou oblast. Sloučení buněk je pochopitelně podporováno i knihovnou XlsxWriter. Sloučenou buňku je v této knihovně možné vytvořit metodou nazvanou Worksheet.merge_range, které se předává definice oblasti se sloučenými buňkami, obsah sloučené buňky a popř. i specifikace formátu sloučené buňky (protože jinak by nebylo zřejmé, který formát se má použít, pokud se slučují buňky s různými formáty).

Vytvoření buňky vzniklé sloučením více buněk na stejném řádku, konkrétně buněk B2, C2 a D2, by mohlo být provedeno takto:

worksheet.merge_range('B2:D2', 'Sloučená buňka', format1)

Následuje podobný příklad, ovšem nyní slučující buňky ležící pod sebou ve stejném sloupci. Konkrétně se jedná o buňky F2, F3 a F4:

worksheet.merge_range('F2:F4', 'Sloučená\nbuňka', format2)

V pořadí již dvacátém sedmém demonstračním příkladu je vytvořen sešit s jediným listem. Tento list obsahuje dvojici sloučených buněk, přičemž každá z těchto buněk má odlišný styl zobrazení (formát):

#!/usr/bin/env python3
# vim: set fileencoding=utf-8
 
"""Vytvoření sešitu se sloučenými buňkami."""
 
import xlsxwriter
 
# vytvoření objektu reprezentujícího celý sešit
with xlsxwriter.Workbook('example27.xlsx') as workbook:
    # vložení nového listu do sešitu
    worksheet = workbook.add_worksheet()
 
    # první formát použitý pro zvýraznění sloučené buňky
    format1 = workbook.add_format({
        'bold': 1,
        'border': 1,
        'align': 'center',
        'valign': 'vcenter',
        'fg_color': '#ff8080'})
 
    # druhý formát použitý pro zvýraznění sloučené buňky
    format2 = workbook.add_format({
        'bold': 1,
        'border': 1,
        'align': 'center',
        'valign': 'vcenter',
        'fg_color': '#80ff80'})
 
    # sloučená buňka (přes více sloupců)
    worksheet.merge_range('B2:D2', 'Sloučená buňka', format1)
 
    # sloučená buňka (přes více řádků)
    worksheet.merge_range('F2:F4', 'Sloučená\nbuňka', format2)
 
    # sešit bude uzavřen automaticky

Výsledek může po otevření sešitu v tabulkovém procesoru vypadat takto:

Obrázek 7: Sešit se sloučenými buňkami.

7. Překryv sloučených oblastí?

Zdánlivě nic nám nebrání v tom, abychom vytvořili sloučené buňky, které se budou překrývat. Například se může jednat o pokus o vytvoření sloučené buňky ze základních buněk B2, C2 a D2 a následně o sloučení buněk B2, B3 a B4:

# sloučená buňka
worksheet.merge_range('B2:D2', 'Sloučená buňka', format1)
 
# sloučená buňka
worksheet.merge_range('B2:B4', 'Sloučená\nbuňka', format2)

Tento pokus sice povede k vytvoření souboru se sešitem, ovšem tabulkové procesory většinou takto sloučené buňky odmítnou načíst, což je ostatně patrné i z následujícího screenshotu:

Obrázek 8: Výsledek pokusu o načtení sešitu se sloučenými oblastmi, které se překrývají.

Následuje úplný zdrojový kód s výše uvedenými sloučenými oblastmi:

#!/usr/bin/env python3
# vim: set fileencoding=utf-8
 
"""Pokus o vytvoření sešitu se sloučenými buňkami."""
 
import xlsxwriter
 
# vytvoření objektu reprezentujícího celý sešit
with xlsxwriter.Workbook('example28.xlsx') as workbook:
    # vložení nového listu do sešitu
    worksheet = workbook.add_worksheet()
 
    # první formát použitý pro zvýraznění sloučené buňky
    format1 = workbook.add_format({
        'bold': 1,
        'border': 1,
        'align': 'center',
        'valign': 'vcenter',
        'fg_color': '#ff8080'})
 
    # druhý formát použitý pro zvýraznění sloučené buňky
    format2 = workbook.add_format({
        'bold': 1,
        'border': 1,
        'align': 'center',
        'valign': 'vcenter',
        'fg_color': '#80ff80'})
 
    # sloučená buňka
    worksheet.merge_range('B2:D2', 'Sloučená buňka', format1)
 
    # sloučená buňka
    worksheet.merge_range('B2:B4', 'Sloučená\nbuňka', format2)
 
    # sešit bude uzavřen automaticky

8. Sloučení buněk přes více sloupců a řádků

Knihovna XlsxWriter, ostatně stejně jako většina tabulkových procesorů, podporuje sloučení buněk přes větší množství sloupců a řádků. Slučované buňky tedy budou vždy tvořit obdélníkovou oblast. Sloučení celé oblasti od B2 do D4 je realizováno v následujícím demonstračním příkladu:

#!/usr/bin/env python3
# vim: set fileencoding=utf-8
 
"""Vytvoření sešitu se sloučenými buňkami."""
 
import xlsxwriter
 
# vytvoření objektu reprezentujícího celý sešit
with xlsxwriter.Workbook('example29.xlsx') as workbook:
    # vložení nového listu do sešitu
    worksheet = workbook.add_worksheet()
 
    # formát použitý pro zvýraznění sloučené buňky
    format1 = workbook.add_format({
        'bold': 1,
        'border': 1,
        'align': 'center',
        'valign': 'vcenter',
        'fg_color': '#ff8080'})
 
    # sloučená buňka
    worksheet.merge_range('B2:D4', 'Sloučená buňka', format1)
 
    # sešit bude uzavřen automaticky

Výsledek po zobrazení v tabulkovém procesoru:

Obrázek 9: Sešit se sloučenou buňkou, která vznikla sloučením obdélníkové oblasti.

9. Alternativní určení oblasti buněk, které se mají spojit

Mnohdy se setkáme s požadavkem, aby určení slučované oblasti bylo provedeno programově (tedy ve skriptu). V takovém případě je manipulace s adresami buněk, resp. s rozsahem buněk ve formátu „A1:D4“, zbytečně komplikovaná (a výpočty navíc nemusí být zcela triviální). Alternativní způsob definice celé oblasti spočívá v tom, že se specifikuje index řádku a sloupce první buňky (indexuje se od nuly) a index řádku a sloupce buňky poslední. Tento způsob ovšem nemusí být příliš přehledný:

worksheet.merge_range(řádek, sloupec, řádek, sloupec, obsah sloučené buňky, formát_sloučené_buňky)

Skript, který byl uveden v sedmé kapitole, tedy můžeme upravit do následující podoby:

#!/usr/bin/env python3
# vim: set fileencoding=utf-8
 
"""Vytvoření sešitu se sloučenými buňkami."""
 
import xlsxwriter
 
# vytvoření objektu reprezentujícího celý sešit
with xlsxwriter.Workbook('example30.xlsx') as workbook:
    # vložení nového listu do sešitu
    worksheet = workbook.add_worksheet()
 
    # první formát použitý pro zvýraznění sloučené buňky
    format1 = workbook.add_format({
        'bold': 1,
        'border': 1,
        'align': 'center',
        'valign': 'vcenter',
        'fg_color': '#ff8080'})
 
    # druhý formát použitý pro zvýraznění sloučené buňky
    format2 = workbook.add_format({
        'bold': 1,
        'border': 1,
        'align': 'center',
        'valign': 'vcenter',
        'fg_color': '#80ff80'})
 
    # sloučená buňka
    worksheet.merge_range(1, 2, 1, 4, 'Sloučená buňka', format1)
 
    # sloučená buňka
    worksheet.merge_range(1, 6, 4, 6, 'Sloučená\nbuňka', format2)
 
    # sešit bude uzavřen automaticky

Obrázek 10: Sešit se sloučenými buňkami.

10. Podmíněné formátování buněk

Již v úvodním článku o knihovně XlsxWriter jsme si ukázali, jakým způsobem je možné definovat formát buněk. Připomeňme si, že formát je specifikován formou slovníku a tvoří se metodou Workbook.add_format (přičemž celkový počet formátů je většinou tabulkovými procesory omezen):

# definice nového stylu
bold_style = workbook.add_format({'bold': True})

Formát lze předat v posledním parametry nám již velmi dobře známé metody Worksheet.write volané při přidávání další buňky do sešitu:

worksheet.write('A1', 'x', bold_style)

Tabulkové procesory ovšem většinou podporují i podmíněné formátování, což jsou formáty, které jsou na buňku aplikovány jen tehdy, pokud je splněna (či naopak nesplněna) nějaká podmínka. Tímto způsobem je možné například zvýraznit nějak důležité či chybné popř. zcela chybějící hodnoty atd. I podmíněné formátování je knihovnou XlsxWriter podporováno, takže si v dalším textu popíšeme základní způsoby práce s takovými formáty.

Formát buňky lze specifikovat s podmínkou, která se vztahuje na hodnotu uloženou v buňce. Typickým příkladem může být obarvení těch buněk, jejichž hodnota je větší než zadaná konstanta:

# formát s podmínkou
conditional_format = {
         'type':     'cell',
         'criteria': '>=',
         'value':    0.2,
         'format':   red_style
        }

Tento formát můžeme nastavit pro druhý sloupec tabulky, a to konkrétně následovně:

# nastavení podmíněného formátu u druhého sloupce hodnot
worksheet.conditional_format('B2:B21', conditional_format)

Vše bude ukázáno v následujícím skriptu:

#!/usr/bin/env python3
# vim: set fileencoding=utf-8
 
"""Podmíněné formátování."""
 
import xlsxwriter
 
# vytvoření objektu reprezentujícího celý sešit
with xlsxwriter.Workbook('example31.xlsx') as workbook:
    # vložení nového listu do sešitu
    worksheet = workbook.add_worksheet()
 
    # definice nového stylu
    bold_style = workbook.add_format()
    bold_style.set_bold()
    bold_style.set_font_color('blue')
 
    # definice dalšího nového stylu
    red_style = workbook.add_format()
    red_style.set_font_color('red')
 
    # definice formátu čísel
    numeric_format = workbook.add_format({'num_format': '0.0000'})
 
    # nastavení šířky sloupců a stylu
    worksheet.set_column('A:A', 8)
    worksheet.set_column('B:B', 14, numeric_format)
    worksheet.set_column('C:Z', 2)
 
    # styl pro první řádek
    worksheet.set_row(0, 20, bold_style)
 
    # buňky s textem
    worksheet.write_string('A1', 'x')
    worksheet.write_string('B1', '1/x')
 
    # buňky s numerickými hodnotami
    for x in range(1, 21):
        worksheet.write_number(x, 0, x)
        worksheet.write_number(x, 1, 1.0/x)
 
    # formát s podmínkou
    conditional_format = {
             'type':     'cell',
             'criteria': '>=',
             'value':    0.2,
             'format':   red_style
            }
 
    # nastavení podmíněného formátu u druhého sloupce hodnot
    worksheet.conditional_format('B2:B21', conditional_format)
 
    # sešit bude uzavřen automaticky

S tímto výsledkem:

Obrázek 11: Podmíněný formát buněk ve druhém sloupci.

Alternativně je – podobně jako tomu bylo v případě spojování buněk – možné určit oblast pro automatický formát dvojicí adres řádek:sloupec, a to konkrétně následujícím způsobem:

worksheet.conditional_format(řádek, sloupec, řádek, sloupec, conditional_format)

Opět si pro úplnost ukažme celý skript, který tuto variantu použije:

#!/usr/bin/env python3
# vim: set fileencoding=utf-8
 
"""Podmíněné formátování, alternativní adresování buněk."""
 
import xlsxwriter
 
# vytvoření objektu reprezentujícího celý sešit
with xlsxwriter.Workbook('example32.xlsx') as workbook:
    # vložení nového listu do sešitu
    worksheet = workbook.add_worksheet()
 
    # definice nového stylu
    bold_style = workbook.add_format()
    bold_style.set_bold()
    bold_style.set_font_color('blue')
 
    # definice dalšího nového stylu
    red_style = workbook.add_format()
    red_style.set_font_color('red')
 
    # definice formátu čísel
    numeric_format = workbook.add_format({'num_format': '0.0000'})
 
    # nastavení šířky sloupců a stylu
    worksheet.set_column('A:A', 8)
    worksheet.set_column('B:B', 14, numeric_format)
    worksheet.set_column('C:Z', 2)
 
    # styl pro první řádek
    worksheet.set_row(0, 20, bold_style)
 
    # buňky s textem
    worksheet.write_string('A1', 'x')
    worksheet.write_string('B1', '1/x')
 
    # buňky s numerickými hodnotami
    for x in range(1, 21):
        worksheet.write_number(x, 0, x)
        worksheet.write_number(x, 1, 1.0/x)
 
    # formát s podmínkou
    conditional_format = {
             'type':     'cell',
             'criteria': '>=',
             'value':    0.2,
             'format':   red_style
            }
 
    # nastavení podmíněného formátu u druhého sloupce hodnot
    worksheet.conditional_format(1, 1, 20, 1, conditional_format)
 
    # sešit bude uzavřen automaticky

Výsledek by měl být totožný s předchozím příkladem:

Obrázek 12: Podmíněný formát buněk ve druhém sloupci.

11. Další možnosti podmíněného formátování: hodnoty ležící v daném intervalu

Podmíněný formát buněk může být založen i na testu, zda se hodnota nachází mezi (between dvěma limitními hodnotami. Tato podmínka se specifikuje následujícím způsobem (kritérium je nutné nastavit na „between“):

conditional_format = {
         'type':     'cell',
         'criteria': 'between',
         'minimum':  5,
         'maximum':  15,
         'format':   red_style
        }

Celý skript:

#!/usr/bin/env python3
# vim: set fileencoding=utf-8
 
"""Podmíněné formátování, hodnoty mezi zadanými mezemi."""
 
import xlsxwriter
 
# vytvoření objektu reprezentujícího celý sešit
with xlsxwriter.Workbook('example33.xlsx') as workbook:
    # vložení nového listu do sešitu
    worksheet = workbook.add_worksheet()
 
    # definice nového stylu
    bold_style = workbook.add_format()
    bold_style.set_bold()
    bold_style.set_font_color('blue')
 
    # definice dalšího nového stylu
    red_style = workbook.add_format()
    red_style.set_font_color('red')
 
    # definice formátu čísel
    numeric_format = workbook.add_format({'num_format': '0.0000'})
 
    # nastavení šířky sloupců a stylu
    worksheet.set_column('A:A', 8)
    worksheet.set_column('B:B', 14, numeric_format)
    worksheet.set_column('C:Z', 2)
 
    # styl pro první řádek
    worksheet.set_row(0, 20, bold_style)
 
    # buňky s textem
    worksheet.write_string('A1', 'x')
    worksheet.write_string('B1', '1/x')
 
    # buňky s numerickými hodnotami
    for x in range(1, 21):
        worksheet.write_number(x, 0, x)
        worksheet.write_number(x, 1, 1.0/x)
 
    # formát s podmínkou
    conditional_format = {
             'type':     'cell',
             'criteria': 'between',
             'minimum':  5,
             'maximum':  15,
             'format':   red_style
            }
 
    # nastavení podmíněného formátu u prvního sloupce hodnot
    worksheet.conditional_format(1, 0, 20, 0, conditional_format)
 
    # sešit bude uzavřen automaticky

Obrázek 13: Hodnota červeně obarvených buněk leží v zadaných mezích.

Opačná podmínka platná pro buňky, jejichž hodnoty v daném intervalu neleží, se zapíše pomocí kritéria „not between“:

conditional_format_2 = {
         'type':     'cell',
         'criteria': 'not between',
         'minimum':  0.1,
         'maximum':  0.8,
         'format':   green_style
        }

Celý skript, který je na této podmínce založen, vypadá takto:

#!/usr/bin/env python3
# vim: set fileencoding=utf-8
 
"""Podmíněné formátování, hodnoty mimo zadané meze."""
 
import xlsxwriter
 
# vytvoření objektu reprezentujícího celý sešit
with xlsxwriter.Workbook('example33.xlsx') as workbook:
    # vložení nového listu do sešitu
    worksheet = workbook.add_worksheet()
 
    # definice nového stylu
    bold_style = workbook.add_format()
    bold_style.set_bold()
    bold_style.set_font_color('blue')
 
    # definice dalšího nového stylu
    red_style = workbook.add_format()
    red_style.set_font_color('red')
 
    # definice dalšího nového stylu
    green_style = workbook.add_format()
    green_style.set_font_color('green')
 
    # definice formátu čísel
    numeric_format = workbook.add_format({'num_format': '0.0000'})
 
    # nastavení šířky sloupců a stylu
    worksheet.set_column('A:A', 8)
    worksheet.set_column('B:B', 14, numeric_format)
    worksheet.set_column('C:Z', 2)
 
    # styl pro první řádek
    worksheet.set_row(0, 20, bold_style)
 
    # buňky s textem
    worksheet.write_string('A1', 'x')
    worksheet.write_string('B1', '1/x')
 
    # buňky s numerickými hodnotami
    for x in range(1, 21):
        worksheet.write_number(x, 0, x)
        worksheet.write_number(x, 1, 1.0/x)
 
    # formát s podmínkou
    conditional_format_1 = {
             'type':     'cell',
             'criteria': 'between',
             'minimum':  5,
             'maximum':  15,
             'format':   red_style
            }
 
    # nastavení podmíněného formátu u druhého sloupce hodnot
    worksheet.conditional_format(1, 0, 20, 0, conditional_format_1)
 
    # formát s podmínkou
    conditional_format_2 = {
             'type':     'cell',
             'criteria': 'not between',
             'minimum':  0.1,
             'maximum':  0.8,
             'format':   green_style
            }
 
    # nastavení podmíněného formátu u prvního sloupce hodnot
    worksheet.conditional_format(1, 0, 20, 0, conditional_format_1)
 
    # nastavení podmíněného formátu u druhého sloupce hodnot
    worksheet.conditional_format(1, 1, 20, 1, conditional_format_2)
 
    # sešit bude uzavřen automaticky

Obrázek 14: Hodnota zeleně obarvených buněk leží mimo zadané meze.

12. Podmíněný formát vztažený k více buňkám

V předchozích dvou kapitolách jsme si ukázali, jak lze změnit formát buňky na základě jejího obsahu, tedy například na faktu, že hodnota uložená v buňce je menší popř. větší než zadaná hodnota, nebo na základě toho, zda hodnota leží či neleží v určitém rozsahu. Ovšem existují i další kritéria vztažená typicky k celému sloupci buněk – test na maximální a minimální hodnotu, test, zda je hodnota buňky větší nebo menší než průměr atd. Podívejme se nyní na poslední zmíněné kritérium – vytvoříme formát, který je uplatněn pouze ve chvíli, kdy je hodnota buňky větší než průměr všech buněk (ve vybraném sloupci):

conditional_format_2 = {
         'type':     'average',
         'criteria': 'above',
         'format':   green_style
        }
Poznámka: některé tabulkové procesory takovou podmínku nedokážou korektně zpracovat.

Tento formát založený na podmínce je použit i v následujícím demonstračním příkladu:

#!/usr/bin/env python3
# vim: set fileencoding=utf-8
 
"""Podmíněné formátování, hodnoty pod průměrem."""
 
import xlsxwriter
 
# vytvoření objektu reprezentujícího celý sešit
with xlsxwriter.Workbook('example34.xlsx') as workbook:
    # vložení nového listu do sešitu
    worksheet = workbook.add_worksheet()
 
    # definice nového stylu
    bold_style = workbook.add_format()
    bold_style.set_bold()
    bold_style.set_font_color('blue')
 
    # definice dalšího nového stylu
    red_style = workbook.add_format()
    red_style.set_font_color('red')
 
    # definice dalšího nového stylu
    green_style = workbook.add_format()
    green_style.set_font_color('green')
 
    # definice formátu čísel
    numeric_format = workbook.add_format({'num_format': '0.0000'})
 
    # nastavení šířky sloupců a stylu
    worksheet.set_column('A:A', 8)
    worksheet.set_column('B:B', 14, numeric_format)
    worksheet.set_column('C:Z', 2)
 
    # styl pro první řádek
    worksheet.set_row(0, 20, bold_style)
 
    # buňky s textem
    worksheet.write_string('A1', 'x')
    worksheet.write_string('B1', '1/x')
 
    # buňky s numerickými hodnotami
    for x in range(1, 21):
        worksheet.write_number(x, 0, x)
        worksheet.write_number(x, 1, 1.0/x)
 
    # formát s podmínkou
    conditional_format_1 = {
             'type':     'cell',
             'criteria': 'between',
             'minimum':  5,
             'maximum':  15,
             'format':   red_style
            }
 
    # nastavení podmíněného formátu u druhého sloupce hodnot
    worksheet.conditional_format(1, 0, 20, 0, conditional_format_1)
 
    # formát s podmínkou
    conditional_format_2 = {
             'type':     'average',
             'criteria': 'above',
             'format':   green_style
            }
 
    # nastavení podmíněného formátu u prvního sloupce hodnot
    worksheet.conditional_format(1, 0, 20, 0, conditional_format_1)
 
    # nastavení podmíněného formátu u druhého sloupce hodnot
    worksheet.conditional_format(1, 1, 20, 1, conditional_format_2)
 
    # sešit bude uzavřen automaticky

13. Automatické filtry

Velmi užitečnou vlastností většiny tabulkových procesorů je podpora takzvaných automatických filtrů. Tuto funkci pravděpodobně znáte z praxe – v tabulkovém procesoru je možné nastavit, že se v buňkách tvořících nadpisy jednotlivých sloupců vytvoří kontextová menu (realizovaná většinou rozbalovacími seznamy), které obsahují předpočítané parametry filtru (například všechny položky, nejčastější hodnoty buněk atd.). Výběrem položek z jednotlivých filtrů dochází k výběru jen určitých údajů z potenciálně rozsáhlé tabulky.

V knihovně XlsxWriter se filtry specifikují vždy pro celou oblast, například pro první sloupec tabulky s rozsahem od A1 do A21. Nejprve je nutné tento filtr povolit (aby se vůbec zobrazil v tabulkovém procesoru):

# nastavení automatického filtru
worksheet.autofilter('A1:A21')

Takto jednoduše definovaný automatický filtr (jeho obsah je vytvořen zcela automaticky) je použit v dalším demonstračním příkladu, jehož úplný zdrojový kód vypadá následovně:

#!/usr/bin/env python3
# vim: set fileencoding=utf-8
 
"""Automatické filtry."""
 
import xlsxwriter
 
# vytvoření objektu reprezentujícího celý sešit
with xlsxwriter.Workbook('example36.xlsx') as workbook:
    # vložení nového listu do sešitu
    worksheet = workbook.add_worksheet()
 
    # definice nového stylu
    bold_style = workbook.add_format()
    bold_style.set_bold()
    bold_style.set_font_color('blue')
 
    # definice dalšího nového stylu
    red_style = workbook.add_format()
    red_style.set_font_color('red')
 
    # definice formátu čísel
    numeric_format = workbook.add_format({'num_format': '0.0000'})
 
    # nastavení šířky sloupců a stylu
    worksheet.set_column('A:A', 8)
    worksheet.set_column('B:B', 14, numeric_format)
    worksheet.set_column('C:Z', 2)
 
    # styl pro první řádek
    worksheet.set_row(0, 20, bold_style)
 
    # buňky s textem
    worksheet.write_string('A1', 'x')
    worksheet.write_string('B1', '1/x')
 
    # buňky s numerickými hodnotami
    for x in range(1, 21):
        worksheet.write_number(x, 0, x)
        worksheet.write_number(x, 1, 1.0/x)
 
    # nastavení automatického filtru
    worksheet.autofilter('A1:A21')
 
    # formát s podmínkou
    conditional_format = {
             'type':     'cell',
             'criteria': '>=',
             'value':    0.2,
             'format':   red_style
            }
 
    # nastavení podmíněného formátu u druhého sloupce hodnot
    worksheet.conditional_format('B2:B21', conditional_format)
 
    # sešit bude uzavřen automaticky

Obrázek 15: Automatický filtr vytvořený pro první sloupec tabulky.

Automatické filtry je možné nastavit pro větší množství sloupců, a to jediným příkazem:

worksheet.autofilter('A1:B21')

Což je volání použité v dalším skriptu:

#!/usr/bin/env python3
# vim: set fileencoding=utf-8
 
"""Automatické filtry."""
 
import xlsxwriter
 
# vytvoření objektu reprezentujícího celý sešit
with xlsxwriter.Workbook('example37.xlsx') as workbook:
    # vložení nového listu do sešitu
    worksheet = workbook.add_worksheet()
 
    # definice nového stylu
    bold_style = workbook.add_format()
    bold_style.set_bold()
    bold_style.set_font_color('blue')
 
    # definice dalšího nového stylu
    red_style = workbook.add_format()
    red_style.set_font_color('red')
 
    # definice formátu čísel
    numeric_format = workbook.add_format({'num_format': '0.0000'})
 
    # nastavení šířky sloupců a stylu
    worksheet.set_column('A:A', 8)
    worksheet.set_column('B:B', 14, numeric_format)
    worksheet.set_column('C:Z', 2)
 
    # styl pro první řádek
    worksheet.set_row(0, 20, bold_style)
 
    # buňky s textem
    worksheet.write_string('A1', 'x')
    worksheet.write_string('B1', '1/x')
 
    # buňky s numerickými hodnotami
    for x in range(1, 21):
        worksheet.write_number(x, 0, x)
        worksheet.write_number(x, 1, 1.0/x)
 
    # nastavení automatického filtru pro dva sloupce
    worksheet.autofilter('A1:B21')
 
    # formát s podmínkou
    conditional_format = {
             'type':     'cell',
             'criteria': '>=',
             'value':    0.2,
             'format':   red_style
            }
 
    # nastavení podmíněného formátu u druhého sloupce hodnot
    worksheet.conditional_format('B2:B21', conditional_format)
 
    # sešit bude uzavřen automaticky

Obrázek 16: Automatický filtr vytvořený pro první i druhý sloupec tabulky.

14. Další možnosti automatických filtrů

Do automaticky vytvářených filtrů je možné zadat i vlastní podmínky, které mohou být zapsány následujícím způsobem:

worksheet.filter_column('A', 'x > 10')

Povšimněte si, že se v podmínce vyskytuje proměnná x, která však ve skutečnosti může mít jakékoli jméno, protože se namísto této proměnné použije hodnota ve filtrovaných buňkách. Pro úplnost se podívejme na upravený skript, který tento filtr používá:

#!/usr/bin/env python3
# vim: set fileencoding=utf-8
 
"""Automatické filtry."""
 
import xlsxwriter
 
# vytvoření objektu reprezentujícího celý sešit
with xlsxwriter.Workbook('example38.xlsx') as workbook:
    # vložení nového listu do sešitu
    worksheet = workbook.add_worksheet()
 
    # definice nového stylu
    bold_style = workbook.add_format()
    bold_style.set_bold()
    bold_style.set_font_color('blue')
 
    # definice dalšího nového stylu
    red_style = workbook.add_format()
    red_style.set_font_color('red')
 
    # definice formátu čísel
    numeric_format = workbook.add_format({'num_format': '0.0000'})
 
    # nastavení šířky sloupců a stylu
    worksheet.set_column('A:A', 8)
    worksheet.set_column('B:B', 14, numeric_format)
    worksheet.set_column('C:Z', 2)
 
    # styl pro první řádek
    worksheet.set_row(0, 20, bold_style)
 
    # buňky s textem
    worksheet.write_string('A1', 'x')
    worksheet.write_string('B1', '1/x')
 
    # buňky s numerickými hodnotami
    for x in range(1, 21):
        worksheet.write_number(x, 0, x)
        worksheet.write_number(x, 1, 1.0/x)
 
    # nastavení automatického filtru pro dva sloupce
    worksheet.autofilter('A1:B21')
 
    worksheet.filter_column('A', 'x > 10')
 
    # formát s podmínkou
    conditional_format = {
             'type':     'cell',
             'criteria': '>=',
             'value':    0.2,
             'format':   red_style
            }
 
    # nastavení podmíněného formátu u druhého sloupce hodnot
    worksheet.conditional_format('B2:B21', conditional_format)
 
    # sešit bude uzavřen automaticky

Automatické filtry se uplatní ve chvíli, kdy se v nějakém sloupci použije jen několik opakujících se hodnot, například:

for x in range(1, 21):
    worksheet.write_number(x, 0, x)
    worksheet.write_number(x, 1, 1.0/x)
    msg = "yes" if x % 2 == 0 else "no"
    worksheet.write_string(x, 2, msg)

V takovém případě budou tyto hodnoty obsaženy přímo ve výběrovém seznamu filtru:

#!/usr/bin/env python3
# vim: set fileencoding=utf-8
 
"""Automatické filtry."""
 
import xlsxwriter
 
# vytvoření objektu reprezentujícího celý sešit
with xlsxwriter.Workbook('example39.xlsx') as workbook:
    # vložení nového listu do sešitu
    worksheet = workbook.add_worksheet()
 
    # definice nového stylu
    bold_style = workbook.add_format()
    bold_style.set_bold()
    bold_style.set_font_color('blue')
 
    # definice dalšího nového stylu
    red_style = workbook.add_format()
    red_style.set_font_color('red')
 
    # definice formátu čísel
    numeric_format = workbook.add_format({'num_format': '0.0000'})
 
    # nastavení šířky sloupců a stylu
    worksheet.set_column('A:A', 8)
    worksheet.set_column('B:B', 14, numeric_format)
    worksheet.set_column('C:Z', 20)
 
    # styl pro první řádek
    worksheet.set_row(0, 20, bold_style)
 
    # buňky s textem
    worksheet.write_string('A1', 'x')
    worksheet.write_string('B1', '1/x')
    worksheet.write_string('C1', 'valid?')
 
    # buňky s numerickými hodnotami
    for x in range(1, 21):
        worksheet.write_number(x, 0, x)
        worksheet.write_number(x, 1, 1.0/x)
        msg = "yes" if x % 2 == 0 else "no"
        worksheet.write_string(x, 2, msg)
 
    # nastavení automatického filtru
    worksheet.autofilter('A1:C21')
 
    # formát s podmínkou
    conditional_format = {
             'type':     'cell',
             'criteria': '>=',
             'value':    0.2,
             'format':   red_style
            }
 
    # nastavení podmíněného formátu u druhého sloupce hodnot
    worksheet.conditional_format('B2:B21', conditional_format)
 
    # sešit bude uzavřen automaticky

Obrázek 17: Automaticky vytvořené filtry pro tři sloupce. Ve třetím sloupci se ve filtru nabízí i všechny hodnoty v tomto sloupci použité.

15. Uzamčení buněk

Vzhledem k tomu, že tabulkové procesory mnohdy slouží i pro zadávání hodnot do předpřipravených formulářů, je v takových případech vhodné, aby byly některé buňky či dokonce celé listy sešitu uzamčeny. K tomu slouží metoda nazvaná Worksheet.protect():

# uzamčení sešitu
worksheet.protect()

Této metodě lze předat i heslo pro odemčení:

# uzamčení sešitu
worksheet.protect(password)
Poznámka: nejedná se ovšem o robustní techniku, neboť i znalejší uživatel dokáže sešit odemknout bez znalosti hesla.

Dále je možné u jednotlivých buněk určit, zda mají být odemčeny či uzamčeny. Jedná se o vlastnost, která je součástí stylu buňky a tudíž se nastavuje přes styly:

# definice dalšího nového stylu
locked_style = workbook.add_format()
 
# buňky budou uzamčeny
locked_style.set_locked('True')
 
# definice dalšího nového stylu
unlocked_style = workbook.add_format()
 
# buňky budou uzamčeny
unlocked_style.set_locked('False')

Tyto styly je možné ihned použít při přidávání jednotlivých buněk do sešitů, což je ukázáno v dalším skriptu:

#!/usr/bin/env python3
# vim: set fileencoding=utf-8
 
"""Uzamčení buněk."""
 
import xlsxwriter
 
# vytvoření objektu reprezentujícího celý sešit
with xlsxwriter.Workbook('example40.xlsx') as workbook:
    # vložení nového listu do sešitu
    worksheet = workbook.add_worksheet()
 
    # uzamčení sešitu
    worksheet.protect()
 
    # definice nového stylu
    bold_style = workbook.add_format()
    bold_style.set_bold()
    bold_style.set_font_color('blue')
 
    # definice dalšího nového stylu
    locked_style = workbook.add_format()
    locked_style.set_font_color('red')
 
    # buňky budou uzamčeny
    locked_style.set_locked('True')
 
    # definice dalšího nového stylu
    unlocked_style = workbook.add_format()
 
    # buňky budou uzamčeny
    unlocked_style.set_locked('False')
 
    # definice formátu čísel
    numeric_format = workbook.add_format({'num_format': '0.0000'})
 
    # nastavení šířky sloupců a stylu
    worksheet.set_column('A:A', 8)
    worksheet.set_column('B:B', 14, numeric_format)
    worksheet.set_column('C:Z', 20)
 
    # styl pro první řádek
    worksheet.set_row(0, 20, bold_style)
 
    # buňky s textem
    worksheet.write_string('A1', 'x')
    worksheet.write_string('B1', '1/x')
    worksheet.write_string('C1', 'valid?')
 
    # buňky s numerickými hodnotami
    for x in range(1, 21):
        worksheet.write_number(x, 0, x, locked_style)
        worksheet.write_number(x, 1, 1.0/x)
        msg = "yes" if x % 2 == 0 else "no"
        worksheet.write_string(x, 2, msg, unlocked_style)
 
    # nastavení automatického filtru
    worksheet.autofilter('A1:C21')
 
    # sešit bude uzavřen automaticky

Obrázek 18: Pokus o přístup k uzamčené buňce.

16. Validace dat zadávaných do buněk

Další užitečnou vlastností tabulkových procesorů, kterou je možné přes knihovnu XlsxWriter využít, je validace vstupních dat zadávaných uživatelem. Opět se jedná o funkcionalitu, jíž lze využít ve chvíli, kdy sešity slouží pro zadávání dat do formulářů (které mohou být mnohdy i velmi složité). V takových případech je vhodné, aby se vstupní data, která uživatel zadává, kontrolovala. A přesně k tomuto účelu se používají validátory.

Validátory se specifikují metodou Worksheet.data_validation, které je nutné předat adresu buňky a taktéž slovník, v němž je obsažena specifikace validátoru. Podívejme se na příklad získaný přímo z dokumentace ke knihovně XlsxWriter:

worksheet.data_validation('B25', {'validate': 'integer',
                                  'criteria': 'between',
                                  'minimum': 1,
                                  'maximum': 100,
                                  'input_title': 'Enter an integer:',
                                  'input_message': 'between 1 and 100'})

Vidíme, že validátor obsahuje jak očekávaný datový typ, tak i podmínku, která musí být splněna. Poslední dvě položky jsou použity v grafickém uživatelském rozhraní tabulkového procesoru pro nápovědu zobrazenou uživateli.

17. Sešit obsahující buňku s validátorem

Vyzkoušejme si nyní, jak se takto nakonfigurovaný validátor bude chovat v tabulkovém procesoru, pokud vytvoříme sešit tímto skriptem:

#!/usr/bin/env python3
# vim: set fileencoding=utf-8
 
"""Jednoduchý validátor."""
 
import xlsxwriter
 
# vytvoření objektu reprezentujícího celý sešit
with xlsxwriter.Workbook('example41.xlsx') as workbook:
    # vložení nového listu do sešitu
    worksheet = workbook.add_worksheet()
 
    # nastavení šířky sloupců a stylu
    worksheet.set_column('A:A', 50)
    worksheet.set_column('B:B', 14)
 
    # zápis hodnot do buněk
    worksheet.write_string('A1', 'Hodnota v rozsahu 1-100:')
    worksheet.write_number('B1', 50)
 
    # nastavení validátoru
    worksheet.data_validation('B1', {'validate': 'integer',
                                     'criteria': 'between',
                                     'minimum': 1,
                                     'maximum': 100,
                                     'input_title': 'Celočíselná hodnota:',
                                     'input_message': 'v rozsahu 1 až 100'})
 
    # sešit bude uzavřen automaticky

Chování takto vytvořeného sešitu lze ověřit v tabulkovém procesoru:

Obrázek 19: Nápověda zobrazená uživateli, které obsahuje zprávy zadané do atributů input_title a input_message.

Obrázek 20: Kontrola typu zapsané hodnoty. V našem případě se očekávají hodnoty typu integer.

Obrázek 21: Kontrola rozsahu v případě, že je rozsah kontrolován. V našem případě musí hodnota ležet v rozsahu od 1 do 100 (včetně obou mezních hodnot).

Root obecny

18. Další objekty vkládané do sešitů

Do sešitů je možné v moderních tabulkových procesorech vkládat i další objekty. Jedná se o rastrové obrázky, vektorové kresby, pochopitelně grafy, ale například i takzvané „sparklines“, což jsou miniaturní grafy uložené do jediné buňky. A právě s těmito objekty se seznámíme v navazující (a současně i poslední) části tohoto miniseriálu.

19. Repositář s demonstračními příklady

Zdrojové kódy všech minule i dnes popsaných demonstračních příkladů určených pro programovací jazyk Python 3 a nejnovější stabilní verzi knihovny XlsxWriter byly uloženy do Git repositáře dostupného na adrese https://github.com/tisnik/most-popular-python-libs. V případě, že nebudete chtít klonovat celý repositář (ten je ovšem stále velmi malý, dnes má velikost zhruba několik desítek kilobajtů), můžete namísto toho použít odkazy na jednotlivé příklady, které naleznete v následující tabulce:

# Demonstrační příklad Stručný popis příkladu Cesta
1 01_empty_worksheet.py vytvoření prázdného sešitu https://github.com/tisnik/most-popular-python-libs/blob/master/XlsxWriter/01_em­pty_worksheet.py
2 02_with_statement.py vytvoření prázdného sešitu, zajištění uzavření objektu worksheet https://github.com/tisnik/most-popular-python-libs/blob/master/XlsxWriter/02_wit­h_statement.py
3 03_simple_data.py vytvoření sešitu s několika vyplněnými buňkami https://github.com/tisnik/most-popular-python-libs/blob/master/XlsxWriter/03_sim­ple_data.py
4 04_alternative_addressing.py vytvoření sešitu s několika vyplněnými buňkami, alternativní adresování buněk https://github.com/tisnik/most-popular-python-libs/blob/master/XlsxWriter/04_al­ternative_addressing.py
5 05_fill_in_table.py vytvoření sešitu s delší tabulkou https://github.com/tisnik/most-popular-python-libs/blob/master/XlsxWriter/05_fi­ll_in_table.py
6 06_column_widths.py vytvoření sešitu s delší tabulkou, nastavení šířky sloupců https://github.com/tisnik/most-popular-python-libs/blob/master/XlsxWriter/06_co­lumn_widths.py
7 07_styles.py nastavení stylů vybraných buněk https://github.com/tisnik/most-popular-python-libs/blob/master/XlsxWriter/07_sty­les.py
8 08_more_styles.py nastavení stylů vybraných buněk https://github.com/tisnik/most-popular-python-libs/blob/master/XlsxWriter/08_mo­re_styles.py
9 09_alternative_styles.py alternativní způsob nastavení stylů vybraných buněk https://github.com/tisnik/most-popular-python-libs/blob/master/XlsxWriter/09_al­ternative_styles.py
10 10_format_for_row.py nastavení stylu celých řádků v sešitu https://github.com/tisnik/most-popular-python-libs/blob/master/XlsxWriter/10_for­mat_for_row.py
11 11_format_set_column.py nastavení stylu celých sloupců v sešitu https://github.com/tisnik/most-popular-python-libs/blob/master/XlsxWriter/11_for­mat_set_column.py
12 12_write_proper_types.py buňky s explicitní specifikací datového typu https://github.com/tisnik/most-popular-python-libs/blob/master/XlsxWriter/12_wri­te_proper_types.py
13 13_numeric_format.py buňky se specifikací číselného formátu https://github.com/tisnik/most-popular-python-libs/blob/master/XlsxWriter/13_nu­meric_format.py
14 14_numeric_format.py buňky se specifikací číselného formátu https://github.com/tisnik/most-popular-python-libs/blob/master/XlsxWriter/14_nu­meric_format.py
15 15_alternative_numeric_format.py další způsob specifikace číselného formátu buněk tabulky https://github.com/tisnik/most-popular-python-libs/blob/master/XlsxWriter/15_al­ternative_numeric_format.py
16 16_font_size.py změna velikosti písma v různých buňkách tabulky https://github.com/tisnik/most-popular-python-libs/blob/master/XlsxWriter/16_fon­t_size.py
17 17_named_colors.py použití pojmenovaných barev pro změnu barvy písma https://github.com/tisnik/most-popular-python-libs/blob/master/XlsxWriter/17_na­med_colors.py
18 18_html_colors.py použití šestice hexadecimálních číslic pro změnu barvy písma https://github.com/tisnik/most-popular-python-libs/blob/master/XlsxWriter/18_html_co­lors.py
19 19_named_bg_colors.py použití pojmenovaných barev pro změnu barvy pozadí https://github.com/tisnik/most-popular-python-libs/blob/master/XlsxWriter/19_na­med_bg_colors.py
20 20_html_background_colors.py použití šestice hexadecimálních číslic pro změnu barvy pozadí https://github.com/tisnik/most-popular-python-libs/blob/master/XlsxWriter/20_html_bac­kground_colors.py
       
21 21_comments.py komentáře přidané k buňkám https://github.com/tisnik/most-popular-python-libs/blob/master/XlsxWriter/21_com­ments.py
22 22_show_comments.py komentáře, které se zobrazí ihned po otevření sešitu https://github.com/tisnik/most-popular-python-libs/blob/master/XlsxWriter/22_show_com­ments.py
23 23_comments_addressing.py alternativní adresování buněk s komentáři https://github.com/tisnik/most-popular-python-libs/blob/master/XlsxWriter/23_com­ments_addressing.py
24 24_more_worksheets.py více listů v sešitu https://github.com/tisnik/most-popular-python-libs/blob/master/XlsxWriter/24_mo­re_worksheets.py
25 25_more_worksheets.py pojmenované listy v sešitu https://github.com/tisnik/most-popular-python-libs/blob/master/XlsxWriter/25_mo­re_worksheets.py
26 26_more_worksheets_names.py kolize jmen listů v sešitu https://github.com/tisnik/most-popular-python-libs/blob/master/XlsxWriter/26_mo­re_worksheets_names.py
27 27_merged_cells.py sloučení buněk na listu https://github.com/tisnik/most-popular-python-libs/blob/master/XlsxWriter/27_mer­ged_cells.py
28 28_merged_cells_error.py překryv sloučených oblastí https://github.com/tisnik/most-popular-python-libs/blob/master/XlsxWriter/28_mer­ged_cells_error.py
29 29_merged_cells.py sloučení buněk přes více sloupců a řádků https://github.com/tisnik/most-popular-python-libs/blob/master/XlsxWriter/29_mer­ged_cells.py
30 30_merged_cells_addressing.py alternativní určení oblasti buněk, které se mají spojit https://github.com/tisnik/most-popular-python-libs/blob/master/XlsxWriter/30_mer­ged_cells_addressing.py
31 31_conditional_formatting.py podmíněné formátování buněk https://github.com/tisnik/most-popular-python-libs/blob/master/XlsxWriter/31_con­ditional_formatting.py
32 32_conditional_formatting_addressing.py podmíněné formátování, alternativní adresace https://github.com/tisnik/most-popular-python-libs/blob/master/XlsxWriter/32_con­ditional_formatting_addres­sing.py
33 33_conditional_formatting_between.py použití podmínky „between“ https://github.com/tisnik/most-popular-python-libs/blob/master/XlsxWriter/33_con­ditional_formatting_betwe­en.py
34 34_conditional_formatting_not_bet­ween.py použití podmínky „not between“ https://github.com/tisnik/most-popular-python-libs/blob/master/XlsxWriter/34_con­ditional_formatting_not_bet­ween.py
35 35_conditional_formatting_average.py použití klauzule „average“ https://github.com/tisnik/most-popular-python-libs/blob/master/XlsxWriter/35_con­ditional_formatting_avera­ge.py
36 36_autofilter.py automatický filtr hodnot ve sloupci https://github.com/tisnik/most-popular-python-libs/blob/master/XlsxWriter/36_au­tofilter.py
37 37_autofilters.py větší množství automatických filtrů https://github.com/tisnik/most-popular-python-libs/blob/master/XlsxWriter/37_au­tofilters.py
38 38_autofilters_data.py vlastní automatický filtr https://github.com/tisnik/most-popular-python-libs/blob/master/XlsxWriter/38_au­tofilters_data.py
39 39_autofilter_text.py automatický filtr a sloupec obsahující opakující se hodnoty https://github.com/tisnik/most-popular-python-libs/blob/master/XlsxWriter/39_au­tofilter_text.py
40 40_locked_cells.py uzamčení sešitu, vybraných buněk atd. https://github.com/tisnik/most-popular-python-libs/blob/master/XlsxWriter/40_loc­ked_cells.py
41 41_validator.py jednoduchý validátor pro vybranou buňku https://github.com/tisnik/most-popular-python-libs/blob/master/XlsxWriter/41_va­lidator.py

20. Odkazy na Internetu

  1. Comparison of spreadsheet software
    https://en.wikipedia.org/wi­ki/Comparison_of_spreadshe­et_software
  2. Creating Excel files with Python and XlsxWriter
    https://xlsxwriter.readthedocs.io/
  3. XlsxWriter
    https://github.com/jmcnama­ra/XlsxWriter
  4. openpyxl – A Python library to read/write Excel 2010 xlsx/xlsm files
    https://openpyxl.readthedoc­s.io/en/stable/index.html
  5. Spreadsheet Files
    https://fileinfo.com/file­types/spreadsheet
  6. OpenDocument
    https://en.wikipedia.org/wi­ki/OpenDocument
  7. Tvorba sešitů pro tabulkové procesory v programovacím jazyku Go
    https://www.root.cz/clanky/tvorba-sesitu-pro-tabulkove-procesory-v-programovacim-jazyku-go/
  8. Spreadsheet
    https://en.wikipedia.org/wi­ki/Spreadsheet
  9. List of spreadsheet software
    https://en.wikipedia.org/wi­ki/List_of_spreadsheet_sof­tware
  10. Processing spreadsheet data in Go
    https://appliedgo.net/spreadsheet/
  11. Comma-separated values
    https://en.wikipedia.org/wiki/Comma-separated_values
  12. Common Format and MIME Type for Comma-Separated Values (CSV) Files
    https://tools.ietf.org/html/rfc4180
  13. Tab-separated values
    https://en.wikipedia.org/wiki/Tab-separated_values
  14. Office Open XML (Wikipedia)
    https://en.wikipedia.org/wi­ki/Office_Open_XML
  15. Standard ECMA-376: Office Open XML File Formats
    https://www.ecma-international.org/publica­tions/standards/Ecma-376.htm
  16. A Simple File Format for NumPy Arrays
    https://docs.scipy.org/doc/numpy-1.14.2/neps/npy-format.html
  17. numpy.lib.format
    https://numpy.org/devdocs/re­ference/generated/numpy.lib­.format.html
  18. The NumPy array: a structure for efficient numerical computation
    https://arxiv.org/pdf/1102.1523.pdf
  19. numpy.ndarray.tofile
    https://numpy.org/doc/sta­ble/reference/generated/num­py.ndarray.tofile.html#num­py.ndarray.tofile
  20. numpy.fromfile
    https://numpy.org/doc/sta­ble/reference/generated/num­py.fromfile.html
  21. How to read part of binary file with numpy?
    https://stackoverflow.com/qu­estions/14245094/how-to-read-part-of-binary-file-with-numpy
  22. How to read binary files in Python using NumPy?
    https://stackoverflow.com/qu­estions/39762019/how-to-read-binary-files-in-python-using-numpy
  23. Understanding the Open XML file formats
    https://docs.microsoft.com/en-us/office/open-xml/understanding-the-open-xml-file-formats

Autor článku

Pavel Tišnovský vystudoval VUT FIT a v současné době pracuje ve společnosti Red Hat, kde vyvíjí nástroje pro OpenShift.io.