Jak czytać i przetwarzać arkusze Excela za pomocą Pandas i OpenPyxl: od podstaw do poziomu profesjonalnego

Jeśli zajmujesz się analizą danych lub potrzebujesz zautomatyzować powtarzające się zadania związane z arkuszami kalkulacyjnymi, połączenie Pythona z Excelem to skuteczna strategia przyspieszająca przepływ pracy . Excel pozostaje dominującym narzędziem w wielu firmach, a nauczenie się, jak zapobiegać konwersji liczb na daty, jest kluczowe. Python oferuje natomiast moc, elastyczność i solidny ekosystem bibliotek przeznaczonych do obsługi danych. W tym przewodniku szczegółowo dowiesz się, jak odczytywać i przetwarzać arkusze kalkulacyjne Excela za pomocą bibliotek Pandas i OpenPyXL , kiedy używać każdego z nich i jak najlepiej wykorzystać ich możliwości w rzeczywistych sytuacjach.

Poza prostym otwarciem pliku i przejrzeniem kilku komórek, dowiesz się, jak ładować określone arkusze i zakresy, filtrować, przekształcać i zapisywać wyniki , formatować komórki za pomocą zaawansowanych stylów, tworzyć nowe skoroszyty i arkusze, generować automatyczne raporty, a nawet tworzyć wykresy i małe pulpity nawigacyjne. Przejdziemy od podstaw do praktycznych przykładów, z gotowym do adaptacji kodem oraz zaleceniami dotyczącymi wydajności i najlepszymi praktykami, aby uniknąć wąskich gardeł i typowych błędów.

Przygotowanie środowiska i niezbędnych bibliotek

Zanim zaczniesz, upewnij się, że masz zainstalowaną najnowszą wersję Pythona. Zalecamy Pythona w wersji 3.7 lub nowszej, aby zapewnić zgodność z bibliotekami, których będziemy używać. Aby sprawdzić swoją wersję, możesz uruchomić następujące polecenie w terminalu.

python --version

Do obsługi Excela w Pythonie, kluczowymi bibliotekami, których będziesz używać, są pandas i openpyxl ; każda z nich zaspokaja inne potrzeby. Możesz je szybko zainstalować za pomocą pip i zacząć eksperymentować.

pip install pandas openpyxl

Jeśli wolisz zarządzać zależnościami za pomocą menedżera takiego jak Poetry, możesz także zainstalować oba pakiety za pomocą prostych poleceń , na przykład poetry add pandas i poetry add openpyxl , co pomoże Ci utrzymać powtarzalne środowisko dla każdego projektu bez problemów.

Czytanie książek, arkuszy i komórek za pomocą openpyxl

Biblioteka openpyxl działa bezpośrednio z plikami .xlsx, umożliwiając precyzyjne otwieranie skoroszytów, manipulowanie arkuszami oraz odczytywanie i zapisywanie komórek. Jest idealna, gdy potrzebujesz precyzyjnej kontroli nad formatowaniem , stosowaniem stylów i formuł oraz pracą ze strukturą Excela.

from openpyxl import load_workbook

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

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

Po otwarciu skoroszytu można wybrać arkusz według nazwy i wyświetlić określone wartości. Takie podejście jest przydatne, gdy chcemy przejrzeć poszczególne komórki lub iterować po zakresach bez przekształcania wszystkiego w strukturę tabelaryczną.

# Seleccionar una hoja concreta
sheet = workbook

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

Do iteracji po wierszach lub zakresach, `iter_rows` to Twój najlepszy przyjaciel. Możesz zawęzić zakres wierszy i kolumn oraz przetwarzać każdą komórkę. Jeśli tylko odczytujesz, włączenie trybu tylko do odczytu zmniejsza zużycie pamięci i poprawia szybkość dużych plików.

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

Wydajne odczytywanie i zapisywanie danych dzięki Pandas

Jeśli Twoim celem jest analiza i manipulacja danymi , Pandas to najlepszy wybór. Przekształca arkusze kalkulacyjne Excela w DataFrame (wydajne tabele) umożliwiające filtrowanie, agregowanie, transformację i eksportowanie wyników szybciej niż w przypadku pętli ręcznych.

import pandas as pd

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

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

Funkcja read_excel umożliwia wczytanie określonych arkuszy, kolumn lub pominięcie początkowych wierszy (bardzo przydatne w przypadku plików ze złożonymi nagłówkami lub notatkami). Zapewnia to kontrolę i poprawia wydajność, ponieważ pozwala uniknąć pobierania niepotrzebnych danych.

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

Po zakończeniu transformacji DataFrame możesz wyeksportować go do Excela za pomocą jednej metody. Ustawienie index=False pozwala uniknąć zapisywania indeksu jako dodatkowej kolumny, co jest bardzo powszechne podczas przygotowywania raportów biznesowych.

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

Kiedy używać Pandas, a kiedy OpenPyxl

Choć się uzupełniają, nie rozwiązują tego samego problemu: Pandas doskonale radzi sobie z przetwarzaniem zbiorczym (filtrowanie, agregacje, łączenia, czyszczenie), podczas gdy OpenPyXL jest lepszy w formatowaniu (style, obramowania, szerokości, formuły, wykresy, tworzenie/usuwanie arkuszy itp.). Mądry wybór oszczędza czas.

Jeśli chcesz zmodyfikować tysiące komórek za pomocą prostej reguły (na przykład dodając 10% do kolumny), Pandas pozwala to zrobić w jednym wierszu; w OpenPyXL musisz iterować komórki za pomocą pętli i zarządzać odwołaniami. Jeśli jednak chcesz zastosować firmowe formatowanie, style lub dodać wykresy w końcowym pliku Excel, OpenPyXL będzie lepszym wyborem.

Bardzo użyteczną strategią jest połączenie obu: procesu z Pandas, a po wygenerowaniu tabeli finalnej, użycie OpenPyXL do dopracowania profesjonalnego wyglądu (pogrubienie, wyśrodkowanie nagłówków, kolory, formatowanie liczb itp.). W ten sposób uzyskuje się wydajność i gotowy do prezentacji wynik.

Typowe operacje w Pandas: selekcja, filtrowanie i modyfikacje

Dzięki Pandas wybór kolumn i filtrowanie warunkowe to pestka. Pozwala to na transformację dużych zbiorów danych za pomocą operacji wektorowych bez pisania pętli, co przekłada się na szybszy i bardziej czytelny kod.

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)

Aby zbadać tylko fragment, można użyć metody indeksowania `.head()` lub `.iloc`, gdy potrzebne są wiersze/kolumny według pozycji. Dodatkowo, podczas eksportu, Pandas obsługuje wiele formatów (CSV, Parcel itp.), otwierając możliwości wykraczające poza Excela. Powszechną praktyką jest również uzupełnianie Pandas o przewodniki dotyczące operacji arytmetycznych w Excelu podczas migracji logiki między dwoma środowiskami.

Czytanie i edycja za pomocą openpyxl: od komórek do zakresów

Jeśli skupiasz się na samym dokumencie Excela, openpyxl pozwala tworzyć skoroszyty, dodawać arkusze, zmieniać ich nazwy i usuwać te, których nie potrzebujesz. Ta szczegółowa kontrola jest niezbędna, gdy musisz dostosować się do układu szablonu lub zachować istniejące formuły.

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

Dostęp do pojedynczych komórek lub zakresów jest prosty. Możesz również odczytywać, zmieniać wartości i zapisywać je. W przypadku zmian zbiorczych, korzystanie z zakresów i zrozumienie struktury programu Excel ułatwi Ci pracę.

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)

Zastosuj style, formaty i liczby za pomocą OpenPyxl

Jedną z zalet OpenPyXL jest możliwość personalizacji raportu za pomocą czcionek, obramowań, odstępów i wyrównania , a także formatowania liczb (np. z dwoma miejscami po przecinku). Jest to kluczowe w przypadku raportów przeznaczonych dla użytkowników nietechnicznych.

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

Można również wprowadzać formuły Excela bezpośrednio do komórek, które zostaną przeliczone po otwarciu pliku w Excelu. Należy pamiętać o typowych błędach w formułach Excela , które często występują podczas łączenia danych generowanych przez kod z logiką arkusza kalkulacyjnego.

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

Proste wykresy i wizualizacje w programie Excel z OpenPyxl

Aby utworzyć raport, czasami konieczne jest dodanie wykresu do skoroszytu. Za pomocą OpenPyXL można tworzyć wykresy słupkowe, liniowe i inne typy wykresów na podstawie zakresów danych i umieszczać je w określonym miejscu.

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

Automatyzacja raportów: łączenie Pandas i OpenPyxl

Bardzo przydatnym podejściem jest przetwarzanie danych za pomocą Pandas (sumy, średnie, grupowania) i eksportowanie wyników do nowego skoroszytu, który następnie formatuje się za pomocą OpenPyXL, aby uzyskać poprawnie sformatowany raport . Ten schemat dobrze skaluje się w przypadku raportów cyklicznych.

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

Jeśli chcesz również zadbać o spójność wizualną, dodaj cienkie obramowanie i formatowanie liczbowe do kolumn kwot. W ten sposób raport będzie gotowy do udostępnienia bez ręcznych korekt w programie Excel. A jeśli potrzebujesz zautomatyzować wprowadzanie danych, możesz skorzystać z technik automatycznego uzupełniania danych na podstawie wzorców.

Typowe procesy: filtrowanie, transformacja i zapisywanie w Pandas

Typowym przypadkiem użycia jest filtrowanie według warunku i zapisywanie wyniku do nowego pliku. Można to zrobić w zaledwie kilku linijkach kodu w Pandas i idealnie nadaje się do czyszczenia lub przygotowywania danych w zespołach biznesowych.

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

Jeśli chcesz zachować oryginalną kolumnę i utworzyć zmodyfikowaną (na przykład dodając 10% do „Całkowitej powierzchni”), operacja wektorowa pozwala uniknąć pętli i pozostawia czystą i łatwą do przeglądania ramkę danych.

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)

Jeśli wolisz tymczasowo ukryć wyniki w pliku końcowym zamiast je usuwać, rozważ zastosowanie reguł ukrywających wiersze na podstawie wartości komórek, co ułatwi zespołowi przeglądanie wyników.

Usuwanie wierszy i czyszczenie danych

Innym częstym zadaniem jest usuwanie rekordów według pozycji lub warunku. W Pandas usunięcie pierwszych 10 wierszy jest proste dzięki indeksowi; w przypadku bardziej złożonych filtrów należy użyć wyrażeń boolowskich bez pętli for.

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

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

Jeśli czyszczenie opiera się na regułach takich jak „usuń wiersze o parzystej powierzchni całkowitej”, możesz utworzyć warunek i go zastosować, zachowując ekspresję i łatwość obsługi kodu , zamiast polegać na pętlach i ręcznych licznikach. A jeśli arkusz jest chroniony, pamiętaj, jak usunąć ochronę z arkusza Excela chronionego hasłem przed jego modyfikacją.

Edycja i usuwanie za pomocą openpyxl: komórki i wiersze

Podczas pracy z OpenPyXL, modyfikacja całej kolumny wymaga przechodzenia przez komórki. Zaletą jest to, że można wstawiać nowe kolumny, zachowywać oryginalny układ i zapisywać w innym pliku bez naruszania formatowania.

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

Aby usunąć wiersze według pozycji, openpyxl również rozwiązuje ten problem za pomocą jednego wywołania, jednak w przypadku bardziej zaawansowanych warunków konieczne będzie iteracyjne wykonanie polecenia i podjęcie decyzji, co usunąć, na podstawie zawartości każdego wiersza.

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

Konsolidacja wielu plików Excel w jeden

Klasyczny scenariusz: masz dziesiątki plików .xlsx z tym samym schematem i chcesz połączyć je w jedną tabelę do analizy. Dzięki bibliotekom Pandas i Glob możesz to zrobić w kilku linijkach, bez konieczności ręcznego otwierania każdego pliku.

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)

To podejście doskonale sprawdza się w przypadku miesięcznych integracji, raportów obejmujących wiele delegacji lub dowolnego procesu, w którym otrzymujesz wiele ksiąg o jednorodnej strukturze.

Automatyczne generowanie raportów według działu

Z pliku zawierającego ogólne dane sprzedażowe możesz segmentować je według działów i automatycznie generować spersonalizowany raport dla każdego z nich . Każdy plik jest wtedy gotowy do udostępnienia zespołowi.

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)

Jeśli potrzebujesz również sformatować każdy raport, możesz załadować plik wynikowy za pomocą OpenPyXL i nadać mu styl nagłówków , dostosować szerokość kolumn i dodać kolor firmowy. Możesz również zautomatyzować organizację plików i utworzyć kaskadowe foldery i podfoldery dla każdego działu.

Mały interaktywny panel z Tkinterem i pandami

Do szybkiego prototypowania można utworzyć proste okno, które wyświetla kolumny i oblicza średnią z wybranej kolumny. Nie jest to pełnoprawne narzędzie BI, ale sprawdza się w przypadku szybkich walidacji bez wychodzenia z Pythona.

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

W przypadku bardziej złożonych projektów raportowania możesz przenieść je do aplikacji internetowej za pomocą Streamlit lub Dash , ale jeśli szukasz szybkiego narzędzia lokalnego, Tkinter pomoże ci wyjść z opresji, wymagając bardzo mało kodu.

Analiza eksploracyjna: statystyka i grafika z Pandas + Matplotlib

Gdy arkusz kalkulacyjny Excel zawiera dane o klientach lub sprzedaży, warto szybko przejrzeć rozkłady i relacje. Pandas może zapewnić statystyki opisowe, a Matplotlib może generować bardzo przydatne histogramy i wykresy punktowe .

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

Pozwala to zidentyfikować wartości skrajne, odchylenia lub interesujące zależności do dalszych badań. Jeśli chcesz to zgłosić w programie Excel, wyeksportuj tabele podsumowujące za pomocą Pandas i utwórz wykresy w książce za pomocą OpenPyXL, aby uzyskać dopracowany wynik. W przypadku konkretnych analiz statystycznych możesz również skorzystać z funkcji kwartylowej w programie Excel jako odniesienia podczas migracji wskaźników.

Zapisywanie wyników za pomocą pandas i openpyxl

Dzięki Pandas zapisywanie w Excelu jest natychmiastowe; dodatkowo masz możliwość eksportu do innych formatów, takich jak CSV czy Parse. Jeśli plik jest przeznaczony do użytku biznesowego, dodaj arkusze z różnymi szczegółami lub danymi filtrowanymi według segmentów w obrębie tego samego skoroszytu.

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

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

Jeśli korzystasz z OpenPyXL, pamiętaj, że możesz tworzyć nowe kolumny i wypełniać je zakresami. To prosty sposób na oznaczenie sprawdzonych rekordów w raportach za pomocą stałej struktury.

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

Wydajność i dobre praktyki

W przypadku dużych plików najlepiej ograniczyć ilość danych ładowanych do pamięci. W Pandas odczytaj tylko niezbędne kolumny za pomocą `usecols` i unikaj przetwarzania nieistotnych kolumn. W OpenPyXL użyj `read_only=True` do odczytu i `write_only=True` do zapisu.

Zwróć uwagę na typy danych: Excel czasami zapisuje liczby jako tekst, co może zaburzyć działanie filtrów i agregacji. Podczas ładowania za pomocą Pandas, możesz określić typy danych lub znormalizować kolumny po odczytaniu, aby uniknąć niespodzianek związanych z datami, kwotami lub identyfikatorami.

Korzystając z formuł programu Excel, pamiętaj, że nie wszystkie funkcje są obsługiwane przez biblioteki w równym stopniu; najprawdopodobniej zostaną one przeliczone po otwarciu w programie Excel . Jeśli potrzebujesz stałych wartości, najpierw oblicz je w Pandas, a następnie zapisz obliczone liczby.

Aby zapewnić zgodność, priorytetyzuj pliki .xlsx (format nowoczesny) i testuj skrypty w różnych wersjach programu Excel, gdy użytkownicy korzystają z heterogenicznych środowisk. Zmniejsza to liczbę problemów spowodowanych przez funkcje lub funkcjonalności nieobsługiwane w starszych wersjach.

Na koniec pomyśl o powtarzalnych potokach: ustaw zależności (Poetry, requirements.txt), udokumentuj parametry (sheet_name, usecols, skiprows) i dodaj minimalną liczbę logów do debugowania. Zaoszczędzisz czas, gdy projekt się skaluje lub źródło danych ulegnie zmianie.

Jeśli dotarłeś tak daleko, opanowałeś już czytanie, przetwarzanie i zapisywanie danych w Excelu za pomocą Pandas i OpenPyXL. Wiesz, kiedy używać każdego z tych narzędzi i jak je łączyć, aby tworzyć profesjonalne raporty. Od teraz Twoje arkusze kalkulacyjne będą zarówno źródłami, jak i miejscami docelowymi w ramach solidnego, zautomatyzowanego i znacznie szybszego przepływu pracy.