Hands-on Python Openpyxl-zelfstudie met voorbeelden

hands python openpyxl tutorial with examples

Volledige gids voor Python-bibliotheek Openpyxl omvat installatie, het maken van een nieuw werkblad, lezen / schrijven / verwijderen van gegevens uit de werkmap:

Excel is de meest populaire spreadsheet-applicatie die wordt gebruikt om de informatie beter te ordenen, berekeningen uit te voeren, uitvoergrafieken te produceren en nog veel meer. In de testwereld is het verifiëren van de applicatie met verschillende datasets cruciaal om een ​​hoogwaardig product en een goede testdekking te krijgen.

Openpyxl is zo'n bibliotheek waarmee we Excel kunnen gebruiken om gegevensgestuurde tests uit te voeren en zelfs om testresultatenrapporten op te stellen, enz.

In deze tutorial zullen we leren wat Openpyxl is, de installatie ervan, het maken van een nieuwe werkmap, het lezen van gegevens uit de spreadsheet en ook het schrijven van gegevens erin. We zullen ook leren hoe u hetzelfde van een webpagina kunt uploaden en downloaden.

Wat je leert:

• Wat is Openpyxl
  • Hoe Openpyxl te installeren
  • Configuratie van PyCharm IDE
  • Maak een nieuwe Excel-werkmap
  • Schrijf gegevens naar het Excel-bestand
  • Hernoem het blad in Excel-bestand
  • Voeg bladen toe aan het Excel-bestand
  • Meerdere waarden aan blad toevoegen
  • Verwijder een blad uit Excel-werkmap
  • Gegevens uit het Excel-bestand lezen
  • Alle waarden in het bestand lezen
  • Kolombrief ophalen
• Datagestuurd testen
  • Excel-bestand maken met invoergegevens
  • Testscript schrijven
  • Upload bestand
  • Download bestand
  • Veel Gestelde Vragen
• Gevolgtrekking
  • Aanbevolen literatuur

Wat is Openpyxl

Openpyxl is een Python-bibliotheek die wordt gebruikt om Excel-bestanden te lezen en te schrijven (xlsx / xlsm / xltx / xltm-bestanden). Met deze module kunnen de Python-programma's de spreadsheet lezen en wijzigen.

XLSX bestand is het standaard bestandsformaat voor Microsoft Excel. Het is gebaseerd op de Office Open XML-standaard.

XLSM bestand is een macro-enabled spreadsheet-bestand.

XLTX -bestand is een Excel-sjabloonbestand waarin door de gebruiker gedefinieerde instellingen behouden blijven.

XLTM -bestand is een sjabloonbestand met ingeschakelde macro's dat is gemaakt met Microsoft Excel.

Hoe Openpyxl te installeren

Wanneer u Python installeert, worden Openpyxl-bibliotheken niet standaard geïnstalleerd. We moeten een commando uitvoeren om de bibliotheken in onze Python te krijgen. Hiervoor moet u de opdrachtprompt openen en de map wijzigen in de map waarin uw Python is geplaatst en de onderstaande opdracht uitvoeren.

Nadat u de opdracht heeft uitgevoerd, worden de bibliotheken gedownload en geïnstalleerd. Dit kan worden geverifieerd met pip lijst commando zoals getoond in de afbeelding hieronder.

Configuratie van PyCharm IDE

Er zijn 2 manieren om Openpyxl-bibliotheken te configureren voor een project in PyCharm.

# 1) Beschikbare pakketten gebruiken in PyCharm

Klik op Bestand-> Nieuw project. Klik op Aanmaken.

Uw project wordt met succes gemaakt. Ga naar om te controleren of de bibliotheken zijn geconfigureerd Bestand -> Instellingen ​Ga op de instellingenpagina naar Project -> Project Interpreter ​

Onder Pakketten ziet u dan Openpyxl-pakket. Als dat ontbreekt, druk dan op de ' ​ ”Knop in de rechterhoek. Zoek onder Beschikbare pakketten naar Openpyxl en klik op Installatiepakket, zoals weergegeven in de onderstaande afbeelding. Nadat de installatie is voltooid, wordt het 'pakket‘ openpyxl ’met succes geïnstalleerd'

Controleer of het pakket met succes is geïnstalleerd.

# 2) De optie Overnemen van globale sitepakketten gebruiken

Deze methode is vrij eenvoudig. Ga naar Bestand-> Nieuw project ​Selecteer tijdens het maken van een nieuw project ' Overnemen van globale sitepakketten ”Selectievakje. Nadat het project is gemaakt, navigeert u naar Bestand -> Instellingen-> Project -> Projectinterpreter ​ u zult zien dat het Openpyxl-pakket al is geïnstalleerd.

Maak een nieuwe Excel-werkmap

We beginnen met het maken van een nieuwe werkmap. Een Excel-bestand wordt een werkmap genoemd en bevat minimaal één blad. Om een ​​werkmap te maken, moeten we eerst de werkmap importeren uit de Openpyxl-bibliotheek met behulp van de onderstaande opdracht:

Laten we een werkmapobject maken. We kunnen dit object gebruiken voor het afhandelen van alle acties die moeten worden uitgevoerd, zoals lezen, schrijven, enz.

Stel het bestandspad in waar u de werkmap wilt maken, zoals hieronder wordt weergegeven:

Gebruik om de werkmap op te slaan

Hiermee wordt een nieuw leeg Excel-bestand gemaakt in het opgegeven pad.

Als je een nieuw Excel-bestand wilt maken op dezelfde locatie als de Python-scripts, kun je het bestandspad overslaan en de bestandsnaam direct vermelden in wb. opslaan commando zoals hieronder getoond:

Schrijf gegevens naar het Excel-bestand

Nu we weten hoe we een nieuw bestand moeten maken, gaan we door en vullen we gegevens in voor het bestand. Om dit te bereiken, moeten we de werkmap laden die we hierboven hebben gemaakt. Dit kan gedaan worden met de onderstaande opdracht:

We moeten aangeven in welk blad we de gegevens gaan invoeren. Aangezien we slechts één blad hebben, kunnen we gewoon het trefwoord 'actief' gebruiken en dit zal het huidige actieve blad gebruiken om de gegevens in te voeren.

Als u meerdere bladen heeft, moet u de naam van het werkblad vermelden, zoals hieronder aangegeven. Waar SheetName de naam is van het blad in de werkmap.

Er zijn 2 manieren om de gegevens in het Excel-bestand in te voeren. Dit zijn de volgende:

• Gebruik de columnrow-combinatie direct. Voorbeeld (A1), waarbij A de kolom is en 1 de rij.
• Gebruik de rij- en kolomnummers. Voorbeeld rij = 4, kolom = 2

Zorg ervoor dat u het bestand opslaat nadat u de waarden hebt ingevoerd.

Volledige code:

De bovenstaande code schrijft 'Software Testing Help', 10 en 'Openpyxl Tutorial', 13.4 in 1^sten 2^ndrij respectievelijk zoals hieronder getoond.

Hernoem het blad in Excel-bestand

In het geval dat u het rapport datumgewijs bijhoudt en u de bladnaam wilt wijzigen in de huidige datum. Het kan worden gedaan door middel van het 'titel' commando.

Voor dit scenario hebben we de huidige datum nodig. Dus eerst moeten we datetime-pakket importeren.

nu() geeft ons de huidige datum en tijd .strftime () methode retourneert een tekenreeks die datum en tijd vertegenwoordigt.

U kunt de naam van het blad wijzigen met sheet.title methode. Vergeet niet het bestand op te slaan.

Volledige code:

Omdat we een actief blad hebben gebruikt, is de naam van het huidige actieve blad gewijzigd.

Voeg bladen toe aan het Excel-bestand

Gezien het bovenstaande voorbeeld om het rapport van elke dag bij te houden, moeten we voor elke dag een nieuw blad maken. Om nieuwe bladen aan Excel toe te voegen, gebruikt u de create_sheet opdracht.

Syntaxis:

Maak een werkblad (bij een optionele index).

Parameters:

• titel (str): optionele titel van het blad.
• index (int): optionele positie waarop het blad wordt ingevoegd.

Notitie: Als er geen parameters zijn ingevoerd, maakt het het blad na het laatste blad en noemt het 'blad'.

Volledige code:

Resultaat:

Meerdere waarden aan blad toevoegen

We kunnen een groep waarden toevoegen onder aan het huidige blad. Dit kan worden bereikt door 'Toevoegen' opdracht.

Syntaxis:

Waar iterabel een lijst met waarden of een enkele waarde is.

hoe swf-bestanden te openen met Adobe Flash Player

• Als het een lijst is, alle waarden worden in volgorde opgeteld, beginnend bij de eerste kolom.
• Als het één waarde is, vervolgens worden de waarden toegewezen aan de kolommen die worden aangegeven door de toetsen (cijfers of letters).

Raadpleeg de onderstaande code:

Resultaat:

Notitie: U kunt de bladnamen afdrukken met print ('actieve bladtitel:' + blad.titel)

Verwijder een blad uit Excel-werkmap

Als u in Openpyxl een blad verwijdert, moet u een beetje voorzichtig zijn, want het kan niet worden hersteld. Laten we een Excel-blad maken met 3 bladen en dit 'Blad1' 'Blad2' 'Blad3' noemen en het opslaan als DeleteSheet.xlsx ​

Het is altijd beter om de bladen te kennen die al aanwezig zijn voordat u ze verwijdert. Je kunt het erdoor krijgen bladnamen opdracht.

Dan kunt u gebruiken wb.remove (bladnaam) of del wb (bladnaam)

Waar, wb is de werkmap die u hebt geladen.

Raadpleeg de onderstaande code:

Resultaat:

Gegevens uit het Excel-bestand lezen

Net als bij het schrijven van gegevens naar het bestand, kunnen we de gegevens op 2 manieren uit een Excel-bestand lezen.

Een van beide, direct gebruik columnrow combinatie ​Voorbeeld (A1) of, gebruik de rij- en kolomnummers ​Voorbeeld rij = 4, kolom = 2.

sheet.cell (). waarde commando helpt ons de waarde uit de opgegeven cel op te halen.

Vind de volledige code hieronder:

Resultaat:

Alle waarden in het bestand lezen

Meestal moeten we bij softwaretests de functionaliteit verifiëren met behulp van verschillende sets gegevens. Stel dat alle gegevens in een werkmap worden geplaatst en dat we de functionaliteit moeten testen met alle gegevens. We moeten dus elke rij en kolom doorlopen om de waarden op te halen. Voor een grote set gegevens is de bovenstaande methode niet haalbaar. Om dit te bereiken, moeten we het hele blad herhalen om de waarden op te halen.

We moeten eerst het aantal rijen en het aantal kolommen in het blad ophalen.

• max_row- Dit geeft de maximale rij-index met gegevens (1-gebaseerd)
• max_column - De maximale kolomindex die gegevens bevat (1-gebaseerd)

Notitie: max_row en max_column zijn 1-gebaseerde indexering. Om naar de laatste rij te gaan, moeten we er een optellen bij de telwaarde.

Hieronder staat de volledige code om de waarden te lezen:

Notitie: We hebben end = ’‘ gebruikt om een ​​spatie tussen elke kolom en nieuwe regel ( n) te hebben om de waarden in een nieuwe rij af te drukken.

Resultaat:

Kolombrief ophalen

Stel dat u een grote set gegevens heeft opgeslagen in een werkmap en u wilt weten in welke rij-index en kolom een ​​bepaalde waarde beschikbaar is. U kunt dat vinden met get_column_letter. Dit zet een kolomindex om in een kolomletter. Voorbeeld 3 tot C.

Raadpleeg de onderstaande code:

Resultaat:

Datagestuurd testen

Nu we weten dat Openpyxl ons helpt bij het lezen en schrijven van gegevens uit Excel, zullen we deze concepten gebruiken om een ​​testcase te bouwen om de applicatie te testen met meerdere verschillende waarden die uit de spreadsheet zijn opgehaald. Vandaar het bereiken van datagedreven testen.

>> Aanbevolen literatuur: Hoe datagestuurd testen werkt

Overweeg een scenario waarin we inloggen op Facebook.com, inloggegevens uit de werkmap lezen, de titel van de pagina verifiëren na inloggen en het resultaat terugschrijven naar dezelfde werkmap.

Herhaal het bovenstaande scenario voor verschillende inloggegevens

Excel-bestand maken met invoergegevens

Laten we een bestand maken met inloggegevens, verwachte titel, daadwerkelijke titel en het resultaatveld en opslaan als Invoergegevens.xlsx.

Notitie: Dit is een dummy-bestand dat wordt gebruikt om een ​​voorbeeld te laten zien. Vul het juiste e-mailadres en wachtwoord in terwijl u het script oefent.

Testscript schrijven

Als we naar het bovenstaande invoerbestand kijken, kunnen we zeggen dat we het script 4 keer moeten herhalen voor 4 verschillende inloggegevens. Om redundantie te voorkomen, maken we een aparte klasse met functies voor het lezen en schrijven van het Excel-bestand. We hebben het ExcelUtils genoemd.

Hieronder staat de ExcelUtils-code:

Importeer nu de klasse ExcelUtils.py in uw hoofdklasse en we zullen de lees- en schrijffuncties opnieuw kunnen gebruiken

We lezen de e-mail en het wachtwoord uit het Excel-bestand en loggen in op Facebook. Als het inloggen is gelukt, zal het script het resultaat in het bestand bijwerken als Geslaagd. Als het inloggen niet is gelukt, zal het script de daadwerkelijke titel van de pagina bijwerken en het resultaat markeren als Mislukt.

Notitie: Installeer Selenium-bibliotheken in uw IDE, u kunt de code niet zonder dat uitvoeren. pip Selenium installeren commando wordt gebruikt om Selenium-bibliotheken te installeren.

Hieronder staat de volledige code:

Resultaat:

Upload bestand

Zodra u begint met het automatiseren van de realtime projecten, gaat u vroeg of laat bestandsuploads uitvoeren. Bijvoorbeeld, het resultaatbestand uploaden naar de gedeelde schijf of de toepassing testen door een bestand te uploaden, enz. Het probleem met het uploaden van bestanden is dat zodra u op de uploadknop klikt, het dialoogvenster Bestand selecteren dat wordt geopend, geen eigendom is van de browser, dus je kunt het niet beheersen met selenium.

Om dit te bereiken, moeten we het bestandspad rechtstreeks doorgeven aan het invoerelement.

c # interviewvragen en antwoorden voor ervaren met voorbeelden

We zouden het invoerelement moeten selecteren en vervolgens het pad van het bestand ernaar moeten doorgeven met behulp van de send_keys methode.

(beeld bron ​

In dit voorbeeld nemen we het invoerveld webelement id ('content') en geven we het bestandspad rechtstreeks door met behulp van de send-toetsen.

Hieronder staat de code voor het uploaden van een Excel-bestand.

Resultaat:

Download bestand

Het downloaden van een bestand is vrij eenvoudig in Selenium. Zoek het webelement van het bestand en de downloadknop. Maar meestal worden de bestanden gedownload onder de downloadmap.

Als u de bestanden in een specifieke map wilt downloaden, moet u de Chrome-opties gebruiken en het pad specificeren van de map waar het bestand moet worden gedownload.

Raadpleeg de onderstaande code:

Deze code zal het bestand downloaden in het door u opgegeven pad.

Resultaat:

Veel Gestelde Vragen

Q # 1) Hoe installeer ik Openpyxl?

Antwoord: We moeten een commando uitvoeren om de bibliotheken in onze Python te krijgen. Open de opdrachtprompt en verander de map naar de Python-werkmap en voer de onderstaande opdracht uit.

V # 2) Kan ik Python in Excel gebruiken?

Antwoord: Met de Openpyxl-module kan Python gegevens uit een Excel-bestand lezen en kunnen we er ook gegevens aan schrijven / wijzigen / toevoegen.

Q # 3) Hoe laad ik een werkmap in Openpyxl?

Antwoord:

# Importeer load_workbook
van openpyxl import load_workbook

# Laad de werkmap
wb = load_workbook (pad van de werkmap)

Voorbeeld- wb = load_workbook ('C: \ Users \ demo.xlsx')

V # 4) Werkt Openpyxl met XLS?

Antwoord: Nee, het ondersteunt geen XLS. Als u het .XLS-bestandsformaat probeert te gebruiken, genereert Python een uitzondering.

verhogen InvalidFileException (msg)

openpyxl.utils.exceptions.InvalidFileException: openpyxl ondersteunt het oude .xls bestandsformaat niet, gebruik xlrd om dit bestand te lezen, of converteer het naar het meer recente .xlsx bestandsformaat.

Verder leren = >> Afhandeling van uitzonderingen in Python

V # 5) Hoe vind ik welke versie van Openpyxl ik heb?

Antwoord: Open de opdrachtprompt. Verander de map naar de map waarin je Python is geïnstalleerd en voer vervolgens de opdracht uit pip lijst ​Dit geeft alle pakketten die zijn geïnstalleerd samen met hun versie.

V # 6) Werkt Openpyxl met Excel 2016?

Antwoord: Ja, het ondersteunt xlsx-, xlsm-, xltx- en xltm-bestandsindelingen.

Q # 7) Hoe maak ik een nieuw blad in Openpyxl?

Antwoord: Om nieuwe bladen aan Excel toe te voegen, gebruikt u

Maak een werkblad (bij een optionele index).

Parameters ​

• titel (str) - optionele titel van het blad.
• index (int) - optionele positie waarop het blad wordt ingevoegd.

Notitie: het voert geen If-parameters in, dan maakt het blad na het laatste blad en noemt het als 'blad'

Voorbeeld - wb.create_sheet (index = 3, title = 'Day4')

V # 8) Hoe sluit ik een werkmap in Openpyxl?

Antwoord: Workbook_Object.close ()

Hiermee wordt het werkmapbestand gesloten als het is geopend. Het heeft alleen invloed op de modi alleen-lezen en alleen schrijven. Voorbeeld ​ Wb.close ()

Zodra u de werkmap sluit. U mag het pas weer gebruiken als u de werkmap opnieuw heeft geladen.

V # 9) Hoe op te lossen - geen module met de naam ‘openpyxl’ fout?

Antwoord: ModuleNotFoundError: Er treedt geen module met de naam openpyxl op als die module niet is geïnstalleerd.

Als je zowel Python2.x- als Python3.x-versies op dezelfde machine hebt geïnstalleerd, gebruik dan pip om te installeren in Python2.x en pip3 om te installeren in Python3.x.

Voorbeeld ​

pip installeer openpyxl - Installeer de Openpyxl-module in Python2.x

pip3 installeer openpyxl - Installeer de Openpyxl-module in Python3.x

V # 10) Hoe open ik een Excel-bestand dat al bestaat met Openpyxl?

Antwoord: Als je al een Excel-bestand hebt, gebruik dan de load_workbook functie om te openen.

Als het bestand zich in de huidige werkmap van Python bevindt, schrijf dan de bestandsnaam als argument voor deze functie.

Voorbeeld - van openpyxl import load_workbook
wb = load_workbook ('myexcelfile.xlsx')

Als uw bestand niet in uw python-werkmap staat, vermeld dan het pad van het bestand als parameter om de werkmap te laden.

Voorbeeld - van openpyxl import load_workbook
wb = load_workbook ('C: \ Users \ myexcelfile.xlsx')

Gevolgtrekking

In deze zelfstudie hebben we geleerd over de Openpyxl-bibliotheek, die ons de flexibiliteit geeft om met Excel te communiceren en daarom gegevensgestuurd testen eenvoudig maakt.

Hieronder staan ​​de essentie van deze tutorial:

1. Het is een Python-bibliotheek waarmee we Excel kunnen gebruiken om acties uit te voeren zoals het lezen van gegevens en het schrijven van gegevens.
2. Deze Python-bibliotheek kan met slechts een enkele opdracht worden geïnstalleerd pip installeer openpyxl ​
3. Deze module kan eenvoudig worden geïnstalleerd in PyCharm IDE met behulp van het venster Beschikbare pakketten.
4. We hebben gezien hoe we een werkmap kunnen maken en er gegevens in kunnen schrijven.
5. Het stelt ons ook in staat om de bladnamen te hernoemen, nieuwe bladen te maken en ook de bladen te verwijderen.
6. Met deze bibliotheek kunt u een individuele celwaarde lezen of de hele werkmap lezen.
7. We hebben ook een voorbeeldtestscript gezien dat laat zien hoe een datagedreven test kan worden uitgevoerd.
8. Het uploaden en downloaden van het bestand met selenium wordt ook in deze tutorial beschreven.

Aanbevolen literatuur

• Hoe XML-bestand te openen in Excel, Chrome en MS Word
• Python-zelfstudie voor beginners (hands-on GRATIS Python-training)
• Werken met VBScript Excel-objecten
• 12 beste Python IDE's en code-editors in 2021
• Gegevens lezen of schrijven vanuit Excel-blad in Selenium Web Driver
• Tutorial voor Python-bestandsafhandeling: creëren, openen, lezen, schrijven
• Python Main Function-zelfstudie met praktische voorbeelden
• Python Tuple-zelfstudie met praktische voorbeelden