Registering Functions

XLLoop provides two mechanisms for calling functions on a server:

1. A generic function (“FS” by default) that takes a function name and then a variable number of arguments.
2. A dynamic function registration mechanism.

The function registration mechanism works by calling the function server (on excel startup or activation of the add-in) with a “GetFunctions” generic request. The server (ie. your server) implements this request handler and returns a collection of function definitions encoded as variants.

The easiest way to return the correct response is to use the FunctionInformation class. This provides an encode method that generates the format required for a single function. Simply add the encoding of multiple FunctionInformation objects to a VTCollection class and return this as a result. The follow code snippet shows the detail:

VTCollection collection = new VTCollection();
FunctionInformation fi = new FunctionInformation("Math.pow");
fi.setFunctionHelp("Raises the first value to the power of the second");
fi.setCategory("Maths");
fi.addArgument("value", "The first value");
fi.addArgument("power", "The power value");
collection.add(fi.encode());

This will expose “Math.pow” as a function in excel. When this function is called from excel, XLLoop (the XLL) will call your function handler, passing “Math.pow” as the function name and the arguments as a VTCollection.

About

XLLoop is an open source framework for implementing excel functions (ie. UDFs) outside the excel process. It consists of two components:

1. An excel add-in (XLL) written in C++.
2. A server process (there is one written in Java and another written in Erlang).

This provides a mechanism for implementing excel functions in Java and any JVM supported language (and also in Erlang).

The project lives at sourceforge at http://xlloop.sourceforge.net

The add-in and server communicate via TCP sockets, using a binary protocol (for efficiency). The protocol sends and receives variant structures that are very similar to the xloper structure native to excel. There are two types of requests the add-in makes to the server:

• A generic request: This sends the request type as an integer, the request name as a string and then the request arguments as a variant map/struct. The response is a variant of any type.
• A function request: This sends the request type as an integer, the function name as a string and then the function arguments as a variant collection. The response is a variant of any type.

Out of the box, the add-in registers a single function with excel. The function is “FS” (short for function server). This generic function takes a variable number of arguments with the first argument as the function name. The invocation of this function translates to a function request as above. The server responds with a variant result and this is translated into an xloper object, which is then displayed by excel.