ExPy Add-In Examples

Example Spreadsheets

• Simple examples spreadsheet

Simplest example: define a Python function and evaluate it

This is essentially the simplest possible example: we define a simple function which returns the square of its argument. It is easiest to type the source code in a cell by itself and then evaluate it from another cell. Hence:

def f1(x):
     return x*x

Note that the multi-row cells can be entered by pressing Alt-Enter to get a line break while editing a cell. Here we use the ExPyScript function since we are interested in the side-effect of this evaluation (the definition of the function, which we use later) rather than the result of evaluation.

Once cell B1 is evaluated, the function f1 will be defined in the __main__ Python namespace in exactly the equivalent way as if the contents of cell A1 were in a source code file and this file was execfile from with the Python interpreter.

In order to make use of the function to carry out a computation we use the ExPyEvalSS function which is evaluates Python code for its return value. Here is an example:

Like you'd expect, this evaluates to

Importing modules

Modules must be imported as normal before use. For example:

import math

Note that again we use the ExPyScript function as importing a module is done for its side-effect.

All of the standard Python modules come supplied with the ExPy Add-in, packaged together in the single dll file. If you'd like to use your own modules, you will need to ensure that they are in the Python search path. The search path is specified as normal by the sys.path variable and can be adjusted in the usual way. For example, to add c:\pymods to the search path you can use the following:

import sys
sys.path.append("C:\\pymods")

Once you have loaded your modules you can use the functions and data in them as usual. For example:

math.asin(math.pi/4)

evaluates to:

math.asin(math.pi/4)

Finally, if you not want to import files as modules, but simply execute them in the top level namespace, then execfile works as you'd expect:

execfile("C:\\temp\t1.py")

Executes the script at c:\temp\t1.py

Interacting with other DLLs and the Windows API

The Add-in comes with the ctypes module. This means that it can easily interact with any other DLL or the windows API itself. For example, this code will call the standard Win API Message Box:

import ctypes
ctypes.windll.user32.MessageBoxA(0,
                                 "Your text here",
                                 "Your title here",
                                 0x40)

Introspection

Python objects and namespaces can be introspected as usual. For example, to see the content of the math module (the module must have been previously imported):

dir(math)

Reflection of functions into Excel

Simple Python functions can be called directly from within Excel by registering them using the ExPy.register function. In reflected functions, all parameters are passed in as xlltypes.XLOper objects and the returned value has to be converted to the xlltypes.XLOper. In this very simple case below we convert the input XLOper to string through the use of the str function before using it.

def stringfn(x):
     return "Got " + str(x)

ExPy.register(stringfn, 1)

This can then be used as:

=stringfn("Test!")

Which in turn evaluates to:

=stringfn("Test!")