Function Handling

From Apache OpenOffice Wiki
Jump to: navigation, search



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.

FunctionAccess

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.

Documentation note.png 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 OpenOffice.org 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.

FunctionDescriptions

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:

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.

Documentation note.png 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.

RecentFunctions

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.

  // --- 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();
Content on this page is licensed under the Public Documentation License (PDL).
Personal tools
In other languages