PyXLL is a tool that bridges the gap between Microsoft Excel and Python. It lets you seamlessly integrate Python code and functionality into Excel spreadsheets. With PyXLL, Excel becomes a platform for leveraging Python’s libraries and capabilities.
PyXLL serves as an Excel add-in. you’re able to use it to write Python functions and macros directly in Excel’s VBA environment. PyXLL then acts as the interpreter and runs the code within Excel cells, opening up many possibilities. Some of these include automating complex tasks, advanced data analysis, and data visualization.
An Overview of PyXLL
PyXLL works by running a Python interpreter within the Excel process. This gives your Python code, running in PyXLL, direct access to Excel data and objects. The tool is written in C++ and it uses the same underlying technology as Excel. This means that Python code running in PyXLL is typically much faster thanExcel VBA code.
Installation and Setup
To install PyXLL, head to thePyXLL websiteand download the add-in. Make sure the Python version and the Excel version you choose match those installed in your system. PyXLL is only available for the Windows version of Excel.
When the download is complete, open a command prompt and run this command:
You need tohave Pip installed in your systemfor the above command to run. Then use the PyXLL package to install the PyXLL add-in:
The installer will ask whether you have downloaded the add-in. Enter yes and then provide the path to the zip file containing the add-in. Then follow the on-screen instructions to complete the installation.
Getting Started With PyXLL
Once you’ve installed the plugin, launch Excel. Before it launches, a prompt will pop up asking you toStart TrialorBuy Now. The trial version will expire after thirty days and you’ll then need to purchase a license to continue using PyXLL.
Click on theStart Trialbutton. This will launch Excel with the installed add-in.
On thePyXLL Example Tab, click on theAbout PyXLLbutton. This will show you the path in which you installed the add-in, along with paths to the configuration and log files.
The path containing the configuration file is important as you will need to edit that file later, so make a note of it.
Exposing Python Functions to Excel
To expose a Python function to Excel as a user-defined function (UDF), use the@xl_funcdecorator. This decorator instructs PyXLL to register the function with Excel, making it available to users.
For example, to expose a Pythonfibonacci()function to Excel as a UDF, you can use the@xl_funcdecorator as follows:
Save this code with a .py extension and take note of the path of the folder in which you save the file.
Now, open the PyXLL configuration file in an editor and scroll down to a line beginning “pythonpath”. This setting is typically a list of folders that PyXLL will search for Python modules. Add the path to the folder that contains the Fibonacci function source code.
Then scroll down to “modules” and add your module. For example, if you saved your file asfibonacci.py, add the name"fibonacci"to the list:
This will expose the module functions that use the@xl_funcdecorator to Excel. Then go back to Excel and, on thePyXLL Example Tab, click on theReload PyXLLbutton for the changes in the configuration file to sync. You can then call the Pythonfibonaccifunction as you would any other Excel formula.
you’re able to create as many functions as you need and expose them to Excel in the same way.
Passing Data Between Excel and Python
PyXLL supports the use of external Python libraries, like Pandas. It lets you pass data from these libraries to Python and vice versa. For example, you canuse Pandas to create a random dataframeand pass it to Excel. Make sure Pandas is installed in your system, then try this code out:
You should follow the same process to expose this module and its functions to Excel. Then try calling therandom_dataframefunction as you would other Excel formula:
you may change the number of rows and columns as you like.
You can pass your predefined dataframes to Excel the same way. It is also possible toimport Excel data into the Python script using Pandas.
Limitations of PyXLL
Should You Still Use Excel Functions?
It depends on what you want to accomplish. It always makes sense to use native Excel functions when they are available. But, for more complex tasks, that Excel’s built-in functions cannot handle, PyXLL is an excellent solution.
The Pandas library is a perfect complement for PyXLL with its analytic capabilities and strong support for data processing.
