Obba Documentation: Spreadsheet Functions

Contents

Introduction

Obba provides a bridge between spreadsheets and Java classes. With Obba, you can use spreadsheets as GUIs (Graphical User Interfaces) for your Java libraries. Its main features are:

• Loading of arbitrary jar or class files at runtime through a spreadsheet function.
• Instantiation of Java objects, storing the object reference under a given object label.
• Invocation of methods on objects referenced by their object handle, storing the handle to the result under a given object label.
• Asynchronous method invocation and tools for synchronization, turning your spreadsheet into a multi-threaded calculation tool.
• Serialization and de-serialization (save Serializable objects to a file, restore them any time later).
• All this through spreadsheet functions, without any additional line of code (no VBA needed, no additional Java code needed).

Handling Objects in Spreadsheets

Obba provides an object handler to instantiate Java objects and communicate with them. The concept behind Obba is described in the more theoretical paper

Fries, Christian P.: Comments on Handling Objects in Spreadsheets, http://www.christian-fries.de/objectinspreadsheets.

Spreadsheet Function Overview

Object Handling

• obAddAllJars, obAddClasses, obAddJar: loading of Java libraries/classes.
• obMake: create a primitive value or an object using its constructor.
• obCall: invoke a method on an object.
• obGet: convert an object value to a spreadsheet type.
• obRelease: release an object from the object handler.
• obReleaseDeferred: release an object from the object handler after its next use.
• obRun: asynchronously invoke a method on an object.
• obSync: synchronize the result of an asynchronously invoked method or provide block synchronization.
• obCast: change the type of an object (rarely needed).

Object Persistence and Settings

• obSave
• obLoad
• obControlPanelSetVisible:

Loading Java Libraries

Java libraries may be loaded as jar or class files. Obba provides spreadsheet functions to add jar files or class folders to the class loader of your Java virtual machine. The jar files can be added at runtime, at any point during your spreadsheet calculation. It is not necessary to have the jar files in your classpath.

Note on class loading: Classes made known to the Java virtual machine via the class path of the Obba Server are known to the classes added via one of the functions obAddAllJars, obAddJar, obAddAllJars. Classes added though one of the functions all share the same class loader, i.e., they do know each other. However, classes added through these functions are not known to the classes added via the class path of the Obba Server (in most situations this is not an issue).

obAddAllJars

Add jar file(s) in a given folder to the class loader.

Arguments:

pathToLibFolder (String)

The path to the folder containing the jar file(s).

isPathRelative (Boolean)

Optional parameter. If true, the path is relative and the current worksheet path added. If missing, isPathRelative is assumed to be false.

Return value:

absolutePath (String)

The corresponding absolute path.

obAddClasses

Add class folder to the class loader.

Arguments:

pathToClassFolder (String)

The path to the folder containing the class file(s). The path has to be a classpath (in the Java sense) not just any path to .class files, i.e., the directory layout has to correspond to the Java packages.

isPathRelative (Boolean)

Optional parameter. If true, the path is relative and the current worksheet path added. If missing, isPathRelative is assumed to be false.

Return value:

absolutePath (String)

The corresponding absolute path.

obAddJar

Add a jar file to the class loader.

Arguments:

pathToJar (String)

The path to the jar file.

isPathRelative (Boolean)

Optional parameter. If true, the path is relative and the current worksheet path added. If missing, isPathRelative is assumed to be false.

Return value:

absolutePath (String)

The corresponding absolute path.

Object Labels and Object Handles

• The object labels are assigned by the user upon creation of the object or primitive value. They specify the object's storage place.
• The object handle is generated by the object handler upon storage of an object. Object handles must be used to access an object.

The generated object handle consist of the object label plus a # character and a unique integer number (do not rely on this).

Re-using Object Labels

If you create an object under an object label that is already associated to another object, then the other object is released from the object handler and the new object is assigned the object label. A new object handle will be returned and the old object handle is no longer valid.

The released object will be marked for deletion if no other object holds a reference to it.

Object Creation

obMake

Create an object calling a constructor of the given class using the given arguments and storing it under a given object label.

Arguments:

objectLabel (String)

The label under which the object should be stored in the object handler. If objectLabel is an empty string, a transient object label is generated and returned.

className (String)

The Java class name.

parameters (type depends on className, see below)

Optional. Parameters.

... (String or Array of String, each representing an object handle)

Optional. Additional parameters. See section entitled "Parameters".

Return value:

objectHandle (String)

The object handle by which the object must be referenced when used as an argument: see section entitled "Object Labels and Object Handles"..

Parameters

Primitives and Wrapper Classes

• int: If the className is "int", obMake expects a single integer parameter and stores it as a Java int.
• double: If the className is "double", obMake expects a single double parameter and stores it as a Java double.
• boolean: If the className is "boolean", obMake expects a single boolean parameter and stores it as a Java boolean.
• String: If the className is "String", obMake expects a single String parameter and stores it as a Java String.
• byte: If the className is "byte", obMake expects a single integer parameter and stores it as a Java byte.
• short: If the className is "short", obMake expects a single integer parameter and stores it as a Java short.
• long: If the className is "long", obMake expects a single integer parameter and stores it as a Java long.
• float: If the className is "float", obMake expects a single double parameter and stores it as a Java float.
• char: If the className is "char", obMake expects a single character parameter and stores it as a Java char.
• Integer: If the className is "Integer", obMake expects a single integer parameter and stores it as a Java java.lang.Integer.
• Double: If the className is "Double", obMake expects a single double parameter and stores it as a Java java.lang.Double.
• Float: If the className is "Float", obMake expects a single double parameter and stores it as a Java java.lang.Float.
• Date: If the className is "Date", obMake expects a single double parameter representing an Excel / OpenOffice date and stores it as a Java java.lang.Date.

One Dimensional Arrays of Primitives and Wrapper Classes

• int[]: If the className is "int[]", obMake expects a range of integers. The range will be transformed to an one-dimensional array using row major. Likewise, the classNames "byte[]", "short[]", "long[]", and "Integer[]" will create a Java byte[] array, short[] array, long[] array, java.lang.Integer[] array, respectively.
• double[]: If the className is "double[]", obMake expects a range of doubles. The range will be transformed to an one-dimensional array using row major. Likewise, the className "float[]" will create a Java float[] array.
• boolean[]: If the className is "boolean[]", obMake expects a range of booleans. The range will be transformed to an one-dimensional array using row major.
• char[]: If the className is "char[]", obMake expects a range of characters. The range will be transformed to an one-dimensional array using row major.
• String[]: If the className is "String[]", obMake expects a range of Strings. The range will be transformed to an one-dimensional array using row major.

Two Dimensional Arrays of Primitives and Wrapper Classes

• int[][]: If the className is "int[][]", obMake expects a range of integers. The range will be transformed to the corresponding two-dimensional array. Likewise, the className "byte[][]", "short[][]", "long[][]" will create a Java byte[][] array, short[][] array, long[][] array, respectively.
• double[][]: If the className is "double[][]", obMake expects a range of doubles. The range will be transformed to the corresponding two-dimensional array. Likewise, className "float[][]" will create a Java float[][] array.
• boolean[][]: If the className is "boolean[][]", obMake expects a range of booleans. The range will be transformed to the corresponding two-dimensional array.
• char[][]: If the className is "char[][]", obMake expects a range of characters. The range will be transformed to the corresponding two-dimensional array.
• String[][]: If the className is "String[][]", obMake expects a range of Strings. The range will be transformed to the corresponding two-dimensional array.

Classes

If the parameter className specifies a Java class name (fully quallyfied including it's package name), an object of this class is constructed using the class's constructor having the signature specified by parameters. If the parameters are omitted, the default constructor (no arguments) is called. If not, the parameters are interpreted as described in the Section "Parameters" below and define the signature of the constructor.

Advanced feature: If className refers to an object handle of an object of type java.lang.Class, an object of this class is constructed using the class's constructor (as above).

Examples

Literals, Primitives

Classes

class java.lang.Double

class java.util.Date

Remark

Method Invocation

obCall

Call a method on a given object using the given arguments and storing its result under the given resultLabel. If resultLabel is an empty string, the result will be stored under a generated transient object label.

Arguments:

resultLabel (String)

The label to be assigned to the method's result. If objectLabel is an empty string, a transient object label is generated and returned.

objectLabel (String)

The label of the object.

methodName (String)

The method name.

parameters (String or Array of String, each representing an object handle)

Optional. Parameters. See section entitled "Parameters".

... (String or Array of String, each representing an object handle)

Optional. Additional parameters. See section entitled "Parameters".

Return value:

objectHandle (String)

The object handle by which the result object must be referenced when used as an argument: see section entitled "Object Labels and Object Handles". If the method is of void return type, the method is treated as if it would return a self-reference: the resultLabel is ignored and the object associated with objectLabel gets a new object handle. The new object handle is returned. If the method is a static void, a new Boolean(true) object is stored under the resultLabel.

• If parameters is omitted, then the method is called without parameters. Otherwise, parameters is interpreted as described in the section "Parameters" below and defines the signature of the method.
• The type of the return value is inferred from the method.
• To invoke a class method (static method) objectLabel must refer to the class name (assuming that the object handler does not have an object under this label).
• Methods may be invoked asynchronously using obRun, see below.
• To access (get) a public field, use "." + fieldname as the methodName.

Examples

Passing Parameters

The functions obMake and obCall have an optional set of parameters parameters1, parameters2, parameters3 ..., where each parameter is a string or an array of strings. From these parameters a single vector of strings is build (buy appending the elements, row major). Each string is then interpreted as an object handle defining one parameter of the constructor or method.

The advantage of this approach is that parameters may be represented by large arrays of object handles and thus there is no limit to the number of parameters. On the other hand, it is possible to split the arrays, which allows a flexible spreadsheet setup.

Examples

• If the parameters are (B5:B8), the method is called with (B5,B6,B7,B8).
• If the parameters are (B5:B6,D9,B8), the method is called with (B5,B6,D9,B8).
• If the parameters are (B5:B7,"dateObject"), the method is called with (B5,B6,B7,"dateObject").
• If the parameters are (B5:C6,A4,D6:D8), the method is called with (B5,C5,B6,C6,A4,D6,D7,D8).

Each cell referenced above must contain a valid object handle. The signature of the constructor of the method is constructed from the type of the object represented by the object handle.

Passing Literal Values

It is not possible to pass a literal in the parameter set. For example, the parameter set (B5, 0.5, B7:B8) is not admissible: you would need to use (B5, obMake("","double",0.5) , B7:B8). This is necessary so as to associate the literal value (here, 0.5) with its type (here, double). See also the section on compound function calls below. (Note: the example here uses a transient object handle).

Casting Objects

When you invoke a method with a given parameter list, the object handler searches for a matching method. If there is no exact match it will calculate a score and choose a method for which this score is the smallest. Currently, the score is the sum distance of the inheritance levels between the argument classes and the method signature. If there are more than one method with the smallest score, one of them is arbitrarily selected.

To ensure that a method with a specific signature is chosen, objects can be cast to a different type using the obCast function.

Note: In general it is rarely necessary to use obCast.

obCast

Changes the type/class of an object in the object handler and returns a new object handle to the casted object. The object handle of the original object is invalidated.

Arguments:

objectHandle (String)

The object handle of the object to be casted.

className (String)

Java class name by which the object should be casted.

Return value:

objectHandle (String)

A new object handle for the casted object.

Converting Object Values to Spreadsheet Values

obGet

Return the value of an object as a spreadsheet type.

Arguments:

objectHandle (String)

The object's handle.

Return value:

value (type depends on object type)

The object's value.

Supported Conversions to Spreadsheet Types

Primitive Types and Selected Wrapper Classes

• double, float, java.lang.Double, java.lang.Float: Converted to a Double value.
• int, byte, short, long, java.lang.Integer: Converted to an Integer value.
• boolean, java.lang.Boolean: Converted to a Boolean value.
• java.lang.String: Converted to a String value.

Arrays of Primitive Types and Selected Wrapper Classes

• One-dimensional arrays of the above types are converted to a row vector of values (use the spreadsheet function TRANSPOSE to get a column vector).
• Two-dimensional arrays of the above types are converted to a two-dimensional range of values.

Note: If you convert an array (String[], String[][], double[], double[][], etc.), then the spreadsheet function has to be treated as an array function (in Excel: mark the region where the result should appear, enter the formula and press CTRL-SHIFT-RETURN (or CTRL-SHIFT-RETURN), see Introducing array formulas in Excel).

Classes

• All other objects are converted to a String value using the objects toString method. This allows visualizing objects, e.g., collections (e.g., HashMap).

Releasing Objects

obRelease

Release an object from the object handler.

Arguments:

objectHandle (String)

The object's handle.

Return value:

objectHandle (String)

The deleted object's handle.

Building Spreadsheet Functions

Compound Function Calls

Example

The following spreadsheet formula creates a java.util.Date object, stores it under "dateObject", calls the method toString() on this object, stores the result under "dateString", and then returns the value of "dateString" as spreadsheet String:

Object Handle Checking

An object must be referenced by its object handle; using the object label is not sufficient. Thus, if the object is referenced as an argument of some worksheet function (e.g., calling a method), then the object handle must be used. Since object handles are generated, it has to be passed by referencing the corresponding cell, rather than using a literal. Object handles for the same object label (even for the same object) may change to show that the object may have been altered.

Building the correct spreadsheet cell dependencies is important. For example, a cell dependency enforces the spread sheet application to first call the function that creates the object, then calling the function that calls a method on the newly created object.

To some extend, Obba checks for the cell dependency by generating and returning a new object handle every time the object is the result of an Obba function call.

The consistency check can be disabled in the control panel. Alternatively, there is a worksheet function, obGetHandle, that can be employed to obtain an object handle for a given object label.

obGetHandel

Get the object handle for a given object label.

Arguments:

objectLabel (String)

The object label.

Return value:

objectHandle (String)

The object handle.

Example

Transient Object Handles

However, for compounding function calls it may occur that a newly assigned object handle is used only as an argument and never used again. In the above example this situation could apply to "dateString" and/or "dateObject", given that the two labels are not used anywhere else. In this case, a transient object label may be used.

To use a transient object label use an empty string as objectLabel in obMake or as resultLabel in obCall. The following will then occur:

1. The object handler will generate a unique object label.
2. The object will be stored under this label and a corresponding object handle will be returned by the function obMake or obCall.
3. The object will be released automatically, the first time (!) this generated object handle is used as a parameter in any of the functions obMake or obCall or obGet.

Example

Example: The corresponding Java code

Deferred Release - Making Object Handles Transient

obReleaseDeferred

Release an object from the object handler after it is used for the first time.

Arguments:

objectHandle (String)

The object's handle.

Return value:

transientObjectHandle (String)

The transient object handle of same object.

Concurrency

Asynchronous Method Invocation

obRun

Asynchronously calls a method on a given object using the given arguments and stores its result under the given resultLabel. If resultLabel is an empty string, the result will be stored under a generated transient object label.

Arguments:

resultLabel (String)

The label to be assigned to the method's result. If objectLabel is an empty string, a transient object label is generated and returned.

objectLabel (String)

The object's label.

methodName (String)

The method name.

parameters (String or Array of String, each representing an object handle)

Optional. Parameters. See section entitled "Parameters".

... (String or Array of String, each representing an object handle)

Optional. Additional parameters. See section entitled "Parameters".

Return value:

objectHandle (String)

The object handle by which the result object must be referenced when used as an argument: see section entitled "Object Labels and Object Handles". If the method is of void return type, then the method is treated as if it would return a self reference: the resultLabel is ignored and the object associated with objectLabel receives a new object handle. The new object handle is returned. If the method is a static void, a new Boolean(true) object is stored under the resultLabel.

• Obba provides a basic synchronization. The function obRun returns the object handle of the result. The first time this object is accessed, the thread generating it will be synchronized and the object will be assigned to the handle.
• Basic synchronization is often sufficient, but does not guarantee that there are no side effects, i.e., two concurrent threads modifying and accessing the same object. Therefore obRun must be used with caution. Use obSync to provide additional synchronization.
• The notes relating to obCall are applicable here.

Synchronization

obSync

Wait for the calculation of the given object handles.

Arguments:

parameters (String or Array of String, each representing an object handle)

Optional. Parameters. See section entitled "Parameters".

... (String or Array of String, each representing an object handle)

Optional. Additional parameters. See section entitled "Parameters".

Return value:

parameters0 (String)

The first argument will in the argument list.

Special Language Constructs

Null

Example

Null Reference of Type String

Array Access

Example

One-Dimensional Array

Two-Dimensional Array

Array Creation

Example

One Dimensional Array

Multi-Dimensional Array

Field Access

Example

Inner Classes

Example

Enums

Creating an Enum

Getting an Enum

Example

Java Tools

Obba comes with some utility Java classes which are directly accessible from the spreadsheet (using obMake, obCall) and provide convenient methods for creating some frequently used objects.

Creation of a Java Map from two Arrays.

Obba allows to create an array directly from a spreadsheet column. The class info.obba.javatools.Collections provides the static method

Example

Compiling and using a Java Class dynamically from a Source Code String

If Obba Server runs within a JDK, the javatools compiler may be used to dynamically create objects from source code passed as a string argument.

The class info.obba.javatools.Compiler provides the method

Example

The following spreadsheet function will instantiate an objects under the object label myObject of class mypackage.MyClass whose source code is contained in cell A1:

Guideline for Building Worksheets

Types of Cells, Color Scheme

• Input values or other data being input to the objects and methods.
• Object handles returned by calls to the object handler.
• Return values obtained by a call to obGet.
• Descriptive text and comments.

While the value of the first cell type is volatile in that it can possibly be changed by the spreadsheet user, the other elements are usually static, especially if the spreadsheet has gone past the development stage.

To avoid having a formula overwritten by user input and thus breaking a spreadsheet dependency tree, the different cell types above should be made distinguishable, e.g., by a scheme for background colors or fonts.

Choice of Object Labels and Cell Formatting

• Use a descriptive name as an object label.
• The object handle returned consists of the object label, followed by a newline character, followed by a unique object handle number. The part after the newline may be hidden by changing the cell format as follows:
  • Enable "Word wrap" for the cell. This will move the unique object handle number to a new line.
  • Set the cell height to one line, this will hide the additional text such that only the object label is visible.

Obba Control Panel

The Obba control panel showing for each object its label, type, creation time and last access time.

obControlPanelSetVisible

Switch the Obba Control Panel on or off.

Arguments:

visible (Boolean)

If true, the Obba Control Panel will be made visible; else invisible.

The Obba Control Panel allows viewing all objects in the object handler and permits releasing selected objects from the object handler.

Note: Releasing non-transient object handles via the Obba Control Panel is not recommended if these objects are still referenced in the spreadsheet.

Object Dependency Graph

The Obba object dependency graph.

Object Serialization and De-Serialization

obSave

Save Serializable objects to a file. If a list of object handles is given, then only those objects are saved to the file. If no list of object handles is given, then all objects are saved to the file.

Arguments:

fileName (String)

The file name under which the object should be saved to.

parameters (String or Array of String, each representing an object handle)

Optional. Parameters. See section entitled "Parameters".

... (String or Array of String, each representing an object handle)

Optional. Additional parameters. See section entitled "parameters".

Return value:

objectLabels (Array of String)

The labels of the objects saved.

obLoad

Load Serializable objects from a file. If a list of object handles is given, only those objects are loaded from the file. If no list of object handles is given, all objects are loaded from the file. Objects currently in the object handler are either left intact or replaced.

Arguments:

fileName (String)

The file name under which the object should be saved to.

parameters (String or Array of String, each representing an object handle)

Optional. Parameters. See section entitled "Parameters".

... (String or Array of String, each representing an object handle)

Optional. Additional parameters. See section entitled "Parameters".

Return value:

objectLabels (Array of String)

The labels of the objects saved.

Literature

The concept behind Obba is described in the paper

Fries, Christian P.: Comments on Handling Objects in Spreadsheets, http://www.christian-fries.de/objectinspreadsheets.

Feedback and Feature Requests