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[5] in Chapter 3, Using XPath in XMLmind XML Editor - Developer's Guide in order to be able to write such functions.

The easiest way to create such custom spreadsheet functions is to use our project creation “wizard” in XMLmind XML Editor - Developer's Guide. You'll have to download and unpack the developer's documentation in order to be able to use this wizard. The developer's documentation and samples may be downloaded 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/spreadsheet/custom_functions/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/.

<com.xmlmind.xmleditext.spreadsheet.SpreadsheetFunctionsInfo 
  xmlns="http://www.xmlmind.com/xmleditor/schema/configuration/extension"
  location="myspreadsheetfunctions.xml" />

After doing that, restart XXE, clear the Quick Start cache (OptionsPreferences, Advanced|Cached Data section in XMLmind XML Editor - Online Help), then restart XXE one more time. If you forget to do that, XXE will fail to see your extension.



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