Archivo de la categoría: Python

Actualizar las conexiones de datos de una serie de hojas Excel con Python

Posted on por
Responder

Si tenemos una serie de hojas Excel en las que se han incorporado conexiones a bases de datos, y queremos que se actualice el contenido de las mismas para que aparezca reflejada la última información cuando se aceda a ellas, podemos hacerlo de varias maneras: podemos, por ejemplo, definir esas conexiones de forma que se actualicen cuando se abra la hoja de cálculo, pero si la actualización es lenta porque se accede a muchas tablas o se actualiza mucho contenido, provoca que haya que esperar un buen rato desde que se abre la hoja hasta que se puede empezar a trabajar con ella; podemos también ir entrando en cada una y pulsando en «Actualizar datos», pero esto puede llegar a ser tedioso si son muchas hojas las que hay que actualizar; otra manera sería con macros o VB, pero las primeras son una tecnología a abandonar, y puestos a hacerlo por programa, a mí me gusta más en Python, utilizando la función que muestro a continuación, con comentarios que explican cada uno de los pasos:

# Importamos las librerías necesarias para la ejecución
import os
import win32com.client

# Especificamos el directorio que contiene los archivos Excel
ruta_directorio = "C:\Mis Programas\"

#
# Función para la actualización automática de las conexiones de datos
#
def actualizar_conexiones(directorio):

    # Creamos una instancia de la aplicación Excel
    excel_app = win32com.client.Dispatch("Excel.Application")

    # Hacemos la aplicación invisible para el usuario
    excel_app.Visible = False

    # Recorremos todos los archivos en el directorio especificado
    for filename in os.listdir(directorio):
        # Abrir solo hojas de cálculo, evitando los archivos ocultos
        if (filename.endswith(".xlsx") or filename.endswith(".xlsm")) and not filename.startswith("~"):
            file_path = os.path.join(directorio, filename)
            print(f"Actualizando conexiones de datos en: {file_path}")

            # Creamos un libro excel en memoria con la ruta del archivo
            workbook = excel_app.Workbooks.Open(file_path)

            try:
                # Recorremos todas las conexiones de datos del libro
                for connection in workbook.Connections:
                    print(f"Habilitando contenido: {connection.Name}")
                    connection.Refresh()
                    # Para hacer esperar la ejecución hasta que termine
                    # la actualización de datos:
                    excel_app.CalculateUntilAsyncQueriesDone()
                    # Para evitar que se abran ventanas de diálogo que 
                    # pararían el proceso:
                    excel_app.DisplayAlerts = False

            except Exception as e:
                print(f"Error al actualizar la conexión")

            finally:
                # Guardamos y cerramos el libro
                workbook.Save()
                workbook.Close()

    # Cerramos la aplicación Excel 
    # Ojo, no es lo mismo que cerrar el libro excel creado
    excel_app.Quit()

    print("Actualización de conexiones de datos completada.")


# Llamada a la función para actualizar las conexiones de datos
actualizar_conexiones(ruta_directorio)

Obtener con Python el número que corresponde a la columna de una hoja de cálculo

Posted on por
Responder

Cuando trabajamos con hojas de cálculo a veces es necesario conocer qué número de columna se corresponde con la letra de la misma, es decir, qué posición de columna (primera, segunda, etc.) se corresponde con cada columna en letra (A, B, etc.). Tanto en Excel como en LibreOffice tenemos la función COLUMNA(«..») que nos devuelve dicho dato, pero en ocasiones necesitamos conocerlo para acceder por programa a un dato determinado dentro de una hoja de cálculo de grandes dimensiones, que hace difícil calcular mentalmente dicha información.

Para convertir la letra a su número correspondiente podemos hacerlo de la misma manera que mentalmente damos un valor numérico a una cifra de varios dígitos. Me explico: cuando vemos un número, por ejemplo 325, el valor que le damos mentalmente es 3 * 102 + 2 * 101 + 5 * 100 porque diez es la base numérica de nuestro sistema decimal.

Con las letras de la hoja de cálculo podemos hacer lo mismo, teniendo en cuenta que la base de nuestro sistema «numérico» son las letras de la A a la Z, un conjunto de 26 dígitos, es decir, es como si trabajáramos en base 26. De esta forma, el valor «numérico» de una «cifra» como «XDR» sería 24 * 263 + 4 * 261 + 18 * 260 porque X es la letra vigesimocuarta, D es la cuarta letra y R la decimoctava de un total de 26 letras.

Podemos tener ese cálculo guardado en una función con el lenguaje de programación que usemos habitualmente, por ejemplo en Python sería:

def num_from_col(col):
    letras = "ABCDEFGHIJKLMNOPQRSTUVWXYZ"
    rpta = n = 0
    columna_aux = col[::-1].upper()
    for letra in columna_aux:
        valor_letra = letras.index(letra) + 1
        rpta += valor_letra * len(letras) ** n
        n += 1
    return rpta

Para hacer el cálculo hemos dado la vuelta a la expresión a evaluar para que su posición en la cadena se corresponda con el índice al que hay que elevar la base. También hemos pasado a mayúsculas el parámetro recibido para que se corresponda con la lista de letras que usamos de base. Sin embargo, a esta función le falta cosas como el control de excepciones, la validación del dato de entrada, etc. pero la dejo así como ejemplo básico para poder adaptarlo cuando se necesite.

Cómo crear un libro Excel a partir de otro en Python conservando las imágenes y el texto enriquecido

Posted on por
Responder

Leer y grabar un libro Excel con Python es muy sencillo utilizando la librería openpyxl, la cual permite hacer multitud de operaciones con archivos Excel y además está muy bien documentada.

Sin embargo, si queremos que al crear un libro a partir de otro que nos sirve de plantilla se mantengan tanto las imágenes como el texto enriquecido, es necesario tener en cuenta lo siguiente:

Para que toda la funcionalidad de openpyxl esté disponible, tenemos que instalar también los paquetes pillow y lxml en el entorno virtual en el que estemos ejecutando el programa. La librería lxml es importante sobre todo para que cuando grabemos el contenido de una celda, si el texto de ésta tiene diferentes formatos (por ejemplo parte es negrita, parte es cursiva o aparecen superíndices) dicho texto se mantenga tal cual está en el original. Sin esta librería se aplicará a toda la celda un único formato de texto.

Otra cosa que hay que tener en cuenta es que al abrir el archivo para leerlo en el código, tenemos que incluir explícitamente la opción rich_text=True, porque por defecto viene a False.

from openpyxl import load_workbook
wb = load_workbook(filename=excel_base, rich_text=True)

La imágenes que existan en el archivo original se van a copiar al archivo destino solamente si en origen están como objeto de Office, si están incrustadas como imagen no se van a copiar. Para comprobarlo, la manera más fácil es abrir el archivo origen, hacer click derecho sobre la imagen y seleccionar «Modificar». Si al hacerlo nos aparece este mensaje:

Tenemos que pulsar en «Sí», de forma que la imagen se guarde como objeto de dibujo de Office. Guardamos el archivo y cuando lo copiemos ya aparecerán las imágenes en la copia.

Teniendo en cuenta estas consideraciones, podremos replicar libros Excel con Python sin perder nada del archivo original.