Installing with Enthought Canopy

Have Canopy 1.2 or lower?

You must upgrade to Canopy v1.3 for PyXLL to install correctly. You can upgrade Canopy under the Help menu.

Enthought Canopy provides an integrated PyXLL egg that you can install via the Package Manager. You must upgrade to Canopy v1.3 for PyXLL to install correctly. You can upgrade Canopy under the Help menu.

Launch the Package Manager from the Canopy Welcome Screen.

Select Available Packages and search for pyxll.

Select the pyxll package from the list and click the Install button to the right to have Canopy download and install PyXLL for you.

Installing with other Python Distributions

Configuration

Either way you’ve installed PyXLL, if you’ve followed the steps above you will now have the config file open.
Make the following changes:

Set the log path and file to wherever you want the log to go.

Change pythonpath[1] to where you’re going to put your Python modules (this may be the same as where you’ve extracted the archive to for now)

Set modules to the name of the Python module you will be putting your code in.

If you have a license key you should copy it to the license section (see the config section for more details). If you do not have a license key you may still use PyXLL for non-commercial or evaulation purposes, but you will see a PyXLL pop-up dialog each time you start Excel.

Writing some Python code

Now create the python module that you specified in the config file using a text editor (in my example, the file will be called my_xl_addin_module.py).

First of all import xl_func from the pyxll module. The pyxll module is compiled into the PyXLL addin so does not need to be included in the pythonpath. Write a simple function that takes a string and returns a string, as shown in the example file below.

The python function needs to be decorated using the xl_func decorator in order for the PyXLL addin to expose it to Excel. The decorator takes one mandatory argument and several option arguments (as described in the xl_func section). The mandatory argument is the function signature which should specify the argument types and names (names are optional) and the return type. The return type is optional, and if not specified the default return type var will be used. var is a special type that can be used to pass any of the standard types. For more information about types see Standard argument and return types.

Installing the Excel Addin

Now we’re ready to use that function in Excel! Start Excel and add the PyXLL addin. If you’re using Excel 97-2003 go to Tools -> Add-Ins -> Browse and locate pyxll.xll and add it. For later versions of Excel click the top left circle in Excel 2007 or File menu in Excel 2010 and 2013 and go to Options -> Add-Ins -> Manage Excel Addins and browse for the pyxll.xll file and add it.

Python DLL

PyXLL looks for the python dll in the same folder as itself first before trying to find it in the system path.

If at this stage you get an error saying that Python is not installed or the Python dll can’t be found, the most likely problem is that you don’t have the correct Python version installed on your PC. The Python version you have installed must match whatever version of PyXLL you downloaded (currently versions 2.3 to 3.3 are available). Install the correct version and try again. It’s important that python2x.dll (where x is whatever version of python you’re using) is in your system path (if you use one of the standard binary distributions of python it will already have been automatically installed somewhere in your system path).

Trying it out

You should now be able to enter the formula =hello("me") and see the calculated value “Hello, me”. If you look in the function browser in Excel you should see your function in the PyXLL category with your docstring as the help text for the function. Using other categories for your functions is covered in the xl_func section.

Using Eclipse and PyDev?

You can interactively debug Python code running in PyXLL with Eclipse and PyDev by attaching the PyDev debugger.
See this example for details.

If your function doesn’t work, check the log file, which you will find in the directory you specified in the config file. If you have made any errors in your python module you will see those in the log file. Correct those errors and select the ‘Reload PyXLL’ menu item from the PyXLL Addin menu. This will either be in the main menu bar if you’re using a version of Excel before 2007, or in the AddIns ribbon menu for 2007 and later versions.

Your system pythonpath must also be setup so PyXLL can find the standard modules. The pythonpath in the config is only for additional paths.

Configuration

Getting at the config from your code

The PyXLL config is available to your addin code at run-time via get_config.

If you add your own sections to the config file they will be ignored by PyXLL but accessible to your code via the config object.

The config file is used to control settings for PyXLL. It’s a plain text file that should be kept in the same folder as the PyXLL addin .xll file, and should be called the same as the addin but with the extension .cfg. In most cases this will simply be pyxll.cfg.

If you do not have a config file, or it’s not readable by PyXLL you will get an error when starting Excel or adding the addin and PyXLL will not work.

Paths used in the config file may be absolue or relative paths. Any relative paths should be relative to the config file.

The config file is broken down into several section, each of which is documented below.

PyXLL settings

Settings that determine the basic behavior of the PyXLL addin are put in the [PYXLL] config section:

[PYXLL]
pythonpath = semi-colon or new line delimited list of directories
modules = comma or new line delimited list of python modules
developer_mode = (optional) 1 or 0 indicating whether or not to use the developer mode
external_config = (optional) paths of additional config files to load
name = (optional) name of the addin visible in Excel
allow_abort = (optional) 1 or 0 to set the default value for the allow_abort kwarg

Tip

pythonpath and modules may span multiple lines.

The standard pythonpath is appended with whatever paths you specify in the pythonpath setting. This is useful so
you can tell PyXLL where to look for the python modules containing your addin code.

When PyXLL is started (or re-loaded) it will import all modules listed in the config file. It’s these modules that
expose worksheet functions and menu functions.

The developer_mode setting can be used to put PyXLL in a developer mode, which is useful when you’re developing
your addin. In addition to whatever menu items you have added, when the developer mode is used there will be an
additional menu item to reload the addin. This can be used to allow you to develop your python modules and test changes
without having to restart Excel. If unset, the default is to not use the developer mode.

The external_config setting may be used to reference another config file somewhere else. For example, if you want
to have the main pyxll.cfg installed on users’ local PCs but want to control the configuration via a shared file on the
network you can use this to reference that external config file. Multiple external config files can be used by
specifying them as a comma seperated list. Values in external config files override what’s in the parent config file,
apart from pythonpath, modules and external_config which get appended to.

The name setting is optional and only has an effect when a valid license for PyXLL is found. It is used to change
the name of the addin as it appears in Excel. When using this setting the addin in Excel is indistinguishable from any
other addin, and there is no reference to the fact it was written using PyXLL. If there are any menu items in the
default menu, that menu will take the name of the addin instead of the default ‘PyXLL’.

The allow_abort setting is optional and sets the default value for the allow_abort keyword argument to the
decorators xl_func, xl_macro and xl_menu. It should be set to 1 for True
or 0 for False. By default the allow_abort kwarg is False.

Logging

PyXLL redirects all stdout and stderr to a log file. All logging is done using the standard logging python module.

The [LOG] section of the config file determines where logging information is redirected to, and the verbosity of the information logged:

The items in the config file may include substitution values that will be replaced with other section values or the config defaults setup by PyXLL.

Config default

Explaination

pid

process id

date

current date

xlversion

Excel version

These may be used, for example, to create the log file name file=pyxll.%(date)s.%(pid)s.%(xlversion)s.log. For more information about variable substitution in the config please see ConfigParser.get.

The format string is used by the logging module to format any log messages. An example format string is "%(asctime)s-%(name)s-%(levelname)s-%(message)s". For more information about log formatting, please see the logging module documentation.

Environment Variables

For some python modules it can be helpful to set some environment variables before they are imported. Usually this would be done in the environment running the python script, but in Excel it’s more complicated as it would require either changing the global environment variables on each PC, or using a batch script to launch Excel.

For this reason, it’s possible to set environment variables in the [ENVIRONMENT] section of the config file:

[ENVIRONMENT]NAME=VALUE...

For each environement variable you would like set, add a line to the [ENVIRONMENT] section.

License key

If you have licensed PyXLL for commercial or evaluation use, you should put the license key in the [LICENSE] section of the config file.

If you do not have a license key, you may omit this section. Unlicensed versions of PyXLL will display a dialog box each time the addin is loaded which must be acknowledged before Excel will continue.

For convenience for users with site licenses it’s also possible to specify a license file instead of a license key. The license file is a text file containing the site license key that must be readable by all users of PyXLL. This simplifies the process of updating a site license as it only needs to be updated in one shared file, rather than every installed instance of PyXLL. In the event that both key and file are specified, key takes precedence.

The decorator xl_func tells PyXLL what arguments the function takes and (optionally) the return type so it can register it correctly in Excel. The argument text you see in Excel is taken from this signature, and the function help is taken from the function’s docstring.

And that’s it. There is no more code to write to get that function in Excel. There’s nothing to compile; you don’t even need to restart Excel to see it!

To see this new function in Excel, the PyXLL addin is loaded via the addins menu in Excel in the same way as any other addin.

The PyXLL addin automatically picks up any imported functions with the xl_func decorator. By default the functions are added to the PyXLL category in Excel, but that can be altered on a per function basis using the optional argument category to xl_func.

Here’s how this python function looks in Excel:

Exposing functions as UDFs

Python functions to be exposed as UDFs are decorated with the xl_func decorator, imported from the
pyxll module. The pyxll module is compiled into the pyxll.xll addin so it doesn’t have
to be on the pythonpath.

xl_func is a function that returns a decorator for exposing python functions to Excel.

Parameters:

signature (string) –

string specifying the argument types and, optionally, their names and the return type.

If the return type isn’t specified the var type is assumed. eg:

"intx,stringy:double" for a function that takes two arguments, x and y and returns a double.

"floatx" or "floatx:var" for a function that takes a float x and returns a variant type.

category (string) – String that sets the category in the Excel function wizard the exposed function will
appear under.

help_topic (string) – Path of the help file (.chm) that will be available from the function wizard in Excel.

thread_safe (boolean) – Indicates whether the function is thread-safe or not. If True the function
may be called from multiple threads in Excel 2007 or later

macro (boolean) – If True the function will be registered as a macro sheet equivalent function.
Macro sheet equivalent functions are less restricted in what they can do, and in
particular they can call Excel macro sheet functions such as xlfCaller.

allow_abort (boolean) – If True the function may be cancelled by the user pressing Esc. A KeyboardInterrupt
exception is raised when Esc is pressed. If not specified the behavior is determined
by the allow_abort setting in the config (see Configuration).

volatile (boolean) – if True the function will be registered as a volatile function, which means
it will be called everytime Excel recalculates regardless of whether any of the
parameters to the function have changed or not

disable_function_wizard_calc (boolean) – Don’t call from the Excel function wizard. This is useful for
functions that take a long time to complete that would otherwise
make the function wizard unresponsive

disable_replace_calc (boolean) – Set to True to stop the function being called from Excel’s find and replace
dialog.

Documenting functions

When a python function is exposed to Excel the docstring of that function is visible in Excel’s function wizard dialog.

Parameter documentation may also be provided help the user know how to call the function. The most convenient way to add parameter documentation is
to add it to the docstring as shown in the following example:

frompyxllimportxl_func@xl_func("float x, int n: float")defpy_round(x,n):""" Return a number to a given precision in decimal digits. x: floating point number to round n: number of decimal digits """returnround(x,n)

Here PyXLL will automatically detect that the last two lines of the docstring are parameter documentation. They will appear in the function wizard
as help strings for the parameters when selected. The first line will be used as the function description.

One or more of any of the characters :, - or = may be used to separate the parameter name from it’s description, and the Sphinx style
:paramx:description is also recognized.

Parameter documentation may also be added by passing a dictionary of parameter names to help strings to xl_func as the keyword
argument arg_descriptions if it is not desirable to add it to the docstring for any reason.

Standard argument and return types

Several standard types may be used in the signature specifed when exposing a Python UDF. It is also possible to pass arrays and custom types, which are discussed later.

Below is a list of the standard types. Any of these can be specified as an argument type or return type in a function signature. If a type passed from Excel or returned from Python is not (or cannot be converted to) the Python type in this list an error will be written to the log file and NaN will be returned to Excel if possible.

The var type

In addition to the standard types there is also the var type. This can be used when the argument or return type isn’t fixed. Using the strong types has the advantage that arguments passed from Excel will get coerced correctly. For example if your function takes an int you’ll always get an int and there’s no need to do type checking in your function. If you use a var, you may get a float if a number is passed to your function, and if the user passes a non-numeric value your function will still get called so you need to check the type and raise an exception yourself.

Using arrays

Ranges of cells can be passed from Excel to Python as a 2d array, represented in python as a list of lists.

Any type can be used as an array type by appending [], as shown in the following example:

frompyxllimportxl_func@xl_func("float[] array: float")defpy_sum(array):"""return the sum of a range of cells"""total=0.0# array is a list of lists of floatsforrowinarray:forcell_valueinrow:total+=cell_valuereturntotal

Tip: 1d arrays

If you want to pass rows and columns of data see Custom types and arrays in the example Custom Types.

Arrays can be used as return values as well. When returning an array remember that it has to be a list of lists. This means to return a row of data you would return [[1,2,3,4]], for example. To enter an array forumla in Excel you select the cells, enter the formula and then press Ctrl+Shift+Enter. Please refer to the Excel documentation for more information about array formula.

Any type can be used as an array type, but float[] requires the least marshalling between Excel and python and is therefore the fastest of the array types.

If you use the var type in your function signature then an array type will be used if you return a list of lists, or if the argument to your function is a range of data.

Using NumPy arrays

To be able to use numpy arrays you must have numpy installed and in your pythonpath.

You can use numpy 1d and 2d arrays as argument types to pass ranges of data into your function, and as return types for returing for array functions. Only up to 2d arrays are supported, as higher dimension arrays don’t fit well with how data is arranged in a spreadsheet.

The most common type of numpy array to use is a 2d array of floats, for which the type to use in the function signature is numpy_array. For 1d arrays, the types numpy_row and numpy_column may be used.

Types other than floating point arrays are supported too, and are listed below for numpy_array. The same applies to the 1d array types.

PyXLL type

Python type

numpy_array

numpy.array of float

numpy_array<float>

numpy.array of float

numpy_array<int>

numpy.array of int

numpy_array<bool>

numpy.array of bool

Passing errors as values

Sometimes it is useful to be able to pass a cell value from Excel to python when the cell value is actually an error, or vice-versa.

PyXLL has two different ways of doing this.

The first is to use the var type, which passes Excel errors as Python exception objects. Below is a table that shows how Excel errors are converted to python exception objects when the var type is used.

Excel error

Python exception type

#NULL!

LookupError

#DIV/0!

ZeroDivisionError

#VALUE!

ValueError

#REF!

ReferenceError

#NAME!

NameError

#NUM!

ArithmeticError

#NA!

RuntimeError

The second is to use the special type: float_nan.

float_nan behaves in almost exactly the same way as the normal float type. It can be used as an array type, or as an element type in a numpy array, e.g. numpy_array<float_nan>. The only difference is that if the Excel value is an error or a non-numeric type (e.g. an empty cell), the value passed to python will be float('nan') or 1.#QNAN, which is equivalent to numpy.nan.

The two different float types exist because sometimes you don’t want your function to be called if there’s an error with the inputs, but sometimes you do. There is also a slight performance penalty for using the float_nan type when compared to a plain float.

Errors can also be returned to Excel using instances of python exception types. This way, it is possible to return arrays where some values are errors but some aren’t.

Custom types

As well as the standard types listed above, it’s also possible to define your own argument and return types that can then be used in your function signatures.

Custom argument types need a function that will convert a standard type to the custom type, which will then be passed to your function. For example, if you have a function that takes an instance of type X, you can declare a function to convert from a standard type to X and then use X as a type in your function signature. When called from Excel, your conversion function will be called with an instance of the base type, and then your exposed UDF will be called with the result of that conversion.

To declare a custom type, you use the xl_arg_type decorator on your conversion function. The xl_arg_type decorator takes at least two arguments, the name of your custom type and the base type.

Returns a decorator for registering a function for converting from a base type to a custom type.

Parameters:

name (string) – custom type name

base_type (string) – base type

allow_arrays (boolean) – custom type may be passed in an array using the standard [] notation

macro (boolean) – If True all functions using this type will automatically be registered as a macro sheet equivalent function

thread_safe (boolean) – If False any function using this type will never be registered as thread safe

Here’s an example of a simple custom type:

frompyxllimportxl_arg_typeclassCustomType:def__init__(self,x):self.x=x@xl_arg_type("custom","string")defstring_to_customtype(x):returnCustomType(x)@xl_func("custom x: bool")deftest_custom_type_arg(x):# this function is called from Excel with a string, and then# string_to_customtype is called to convert that to a CustomType# and then this function is called with that instancereturnisinstance(x,CustomType)

custom can now be used as an argument type in a function signature. The Excel UDF will take a string, but before your Python function is called the conversion function will be used to convert that string to a CustomType instance.

To use a custom type as a return type you also have to specify the conversion function from your custom type to a base type. This is exactly the reverse of the custom argument type conversion described previously.

The custom return type conversion function is decorated with the xl_return_type decorator.

Returns a decorator for registering a function for converting from a custom type to a base type.

Parameters:

name (string) – custom type name

base_type (string) – base type

allow_arrays (boolean) – custom type may be returned as an array using the standard [] notation

macro (boolean) – If True all functions using this type will automatically be registered as a macro sheet equivalent function

thread_safe (boolean) – If False any function using this type will never be registered as thread safe

For the previous example the return type conversion function could look like:

frompyxllimportxl_return_type,xl_func@xl_return_type("custom","string")defcustomtype_to_string(x):# x is an instance of CustomTypereturnx.x@xl_func("string x: custom")deftest_returning_custom_type(x):# the returned object will get converted to a string# using customtype_to_string before being returned to ExcelreturnCustomType(x)

Any recognized type can be used as a base type. That can be a standard type, an array type or another custom type (or even an array of a custom type!). The only restriction is that it must resolve to a standard type eventually.

There are more examples of custom types included in the PyXLL download.

Type conversion

Sometimes it’s useful to be able to convert from one type to another, but it’s not always convenient to have to determine the chain of functions
to call to convert from one type to another.

For example, you might have a function that takes an array of var types, but some of those may actually be datetimes, or one of your own custom types.
To convert them to those types you would have to check what type has actually been passed to your function and then decide what to call to get it
into exactly the type you want.

PyXLL includes the function get_type_converter to do this for you. It takes source and target types by name and returns a function that
will perform the conversion, if possible.

pyxll.get_type_converter(src_type, dest_type)

Returns a function to convert objects of type src_type to dest_type.

Parameters:

src_type (string) – name of type to convert from

dest_type (string) – name of type to convert to

Returns:

function to convert from src_type to dest_type

Even if there is no function registered that converts exactly from src_type to dest_type, as long as there is a way to convert from
src_type to dest_type using one or more intermediate types this function will create a function to do that.

Here’s an example that shows how to get a datetime from a var parameter:

XLRect instances are accessed via XLCell.rect to get the coordinates of the cell.

first_row

last_row

first_col

last_col

Asynchronous functions

In Excel 2010 Microsoft introduced asynchronous functions. Instead of returning a value immediately, an asynchronous function initiates a potentially slow calculation in another thread, or perhaps via a server request, and returns immediately. When the result of the calculation is ready xlAsyncReturn is called to inform Excel.

PyXLL makes registering an asynchronous function very simple. By using the type async_handle, in the function signature passed to xl_func, the function will automatically get registered as an asynchronous function.

The async_handle parameter will be a unique handle for that function call and must be used to return the result when it’s ready. The async_handle type should be considered opaque and any functions using that type shouldn’t return a value.

frompyxllimportxl_func,xlAsyncReturnfromthreadingimportThreadimporttimeclassMyThread(Thread):def__init__(self,async_handle,x):Thread.__init__(self)self.__async_handle=async_handleself.__x=xdefrun(self):# here would be your long running function or a call to a server# or something like thattime.sleep(5)xlAsyncReturn(self.__async_handle,self.__x)# no return type required as async functions don't return a value# the excel function will just take x, the async_handle is added automatically by Excel@xl_func("async_handle h, int x")defmy_async_function(h,x):# start the long calculation in another threadthread=MyThread(h,x)thread.start()# return immediately, the real result will be returned by the thread functionreturn

Asynchronous functions are only available in Excel 2010. Attempting to use them in an earlier version will result in an error.

Creating custom menu items

Adding custom menus is just as easy. You use the xl_menu decorator to expose a python function as a menu callback. PyXLL creates the menu item for you, and when it’s selected your python function is called. That python function can call back into Excel using win32com to make changes to the current sheet or workbook.

Different menus can be created and you can also create submenus. The order in which the items appear is controlled by optional keyword arguments to the xl_menu decorator.

Here’s a very simple example that uses the win32api module to pop up a message box when the user selects the menu item:

Basic menu items

xl_menu is a function that returns a decorator for creating menu items that call Python functions.

param string name:

name of the menu item that the user will see in the menu

param string menu:

name of the menu that the item will be added to. If a menu of that name doesn’t
already exist it will be created. By default the PyXLL menu is used

param string sub_menu:

name of the submenu that this item belongs to. If a submenu of that name doesn’t
exist it will be created

param int order:

influences where the item appears in the menu. The higher the number, the further down
the list. Items with the same sort order are ordered lexographically. If the item is a
sub-menu item, this order influences where the sub-menu will appear in the main menu

param int sub_order:

similar to order but it is used to set the order of items within a sub-menu

param int menu_order:

used when there are multiple menus and controls the order in which the menus are added

Parameters:

allow_abort (boolean) – If True the function may be cancelled by the user pressing Esc. A KeyboardInterrupt
exception is raised when Esc is pressed. If not specified the behavior is determined
by the allow_abort setting in the config (see Configuration).

To add a new menu entry to the PyXLL menu you use the xl_menu decorator with the label you want to use for that menu item, and a function that takes no arguments that will be called when that menu item is selected.

Here is an example of a simple menu item that uses win32api to display a message box to the user:

Menu items may modify the current workbook, or in fact do anything that you can do via the Excel automatition API. This allows you to do anything in Python that you previously would have had to have done in VBA.

Below is an example that uses the win32com module to call back into Excel from a menu item:

frompyxllimportxl_menu,get_active_objectimportwin32com.client@xl_menu("win32com menu item")defwin32com_menu_item():# get the Excel application objectxl_window=get_active_object()xl_app=win32com.client.Dispatch(xl_window).Application# get the current selected rangeselection=xl.Selection# set some text to the selectionselection.Value="Hello!"

New menus

As well as adding menu items to the main PyXLL addin menu it’s possible to create entirely new menus.

To create a new menu, use the menu keyword argument to the xl_menu decorator.

In addition, if you want to control the order in which menus are added you may use the menu_order integer keyword argument. The higher the value, the later in the ordering the menu will be added.

Below is a modification of an earlier menu example that puts the menu item in a new menu, called “New Menu”:

Sub-menus

Sub-menus may also be created. To add an item to a sub-menu, use the sub_menu keyword argument to the xl_menu decorator.

All sub-menu items share the same sub_menu argument. The ordering of the items within the submenu is controlled by the sub_order integer keyword argument. In the case of sub-menus, the order keyword argument controls the order of the sub-menu within the parent menu.

For example, to add the sub-menu item “TEST” to the sub-menu “Sub Menu” of the main menu “My Menu”, you would use a decorator as illustrated by the following code:

Writing Excel macros

You can write an Excel macro in python to do whatever you would previously have used VBA for. Macros work in a very similar way to worksheet functions. To register a function as a macro you use the xl_macro decorator.

Macros are useful as they can be called when GUI elements (buttons, checkboxes etc.) fire events. They can also be called from VBA.

Exposing functions as macros

Python functions to be exposed as macros are decorated with the xl_macro decorator imported from the pyxll module.

pyxll.xl_macro([signature=""] [, allow_abort=?])

xl_macro is a function that returns a decorator for exposing python functions to Excel as macros.

Parameters:

signature (str) – an optional string that specifies the argument types and, optionally,
their names and the return type. The format of the signature is identical to the one
used by xl_func. If no signature is supplied, it is assumed the function
takes no arguments and the return value is not used.

allow_abort (bool) – If True the function may be cancelled by the user pressing Esc. A KeyboardInterrupt
exception is raised when Esc is pressed. If not specified the behavior is determined
by the allow_abort setting in the config (see Configuration).

Example usage:

frompyxllimportxl_macroimportwin32api@xl_macro()defpopup_messagebox():"""pops up a message box"""win32api.MessageBox(0,"Hello","Hello")@xl_macro("string x: int")defpy_strlen(n):"""returns the length of x"""returnlen(x)

Calling macros from Excel

Macros defined with PyXLL can be called from Excel the same way as any other Excel macros.

The most usual way is to assign a macro to a control. To do that, first add the Forms toolbox by going to the Tools Customize menu in Excel and check the Forms checkbox. This will present you with a panel of different controls which you can add to your worksheet. For the message box example above, add a button and then right click and select ‘Assign macro...’. Enter the name of your macro, in this case popup_messagebox. Now when you click that button the macro will be called.

It is also possible to call your macros from VBA. While PyXLL may be used to reduce the need for VBA in your projects, sometimes it is helpful to be able to call python functions from VBA.

For the py_strlen example above, to call that from VBA you would use the Run VBA function, e.g.:

On their own macros might seem a bit limited. When you consider you can call back into Excel from macros using the Excel COM API to do everything you previously would have done in VBA they suddenly become a lot more useful.

There are more examples of macros called from controls in the examples supplied with PyXLL.

Calling back into Excel from Python

While worksheet functions don’t usually require to call back into Excel, it’s usual that menu items and macros do.

Occasionally worksheet functions may also want to call back into Excel. For them to do so, they must be registered as macro sheet equivalent functions (see xl_func).

Macro sheet and command functions

The Excel C API provides a number of functions for querying information from Excel. A few of those are also available to call from PyXLL.

In order to call most of these functions from an Excel worksheet function the function must be registered as a macro sheet equivalent function (see Exposing functions as UDFs).

It’s better to use get_active_object than win32com.client.GetActiveObject("Excel.Application") as it will always give you the current instance of Excel, whereas getting it by name will return you any running instance which may not be the same if you’re running more than one instance of Excel.

Using the Excel COM API to modify the current worksheet is something that you might do from a menu item or from a macro. In some cases you may also want to do it from a worksheet function. Some versions of Excel will block calls to the COM API while calling a worksheet function and so if you try you will cause Excel to hang, even if using a macro sheet equivalent worksheet function.

Because of the deadlock problems associated with calling back into Excel from a worksheet function, PyXLL has a function async_call[4] . It takes a callable object and calls it in a background thread. It returns immediately and so doesn’t block waiting for the call back to Excel.

pyxll.async_call(func [, *args] [, **kwargs])

async_call schedules func to be called from a background thread.

If args or kwargs contain any COM objects they will be marshalled across the thread boundary automatically.

Returns immediately.

Parameters:

func – callable object (e.g. a function) to be called in the background thread

args (tuple) – passed to func when it’s called

kwargs (dict) – passed to func when it’s called

When calling back into Excel and modifying the current sheet from a macro sheet equivalent function it’s possible to end up with a circular dependency. Macro sheet equivalent functions with any xl_cell arguments are considered volatile by Excel, so any change to the worksheet causes them to be re-evaulated.

To help with this, PyXLL keeps track of the arguments and return values for worksheet functions when it’s likely that a circular dependency will occur and prevents that from happening. If you decide not to use async_call and to use another thread yourself you need to be careful of circular dependencies.

Not to be confused with asynchronous functions that return their result to Excel asynchronously. async_call is simply a function that queues a callable object to be called later. See also Asynchronous functions.

PyXLL callbacks

You can register some functions to be called at certain times by PyXLL using a few decorators that may be imported from pyxll. This section describes those decorators.

pyxll.xl_on_open(func)

Decorator for callbacks that should be called after PyXLL has
been opened and the user modules have been imported.

The callback takes a list of tuples of three three items:
(modulename,module,exc_info)

When a module has been loaded successfully, exc_info is None.

When a module has failed to load, module is None and exc_info
is the exception information (exc_type,exc_value,exc_traceback).

Decorator for callbacks that should be called after a reload is
attempted.

The callback takes a list of tuples of three three items:
(modulename,module,exc_info)

When a module has been loaded successfully, exc_info is None.

When a module has failed to load, module is None and exc_info
is the exception information (exc_type,exc_value,exc_traceback).

example usage:

frompyxllimportxl_on_reload,xlcCalculateNow@xl_on_reloaddefon_reload(reload_info):formodulename,module,exc_infoinreload_info:ifmoduleisNone:exc_type,exc_value,exc_traceback=exc_info...dosomethingwiththiserror...# recalcuate all open workbooksxlcCalculateNow()

pyxll.xl_on_close(func)

Decorator for registering a function that will be called when Excel is about to close.

This can be useful if, for example, you’ve created some background threads and need to stop them cleanly for Excel to shutdown successfully. You may have other resources that you need to release before Excel closes as well, such as COM objects, that would prevent Excel from shutting down. This callback is the place to do that.

This callback is called when the user goes to close Excel. However, they may choose to then cancel the close operation but the callback will already have been called. Therefore you should ensure that anything you clean up here will be re-created later on-demand if the user decides to cancel and continue using Excel.

To get a callback when Python is shutting down, which occurs when Excel is finally quitting, you should use the standard atexit Python module. Python will not shut down in some circumstances (e.g. when a non-daemonic thread is still running or if there are any handles to Excel COM objects that haven’t been released) so a combination of the two callbacks is sometimes required.

example usage:

frompyxllimportxl_on_close@xl_on_closedefon_close():print"closing..."

pyxll.xl_license_notifier(func)

Decorator for registering a function that will be called when PyXLL is starting up and checking the license key.

It can be used to alert the user or to email a support or IT person when the license is coming up for renewal, so a new license can be arranged in advance to minimize any disruption.

If the license is perpetual (doesn’t expire) expdate will be the end date of the maintenance agreement (when maintenance builds are available until) and days_left will be the days between the PyXLL build date and expdate.

There commands can be called via Excel’s COM API, and so may be called from other processes
outside of Excel which means they can be called by scripts invoked from an external editor.

pyxll_reload()

Reloads all modules listed in the PyXLL config file and updates all the Excel functions,
macros and menus.

Calling this macro command is equivalent to selecting the Reload menu item.

pyxll_rebind()

Refreshes all the Excel function, macro and menu bindings. This can be used if additional
modules have been imported since PyXLL started, or if modules have been reloaded and
those modules include Excel functions that need to be updated to refer to the reloaded
instances of the Python functions.

The are Excel macro commands, not python functions.

They may be called from VBA using the Run command, or from Python using the win32com module.

Example of calling from VBA:

Sub ReloadPyXLL()
Run "pyxll_reload"
End Sub

Example of calling from Python (this may be called from any Python instance, not just from within Excel):

Debugging

It should be possible to use any remote Python debugger to debug code running in Excel using PyXLL.

If you are using Eclipse with PyDev you can use PyDev’s remote debugging feature to debug
Python code in Eclipse. There is an example included with PyXLL that shows how, and is
also available in the developer tools examples.

Examples

All of the following examples are included in the package available for download.
The download also includes a workbook showing all of these examples in action.

"""PyXLL Examples: Worksheet functionsThe PyXLL Excel Addin is configured to load one or morepython modules when it's loaded. Functions are exposedto Excel as worksheet functions by decorators declared inthe pyxll module.Functions decorated with the xl_func decorator are exposedto Excel as UDFs (User Defined Functions) and may be calledfrom cells in Excel."""## 1) Basics - exposing functions to Excel### xl_func is the main decorator and is used for exposing# python functions to excel.#frompyxllimportxl_func## xl_func takes a string argument that is the signature of# the function to be exposed to excel. This example takes# three integers and returns an integer.#@xl_func("int x, int y, int z: int")defbasic_pyxll_function_1(x,y,z):"""returns (x * y) ** z """return(x*y)**z## there are a number of basic types that can be used in# the function signature. These include:# int, float, bool and string# There are more types that we'll come to later.#@xl_func("int x, float y, bool z: float")defbasic_pyxll_function_2(x,y,z):"""if z return x, else return y"""ifz:# we're returning an integer, but the signature# says we're returning a float.# PyXLL will convert the integer to a float for us.returnxreturny## you can change the category the function appears under in# Excel by using the optional argument 'category'.#@xl_func("int x: int",category="My new PyXLL Category")defbasic_pyxll_function_3(x):"""docstrings appear as help text in Excel"""returnx## 2) The var type### Another type is the var type. This can represent any# of the basic types, depending on what type is passed to the# function, or what type is returned.#@xl_func("var x: string")defvar_pyxll_function_1(x):"""takes an float, bool, string, None or array"""# we'll return the type of the object passed to us, pyxll# will then convert that to a string when it's returned to# excel.returntype(x)## If var is the return type. PyXll will convert it to the# most suitable basic type. If it's not a basic type and# no suitable conversion can be found, it will be converted# to a string and the string will be returned.#@xl_func("bool x: var")defvar_pyxll_function_2(x):"""if x return string, else a number"""ifx:return"var can be used to return different types"return123.456## 3) Arrays### Arrays in PyXll are 2d arrays that correspond to the grid in# Excel. In python, they are represented as lists of lists.# Arrays of any type can be used, and the var type may be# an array of vars.## Arrays of floats are more efficient to marshall between# python and Excel than other array types so should be used# when possible instead of var.## NumPy arrays are also supported. For those, see the# next section.#@xl_func("float[] x: float")defarray_pyxll_function_1(x):"""returns the sum of a range of floats"""total=0.0# x is a list of lists - iterate through the rows:forrowinx:# each row is a list of floatsforelementinrow:total+=elementreturntotal## Functions can also return 2d arrays as lists of lists# in python. These can be used as array formulas in excel# to return a grid of data.#@xl_func("string[] array, string sep: string[]")defarray_pyxll_function_2(x,sep):"""joins each row by 'sep' and returns a column of strings"""# result is a list of lists of stringsresult=[]forrowinx:s=sep.join(row)# the result is just one column wideresult_row=[s]result.append(result_row)returnresult## the var type may also be used to pass and return arrays, but# the python function should do any necessary type checking.#@xl_func("var x: string[]")defarray_pyxll_function_3(x):"""returns the types of the elements as strings"""# x may not be an arrayifnotisinstance(x,list):return[[type(x)]]# x is a 2d array - list of lists.return[[type(e)foreinrow]forrowinx]## var arrays may also be used#@xl_func("var[] x: string[]")defarray_pyxll_function_4(x):"""returns the types of the elements as strings"""# x will always be a 2d array - list of lists.return[[type(e)foreinrow]forrowinx]## xlfCaller can be used to get information about the# calling cell or range#frompyxllimportxlfCaller@xl_func("var[] x: var[]")defarray_pyxll_function_5(x):""" return the input array with row and col numbers. This example shows how to use xlfCaller to get the range of the cells the array function is being called by. """# get the size of the rect the array function was called over# i.e. the size of the array to be returnedcaller=xlfCaller()width=caller.rect.last_col-caller.rect.first_col+1height=caller.rect.last_row-caller.rect.first_row+1# check the input array is the same sizeassertlen(x)==heightassertlen(x[0])==width# construct the return value as a list of lists with the# same dimensions as the calling cells.result=[]foriinrange(height):row=[]forjinrange(width):row.append("%s (col=%d, row=%d)"%(x[i][j],j,i))result.append(row)returnresult## 4) NumPy arrays## the numpy_array type corresponds to the numpy.ndarray# type.## You must have numpy installed to be able to use the# numpy_array type.#@xl_func("numpy_array x: numpy_array")defnumpy_array_function_1(x):# return the transpose of the arrayreturnx.transpose()@xl_func("numpy_array<float_nan> x: numpy_array<float_nan>")defnumpy_array_function_2(x):# simply return the array to demonstrate how errors from# excel may be passed to python as NaNreturnx## As well as 2d arrays, 1d rows and columns may also be used# as argument and return types.#@xl_func("numpy_row x: string")defnumpy_row_function_1(x):returnstr(x)@xl_func("numpy_row x: numpy_column")defnumpy_row_function_2(x):returnx.transpose()@xl_func("numpy_column x: string")defnumpy_col_function_1(x):returnstr(x)@xl_func("numpy_column x: numpy_row")defnumpy_col_function_2(x):returnx.transpose()## 5) Date and time types### There are three date and time types: date, time, datetime## Excel represents dates and times as floating point numbers.# The pyxll datetime types convert the excel number to a# python datetime.date, datetime.time and datetime.datetime# object depending on what type you specify in the signature.## dates and times may be returned using their type as the return# type in the signature, or as the var type.#importdatetime@xl_func("date x: string")defdatetime_pyxll_function_1(x):"""returns a string description of the date"""return"type=%s, date=%s"%(type(x),x)@xl_func("time x: string")defdatetime_pyxll_function_2(x):"""returns a string description of the time"""return"type=%s, time=%s"%(type(x),x)@xl_func("datetime x: string")defdatetime_pyxll_function_3(x):"""returns a string description of the datetime"""return"type=%s, datetime=%s"%(type(x),x)@xl_func("datetime[] x: datetime")defdatetime_pyxll_function_4(x):"""returns the max datetime"""m=datetime.datetime(1900,1,1)forrowinx:m=max(m,max(row))returnm## 6) xl_cell## The xl_cell type can be used to receive a cell# object rather than a plain value. The cell object# has the value, address, formula and note of the# reference cell passed to the function.## The function must be a macro sheet equivalent function# in order to access the value, address, formula and note# properties of the cell.#@xl_func("xl_cell cell : string",macro=True)defxl_cell_example(cell):"""a cell has a value, address, formula and note"""return"[value=%s, address=%s, formula=%s, note=%s]"%(cell.value,cell.address,cell.formula,cell.note)

Custom Types

This example shows some basic usage of the custom type functionality that demonstrates how non-standard types can coerced into types Excel understands.

"""PyXLL Examples: Custom typesWorksheet functions can use a number of standard typesas shown in the worksheetfuncs example.It's also possible to define custom types thatcan be used in the PyXLL function signaturesas shown by these examples.For a more complicated custom type example see theobject cache example."""frompyxllimportxl_func## xl_arg_type and xl_return type are decorators that can# be used to declare types that our excel functions# can use in addition to the standard types#frompyxllimportxl_arg_type,xl_return_type## 1) Custom types### All variables are passed to and from excel as the basic types,# but it's possible to register conversion functions that will# convert those basic types to whatever types you like before# they reach your function, (or after you function returns them# in the case of returned values).### CustomType1 is a very simple class used to demonstrate# custom types.#classCustomType1:def__init__(self,name):self.name=namedefgreeting(self):return"Hello, my name is %s"%self.name## To use CustomType1 as an argument to a pyxll function you have to# register a function to convert from a basic type to our custom type.## xl_arg_type takes two arguments, the new custom type name, and the# base type.#@xl_arg_type("custom1","string")defstring_to_custom1(name):returnCustomType1(name)## now the type 'custom1' can be used as an argument type # in a function signature.#@xl_func("custom1 x: string")defcustomtype_pyxll_function_1(x):"""returns x.greeting()"""returnx.greeting()## To use CustomType1 as a return type for a pyxll function you have# to register a function to convert from the custom type to a basic type.## xl_return_type takes two arguments, the new custom type name, and# the base type.#@xl_return_type("custom1","string")defcustom1_to_string(x):returnx.name## now the type 'custom1' can be used as the return type.#@xl_func("custom1 x: custom1")defcustomtype_pyxll_function_2(x):"""check the type and return the same object"""assertisinstance(x,CustomType1),"expected an CustomType1 object"""returnx## CustomType2 is another example that caches its instances# so they can be referred to from excel functions.#classCustomType2:__instances__={}def__init__(self,name,value):self.value=valueself.id="%s-%d"%(name,id(self))# overwrite any existing instance with selfself.__instances__[name]=selfdefgetValue(self):returnself.value@classmethoddefgetInstance(cls,id):name,unused=id.split("-")returncls.__instances__[name]defgetId(self):returnself.id@xl_arg_type("custom2","string")defstring_to_custom2(x):returnCustomType2.getInstance(x)@xl_return_type("custom2","string")defcustom2_to_string(x):returnx.getId()@xl_func("string name, float value: custom2")defcustomtype_pyxll_function_3(name,value):"""returns a new CustomType2 object"""returnCustomType2(name,value)@xl_func("custom2 x: float")defcustomtype_pyxll_function_4(x):"""returns x.getValue()"""returnx.getValue()## custom types may be base types of other custom types, as# long as the ultimate base type is a basic type.## This means you can chain conversion functions together.#classCustomType3:def__init__(self,custom2):self.custom2=custom2defgetValue(self):returnself.custom2.getValue()*2@xl_arg_type("custom3","custom2")defcustom2_to_custom3(x):returnCustomType3(x)@xl_return_type("custom3","custom2")defcustom3_to_custom2(x):returnx.custom2## when converting from an excel cell to a CustomType3 object,# the string will first be used to get a CustomType2 object# via the registed function string_to_custom2, and then# custom2_to_custom3 will be called to get the final # CustomType3 object.#@xl_func("custom3 x: float")defcustomtype_pyxll_function_5(x):"""return x.getValue()"""returnx.getValue()## 2) Custom types and arrays### Array types may be used as the base types for custom types# in the same way as any other type.### This example shows how to reduce a range of data (list of# lists) to a single list for use by a function.## It also shows how it's possible to use multiple xl_arg_type# decorators for the same function without duplicating code.#@xl_arg_type("int_list","int[]")@xl_arg_type("float_list","float[]")@xl_arg_type("custom1_list","custom1[]")defflatten(x):returnreduce(lambdaa,b:a+b,x,[])@xl_func("float_list x: string")defcustomarray_pyxll_function_1(x):# x is list of floatstotal=sum(x,0)return"sum=%f : %s"%(total,x)@xl_func("custom1_list x: string")defcustomarray_pyxll_function_2(x):# x is a list of CustomType1 objectsreturn"Hello %s"%(", ".join([c.nameforcinx]))

Menu Functions

These examples show how to create Excel menu items that call your Python code when selected.

"""PyXLL Examples: MenusThe PyXLL Excel Addin is configured to load one or morepython modules when it's loaded.Menus can be added to Excel via the pyxll xl_menu decorator."""importlogging_log=logging.getLogger(__name__)# the webbrowser module is used in an example to open the log filetry:importwebbrowserexceptImportError:_log.warning("*** webbrowser could not be imported ***")_log.warning("*** the menu examples will not work correctly ***")importos## 1) Basics - adding a menu items to Excel### xl_menu is the decorator used for addin menus to Excel.#frompyxllimportxl_menu,get_config,xlcAlert## The only required argument is the menu item name.# The example below will add a new menu item to the# addin's default menu.#@xl_menu("Example Menu Item 1")defon_example_menu_item_1():xlcAlert("Hello from PyXLL")## menu items are normally sorted alphabetically, but the order# keyword can be used to influence the ordering of the items# in a menu.## The default value for all sort keyword arguments is 0, so positive# values will result in the item appearing further down the list# and negative numbers result in the item appearing further up.#@xl_menu("Another example menu item",order=1)defon_example_menu_item_2():xlcAlert("Hello again from PyXLL")## It's possible to add items to menus other than the default menu.# The example below creates a new menu called 'My new menu' with# one item 'Click me' in it.## The menu_order keyword is optional, but may be used to influence# the order that the custom menus appear in.#@xl_menu("Click me",menu="PyXLL example menu",menu_order=1)defon_example_menu_item_3():xlcAlert("Wow, a different menu!")## 2) Sub-menus## it's possible to add sub-menus just by using the sub_menu# keyword argument. The example below adds a new sub menu# 'Sub Menu' to the default menu.## The order keyword argument affects where the sub menu will# appear in the parent menu, and the sub_order keyword argument# affects where the item will appear in the sub menu.#@xl_menu("Click me",sub_menu="More Examples",order=2)defon_example_submenu_item_1():xlcAlert("Sub-menus can be created easily with PyXLL")## When using Excel 2007 and onwards the Excel functions accept unicode strings#@xl_menu("Unicode Test",sub_menu="More Examples")defon_unicode_test():xlcAlert(u"\u01d9ni\u0186\u020dde")## A simple menu item to show how to get the PyXLL config# object and open the log file.#@xl_menu("Open log file",order=3)defon_open_logfile():# the PyXLL config is accessed as a ConfigParser.ConfigParser objectconfig=get_config()ifconfig.has_option("LOG","path")andconfig.has_option("LOG","file"):path=os.path.join(config.get("LOG","path"),config.get("LOG","file"))webbrowser.open("file://%s"%path)

Macros and Excel Scripting

These examples show how to register macros that can be attached to
Excel GUI object and how call back into Excel from Python using the win32com module.

"""PyXLL Examples: AutomationPyXLL worksheet and menu functions can call back into Excelusing the Excel COM API*.In addition to the COM API there are a few Excel functionsexposed via PyXLL that allow you to query information aboutthe current state of Excel without using COM.Excel uses different security policies for different typesof functions that are registered with it. Depending onthe type of function, you may or may not be able to makesome calls to Excel.Menu functions and macros are registered as 'commands'.Commands are free to call back into Excel and make changes todocuments. These are equivalent to the VBA Sub routines.Worksheet functions are registered as 'functions'. Theseare limited in what they can do. You will be able tocall back into Excel to read values, but not changeanything. Most of the Excel functions exposed via PyXLLwill not work in worksheet functions. These are equivalentto VBA Functions.There is a third type of function - macro-sheet equivalentfunctions. These are worksheet functions that are allowed todo most things a macro function (command) would be allowedto do. These shouldn't be used lightly as they may breakthe calculation dependencies between cells if notused carefully.* Excel COM support was added in Office 2000. If you are using an earlier version these COM examples won't work."""importpyxllfrompyxllimportxl_menu,xl_func,xl_macroimportlogging_log=logging.getLogger(__name__)try:importwin32com.clientexceptImportError:_log.warning("*** win32com.client could not be imported ***")_log.warning("*** some of the automation examples will not work ***")_log.warning("*** to fix this, install the pywin32 extensions. ***")## Getting the Excel COM object## PyXLL has a function 'get_active_object'. This returns# a PyIDispatch object for the Excel window instance.# It is better to use this than# win32com.client.Dispatch("Excel.Application")# as it will always be the correct handle - ie the handle# to the correct instance of Excel.## The window object can be wrapped as a # win32com.client.Dispatch object to make it# easier to use, as shown in these examples.## For more information on win32com see the pywin32 project# on sourceforge.## The Excel object model is the same from COM as from VBA# so usually it's straightforward to write something# in python if you know how to do it in VBA.## For more information about the Excel object model# see MSDN or the object browser in the Excel VBA editor.#defxl_app():"""returns a Dispatch object for the current Excel instance"""# get the Excel application object from PyXLL and wrap itxl_window=pyxll.get_active_object()xl_app=win32com.client.Dispatch(xl_window).Application# it's helpful to make sure the gen_py wrapper has been created# as otherwise things like constants and event handlers won't work.win32com.client.gencache.EnsureDispatch(xl_app)returnxl_app## A simple example of a menu function that modifies# the contents of the selected range.#@xl_menu("win32com test",sub_menu="More Examples")defwin32com_menu_test():# get the current selected range and set some textselection=xl_app().Selectionselection.Value="Hello!"pyxll.xlcAlert("Some text has been written to the current cell")## Macros can also be used to call back into Excel when# a control is activated.## These work in the same way as VBA macros, you just assign# them to the control in Excel by name.#@xl_macro()defbutton_example():xl=xl_app()range=xl.Range("button_output")range.Value=range.Value+1@xl_macro()defcheckbox_example():xl=xl_app()check_box=xl.ActiveSheet.CheckBoxes(xl.Caller)ifcheck_box.Value==1:xl.Range("checkbox_output").Value="CHECKED"else:xl.Range("checkbox_output").Value="Click the check box"@xl_macro()defscrollbar_example():xl=xl_app()caller=xl.Callerscrollbar=xl.ActiveSheet.ScrollBars(xl.Caller)xl.Range("scrollbar_output").Value=scrollbar.Value## Worksheet functions can also call back into Excel.## The function 'async_call' must be used to do the# actual work of calling back into Excel from another# thread, otherwise Excel may lock waiting for the function# to complete before allowing the COM object to modify the# sheet, which will cause a dead-lock.## To be able to call xlfCaller from the worksheet function,# the function must be declared as a macro sheet equivalent# function by passing macro=True to xl_func.## If your function modifies the Excel worksheet it will# trigger a recalculation so you have to take care not to# cause an infinite loop.## Accessing the 'address' property of the XLCell returned# by xlfCaller requires this function to be a macro sheet# equivalent function.#@xl_func("int rows, int cols, var value",macro=True)defautomation_example(rows,cols,value):"""copies value to a range of rows x cols below the calling cell"""# get the address of the calling cell using xlfCallercaller=pyxll.xlfCaller()address=caller.address# the update is done asynchronously so as not to block some# versions of Excel by updating the worksheet from a worksheet functiondefupdate_func():xl=xl_app()range=xl.Range(address)# get the cell below and expand it to rows x colsrange=xl.Range(range.Resize(2,1),range.Resize(rows+1,cols))# and set the range's valuerange.Value=value# kick off the asynchronous call the update functionpyxll.async_call(update_func)returnaddress

PyXLL Callbacks

It’s possible to register functions for PyXLL to call at certain points. These examples show how to use each of the callback decorators.

"""PyXLL Examples: CallbacksThe PyXLL Excel Addin is configured to load one or morepython modules when it's loaded.Moldules can register callbacks with PyXLL that will becalled at various times to inform the user code ofcertain events."""frompyxllimportxl_on_open, \
xl_on_reload, \
xl_on_close, \
xl_license_notifier, \
xlcAlert, \
xlcCalculateNowimportlogging_log=logging.getLogger(__name__)@xl_on_opendefon_open(import_info):""" on_open is registered to be called by PyXLL when the addin is opened via the xl_on_open decorator. This happens each time Excel starts with PyXLL installed. """# check to see which modules didn't import correctlyerrors=[]formodulename,module,exc_infoinimport_info:ifmoduleisNone:exc_type,exc_value,exc_traceback=exc_infoerrors.append("Error loading '%s' : %s"%(modulename,exc_value))iferrors:# report any errors to the userxlcAlert("\n".join(errors)+"\n\n(See callbacks.py example)")@xl_on_reloaddefon_reload(import_info):""" on_reload is registered to be called by PyXLL whenever a reload occurs via the xl_on_reload decorator. """# check to see which modules didn't import correctlyerrors=[]formodulename,module,exc_infoinimport_info:ifmoduleisNone:exc_type,exc_value,exc_traceback=exc_infoerrors.append("Error loading '%s' : %s"%(modulename,exc_value))iferrors:# report any errors to the userifwin32api:win32api.MessageBox(0,"\n".join(errors)+"\n\n(See callbacks.py example)","PyXLL Callbacks Example",win32con.MB_ICONWARNING)else:_log.info("callbacks.on_reload: "+"\n".join(errors))else:# report everything reloaded OKxlcAlert("PyXLL Reloaded OK\n(See callbacks.py example)")# recalcuate all open workbooksxlcCalculateNow()@xl_on_closedefon_close():""" on_close will get called as Excel is about to close. This is a good time to clean up any globals and stop any background threads so that the python interpretter can be closed down cleanly. The user may cancel Excel closing after this has been called, so your code should make sure that anything that's been cleaned up here will get recreated again if it's needed. """_log.info("callbacks.on_close: PyXLL is closing")@xl_license_notifierdeflicense_notifier(name,expdate,days_left,is_perpetual):""" license_notifier will be called when PyXLL is starting up, after it has read the config and verified the license. If there is no license name will be None and days_left will be less than 0. """ifdays_left>=0oris_perpetual:_log.info("callbacks.license_notifier: ""This copy of PyXLL is licensed to %s"%name)ifnotis_perpetual:_log.info("callbacks.license_notifier: ""%d days left before the license expires (%s)"%(days_left,expdate))else:_log.info("callbacks.license_notifier: ""This copy of PyXLL is for evaluation or non-commercial use only")

Object Cache

Advanced example

This is an advanced example but it’s fine to just use this code as it is.

All you have to do is include this code in your project and use the custom type cached_object as the return type of functions returning Python objects and as the argument type for functions expecting those objects.

Look at the functions cached_object_return_test and cached_object_arg_test in the code below.

This examples shows how Python objects can be passed on the Excel grid.

Using this example you can declare Python functions that return complex Python objects and functions that accept them as arguments without converting to and from basic types by storing the complex objects in an object cache.

A custom type cached_object is used to add the returned Python object to an object cache and return a string key into that cache that’s displayed in Excel.

When the custom type cached_object is used as an argument to a function it looks up that string key in the cache and retreives the cached object.

The function xlfCaller is used to determine which cell ‘owns’ the Python object and if that cell is updated the cache will remove its reference to that object and the new one is inserted in the cache.

Excel COM event handlers are used to monitor changes to the workbooks and worksheets so the object cache can be kept up to date as cells change, workbooks are closed and sheets are deleted.

"""PyXLL Examples: Object CacheExcel cells hold basic types (strings, numbers, booleans etc) but sometimesit can be useful to have functions that take and return objects and to beable to call those functions from Excel.This example shows how a custom type ('cached_object') and an object cachecan be used to pass objects between functions using PyXLL.It also shows how COM events can be used to remove items fromthe object cache when they are no longer needed."""frompyxllimportxlfCaller, \
xl_arg_type, \
xl_return_type, \
xl_func, \
xl_on_close, \
xl_on_reloadimportpyxllimportlogging_log=logging.getLogger(__name__)## win32com and automation.xl_app are required for the code that# cleans up the cache in response to Excel events.# The basic ObjectCache and related code will work without these# modules.try:importwin32com.client_have_win32com=TrueexceptImportError:_log.warning("*** win32com.client could not be imported ***")_log.warning("*** some of the objectcache examples will not work ***")_log.warning("*** to fix this, install the pywin32 extensions ***")_have_win32com=FalseclassObjectCacheKeyError(KeyError):""" Exception raised when attempting to retrieve an object from the cache that's not found. """def__init__(self,key):KeyError.__init__(self,key)classObjectCache(object):""" ObjectCache maintains a cache of objects returned to Excel and the cells referring to those objects. As xl functions return objects they update the cache and any previously cached objects are removed from the cache when they are no longer referred to by any cells. Custom functions don't reference this class directly, instead they use the custom type 'cached_object' which is registered with PyXLL after this class. """def__init__(self):# dict of workbooks -> worksheets -> cell to object idsself.__cells={}# dict of object ids to (object, {[referring (wb, ws, cell)] -> None})self.__objects={}def__len__(self):"""returns the number of cached objects"""returnlen(self.__objects)@staticmethoddef_get_obj_id(obj):"""returns the id for an object stored in the cache"""# the object id must be unique for objects within the cachecls_name=getattr(obj,"__class__",type(obj)).__name__return"<%s instance at 0x%x>"%(cls_name,id(obj))defupdate(self,workbook,sheet,cell,value):"""updates the cached value for a workbook, sheet and cell and returns the cache id"""obj_id=self._get_obj_id(value)# remove any previous entry in the cache for this cellself.delete(workbook,sheet,cell)_log.debug("Adding entry %s to cache at (%s, %s, %s)"%(obj_id,workbook,sheet,cell))# update the object cache to include this cell as a referring cell# (a dict is used instead of a set to be compatible with older python versions)unused,referring_cells=self.__objects.setdefault(obj_id,(value,{}))referring_cells[(workbook,sheet,cell)]=None# update the cache of cells to object idsself.__cells.setdefault(workbook,{}).setdefault(sheet,{})[cell]=obj_id# return the id for fetching the object from the cache laterreturnobj_iddefget(self,obj_id):""" returns an object stored in the cache by the object id returned from the update method. """try:returnself.__objects[obj_id][0]exceptKeyError:raiseObjectCacheKeyError(obj_id)defdelete(self,workbook,sheet,cell):"""deletes the cached value for a workbook, sheet and cell"""try:obj_id=self.__cells[workbook][sheet][cell]exceptKeyError:# nothing cached for this cellreturn_log.debug("Removing entry %s from cache at (%s, %s, %s)"%(obj_id,workbook,sheet,cell))# remove this cell from the object's referring cells and remove the# object from the cache if no more cells are referring to itobj,referring_cells=self.__objects[obj_id]delreferring_cells[(workbook,sheet,cell)]ifnotreferring_cells:delself.__objects[obj_id]# remove the entries from the __cells dictwb_cache=self.__cells[workbook]ws_cache=wb_cache[sheet]delws_cache[cell]ifnotws_cache:delwb_cache[sheet]ifnotwb_cache:delself.__cells[workbook]defdelete_all(self,workbook,sheet=None,predicate=None):""" deletes all references in the cache by workbook, worksheet. If predicate is not None, the cells will only be deleted if predicate(cell, obj_id) returns True """wb_cache=self.__cells.get(workbook)ifwb_cacheisnotNone:ifsheetisnotNone:sheets=[sheet]else:sheets=wb_cache.keys()forsheetinsheets:ws_cache=wb_cache.get(sheet)ifws_cacheisnotNone:cached_cells=ws_cache.items()forcell,obj_idincached_cells:ifpredicateisNoneorpredicate(cell,obj_id):self.delete(workbook,sheet,cell)## there's one global instance of the cache#_global_cache=ObjectCache()## Here we register the functions that convert the cached objects to and# from more basic types so they can be used by PyXLL Excel functions#@xl_return_type("cached_object","string",macro=True,allow_arrays=False,thread_safe=False)defcached_object_return_func(x):""" custom return type for objects that should be cached for use as parameters to other xl functions """# this requires the function to be registered as a macro sheet equivalent# function because it calls xlfCaller, hence macro=True in# the xl_return_type decorator above.## As xlfCaller returns the individual cell a function was called from, it's# not possible to return arrays of cached_objects using the cached_object[] # type in a function signature. allow_arrays=False prevents a function from# being registered with that return type. Arrays of cached_objects as an# argument type is fine though.if_have_win32com:# _setup_event_handler creates an event handler for Excel events to# ensure the cache is kept up to date with cell changes_setup_event_handler(_global_cache)# get the calling cell in [book]sheet!address formatcaller=xlfCaller()address=caller.address# split the cell up into workbook, sheet and cellassert"!"inaddress,"Calling cell address not in [book]sheet!address format: %s"%addresswb_and_sheet,cell=address.split("!",1)wb_and_sheet=wb_and_sheet.strip("'")assertwb_and_sheet.startswith("[")and"]"inwb_and_sheet, \
"Calling cell not in [book]sheet!address format: %s"%addressworkbook,sheet=wb_and_sheet.strip("[").split("]",1)while"''"insheet:sheet=sheet.replace("''","'")# update the cache and return the cached object idreturn_global_cache.update(workbook,sheet,cell,x)@xl_arg_type("cached_object","string")defcached_object_arg_func(x,thread_safe=False):""" custom argument type for objects that have been stored in the global object cache. """# lookup the object in the cache by its cached object idreturn_global_cache.get(x)## Example worksheet functions using the object cache## The following examples show how worksheet functions using# xl_func can use the new 'cached_object' type registered# above to return and take python objects cached by the# object cache (appear to be cached on the excel grid).#@xl_func(": int",volatile=True)defcached_object_count():"""returns the number of cached objects"""returnlen(_global_cache)classMyTestClass(object):"""A basic class for testing the cached_object type"""def__init__(self,x):self.__x=xdef__str__(self):return"%s(%s)"%(self.__class__.__name__,self.__x)@xl_func("var: cached_object")defcached_object_return_test(x):"""returns an instance of MyTestClass"""returnMyTestClass(x)@xl_func("cached_object: string")defcached_object_arg_test(x):"""takes a MyTestClass instance and returns a string"""returnstr(x)classMyDataGrid(object):""" A second class for demonstrating cached_object types. This class is constructed with a grid of data and has some basic methods which are also exposed as worksheet functions. """def__init__(self,grid):self.__grid=griddefsum(self):"""returns the sum of the numbers in the grid"""total=0forrowinself.__grid:total+=sum(row)returntotaldef__len__(self):total=0forrowinself.__grid:total+=len(row)returntotaldef__str__(self):return"%s(%d values)"%(self.__class__.__name__,len(self))@xl_func("float[]: cached_object")defmake_datagrid(x):"""returns a MyDataGrid object"""returnMyDataGrid(x)@xl_func("cached_object: int")defdatagrid_len(x):"""returns the length of a MyDataGrid object"""returnlen(x)@xl_func("cached_object: float")defdatagrid_sum(x):"""returns the sum of a MyDataGrid object"""returnx.sum()@xl_func("cached_object: string")defdatagrid_str(x):"""returns the string representation of a MyDataGrid object"""returnstr(x)## So far we can cache objects and keep the cache up to date as# functions are called and the return values change.## However, if a cell is changed from a function that returns a cached# object to something that doesn't there will be a reference# left in the cache - and so references can be leaked. Or, if a workbook# or worksheet is deleted objects will be leaked.## We can hook into some of Excel's Application and Workbook events to# detect when references to objects are no longer required and remove# them from the cache.#classEventHandlerMetaClass(type):""" A meta class for event handlers that don't repsond to all events. Without this an error would be raised by win32com when it tries to call an event handler method that isn't defined by the event handler instance. """def__new__(mcs,name,bases,dict):# construct the new classcls=type.__new__(mcs,name,bases,dict)# create dummy methods for any missing event handlerscls._dispid_to_func_=getattr(cls,"_dispid_to_func_",{})fordispid,nameincls._dispid_to_func_.iteritems():func=getattr(cls,name,None)iffuncisNone:func=lambda*args,**kwargs:Nonesetattr(cls,name,func)returnclsclassObjectCacheApplicationEventHandler(object):""" An event handler for Application events used to clean entries from the object cache that would otherwise be missed. """__metaclass__=EventHandlerMetaClassdef__init__(self):# we have an event handler per workbook, but they only get# created once set_cache is called.self.__wb_event_handlers={}self.__cache=Nonedefset_cache(self,cache):self.__cache=cache# create event handlers for all of the current workbooksforworkbookinself.Workbooks:wb=win32com.client.DispatchWithEvents(workbook,ObjectCacheWorkbookEventHandler)wb.set_cache(cache)self.__wb_event_handlers[workbook.Name]=wbdefOnWorkbookOpen(self,workbook):# this workbook can't have anything in the cache yet, so make# sure it doesn't (it's possible a workbook with the same name# was closed with some cached entries and this one was then# opened)ifself.__cacheisnotNone:self.__cache.delete_all(workbook=str(workbook.Name))# create a new workbook event handler for this workbookwb=win32com.client.DispatchWithEvents(workbook,ObjectCacheWorkbookEventHandler)wb.set_cache(self.__cache)# delete any previous handler now rather than possibly wait for the GCifworkbook.Nameinself.__wb_event_handlers:delself.__wb_event_handlers[workbook.Name]self.__wb_event_handlers[workbook.Name]=wbdefOnWorkbookActivate(self,workbook):# remove any workbooks that no longer existwb_names=[x.Nameforxinself.Workbooks]forname,handlerinself.__wb_event_handlers.items():ifnamenotinwb_names:# it's gone so remove the cache entries and the wb handlerifself.__cacheisnotNone:self.__cache.delete_all(str(name))delself.__wb_event_handlers[name]# add in any new workbooks, which can happen if a workbook has just been renamedifself.__cacheisnotNone:forwbinself.Workbooks:ifwb.Namenotinself.__wb_event_handlers:wb=win32com.client.DispatchWithEvents(wb,ObjectCacheWorkbookEventHandler)wb.set_cache(self.__cache)self.__wb_event_handlers[wb.Name]=wbclassObjectCacheWorkbookEventHandler(object):""" An event handler for Workbook events used to clean entries from the object cache that would otherwise be missed. """__metaclass__=EventHandlerMetaClassdef__init__(self):# keep track of sheets we know about for when sheets get deleted or renamedself.__sheets=[x.Nameforxinself.Sheets]self.__cache=Nonedefset_cache(self,cache):self.__cache=cachedefOnWorkbookNewSheet(self,sheet):# this work can't have anything in the cache yetifself.__cacheisnotNone:self.__cache.delete_all(str(self.Name),str(sheet.Name))# add it to our list of known sheetsself.__sheets.append(sheet.Name)defOnSheetActivate(self,sheet):# remove any worksheets that not longer existws_names=[x.Nameforxinself.Sheets]fornameinlist(self.__sheets):ifnamenotinws_names:# it's gone so remove the cache entries an the referenceifself.__cacheisnotNone:self.__cache.delete_all(str(self.Name),str(name))self.__sheets.remove(name)# ensure our list includes any new names due to renamesself.__sheets=ws_namesdefOnSheetChange(self,sheet,range):# delete all the cells from the cache where the cell is in range# and the current value is not the cached object iddefcheck_cell(cell,obj_id):# check this cell is in the range that's changedcell=sheet.Range(cell)ifrange.Find(cell)isNone:returnFalse# check the cell's value has changed from obj_idreturnstr(cell.Value)!=obj_idifself.__cacheisnotNone:self.__cache.delete_all(str(self.Name),str(sheet.Name),predicate=check_cell)def_xl_app():"""returns a Dispatch object for the current Excel instance"""# get the Excel application object from PyXLL and wrap itxl_window=pyxll.get_active_object()xl_app=win32com.client.Dispatch(xl_window).Application# it's helpful to make sure the gen_py wrapper has been created# as otherwise things like constants and event handlers won't work.win32com.client.gencache.EnsureDispatch(xl_app)returnxl_app_event_handlers={}def_setup_event_handler(cache):# only setup the app event handler onceifcachenotin_event_handlers:app_handler=win32com.client.DispatchWithEvents(_xl_app(),ObjectCacheApplicationEventHandler)app_handler.set_cache(cache)_event_handlers[cache]=app_handler@xl_on_reload@xl_on_closedef_delete_event_handlers(*args):# make sure the event handles are deleted now as otherwise they could still# exist for a while until the GC gets to them, which can stop Excel from closing# or result in old event handlers still running if this module is reloaded.## If you never wanted to reload this module, you could just import it from another# module loaded by pyxll and remove it from the pyxll.cfg and remove the# @xl_on_reload callback.#global_event_handlershandlers=_event_handlers.values()_event_handlers={}whilehandlers:handler=handlers.pop()delhandler

Developer Tools

Debugging with Eclipse and PyDev

This example shows how to attach the PyDev interactive debugger to Python code running
in PyXLL.

This module must be loaded by PyXLL by adding it to the modules list in the pyxll config.

"""PyXLL Examples: eclipse_debug.pyPyDev can be used to interactively debug Python code runningin Excel via PyXLL.Before using this script you must have Eclipse and PyDevinstalled:http://www.eclipse.org/http://pydev.org/To be able to attach the PyDev debugger to Excel and youPython code open the PyDev Debug perspective in Eclipseand start the PyDev server by clicking the toolbarbutton with a bug and a small P on it (hover over for thetooltip).Any python process can now attach to the PyDev debugserver by importing the 'pydevd' module included as partof PyDev and calling pydevd.settrace()This module adds an Excel menu item to attach to thePyDev debugger, and also an Excel macro so that thisscript can be run outside of Excel and call PyXLL toattach to the PyDev debugger.See http://pydev.org/manual_adv_remote_debugger.htmlfor more details about remote debugging using PyDev."""importsysimportosimportloggingimporttimeimportglob_log=logging.getLogger(__name__)#### UPDATE THIS TO MATCH WHERE YOU HAVE ECLIPSE AND PYDEV INSTALLED#### The following code tries to guess where Eclipse is installedeclipse_roots=[r"C:\"Program Files*\Eclipse"]if"USERPROFILE"inos.environ:eclipse_roots.append(os.path.join(os.environ["USERPROFILE"],".eclipse","org.eclipse.platform_*"))foreclipse_rootineclipse_roots:pydev_src=os.path.join(eclipse_root,r"plugins\org.python.pydev.debug_*\pysrc")paths=glob.glob(pydev_src)ifpaths:paths.sort()_log.info("Adding PyDev path '%s' to sys.path"%paths[-1])sys.path.append(paths[-1])breakdefmain():importwin32com.client# get Excel and call the macro declared belowxl_app=win32com.client.GetActiveObject("Excel.Application")xl_app.Run("attach_to_pydev")## PyXLL function for attaching to the debug server#try:frompyxllimportxl_menu,xl_macro,xlcAlert# if this doesn't import check the paths abovetry:importpydevdimportpydevd_tracingexceptImportError:_log.warn("pydevd failed to import - eclipse debugging won't work")_log.warn("Check the eclipse path in %s"%__file__)raisetry:importthreadingexceptImportError:threading=None# this creates a menu item and a macro from the same function@xl_menu("Attach to PyDev")@xl_macro()defattach_to_pydev():# remove any redirection from previous debuggingifgetattr(sys,"_pyxll_pydev_orig_stdout",None)isNone:sys._pyxll_pydev_orig_stdout=sys.stdoutifgetattr(sys,"_pyxll_pydev_orig_stderr",None)isNone:sys._pyxll_pydev_orig_stderr=sys.stderrsys.stdout=sys._pyxll_pydev_orig_stdoutsys.stderr=sys._pyxll_pydev_orig_stderr# stop any existing PyDev debuggerdbg=pydevd.GetGlobalDebugger()ifdbg:dbg.FinishDebuggingSession()time.sleep(0.1)pydevd_tracing.SetTrace(None)# remove any additional info for the current threadifthreading:try:delthreading.currentThread().__dict__["additionalInfo"]exceptKeyError:passpydevd.SetGlobalDebugger(None)pydevd.connected=Falsetime.sleep(0.1)_log.info("Attempting to attach to the PyDev debugger")try:pydevd.settrace(stdoutToServer=True,stderrToServer=True,suspend=False)exceptException,e:xlcAlert("Failed to connect to PyDev\n""Check the debug server is running.\n""Error: %s"%e)returnxlcAlert("Attatched to PyDev")exceptImportError:passif__name__=="__main__":sys.exit(main())

Reloading and importing modules

This example shows how to use the pyxll_reload and pyxll_rebind commands
from outside Excel to reload and import modules in PyXLL. This can be useful when combined
with calling this or a similar script from an editor.

This module must be loaded by PyXLL by adding it to the modules list in the pyxll config.

"""PyXLL Examples: reload.pyThis script can be called from outside of Excel to load andreload modules using PyXLL.It uses win32com (part of pywin32) to call into Excel to two built-inPyXLL Excel macros ('pyxll_reload' and 'pyxll_rebind') and anothermacro 'pyxll_import_file' defined in this file.The PyXLL reload and rebind commands are only available in developer mode,so ensure that developer_mode in the pyxll.cfg configuration is set to 1.Excel must already be running for this script to work.Example Usage:# reload all modulespython reload.py# reload a specific modulepython reload.py <filename>"""importsysimportosimportcPickleimportlogging_log=logging.getLogger(__name__)defmain():# pywin32 must be installed to run this scripttry:importwin32com.clientexceptImportError:_log.error("*** win32com.client could not be imported ***")_log.error("*** tools.reload.py will not work ***")_log.error("*** to fix this, install the pywin32 extensions. ***")return-1# any arguments are assumed to be filenames# of modules to reloadfilenames=Noneiflen(sys.argv)>1:filenames=sys.argv[1:]# this will fail if Excel isn't runningxl_app=win32com.client.GetActiveObject("Excel.Application")# load the modules listed on the command line by# calling the macro defined in this file.iffilenames:forfilenameinfilenames:filename=os.path.abspath(filename)print"re/importing %s"%filenameresponse=xl_app.Run("pyxll_import_file",filename)response=cPickle.loads(str(response))ifisinstance(response,Exception):raiseresponse# once all the files have been imported or reloaded# call the built-in pyxll_rebind macro to update the# Excel functions without reloading anything elsexl_app.Run("pyxll_rebind")print"Rebound PyXLL functions"else:# call the built-in pyxll__reload macroxl_app.Run("pyxll_reload")print"Reloaded all PyXLL modules"## in order to be able to reload particular files we add# an Excel macro that has to be loaded by PyXLL#try:frompyxllimportxl_macro@xl_macro("string filename: string")defpyxll_import_file(filename):""" imports or reloads a python file. Returns an Exception on failure or True on success as a pickled string. """# keep a copy of the path to restore latersys_path=list(sys.path)try:# insert the path to the pythonpathpath=os.path.dirname(filename)sys.path.insert(0,path)try:# try to load/reload the modulebasename=os.path.basename(filename)modulename,ext=os.path.splitext(basename)ifmodulenameinsys.modules:module=sys.modules[modulename]reload(module)else:__import__(modulename)exceptException,e:# return the pickled exceptionreturncPickle.dumps(e)finally:# restore the original pathsys.path=sys_pathreturncPickle.dumps(True)exceptImportError:passif__name__=="__main__":sys.exit(main())