3. Defining custom spreadsheet functions

3.1. Registering custom spreadsheet functions with XXE

Custom spreadsheet functions must be defined in an XML document conforming to (XMLmind proprietary) W3C XML Schema having http://www.xmlmind.com/xmleditor/schema/spreadsheet/functions as its target namespace.

Example, myspreadsheetfunctions.xml (found in XXE_install_dir/doc/configure/samples2/)

<?xml version="1.0" encoding="ISO-8859-1"?>
<f:functions xmlns="http://www.w3.org/1999/xhtml"
             xmlns:ns="http://www.w3.org/1999/xhtml"
             xmlns:f="http://www.xmlmind.com/xmleditor/schema/spreadsheet/functions">
  <f:function>
    <f:name>factorial</f:name>
    <f:parameters>n</f:parameters>
    <f:category>Mathematical</f:category>
    <f:description>
      <body>
        <p>Returns the factorial of <i>n</i>.</p>
      </body>
    </f:description>
    <f:macro><![CDATA[= if(n = 1, 1, n*factorial(n-1))]]></f:macro>
  </f:function>
</f:functions>

This XML document must be referenced in an XXE configuration file using configuration element spreadsheetFunctions.

  • If you add this to XXE_user_preferences_dir/addon/customize.xxe (after copying myspreadsheetfunctions.xml to XXE_user_preferences_dir/addon/):

    <spreadsheetFunctions location="myspreadsheetfunctions.xml" />

    you'll be able to use custom function factorial() whatever is the type of the document that you open in XXE. That is, function factorial() and its documentation will always show up in the Formula Editor, just like sin() or cos().

  • If you add this to an XXE configuration file which is specific to an XML application, for example XXE_install_dir/addon/config/docbook/docbook.xxe (after copying myspreadsheetfunctions.xml to XXE_install_dir/addon/config/docbook/):

    <spreadsheetFunctions location="myspreadsheetfunctions.xml" />

    you'll be able to use custom function factorial() only when you open a DocBook document.

3.2. Specifying custom spreadsheet functions

The easiest way to create documents conforming to the http://www.xmlmind.com/xmleditor/schema/spreadsheet/functions schema, is to use XXE after installing the configuration found in XXE_install_dir/doc/configure/functions_config/.

In order to do this, recursively copy directory XXE_install_dir/doc/configure/functions_config/ to XXE_user_preferences_dir/addon/ and restart XXE. Then, use File|New and choose Spreadsheet Functions|Template.

Figure 1. Custom function factorial() edited in XXE using the Spreadsheet Functions configuration

Custom function factorial() edited in XXE using the Spreadsheet Functions configuration

The content model of the documents used to specify spreadsheet functions is:

<functions>
  Content: function+
</functions>

<function>
  Content: name parameters category description
           ( macro | method | runtime | intrinsic )
</function>

<name>
  Content: function name (any combination of letter, digit and _
           cannot start with digit or _)  
</name>

<parameters>
  Content: [ parameter name (any combination of letter, digit and _
             cannot start with digit) [ ?|*|+ ]? ]*
</parameters>

<category>
  Content:  non empty token
</category>

<description>
  Content:  an XHTML body (restrict yourself to
            HTML 3.2)
</description>

<macro
  xml:space = preserve
>
  Content: = definition of the function using the
             spreadsheet language
</macro>

<method>
  Content: a Java fully qualified method name
           (ASCII only)
</method>

<runtime
/>

<intrinsic
/>
name

Name of the custom spreadsheet function.

parameters

Specifies the name and the number of the formal parameters of the custom spreadsheet function.

Function factorial has a single, mandatory, formal parameter called n. Note that parameter n is referenced by its name in the specification of the macro-function (=if(n=1,1,n*factorial(n-1))).

The parameters element is as important as the ``formula'' of the custom spreadsheet function because it is used in many places. For example, it is used by the formula parser to check the number of arguments passed to functions (i.e. using factorial(3, 4) will cause the parse to report an error).

Examples:

  • For standard spreadsheet function and: boolean1 boolean2+

  • For standard spreadsheet function numbervalue: text format? locale?

  • For standard spreadsheet function sumif: nodeset test sum_nodeset?

  • For standard spreadsheet function max: value+

  • For standard spreadsheet function today: nothing at all: empty parameters element.

  • For standard spreadsheet function if: test1 value1 alternative* fallback

Without an occurrence specifier, a single argument must be passed for that parameter. Occurrence specifiers are:

?

0 or 1 argument corresponding to that parameter.

*

0 or more arguments corresponding to that parameter.

+

1 or more arguments corresponding to that parameter.

category

Category of the custom spreadsheet function.

You can use any of the predefined categories: Logical, Mathematical, Text, etc, or you can define your own categories.

This category is used by the Formula Editor.

description

Documentation in XHTML (restrict yourself to the HTML 3.2 subset) of the custom spreadsheet function.

This documentation is displayed by the Formula Editor.

macro

Specifies the custom spreadsheet function using the spreadsheet language (macro-function).

This specification must start with =.

This specification can reference the parameter names declared in the parameters sibling.

This specification can reference any other spreadsheet function, including itself (recursive macro-function).

method

Specifies the fully qualified name of the Java™ method used to implement the custom spreadsheet function.

The method name must be the name of the custom spreadsheet function, after converting it to lower case.

If the custom spreadsheet function has a name which is a reserved Java™ keyword (example: standard function char), the method name must be '_' (underscore) followed by the lower-case name of the custom spreadsheet function (example: com.xmlmind.xmleditapp.spreadsheet.FunctionLibrary._char).

More on this in next section.

runtime

Reserved to XMLmind. Do not use.

intrinsic

Reserved to XMLmind. Do not use.

3.3. Custom spreadsheet functions written in the Java™ programming language

Custom spreadsheet functions written in the Java™ programming language are implemented using static methods having this signature:

import com.xmlmind.xmledit.doc.XNode;
import com.xmlmind.xmledit.xpath.Variant;
import com.xmlmind.xmledit.xpath.VariantExpr;
import com.xmlmind.xmledit.xpath.EvalException;
import com.xmlmind.xmledit.xpath.ExprContext;

public static Variant method_name(VariantExpr[] args, XNode node,
                                  ExprContext context) throws EvalException;

There is not much to say about the above static methods. You'll need to read the chapter describing XPath programming[4] in XMLmind XML Editor - Developer's Guide in order to be able to write such functions.

You'll find a template for spreadsheet functions in XXE_install_dir/doc/dev/templates/FunctionLibraryTemplate.java. You'll find a sample static method in XXE_install_dir/doc/dev/samples/MySpreadsheetFunctions.java. (Download developer's documentation and samples from www.xmlmind.com/xmleditor/download.shtml.)

Example:

public final class MySpreadsheetFunctions {
    public static Variant capitalize(VariantExpr[] args, XNode node,
                                     ExprContext context)
        throws EvalException {
        if (args.length != 1)
            throw new EvalException("bad number of arguments");

        String string = args[0].eval(node, context).convertToString();
        int length = string.length();
        String transformed;

        if (length == 0)
            transformed = string;
        else if (length == 1)
            transformed = string.toUpperCase();
        else
            transformed = (Character.toUpperCase(string.charAt(0)) + 
                           string.substring(1));

        return new StringVariant(transformed);
    }
}

This spreadsheet function needs to be declared in myspreadsheetfunctions.xml as follows:

The code of the capitalize spreadsheet function is found in XXE_install_dir/doc/configure/samples2/myspreadsheetfunctions.jar. Copy this jar file to one of the directories scanned by XXE at startup-time.

For example, add this to XXE_user_preferences_dir/addon/customize.xxe, after copying both myspreadsheetfunctions.jar and myspreadsheetfunctions.xml to XXE_user_preferences_dir/addon/.

<spreadsheetFunctions location="myspreadsheetfunctions.xml" />


[4] Remember that the spreadsheet language used by XMLmind XML Editor is basically an easy-to-learn syntax for XPath expressions.