Using AFC Without A Spreadsheet File

Some applications may wish to use AFC to compile computation engines, but want users to be able to specify simple computations without having to setup a spreadsheet. These can use a builder to dynamically construct a spreadsheet as input to AFC.

Motivation

Consider again the main example for AFC, the order line item rebate computation. We solved the problem of allowing distributors and customers to customize the computation by letting them specify their own computations as spreadsheets, such as this one:

	A	B
1	Customer Rebate	3%
2	Article Rebate	6%
3
4	Rebate	6% =MAX(B1:B2)

Maybe many of your users must define their own, typically very simple, custom computations. Maybe there are a great many points in your application, where they want to do this (not just the line item rebate). In such a case you may want to offer your users a simple UI for defining straightforward custom computations (like, for instance, one liners), but still allow them to switch to a spreadsheet for more complex cases. Users thus might be able to define, right in your UI, the following formula:

Rebate = CustomerRebate + ArticleRebate

How can you implement this? Remember that we already factored out the actual computation as a strategy. So a straightforward approach is to implement a new strategy, which is effectively an interpreter for the simple one-line formulas (or whatever your UI allows users to specify).

If you expect these computations to be used heavily in your application, however, it may be worthwhile to harness AFC to generate true byte-code computations for these formulas, instead of interpreting them. A side benefit of this approach will be that you can give users switching from a simple formula to a full blown spreadsheet a head start by letting AFC generate an initial spreadsheet implementing the currently defined formula.

Building Your Own Spreadsheet Model

AFC’s compiler takes an internal representation of a spreadsheet as input. Normally, you load this from an actual spreadsheet file. With the SpreadsheetBuilder, however, you can build one in memory from scratch. I’ll first show you how to define the spreadsheet with only the two input cells:

private Spreadsheet buildSpreadsheet()
{
  SpreadsheetBuilder b = SpreadsheetCompiler.newSpreadsheetBuilder();

  b.newCell( b.cst( "CustomerRebate" ) );
  b.newCell( b.cst( 0.1 ) );

  b.newRow();
  b.newCell( b.cst( "ArticleRebate" ) );
  b.newCell( b.cst( 0.05 ) );

  return b.getSpreadsheet();
}

The method b.cst() is a factory method for constant values. It accepts all the types of constant value that AFC supports, for example String and Number.

Of particular interest is how to build calculated cells, B1+B2 in this example. Here’s the same method again, showing how the calculated cell is defined:

private Spreadsheet buildSpreadsheet()
{
  SpreadsheetBuilder b = SpreadsheetCompiler.newSpreadsheetBuilder();

  b.newCell( b.cst( "CustomerRebate" ) );
  b.newCell( b.cst( 0.1 ) );
  // -- defCalc
  SpreadsheetBuilder.CellRef cr = b.currentCell();
  // -- defCalc

  b.newRow();
  b.newCell( b.cst( "ArticleRebate" ) );
  b.newCell( b.cst( 0.05 ) );
  // -- defCalc
  SpreadsheetBuilder.CellRef ar = b.currentCell();

  b.newRow();
  b.newRow();
  b.newCell( b.cst( "Rebate" ) );
  b.newCell( b.op( Operator.PLUS, b.ref( cr ), b.ref( ar ) ) );
  // -- defCalc

  return b.getSpreadsheet();
}

The currentCell() method returns a reference to the cell defined just before it. These references can be used later on in formulas to reference other cells.

The part b.op( Operator.PLUS, ... ) defines the last cell as one performing an addition, which is one of the supported operators. Its arguments are references to the two cells defined above. There is also fun() for functions and aggregators.

Switching To A Spreadsheet File

When users find the simple formula UI too limited, they will want to switch to using a full-blown spreadsheet. As noted above, you can give them a head start by having AFC generate the initial spreadsheet for them. This spreadsheet then implements the current simple formula as a starting point for further customization.