XLLoop is an open source framework for implementing Excel user-defined functions (UDFs) on a centralised server
(a function server).
Why is this useful?
In JSON mode the addin and server communicate over http(s), sending and receiving JSON serialised excel objects. XLLoop is licensed under the Common Public License (CPL). |
About
Download
The latest download is available from the Project Page.
The source code for XLLoop has moved to GitHub. Git patches are now the preferred way of accepting patches (bug fixes and enhancements).
The source code for XLLoop has moved to GitHub. Git patches are now the preferred way of accepting patches (bug fixes and enhancements).
The following table summarizes the current support for different languages.
Language | Supported? |
---|---|
Java | Yes, native server (binary and json) |
C++ | Yes, via XLLServer (included in download) |
Erlang | Yes, native server |
Lisp | Yes, using Jatha |
Ruby | Yes, a native ruby server is included in the download. |
Python | Yes, a native python server is included in the download. |
PHP | Yes, a native server (json) is included in the download. |
Javascript | Yes, using Rhino |
R | Yes, a native R server is included in the download. |
Perl | Yes, a native Perl server has been implemented here: github.com/aero/XLLoop-perl5 |
Usage
An example of a very simple server that exposes the methods in the java.lang.Math class:
package org.boris.xlloop.util; import org.boris.xlloop.FunctionServer; import org.boris.xlloop.handler.*; import org.boris.xlloop.reflect.*; public class ServerExample { public static void main(String[] args) throws Exception { // Create function server on the default port FunctionServer fs = new FunctionServer(); // Create a reflection function handler and add the Math methods ReflectFunctionHandler rfh = new ReflectFunctionHandler(); rfh.addMethods("Math.", Math.class); rfh.addMethods("Math.", Maths.class); rfh.addMethods("CSV.", CSV.class); rfh.addMethods("Reflect.", Reflect.class); // Create a function information handler to register our functions FunctionInformationHandler firh = new FunctionInformationHandler(); firh.add(rfh.getFunctions()); // Set the handlers CompositeFunctionHandler cfh = new CompositeFunctionHandler(); cfh.add(rfh); cfh.add(firh); fs.setFunctionHandler(new DebugFunctionHandler(cfh)); // Run the engine System.out.println("Listening on port " + fs.getPort() + "..."); fs.run(); } }This will expose all the java.lang.Math functions inside excel. Excel usage is as follows:
=FS("Math.random") =Math.random() =FS("Math.sin", 3.14) =Math.sin(3.14) =FS("Math.pow", 2, 4.45) =Math.pow(2, 4.45)The addin can be customized using an INI file with the same name as the addin. Eg. (xlloop.ini). The INI file can accept the following settings:
Key | Description |
---|---|
protocol | Specifies the protocol to use ("binary" or "http"). Defaults to binary. |
server | When in binary mode, this is a list of servers to connect to (eg. "server=localhost:5454,localhost:5455 ") |
server.selection.mode | Set this to "round-robin" to use a connect to servers in the order which they appear in the server list (the default is random). |
server.retry.count | This will cause the addin to attempt to connect to subsequent servers in the list if the first connection attempt fails |
url | The URL to connect to when in "http" mode (eg. "url=http://localhost:8080/functionserver ") |
addin.name | This is the name that will appear in the Excel addin manager window (default is addin filename without extension). |
function.name | This is the name of the function that will be registered in excel (default is "FS"). |
include.volatile | This is a switch to include a volatile version of the "FS" function. |
function.name.volatile | This is the name of the volatile version of the function (default is "FSV"). |
generic.function.category | The category under which the generic functions will be registered (default is "General"). |
disable.function.list | Set this to "true" to switch off dynamic excel function registration. |
send.user.info | Set this to "true" to send username/hostname to server when session initializes (default is true). |
user.key | A string that is sent as the third argument to the initialize function on session startup. |
send.caller.info | Set this to "true" to send sheet/cell information to each function (default is false). |
disable.calc.popup | Set this to "true" to disable the popup. |
disable.calc.cancel | Set this to "true" to disable to 'click to cancel' feature. |
ini.file.location | The addin will include INI keys from the file location specified (eg "C:\Program Files\MyApp\include.ini ") |
ini.registry.location | The addin will include INI keys from the registry location specified (eg "HKEY_CURRENT_USER\Software\MyApp "). Currently only string and DWORD values are supported |
Javadoc
The javadoc for the server framework can be found here.
Popup For Long Running Operations
The following screenshot shows the popup in action:
The popup will appear after receiving no response from the server for a couple of seconds. It will automatically disappear when the server responds. The user can click the popup to terminate the connection to the server (it will automatically reconnect on the next function call).
Note that this feature can be disabled via the INI file (see table above).
The popup will appear after receiving no response from the server for a couple of seconds. It will automatically disappear when the server responds. The user can click the popup to terminate the connection to the server (it will automatically reconnect on the next function call).
Note that this feature can be disabled via the INI file (see table above).
Function Information Registration
The addin can register function information/help with Excel on startup. This is
is achieved by calling a special function on the server called
The server responds with an array of FunctionInformation objects (which are encoded as arrays).
The addin then calls the
Note that the Java server framework has a handler called
The following code example shows how to uses annotations to add function information when using the
org.boris.xlloop.GetFunctions
.
The server responds with an array of FunctionInformation objects (which are encoded as arrays).
The addin then calls the
Excel4(xlfRegister, ...)
function to register each function.
The user will then see the help information via the Insert->Function menu.
Note that the Java server framework has a handler called
FunctionInformationFunctionHandler
,
which accepts FunctionInformation
objects. This handler implements the special function above. For
an example see the ServerExample
class in the Java source.
The following code example shows how to uses annotations to add function information when using the
ReflectFunctionHandler
handler:
package org.boris.xlloop; import java.io.File; import org.boris.xlloop.reflect.XLFunction; public class AnnotationsTest { @XLFunction(name = "ListFiles", help = "List the files contained within a directory", args = { "dir" }, argHelp = { "The directory" }, category = "Files") public static String[] listFiles(String dir) { return new File(dir).list(); } }
Lisp Example
The following page shows an example lisp function handler.
Bean Scripting Framework Support
The following example shows a server that exposes all scripts within a directory (and sub directories) as
functions:
package org.boris.functionserver; import java.io.File; import org.boris.functionserver.reflect.ReflectFunctionHandler; import org.boris.functionserver.script.ScriptRepository; import org.boris.functionserver.util.CompositeFunctionHandler; public class ServerTest { public static void main(String[] args) throws Exception { FunctionServer fs = new FunctionServer(); ReflectFunctionHandler rfh = new ReflectFunctionHandler(); ScriptRepository srep = new ScriptRepository(new File("functions"), "Script."); rfh.addMethods("Math.", Math.class); CompositeFunctionHandler cfh = new CompositeFunctionHandler(); cfh.add(rfh); cfh.add(srep); fs.setFunctionHandler(cfh); fs.run(); } }An example script called "mult.js", written in javascript simply multiplies the two numbers entered:
args[0] * args[1];This can be called from Excel with the configuration above as follows:
=FS("Script.mult", 45.3, 23) =Script.mult(45.3, 23)
Multiple Server Support
The binary protocol supports connecting to multiple servers to improve reliability and
scalability.
The approach is simple; it connects to a random server, if this fails it reports an error to the user. If the user attempts to connect again (ie. by invoking another function) the addin will choose a random server to connect to. The following example shows an INI file with multiple servers specified.
The approach is simple; it connects to a random server, if this fails it reports an error to the user. If the user attempts to connect again (ie. by invoking another function) the addin will choose a random server to connect to. The following example shows an INI file with multiple servers specified.
server=localhost,localhost:5455,myserver:9000If a port is not specified it uses the default of 5454.
JSON over HTTP Protocol
The addin supports a JSON over HTTP protocol. Each function invocation is a separate HTTP request.
The input data is sent via a POST method. The result is a single xloper object encoded in JSON.
To enable this feature setup the INI file as follows:
protocol=http url=http://localhost:8000/FunctionServerThe JSON input request looks like:
{ "args": [], "name": "Math.random", "request": "XLLoop", "version": "0.1.0" }The result JSON looks like:
{ "num": 0.8091614905358369, "type": 1 }
Multiple Function Providers
XLLoop can be configured to connect to multiple function servers. The following shows an example of the INI file configuration to achieve this:
This will setup connections to multiple servers. The
providers=Java,Python,Ruby,PHP include.volatile=false [Java] server=localhost [Python] server=localhost:5460 function.name=PY [Ruby] server=localhost:5470 function.name=RB [Erlang] server=localhost:5480 function.name=ERL [PHP] protocol=http url=http://xlloop.sourceforge.net/servers/php/TestServer.php send.caller.info=true function.name=LPEach provider specified in the
providers
property (comma-separated list) refers to a section in the INI file. Each section accepts all the INI properties listed in the table above (apart from global properties).
This will setup connections to multiple servers. The
function.name
property is used to set the generic function name registered for that server. For example, to invoke a python function from the server at localhost:5460
you could write the following formula:
=PY("ArgsTest", "Hello World!")Note that difference between configuring multiple servers for one function provider and configuring mutiple function providers. Multiple function server instances serve up the same functions whereas multiple function providers each provide a different set of functions. Also note that no attempt is made to reconcile function name clashes between function providers.
Change History
V0.3.2
- Added support for multiple servers (providers)
- Moved source code to github and refactored addin source folders
- Added round-robin connection mode for multiple servers in binary mode
- Added retry ability to binary mode
- Added annotation support for function information to reflection-based java functions
- Java server framework now requires Java 1.5 (for annotations)
- Generic function category can be customized
- added ability to load INI keys from an external file
- added ability to load INI keys from the registry
- Added JSON over HTTP(S) protocol support
- Added native PHP server
- Added support for multiple servers in binary protocol mode
- Functions can now support up to 30 arguments (and variable length)
- Added ability to send caller information (sending sheet and cell)
- Improvements in reliability of binary protocol
- Addin name now defaults to the filename (minus .xll)
- Added an Initialize call when addin first connects. This can send user information (for initializing a user session)
- Fixed build issue with 0.2.1 release
- Fixed marshalling issues with empty/long strings
- Added popup to client for long running operations (with option to cancel request)
- Added native Ruby server
- Added native Python server
- Added native R server
- Fixed native Erlang server
- Buffered streams on java server for improved performance
- Improvements to reliability on binary protocol
- Support up to 512 registered functions
- Reduced xll size by using wcrt