openpyxl: Automatizar tareas de Excel con Python

openpyxl es una eficaz biblioteca de Python que te permite leer y escribir archivos de Excel en los modernos formatos basados en XML (.xlsx, .xlsm) introducidos en Excel 2007, con compatibilidad total con funciones avanzadas a partir de Excel 2010. Se desarrolló originalmente como una adaptación a Python de PHPExcel y sigue siendo mantenido por una activa comunidad de programadores. La biblioteca destaca en la automatización de datos, la elaboración de informes y los flujos de trabajo de formato, lo que la hace especialmente útil para los usuarios que necesitan trabajar con archivos de Excel pero no tienen Excel instalado en sus sistemas.

Muchas organizaciones utilizan openpyxl para automatizar informes financieros, generar gráficos a partir de exportaciones de bases de datos y aplicar fórmulas y formatos a informes de marketing. Esta biblioteca ofrece un control detallado de los archivos de Excel mediante código Python.

¿Qué es openpyxl?

openpyxl está diseñado específicamente para ofrecer compatibilidad nativa con los formatos de Excel 2010+. La biblioteca funciona con varios formatos de archivo:

•  .xlsx - Cuaderno de trabajo Excel

•  .xlsm - Cuaderno de trabajo Excel con macros

•  .xltx - Plantilla Excel

•  .xltm - Plantilla Excel con macros

Una gran ventaja de openpyxl es que no requiere que Excel esté instalado en tu ordenador. Esto lo hace ideal para entornos de servidor, sistemas de informes automatizados y conductos de procesamiento de datos. Otras bibliotecas de Python, como pandas, utilizan openpyxl como interfaz Excel, lo que demuestra su fiabilidad y funcionalidad.

Instalar e importar openpyxl

Instalar openpyxl es sencillo utilizando pip:

pip install openpyxl

Para mejorar la seguridad al trabajar con archivos de fuentes no fiables, también puedes instalar el paquete opcional defusedxml:

pip install defusedxml

Para utilizar openpyxl en tus scripts de Python, importa las clases necesarias:

from openpyxl import Workbook, load_workbook

Conceptos clave y terminología

Para utilizar openpyxl con eficacia, necesitas comprender la estructura básica de Excel:

• Cuadernos de trabajo: El propio archivo Excel, que contiene una o varias hojas de cálculo
• Ficha de trabajo: Fichas/hojas individuales dentro de un cuaderno de trabajo
• Celda: Puntos de datos individuales en una hoja de cálculo, identificados por la letra de la columna y el número de la fila (por ejemplo, "A1")
• Fila: Línea horizontal de celdas, identificadas por números (1, 2, 3...)
• Columna: Línea vertical de celdas, identificadas por letras (A, B, C...)

En openpyxl, puedes hacer referencia a celdas utilizando cualquiera de los dos métodos:

• Referencias de estilo de Excel: sheet[“A1”]

• Indexación fila-columna: sheet.cell(row=1, column=1) (Nota: openpyxl utiliza indexación basada en 1, no en 0)

Lectura de archivos Excel con openpyxl

Aquí tienes un ejemplo práctico que muestra cómo leer datos de un fichero Excel:

Carga y explora un cuaderno de trabajo

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

Los parámetros opcionales controlan cómo se carga el cuaderno de trabajo:

• read_only=True mejora el rendimiento de los archivos grandes, pero limita la capacidad de edición

• data_only=True devuelve valores calculados en lugar de fórmulas

Iterar a través de los datos

Para procesar varias celdas con eficacia, utiliza los métodos de iteración:

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

Convertir a estructuras de datos Python

Puedes transformar fácilmente datos de Excel en estructuras de datos de Python:

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

Escribir archivos Excel con openpyxl

Crear y modificar archivos Excel es igual de sencillo con openpyxl.

Crea un cuaderno de trabajo y escribe datos básicos

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

Modificar archivos Excel existentes

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

Añade datos dinámicamente

El método .append() facilita la adición de nuevas filas:

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

Formato y estilo en Excel

openpyxl ofrece amplias opciones para dar formato y estilo a las celdas.

Añade fuentes, bordes y alineación

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

Formato condicional

El formato condicional te permite aplicar estilos en función de los valores de las celdas:

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

Funciones avanzadas

openpyxl admite muchas funciones avanzadas de Excel que ayudan a realizar tareas complejas en hojas de cálculo.

Añadir fórmulas

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

Nota: Al utilizar load_workbook(), openpyxl no calcula automáticamente las fórmulas. Para ver los valores calculados, utiliza load_workbook(“file.xlsx”, data_only=True).  

Insertar gráficos

openpyxl puede crear varios tipos de gráficos:

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

Insertar imágenes

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

Nota: Necesitas tener instalada la biblioteca Pillow para trabajar con imágenes: pip install pillow

Gestión de hojas, filas y columnas

openpyxl proporciona funciones para gestionar la estructura de tus archivos Excel:

Crear y eliminar hojas

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

Cambiar el nombre de las hojas

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

Copiar hojas de ejercicios

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

Insertar y eliminar filas y columnas

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

Congelar paneles y añadir filtros

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

Limitaciones y comentarios de la comunidad

Aunque openpyxl tiene muchas funciones, los usuarios han observado algunas limitaciones:

• Soporte limitado para rangos con nombre
• Los ejemplos de documentación pueden ser escasos para las funciones avanzadas
• El rendimiento puede ser lento para archivos muy grandes (aunque el modo de sólo lectura ayuda)

A pesar de estas limitaciones, openpyxl cuenta con un fuerte apoyo de la comunidad. Los problemas pueden notificarse en el rastreador de GitLab, y a veces se dispone de asistencia profesional. Para más información y ayuda, puedes consultar:

• documentación de openpyxl
• Código fuente
• Seguimiento de problemas
• Apoyo profesional

Conclusión

openpyxl es una biblioteca bien establecida para la automatización de Excel basada en Python que ofrece soporte completo para leer, escribir y estilizar archivos de Excel. Permite un control detallado de los resultados de las hojas de cálculo en flujos de trabajo automatizados, desde la manipulación de celdas individuales hasta la creación de gráficos y la aplicación de formatos complejos.

Para los usuarios que necesiten generar o analizar archivos Excel mediante programación, sobre todo al crear informes o procesar datos tabulares, openpyxl proporciona un amplio conjunto de herramientas. Su capacidad para funcionar sin necesidad de instalar Excel lo hace especialmente valioso para entornos de servidor y conductos de datos automatizados.

Tanto si estás creando modelos financieros, analizando datos o generando informes, openpyxl te proporciona las herramientas para manejar archivos Excel de forma eficiente mediante código Python.