openpyxl: Automatiseer Excel-taken met Python

openpyxl is een efficiënte Python-bibliotheek waarmee je Excel-bestanden kunt lezen en schrijven in de moderne, op XML gebaseerde indelingen (.xlsx, .xlsm) die in Excel 2007 zijn geïntroduceerd, met volledige ondersteuning voor geavanceerde functies vanaf Excel 2010. Het werd oorspronkelijk ontwikkeld als een Python-port van PHPExcel en wordt nog steeds onderhouden door een actieve community van ontwikkelaars. De bibliotheek blinkt uit in data-automatisering, rapportage en opmaakworkflows, en is vooral handig voor gebruikers die met Excel-bestanden moeten werken maar geen Excel op hun systeem hebben geïnstalleerd.

Veel organisaties gebruiken openpyxl om financiële rapporten te automatiseren, grafieken te genereren uit database-exporten en formules en opmaak toe te passen op marketingrapporten. Deze bibliotheek biedt zeer gedetailleerde controle over Excel-bestanden, volledig via Python-code.

Wat is openpyxl?

openpyxl is specifiek ontworpen om native ondersteuning te bieden voor Excel 2010+-indelingen. De bibliotheek werkt met meerdere bestandsindelingen:

•  .xlsx - Excel-werkmap

•  .xlsm - Excel-werkmap met macro's

•  .xltx - Excel-sjabloon

•  .xltm - Excel-sjabloon met macro's

Een groot voordeel van openpyxl is dat je geen Excel op je computer hoeft te installeren. Dit maakt het ideaal voor serveromgevingen, geautomatiseerde rapportagesystemen en dataverwerkingspijplijnen. Verschillende andere Python-bibliotheken, waaronder pandas, gebruiken openpyxl als hun Excel-interface, wat de betrouwbaarheid en functionaliteit aantoont.

openpyxl installeren en importeren

openpyxl installeren is eenvoudig met pip:

pip install openpyxl

Voor extra veiligheid bij het werken met bestanden uit onbetrouwbare bronnen kun je ook het optionele pakket defusedxml installeren:

pip install defusedxml

Om openpyxl in je Python-scripts te gebruiken, importeer je de benodigde classes:

from openpyxl import Workbook, load_workbook

Belangrijke concepten en terminologie

Om openpyxl effectief te gebruiken, moet je de basisstructuur van Excel begrijpen:

• Workbook: Het Excel-bestand zelf, met een of meer werkbladen
• Worksheet: Individuele tabs/werkbladen binnen een werkmap
• Cell: Afzonderlijk datapunt in een werkblad, geïdentificeerd door kolomletter en rijnummer (bijv. "A1")
• Row: Horizontale rij cellen, geïdentificeerd door nummers (1, 2, 3...)
• Column: Verticale kolom cellen, geïdentificeerd door letters (A, B, C...)

In openpyxl kun je cellen refereren met:

• Excel-stijl referenties: sheet[“A1”]

• Rij-kolom-indexering: sheet.cell(row=1, column=1) (Let op: openpyxl gebruikt 1-based indexering, geen 0-based)

Excel-bestanden lezen met openpyxl

Hier is een praktisch voorbeeld van hoe je data uit een Excel-bestand leest:

Een werkmap laden en verkennen

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

De optionele parameters bepalen hoe de werkmap wordt geladen:

• read_only=True verbetert de prestaties voor grote bestanden maar beperkt de bewerkingsmogelijkheden

• data_only=True geeft berekende waarden terug in plaats van formules

Door data itereren

Gebruik de iteratiemethodes om meerdere cellen efficiënt te verwerken:

# 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)

Converteren naar Python-datastructuren

Je kunt Excel-data eenvoudig omzetten naar Python-datastructuren:

# 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-bestanden schrijven met openpyxl

Excel-bestanden maken en aanpassen is net zo eenvoudig met openpyxl.

Een werkmap maken en basisdata schrijven

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")

Bestaande Excel-bestanden wijzigen

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")

Dynamisch data toevoegen

Met de methode .append() voeg je eenvoudig nieuwe rijen toe:

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")

Opmaak en styling in Excel

openpyxl biedt uitgebreide opties voor celopmaak en styling.

Lettertypen, randen en uitlijning toevoegen

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")

Voorwaardelijke opmaak

Met voorwaardelijke opmaak kun je stijlen toepassen op basis van celwaarden:

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")

Geavanceerde features

openpyxl ondersteunt veel geavanceerde Excel-functies die helpen bij complexe spreadsheettaken.

Formules toevoegen

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")

Opmerking: Bij gebruik van load_workbook() worden formules niet automatisch berekend door openpyxl. Om berekende waarden te zien, gebruik je load_workbook(“file.xlsx”, data_only=True).  

Grafieken invoegen

openpyxl kan verschillende grafiektypen maken:

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")

Afbeeldingen invoegen

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")

Opmerking: Je hebt de Pillow-bibliotheek nodig om met afbeeldingen te werken: pip install pillow

Beheer van bladen, rijen en kolommen

openpyxl biedt functies om de structuur van je Excel-bestanden te beheren:

Bladen maken en verwijderen

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")

Bladen hernoemen

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

Werkbladen kopiëren

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")

Rijen en kolommen invoegen en verwijderen

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")

Vaste rijen/kolommen en filters toevoegen

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")

Beperkingen en feedback uit de community

Hoewel openpyxl rijk is aan features, hebben gebruikers enkele beperkingen genoemd:

• Beperkte ondersteuning voor benoemde bereiken
• Documentatievoorbeelden kunnen schaars zijn voor geavanceerde functies
• Prestaties kunnen traag zijn bij zeer grote bestanden (al helpt read-only-modus)

Ondanks deze beperkingen heeft openpyxl sterke communityondersteuning. Issues kunnen worden gemeld op de GitLab-tracker en professionele ondersteuning is soms beschikbaar. Voor meer informatie en hulp kun je terecht bij:

• openpyxl-documentatie
• Broncode
• Issue-tracker
• Professionele ondersteuning

Conclusie

openpyxl is een gevestigde bibliotheek voor Python-gebaseerde Excel-automatisering die volledige ondersteuning biedt voor het lezen, schrijven en opmaken van Excel-bestanden. Het biedt gedetailleerde controle over spreadsheetoutput in geautomatiseerde workflows, van het manipuleren van afzonderlijke cellen tot het maken van grafieken en het toepassen van complexe opmaak.

Voor gebruikers die programmatisch Excel-bestanden moeten genereren of analyseren, vooral bij het bouwen van rapporten of het verwerken van tabeldata, biedt openpyxl een uitgebreide toolkit. Doordat het werkt zonder dat Excel geïnstalleerd hoeft te zijn, is het bijzonder waardevol voor serveromgevingen en geautomatiseerde datapijplijnen.

Of je nu financiële modellen maakt, data analyseert of rapporten genereert, met openpyxl heb je de tools om Excel-bestanden efficiënt te verwerken via Python-code.