Noch mehr Excel Testdaten!

Im letzten Artikel zu Excel Testdaten haben wir uns eine Excel Datei erzeugt und die einfach mit einem Satz an Daten befüllt.
Das reicht dir nicht?
Hier zeige ich dir, wie du den Code ganz simpel erweitern kannst, um beliebig viele Excel Testdaten Dateien mit beliebig vielen Arbeitsblättern erzeugen zu können.

Bibliotheken

• Faker (hier ein Post dazu)
• Pandas (hier ein Post dazu)
• OpenPyxl (hier ein Post dazu)

Wie kann ich mit Python Testdaten erzeugen?

Faker eine hervorragende Bibliothek um schnell und unkompliziert Testdaten erzeugen zu können.
Du kannst genau angeben, in welcher Sprache und welche Art von Daten du haben möchtest.
Ein Name? Eine vollständige Anschrift oder gar ein ganzes Profil?
Kein Problem, mit Faker hast du die Testdaten in wenigen Schritten erzeugt.

Excel Testdaten erstellen

Das Ziel ist es, mehrere Excel Dateien erstellen zu können, die jeweils mehrere Arbeitsblätter beinhalten, damit wir die dann zusammenführen können.

Also erstellen wir uns doch einfach eine Funktion, die einen Dateinamen und eine Liste mit Namen für Arbeitsblätter entgegennimmt.
Damit kann die Funktion dann eine neue Datei erzeugen, die Arbeitsblätter anlegen und die auch mit Daten befüllen.

Schritt 1: Excel Datei anlegen

def create_workbook(workbook_name, sheet_names):
    workbook = Workbook(write_only=True)

Mit der Workbook() Klasse von OpenPyxl erzeugen wir ein neues Workbook. Also eine neue Excel Datei.
Die Datei wird generell so erstellt, dass sie direkt nutzbar wäre. Das heißt, wir bekommen auch direkt ein leeres Sheet, also ein Arbeitsblatt, mit erzeugt.

Wir wollen aber selbst Arbeitsblätter erstellen, mit Namen versehen und mit Daten befüllen.
Damit wir das Standard-Arbeitsblatt nicht erst löschen oder umbenennen müssen, können wir OpenPyxl bei der Erstellung auch direkt mitteilen, dass kein Standard Arbeitsblatt erzeugt werden soll.
Das geht einfach über das Parameter write_only=True.

OpenPyxl akzeptiert damit, dass die Datei nicht zum lesen geeignet ist und wir uns selbst um die Arbietsblätter kümmern.

Schritt 2: Arbeitsblatt erstellen

Im nächsten Schritt müssen wir uns also genau darum kümmern. Ein Arbeitsblatt erstellen.

Unsere Funktion ist so geplant, dass sie eine Liste mit Namen für Arbeitsblätter entgegennimmt und wir für jeden Namen in dieser Liste dann ein Arbeitsblatt erstellen.
Das wiederum bedeutet, wir brauchen eine Schleife:

def create_workbook(workbook_name, sheet_names):
    workbook = Workbook(write_only=True)
    
    for sheet_name in sheet_names:
        sheet = workbook.create_sheet(sheet_name)

So einfach geht’s!
Mit der Schleife gehen wir durch die Namen durch.
Mit der create_sheet() Funktion an unserem Workbook Objekt können wir ein neues Arbeitsblatt erzeugen.

Hier muss kein Name übergeben werden.
Wenn wir nichts übergeben, wird einfach der Standardname für Arbeitsblätter verwendet und hochgezählt.
Da wir aber einen Namen übergeben, wird das Arbeitsblatt auch direkt mit dem richtigen Namen erzeugt

Schritt 3: Testdaten erstellen

Hier nochmal der Code aus dem letzten Teil zur Testdaten-Erstellung:

def create_testdata():
    fake = Faker('de_DE')
    data_list = []
    for num in range(200):
        data_list.append({
            'Name': fake.last_name(),
            'Vorname': fake.first_name(),
            'Telefon': fake.phone_number(),
            'Strasse': fake.street_address(),
            'Postleitzahl': fake.postcode(),
            'Stadt': fake.city(),
            'Bank': fake.iban(),
            'Eintritt': fake.date_between().strftime('%d.%m.%Y')
        })
    return data_list

Ich habe lediglich die Ausgabe in eine Datei entfernt und den Code in eine Funktion gepackt.

Jetzt können wir die Funktion einfach aufrufen und bekommen 200 Datensätze mit Testdaten erzeugt.
Um die Daten in unser frisch erzeugtes Arbeitsblatt zu bekommen, haben wir mehrere Möglichkeiten.

OpenPyxl braucht die Daten Zeile für Zeile, um sie dem Arbeitsblatt zuzufügen.
Wir könnten die Funktion jetzt so umschreiben, dass die Daten direkt im richtigen Format erzeugt und Zeile für Zeile an das Arbeitsblatt übergeben werden.

Oder wir machen es uns einfach 😉
Mit der Hilfe von Pandas kann aus der Liste von Dictionaries direkt ein Dataframe erzeugt werden.

Ein Dataframe kannst du dir genau wie ein Excel Arbeitsblatt vorstellen.
Es hat Zeilen und Spalten und hat Spaltenbezeichner. Also genau das Format, dass wir am Ende haben wollen.
Und so ein Dataframe lässt sich, wie gesagt, ganze einfach aus unseren Daten erstellen:

def create_workbook(workbook_name, sheet_names):
    workbook = Workbook(write_only=True)
    
    for sheet_name in sheet_names:
        sheet = workbook.create_sheet(sheet_name)
        data = pandas.DataFrame(create_testdata())


def create_testdata():
    fake = Faker('de_DE')
    data_list = []
    for num in range(200):
        data_list.append({
            'Name': fake.last_name(),
            'Vorname': fake.first_name(),
            'Telefon': fake.phone_number(),
            'Strasse': fake.street_address(),
            'Postleitzahl': fake.postcode(),
            'Stadt': fake.city(),
            'Bank': fake.iban(),
            'Eintritt': fake.date_between().strftime('%d.%m.%Y')
        })
    return data_list

Bei der Erzeugung eines DataFrame können wir direkt Daten mit übergeben, mit denen Pandas das DataFrame befüllen soll.
Dabei werden die Schlüssel des Dictionaries als Spaltennamen und die Werte als Spalteninhalt genutzt.

Schritt 4: Arbeitsblatt füllen

OpenPyxl möchte die Daten also Zeile für Zeile.
Dann müssen wir jetzt nur noch Zeile für Zeile durch das DataFrame durchgehen und die Daten von dem DataFrame in das Excel Arbeitsblatt übertragen.

def create_workbook(workbook_name, sheet_names):
    workbook = Workbook(write_only=True)
    
    for sheet_name in sheet_names:
        sheet = workbook.create_sheet(sheet_name)
        data = pandas.DataFrame(create_testdata())

        for row in dataframe_to_rows(data, index=False):
            sheet.append(row)


def create_testdata():
    fake = Faker('de_DE')
    data_list = []
    for num in range(200):
        data_list.append({
            'Name': fake.last_name(),
            'Vorname': fake.first_name(),
            'Telefon': fake.phone_number(),
            'Strasse': fake.street_address(),
            'Postleitzahl': fake.postcode(),
            'Stadt': fake.city(),
            'Bank': fake.iban(),
            'Eintritt': fake.date_between().strftime('%d.%m.%Y')
        })
    return data_list

Mit dataframe_to_rows() liefert uns OpenPyxl eine Hilfsfunktion. Damit müssen wir nicht selbst durch die Daten iterieren, sondern bekommen von OpenPyxl die Zeilen schon geliefert.
Es müssen lediglich die Daten übergeben werden.

Pandas erzeugt an einem DataFrame immer einen Index.
Das wäre die erste Spalte, die einfach von 0 beginnend die Zeilen durchzählt.
Die wollen wir nicht in unserer Excel Datei. Deswegen sagen wir mit dem Parameter index=False einfach, dass der Index nicht mit übernommen werden soll.

Anschließend wird ein Arbeitsblatt wie eine Liste behandelt. Über sheet.append() kann die nächste Zeile zugefügt werden.

Schritt 5: Dateien erzeugen

Als letzter Schritt fehlt noch das Speichern der Datei.
Mit workbook.save() kann ein Dateiname oder ein ganzer Pfad an OpenPyxl übergeben werden.
Unter dem Pfad finden wir dann unsere Excel Datei wieder.

def create_workbook(workbook_name, sheet_names):
    workbook = Workbook(write_only=True)
    
    for sheet_name in sheet_names:
        sheet = workbook.create_sheet(sheet_name)
        data = pandas.DataFrame(create_testdata())

        for row in dataframe_to_rows(data, index=False):
            sheet.append(row)

    workbook.save(workbook_name)


def create_testdata():
    fake = Faker('de_DE')
    data_list = []
    for num in range(200):
        data_list.append({
            'Name': fake.last_name(),
            'Vorname': fake.first_name(),
            'Telefon': fake.phone_number(),
            'Strasse': fake.street_address(),
            'Postleitzahl': fake.postcode(),
            'Stadt': fake.city(),
            'Bank': fake.iban(),
            'Eintritt': fake.date_between().strftime('%d.%m.%Y')
        })
    return data_list

Schritt 6: Mehrere Dateien erzeugen

Abschließend müssen wir unser Skript nur noch aufrufen.
Der Aufruf geht über einen Dateinamen und eine Liste mit Arbeitsblättern, die wir in der Excel Datei vorfinden möchten.

if __name__ == '__main__':
    create_workbook('daten/Adressen 1.xlsx', ['A', 'B', 'C'])
    create_workbook('daten/Adressen 2.xlsx', ['D'])
    create_workbook('daten/Adressen 3.xlsx', ['E', 'F', 'G'])

Wir müssen also nur für jede Excel Datei, die wir erzeugen möchten, einmal die Funktion aufrufen.
Dabei übergeben wir einen Pfad – in meinem Fall ‘daten/Adressen 1.xlsx’ und eine Liste mit Arbeitsblättern [‘A’, ‘B’, ‘C’].

Die Funktion kannst du so oft du willst aufrufen und so viele Arbeitsblätter wie du möchtest da drin definieren.
Und schon bekommst du massenhaft Testdaten.

Wenn du nicht weißt, was if __name__ == ‘__main__’ bedeutet, findest du hier ein Video von mir dazu.

Zusammenfassung

• Schritt 1: Excel Datei anlegen
  • Mit der Workbook() Klasse aus OpenPyxl erstellst du eine Excel Datei.
• Schritt 2: Arbeitsblatt erstellen
  • Die create_sheet() Funktion am Workbook erzeugt ein neues Arbeitsblatt.
• Schritt 3: Testdaten erstellen
  • Mit dem Code aus dem letzten Artikel zusammen mit Pandas erzeugst du schnell einen Haufen Testdaten.
• Schritt 4: Arbeitsblatt füllen
  • Die Funktion dataframe_to_rows() aus OpenPyxl hilft dabei die Pandas Daten wieder in Zeilen umzuwandeln.
• Schritt 5: Dateien erzeugen
  • Das Workbook kann über save() direkt an einem beliebigen Ort abgespeichert werden.
• Schritt 6: Mehrere Dateien erzeugen
  • Mehrere Aufrufe mit verschiedenen Parametern erzeugen einfach und schnell viele Testdaten.