Tvorba sešitů ve formátu XLSX v Pythonu: knihovna XlsxWriter

Obsah

1. Tvorba sešitů ve formátu XLSX v Pythonu: knihovna XlsxWriter

2. Formáty používané tabulkovými procesory

3. Instalace knihovny XlsxWriter

4. Vytvoření prázdného sešitu

5. Vytvoření listu s několika vyplněnými buňkami

6. Alternativní adresování buněk

7. Nastavení šířky sloupců v rámci listu

8. Nastavení formátu vybraných buněk

9. Alternativní nastavení formátu buněk

10. Nastavení stylu/formátu celých řádků nebo sloupců

11. Buňky se specifikací datového typu

12. Buňky se specifikací číselného formátu

13. Další numerický formát buněk

14. Změna velikosti fontu (písma) v buňkách

15. Nastavení barvy textu v buňkách

16. Kód barvy textu určený šesticí hexadecimálních hodnot

17. Nastavení barvy pozadí buněk

18. Obsah druhé části článku

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

20. Odkazy na Internetu

1. Tvorba sešitů ve formátu XLSX v Pythonu: knihovna XlsxWriter

Jedním z relativně častých požadavků kladených na různé informační systémy je požadavek na to, aby bylo možné ručně zadaná, naměřená či vypočtená data vyexportovat v takovém formátu, který by byl zpracovatelný v tabulkových procesorech (spreadsheet), například v „kancelářských“ aplikacích Gnumeric, Microsoft Excel, LibreOffice Calc či v některých tabulkových procesorech naprogramovaných a dostupných ve formě webových aplikací (Google Doc či neprávem přehlížený Ethercalc). Z pohledu uživatelů se jedná o logický požadavek, protože v tabulkových procesorech je možné relativně snadno provádět další analýzy dat, interaktivně vytvářet grafy, provádět různé dotazy nad daty atd. Navíc data vyexportovaná do jednoho z podporovaných a (polo)standardizovaných formátů lze naimportovat například do Jupyter Notebooku, Matlabu atd. a následně je sofistikovaněji zpracovat v těchto prostředích.

Dnes si ukážeme použití knihovny XlsxWriter určené pro programovací jazyk Python. Předností této knihovny je podpora pro práci s větším množstvím listů v sešitu, podpora pro styly tabulek i jednotlivých buněk, možnost vkládání dalších objektů do tabulek a v neposlední řadě i fakt, že tato knihovna má jen minimální závislosti a ani velikost příslušného balíčku není nijak ohromující – cca 700 kilobajtů po rozbalení.

2. Formáty používané tabulkovými procesory

Existuje hned několik formátů, které jsou podporovány jak knihovnami určenými pro programovací jazyk Python (kterým se dnes budeme zabývat), tak i tabulkovými procesory. Tyto formáty se od sebe odlišují svými vlastnostmi: některé formáty pouze dokážou ukládat tabulky s hodnotami jen několika datových typů (což způsobuje obecně známé problémy s reprezentací časových údajů či peněžních částek), další formáty již umožňují ukládat vzorce, formátování buněk, styly buněk a některé dokonce i tak složité objekty, jako jsou grafy. Z hlediska kompatibility mezi různými systémy je tak možné se rozhodnout například mezi následujícími formáty:

• CSV neboli Comma-Separated Values [1] je jedním z nejčastěji používaných souborových formátů v této oblasti, a to přesto, že je export a import CSV v některých případech problematický (například některé české mutace Excelu namísto čárek používají středníky, problémy nastávají s buňkami obsahujícími znaky pro konec řádku atd.). Pokud máte při importu či exportu potíže se zpracováním CSV, můžete v naprosté většině tabulkových procesorů zvolit přesnou specifikaci, jak se má konverze (resp. přesněji řečeno import nebo export) provést. V Pythonu pro tento účel existuje standardní knihovna csv (viz též https://docs.python.org/3/li­brary/csv.html), která s CSV soubory dokáže pracovat podle RFC 4180. Tato de facto norma však zdaleka nepopisuje všechny varianty CSV, s nimiž se můžete v praxi setkat.
• TSV neboli Tab-Separated Values [2] [3] je velmi podobným formátem, ovšem s tím rozdílem, že oddělovačem jednotlivých buněk je znak tabulátoru. Podobně jako v případě CSV i zde existuje několik voleb, které ovlivňují způsob importu (zda tabulka obsahuje hlavičky sloupců atd.). Nicméně i zde jsme omezeni pouze na „čistá“ data – nelze (jednotně) uložit metadata o jednotlivých sloupcích, export je omezen na jediný sešit a neexistuje zde ani možnost vložení dalších objektů do tabulky.
• Existuje i relativně velké množství aplikací, v nichž jsou tabulková data uložena ve formě běžných textových souborů s nějakými oddělovači odlišnými od výše zmíněného tabulátoru (relativně často se jedná o středníky, dvojtečky nebo o znak |, i když invence programátorů jsou v tomto směru velké). Buď se jedná o zobecnění formátů CSV a TSV [4], nebo může mít textový soubor podobu naformátovaných sloupců s pevnou délkou (a tedy bez problémů čitelných uživatelem). V tomto případě většinou musí tabulkové procesory soubor analyzovat a na základě této analýzy navrhnout, kde se nachází jednotlivé sloupce. Příkladem takového souboru je například https://www.cnb.cz/cs/finan­cni_trhy/devizovy_trh/kur­zy_devizoveho_trhu/denni_kur­z.txt, v němž se jako oddělovače používají právě znaky „|“ a navíc – aby byl import ještě zajímavější – jsou v numerických hodnotách použity čárky namísto teček.
• Dalším velmi často používaným formátem pro přenos tabulek i celých sešitů (sheet) je formát nazvaný Office Open XML Workbook. Tento formát je součástí skupiny formátů známých též pod zkratkou OOXML (plným jménem Office Open XML) nebo též (z dnes již spíše historických důvodů) Microsoft Open XML. Práci s tímto formátem v programovacím jazyku Python je věnována celá druhá část dnešního článku.
• Podobně koncipovaný je i Open Document Format for Office Applications (ODF), známý též pod kratším jménem OpenDocument. Tímto formátem se dnes ještě nebudeme zabývat, i když je taktéž poměrně často používaný (i když se zdá, že souboj mezi ODF a OOXML je již rozhodnutý).

3. Instalace knihovny XlsxWriter

Pro tvorbu sešitů ve formátu Office Open XML Workbook (zkráceně XLSX) z Pythonu existuje knihovna nazvaná přímočaře XlsxWriter. Nejprve samozřejmě musíme příslušnou knihovnu nainstalovat, což je v tomto konkrétním případě otázka několika sekund, protože tato knihovna nemá žádné složité závislosti. Postačuje použít příkaz pip popř. pip3 pro instalaci knihovny redis. Pro jistotu instalaci provedeme pouze pro aktivního uživatele, takže se knihovna i její metadata uloží do adresáře ~/.local/lib/python{VERZE}/site-packages/:

$ pip3 install --user XlsxWriter
 
Collecting XlsxWriter
  Downloading https://files.pythonhosted.org/packages/cd/84/c239b08592a431f7ad8773f7869470255b5f1ad860d7b40a9e7ed3f01bde/XlsxWriter-3.0.2-py3-none-any.whl (149kB)
    100% |████████████████████████████████| 153kB 1.5MB/s
Installing collected packages: XlsxWriter
Successfully installed XlsxWriter-3.0.2

Otestování instalace:

$ python3
 
>>> import xlsxwriter
>>> help(xlsxwriter)

S výsledkem:

Help on package xlsxwriter:
 
NAME
    xlsxwriter
 
DESCRIPTION
    # SPDX-License-Identifier: BSD-2-Clause
    # Copyright 2013-2021, John McNamara, jmcnamara@cpan.org
    #
 
PACKAGE CONTENTS
    app
    ...
    ...
    ...

4. Vytvoření prázdného sešitu

Demonstrační příklady, s nimiž se dnes seznámíme, budou zpočátku velmi jednoduché, a to z toho důvodu, aby se na nich ukázala snadnost práce s knihovnou XlsxWriter. V prvním příkladu je ukázáno, jak se vytvoří sešit s jediným prázdným listem. Sešit je představován instancí třídy Workbook, list v sešitu pak instancí třídy Worksheet. Na konci skriptu je korektní zavolat metodu Workbook.close:

#!/usr/bin/env python3
# vim: set fileencoding=utf-8
 
"""Vytvoření prázdného sešitu."""
 
import xlsxwriter
 
# vytvoření objektu reprezentujícího celý sešit
workbook = xlsxwriter.Workbook('example01.xlsx')
 
# vložení nového listu do sešitu
worksheet = workbook.add_worksheet()
 
# explicitní uzavření sešitu
workbook.close()

Obrázek 1: Výsledný sešit otevřený v Gnumericu.

Nutnost explicitního volání metody Workbook.Close je z praktického pohledu dosti nepříjemná, protože se například v důsledku vyvolání výjimky může stát, že se metoda nezavolá. V Pythonu však existuje lepší řešení – vytvoření kontextu, v jehož rámci je sešit otevřen. Po opuštění tohoto kontextu dojde k automatickému uzavření sešitu, což je přesně to chování, které vyžadujeme. Z tohoto důvodu budou všechny další demonstrační příklady založeny na této kostře:

#!/usr/bin/env python3
# vim: set fileencoding=utf-8
 
"""Vytvoření prázdného sešitu, zajištění uzavření objektu worksheet."""
 
import xlsxwriter
 
# vytvoření objektu reprezentujícího celý sešit
with xlsxwriter.Workbook('example02.xlsx') as workbook:
    # vložení nového listu do sešitu
    worksheet = workbook.add_worksheet()
    # sešit bude uzavřen automaticky

Obrázek 2: Výsledný sešit otevřený v Gnumericu.

5. Vytvoření listu s několika vyplněnými buňkami

Do prozatím prázdného sešitu nyní přidáme několik buněk s hodnotami. Pro tento účel se používá metoda nazvaná Worksheet.Write, která má několik podob. V základní podobě se této metodě předává adresa buňky následovaná hodnotou, která se má do buňky uložit. Adresu buňky lze zapsat stejným způsobem, jako je tomu i ve vlastním spreadsheetu – řetězcem se jménem sloupce a číslem řádku. A hodnotou může být jak řetězec, tak i číslo:

worksheet.write('A1', 'www.root.cz')
worksheet.write('A2', 6)

Celý skript, který vytvoří sešit s jedním listem s několika vyplněnými buňkami, může vypadat následovně:

#!/usr/bin/env python3
# vim: set fileencoding=utf-8
 
"""Vytvoření sešitu s několika vyplněnými buňkami."""
 
import xlsxwriter
 
# vytvoření objektu reprezentujícího celý sešit
with xlsxwriter.Workbook('example03.xlsx') as workbook:
    # vložení nového listu do sešitu
    worksheet = workbook.add_worksheet()
 
    # buňka s textem
    worksheet.write('A1', 'www.root.cz')
 
    # buňky s numerickými hodnotami
    worksheet.write('A2', 6)
    worksheet.write('A3', 7)
    worksheet.write('B3', 8)
 
    # sešit bude uzavřen automaticky

Obrázek 3: Výsledný sešit otevřený v Gnumericu.

6. Alternativní adresování buněk

Zejména při programovém vyplňování tabulky není výše použitý způsob adresování buněk příliš vhodný, protože by bylo nutné programově skládat adresu buňky ve formátu řetězce „A1“. Ovšem existuje i alternativní způsob adresace buněk, v němž je jak číslo řádku, tak i číslo sloupce zadáno celočíselným indexem. Přitom se číslo řádku uvádí na prvním místě:

worksheet.write(řádek, sloupec, 'www.root.cz')

Demonstrační příklad z předchozí kapitoly je tedy možné upravit takto:

#!/usr/bin/env python3
# vim: set fileencoding=utf-8
 
"""Vytvoření sešitu s několika vyplněnými buňkami, alternativní adresování buněk."""
 
import xlsxwriter
 
# vytvoření objektu reprezentujícího celý sešit
with xlsxwriter.Workbook('example04.xlsx') as workbook:
    # vložení nového listu do sešitu
    worksheet = workbook.add_worksheet()
 
    # buňka s textem
    worksheet.write(0, 0, 'www.root.cz')
 
    # buňky s numerickými hodnotami
    worksheet.write(1, 0, 6)
    worksheet.write(2, 0, 7)
    worksheet.write(2, 1, 8)
 
    # sešit bude uzavřen automaticky

Obrázek 4: Výsledný sešit otevřený v Gnumericu.

Díky tomuto způsobu zápisu můžeme velmi snadno programově vytvořit tabulku s převrácenými hodnotami pro x=1 až 10. Postačovat nám přitom bude jednoduchá programová smyčka:

#!/usr/bin/env python3
# vim: set fileencoding=utf-8
 
"""Vytvoření sešitu s delší tabulkou."""
 
import xlsxwriter
 
# vytvoření objektu reprezentujícího celý sešit
with xlsxwriter.Workbook('example05.xlsx') as workbook:
    # vložení nového listu do sešitu
    worksheet = workbook.add_worksheet()
 
    # buňky s textem
    worksheet.write('A1', 'x')
    worksheet.write('B1', '1/x')
 
    # buňky s numerickými hodnotami
    for x in range(1, 21):
        worksheet.write(x, 0, x)
        worksheet.write(x, 1, 1.0/x)
 
    # sešit bude uzavřen automaticky

Obrázek 5: Výsledný sešit otevřený v Gnumericu.

7. Nastavení šířky sloupců v rámci listu

Při pohledu na předchozí screenshoty je zřejmé, že se šířky sloupců automaticky nepřizpůsobily vizuální šířce zobrazených údajů. Ovšem ve skutečnosti můžeme šířku sloupců zadat explicitně, a to velmi jednoduše – metodou Worksheet.set_column, které se předají adresy sloupců a požadovaná šířka ve „znacích“ (tedy tak, jak je zadávána i přímo v Excelu). Alternativně je možné použít metodu pojmenovanou Worksheet.set_column_pixels a zadat tak šířku v pixelech:

# šířka jediného sloupce
worksheet.set_column('A:A', 8)

Podívejme se nyní na upravený skript, který nastaví šířky obou použitých sloupců i dalších 24 nepoužitých (prázdných) sloupců:

#!/usr/bin/env python3
# vim: set fileencoding=utf-8
 
"""Vytvoření sešitu s delší tabulkou, nastavení šířky sloupců."""
 
import xlsxwriter
 
# vytvoření objektu reprezentujícího celý sešit
with xlsxwriter.Workbook('example06.xlsx') as workbook:
    # vložení nového listu do sešitu
    worksheet = workbook.add_worksheet()
 
    # nastavení šířky sloupců
    worksheet.set_column('A:A', 8)
    worksheet.set_column('B:B', 14)
    worksheet.set_column('C:Z', 2)
 
    # buňky s textem
    worksheet.write('A1', 'x')
    worksheet.write('B1', '1/x')
 
    # buňky s numerickými hodnotami
    for x in range(1, 21):
        worksheet.write(x, 0, x)
        worksheet.write(x, 1, 1.0/x)
 
    # sešit bude uzavřen automaticky

Obrázek 6: Výsledný sešit otevřený v Gnumericu.

8. Nastavení formátu vybraných buněk

Mezi další přednosti plnohodnotných formátů ze skupiny Office Open či Open Document Format patří podpora pro nastavení stylů a formátů vybraných buněk. Styly podporuje i knihovna XlsxWriter. Nejdříve je nutné vytvořit objekt představující formát. K tomuto účelu slouží metoda Workbook.add_format, které lze předat slovník se specifikací formátu buněk. Pokud například budeme chtít buňky zvýraznit tučným písmem, bude tento slovník obsahovat jediný atribut nazvaný „bold“:

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

Formát lze předat v posledním parametry nám již známé metody Worksheet.write:

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

Podívejme se nyní na demonstrační příklad, v němž se nastaví tučné písmo u buněk „A1“ a „B1“, tedy u buněk tvořících názvy sloupců:

#!/usr/bin/env python3
# vim: set fileencoding=utf-8
 
"""Vytvoření sešitu s delší tabulkou, nastavení stylů vybraných buněk."""
 
import xlsxwriter
 
# vytvoření objektu reprezentujícího celý sešit
with xlsxwriter.Workbook('example07.xlsx') as workbook:
    # vložení nového listu do sešitu
    worksheet = workbook.add_worksheet()
 
    # nastavení šířky sloupců
    worksheet.set_column('A:A', 8)
    worksheet.set_column('B:B', 14)
    worksheet.set_column('C:Z', 2)
 
    # definice nového stylu
    bold_style = workbook.add_format({'bold': True})
 
    # buňky s textem
    worksheet.write('A1', 'x', bold_style)
    worksheet.write('B1', '1/x', bold_style)
 
    # buňky s numerickými hodnotami
    for x in range(1, 21):
        worksheet.write(x, 0, x)
        worksheet.write(x, 1, 1.0/x)
 
    # sešit bude uzavřen automaticky

Obrázek 7: Výsledný sešit otevřený v Gnumericu.

Samozřejmě je možné nastavit i další vlastnosti, například je možné změnit barvu textu v buňce na modrou:

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

Předchozí skript nepatrně upravíme do této podoby:

#!/usr/bin/env python3
# vim: set fileencoding=utf-8
 
"""Vytvoření sešitu s delší tabulkou, nastavení stylů vybraných buněk."""
 
import xlsxwriter
 
# vytvoření objektu reprezentujícího celý sešit
with xlsxwriter.Workbook('example08.xlsx') as workbook:
    # vložení nového listu do sešitu
    worksheet = workbook.add_worksheet()
 
    # nastavení šířky sloupců
    worksheet.set_column('A:A', 8)
    worksheet.set_column('B:B', 14)
    worksheet.set_column('C:Z', 2)
 
    # definice nového stylu
    bold_style = workbook.add_format({'bold': True,
                                      'font_color': 'blue'})
 
    # buňky s textem
    worksheet.write('A1', 'x', bold_style)
    worksheet.write('B1', '1/x', bold_style)
 
    # buňky s numerickými hodnotami
    for x in range(1, 21):
        worksheet.write(x, 0, x)
        worksheet.write(x, 1, 1.0/x)
 
    # sešit bude uzavřen automaticky

Obrázek 8: Výsledný sešit otevřený v Gnumericu.

9. Alternativní nastavení formátu buněk

Samotný styl (resp. formát) je objektem a jeho atributy nemusí být nastaveny pouze obsahem slovníku s parametry formátu. Alternativně je totiž možné použít i k tomu určené metody, například metodu set_bold pro nastavení tučného písma buněk, set_font_color pro modifikaci barvy textu či set_font_size pro změnu velikosti písma. Tento přístup může být v některých situacích výhodnější, takže si ho ukažme:

# definice nového stylu
bold_style = workbook.add_format()
bold_style.set_bold()
bold_style.set_font_color('blue')

Takový styl se použije naprosto stejným způsobem, jako tomu bylo v předchozích dvou demonstračních příkladech:

#!/usr/bin/env python3
# vim: set fileencoding=utf-8
 
"""Vytvoření sešitu s delší tabulkou, nastavení stylů vybraných buněk."""
 
import xlsxwriter
 
# vytvoření objektu reprezentujícího celý sešit
with xlsxwriter.Workbook('example09.xlsx') as workbook:
    # vložení nového listu do sešitu
    worksheet = workbook.add_worksheet()
 
    # nastavení šířky sloupců
    worksheet.set_column('A:A', 8)
    worksheet.set_column('B:B', 14)
    worksheet.set_column('C:Z', 2)
 
    # definice nového stylu
    bold_style = workbook.add_format()
    bold_style.set_bold()
    bold_style.set_font_color('blue')
 
    # buňky s textem
    worksheet.write('A1', 'x', bold_style)
    worksheet.write('B1', '1/x', bold_style)
 
    # buňky s numerickými hodnotami
    for x in range(1, 21):
        worksheet.write(x, 0, x)
        worksheet.write(x, 1, 1.0/x)
 
    # sešit bude uzavřen automaticky

Obrázek 9: Výsledný sešit otevřený v Gnumericu.

10. Nastavení stylu/formátu celých řádků nebo sloupců

V tabulkových procesorech je možné pracovat s celými řádky popř. celými sloupci. Podívejme se nejdříve na způsob nastavení vlastností celého (vybraného) řádku. Pro tento účel se používá metoda nazvaná Worksheet.set_row, které je nutné předat index řádku (přitom se číslují od nuly!), dále výšku řádku a ve třetím (nepovinném) parametru příslušný styl (existuje ještě čtvrtý nepovinný parametr, ke kterému se vrátíme příště). Jak vypadá základní specifikace stylu již víme, takže nám zbývá určit nastavení výšky celého řádku. Výchozí hodnotou je 20, takže pro první řádek (s indexem rovným nule) nastavíme jeho styl takto:

# definice nového stylu
bold_style = workbook.add_format()
bold_style.set_bold()
bold_style.set_font_color('blue')
 
# styl pro první řádek
worksheet.set_row(0, 20, bold_style)

Opět se podívejme na celý skript:

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

Obrázek 10: Výsledný sešit otevřený v Gnumericu.

Zatímco pro nastavení stylu celého řádku se používá metoda Worksheet.set_row, pro určení stylu sloupce či sloupců lze použít podobnou metodu pojmenovanou Worksheet.set_column. Této metodě se předává označení sloupce (index či řetězec se jménem sloupce resp. rozsahem), šířka sloupce ve znakových jednotkách (to již známe) a konečně styl přiřazený celému sloupci:

# definice dalšího nového stylu
red_style = workbook.add_format()
red_style.set_font_color('red')
 
# nastavení šířky sloupců a stylu
worksheet.set_column('A:A', 8, red_style)

Opět si samozřejmě vše ukážeme v reálném skriptu, v němž mimochodem nastavujeme jak formát prvního řádku, tak i odlišný formát prvního sloupce:

#!/usr/bin/env python3
# vim: set fileencoding=utf-8
 
"""Vytvoření sešitu s delší tabulkou, nastavení stylu sloupců."""
 
import xlsxwriter
 
# vytvoření objektu reprezentujícího celý sešit
with xlsxwriter.Workbook('example11.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')
 
    # nastavení šířky sloupců a stylu
    worksheet.set_column('A:A', 8, red_style)
    worksheet.set_column('B:B', 14)
    worksheet.set_column('C:Z', 2)
 
    # styl pro první řádek
    worksheet.set_row(0, 20, bold_style)
 
    # buňky s textem
    worksheet.write('A1', 'x')
    worksheet.write('B1', '1/x')
 
    # buňky s numerickými hodnotami
    for x in range(1, 21):
        worksheet.write(x, 0, x)
        worksheet.write(x, 1, 1.0/x)
 
    # sešit bude uzavřen automaticky

Z výsledků je patrné, že později nastavený styl (řádku) „vyhrál“, což je patrné na vzhledu buňky A1:

Obrázek 11: Výsledný sešit otevřený v Gnumericu.

11. Buňky se specifikací datového typu

Prozatím jsme do buněk zapisovali údaje metodou Worksheet.write. Ve skutečnosti je však možné specifikovat i typ hodnot (typicky tedy rozlišit řetězce od numerických hodnot) tím, že namísto Worksheet.write použijeme metody Worksheet.write_string a Worksheet.write_number tak, jak je to ukázáno v dalším demonstračním příkladu:

#!/usr/bin/env python3
# vim: set fileencoding=utf-8
 
"""Vytvoření sešitu s delší tabulkou, buňky se specifikací datového typu."""
 
import xlsxwriter
 
# vytvoření objektu reprezentujícího celý sešit
with xlsxwriter.Workbook('example12.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')
 
    # nastavení šířky sloupců a stylu
    worksheet.set_column('A:A', 8, red_style)
    worksheet.set_column('B:B', 14)
    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

Obrázek 12: Výsledný sešit otevřený v Gnumericu.

12. Buňky se specifikací číselného formátu

V tabulkových procesorech se na data zadaná do jednotlivých buněk můžeme dívat dvěma způsoby. Jedná se o vlastní zapsanou hodnotu, která je interně reprezentována sekvencí bitů a v lidsky čitelné podobě můžeme tuto hodnotu vidět na vstupním řádku. A druhý způsob pohledu na data zapsaná do buňky je závislý na zvoleném formátu zobrazení, který s původní hodnotou může souviset mnohdy jen okrajově. Příkladem může být specifikace formátu číselné hodnoty tak, aby se zobrazila jen celá část čísla – tabulkový procesor v tomto případě nebude případné cifry za desetinnou čárkou/tečkou zobrazovat a maximálně hodnotu zaokrouhlí. Podobně lze u časových razítek požadovat pouze zobrazení data (a nikoli hodin, minut, sekund atd.). A naopak je možné vhodným formátem například zvýraznit záporné hodnoty apod.

Formát zobrazení hodnot v buňkách lze měnit i při použití knihovny XlsxWriter. Předností je, že formát zobrazení se specifikuje stejným či velmi podobným způsobem, jako je tomu i v GUI tabulkového procesoru:

#!/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('example13.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': '##.###'})
 
    # 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

Obrázek 13: Výsledný sešit otevřený v Gnumericu.

Jiný formát, tentokrát vyžadující zobrazení čtyř cifer za desetinnou čárkou/tečkou:

#!/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

Obrázek 14: Výsledný sešit otevřený v Gnumericu.

13. Další numerický formát buněk

Podívejme se na různé možnosti specifikace formátu numerických hodnot a časových razítek. Formát je zadán stejně, jako ve většině tabulkových procesorů:

Příklad explicitní specifikace numerického formátu, v němž se nepoužívá slovník, ale přímo metoda set_num_format:

numeric_format = workbook.add_format()
numeric_format.set_num_format('0.0000')

#!/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('example15.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()
    numeric_format.set_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

Obrázek 15: Výsledný sešit otevřený v Gnumericu.

14. Změna velikosti fontu (písma) v buňkách

Ve chvíli, kdy máme k dispozici objekt pro specifikaci formátu (Workbook.add_format), je možné kromě stylu (tučné písmo atd.) a barvy nastavit i velikost písma. Jednou z možností je specifikace velikosti metodou set_font_size tak, jak je to ukázáno v dalším skriptu. Přitom je výška řádku automaticky upravena tak, aby byly vidět obsahy všech buněk na tomto řádku (to však neplatí pro šířku sloupců, což již ostatně víme):

#!/usr/bin/env python3
# vim: set fileencoding=utf-8
 
"""Vytvoření sešitu s delší tabulkou, změna velikosti fontu."""
 
import xlsxwriter
 
# vytvoření objektu reprezentujícího celý sešit
with xlsxwriter.Workbook('example16.xlsx') as workbook:
    # vložení nového listu do sešitu
    worksheet = workbook.add_worksheet()
 
    # nastavení šířky sloupců
    worksheet.set_column('A:A', 20)
 
    # buňky s numerickými hodnotami a různou velikostí textu
    for x in range(1, 21):
        style = workbook.add_format()
        style.set_font_size(x)
        worksheet.write_number(x, 0, x, style)
 
    # sešit bude uzavřen automaticky

Význam jednotlivých hodnot od 1 do 20 je vidět na screenshotu:

Obrázek 16: Výsledný sešit otevřený v Gnumericu.

15. Nastavení barvy textu v buňkách

V sešitech je možné jednotlivé části buněk vybarvit různými barvami. Pro specifikaci těchto barev slouží osm metod, jejichž názvy a stručný popis naleznete v následující tabulce:

Nejprve si ukažme, jakým způsobem je možné změnit barvu textu v buňce. Použijeme přitom metodu format.set_font_color, která je zmíněna v předchozí tabulce:

style = workbook.add_format()
style.set_font_color(color)

Použití této metody v uceleném skriptu je snadné. Použijeme přitom standardní jména barev rozpoznávaných knihovnou XlsxWriter:

#!/usr/bin/env python3
# vim: set fileencoding=utf-8
 
"""Vytvoření sešitu s delší tabulkou, nastavení barvy textu."""
 
import xlsxwriter
 
# vytvoření objektu reprezentujícího celý sešit
with xlsxwriter.Workbook('example17.xlsx') as workbook:
    # vložení nového listu do sešitu
    worksheet = workbook.add_worksheet()
 
    # nastavení šířky sloupců
    worksheet.set_column('A:A', 20)
 
    # barvy buněk
    colors = (
            'black',
            'blue',
            'brown',
            'cyan',
            'gray',
            'green',
            'lime',
            'magenta',
            'navy',
            'orange',
            'pink',
            'purple',
            'red',
            'silver',
            'white',
            'yellow'
            )
 
    # buňky s řetězci a různou barvou textu
    for x, color in enumerate(colors):
        style = workbook.add_format()
        style.set_font_color(color)
        worksheet.write_string(x, 0, color, style)
 
    # sešit bude uzavřen automaticky

Výsledkem bude tato tabulka:

Obrázek 17: Výsledný sešit otevřený v Gnumericu.

16. Kód barvy textu určený šesticí hexadecimálních hodnot

Kromě jména barvy je možné barvu textu, pozadí, okraje buňky atd. určit i zápisem hodnoty barvových složek ve formátu #ffggbb (takzvaný hexa triplet), v níž je každá barvová složka z barvového prostoru RGB (tedy RGB triplet) určena dvojicí hexadecimálních hodnot a tedy může nabývat hodnot z rozsahu 00 až FF. Tímto jednoduchým způsobem lze (teoreticky) použít jakoukoli barvu z palety s více než 16 miliony barev. Opět si pochopitelně ukážeme, jak lze takto zakódované barvy použít v praxi:

#!/usr/bin/env python3
# vim: set fileencoding=utf-8
 
"""Vytvoření sešitu s delší tabulkou, nastavení barvy textu."""
 
import xlsxwriter
 
# vytvoření objektu reprezentujícího celý sešit
with xlsxwriter.Workbook('example18.xlsx') as workbook:
    # vložení nového listu do sešitu
    worksheet = workbook.add_worksheet()
 
    # nastavení šířky sloupců
    worksheet.set_column('A:A', 20)
 
    # barvy buněk
    colors = (
            '#000000',
            '#0000ff',
            '#800000',
            '#00ffff',
            '#808080',
            '#008000',
            '#00ff00',
            '#ff00ff',
            '#000080',
            '#ff6600',
            '#ff00ff',
            '#800080',
            '#ff0000',
            '#c0c0c0',
            '#ffffff',
            '#ffff00',
            )
 
    # buňky s řetězci a různou barvou textu
    for x, color in enumerate(colors):
        style = workbook.add_format()
        style.set_font_color(color)
        worksheet.write_string(x, 0, color, style)
 
    # sešit bude uzavřen automaticky

Výsledek by měl v tomto případě odpovídat předchozí tabulce, protože jsou použity hexa kódy pro pojmenované barvy:

Obrázek 18: Výsledný sešit otevřený v Gnumericu.

17. Nastavení barvy pozadí buněk

Stejně snadno jako barvu textu je možné v knihovně XlsxWriter modifikovat i barvu pozadí vybrané buňky (resp. v našem případě nikoli vybrané buňky, ale buňky, do níž právě zapisujeme data). Pro tento účel se používá metoda format.set_bg_color. Pochopitelně je opět možné pro specifikaci barvy pozadí zvolit některou z pojmenovaných barev tak, jak je to ukázáno v dalším demonstračním příkladu:

style = workbook.add_format()
style.set_bg_color("red")

Tabulka, v níž jsou použity všechny pojmenované barvy podporované knihovnou XlsxWriter, se vytvoří po spuštění tohoto skriptu:

#!/usr/bin/env python3
# vim: set fileencoding=utf-8
 
"""Vytvoření sešitu s delší tabulkou, nastavení barvy pozadí."""
 
import xlsxwriter
 
# vytvoření objektu reprezentujícího celý sešit
with xlsxwriter.Workbook('example19.xlsx') as workbook:
    # vložení nového listu do sešitu
    worksheet = workbook.add_worksheet()
 
    # nastavení šířky sloupců
    worksheet.set_column('A:A', 20)
 
    # barvy buněk
    colors = (
            'black',
            'blue',
            'brown',
            'cyan',
            'gray',
            'green',
            'lime',
            'magenta',
            'navy',
            'orange',
            'pink',
            'purple',
            'red',
            'silver',
            'white',
            'yellow'
            )
 
    # buňky s řetězci a různou barvou pozadí buněk
    for x, color in enumerate(colors):
        style = workbook.add_format()
        style.set_bg_color(color)
        worksheet.write_string(x, 0, color, style)
 
    # sešit bude uzavřen automaticky

Obrázek 19: Výsledný sešit otevřený v Gnumericu.

Alternativní způsob specifikace barvy pozadí buňky spočívá v použití hexa tripletů (tady „podle CSS“ resp. „podle HTML“). To již ostatně dobře známe z předchozí kapitoly, takže jen v krátkosti:

#!/usr/bin/env python3
# vim: set fileencoding=utf-8
 
"""Vytvoření sešitu s delší tabulkou, nastavení barvy pozadí."""
 
import xlsxwriter
 
# vytvoření objektu reprezentujícího celý sešit
with xlsxwriter.Workbook('example20.xlsx') as workbook:
    # vložení nového listu do sešitu
    worksheet = workbook.add_worksheet()
 
    # nastavení šířky sloupců
    worksheet.set_column('A:A', 20)
 
    # barvy buněk
    colors = (
            '#000000',
            '#0000ff',
            '#800000',
            '#00ffff',
            '#808080',
            '#008000',
            '#00ff00',
            '#ff00ff',
            '#000080',
            '#ff6600',
            '#ff00ff',
            '#800080',
            '#ff0000',
            '#c0c0c0',
            '#ffffff',
            '#ffff00',
            )
 
    # buňky s řetězci a různou barvou pozadí buněk
    for x, color in enumerate(colors):
        style = workbook.add_format()
        style.set_bg_color(color)
        worksheet.write_string(x, 0, color, style)
 
    # sešit bude uzavřen automaticky

Obrázek 20: Výsledný sešit otevřený v Gnumericu.

18. Obsah druhé části článku

V dnešním článku jsme se seznámili pouze se základními možnostmi poskytovanými knihovnou XlsxWriter. Příště se zaměříme na některé pokročilejší vlastnosti, například na vytváření sloučených buněk (velmi často požadováno), 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ů – obrázků, formátovaných textů, komentářů atd.

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

Zdrojové kódy všech 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:

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