Manipulácia s tabuľkami programu Excel pomocou jazyka Python

Microsoft Excel je tabuľkový softvér, ktorý sa používa na ukladanie a správu tabuľkových údajov. V programe Excel možno ďalej vykonávať výpočty pomocou vzorcov na údaje a vytvárať vizualizácie údajov.Mnoho úloh vykonávaných v tabuľkách, napríklad matematické operácie, je možné automatizovať pomocou programovania a veľa programovacích jazykov obsahuje moduly na manipuláciu s tabuľkami programu Excel. V tomto výučbe vám ukážeme, ako používať modul python's openpyxl na čítanie a úpravu tabuliek programu Excel.

Inštaluje sa openpyxl

Pred inštaláciou openpyxl musíte nainštalovať pip. Pip sa používa na inštaláciu balíkov Pythonu. Spustením nasledujúceho príkazu v príkazovom riadku skontrolujte, či je pip nainštalovaný.

C: \ Users \ windows> pip help

Ak sa vráti obsah pomoci pip, potom je pip nainštalovaný; inak choďte na nasledujúci odkaz a stiahnite si get-pip.súbor py:

https: // bootstrap.pypa.io / get-pip.py

Teraz spustite nasledujúci príkaz na inštaláciu pipu:

C: \ Users \ windows> python get-pip.py

Po inštalácii pipu je možné na inštaláciu openpyxl použiť nasledujúci príkaz.

C: \ Users \ windows> pip install openpyxl

Vytvorenie dokumentu programu Excel

V tejto časti použijeme modul openpyxl na vytvorenie dokumentu programu Excel. Najskôr otvorte príkazový riadok zadaním výrazu „cmd“ do vyhľadávacieho panela; potom vstúpte

C: \ Users \ windows> python

Na vytvorenie zošita programu Excel naimportujeme modul openpyxl a na vytvorenie zošita potom použijeme metódu 'Workbook ()'.

>>> # import modulu openpyxl
>>> import openpyxl
>>> # Inicializácia zošita
>>> work_book = openpyxl.Zošit ()
>>> # uloženie zošita ako príklad.xlsx '
>>> pracovný_kniha.uložiť („príklad.xlsx ')

Vyššie uvedené príkazy vytvárajú dokument programu Excel s názvom príklad.xlsx. Ďalej s týmto dokumentom programu Excel budeme manipulovať.

Manipulácia s hárkami v dokumente programu Excel

Vytvorili sme dokument programu Excel s názvom príklad.xlsx. Teraz budeme manipulovať s listami tohto dokumentu pomocou Pythonu. Modul openpyxl má metódu 'create_sheet ()', ktorú je možné použiť na vytvorenie nového hárka. Táto metóda vyžaduje dva argumenty: index a názov. Index definuje umiestnenie hárku pomocou nezáporného celého čísla (vrátane 0) a nadpis je nadpis hárka. Zoznam všetkých hárkov v objekte work_book je možné zobraziť vyvolaním zoznamu hárkov.

>>> # import openpyxl
>>> import openpyxl
>>> # načítanie existujúceho dokumentu programu Excel do objektu work_book
>>> work_book = openpyxl.load_workbook ('príklad.xlsx ')
>>> # Vytvorenie nového hárku s 0. indexom
>>> pracovný_kniha.create_sheet (index = 0, title = 'Prvý hárok')

>>> # Získavanie všetkých hárkov
>>> pracovný_kniha.názvy listov
[„Prvý hárok“, „Hárok“]
>>> # Uloženie dokumentu programu Excel
>>> pracovný_kniha.uložiť („príklad.xlsx ')

Vo vyššie uvedenom kóde sme vytvorili hárok s názvom Prvý hárok a umiestnili sme ho na 0. index. Hárok predtým umiestnený na 0. indexe sa presunul do prvého indexu, ako je znázornené na výstupe. Teraz zmeníme názov pôvodného listu z listu na druhý list.

Atribút title obsahuje názov hárka. Ak chcete premenovať hárok, musíme k nemu najskôr prejsť nasledujúcim spôsobom.

>>> # Získanie aktívneho hárka z dokumentu programu Excel
>>> list = pracovny_kniha.aktívny
>>> # Názov tlačového listu
>>> tlač (list.názov)
Prvý hárok >>> # Navigácia na druhý hárok (na indexe 1)
>>> pracovný_kniha.aktívny = 1
>>> # Získanie aktívneho listu
>>> list = pracovný_kniha.aktívny
>>> # tlač listu Názov
>>> tlač (list.názov)
Hárok >>> # Zmena názvu hárka
>>> list.title = 'Druhý hárok'
>>> # Názov tlačového listu
>>> tlač (list.názov)
Druhý list

Podobne môžeme odstrániť hárok z dokumentu programu Excel. Modul openpyxl ponúka metódu remove () na odstránenie hárku. Táto metóda prevezme ako argument názov hárka, ktorý sa má odstrániť, a potom tento hárok odstráni. Druhý list môžeme odstrániť nasledovne:

>>> # odstránenie tabuľky podľa názvu
>>> pracovný_kniha.remove (work_book ['Second Sheet'])
>>> # získanie všetkých listov
>>> pracovný_kniha.názvy listov
['Prvý hárok']
>>> # ukladanie dokumentu programu Excel
>>> pracovný_kniha.uložiť („príklad.xlsx ')

Pridávanie údajov do buniek

Doteraz sme vám ukázali, ako vytvárať alebo mazať hárky v dokumente programu Excel. Teraz pridáme údaje do buniek rôznych hárkov. V tomto príklade máme v dokumente jeden hárok s názvom Prvý hárok a chceme vytvoriť ďalšie dva hárky.

>>> # import openpyxl
>>> import openpyxl
>>> # načítanie zošita
>>> work_book = openpyxl.load_workbook ('príklad.xlsx ')
>>> # Vytvorenie nového hárku pri 1. indexe
>>> pracovný_kniha.create_sheet (index = 1, title = 'Druhý hárok')

>>> # vytvorenie nového hárku s 2. indexom
>>> pracovný_kniha.create_sheet (index = 2, title = 'tretí list')

>>> # získanie všetkých listov
>>> pracovný_kniha.názvy listov
[„Prvý hárok“, „Druhý hárok“, „Tretí hárok“]

Teraz máme tri listy a do buniek týchto listov pridáme údaje.

>>> # Získanie prvého hárku
>>> sheet_1 = work_book ['First Sheet']
>>> # Pridanie údajov do bunky „A1“ prvého hárku
>>> sheet_1 ['A1'] = 'Meno'
>>> # Získanie druhého listu
>>> sheet_2 = work_book ['Second Sheet']
>>> # Pridanie údajov do bunky „A1“ druhého listu
>>> sheet_2 ['A1'] = 'ID'
>>> # Získanie tretieho listu
>>> sheet_3 = work_book ['Third Sheet']
>>> # Pridanie údajov do bunky „A1“ tretieho listu
>>> sheet_3 ['A1'] = 'Známky'
>>> # Ukladanie zošita programu Excel
>>> pracovný_kniha.uložiť („príklad.xlsx ')

Čítanie hárkov programu Excel

Modul openpyxl používa atribút hodnoty bunky na ukladanie údajov tejto bunky. Môžeme čítať údaje v bunke volaním atribútu hodnoty bunky. Teraz máme tri listy a každý list obsahuje niektoré údaje. Dáta môžeme načítať pomocou nasledujúcich funkcií v openpyxl:

>>> # import openpyxl
>>> import openpyxl
>>> # načítanie zošita
>>> work_book = openpyxl.load_workbook ('príklad.xlsx ')
>>> # Získanie prvého hárku
>>> sheet_1 = work_book ['First Sheet']
>>> # Získanie druhého listu
>>> sheet_2 = work_book ['Second Sheet']
>>> # Získanie tretieho listu
>>> sheet_3 = work_book ['Third Sheet']
>>> # tlač údajov z bunky „A1“ prvého hárku
>>> tlač (list_1 ['A1']).hodnota)
názov
>>> # tlač údajov z bunky „A1“ druhého listu
>>> tlač (list_2 ['A1']).hodnota)
ID
>>> # tlač údajov z bunky „A1“ tretieho listu
>>> tlač (list_3 ['A1']).hodnota)
Známky

Zmena písma a farieb

Ďalej vám ukážeme, ako zmeniť písmo bunky pomocou funkcie Font (). Najskôr importujte súbor openpyxl.štýly objekt. Metóda Font () obsahuje zoznam argumentov vrátane:

meno (reťazec): názov písma
veľkosť (int alebo float): veľkosť písma
podčiarknutie (reťazec): typ podčiarknutia
farba (struna): hexadecimálna farba textu
kurzíva (bool): či je písmo kurzívou
tučne (bool): či je písmo tučné

Ak chcete použiť štýly, musíme najskôr vytvoriť objekt odovzdaním všetkých parametrov metóde Font (). Potom vyberieme hárok a vo vnútri hárku vyberieme bunku, na ktorú chceme štýl použiť. Potom na vybranú bunku použijeme štýl.

>>> # import openpyxl
>>> import openpyxl
>>> # importovanie metódy písma z openpyxl.štýly
>>> z openpyxl.štýly import písma
>>> # načítanie zošita
>>> work_book = openpyxl.load_workbook ('príklad.xlsx ')
>>> # Vytváranie štýlového objektu
>>> style = Font (name = 'Consolas', size = 13, bold = True,
... italic = False)
>>> # Výber hárka zo zošita
>>> sheet_1 = work_book ['First Sheet']
>>> # Vyberieme bunku, do ktorej chceme pridať štýly
>>> a1 = sheet_1 ['A1']
>>> # Aplikácia štýlov na bunku
>>> a1.font = štýl
>>> # Ukladanie zošita
>>> pracovný_kniha.uložiť („príklad.xlsx ')

Aplikácia hraníc na bunky

Okraje môžeme aplikovať na bunky v hárku programu Excel pomocou metód ohraničenia () a bočných () metódy openpyxl.štýly.modul hraníc. Metóde Border () môžeme odovzdať rôzne funkcie ako parametre. Nasleduje niekoľko funkcií, ktoré sa odovzdávajú ako parametre metóde Border () na definovanie rozmerov orámovania.

vľavo: použiť orámovanie na ľavú stranu bunky
správny: použiť orámovanie na pravú stranu bunky
hore: použitie orámovania na hornú časť bunky
dole: použitie orámovania na spodok bunky

Tieto funkcie berú atribúty štýlu ako parametre. Atribút style definuje štýl ohraničenia (napr.g., plné, prerušované). Parametre štýlu môžu mať ktorúkoľvek z nasledujúcich hodnôt.

dvojitý: dvojité orámovanie
prerušovaná: prerušovaná hranica
tenký: tenký okraj
stredná: stredná hranica
mediumDashDot: prerušovaná a bodkovaná hranica strednej hmotnosti
hrubý: hrubý okraj
dashDot: prerušované a bodkované ohraničenie
vlasy: veľmi tenký okraj
bodkovaný: bodkovaný okraj

Teraz použijeme rôzne typy ohraničení na rôzne bunky našich tabuliek. Najskôr vyberieme bunky a potom definujeme štýly ohraničenia a použijeme tieto štýly na rôzne bunky.

>>> # import openpyxl
>>> import openpyxl
>>> # import hraničných a bočných tried
>>> z openpyxl.štýly.border import Border, Side
>>> # načítanie zošita
>>> work_book = openpyxl.load_workbook ('príklad.xlsx ')
>>> # Výber hárka
>>> sheet_1 = work_book ['First Sheet']
>>> # Výber rôznych buniek z hárka
>>> cell_1 = sheet_1 ['A1']
>>> cell_2 = sheet_1 ['B2']
>>> cell_3 = sheet_1 ['C3']
>>> # Definovanie rôznych štýlov ohraničenia
>>> style_1 = orámovanie (dolné = bočné (štýl = 'bodkované'))
>>> style_2 = Border (right = Side (style = 'thin')))
>>> style_3 = Orámovanie (hore = Bočné (style = 'dashDot'))
>>> # použitie štýlov ohraničenia na bunky
>>> cell_1.border = style_1
>>> bunka_2.border = style_2
>>> cell_3.border = style_3
>>> # Ukladanie zošita
>>> pracovný_kniha.uložiť („príklad.xlsx ')

Úprava rozmerov riadkov a stĺpcov

Výška riadku a šírka stĺpca dokumentu programu Excel sa dajú upraviť aj pomocou Pythonu. Modul openpyxl má dve zabudované metódy, ktoré možno použiť na vykonávanie týchto akcií. Najskôr vyberieme hárok, ktorého chceme zmeniť šírku stĺpca alebo výšku riadku. Potom použijeme metódu na konkrétny riadok alebo stĺpec.

>>> # import openpyxl
>>> import openpyxl
>>> # načítanie zošita
>>> work_book = openpyxl.load_workbook ('príklad.xlsx ')
>>> # výber listu
>>> sheet_1 = work_book ['First Sheet']
>>> # zmena výšky prvého riadku
>>> sheet_1.row_dimensions [1].výška = 50
>>> # Ukladanie zošita
>>> pracovný_kniha.uložiť („príklad.xlsx ')

Podobne môžeme zmeniť šírku stĺpca pomocou nasledujúceho kódu

>>> # výber listu zo zošita programu Excel
>>> sheet_2 = work_book ['Second Sheet']
>>> # zmena šírky stĺpca
>>> list_2.column_dimensions ['A'].šírka = 50
>>> # Ukladanie zošita
>>> pracovný_kniha.uložiť („príklad.xlsx ')

Vyššie uvedený kód zmení výšku prvého riadku na 50 bodov a šírku stĺpca A na 50 bodov.

Zlúčenie a zlúčenie buniek

Pri práci s tabuľkami programu Excel často potrebujeme bunky zlúčiť a zrušiť. Na zlúčenie buniek v Pythone je možné použiť jednoduchú funkciu založenú na openpyxl. Modul openpyxl ponúka metódu merge_cells (), ktorú je možné použiť na zlúčenie buniek v programe Excel. Nová bunka prevezme názov bunky vľavo hore. Napríklad, ak chceme zlúčiť bunky z bunky A1 do bunky B2, potom sa novo vytvorená bunka bude označovať ako A1. Na zlúčenie buniek pomocou openpyxl najskôr vyberieme hárok a potom na hárok použijeme metódu merge_cells ().

>>> # import modulu openpyxl
>>> import openpyxl
>>> # načítanie zošita
>>> work_book = openpyxl.load_workbook ('príklad.xlsx ')
>>> # výber prvého listu zo zošita programu Excel
>>> sheet_1 = work_book ['First Sheet']
>>> # zlučovanie buniek z A1 do B2 v hárku 1
>>> sheet_1.merge_cells ('A1: B2')
>>> # ukladací zošit
>>> pracovný_kniha.uložiť („príklad.xlsx ')

Podobne je možné použiť metódu unmerge_cells () na zlúčenie buniek v excelovskej tabuľke. Na rozdelenie buniek je možné použiť nasledujúci kód:

>>> # výber listu zo zošita
>>> sheet_1 = work_book ['First Sheet']
>>> # oddeľovanie buniek od A1 do B2
>>> sheet_1.unmerge_cells ('A1: B2')
>>> # ukladací zošit
>>> pracovný_kniha.uložiť („príklad.xlsx ')

Záver

Na manipuláciu s údajmi sa bežne používajú tabuľky programu Excel. Takéto úlohy však môžu byť jednotvárne. Preto v takýchto prípadoch možno programovanie použiť na automatizáciu manipulácie s tabuľkami.

V tomto článku sme diskutovali o niektorých užitočných funkciách modulu python's openpyxl. Ukázali sme vám, ako vytvárať, čítať, odstraňovať a upravovať tabuľky programu Excel, ako meniť štýl, používať písmo, ohraničenia a rozmery buniek a ako zlúčiť a zlúčiť bunky. Použitím týchto funkcií môžete pomocou Pythonu automatizovať mnoho úloh manipulácie s tabuľkami.