openpyxl: Excel-Aufgaben mit Python automatisieren

openpyxl ist eine effiziente Python-Bibliothek, mit der du Excel-Dateien in den modernen, XML-basierten Formaten (.xlsx, .xlsm) lesen und schreiben kannst, die mit Excel 2007 eingeführt wurden, und die ab Excel 2010 alle erweiterten Funktionen unterstützt. Es wurde ursprünglich als Python-Portierung von PHPExcel entwickelt und wird weiterhin von einer aktiven Entwicklergemeinschaft gepflegt. Die Bibliothek eignet sich hervorragend für die Automatisierung von Daten, die Erstellung von Berichten und die Formatierung von Workflows und ist damit besonders nützlich für Nutzer, die mit Excel-Dateien arbeiten müssen, aber kein Excel auf ihrem System installiert haben.

Viele Unternehmen nutzen openpyxl, um Finanzberichte zu automatisieren, Diagramme aus Datenbankexporten zu erstellen und Formeln und Formatierungen auf Marketingberichte anzuwenden. Diese Bibliothek bietet eine fein abgestufte Kontrolle über Excel-Dateien, die vollständig durch Python-Code gesteuert wird.

Was ist openpyxl?

openpyxl wurde speziell für die native Unterstützung von Excel 2010+ Formaten entwickelt. Die Bibliothek arbeitet mit verschiedenen Dateiformaten:

•  .xlsx - Excel-Arbeitsmappe

•  .xlsm - Excel-Arbeitsmappe mit Makros

•  .xltx - Excel-Vorlage

•  .xltm - Excel-Vorlage mit Makros

Ein großer Vorteil von openpyxl ist, dass Excel nicht auf deinem Computer installiert sein muss. Das macht sie ideal für Serverumgebungen, automatisierte Berichtssysteme und Datenverarbeitungspipelines. Mehrere andere Python-Bibliotheken, darunter pandas, verwenden openpyxl als Excel-Schnittstelle, was ihre Zuverlässigkeit und Funktionalität unter Beweis stellt.

Installieren und Importieren von openpyxl

Die Installation von openpyxl ist mit pip ganz einfach:

pip install openpyxl

Um die Sicherheit bei der Arbeit mit Dateien aus nicht vertrauenswürdigen Quellen zu erhöhen, kannst du auch das optionale Paket defusedxml installieren:

pip install defusedxml

Um openpyxl in deinen Python-Skripten zu verwenden, importiere die notwendigen Klassen:

from openpyxl import Workbook, load_workbook

Schlüsselbegriffe und Terminologie

Um openpyxl effektiv zu nutzen, musst du die grundlegende Struktur von Excel verstehen:

• Workbook: Die Excel-Datei selbst, mit einem oder mehreren Arbeitsblättern
• Arbeitsblatt: Einzelne Registerkarten/Blätter innerhalb einer Arbeitsmappe
• Handy: Einzelne Datenpunkte in einem Arbeitsblatt, die durch den Spaltenbuchstaben und die Zeilennummer gekennzeichnet sind (z. B. "A1")
• Reihe: Horizontale Reihe von Zellen, gekennzeichnet durch Zahlen (1, 2, 3...)
• Kolumne: Vertikale Reihe von Zellen, gekennzeichnet durch Buchstaben (A, B, C...)

In openpyxl kannst du Zellen entweder mit

• Excel-Stilreferenzen: sheet[“A1”]

• Zeilen-Spalten-Indizierung: sheet.cell(row=1, column=1) (Hinweis: openpyxl verwendet eine 1-basierte Indizierung, keine 0-basierte)

Excel-Dateien mit openpyxl lesen

Hier ist ein praktisches Beispiel, das zeigt, wie man Daten aus einer Excel-Datei liest:

Eine Arbeitsmappe laden und erkunden

from openpyxl import load_workbook

# Load the workbook - use read_only=True for large files
wb = load_workbook('sample.xlsx', read_only=False, data_only=False)
# data_only=True reads values instead of formulas

Die optionalen Parameter steuern, wie die Arbeitsmappe geladen wird:

• read_only=True verbessert die Leistung bei großen Dateien, schränkt aber die Bearbeitungsmöglichkeiten ein

• data_only=True gibt berechnete Werte anstelle von Formeln zurück

Iterieren durch Daten

Um mehrere Zellen effizient zu bearbeiten, verwende die Iterationsmethoden:

# Iterate through rows
for row in sheet.iter_rows(min_row=1, max_row=5, values_only=True):
    print(row)  # Returns a tuple of values

# Iterate through columns
for column in sheet.iter_cols(min_col=1, max_col=3, values_only=True):
    print(column)  # Returns a tuple of values

# Access a range of cells
cell_range = sheet["A1:C5"]
for row in cell_range:
    for cell in row:
        print(cell.value)

In Python-Datenstrukturen umwandeln

Du kannst Excel-Daten ganz einfach in Python-Datenstrukturen umwandeln:

# Convert worksheet data to a list of dictionaries
def sheet_to_dict(sheet):
    data = []
    headers = [cell.value for cell in sheet[1]]  # First row as headers
    
    for row in sheet.iter_rows(min_row=2, values_only=True):
        row_data = {}
        for key, value in zip(headers, row):
            row_data[key] = value
        data.append(row_data)
    
    return data

# Example usage
data_dict = sheet_to_dict(sheet)
print(data_dict)

Excel-Dateien mit openpyxl schreiben

Auch das Erstellen und Ändern von Excel-Dateien ist mit openpyxl ganz einfach.

Eine Arbeitsmappe erstellen und grundlegende Daten schreiben

from openpyxl import Workbook

# Create a new workbook
wb = Workbook()
sheet = wb.active

# Rename the sheet
sheet.title = "Data"

# Write values to cells
sheet["A1"] = "Name"
sheet["B1"] = "Age"
sheet["C1"] = "City"

# Add data
data = [
    ["Alice", 25, "New York"],
    ["Bob", 30, "San Francisco"],
    ["Charlie", 35, "Chicago"]
]

for row_idx, row_data in enumerate(data, start=2):
    for col_idx, cell_value in enumerate(row_data, start=1):
        sheet.cell(row=row_idx, column=col_idx, value=cell_value)

# Save the workbook
wb.save("new_workbook.xlsx")

Ändern bestehender Excel-Dateien

from openpyxl import load_workbook

# Load an existing workbook
wb = load_workbook("existing_file.xlsx")
sheet = wb.active

# Modify cell values
sheet["D1"] = "Updated Data"
sheet.cell(row=5, column=2).value = 42

# Save to a new file (to preserve the original)
wb.save("modified_file.xlsx")

Daten dynamisch anhängen

Die Methode .append() macht es einfach, neue Zeilen hinzuzufügen:

from openpyxl import Workbook

wb = Workbook()
sheet = wb.active

# Add headers
sheet.append(["Date", "Product", "Quantity", "Price"])

# Add multiple rows of data
sales_data = [
    ["2025-04-01", "Laptop", 5, 1200],
    ["2025-04-01", "Mouse", 10, 25],
    ["2025-04-02", "Monitor", 3, 350]
]

for row in sales_data:
    sheet.append(row)

wb.save("sales_report.xlsx")

Formatierung und Stilisierung in Excel

openpyxl bietet umfangreiche Optionen für die Zellformatierung und das Styling.

Schriftarten, Rahmen und Ausrichtung hinzufügen

from openpyxl import Workbook
from openpyxl.styles import Font, Alignment, Border, Side, PatternFill, NamedStyle

wb = Workbook()
sheet = wb.active

# Create a header style
header_style = NamedStyle(name="header_style")
header_style.font = Font(bold=True, size=12, color="FFFFFF")
header_style.fill = PatternFill(fill_type="solid", start_color="366092")
header_style.alignment = Alignment(horizontal="center", vertical="center")
header_style.border = Border(
    bottom=Side(border_style="medium", color="000000"),
    left=Side(border_style="thin"),
    right=Side(border_style="thin"),
    top=Side(border_style="thin")
)

# Add the style to the workbook
wb.add_named_style(header_style)

# Apply the style to header row
headers = ["ID", "Product", "Category", "Price"]
for col_idx, header in enumerate(headers, start=1):
    cell = sheet.cell(row=1, column=col_idx, value=header)
    cell.style = "header_style"

# Basic styling without named styles
sheet["A2"].font = Font(bold=True, italic=True)
sheet["A3"].alignment = Alignment(horizontal="center")
sheet["A4"].border = Border(bottom=Side(border_style="thin"))

wb.save("styled_workbook.xlsx")

Bedingte Formatierung

Mit der bedingten Formatierung kannst du Stile auf der Grundlage von Zellwerten anwenden:

from openpyxl import Workbook
from openpyxl.styles import Font, PatternFill
from openpyxl.styles.differential import DifferentialStyle
from openpyxl.formatting.rule import Rule, ColorScaleRule

wb = Workbook()
sheet = wb.active

# Add sample data
for row in range(1, 11):
    for col in range(1, 5):
        sheet.cell(row=row, column=col, value=(row-1)*4 + col)

# Add color scale (green to red gradient)
color_scale = ColorScaleRule(
    start_type="min", start_color="63BE7B",  # Green
    mid_type="percentile", mid_value=50, mid_color="FFEB84",  # Yellow
    end_type="max", end_color="F8696B"  # Red
)
sheet.conditional_formatting.add("A1:D10", color_scale)

# Add a rule for values less than 5
red_text = Font(color="FF0000")
red_fill = PatternFill(start_color="FFCCCC", end_color="FFCCCC", fill_type="solid")
dxf = DifferentialStyle(font=red_text, fill=red_fill)
rule = Rule(type="cellIs", operator="lessThan", formula=["5"], dxf=dxf)
sheet.conditional_formatting.add("A1:D10", rule)

wb.save("conditional_format.xlsx")

Erweiterte Funktionen

openpyxl unterstützt viele erweiterte Excel-Funktionen, die bei komplexen Tabellenkalkulationsaufgaben helfen.

Hinzufügen von Formeln

from openpyxl import Workbook

wb = Workbook()
sheet = wb.active

# Add data
for row in range(1, 6):
    sheet.cell(row=row, column=1, value=row)

# Add formulas
sheet["B1"] = "=A1*2"
sheet["B2"] = "=A2*2"
sheet["C1"] = "=SUM(A1:A5)"
sheet["C2"] = "=AVERAGE(A1:A5)"
sheet["C3"] = "=COUNTIF(A1:A5,\">2\")"

wb.save("formulas.xlsx")

Hinweis: Wenn du load_workbook() verwendest, werden die Formeln nicht automatisch von openpyxl berechnet. Um berechnete Werte zu sehen, benutze load_workbook(“file.xlsx”, data_only=True).  

Diagramme einfügen

openpyxl kann verschiedene Diagrammtypen erstellen:

from openpyxl import Workbook
from openpyxl.chart import BarChart, LineChart, Reference

wb = Workbook()
sheet = wb.active

# Add data
months = ["Jan", "Feb", "Mar", "Apr", "May"]
values = [30, 45, 37, 50, 62]

for i, (month, value) in enumerate(zip(months, values), start=1):
    sheet.cell(row=i, column=1, value=month)
    sheet.cell(row=i, column=2, value=value)

# Create a bar chart
bar_chart = BarChart()
bar_chart.title = "Monthly Sales"
bar_chart.x_axis.title = "Month"
bar_chart.y_axis.title = "Sales"

# Define the data range
data = Reference(sheet, min_col=2, min_row=1, max_row=5)
categories = Reference(sheet, min_col=1, min_row=1, max_row=5)

# Add the data to the chart
bar_chart.add_data(data)
bar_chart.set_categories(categories)

# Add the chart to the worksheet
sheet.add_chart(bar_chart, "D1")

# Create a line chart on the same data
line_chart = LineChart()
line_chart.title = "Monthly Sales Trend"
line_chart.add_data(data)
line_chart.set_categories(categories)
sheet.add_chart(line_chart, "D15")

wb.save("charts.xlsx")

Bilder einfügen

from openpyxl import Workbook
from openpyxl.drawing.image import Image

wb = Workbook()
sheet = wb.active

# Add an image
img = Image("logo.png")

# Resize the image (optional)
img.width = 150
img.height = 75

# Add the image to cell A1
sheet.add_image(img, "A1")

wb.save("with_image.xlsx")

Hinweis: Du musst die Pillow-Bibliothek installieren, um mit Bildern arbeiten zu können: pip install pillow

Blatt-, Zeilen- und Spaltenverwaltung

openpyxl bietet Funktionen zur Verwaltung der Struktur deiner Excel-Dateien:

Blätter erstellen und entfernen

from openpyxl import Workbook

wb = Workbook()

# Create new sheets
wb.create_sheet("Data")
wb.create_sheet("Summary", 0)  # Add at the beginning

# Remove a sheet
wb.remove(wb["Sheet"])  # Remove the default sheet

print(wb.sheetnames)  # ['Summary', 'Data']

wb.save("multiple_sheets.xlsx")

Blätter umbenennen

wb = Workbook()
sheet = wb.active
sheet.title = "Sales Report"
wb.save("renamed_sheet.xlsx")

Kopieren von Arbeitsblättern

from openpyxl import Workbook

wb = Workbook()
source = wb.active
source.title = "Original"

# Add some data to copy
source["A1"] = "Test Data"

# Create a copy
wb.copy_worksheet(source)

# The copied sheet will have "Copy of Original" name
wb.save("copied_sheet.xlsx")

Einfügen und Löschen von Zeilen und Spalten

from openpyxl import Workbook

wb = Workbook()
sheet = wb.active

# Add some data
for i in range(1, 6):
    for j in range(1, 4):
        sheet.cell(row=i, column=j, value=f"R{i}C{j}")

# Insert a row at position 2
sheet.insert_rows(2)

# Insert multiple rows
sheet.insert_rows(5, 3)  # Insert 3 rows at position 5

# Insert a column at position 2
sheet.insert_cols(2)

# Delete rows
sheet.delete_rows(7, 2)  # Delete 2 rows starting at row 7

# Delete columns
sheet.delete_cols(3)  # Delete column C

wb.save("modified_structure.xlsx")

Scheiben einfrieren und Filter hinzufügen

from openpyxl import Workbook

wb = Workbook()
sheet = wb.active

# Add headers
headers = ["ID", "Name", "Department", "Salary"]
for col_idx, header in enumerate(headers, start=1):
    sheet.cell(row=1, column=col_idx, value=header)

# Add data
data = [
    [1, "John Smith", "HR", 55000],
    [2, "Jane Doe", "IT", 65000],
    [3, "Bob Johnson", "Finance", 60000]
]
for row_idx, row_data in enumerate(data, start=2):
    for col_idx, value in enumerate(row_data, start=1):
        sheet.cell(row=row_idx, column=col_idx, value=value)

# Freeze the header row
sheet.freeze_panes = "A2"

# Add autofilter
sheet.auto_filter.ref = "A1:D4"

wb.save("freeze_and_filter.xlsx")

Einschränkungen und Feedback der Gemeinschaft

Obwohl openpyxl viele Funktionen hat, haben Benutzer einige Einschränkungen festgestellt:

• Eingeschränkte Unterstützung für benannte Bereiche
• Beispiele in der Dokumentation können für fortgeschrittene Funktionen spärlich sein
• Die Leistung kann bei sehr großen Dateien langsam sein (obwohl der Nur-Lese-Modus hilft)

Trotz dieser Einschränkungen hat openpyxl eine starke Unterstützung durch die Community. Probleme können im GitLab-Tracker gemeldet werden, und manchmal gibt es auch professionelle Unterstützung. Weitere Informationen und Hilfe erhältst du unter:

• openpyxl Dokumentation
• Quellcode
• Issues Tracker
• Professionelle Unterstützung

Fazit

openpyxl ist eine etablierte Bibliothek für Python-basierte Excel-Automatisierung, die vollständige Unterstützung für das Lesen, Schreiben und Gestalten von Excel-Dateien bietet. Sie ermöglicht eine detaillierte Kontrolle über die Tabellenkalkulation in automatisierten Arbeitsabläufen, von der Bearbeitung einzelner Zellen bis zur Erstellung von Diagrammen und der Anwendung komplexer Formatierungen.

Für Benutzer, die Excel-Dateien programmatisch erstellen oder analysieren müssen, insbesondere bei der Erstellung von Berichten oder der Verarbeitung von Tabellendaten, bietet openpyxl ein umfangreiches Toolkit. Seine Fähigkeit, ohne Excel-Installation zu arbeiten, macht es besonders wertvoll für Serverumgebungen und automatisierte Datenpipelines.

Egal, ob du Finanzmodelle erstellst, Daten analysierst oder Berichte generierst, openpyxl gibt dir die Werkzeuge an die Hand, um Excel-Dateien effizient mit Python-Code zu bearbeiten.