Excel worksheet functions, or UDFs (User Defined Functions), are the most intuitive way to call Python functions in Excel.
You will no doubt be familiar with calling worksheet functions in Excel already. If you have used SUM to sum a column of numbers then you've used a worksheet function!
With PyXLL, writing your own Excel worksheet function in Python is as simple as adding
PyXLL's xl_func decorator to your Python code.
from pyxll import xl_func
# Adding '@xl_func' to a Python function
# makes it callable from Excel.
@xl_func
def hello(name):
return "Hello, %s" % name
If you’ve not installed the PyXLL addin yet, see Installing PyXLL.
PyXLL user defined functions (UDFs) written in Python are exactly the same as any other Excel worksheet function. They are called from formulas in an Excel worksheet in the same way, and appear in Excel’s function wizard just like Excel’s native functions (see Function Documentation).
To tell the PyXLL add-in to expose a Python function so that we can call it from
Excel, all that is needed is to add the xl_func decorator to a
Python function:
from pyxll import xl_func
@xl_func
def hello(name):
return "Hello, %s" % name
This funciton takes just a single argument, name, which can be passed in when we
call the function from Excel.
PyXLL supports passing arguments and returning values of many different types, which is covered in detail in the next section.
Once you have saved that code you need to ensure the interpreter can find
it by modifying the following settings in your pyxll.cfg config file:
[PYXLL] / modules
The list of Python modules that PyXLL will import.
[PYTHON] / pythonpath
The list of folders that Python will look for modules in.
If you saved the above code into a new file called my_module.py in a folder C:\Users\pyxll\modules you
would add the Python module my_module to the modules list, and C:\Users\pyxll\modules to the
pythonpath.
Note that Python module file names end in .py, but the Python module names do not.
[PYXLL]
;
; Make sure that PyXLL imports the module when loaded.
;
; We use the module name here, not the file name,
; and so the ".py" file extension is omitted.
;
modules = my_module
[PYTHON]
;
; Ensure that PyXLL can find the module.
; Multiple modules can come from a single directory.
;
pythonpath = C:\Users\pyxll\modules
After making these changes reload the PyXLL addin, or restart Excel. You
can use the PyXLL function you have just added in formulas in any Excel
worksheet, because the function was decorated with xl_func.
=hello("me")
Tip
If your function does not appear in Excel or you get an error message, check the PyXLL log file. By default, the log file will be in the logs folder next to the PyXLL add-in.
Worksheet functions can take simple values, as in the example above, or more complex arguments including Pandas DataFrames and Numpy arrays.
This is covered in detail in the next section.
