Binding Cells

Input and output cells of a computation defined through a spreadsheet are bound to methods of plain Java interfaces or classes.

Inputs

To bind spreadsheet cells to variable input values, you must have an interface or a class defining getters for your input values. This is typically a public static inner type, defined explicitly for this purpose:

public static interface Input
{
  double getSomeValue();
  double getAnotherValue();
  int getDecade();
  double getValueForYear( int year );
  String getName();
}

In order for the interface to be accessible to the generated engine, it must be public. This is because the engine is not loaded by the same class loader as your own classes (read why this matters).

You have to provide this type to the engine builder:

EngineBuilder builder = SpreadsheetCompiler.newEngineBuilder();
builder.loadSpreadsheet( new File( path ) );
builder.setInputClass( Input.class );
builder.setOutputClass( Output.class );

In the simple usage scenario, you just passed a factory, and AFC inferred the input and output types by itself. Furthermore, AFC did all the cell binding automatically for you. Now you want to get full control over binding. To do this, you access the loaded spreadsheet, and the so called root section binder. This interface controls the binding of cells for the entire spreadsheet (as opposed to contained sections of variable extent).

Spreadsheet spreadsheet = builder.getSpreadsheet();
SpreadsheetBinder.Section binder = builder.getRootBinder();

You then tell the binder which cells are input cells, and to which getter methods they should be bound:

cell = spreadsheet.getCell( "SOME_VALUE" );
method = Input.class.getMethod( "getSomeValue" );
binder.defineInputCell( cell, method );

Since we already told the binder about Input.class, there is a shorter version for this:

cell = spreadsheet.getCell( "OTHER_VALUE" );
binder.defineInputCell( cell, "getAnotherValue" );

Parameters

You can also bind to parametrized methods. The arguments to the method are either constant values determined at compile-time of the engine, or else cell values computed by the engine. First, an example with only constant parameters:

cell = spreadsheet.getCell( "YEAR_1994" );
method = Input.class.getMethod( "getValueForYear", Integer.TYPE );
binder.defineInputCell( cell, method, 1994 );

Binding to cell values computed at run-time is equally easy (you can use any of the spreadsheet methods returning a cell reference):

cell = spreadsheet.getCell( "YEAR_x" );
method = Input.class.getMethod( "getValueForYear", Integer.TYPE );
binder.defineInputCell( cell, method, spreadsheet.getCell( "x" ) );

By supplying a CallFrame instead of just a method, you can even chain calls. In this example, we do the equivalent of getName().length(), where getName() returns a String:

cell = spreadsheet.getCell( "NAME_LENGTH" );
method = Input.class.getMethod( "getName" );
chainedMethod = String.class.getMethod( "length" );
binder.defineInputCell( cell, builder.newCallFrame( method ).chain( chainedMethod ) );

The range of supported parameter data types is documented in the topic on binding by name.

Exceptions

You cannot, as of the current version, bind to input methods throwing checked exceptions. We are thinking about extending AFC to support this in the future, provided that all bound output methods declare all the checked exceptions declared by the bound input methods.

Outputs

Outputs that the spreadsheet computes are bound to methods of an interface or a dedicated abstract class you define. This type is implemented or subclassed by the generated computation engine, which overrides all methods bound to output cells so they perform the computation specified in the spreadsheet.

Your output type is typically a static inner type. In order for it to be accessible to the generated computation, it must be public.

public static interface Output
{
  double getResult();
  double getCoefficient();
}

Like the input type, you also provide to the engine builder prior to using it:

EngineBuilder builder = SpreadsheetCompiler.newEngineBuilder();
builder.loadSpreadsheet( new File( path ) );
builder.setInputClass( Input.class );
builder.setOutputClass( Output.class );

Now that the type is in place, we can bind output cells to it (again, there is a longer and a shorter version):

cell = spreadsheet.getCell( "RESULT" );
method = Output.class.getMethod( "getResult" );
binder.defineOutputCell( cell, method );

cell = spreadsheet.getCell( "COEFF" );
binder.defineOutputCell( cell, "getCoefficient" );

Parameters

Like inputs, you can also bind outputs to parametrized methods. This is explained in detail in the topic on binding by name.

Defaults

If you supply implementations for your output methods, they serve as defaults in case they are not bound to an output cell of the spreadsheet. This is mainly useful when outputs are bound generically (by cell name, for instance).

To access input values when computing default output values, you can define a constructor that takes the input type as its sole parameter. AFC then automatically calls that constructor with the current inputs. You can also access other output values by calling their corresponding methods, of course.

public static abstract class OutputWithDefault implements Output
{
  private final Input input;

  public OutputWithDefault( Input _input )
  {
    super();
    this.input = _input;
  }

  public abstract double getResult();

  public double getCoefficient()
  {
    return this.input.getSomeValue() * 0.02;
  }
}

When used together with a factory class, you often want the factory method to use the base output interface (Output) as its return type, not the type of the implementation that provides the defaults (OutputWithDefaults). Like so:

public static interface Factory
{
  Output newInstance( Input _input );
}

AFC supports this. Simply override the output class in the EngineBuilder so it does not get inferred from the factory method. Like this:

EngineBuilder builder = SpreadsheetCompiler.newEngineBuilder();
builder.loadSpreadsheet( new File( path ) );
builder.setFactoryClass( Factory.class );
builder.setOutputClass( OutputWithDefault.class );

Generated Code

The above bindings, together with this spreadsheet:

	A	B
1	Inputs
2	Some_Value	3.4
3	Other_Value	4.5
4	Decade_x	5
5	Year_1994	20
6	Year_x	30
7	Name_Length	20
8
9	Intermediates
10	x	1950 =B4*10.0+1900.0
11
12	Outputs
13	Result	70 =SUM(B5:B7)
14	Coeff	7.9 =SUM(B2:B3)

result in the following compiled code (when not using defaults for the outputs):

package org.formulacompiler.gen;
import org.formulacompiler.runtime.Computation;
import org.formulacompiler.runtime.internal.Environment;
import org.formulacompiler.runtime.internal.Runtime_v2;
import org.formulacompiler.tutorials.BindingCells;

final class $Root implements Computation, BindingCells.Output
{
    private final BindingCells.Input $inputs;
    final Environment $environment;
    
    $Root(BindingCells.Input input, Environment environment) {
        $environment = environment;
        $inputs = input;
    }
    
    final double get$0() {
        return get$1() + get$2() + get$3();
    }
    
    public final double getResult() {
        return get$0();
    }
    
    final double get$1() {
        return $inputs.getValueForYear(1994);
    }
    
    final double get$2() {
        return $inputs.getValueForYear((int) Runtime_v2.checkDouble(get$7()));
    }
    
    final double get$3() {
        return (double) $inputs.getName().length();
    }
    
    final double get$4() {
        return get$5() + get$6();
    }
    
    public final double getCoefficient() {
        return get$4();
    }
    
    final double get$5() {
        return $inputs.getSomeValue();
    }
    
    final double get$6() {
        return $inputs.getAnotherValue();
    }
    
    final double get$7() {
        return get$8() * 10.0 + 1900.0;
    }
    
    final double get$8() {
        return (double) $inputs.getDecade();
    }
}