====== 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 [[https://openpyxl.readthedocs.org/|la web del proyecto]].\\ Una vez instalado, para trabajar conla biblioteca: import openpyxl ===== Lectura de documentos EXCEL ===== 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.\\ ==== Obtener hojas de libros ==== 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**. ==== Obtener celdas de hojas ==== 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'] # 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** ==== Conversión entre letras de columnas y números ==== 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. ==== Extraer filas y columnas de la hoja ==== 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. '''((, , ), (, , ), (, , ))''' 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 '''(, , , , , , )''' for celda in list(hoja.columns)[1]: # Recorremos las celdas de la 2ª columna print(celda.value) # e imprimimos sus valores ==== Extraer 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) ===== Escritura en documentos EXCEL ===== ==== Creación y guardado de documentos EXCEL ==== 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. ==== Creación y borrado de hojas ==== 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 celdas ==== 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!' ===== Fuentes de celdas ===== 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. ===== Fórmulas ===== 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, ===== Ajuste de filas y columnas ===== ==== Altura de fila y ancho de columna ==== 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. ==== Combinación de celdas ==== 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. ==== Inmovilizando celdas ==== 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. ===== Gráficas ===== Procespo para crear gráficas: - 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: - La hoja en la que estamos trabajando en un dato de tipo //Worksheet//. - Una tupla de dos enteros que definenla celda superior izquierda, empezando en 1. Pueden cambiarse definiendo los argumentos opcionales min_col y min_row. - Una tupla de dos enteros que definenla celda inferior derecha, empezando en 1. Pueden cambiarse definiendo los argumentos opcionales max_col y max_row. - Crear un objeto de tipo //Series// pasando el objeto //Reference//, con la función seriesObj = **openpyxl.chart.Series(**refObj**,** title='First series'**)** - 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**. - Añadir al objeto //Chart// los datos, en el objeto //Series//: **chartObj.append(seriesObj)**. - 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.