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
(after copying XXE_user_preferences_dir
/addon/customize.xxemyspreadsheetfunctions.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
(after copying XXE_install_dir
/addon/config/docbook/docbook.xxemyspreadsheetfunctions.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.
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
to XXE_install_dir
/doc/configure/functions_config/
and restart XXE. Then, use | and choose Spreadsheet Functions|Template.XXE_user_preferences_dir
/addon/
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 of the custom spreadsheet function.
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 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.
Documentation in XHTML (restrict yourself to the HTML 3.2 subset) of the custom spreadsheet function.
This documentation is displayed by the Formula Editor.
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).
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.
Reserved to XMLmind. Do not use.
Reserved to XMLmind. Do not use.
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
. You'll find a sample static method in XXE_install_dir
/doc/dev/templates/FunctionLibraryTemplate.java
. (Download developer's documentation and samples from www.xmlmind.com/xmleditor/download.shtml.)XXE_install_dir
/doc/dev/samples/MySpreadsheetFunctions.java
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
. Copy this jar file to one of the directories scanned by XXE at startup-time.XXE_install_dir
/doc/configure/samples2/myspreadsheetfunctions.jar
For example, add this to
, after copying both XXE_user_preferences_dir
/addon/customize.xxemyspreadsheetfunctions.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.