Manipularea foilor de calcul Excel folosind Python

Microsoft Excel este un software de foaie de calcul care este utilizat pentru stocarea și gestionarea datelor tabulare. În plus, cu Excel, calculele pot fi efectuate prin aplicarea de formule la date și pot fi produse vizualizări ale datelor.Multe sarcini efectuate în foi de calcul, cum ar fi operațiile matematice, pot fi automatizate prin programare, iar multe limbaje de programare au module pentru manipularea foilor de calcul Excel. În acest tutorial, vă vom arăta cum să utilizați modulul openpyxl al Python pentru a citi și modifica foile de calcul Excel.

Instalarea openpyxl

Înainte de a putea instala openpyxl, trebuie să instalați pip. Pip este folosit pentru a instala pachete Python. Rulați următoarea comandă în promptul de comandă pentru a vedea dacă pip este instalat.

C: \ Users \ windows> pip help

Dacă conținutul de ajutor al pip este returnat, atunci pip este instalat; în caz contrar, accesați următorul link și descărcați get-pip.fișier py:

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

Acum, executați următoarea comandă pentru a instala pip:

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

După instalarea pip, următoarea comandă poate fi utilizată pentru a instala openpyxl.

C: \ Users \ windows> pip install openpyxl

Crearea unui document Excel

În această secțiune, vom folosi modulul openpyxl pentru a crea un document Excel. Mai întâi, deschideți promptul de comandă tastând „cmd” în bara de căutare; apoi, intră

C: \ Users \ windows> python

Pentru a crea un registru de lucru Excel, vom importa modulul openpyxl și apoi vom folosi metoda „Workbook ()” pentru a crea un registru de lucru.

>>> # importul modulului openpyxl
>>> import openpyxl
>>> # Inițializarea unui registru de lucru
>>> work_book = openpyxl.Caiet de lucru ()
>>> # salvarea registrului de lucru ca „exemplu”.xlsx '
>>> carte de lucru.save ('exemplu.xlsx ')

Comenzile de mai sus creează un document Excel numit exemplu.xlsx. Apoi, vom manipula acest document Excel.

Manipularea foilor într-un document Excel

Am creat un document Excel numit exemplu.xlsx. Acum, vom manipula foile acestui document folosind Python. Modulul openpyxl are o metodă „create_sheet ()” care poate fi utilizată pentru a crea o foaie nouă. Această metodă ia două argumente: index și titlu. Index definește plasarea foii folosind orice număr întreg negativ (inclusiv 0), iar titlul este titlul foii. O listă a tuturor foilor din obiectul work_book poate fi afișată apelând lista de nume de foi.

>>> # importând openpyxl
>>> import openpyxl
>>> # încărcarea documentului Excel existent în obiectul Work_book
>>> work_book = openpyxl.load_workbook ('exemplu.xlsx ')
>>> # Crearea unei noi foi la indexul 0
>>> carte de lucru.create_sheet (index = 0, title = 'Prima foaie')

>>> # Obținerea tuturor foilor de calcul
>>> carte de lucru.nume de foi
[„Prima foaie”, „Foaie”]
>>> # Salvarea documentului Excel
>>> carte_de lucru.save ('exemplu.xlsx ')

În codul de mai sus, am creat o foaie numită First Sheet și am plasat-o la indexul 0. Foaia situată anterior la indexul 0 a fost mutată la primul index, așa cum se arată în rezultat. Acum, vom schimba numele foii originale din Foaie în Foaia a doua.

Atributul titlu deține numele foii. Pentru a redenumi o foaie, trebuie mai întâi să navigați la foaia respectivă după cum urmează.

>>> # Obținerea unei foi active din documentul Excel
>>> foaie = carte de lucru.activ
>>> # Tipărirea numelui colii
>>> print (foaie.titlu)
Prima foaie >>> # Navigarea la a doua foaie (la indexul 1)
>>> carte de lucru.activ = 1
>>> # Obținerea foaiei active
>>> foaie = carte de lucru.activ
>>> # tipărirea numelui foii
>>> print (foaie.titlu)
Foaie >>> # Modificarea titlului foii
>>> foaie.title = 'Foaia a doua'
>>> # Tipărirea titlului colii
>>> print (foaie.titlu)
A doua foaie

În mod similar, putem elimina o foaie din documentul Excel. Modulul openpyxl oferă metoda remove () pentru a elimina o foaie. Această metodă ia numele foii de eliminat ca argument și apoi elimină foaia respectivă. Putem elimina a doua foaie după cum urmează:

>>> # eliminarea unei foi după nume
>>> carte de lucru.elimina (carte de lucru ['Foaia a doua'])
>>> # obținerea tuturor foilor
>>> carte_de lucru.nume de foi
[„Prima foaie”]
>>> # salvarea documentului Excel
>>> carte de lucru.save ('exemplu.xlsx ')

Adăugarea de date la celule

Până în prezent, v-am arătat cum să creați sau să ștergeți foi într-un document Excel. Acum, vom adăuga date în celulele diferitelor foi. În acest exemplu, avem o singură foaie numită First Sheet în documentul nostru și dorim să creăm încă două foi.

>>> # importând openpyxl
>>> import openpyxl
>>> # încărcarea registrului de lucru
>>> work_book = openpyxl.load_workbook ('exemplu.xlsx ')
>>> # Crearea unei noi foi la primul index
>>> carte de lucru.create_sheet (index = 1, title = 'A doua foaie')

>>> # crearea unei noi foi la al doilea index
>>> carte_de lucru.create_sheet (index = 2, title = 'A treia foaie')

>>> # obținerea tuturor foilor
>>> carte_de lucru.nume de foi
[„Prima foaie”, „A doua foaie”, „A treia foaie”]

Acum, avem trei foi și vom adăuga date în celulele acestor foi.

>>> # Obținerea primei foi
>>> foaie_1 = carte de lucru [„Prima foaie”]
>>> # Adăugarea de date la celula „A1” a primei foi
>>> sheet_1 ['A1'] = 'Nume'
>>> # Obținerea celei de-a doua foi
>>> sheet_2 = work_book ['Second Sheet']
>>> # Adăugarea de date la celula „A1” din a doua foaie
>>> sheet_2 ['A1'] = 'ID'
>>> # Obținerea celei de-a treia foi
>>> sheet_3 = work_book ['A treia foaie']
>>> # Adăugarea de date la celula „A1” din a treia foaie
>>> sheet_3 ['A1'] = 'Note'
>>> # Salvarea registrului de lucru Excel
>>> carte_de lucru.save ('exemplu.xlsx ')

Citirea foilor Excel

Modulul openpyxl folosește atributul de valoare al unei celule pentru a stoca datele acelei celule. Putem citi datele dintr-o celulă apelând atributul de valoare al celulei. Acum, avem trei foi și fiecare foaie conține câteva date. Putem citi datele folosind următoarele funcții în openpyxl:

>>> # importând openpyxl
>>> import openpyxl
>>> # încărcarea registrului de lucru
>>> work_book = openpyxl.load_workbook ('exemplu.xlsx ')
>>> # Obținerea primei foi
>>> foaie_1 = carte de lucru [„Prima foaie”]
>>> # Obținerea celei de-a doua foi
>>> sheet_2 = work_book ['Second Sheet']
>>> # Obținerea celei de-a treia foi
>>> sheet_3 = work_book ['A treia foaie']
>>> # tipărirea datelor din celula „A1” a primei coli
>>> tipăriți (foaie_1 ['A1'].valoare)
Nume
>>> # tipărirea datelor din celula „A1” din a doua foaie
>>> tipăriți (foaia_2 ['A1'].valoare)
ID
>>> # tipărirea datelor din celula „A1” a celei de-a treia coli
>>> print (sheet_3 ['A1'].valoare)
Note

Schimbarea fonturilor și culorilor

În continuare, vă vom arăta cum să schimbați fontul unei celule utilizând funcția Font (). Mai întâi, importați openpyxl.stil de obiect. Metoda Font () ia o listă de argumente, inclusiv:

nume (șir): numele fontului
dimensiune (int sau float): dimensiunea fontului
subliniere (șir): tipul de subliniat
culoare (șir): culoarea hexazecimală a textului
italic (bool): dacă fontul este cursiv
bold (bool): dacă fontul este îngroșat

Pentru a aplica stiluri, trebuie mai întâi să creăm un obiect trecând toți parametrii la metoda Font (). Apoi, selectăm foaia, iar în interiorul foii, selectăm celula căreia dorim să-i aplicăm stilul. Apoi, aplicăm stil celulei selectate.

>>> # importând openpyxl
>>> import openpyxl
>>> # importarea metodei Font din openpyxl.stiluri
>>> din openpyxl.stiluri import Font
>>> # încărcarea registrului de lucru
>>> work_book = openpyxl.load_workbook ('exemplu.xlsx ')
>>> # Crearea obiectului de stil
>>> stil = Font (nume = 'Consolas', dimensiune = 13, bold = True,
... italic = False)
>>> # Selectarea foii din registrul de lucru
>>> foaie_1 = carte de lucru [„Prima foaie”]
>>> # Selectând celula pe care dorim să o adăugăm stiluri
>>> a1 = foaie_1 ['A1']
>>> # Aplicarea stilurilor la celulă
>>> a1.font = stil
>>> # Salvarea registrului de lucru
>>> carte_de lucru.save ('exemplu.xlsx ')

Aplicarea frontierelor la celule

Putem aplica margini celulelor dintr-o foaie Excel folosind metodele Border () și Side () din openpyxl.stiluri.modulul de margini. Putem trece diferite funcții ca parametri la metoda Border (). Următoarele sunt câteva dintre funcțiile care sunt transmise ca parametri la metoda Border () pentru a defini dimensiunile chenarului.

stânga: aplicați o margine în partea stângă a unei celule
dreapta: aplicați o margine în partea dreaptă a unei celule
top: aplicați un chenar în partea de sus a unei celule
fund: aplicați o margine în partea de jos a unei celule

Aceste funcții iau ca parametri atributele de stil. Atributul de stil definește stilul chenarului (e.g., solid, punctat). Parametrii de stil pot avea oricare dintre următoarele valori.

dubla: o margine de linie dublă
zguduit: o margine punctată
subţire: o margine subțire
mediu: o margine medie
mediumDashDot: o margine punctată și punctată de greutate medie
gros: o margine groasă
dashDot: o margine punctată și punctată
păr: o margine foarte subțire
punctat: o margine punctată

Acum, vom aplica diferite tipuri de chenare diferitelor celule ale foilor noastre de calcul. Mai întâi, selectăm celule și apoi definim stiluri de margine și aplicăm aceste stiluri diferitelor celule.

>>> # importând openpyxl
>>> import openpyxl
>>> # importarea claselor Border și Side
>>> din openpyxl.stiluri.frontiere import Border, Side
>>> # încărcarea registrului de lucru
>>> work_book = openpyxl.load_workbook ('exemplu.xlsx ')
>>> # Selectarea foii
>>> foaie_1 = carte de lucru [„Prima foaie”]
>>> # Selectarea diferitelor celule din foaie
>>> celulă_1 = foaie_1 ['A1']
>>> celulă_2 = foaie_1 ['B2']
>>> cell_3 = sheet_1 ['C3']
>>> # Definirea diferitelor stiluri de margine
>>> stil_1 = chenar (jos = lateral (stil = „punctat”))
>>> style_2 = Border (dreapta = Side (style = 'thin'))
>>> style_3 = Border (top = Side (style = 'dashDot'))
>>> # aplicarea stilurilor de chenar la celule
>>> celulă_1.border = stil_1
>>> celula_2.border = stil_2
>>> celula_3.border = stil_3
>>> # Salvarea registrului de lucru
>>> carte_de lucru.save ('exemplu.xlsx ')

Reglarea dimensiunilor rândurilor și coloanelor

Înălțimea rândului și lățimea coloanei unui document Excel pot fi, de asemenea, ajustate folosind Python. Modulul openpyxl are două metode încorporate care pot fi utilizate pentru a efectua aceste acțiuni. Mai întâi, selectăm foaia căreia dorim să o schimbăm lățimea coloanei sau înălțimea rândului. Apoi, aplicăm o metodă pe rândul sau coloana specifică.

>>> # importând openpyxl
>>> import openpyxl
>>> # încărcarea registrului de lucru
>>> work_book = openpyxl.load_workbook ('exemplu.xlsx ')
>>> # selectare foaie
>>> foaie_1 = carte de lucru [„Prima foaie”]
>>> # schimbarea înălțimii primului rând
>>> foaie_1.row_dimensions [1].înălțime = 50
>>> # Salvarea registrului de lucru
>>> carte_de lucru.save ('exemplu.xlsx ')

În mod similar, putem modifica lățimea unei coloane folosind următorul cod

>>> # selectarea foii din registrul de lucru Excel
>>> sheet_2 = work_book ['Second Sheet']
>>> # modificarea lățimii coloanei A
>>> foaie_2.column_dimensions ['A'].lățime = 50
>>> # Salvarea registrului de lucru
>>> carte_de lucru.save ('exemplu.xlsx ')

Codul de mai sus va schimba înălțimea primului rând la 50 de puncte și lățimea coloanei A la 50 de puncte.

Fuzionarea și fuzionarea celulelor

Când lucrăm cu foi de calcul Excel, de multe ori trebuie să îmbinăm și să unim celulele. Pentru a îmbina celulele în Python, poate fi utilizată o funcție simplă bazată pe openpyxl. Modulul openpyxl oferă metoda merge_cells (), care poate fi utilizată pentru a îmbina celulele în Excel. Noua celulă va prelua numele celulei din stânga sus. De exemplu, dacă vrem să îmbinăm celulele din celula A1 în celula B2, atunci celula nou formată va fi denumită A1. Pentru a îmbina celulele folosind openpyxl, selectăm mai întâi foaia și apoi aplicăm metoda merge_cells () pe foaie.

>>> # importul modulului openpyxl
>>> import openpyxl
>>> # încărcarea registrului de lucru
>>> work_book = openpyxl.load_workbook ('exemplu.xlsx ')
>>> # selectarea primei foi din registrul de lucru Excel
>>> foaie_1 = carte de lucru [„Prima foaie”]
>>> # fuzionarea celulelor de la A1 la B2 în foaia 1
>>> foaie_1.merge_cells ('A1: B2')
>>> registru de lucru de salvare
>>> carte de lucru.save ('exemplu.xlsx ')

În mod similar, metoda unmerge_cells () poate fi utilizată pentru a anula celulele dintr-o foaie de calcul Excel. Următorul cod poate fi utilizat pentru a debarca celulele:

>>> # selectarea foii din registrul de lucru
>>> foaie_1 = carte de lucru [„Prima foaie”]
>>> # celule care nu fuzionează de la A1 la B2
>>> foaie_1.unmerge_cells („A1: B2”)
>>> registru de lucru de salvare
>>> carte de lucru.save ('exemplu.xlsx ')

Concluzie

Foile de calcul Excel sunt utilizate în mod obișnuit pentru manipularea datelor. Cu toate acestea, astfel de sarcini pot fi monotone. Prin urmare, în astfel de cazuri, programarea poate fi utilizată pentru automatizarea manipulării foilor de calcul.

În acest articol, am discutat câteva dintre funcțiile utile ale modulului openpyxl al Python. V-am arătat cum să creați, să citiți, să eliminați și să modificați foile de calcul Excel, cum să schimbați stilul, să aplicați fontul, marginile și dimensiunile celulelor și cum să îmbinați și să uniți celulele. Prin aplicarea acestor funcții, puteți automatiza multe sarcini de manipulare a foilor de calcul folosind Python.