A Guide To PyXLL-Jupyter Package For Excel Integration

27/07/2021

PyXLL is the brainchild of Tony Roberts.

Will Plastic Replace Silicone in Microprocessors?

Last year, PyXLL released its PyXLL-Jupyter plugin. The new extension combines the ease of use of Excel with the interactivity of Jupyter. Once installed, you will be able to plot your data in Jupyter and have the ability to share them with Excel seamlessly. It can also open on Jupyter Notebook in a browser as well as in an Excel task pane.

Excel comes with pivot tables, graphing tools, and a macro programming language known as Visual Basic for Applications (VBA). Jupyter Notebook, on the other hand, is an open-source web application that allows users to create and share documents that contain live code, visualisations, equations and narrative text. With the PyXLL-Jupyter package, you can use both together, side by side.

Register for our Free MasterClass>>

VBA is commonly used to automate Excel with macros, add new user-defined worksheet functions (UDFs) and react to Excel events. The same is achieved with Python, as it is much faster.

Play

00:00

-10:24

Mute

Settings

Enter fullscreen

Play

Founded in 2010, PyXLL is the brainchild of Tony Roberts. He started PyXLL after years of working with Python and Excel in the finance industry. PyXLL is one of the go-to tools for writing Excel add-ins in Python, especially for people working in data science roles.

xiwings, DataNitro, Pandas, NumPy, Anaconda, SciPy, PySpark and DataForm, are some of the alternatives to PyXLL.

How to Install PyXLL-Jupyter?

Firstly, to run Python code in Excel, you need the PyXLL add-in. ‘PyXLL add-in’ lets you integrate Python into Excel and use Python instead of VBA. To set up ‘PyXLL Excel add-in (pip install pyxll) and then use the PyXLL command line tool to install the Excel add-in:

> pip install pyxll

> pyxll install

Beginners can check out PyXLL’s online documentation here.

Once you have successfully installed the PyXLL Excell add-in, the next step is to install the pyxll-jupyter package. This package essentially glues PyXLL and Jupyter so that you can use Jupyter Notebooks within Excel.

The PyXLL-Jupyter package is installed using pip:

> pip install pyxll-jupyter

Once you have installed both the PyXLL Excell add-in and the PyXLL-Jupyter package, you will see a new ‘Jupyter’ tab in the PyXLL dashboard, as highlighted in the image below.

(Source: PyXLL)

Upon clicking this button, Jupyter Notebook opens in a side panel in your Excel workbook. This new panel is part of the Excel Interface and can be dragged to different locations at your convenience.

In the Jupyter panel, you can select an existing notebook or create a new one. If you want to create a new notebook, select the ‘New’ tab followed by ‘Python 3,’ as shown in the image below.

Hands-On Workshop: Learn About oneAPI AI Analytics Toolkit

(Source: PyXLL)

Further, to upgrade to the latest version of PyXLL-Jupyter, you can run pip install –upgrade pyxll-jupyter.

PyXLL-Jupyter

Now that you have a complete Jupyter Notebook running inside of Excel, you can use Excel for working with your data and use Python to work on the same data. In other words, you can use Excel as an interactive playground for organising and visualising your data, effortlessly switching to Python for more sophisticated tools.

Also, you can use Jupyter Notebooks as a scratch-pad for trying out Python code and write Excel functions entirely in Python in a Jupyter notebook and test them out in real-time.

“Once you have developed a useful re-usable function, add it to your PyXLL Python project. That way, you can use the same function every time you see Excel,” wrote Tony Roberts, the founder of PyXLL.

The key highlight of PyXLL-Jupyter include:

Share data between Excel and Python using your Jupyter Notebook
Write Excel worksheet functions (UDFs) in your notebook
Script Excel with Python instead of VBA

Play

00:00

-17:42

Mute

Settings

Enter fullscreen

Play

Wrapping up

Python makes a powerful alternative to VBA. Using PyXLL, you can write fully featured Excel add-ins entirely in Python. At the same time, Excel is also a brilliant tool for interactive computation, and adding Python and Jyupter takes Excel to the next level.

Plus, code written in Jupyter Notebook is easily refactored into standalone Python packages to create Excel toolkits to power intuitive Python tools written using PyXLL — without needing any knowledge of Python. PyXLL-Jupyter just simplifies the entire experience by providing both tools under one umbrella.

What Do You Think?

Join Our Discord Server. Be part of an engaging online community. Join Here.

Subscribe to our Newsletter

Get the latest updates and relevant offers by sharing your email. AMIT RAJA NAIK

Amit Raja Naik is a senior writer at Analytics India Magazine, where he dives deep into the latest technology innovations. He is also a professional bass player.

ORIGINAL News