En el pasado existía la elección de usar ya sea Excel o Python Jupyter Notebooks, pero desde la introducción del paquete PyXLL-Jupyter ahora podemos usar ambos al mismo tiempo.
En este artículo vamos a mostrar cómo configurar Jupyter Notebooks para que corra adentro de Excel, compartir datos entre los dos e incluso llamar funciones de Python escritas en nuestro Jupyter Notebook desde un libro de cálculo de Excel.
Instalar PyXLL
Para empezar a poder ejecutar código de Python en Excel necesitamos el add-in PyXLL. Este nos permitirá integrar Python dentro de Excel y usar Python en lugar de VBA. Para instalar el add-in introduciremos «pip install pyxll» en la consola de Anaconda y después usaremos la herramienta de comandos de PyXLL para instalar el add-in de Excel:
pip install pyxll
pyxll installSi te cuesta entender PyXLL puedes darle un vistazo a la documentación en línea para usuarios nuevos para apoyarte. Toma en cuenta que PyXLL es un software de uso comercial; incluye una prueba gratuita de 30 días y requiere una licencia para ser usada después del periodo de evaluación.
Una vez que tengamos el add-in de PyXLL en Excel instalado tendremos que instalar el paquete PyXLL de Jupyter. Este paquete será lo que una a PyXLL y Jupyter para que podemos usar nuestros cuadernos de Jupyter dentro de Excel.
Para instalar el paquete de PyXLL Jupyter usaremos pip:
pip install pyxll-jupyterUna vez que el add-in de Excel y el paquete de Jupyter estén instalados abriremos Excel y veremos un nuevo botón en la pestaña PyXLL:
Si le damos click a este botón se abrirá el cuaderno de Jupyter en un panel lateral en nuestro libro de Excel. Este panel es parte de la interfaz de Excel y puede ser anclado o desanclado a otra parte si lo arrastramos.
En el panel de Jupyter podemos seleccionar un cuaderno existente o crear uno nuevo. Para crear un nuevo cuaderno seleccionaremos el botón «Nuevo» seguido por «Python 3».
Todo bien hasta ahora, pero puede que te estés preguntando cómo esto nos es útil. ¿Cómo es esto mejor que tener un cuaderno ejecutándose fuera de Excel?
Mejoras agregadas a Excel
Pues bien, ahora podemos usar Excel para trabajar con nuestros datos y usar Python para trabajar en el mismo conjunto de datos. Usa Excel como una herramienta interactiva para organizar y visualizar tus datos de manera fluida y sin interrupciones cambiando a Python para usar herramientas más sofisticadas.
Podemos usar un cuaderno de Jupyter como un bloc de notas para probar código de Python. Podemos escribir funciones de Excel enteramente en Python dentro del cuaderno de Jupyter y probarlo en tiempo real. Una vez que hayamos desarrollado una función reutilizable útil la añadiremos a nuestro proyecto de Python PyXLL. De esta forma, podremos usar la misma función cada vez que usemos Excel.
Para poder explorar más estos usos usos vamos a:
- Compartir datos entre Excel y Python usando nuestro cuaderno de Jupyter
- Escribir funciones de hoja de trabajo de Excel (UDFs) en nuestro cuaderno
- Hacer un script para usar Excel con Python en lugar de VBA OBTENIENDO DATOS DE EXCEL A PYTHON
Dado que PyXLL corre Python en el mismo proceso que Excel, acceder a datos de Excel en Python y llamar entre Python y Excel es fácil.
Para hacer esto lo más fácil posible, el paquete PyXLL-Jupyter viene con algunas funciones IPython «mágicas» para que usemos en nuestros cuadernos de Jupyter.
Notas antes de iniciar
La funciones %xl_get, %xl_set requiere el paquete «pywin32», el cual podemos instalar si ejecutamos lo siguiente:
pip install “pywin32==228”
Si salta un error «DLL Load Failed» puede que sea causado por un bug en la versión 300 del paquete pywin32. Si este es el caso entonces deberás instalar una versión anterior (228). Este bug ha sido arreglado en pywin32, pero aun no ha sido lanzado.
Moviendo datos de Excel a Python
Usaremos la función mágica «%xl_get» para obtener la selección actual de Excel en Python. Si ya tenemos una tabla en Excel entonces seleccionaremos la esquina superior izquierda (o todo el rango, según convenga) y escribiremos «%xl_get» en nuestro cuaderno de Jupyter y tendremos que cada tabla de Excel ahora es un ‘pandas DataFrame’.
La función mágica %xl_get tiene varias opciones:
- «-c» o «–cell». Pasa la dirección de la celda (o celdas) para obtener el valor de, por ejemplo, «%xl_get –cell A1:D5».
- «-t» o «..type». Especifica un tipo de datos para usar cuando se obtiene el valor, por ejemplo, «%xl_get –type numpy_array».
- «-x» o «–no-auto-resize». Solo obtiene los datos para el rango dado o seleccionado. No expande para incluir el rango de datos alrededor.
PyXLL tiene otras formas de interactuar con Excel para leer datos en Python. La función mágica «%xl_get» es solo un atajo que hace las cosas más fáciles, y ya que el cuaderno de Jupyter está corriendo en Excel, todos los demás métodos (como usar la clase XLCell, COM API de Excel o incluso xlwings aun están disponibles).
MOVIENDO DATOS EN PYTHON DE VUELTA A EXCEL
Transferir datos de vuelta funciona de la misma forma (o sea, de Python a Excel). Así hayamos usado Python para cargar un conjunto de datos y queramos transferirlo a nuestro libro de Excel, o bien si hemos manipulado un conjunto de datos de Excel y queremos los resultados de vuelta en Excel.
La función mágica «%xl_set» toma un objeto de Python y lo escribe a Excel. ¿Tienes un marco de datos «df» que quieras en Excel? Solo usa «%xl_set df» y será escrito en la selección actual de Excel.
Tal como %xl_get, %xl_set tiene un rango de opciones para controlar su comportamiento. Incluso podemos usar la función de formateo de celdas de PyXLL para aplicar formato automáticamente al mismo tiempo que escribimos los resultados a Excel.[?]
- «-c» o «–cell». Dirección de la celda o celdas para escribir el valor, por ejemplo: «%xl_set VALUE –cell A1».
- «-t» o «–type». Especificador del tipo de datos a usar cuando se escribe el valor a Excel, por ejemplo: «%xl_set VALUE –type dataframe».
- «-f» o «–formatter». Objeto formateador de celdas,[?] por ejemplo: «%xl_set VALUE –formatter DataFrameFormatter()». Más información sobre formateo de celdas.
- «-x» o «–no-auto-resize». No cambia de tamaño automáticamente el rango para encajar con los datos. Solo escribe valores a la selección actual o rango específicado
Tal como %xl_get, %xl_set es meramente un atajo y todas las demás formas de escribir de vuelta a Excel que puedas haber usado con PyXLL aun funcionarán en un cuaderno de Jupyter.
Usa ‘Python Plots’ (Matplotlib/Plotly, etc) en Excel
Uno de los mejores aspectos sobre trabajar con datos son los potentes paquetes de trazado que están disponibles. Ser capaz de trazar un pandas DataFrame con un simple «df.plot()» es vital.
PyXLL tiene integración con las principales librerías de trazado para que podamos sacarles el mayor provecho en Excel. Esto incluye matplotlib (que es usado por pandas), plotly, bokeh y altair.
Utiliza «%xl_plot» para dibujar cualquier gráfico de Python en Excel, luego pasa cualquier objeto de figura desde una de las librerías de trazado soportadas o usa la última figura pyplot. Usar trazados pandas funciona de maravilla (por ejemplo, «%xl_plot df.plot(kind=’scatter’)»).
La función mágica %xl_plot tiene algunas opciones para controlar el cómo funciona:
- «-n» o «–name». Nombre del objeto de imagen en Excel. Si usamos el nombre de una imagen que ya existe, la imagen será reemplazada.
- «-c» o «–cell». Dirección de la celda que se usa como la ubicación para la nueva imagen. Si la imagen ya existe esto no tendrá efecto.
- «-w» o «–width». Ancho de la imagen en Excel en puntos. Esto no tiene efecto si se actualiza una imagen ya existente.
- «-h» o «–height». Altura de la imagen en Excel en puntos. Esto no tiene efecto si se actualiza una imagen ya existente.
%xl_plot es un atajo para la función pyxll.plot..
Llama funciones de Python a Excel
En lugar de estar moviendo datos entre Excel y Jupyter y luego correr código en Python, podemos simplemente llamar funciones de Python directamente desde el libro de Excel. Uno de los principales casos de uso para PyXLL es escribir funciones personalizadas en libros de trabajo de Excel en Python. Esto es usado para construir modelos en Excel creados a partir de funciones de Python, los cuales pueden usar otros paquetes de herramientas de Python como pandas y scipy.
También podemos escrbir funciones de Excel en nuestro cuaderno de Jupyter. Esta es una gran forma de intentar ideas que tengamos sin necesidad de salir de Excel para ir a Python IDE. Puedes intentarlo por tu cuenta escribiendo una función simple y luego añadiendo el decorador «pyxll.xl_func» a nuestra función:
from pyxll import xl_func
@xl_func
def test_func(a, b, c):
# This function can be called from Excel!
return (a * b) + cLuego de haber introducido el código y correr la celda en Jupyter, esa función de Pyton estará disponible para ser llamada inmediatamente desde el libro de Excel.
No es solo para funciones simples. Podemos pasar rangos enteros de datos a nuestra función como pandas DataFrames y regresar un tipo de Python, incluyendo ‘numpy arrays’ y DataFrames. Podemos decirle a PyXLL qué tipos deberá esperar si le damos al decorador @xl_func una secuencia de firma.
Crea Scripts de Excel VBA en Python
¿Sabías que todo lo que puedes hacer en VBA también se puede hacer en Python? El Modelo de Objetos de Excel es lo que usamos cuando escribimos VBA, pero el mismo API está disponible en Python también.
En la documentación de PyXLL encontraremos detalles sobre cómo Python puede trabajar como un reemplazo para VBA y cómo es posible.
Dado que PyXLL corre Python dentro del proceso de Excel no hay ningún problema de desempeño cuando llamamos Python dentro de Excel. También es posible llamar a Excel desde un proceso externo de Python, pero esto es generalmente mucho más lento. Tener un cuaderno de Jupyter corriendo en Excel hace todo mucho más conveniente.
Utiliza la función xl_app de PyXLL para obtener el objeto «Excel.Application», el cual es el equivalente al objeto Application en VBA. Intenta algo como obtener la selección actual y cambiar el color interior de la celda. Una gran forma de averiguar cómo hacer algo con el Modelo de Objetos de Excel es grabar un macro de VBA y después traducir ese macro en Python.
from pyxll import xl_app xl = xl_app() xl.Selection.Interior.Color = 0xff00ff
Consideraciones finales
Python nos ofrece una poderosa alternativa a VBA. Con PyXLL podemos escribir add-ins completamente funcionales de Excel usando solamente Python. Excel es una gran herramienta para la computación interactiva, añadir Python y Jupyter lleva a Excel a un nuevo nivel pues el código escrito en cuadernos de Jupyter puede ser fácilmente refactorado en paquetes solitarios para crear kits de herramientas de Excel para mejorar y lograr libros e interfaces más intuitivas.
Cualquier usuario de Excel sin importar su nivel será capaz de tomar ventaja de las herramientas de Python usando PyXLL sin necesidad de tener conocimiento de Python.
Nos leemos abajo.
