Trabajar en Excel con Python

Para trabajar en Excel con Python se instala la biblioteca de terceros OpenPyXL, estas notas versan sobre la versión 2.6.2: pip install –user -U openpyxl==2.6.2.

se puede encontrar la documentación completa en la web del proyecto.
Una vez instalado, para trabajar conla biblioteca:

 import openpyxl

Para trabajar con un documento Excel (denominado “libro”), hay que abrirlo y asignarlo a una variable, como se hacía con los archivos, con el método load_workbook():

wb = openpyxl.load_workbook('libro.xlsx')

Cada libro de Excel está compuesto por “hojas” o sheets, que a su vez están divididas en cuadrículas numeradas alfabéticamente en columnas y numéricamente en filas.

Los libros pueden tratarse como diccionarios, cuyas claves son los títulos de las hojas. La hoja en la que trabajemos debe obtenerse de este modo.

hoja = wb['Hoja1']
hoja.title    # 'Hoja1'

Con el método title() obtenemos el nombre de la hoja.
Podemos saber qué hoja está activa con el atributo libro.active.

Del mismo modo, las hojas pueden ser tratadas como diccionarios, en los que las claves son las coordenadas de las celdas.

import openpyxl
wb = openpyxl.load_workbook('ejemplo.xlsx')
sheet = wb['Hoja1']
sheet['A1']         # <Cell 'Hoja1'.A1>
sheet['A1'].value   # Devuelve el contenido de la Celda
c = sheet['A1']     # Se asigna la celda a una variable
c.value             # Con la que también se puede trabajar sin tener que estar indicando constantemente sus coordenadas
f'La celda con coordenadas: Fila {c.row}, Columna {c.column}, tiene el valor: {c.value}'
f'La celda con coordenadas: Fila {c.coordinate}, tiene el valor: {c.value}'

Se pueden ver los siguientes atributos de una celda: value, row, column y coordinate.

También podemos referirnos a una celda a través de sus coordenadas con la función cell():

hoja.cell(row=1, column=2)
hoja.cell(row=1, column=2).value

Con la función anterior y un bucle for con range() podemos recorrer un conjunto de celdas:

for i in range(1, 8, 2):
    print(i, hoja.cell(row=i, column=2).value)

atributos de la hoja sheet que pueden ser interesantes:

• sheet.max_row
• sheet.max_column

Para ello hay que importar las bibliotecas get_column_letter y column_index_from_string de openpyxl.utils.

from openpyxl.utils import get_column_letter, column_index_from_string

• get_column_letter(Número de columna) Devuelve la letra de la columna.
• column_index_from_string(letra_de_la_columna) Devuelve el número de la columna.

Es posible extraer (split) filas, columnas, o áreas de una hoja, por ejemplo a una tupla, con otras tuplas como elementos, para despues recorrerlas con un bucle for:

import openpyxl
hc = openpyxl.load_workbook('ejemplo.xlsx')
hoja = hc['Hoja1']
tuple(hoja['A1':'C3']) # Devuelve todas las celdas en el área de A1 a C3.
'''((<Cell 'Sheet1'.A1>, <Cell 'Sheet1'.B1>, <Cell 'Sheet1'.C1>), (<Cell
   'Sheet1'.A2>, <Cell 'Sheet1'.B2>, <Cell 'Sheet1'.C2>), (<Cell 'Sheet1'.A3>,
   <Cell 'Sheet1'.B3>, <Cell 'Sheet1'.C3>))'''
 
for fila_de_celdas in sheet['A1':'C3']:  # Recorrerá el área indicada fila a fila (devuelve una fila en cada iteración)
    for celda in fila_de_celdas:         # Recorre la fila (tupla), elemento a elemento (celdas) en cada iteración
        print(celda.coordinate, celda.value)  # Muestra coordenada y calor de cada celda
    print('--- END OF ROW ---')               # Muestra que se ha llegado al fin de una fila

Del mismo modo, podemos extraer una fila o columna, por ejemplo, en una lista:

import openpyxl
hc = openpyxl.load_workbook('ejemplo.xlsx')
hoja = hc.active
list(hoja.columns)[1]    # Extraemos en una lista las celdas de la 2ª columna
'''(<Cell 'Sheet1'.B1>, <Cell 'Sheet1'.B2>, <Cell 'Sheet1'.B3>, <Cell 'Sheet1'.
B4>, <Cell 'Sheet1'.B5>, <Cell 'Sheet1'.B6>, <Cell 'Sheet1'.B7>)'''
 
for celda in list(hoja.columns)[1]:  # Recorremos las celdas de la 2ª columna 
    print(celda.value)               # e imprimimos sus valores

Como hemos visto hasta ahora, la única forma de extraer valores, es con el atributo value de una celda (objeto tipo cell). Vamos a ver otra forma de extraer valores en masa.

Para extraer valores, usamos la propiedad values de la hoja, de modo que podemos por ejemplo asignar a una “lista de listas” todos los valores de la hoja, o recorrer con un for una hoja Excel accediendo directamente a sus valores:

datos = list(hoja.values)

for fila in hoja.values:
    for valor in fila:
        print(valor)

Para obtener los valores de filas o columnas, se pueden usar los métodos iter_rows() y iter_cols() respectivamente, con el atributo values_only=True.

for row in ws.iter_rows(min_row=1, max_col=3, max_row=2, values_only=True):
    print(row)

Los documentos deben abrirse, y las hojas seleccionarse y activarse, al igual que en la lectura de datos, para poder trabajar con ellas.

import openpyxl
hc = openpyxl.Workbook() # Crea una hoja de cálculo en blanco
hc.sheetnames            # ['Hoja']
hoja = hc.active
hoja.title               # 'Hoja'
sheet.title = 'Spam Bacon Eggs Sheet' # Cambiamos el nombre de la hoja
hc.sheetnames            # ['Spam Bacon Eggs Sheet']

Si no especificamos un nombre de hoja de cálculo, se creará un libro en blanco. Se puede guardar con el método hc.save(), al que se le puede pasar como argumento el nombre de archivo, que si difiere del nombre del documento abierto, se guardará con ese otro nombre.

Para crear hojas se usa el método libro.create_sheet() y devuelve un objeto tipo Worksheet con nombre Sheetn, que representa la hoja creada. Admite algunos argumentos:

• index=n n indica la posición con las demás hojas, empezando desde 0.
• title='Hoja_n' Define el título de la hoja.

Para eliminar una hoja se usa el operador del: del libro['nombre_hoja']

Escribir valores en una celda es tan sencillo, como escribir en un diccionario:

import openpyxl
wb = openpyxl.Workbook()
sheet = wb['Sheet']
sheet['A1'] = 'Hello, world!' # Edit the cell's value.
sheet['A1'].value    # 'Hello, world!'

Para fijar las fuentes de las celdas debemos importar la librería adecuada:

from openpyxl.styles import Font

Para definir un tipo de fuente se utiliza la función Font(), que devuelve un objeto de tipo Font que puede ser asignado a una celda.

Font admite los siguientes argumentos:

• name String, nombre de la fuente, como 'Calibri', 'Times New Roman', etc.
• size Integer, los puntos que definen el tamaño.
• bold Boolean, si es negrita.
• italic Boolean, si es cursiva.

Para asignar el tipo de celda, se asigna la variable tipo Font al atributo .font de la celda.

Es tan sencillo como escribir la fórmua como una cadena de texto en la celda correspondiente. Sin embargo, las fórmulas de Excel pueden llegar a ser muy complejas, y puede ser más sencillo usar Python,

Los objetos tipo hoja Worksheet disponen de los siguientes atributos para definir la altura de filas y ancho de columnas:

• hoja.row_dimensions[nºFila].height = tamaño en puntos del alto de fila. Comprendido entre 0 y 409 (el normal es 12.75).
• hoja.column_dimensions['Letra_columna'].width = tamaño en caracteres de tamaño 11 puntos del ancho de la columna. Comprendido entre 0 y 255 (el normal es 8.43).

Cualquiera que tenga tamaño 0, se oculta.

Consiste en la fusión de varias celdas en una sola, o en la división de una celda en más.

Para combinar celdas se llama al método hoja.merge_cells('CeldaOrigen:CeldaFin').

Para “descombinar” celdas se llama al método hoja.unmerge_cells('CeldaOrigen:CeldaFin'). Las Celdas indicadas vuelven a mostrarse individuales.

Las hojas tienen el atributo nombreHoja.freeze_panes, en el cual se especifica la celda que servirá de vértice para la inmovilización de paneles.

• Si su valor es 'A1' o None, no habrá celdas inmovilizadas.
• Si su valor es cualquier otra celda de la primera fila, las celdas inmovilizadas serán las columnas que queden a la izquierda.
• En caso contrario, las celdas inmovilizadas serán las filas que queden por encima, y las columas situadas a su izquierda.

Procespo para crear gráficas:

1. Crear un objeto de tipo Reference a partir de una selección rectangular de celdas, donde se encuentran los datos de la gráfica. El objeto Reference se crea con el método openpyxl.chart.Reference(), a la que se le pasan 3 argumentos:
   1. La hoja en la que estamos trabajando en un dato de tipo Worksheet.
   2. Una tupla de dos enteros que definenla celda superior izquierda, empezando en 1. Pueden cambiarse definiendo los argumentos opcionales min_col y min_row.
   3. Una tupla de dos enteros que definenla celda inferior derecha, empezando en 1. Pueden cambiarse definiendo los argumentos opcionales max_col y max_row.
2. Crear un objeto de tipo Series pasando el objeto Reference, con la función seriesObj = openpyxl.chart.Series(refObj, title='First series')
3. Crear un objeto tipo Chart con la función: chartObj = openpyxl.chart.BarChart(). A este objeto se le puede añadir un título con el atributo .title.
4. Añadir al objeto Chart los datos, en el objeto Series: chartObj.append(seriesObj).
5. Añadir el gráfico a la hoja definiendo la celda correspondiente con la esquina superior izquiera, usando la función: sheet.add_chart(chartObj, 'C5').

No olvidar guardar la hoja.