Logging Intermediate Values

Sometimes, especially when debugging, you want to track how output values of a computation are calculated. AFC allows you to register listeners for such calculations both at compile-time (for constant folding) and at run-time.

Basic Example

Sample spreadsheet

The use case is very simple. The spreadsheet computes the volume of a cube:

	A	B
1	Length	2
2	Width	3
3	Height	5
4	Area	6 =B1*B2
5	Volume	30 =B4*B3

We provide dimensions as Inputs class:

public static class Inputs
{
  public double getLength()
  {
    return 7;
  }

  public double getWidth()
  {
    return 11;
  }

  public double getHeight()
  {
    return 13;
  }
}

and receive the volume in the Outputs interface:

  public static interface Outputs
  {
    double getVolume();
  }

Compile with listener support

Listener support must be explicitly enabled when compiling the engine as it affects engine performance (even when no listener is registered):

final EngineBuilder b = SpreadsheetCompiler.newEngineBuilder();
b.loadSpreadsheet( getFile() );
b.setInputClass( Inputs.class );
b.setOutputClass( Outputs.class );
b.createCellNamesFromRowTitles();
b.bindAllByName();
b.setComputationListenerEnabled( true );
final SaveableEngine e = b.compile();

Logging computed values

The next step is creating a listener and attaching it to the computation. A listener must implement the CellComputationListener interface. It will be invoked every time the engine computes a value of a cell and will receive the cell’s address and the computed value.

A simple listener is used in this example. It puts received events in a list:

private class TestComputationListener implements CellComputationListener
{
  final List<SpreadsheetCellComputationEvent> events = New.list();

  public void cellCalculated( CellComputationEvent _event )
  {
    this.events.add( (SpreadsheetCellComputationEvent) _event );
  }
}

We pass an instance of the listener when creating computation factory using Computation.Config:

final Computation.Config config = new Computation.Config();
final TestComputationListener listener = new TestComputationListener();
config.cellComputationListener = listener;
final ComputationFactory f = e.getComputationFactory( config );

Now we are ready to perform the computation:

final Outputs c = (Outputs) f.newComputation( new Inputs() );
assertEquals( 1001.0, c.getVolume(), 1e-10 );

Let’s see what events we have received:

assertEquals( 5, listener.events.size() );
assertEvent( "-> 7.0 in Sheet1!B1(Length)", listener.events.get( 0 ) );
assertEvent( "-> 11.0 in Sheet1!B2(Width)", listener.events.get( 1 ) );
assertEvent( "77.0 in Sheet1!B4(Area)", listener.events.get( 2 ) );
assertEvent( "-> 13.0 in Sheet1!B3(Height)", listener.events.get( 3 ) );
assertEvent( "<- 1001.0 in Sheet1!B5(Volume)", listener.events.get( 4 ) );

Repeating Sections

An expression in a cell in a repeating section is calculated for each section’s input data object. The SpreadsheetCellComputationEvent event contains information about the section’s name and instance number. The numbering starts from zero and has nothing to do with row or column numbers in the source worksheet. It is more like an index into the collection that drives the repeating section at run-time.

Example

Let’s watch how the values are computed in the tutorial which introduced repeating sections.

We compile an engine with computation listener support. We also enable caching, so each value will be computed only once, and we won’t receive many identical events for each recomputation:

EngineBuilder builder = SpreadsheetCompiler.newEngineBuilder();
builder.setNumericType( SpreadsheetCompiler.LONG_SCALE4 );
builder.loadSpreadsheet( getFile() );
builder.setFactoryClass( BonusComputationFactory.class );
builder.setOutputClass( BonusComputationDefaults.class );
builder.setComputationListenerEnabled( true );
builder.setFullCaching( true );

bindElements( builder );
SaveableEngine engine = builder.compile();

Then we attach a listener to the engine:

TestComputationListener listener = new TestComputationListener();
Computation.Config config = new Computation.Config();
config.cellComputationListener = listener;
BonusComputationFactory factory = (BonusComputationFactory) engine.getComputationFactory( config );

Now we can execute the computation and watch intermediate values:

final List<SpreadsheetCellComputationEvent> computationEvents = listener.events;
assertEquals( 18, computationEvents.size() );
assertEvents( new String[]{
    "-> 20000.0000 in Sheet1!B7(BonusTotal) in section: ROOT",
    "-> 5600.0000 in Sheet1!B2(BaseSalary) in section: Sheet1!A2:Sheet1!F4(Employees)[0]",
    "-> 20.0000 in Sheet1!C2(HoursOvertime) in section: Sheet1!A2:Sheet1!F4(Employees)[0]",
    "-> 50.0000 in Sheet1!B6(OvertimeSalaryPerHour) in section: ROOT",
    "6600.0000 in Sheet1!D2 in section: Sheet1!A2:Sheet1!F4(Employees)[0]",
    "-> 5400.0000 in Sheet1!B2(BaseSalary) in section: Sheet1!A2:Sheet1!F4(Employees)[1]",
    "-> 15.0000 in Sheet1!C2(HoursOvertime) in section: Sheet1!A2:Sheet1!F4(Employees)[1]",
    "6150.0000 in Sheet1!D2 in section: Sheet1!A2:Sheet1!F4(Employees)[1]",
    "-> 5500.0000 in Sheet1!B2(BaseSalary) in section: Sheet1!A2:Sheet1!F4(Employees)[2]",
    "-> 0 in Sheet1!C2(HoursOvertime) in section: Sheet1!A2:Sheet1!F4(Employees)[2]",
    "5500.0000 in Sheet1!D2 in section: Sheet1!A2:Sheet1!F4(Employees)[2]",
    "18250.0000 in Sheet1!D5 in section: ROOT",
    "0.3616 in Sheet1!E2 in section: Sheet1!A2:Sheet1!F4(Employees)[0]",
    "<- 7232.0000 in Sheet1!F2(BonusAmount) in section: Sheet1!A2:Sheet1!F4(Employees)[0]",
    "0.3369 in Sheet1!E2 in section: Sheet1!A2:Sheet1!F4(Employees)[1]",
    "<- 6738.0000 in Sheet1!F2(BonusAmount) in section: Sheet1!A2:Sheet1!F4(Employees)[1]",
    "0.3013 in Sheet1!E2 in section: Sheet1!A2:Sheet1!F4(Employees)[2]",
    "<- 6026.0000 in Sheet1!F2(BonusAmount) in section: Sheet1!A2:Sheet1!F4(Employees)[2]"
}, computationEvents );

As we can see, the values for the cells Sheet1!D2, Sheet1!E2, and Sheet1!F2 were computed three times because we provided three EmployeeBonusData objects.

Constant Values

If an expression in a cell does not depend on input cells and uses functions which depend only on their arguments, then the compiler calculates the value of this cell at compile-time and replaces the expression by it. The attached CellComputationListener will not receive notifications from such cells when the computation is executed. But it is possible to attach a ConstantExpressionOptimizationListener to the EngineBuilder and watch in which cells the expressions are replaced by what constant values.

Example

We will use the same sheet as for the first example. But now we will provide an input only for height:

public static class Inputs2
{
  public double getHeight()
  {
    return 17;
  }
}

So the area will always be equal to 6 and can be calculated during compile-time.

Firstly we create an engine builder and attach a listener to it:

final EngineBuilder b = SpreadsheetCompiler.newEngineBuilder();
b.loadSpreadsheet( getFile() );
b.setInputClass( Inputs2.class );
b.setOutputClass( Outputs.class );
b.createCellNamesFromRowTitles();
b.bindAllByName();
final TestConstExprOptListener constExprOptListener = new TestConstExprOptListener();
b.setConstantExpressionOptimizationListener( constExprOptListener );

The listener should implement ConstantExpressionOptimizationListener interface:

private class TestConstExprOptListener implements ConstantExpressionOptimizationListener
{
  final List<SpreadsheetCellComputationEvent> events = New.list();

  public void constantCellCalculated( SpreadsheetCellComputationEvent _event )
  {
    this.events.add( _event );
  }
}

Then we compile the engine. Let’s see what events we have got:

final SaveableEngine e = b.compile();

assertEquals( 1, constExprOptListener.events.size() );
assertEvent( "6.0 in Sheet1!B4(Area)", constExprOptListener.events.get( 0 ) );

As expected, the compiler replaced the expression in Sheet1!B4 by its value.