Function Handling
This section describes the services which handle spreadsheet functions.
Calculating Function Results
The com.sun.star.sheet.FunctionAccess service calls any spreadsheet function and gets its result without having to insert a formula into a spreadsheet document.
The service can be instantiated through the service manager. The com.sun.star.sheet.XFunctionAccess interface contains only one method, callFunction()
. The first parameter is the name of the function to call. The name has to be the function's programmatic name.
- For a built-in function, the English name is always used, regardless of the application's UI language.
- For an add-in function, the complete internal name that is the add-in component's service name, followed by a dot and the function's name as defined in the interface. For the
getIncremented
function in the example from the add-in section, this would be: "com.sun.star.sheet.addin.ExampleAddIn.getIncremented
".
The second parameter to callFunction()
is a sequence containing the function arguments. The supported types for each argument are described in the com.sun.star.sheet.XFunctionAccess interface description, and are similar to the argument types for add-in functions. The following example passes two arguments to the ZTEST
function, an array of values and a single value.
// --- Calculate a function ---
Object aFuncInst = xServiceManager.createInstance("com.sun.star.sheet.FunctionAccess");
com.sun.star.sheet.XFunctionAccess xFuncAcc = (com.sun.star.sheet.XFunctionAccess)
UnoRuntime.queryInterface(com.sun.star.sheet.XFunctionAccess.class, aFuncInst);
// put the data into a two-dimensional array
double[][] aData = {{1.0, 2.0, 3.0}};
// construct the array of function arguments
Object[] aArgs = { aData, new Double( 2.0 ) };
Object aResult = xFuncAcc.callFunction("ZTEST", aArgs);
System.out.println("ZTEST result for data {1,2,3} and value 2 is "
+ ((Double)aResult).doubleValue());
In Calc, an argument of the function may accept a range of cells, e.g =SUM(B12:G55)
. When calling a spreadsheet function, a range argument is transmitted as an array with two dimensions. The first corresponds to the row, the second corresponds to the column, indexes are zero-based.
The implementation of com.sun.star.sheet.FunctionAccess uses the same internal structures as a spreadsheet document, therefore it is bound by the same limitations, such as the limit of rows and columns for the function arguments. Version 3.3 of Apache OpenOffice supports 1048576 rows and 1024 columns. |
Information about Functions
The services com.sun.star.sheet.FunctionDescriptions and com.sun.star.sheet.FunctionDescription provide help texts about the available spreadsheet cell functions, including add-in functions and their arguments. This is the same information that OpenOffice.org API Calc displays in the function AutoPilot.
The com.sun.star.sheet.FunctionDescriptions service is instantiated through the service manager. It provides three different methods to access the information for the different functions:
- By name through the com.sun.star.container.XNameAccess interface.
- By index through the com.sun.star.container.XIndexAccess interface.
- By function identifier through the com.sun.star.sheet.XFunctionDescriptions interface's
getById()
method. The function identifier is the same used in the com.sun.star.sheet.RecentFunctions service.
The com.sun.star.sheet.FunctionDescription that is returned by any of these calls is a sequence of com.sun.star.beans.PropertyValue structs. To access one of these properties, loop through the sequence, looking for the desired property's name in the Name member. The Arguments property contains a sequence of com.sun.star.sheet.FunctionArgument structs, one for each argument that the function accepts. The struct contains the name and description of the argument, as well as a boolean flag showing if the argument is optional.
All of the strings contained in the com.sun.star.sheet.FunctionDescription service are to be used in user interaction, and therefore translated to the application's UI language. They cannot be used where programmatic function names are expected, for example, the com.sun.star.sheet.FunctionAccess service. |
The Recently Used Functions section below provides an example on how to use the com.sun.star.sheet.FunctionDescriptions service.
Recently Used Functions
The com.sun.star.sheet.RecentFunctions service provides access to the list of recently used functions of the spreadsheet application, that is displayed in the AutoPilot:Functions and the Function List window for example.
The service can be instantiated through the service manager. The com.sun.star.sheet.XRecentFunctions interface's getRecentFunctionIds()
method returns a sequence of function identifiers that are used with the com.sun.star.sheet.FunctionDescriptions service. The setRecentFunctionIds()
method changes the list. If the parameter to the setRecentFunctionIds()
call contains more entries than the application handles, only the first entries are used. The maximum size of the list of recently used functions, currently 10, can be queried with the getMaxRecentFunctions()
method.
The following example demonstrates the use of the com.sun.star.sheet.RecentFunctions and com.sun.star.sheet.FunctionDescriptions services. <syntaxhighlight lang="java">
// --- Get the list of recently used functions --- Object aRecInst = xServiceManager.createInstance("com.sun.star.sheet.RecentFunctions"); com.sun.star.sheet.XRecentFunctions xRecFunc = (com.sun.star.sheet.XRecentFunctions) UnoRuntime.queryInterface(com.sun.star.sheet.XRecentFunctions.class, aRecInst); int[] nRecentIds = xRecFunc.getRecentFunctionIds(); // --- Get the names for these functions --- Object aDescInst = xServiceManager.createInstance("com.sun.star.sheet.FunctionDescriptions"); com.sun.star.sheet.XFunctionDescriptions xFuncDesc = (com.sun.star.sheet.XFunctionDescriptions) UnoRuntime.queryInterface(com.sun.star.sheet.XFunctionDescriptions.class, aDescInst); System.out.print("Recently used functions: "); for (int nFunction=0; nFunction<nRecentIds.length; nFunction++) { com.sun.star.beans.PropertyValue[] aProperties = xFuncDesc.getById(nRecentIds[nFunction]); for (int nProp=0; nProp<aProperties.length; nProp++) if (aProperties[nProp].Name.equals("Name")) System.out.print(aProperties[nProp].Value + " "); } System.out.println();
</<syntaxhighlight>
Content on this page is licensed under the Public Documentation License (PDL). |