How to seamlessly integrate Python into Excel using PyXLL

How to seamlessly integrate Python into Excel using PyXLL Picture 1

PyXLL is a tool that bridges the gap between Microsoft Excel and Python. It allows you to seamlessly integrate Python code and functions into Excel spreadsheets. With PyXLL, Excel becomes the foundation for enhancing Python's libraries and capabilities.

PyXLL works as an Excel add-in. You can use it to write Python functions & macros directly into Excel's VBA environment. PyXLL then acts as the compiler and runs the code in Excel cells, opening up many possibilities. Some of them include automating complex tasks, advanced data analytics, and data visualization.

Overview of PyXLL

PyXLL works by running the Python interpreter within the Excel process. This makes your Python code run in PyXLL, with direct access to Excel data and objects. This tool is written in C++ and it uses the same basic technology as Excel. That means Python code running in PyXLL is often faster than Excel VBA code.

Installation and setup

To install PyXLL, go to the PyXLL website and download the add-in. Make sure the Python and Excel versions you choose match what's installed on your system. PyXLL is only available for the Windows version of Excel.

How to seamlessly integrate Python into Excel using PyXLL Picture 2

Once the download is complete, open a command prompt and run:

pip install pyxll

You need to install pip on your system for the above command to run. Then, use the PyXLL package to install this add-in:

pyxll install

This installer will ask if you have downloaded this add-in or not. Click Yes, then provide the path to the zip file containing the add-in. Follow the on-screen instructions to complete the installation.

Get started with PyXLL

After installing this plugin, open Excel. Before it opens, a window appears, prompting you to Start Trial or Buy Now . The trial version will expire after 30 days. After that, you need to purchase a license to continue using PyXLL.

How to seamlessly integrate Python into Excel using PyXLL Picture 3

Click the Start Trial button . This will open Excel with the add-in installed. On the PyXLL Example tab, click the About PyXLL button . This action will show you the add-in installed path, along with paths to configuration and log files.

How to seamlessly integrate Python into Excel using PyXLL Picture 4

The path containing the configuration file is important because you need to edit it later, so write it down.

Shows Python functions for Excel

To expose a Python function to Excel, as a user-defined function (UDF), use the @xl_func decorator. This decorator instructs PyXLL to register the function with Excel, making it available to all users.

For example, to display the Python fibonacci() function in Excel as a UDF, you can use the @xl_func decorator as follows:

from pyxll import xl_func @xl_func def fibonacci(n): """ This is a Python function that calculates the Fibonacci sequence. """ if n < 0: raise ValueError("n must be non-negative") elif n == 0 or n == 1: return n else: return fibonacci(n - 1) + fibonacci(n - 2)

Save this code with the .py extension and write down the directory path where you save the file.

Now open the PyXLL configuration file in the editor and scroll down to the line starting with 'pythonpath'. This setting is typically a list of directories where PyXLL will search for Python modules. Add the path to the directory containing the Fibonacci function source code.

How to seamlessly integrate Python into Excel using PyXLL Picture 5

Then scroll down under 'modules' and add this module. For example, if you saved the file fibonacci.py, put the name ' fibonacci ' in the list:

How to seamlessly integrate Python into Excel using PyXLL Picture 6

This action will display module functions using the @xl_func decorator in Excel. Then, go back to Excel and on the PyXLL Example tab, click the Reload PyXLL button for the changes in the configuration file to sync. You can then call the fibonacci function like any other Excel formula.

How to seamlessly integrate Python into Excel using PyXLL Picture 7

You can create as many functions as needed and display them in Excel in the same way.

Transfer data between Excel and Python

PyXLL supports the use of external Python libraries such as Pandas. It allows you to transfer data from these libraries to Python and vice versa. For example, you can use Pandas to generate a random data frame and pass it to Excel. Make sure Pandas is installed on the system, then try this code:

from pyxll import xl_func import pandas as pd import numpy as np @xl_func("int rows, int columns: dataframe  ", auto_resize=True) def random_dataframe(rows, columns): data = np.random.rand(rows, columns) column_names = [chr(ord('A') + x) for x in range(columns)] return pd.DataFrame(data, columns=column_names) 

You will follow the same process to display this module and its functions in Excel. Then, call the random_dataframe function like other Excel formulas:

=random_dataframe(10,5)

You can change the number of rows and columns as desired.

How to seamlessly integrate Python into Excel using PyXLL Picture 8

Above is what you need to know about how to seamlessly integrate Python into Excel using PyXLL. Hope the article is useful to you.

Kareem Winters

Update 16 October 2023