Excel-sheets lezen en verwerken met pandas en openpyxl: van begin tot eind

Als je in data-analyse werkt of repetitieve spreadsheettaken wilt automatiseren, is de combinatie van Python en Excel een winnende strategie om je workflow te versnellen . Excel blijft in veel bedrijven de dominante tool en het is cruciaal om te leren hoe je kunt voorkomen dat het getallen omzet in datums . Python biedt daarentegen kracht, flexibiliteit en een robuust ecosysteem van bibliotheken die speciaal voor data zijn ontworpen. In deze handleiding leer je in detail hoe je Excel-spreadsheets kunt lezen en verwerken met pandas en openpyxl , wanneer je welke tool het beste kunt gebruiken en hoe je er in de praktijk optimaal gebruik van kunt maken.

Hier leer je meer dan alleen een bestand openen en een paar cellen bekijken. Je leert hoe je specifieke werkbladen en bereiken laadt, filtert, transformeert en resultaten opslaat , cellen opmaakt met geavanceerde stijlen, nieuwe werkmappen en werkbladen maakt, geautomatiseerde rapporten genereert en zelfs grafieken of kleine dashboards creëert. We beginnen bij de basis en gaan direct aan de slag met praktische voorbeelden, inclusief direct aanpasbare code, prestatie-aanbevelingen en best practices om knelpunten en veelvoorkomende fouten te voorkomen.

Het voorbereiden van de omgeving en de benodigde bibliotheken

Voordat je begint, zorg ervoor dat je een recente versie van Python hebt geïnstalleerd; Python 3.7 of hoger wordt aanbevolen om compatibiliteit met de bibliotheken die we gaan gebruiken te garanderen. Om je versie te controleren, kun je de volgende opdracht in de terminal uitvoeren.

python --version

Om met Python in Excel te werken, heb je vooral de bibliotheken pandas en openpyxl nodig ; elk is geschikt voor verschillende doeleinden. Je kunt ze snel installeren met pip en meteen aan de slag gaan.

pip install pandas openpyxl

Als je liever afhankelijkheden beheert met een manager zoals Poetry, kun je beide pakketten ook installeren met eenvoudige commando's , bijvoorbeeld `poetry add pandas` en `poetry add openpyxl` . Dit helpt je om per project een reproduceerbare omgeving te behouden zonder gedoe.

Boeken, vellen en cellen lezen met openpyxl

De openpyxl- bibliotheek werkt rechtstreeks met .xlsx-bestanden, waardoor u werkmappen kunt openen, werkbladen kunt bewerken en cellen nauwkeurig kunt lezen en schrijven. Het is ideaal wanneer u gedetailleerde controle nodig hebt over opmaak , het toepassen van stijlen en formules, en het werken met de structuur van Excel.

from openpyxl import load_workbook

# Cargar un archivo Excel
workbook = load_workbook("example.xlsx")

# Ver nombres de hojas disponibles
print(workbook.sheetnames)

Zodra de werkmap is geopend, kunt u een werkblad selecteren op naam en specifieke waarden bekijken. Deze methode is handig wanneer u afzonderlijke cellen wilt inspecteren of door bereiken wilt bladeren zonder alles naar een tabelstructuur om te zetten.

# Seleccionar una hoja concreta
sheet = workbook

# Leer el valor de una celda
valor = sheet.value
print(f"Valor de A1: {valor}")

Om rijen of bereiken te doorlopen, is `iter_rows` je beste vriend. Je kunt rijen en kolommen filteren en elke cel afzonderlijk verwerken. Als je alleen leest, vermindert het inschakelen van de alleen-lezenmodus het geheugenverbruik en verbetert het de snelheid bij grote bestanden.

# Recorrer las primeras 10 filas
for fila in sheet.iter_rows(min_row=1, max_row=10):
    valores = 
    print(" ".join(valores))

Efficiënt gegevens lezen en schrijven met pandas

Als je je wilt richten op data-analyse en -manipulatie , is pandas de beste keuze. Het zet Excel-spreadsheets om in DataFrames (krachtige tabellen) waarmee je resultaten sneller kunt filteren, aggregeren, transformeren en exporteren dan met handmatige lussen.

import pandas as pd

# Leer un Excel a DataFrame
df = pd.read_excel("datos.xlsx")

# Ver las primeras filas
print(df.head())

Met de functie `read_excel` kunt u specifieke werkbladen, kolommen laden of de eerste rijen overslaan (erg handig voor bestanden met complexe kopteksten of notities). Dit geeft u meer controle en verbetert de efficiëntie, omdat u voorkomt dat u onnodige gegevens ophaalt.

# Hoja específica
df = pd.read_excel("datos.xlsx", sheet_name="Hoja2")

# Importar solo ciertas columnas (por etiqueta de Excel o nombre de columna)
df = pd.read_excel("datos.xlsx", usecols=)  # Por letras

# Omitir filas del principio
df = pd.read_excel("datos.xlsx", skiprows=4)

Nadat je je DataFrame hebt getransformeerd, kun je deze met één methode naar Excel exporteren. Door index=False in te stellen , voorkom je dat de index als een extra kolom wordt geschreven, wat vaak voorkomt bij het opstellen van bedrijfsrapporten.

# Guardar el DataFrame en Excel
 df.to_excel("datos_procesados.xlsx", index=False)

Wanneer pandas gebruiken en wanneer openpyxl gebruiken

Hoewel ze elkaar aanvullen, pakken ze niet hetzelfde probleem aan: pandas blinkt uit in bulkverwerking (filteren, aggregaties, joins, opschonen), terwijl openpyxl superieur is in opmaak (stijlen, randen, breedtes, formules, grafieken, het maken/verwijderen van werkbladen, enz.). Een verstandige keuze bespaart je tijd.

Als je duizenden cellen met een simpele regel wilt aanpassen (bijvoorbeeld 10% toevoegen aan een kolom), kun je dat met pandas in één regel doen; met openpyxl moet je door de cellen itereren met behulp van lussen en verwijzingen beheren. Wil je echter bedrijfsopmaak, stijlen toepassen of grafieken toevoegen aan het uiteindelijke Excel-bestand, dan is openpyxl de betere keuze.

Een zeer nuttige strategie is om beide te combineren: verwerk de gegevens met pandas en gebruik vervolgens openpyxl om de professionele afwerking te perfectioneren (vetgedrukte en gecentreerde kopteksten, kleuren, getalnotaties, enz.). Op deze manier profiteert u van optimale prestaties én een resultaat dat klaar is voor presentatie.

Algemene bewerkingen met pandas: selectie, filtering en wijzigingen

Met pandas is het selecteren van kolommen en het toepassen van voorwaardelijke filters een fluitje van een cent. Hierdoor kunt u grote datasets transformeren met gevectoriseerde bewerkingen zonder lussen te hoeven schrijven, wat resulteert in snellere en beter leesbare code.

import pandas as pd

archivo_excel = "ejemplo_excel.xlsx"
df = pd.read_excel(archivo_excel)

# Selección de columnas
df_col = df]

# Filtrado por condición
filtrado = df > 10]

# Nuevas columnas y transformaciones
df = df * 2
df = df.apply(lambda x: x + 5)

# Guardar resultado
df.to_excel("resultado_excel.xlsx", index=False)

Om slechts een gedeelte te inspecteren, kunt u `.head()` of de `.iloc` -indexeringsmethode gebruiken wanneer u rijen/kolommen op positie nodig hebt. Bovendien ondersteunt pandas bij het exporteren meerdere formaten (CSV, Parcel, enz.), waardoor er meer mogelijkheden zijn dan alleen Excel. Het is ook gebruikelijk om pandas aan te vullen met handleidingen voor rekenkundige bewerkingen in Excel bij het migreren van logica tussen de twee omgevingen.

Lezen en bewerken met openpyxl: van cellen naar bereiken

Als de focus ligt op het Excel-document zelf, kunt u met openpyxl werkmappen maken, werkbladen toevoegen, hernoemen en onnodige werkbladen verwijderen. Deze gedetailleerde controle is essentieel wanneer u zich wilt aanpassen aan de lay-out van een sjabloon of bestaande formules wilt behouden.

from openpyxl import Workbook, load_workbook

# Crear un libro nuevo
ewb = Workbook()
ws = ewb.active
ws.title = "Hoja Principal"
ewb.save("nuevo.xlsx")

# Cargar y manipular un libro existente
wb = load_workbook("datos_openpyxl.xlsx")
wb.create_sheet("Nueva Hoja")
del wb
wb.save("datos_openpyxl.xlsx")

Het openen van afzonderlijke cellen of bereiken is eenvoudig. U kunt waarden lezen, wijzigen en terugschrijven. Voor批量wijzigingen zal het gebruik van bereiken en inzicht in de structuur van Excel uw werk vergemakkelijken.

ws = wb
celda = ws
print(celda.value)

# Modificar valores
ws = "Nuevo Nombre"

# Recorrer un rango
for fila in ws:
    for c in fila:
        print(c.value)

Stijlen, opmaak en getallen toepassen met OpenPyxl

Een van de voordelen van openpyxl is dat je je rapport kunt aanpassen met lettertypen, randen, opvulling en uitlijning , evenals numerieke opmaak (zoals twee decimalen). Dit is essentieel voor rapporten die door niet-technische gebruikers worden bekeken.

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

ws = wb

# Estilos
fuente = Font(name="Arial", size=12, bold=True, color="FF000000")
borde = Border(left=Side(style="thin"), right=Side(style="thin"),
               top=Side(style="thin"), bottom=Side(style="thin"))
relleno = PatternFill(start_color="FFFF0000", end_color="FFFF0000", fill_type="solid")

# Aplicar a una celda
c = ws
c.font = fuente
c.border = borde
c.fill = relleno
c.alignment = Alignment(horizontal="center", vertical="center")
c.number_format = "0.00"  # Dos decimales

wb.save("datos_openpyxl.xlsx")

Je kunt ook rechtstreeks Excel-formules in cellen invoeren. Deze worden dan opnieuw berekend wanneer het bestand in Excel wordt geopend. Let wel op veelvoorkomende fouten met Excel-formules , die vaak optreden wanneer door code gegenereerde gegevens worden gecombineerd met spreadsheetlogica.

ws.value = "=SUM(A1:B1)"
wb.save("datos_openpyxl.xlsx")

Eenvoudige grafieken en visualisaties in Excel met OpenPyxl

Om een ​​rapport te voltooien, is het soms nodig om een ​​grafiek in het werkblad zelf op te nemen. Met openpyxl kunt u staafdiagrammen, lijndiagrammen of andere soorten grafieken maken op basis van gegevensbereiken en deze op een specifieke locatie plaatsen.

from openpyxl.chart import BarChart, Reference

chart = BarChart()
datos = Reference(ws, min_col=1, min_row=1, max_col=2, max_row=5)
chart.add_data(datos, titles_from_data=False)
ws.add_chart(chart, "E1")
wb.save("datos_openpyxl.xlsx")

Rapportautomatisering: Pandas en OpenPyxl combineren

Een zeer nuttige aanpak is om gegevens te verwerken met pandas (totalen, gemiddelden, groeperingen) en de resultaten te exporteren naar een nieuwe werkmap, die je vervolgens opmaakt met openpyxl om een ​​goed opgemaakt rapport te genereren . Dit patroon is goed schaalbaar voor terugkerende rapporten.

import pandas as pd
from openpyxl import Workbook
from openpyxl.styles import Font, Alignment

# Leer ventas
ventas = pd.read_excel("ventas.xlsx")

# Agregaciones
por_producto = ventas.groupby("Producto").sum()
promedio = ventas.mean()

# Crear libro de reporte
wb = Workbook()
ws = wb.active
ws.title = "Reporte de Ventas"

# Cabeceras
ws = "Producto"
ws = "Total Ventas"
ws = "Promedio Ventas Mensual"

enc = Font(bold=True)
for celda in ("A1", "B1", "C1"):
    ws.font = enc
    ws.alignment = Alignment(horizontal="center")

# Datos
fila = 2
for producto, total in por_producto.items():
    ws = producto
    ws = total
    ws = promedio
    fila += 1

wb.save("reporte_ventas.xlsx")

Als u ook visuele consistentie wilt garanderen, voeg dan dunne randen en getalnotatie toe aan de kolommen met bedragen. Op deze manier is uw rapport direct klaar om te delen zonder handmatige aanpassingen in Excel; en als u de gegevensinvoer wilt automatiseren, kunt u technieken gebruiken om gegevens automatisch in te vullen op basis van patronen.

Typische processen: filteren, transformeren en opslaan met Pandas

Een veelvoorkomend gebruiksscenario is filteren op basis van een voorwaarde en het resultaat opslaan in een nieuw bestand. Dit kan met pandas in slechts een paar regels code en is ideaal voor dataopschoning of -voorbereidingsprocessen voor business teams.

import pandas as pd

df = pd.read_excel("example.xlsx")

# Filtrar ventas > 1000
filtrado = df > 1000]

# Guardar sin índice
filtrado.to_excel("filtered.xlsx", index=False)
print("Archivo guardado")

Als u een originele kolom wilt behouden en een aangepaste kolom wilt maken (bijvoorbeeld door 10% toe te voegen aan "Totale oppervlakte"), voorkomt de vectorbewerking lussen en levert u een overzichtelijke en gemakkelijk te inspecteren DataFrame op.

df = pd.read_excel("cultivos.xlsx")

df = df
df = df * 1.1

# Mostrar primeras 10 filas a partir de la tercera columna
print(df.iloc)

Als u er de voorkeur aan geeft om resultaten in het uiteindelijke bestand tijdelijk te verbergen in plaats van ze te verwijderen, kunt u regels gebruiken om rijen te verbergen op basis van celwaarden om de teambeoordeling te vergemakkelijken.

Rijen verwijderen en gegevens opschonen

Een andere veelvoorkomende taak is het verwijderen van records op basis van positie of voorwaarde. Met pandas is het verwijderen van de eerste 10 rijen eenvoudig met behulp van de index; voor complexere filters kunt u booleaanse expressies gebruiken zonder for-lussen.

df = pd.read_excel("cultivos.xlsx")

# Quitar las 10 primeras filas por índice
df.drop(df.index, inplace=True)
print(df.head(10))

Als uw opschoning afhankelijk is van regels zoals "verwijder rijen met een even totale oppervlakte", kunt u een voorwaarde maken en deze toepassen. Zo blijft uw code expressief en onderhoudbaar , in plaats van afhankelijk te zijn van lussen en handmatige tellers. En als het werkblad beveiligd is, vergeet dan niet hoe u een met een wachtwoord beveiligd Excel-blad kunt deblokkeren voordat u het wijzigt.

Bewerken en verwijderen met openpyxl: cellen en rijen

Bij het werken met openpyxl moet je, om een ​​hele kolom te wijzigen, door de cellen heen gaan. Het voordeel hiervan is dat je nieuwe kolommen kunt invoegen, de oorspronkelijke lay-out kunt behouden en het bestand kunt opslaan zonder de opmaak te beschadigen.

from openpyxl import load_workbook
from openpyxl.utils import get_column_letter

wb = load_workbook("cultivos.xlsx")
ws = wb

# Insertar columna G y titularla
ws.insert_cols(7)
ws = "Area Total Modificada"

# Sumar 10% a la columna F y guardar antiguo valor en G
for fila in ws.iter_rows(min_row=2):
    valor_antiguo = None
    for celda in fila:
        col = get_column_letter(celda.column)
        if col == "F":
            valor_antiguo = celda.value
            celda.value = float(celda.value) * 1.1
        if col == "G":
            celda.value = valor_antiguo

wb.save("cultivos_modify.xlsx")

Om rijen op basis van hun positie te verwijderen, biedt openpyxl ook een oplossing met één enkele aanroep. Voor complexere situaties is het echter nodig om te itereren en te bepalen wat er verwijderd moet worden op basis van de inhoud van elke rij.

wb = load_workbook("cultivos.xlsx")
ws = wb

# Eliminar las 10 primeras filas (ajusta idx si hay cabeceras)
ws.delete_rows(idx=1, amount=10)
wb.save("cultivos_modify.xlsx")

Consolideer meerdere Excel-bestanden tot één

Een klassiek scenario: je hebt tientallen .xlsx-bestanden met hetzelfde schema en je wilt ze samenvoegen tot één tabel voor analyse. Met pandas en glob kun je dat in een paar regels doen, zonder elk bestand handmatig te hoeven openen.

import pandas as pd
import glob

excel_files = glob.glob("*.xlsx")

# Concatenar todo en un DataFrame
todos = pd.concat(, ignore_index=True)

todos.to_excel("consolidated_data.xlsx", index=False)

Deze aanpak is perfect voor maandelijkse integraties, rapporten met meerdere delegaties of elk proces waarbij u meerdere boeken met een homogene structuur ontvangt.

Genereer automatisch rapporten per afdeling

Vanuit een bestand met algemene verkoopgegevens kunt u segmenteren op afdeling en automatisch een gepersonaliseerd rapport voor elke afdeling genereren. Elk bestand is vervolgens klaar om te delen met uw team.

import pandas as pd

sales = pd.read_excel("sales_data.xlsx")
departamentos = sales.unique()

for dpto in departamentos:
    df_dpto = sales == dpto]
    df_dpto.to_excel(f"{dpto}_report.xlsx", index=False)

Als u elk rapport ook wilt opmaken, kunt u het resulterende bestand laden met openpyxl en kopteksten opmaken, kolombreedtes aanpassen en een bedrijfskleur toevoegen. U kunt ook de bestandsorganisatie automatiseren en mappen en submappen aanmaken voor elke afdeling.

Klein interactief dashboard met Tkinter en pandas

Voor snelle prototyping kun je een eenvoudig venster maken dat kolommen weergeeft en het gemiddelde van de geselecteerde kolom berekent. Het is geen volwaardige BI-tool, maar het werkt prima voor snelle validaties zonder Python te verlaten.

import tkinter as tk
import pandas as pd
from tkinter import messagebox

file = "data.xlsx"
data = pd.read_excel(file)

def calcular_media():
    col = listbox.get(listbox.curselection())
    media = data.mean()
    messagebox.showinfo("Resultado", f"Promedio en {col}: {media:.2f}")

root = tk.Tk()
root.title("Dashboard interactivo")

listbox = tk.Listbox(root)
listbox.pack()
for c in data.columns:
    listbox.insert(tk.END, c)

btn = tk.Button(root, text="Calcular promedio", command=calcular_media)
btn.pack()

root.mainloop()

Voor complexere rapportageprojecten is het wellicht beter om dit naar een webapplicatie met Streamlit of Dash te verplaatsen , maar voor een snelle, lokale toepassing kan Tkinter je met minimale code uit de problemen helpen.

Verkennende analyse: statistieken en grafieken met Pandas + Matplotlib

Als uw Excel-spreadsheet klant- of verkoopgegevens bevat, is het handig om snel de verdelingen en verbanden te bekijken. Pandas kan beschrijvende statistieken leveren en Matplotlib kan zeer nuttige histogrammen en spreidingsdiagrammen genereren.

import pandas as pd
import matplotlib.pyplot as plt

clientes = pd.read_excel("clientes.xlsx")

# Estadísticas generales
print(clientes.describe())

# Histograma de edades
clientes.hist(bins=20)
plt.xlabel("Edad")
plt.ylabel("Frecuencia")
plt.title("Distribución de edades")
plt.show()

# Dispersión ingresos vs satisfacción
clientes.plot.scatter(x="Ingresos", y="Satisfaccion")
plt.xlabel("Ingresos")
plt.ylabel("Satisfacción")
plt.title("Ingresos vs. Satisfacción")
plt.show()

Dit stelt u in staat extreme waarden, vertekeningen of interessante verbanden te identificeren voor verder onderzoek. Als u dit in Excel wilt rapporteren, exporteer dan samenvattende tabellen met pandas en maak grafieken in het rapport met openpyxl voor een professioneel eindresultaat. Voor specifieke statistische analyses kunt u ook de kwartielfunctie in Excel raadplegen als referentie bij het migreren van indicatoren.

Resultaten opslaan met pandas en openpyxl

Met pandas is het schrijven naar Excel direct; bovendien heb je het voordeel dat je naar andere formaten zoals CSV of Parse kunt exporteren. Als het bestand voor zakelijk gebruik is, voeg dan werkbladen met verschillende details of gefilterde gegevens per segment toe binnen dezelfde werkmap.

df = pd.read_excel("cultivos.xlsx")

df = "SI"
df.to_excel("cultivos_modify_pandas.xlsx", index=False)

Als je met openpyxl werkt, vergeet dan niet dat je nieuwe kolommen kunt aanmaken en deze kunt vullen op basis van een bereik. Dit is een eenvoudige manier om beoordeelde records in rapporten met een vaste structuur te markeren.

from openpyxl import load_workbook

wb = load_workbook("cultivos.xlsx")
ws = wb

ws = "Revisado"
for celda in ws:
    celda.value = "SI"

wb.save("cultivos_modify_openpyxl.xlsx")

Prestaties en goede praktijken

Voor grote bestanden is het het beste om de hoeveelheid data die in het geheugen wordt geladen te beperken. Lees in pandas alleen de noodzakelijke kolommen met `usecols` en vermijd het verwerken van irrelevante kolommen. Gebruik in openpyxl `read_only=True` voor lezen en `write_only=True` voor schrijven.

Let op de gegevenstypen: Excel slaat getallen soms op als tekst, wat problemen kan veroorzaken met filters en aggregaties. Bij het laden met pandas kun je gegevenstypen specificeren of kolommen normaliseren na het inlezen om verrassingen met datums, bedragen of ID's te voorkomen.

Houd er bij het gebruik van Excel-formules rekening mee dat niet alle functies door alle bibliotheken even goed worden ondersteund; ze worden hoogstwaarschijnlijk opnieuw berekend wanneer ze in Excel worden geopend . Als u vaste waarden nodig hebt, kunt u deze het beste eerst met pandas evalueren en vervolgens de berekende getallen wegschrijven.

Voor compatibiliteit kunt u het beste prioriteit geven aan .xlsx- bestanden (modern formaat) en uw scripts testen met verschillende versies van Excel wanneer uw gebruikers uiteenlopende omgevingen hebben. Dit vermindert problemen die worden veroorzaakt door functies of mogelijkheden die niet worden ondersteund in oudere versies.

Denk ten slotte na over reproduceerbare pipelines: stel afhankelijkheden in (Poetry, requirements.txt), documenteer je parameters (sheet_name, usecols, skiprows) en voeg minimale logs toe voor debugging. Je bespaart tijd wanneer het project groeit of de gegevensbron verandert.

Als je dit punt hebt bereikt, beheers je al het lezen, verwerken en schrijven van Excel-gegevens met pandas en openpyxl. Je weet wanneer je welke tool moet gebruiken en hoe je ze kunt combineren voor professionele rapporten. Vanaf nu zie je je spreadsheets als zowel bronnen als bestemmingen binnen een robuuste, geautomatiseerde en veel snellere workflow.