Excel-spreadsheets manipuleren met Python

Microsoft Excel is spreadsheetsoftware die wordt gebruikt om tabelgegevens op te slaan en te beheren. Bovendien kunnen met Excel berekeningen worden uitgevoerd door formules op de gegevens toe te passen en kunnen gegevensvisualisaties worden gemaakt.Veel taken die in spreadsheets worden uitgevoerd, zoals wiskundige bewerkingen, kunnen worden geautomatiseerd via programmeren, en veel programmeertalen hebben modules voor het manipuleren van Excel-spreadsheets. In deze zelfstudie laten we u zien hoe u de openpyxl-module van Python gebruikt om Excel-spreadsheets te lezen en aan te passen.

Openpyxl installeren

Voordat je openpyxl kunt installeren, moet je pip . installeren. Pip wordt gebruikt om Python-pakketten te installeren. Voer de volgende opdracht uit in de opdrachtprompt om te zien of pip is geïnstalleerd:.

C:\Users\windows> pip help

Als de help-inhoud van pip wordt geretourneerd, is pip geïnstalleerd; ga anders naar de volgende link en download de get-pip.py-bestand:

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

Voer nu de volgende opdracht uit om pip te installeren:

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

Na het installeren van pip kan de volgende opdracht worden gebruikt om openpyxl te installeren install.

C:\Users\windows> pip install openpyxl

Een Excel-document maken

In deze sectie zullen we de openpyxl-module gebruiken om een Excel-document te maken. Open eerst de opdrachtprompt door 'cmd' in de zoekbalk te typen; voer dan in

C:\Gebruikers\windows> python

Om een Excel-werkmap te maken, zullen we de openpyxl-module importeren en vervolgens de 'Workbook()'-methode gebruiken om een werkmap te maken.

>>> # importeren van openpyxl-module
>>> importeer openpyxl
>>> # Een werkmap initialiseren
>>> werkboek = openpyxl.Werkboek()
>>> # werkmap opslaan als 'voorbeeld'.xlsx'
>>> werkboek.opslaan('voorbeeld.xlsx')

De bovenstaande opdrachten maken een Excel-document met de naam voorbeeld.xlsx. Vervolgens zullen we dit Excel-document manipuleren.

Werkbladen in een Excel-document manipuleren

We hebben een Excel-document gemaakt met de naam voorbeeld.xlsx. Nu zullen we de bladen van dit document manipuleren met Python. De openpyxl-module heeft een 'create_sheet()'-methode die kan worden gebruikt om een nieuw blad te maken. Deze methode heeft twee argumenten: index en titel. Index definieert de plaatsing van het blad met behulp van een niet-negatief geheel getal (inclusief 0), en titel is de titel van het blad. Een lijst van alle werkbladen in het werkboek-object kan worden weergegeven door de lijst met werkbladnamen aan te roepen.

>>> # importeer openpyxl
>>> importeer openpyxl
>>> # laden van bestaand Excel-document in werkboekobject
>>> werkboek = openpyxl.load_workbook('voorbeeld.xlsx')
>>> # Een nieuw blad maken op de 0e index
>>> werkboek.create_sheet(index=0, title='Eerste blad')

>>> # Alle bladen ophalen
>>> werkboek.bladnamen
['Eerste blad', 'Blad']
>>> # Excel-document opslaan
>>> werkboek.opslaan('voorbeeld.xlsx')

In de bovenstaande code hebben we een blad gemaakt met de naam Eerste blad en dit op de 0e index geplaatst. Het blad dat zich eerder op de 0e index bevond, is verplaatst naar de 1e index, zoals weergegeven in de uitvoer. Nu gaan we de naam van het originele blad wijzigen van Blad naar Tweede blad.

Het titelattribuut bevat de naam van het blad. Om een blad te hernoemen, moeten we eerst als volgt naar dat blad navigeren:.

>>> # Actief blad krijgen van Excel-document
>>> blad = werkboek.actief
>>> # Naam afdrukblad
>>> afdrukken (vel).titel)
Eerste blad >>> # Navigeren naar tweede blad (bij index 1)
>>> werkboek.actief = 1
>>> # Actief blad krijgen
>>> blad = werkboek.actief
>>> # bladnaam afdrukken
>>> afdrukken (vel).titel)
Blad >>> # Bladtitel wijzigen
>>> blad.title = 'Tweede Blad'
>>> # Titel afdrukblad
>>> afdrukken (vel).titel)
Tweede blad

Op dezelfde manier kunnen we een blad uit het Excel-document verwijderen. De openpyxl-module biedt de methode remove() om een blad te verwijderen. Deze methode neemt de naam van het te verwijderen blad als argument en verwijdert vervolgens dat blad. We kunnen Second Sheet als volgt verwijderen:

>>> # een blad op naam verwijderen
>>> werkboek.remove(work_book['Tweede blad'])
>>> # alle bladen halen
>>> werkboek.bladnamen
['Eerste blad']
>>> # Excel-document opslaan
>>> werkboek.opslaan('voorbeeld.xlsx')

Gegevens aan cellen toevoegen

Tot nu toe hebben we u laten zien hoe u bladen in een Excel-document kunt maken of verwijderen. Nu gaan we gegevens toevoegen aan de cellen van verschillende bladen. In dit voorbeeld hebben we een enkel blad met de naam Eerste blad in ons document, en we willen nog twee bladen maken.

>>> # importeer openpyxl
>>> importeer openpyxl
>>> # werkmap laden
>>> werkboek = openpyxl.load_workbook('voorbeeld.xlsx')
>>> # Een nieuw blad maken bij 1e index
>>> werkboek.create_sheet(index=1, title='Tweede blad')

>>> # een nieuw blad maken bij 2e index
>>> werkboek.create_sheet(index=2, title='Derde blad')

>>> # alle bladen halen
>>> werkboek.bladnamen
['Eerste blad', 'Tweede blad', 'Derde blad']

Nu hebben we drie bladen en we zullen gegevens toevoegen aan de cellen van deze bladen.

>>> # Eerste blad halen
>>> blad_1 = werkboek['Eerste blad']
>>> # Gegevens toevoegen aan cel 'A1' van eerste blad
>>> blad_1['A1'] = 'Naam'
>>> # Tweede blad krijgen
>>> blad_2 = werkboek['Tweede blad']
>>> # Gegevens toevoegen aan cel 'A1' van tweede blad
>>> sheet_2['A1'] = 'ID'
>>> # Derde blad krijgen
>>> blad_3 = werkboek['Derde blad']
>>> # Gegevens toevoegen aan cel 'A1' van derde blad
>>> sheet_3['A1'] = 'Cijfers'
>>> # Excel-werkmap opslaan
>>> werkboek.opslaan('voorbeeld.xlsx')

Excel-bladen lezen

De openpyxl-module gebruikt het waardeattribuut van een cel om de gegevens van die cel op te slaan. We kunnen de gegevens in een cel lezen door het waardeattribuut van de cel aan te roepen. Nu hebben we drie bladen en elk blad bevat enkele gegevens. We kunnen de gegevens lezen door de volgende functies in openpyxl te gebruiken:

>>> # importeer openpyxl
>>> importeer openpyxl
>>> # werkmap laden
>>> werkboek = openpyxl.load_workbook('voorbeeld.xlsx')
>>> # Eerste blad halen
>>> blad_1 = werkboek['Eerste blad']
>>> # Tweede blad krijgen
>>> blad_2 = werkboek['Tweede blad']
>>> # Derde blad krijgen
>>> blad_3 = werkboek['Derde blad']
>>> # gegevens afdrukken uit cel 'A1' van eerste vel
>>> afdrukken(vel_1['A1'].waarde)
Naam
>>> # gegevens afdrukken uit cel 'A1' van tweede blad
>>> afdrukken(vel_2['A1'].waarde)
ID kaart
>>> # gegevens afdrukken uit cel 'A1' van derde blad
>>> afdrukken(vel_3['A1'].waarde)
Cijfers

Lettertypen en kleuren wijzigen

Vervolgens laten we u zien hoe u het lettertype van een cel kunt wijzigen met behulp van de functie Font(). Importeer eerst de openpyxl.stijlen object. De methode Font() gebruikt een lijst met argumenten, waaronder:

naam (tekenreeks): de naam van het lettertype
maat (int of float): de grootte van het lettertype
onderstrepen (tekenreeks): het onderstrepingstype
kleur (string): de hexadecimale kleur van de tekst
cursief (boos): of het lettertype cursief is
vet (bool): of het lettertype vetgedrukt is

Om stijlen toe te passen, moeten we eerst een object maken door alle parameters door te geven aan de methode Font(). Vervolgens selecteren we het blad en binnen het blad selecteren we de cel waarop we de stijl willen toepassen. Vervolgens passen we stijl toe op de geselecteerde cel.

>>> # importeer openpyxl
>>> importeer openpyxl
>>> # Font methode importeren vanuit openpyxl.stijlen
>>> van openpyxl.stijlen importeren Lettertype
>>> # werkmap laden
>>> werkboek = openpyxl.load_workbook('voorbeeld.xlsx')
>>> # Stijlobject maken
>>> stijl = Lettertype (naam = 'Consolas', grootte = 13, vet = True,
... cursief=Fout)
>>> # Blad selecteren uit werkmap
>>> blad_1 = werkboek['Eerste blad']
>>> # De cel selecteren waaraan we stijlen willen toevoegen
>>> a1 = blad_1['A1']
>>> # Stijlen toepassen op de cel
>>> een1.lettertype = stijl
>>> # Werkmap opslaan
>>> werkboek.opslaan('voorbeeld.xlsx')

Randen toepassen op cellen

We kunnen randen toepassen op de cellen in een Excel-blad met behulp van de methoden Border() en Side() van de openpyxl.stijlen.grenzen module. We kunnen verschillende functies als parameters doorgeven aan de methode Border(). Hieronder volgen enkele van de functies die als parameters worden doorgegeven aan de methode Border() om de afmetingen van de rand te definiëren:.

links: een rand toepassen aan de linkerkant van een cel
Rechtsaf: een rand toepassen aan de rechterkant van een cel
top: een rand toepassen op de bovenkant van een cel
bodem: een rand toepassen op de onderkant van een cel

Deze functies nemen stijlattributen als parameters. Het stijlkenmerk definieert de stijl van de rand (e.g., vast, gestreept). Stijlparameters kunnen een van de volgende waarden hebben:.

dubbele: een dubbele lijnrand
gestreept: een gestippelde rand
dun: een dunne rand
medium: een medium rand
mediumDashDot: een gestippelde en gestippelde rand van gemiddeld gewicht
dik: een dikke rand
dashDot: een gestippelde en gestippelde rand
haar-: een zeer dunne rand
gestippeld: een gestippelde rand

Nu gaan we verschillende soorten randen toepassen op verschillende cellen van onze spreadsheets. Eerst selecteren we cellen en vervolgens definiëren we randstijlen en passen deze stijlen toe op verschillende cellen.

>>> # importeer openpyxl
>>> importeer openpyxl
>>> # Border en Side klassen importeren
>>> van openpyxl.stijlen.randen importeren Rand, Zijkant
>>> # werkmap laden
>>> werkboek = openpyxl.load_workbook('voorbeeld.xlsx')
>>> # Blad selecteren
>>> blad_1 = werkboek['Eerste blad']
>>> # Verschillende cellen selecteren uit blad from
>>> cel_1 = blad_1['A1']
>>> cel_2 = blad_1['B2']
>>> cel_3 = blad_1['C3']
>>> # Verschillende randstijlen definiëren
>>> style_1 = Border(bottom=Side(style='gestippeld'))
>>> style_2 = Rand (rechts=Zijde(stijl='dun'))
>>> style_3 = Rand(top=Zijde(style='dashDot'))
>>> # randstijlen toepassen op de cellen
>>> cel_1.rand = style_1
>>> cel_2.rand = style_2
>>> cel_3.rand = style_3
>>> # Werkmap opslaan
>>> werkboek.opslaan('voorbeeld.xlsx')

Rij- en kolomafmetingen aanpassen

De rijhoogte en kolombreedte van een Excel-document kunnen ook worden aangepast met Python. De openpyxl-module heeft twee ingebouwde methoden die kunnen worden gebruikt om deze acties uit te voeren. Eerst selecteren we het blad waarvan we de kolombreedte of rijhoogte willen wijzigen. Vervolgens passen we een methode toe op de specifieke rij of kolom.

>>> # importeren van openpyxl
>>> importeer openpyxl
>>> # werkmap laden
>>> werkboek = openpyxl.load_workbook('voorbeeld.xlsx')
>>> # blad selecteren
>>> blad_1 = werkboek['Eerste blad']
>>> # de hoogte van de eerste rij wijzigen
>>> blad_1.rij_dimensies[1].hoogte = 50
>>> # Werkmap opslaan
>>> werkboek.opslaan('voorbeeld.xlsx')

Op dezelfde manier kunnen we de breedte van een kolom wijzigen met behulp van de volgende code:

>>> # blad selecteren uit Excel-werkmap
>>> blad_2 = werkboek['Tweede blad']
>>> # de breedte van een kolom wijzigen
>>> blad_2.column_dimensions['A'].breedte = 50
>>> # Werkmap opslaan
>>> werkboek.opslaan('voorbeeld.xlsx')

De bovenstaande code verandert de hoogte van de eerste rij in 50 pts en de breedte van kolom A in 50 pts.

Samenvoegen en samenvoegen van cellen

Wanneer we met Excel-spreadsheets werken, moeten we cellen vaak samenvoegen en weer samenvoegen. Om cellen in Python samen te voegen, kan een eenvoudige functie op basis van openpyxl worden gebruikt. De openpyxl-module biedt de methode merge_cells() die kan worden gebruikt om cellen in Excel samen te voegen. De nieuwe cel krijgt de naam van de cel linksboven. Als we bijvoorbeeld de cellen van cel A1 naar cel B2 willen samenvoegen, wordt de nieuw gevormde cel A1 genoemd. Om cellen samen te voegen met openpyxl, selecteren we eerst het blad en vervolgens passen we de methode merge_cells() toe op het blad.

>>> # importeren van openpyxl-module
>>> importeer openpyxl
>>> # werkmap laden
>>> werkboek = openpyxl.load_workbook('voorbeeld.xlsx')
>>> # eerste blad selecteren uit Excel-werkmap
>>> blad_1 = werkboek['Eerste blad']
>>> # cellen samenvoegen van A1 naar B2 in blad 1
>>> blad_1.merge_cells('A1:B2')
>>> # werkmap opslaan
>>> werkboek.opslaan('voorbeeld.xlsx')

Op dezelfde manier kan de methode unmerge_cells() worden gebruikt om de samenvoeging van cellen in een Excel-spreadsheet ongedaan te maken. De volgende code kan worden gebruikt om cellen samen te voegen:

>>> # blad selecteren uit werkmap
>>> blad_1 = werkboek['Eerste blad']
>>> # samenvoeging van cellen van A1 naar B2
>>> blad_1.unmerge_cells('A1:B2')
>>> # werkmap opslaan
>>> werkboek.opslaan('voorbeeld.xlsx')

Conclusie

Excel-spreadsheets worden vaak gebruikt voor gegevensmanipulatie. Dergelijke taken kunnen echter eentonig zijn. Daarom kan in dergelijke gevallen programmeren worden gebruikt om manipulatie van spreadsheets te automatiseren.

In dit artikel hebben we enkele van de handige functies van de openpyxl-module van Python besproken. We hebben u laten zien hoe u Excel-spreadsheets kunt maken, lezen, verwijderen en wijzigen, hoe u de stijl kunt wijzigen, lettertype, randen en afmetingen van cellen kunt toepassen en hoe u cellen kunt samenvoegen en weer samenvoegen. Door deze functies toe te passen, kunt u veel taken voor het manipuleren van spreadsheets automatiseren met Python.