openpyxl

Es un módilo d ePython que permite leer, crear y modificar ficheros Excel.

En lso ejemplo uho la palabra libro que se corresponde con el libro, la hoja entera, fichero o en inglés workbook o Spreadsheet y la palabra hoja que se corresponde con la pestaña, hoja o en inglés sheet o worksheet.

Para instalar como siempre usamos pip3:

pip3 install openpyxl

Leer un fichero Excel

Para poder leer un fichero Excel debemos abrirlo:

from openpyxl import load_workbook

# leemos el fichero
libro = load_workbook('fichero.xlsx')

# obtenemos la pestaña/hoja activa (nada mas abrir es la primera)
hoja = libro.active

# mostramos la celda B1 = 1:2
print(hoja.cell(row=1, column=2).value)
print(hoja['B1'].value)

Crear un fichero Excel

Para poder crear (o modificar uno que de haya abierto) un fichero Excel debemos:

from openpyxl import load_workbook

# creamos un libro nuevo
libro = load_workbook()

# obtenemos la pestaña/hoja activa (la primera)
hoja = libro.active

# metemos algún dato
hoja['A1'] = "valor A1"
hoja['B2'] = "valor B2"

# grabamos el libro
libro.save(filaname="fichero.xlsx")

Información de la hoja

Podemos obtener diferente información de la pestaña (hoja excel):

dimensiones de la hoja

Podemos obtener desde la primera celda (A1 = 1:1) hasta el máximo que hay definido.

from openpyxl import load_workbook

# leemos el fichero
libro = load_workbook('fichero.xlsx')

# obtenemos la pestaña/hoja activa (nada mas abrir es la primera)
hoja = libro.active

hoja.min_row
1

hoja.max_row
139

hoja.min_column
1

hoja.max_column
13

hoja.dimensions
'A1:M139'

Información de las pestañas de la hoja

Podemos obtener información de las pestañas de la hoja.

from openpyxl import load_workbook

# leemos el fichero
libro = load_workbook('fichero.xlsx')

# obtenemos la pestaña/hoja activa (nada mas abrir es la primera)
hoja = libro.active

# listamos pestañas del libro
print(libro.sheetnames)
['hoja 1', 'hoja 2']

# obtenemos la pestaña/hoja "nombre_pestaña"
# hoja = libro['nombre_pestaña']

Acceder a celdas

Podemos acceder a los valores de una celda de diferente formas, indicando la coordenada con letra (columna) y fila (número), por ejemplo (B1) o por separado indicando fila y columna (ambos numéricos, siendo la A la columna 1):

valores de las celdas (.value)

Para ver el contenido de una celda usaremos el método .value:

from openpyxl import load_workbook

# leemos el fichero
libro = load_workbook('fichero.xlsx')

# primera pestaña o pestaña activa
hoja = libro.active

# mostramos la celda B1 = 1:2 = Fila 1 - Columna B (2)
print(hoja.cell(row=1, column=2).value)

# mostramos la celda B1 = 1:2 = Fila 1 - Columna B (2)
print(hoja['B1'].value)

Acceder a rangos de celdas

Podemos acceder a un grupo de celdas :

from openpyxl import load_workbook

# leemos el fichero
libro = load_workbook('fichero.xlsx')

# primera pestaña o pestaña activa
hoja = libro.active

# Obtenemos las celdas del rango A1:E6
rango1 = hoja['A1:E6']

# obtenemos la columna B entera
colb = hoja['B']

# obtenemos la fila 5 entera
fila5 = hoja['5']

Añadir valores a celdas

Podemos añadir valores a cada celdas:

from openpyxl import load_workbook

# leemos el fichero
libro = load_workbook('fichero.xlsx')

# primera pestaña o pestaña activa
hoja = libro.active

# asignamos a A1 un valor
hoja['A1'] = "valor celda A1"

Podemos añadir los valores de un alista al final de la última fila con datos:

from openpyxl import load_workbook

# leemos el fichero
libro = load_workbook('fichero.xlsx')

# primera pestaña o pestaña activa
hoja = libro.active

# asignamos a la última fila (sin datos) la lista
hoja.append(['col1', 2, 3232, 'col4', 'columna 5'])

Saber si el valor de una celda es una fecha (.is_date)

El método .is_date nos indicará si el formato de la celda es fecha o no.

hoja['aa1'] = "10/04/2019"

hoja['aa1'].value
'10/04/2019'

hoja['aa1'].is_date
False

hoja['aa1'] = datetime.datetime(2010, 7, 21)

hoja['aa1'].value
datetime.datetime(2010, 7, 21, 0, 0)

hoja['aa1'].is_date
True

Obtener el formato de una celda (.number_format)

El método .number_format nos devuelve el forma de la celda.

hoja['a1'] = "10/04/2019"

hoja['a1'].number_format
'General'

hoja['a1'] = datetime.datetime(2010, 7, 21)

hoja['a1'].number_format
'yyyy-mm-dd h:mm:ss'

hoja['a1'] = '#,##0€'

hoja['a1'].number_format
'#,##0€'

Bloquear filas y/o columnas

Lo que en Excel se llama inmovilizar paneles aquí tenemos el método .freeze_panes:

# inmovilizar la primera fila (títulos)
hoja.freeze_panes = "A2"

# inmovilizar la primera fila (títulos) y la primera columna (descripciones)
hoja.freeze_panes = "B2"

Auto filtros

Podemos crear auto filtros lo que permite filtrar cada columna dependiendo de los valores que tenga, debemos indicar el rango que aplica el filtro:

# Auto filtro
hoja.auto_filter.ref = "A1:P200"

Fórmulas

Para insertar una fórmula::

# fórmula, almacena en B2 la Media del rango C2:C20
hoja["B2"] = "=AVERAGE(C2:C20)"

Estilos, colores, etc

También podemos añadir estilo y colores a las celdas:

Cambiar el estilo de las celdas (.font)

Podemos cambiar el estilo, color, forma y demás parámetros de una celda, especialmente cuando creamos una excel.

TITULO = Font(
  name='Calibri',
  size=24,
  bold=True,
  italic=False,
  vertAlign=None,
  underline='none',
  strike=False,
  color='ff0000')

hoja['a1'].font = TITULO

Tipo de letra, colore, borde

from openpyxl import load_workbook
from openpyxl.styles import Font, Color, Alignment, Border, Side, colors

# leemos el fichero
libro = load_workbook('fichero.xlsx')

# obtenemos la pestaña/hoja activa (nada mas abrir es la primera)
hoja = libro.active

# Letra negrita
negrita = Font(bold=True)

# tamaño de letra 20 y en color rojo
rojo20 = Font(color=colors.RED, size=20)

# texto centrado
centrado = Alignment(horizontal="center")

# creamos el borde doble
borde_doble = Side(border_style="double")
borde_cuadrado = Border(top=borde_doble,
                        right=borde_doble,
                        bottom=borde_doble,
                        left=borde_doble)

# cenda A2 negrita
hoja["A2"].font = negrita

# tamaño de letra 20 y en color rojo
hoja["A3"].font = rojo20

# texto centrado
hoja["A4"].alignment = centrado

# creamos el borde doble
hoja["A5"].border = borde_cuadrado

Formato condicional

Marca la líneas rojas cuando el valor de la columna B sea menos que 3

from openpyxl import load_workbook
from openpyxl.styles import PatternFill, colors
from openpyxl.styles.differential import DifferentialStyle
from openpyxl.formatting.rule import Rule

# leemos el fichero
libro = load_workbook('fichero.xlsx')

# obtenemos la pestaña/hoja activa (nada mas abrir es la primera)
hoja = libro.active

# fondo rojo
fondo_rojo = PatternFill(bgColor=colors.RED)
estilo = DifferentialStyle(fill=fondo_rojo)

# la regla es una expresión
regla = Rule(type="expression", dxf=estilo)

# la formula es que la columna B su valor sea menos que 3
regla.formula = ["$B1>3"]

# aplicamos el formato condicional al rango A1:P100
hoja.conditional_formatting.add("A1:P100", regla)

libro.save(filename="fichero_formato_condicional.xlsx")

Marca la colunma en escala de colores según el valor de la columna B

from openpyxl import load_workbook
from openpyxl.styles import PatternFill, colors
from openpyxl.styles.differential import DifferentialStyle
from openpyxl.formatting.rule import Rule
from openpyxl.formatting.rule import ColorScaleRule

# leemos el fichero
libro = load_workbook('fichero.xlsx')

# obtenemos la pestaña/hoja activa (nada mas abrir es la primera)
hoja = libro.active

# creamos escala de colores desde rojo el valor mínimo a verde el máximo
escala_color = ColorScaleRule(start_type="min",
                              start_color=colors.RED,
                              end_type="max",
                              end_color=colors.GREEN)

# aplicamos el formato condicional al rango A1:P100
hoja.conditional_formatting.add("A1:P100", eacala_color)

libro.save(filename="fichero_escala_colores.xlsx")

En la escala de color podemos añadir un color intermedio para que el rango de colores sea más extenso:

# creamos escala de colores desde rojo el valor mínimo (1), amarillo medio (3) a verde el máximo (5)
# aquí debemos especificar los valores mínimo, medio y máximo
escala_color = ColorScaleRule(start_type="num",
                              start_value=1,
                              start_color=colors.RED,
                              mid_type="num",
                              mid_value=3,
                              mid_color=colors.YELLOW,
                              end_type="num",
                              end_value=5,
                              end_color=colors.GREEN)

En la escala de color podemos poner una barra de color que queda más vistoso:

# creamos escala de colores desde rojo el valor mínimo (1) a verde el máximo (5)
# aquí debemos especificar los valores mínimo y máximo
escala_color = DataBarRule(start_type="num",
                          start_value=1,
                          end_type="num",
                          end_value="5",
                          color=colors.GREEN)

---

Más información en: openpyxl