Quick Start

Have you downloaded? Good. Now let’s get your first compiled spreadsheet up and running. I’ll show you how to…

• create a spreadsheet defining a customized line item price computation,
• make AFC compile it to Java classes,
• use those classes in your application,
• look at the generated code, and
• save and reload the classes from a .jar file.

The Problem

This is a fictional example about finding the total price for a line item with

• a base article price,
• a count of items, and
• the customer category (for automatic rebates).

We will flesh out the following bit of code:

// Compile price finding factory and strategy implementation from spreadsheet:
EngineBuilder builder = SpreadsheetCompiler.newEngineBuilder();
builder.loadSpreadsheet( new File( PATH, "CustomPriceFormula.xls" ) );
builder.setFactoryClass( PriceFinderFactory.class );
builder.bindAllByName();
Engine engine = builder.compile();
PriceFinderFactory factory = (PriceFinderFactory) engine.getComputationFactory();

// Use it to compute a line item price:
LineItem item = getCurrentLineItem();
PriceFinder priceFinder = factory.newInstance( item );
BigDecimal price = priceFinder.getPrice();

The Spreadsheet

Here’s the spreadsheet-defined formula our user wants the application to use:

	A	B	C	D	E
1	Input Values
2	Article Price	500.00
3	Item Count	5
4	Customer Category	C
5
6	Intermediate Values
7	Categories	A	B	C	D
8	Rebates	6%	4%	1%	0%
9	Effective Rebate	=HLOOKUP(B4,B7:E8,2.0)
10	Base Price	=B2*B3
11
12	Output Values
13	Price	=B10*(1.0-B9)

Please create this spreadsheet now and save it somewhere under the name CustomPriceFormula.xls.

The Project

Create a new Java project for this demo application now. Add to it references to the following libraries you obtained from the download:

build/formulacompiler-runtime.jar
build/formulacompiler-compiler.jar
build/formulacompiler-spreadsheet.jar
build/formulacompiler-spreadsheet-excel-xls.jar
build/formulacompiler-decompiler.jar
build/lib/asm-x.y.jar
build/lib/asm-commons-x.y.jar
build/lib/jxl.jar
build/lib/jode-decompiler.jar

Then create a main class in it. Unless your IDE can find and organize imports automatically, you might also want to add these to your main class:

import java.io.ByteArrayOutputStream;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.PrintStream;
import java.math.BigDecimal;

import org.formulacompiler.compiler.CompilerException;
import org.formulacompiler.compiler.SaveableEngine;
import org.formulacompiler.decompiler.ByteCodeEngineSource;
import org.formulacompiler.decompiler.FormulaDecompiler;
import org.formulacompiler.runtime.Engine;
import org.formulacompiler.runtime.EngineException;
import org.formulacompiler.runtime.FormulaRuntime;
import org.formulacompiler.spreadsheet.EngineBuilder;
import org.formulacompiler.spreadsheet.SpreadsheetCompiler;

The Interfaces

In essence, what AFC compiles from the spreadsheet is a computation strategy implementation. So we need the customary factory and doer interfaces that go with a strategy:

public static interface PriceFinder {
  BigDecimal getPrice();
}

public static interface PriceFinderFactory {
  PriceFinder newInstance( LineItem item );
}

Paste these directly into your main class (they are nested classes in this example, but AFC is just as happy with top-level classes).

We’ll also need the line item, which provides input data to the computation:

public static class LineItem {
  public BigDecimal getArticlePrice() { return BigDecimal.valueOf( 112.00 ); } 
  public int getItemCount() { return 10; }
  public String getCustomerCategory() { return "B"; }
}

It’s obviously a dummy. Paste it into the main class as well, together with the following dummy getter:

private LineItem getCurrentLineItem() {
  return new LineItem();
}

The Compilation

We’re ready to compile now. Add the following two methods, that I’ll explain shortly:

private SaveableEngine compile() throws FileNotFoundException, IOException, CompilerException, EngineException
{
  EngineBuilder builder = SpreadsheetCompiler.newEngineBuilder();
  builder.loadSpreadsheet( new File( PATH, "CustomPriceFormula.xls" ) );
  builder.setFactoryClass( PriceFinderFactory.class );
  builder.bindAllByName();
  return builder.compile();
}

private PriceFinderFactory factoryFor( Engine engine )
{
  return (PriceFinderFactory) engine.getComputationFactory();
}

Also add a constant for the path where you saved the spreadsheet. In my code, it’s:

public static final File PATH = new File( "src/test/data/org/formulacompiler/tutorials" );

Let’s go through this now:

• First, we get a new engine builder. It gives us a simplified API onto AFC.

• We tell the builder to load the spreadsheet you created earlier on. AFC detects the appropriate loader to use by the file’s extension. It currently supports Microsoft Excel (.xls) and OpenOffice Calc (.ods), and maybe others – check the release notes for details.

• We inform the builder of our factory class. It can usually infer from this the input interface, LineItem, and the output interface, PriceFinder, by itself (if not, you can given them explicitly; you can then also omit the factory interface altogether).

• We tell the builder to bind spreadsheet cells to our interface methods by name. In short, any cell named after a method on the input interface, LineItem, gets its value from that method in the compiled formula. A cell named after a method on the output interface, PriceFinder, is used to implement that method. (The binder is smart about dropping the get prefix on method names and is not case sensitive.)

• But wait, we don’t have any cell names in this spreadsheet. What’s going on? AFC automatically creates cell names from row titles (string values in column A are applied as names for corresponding cells in column B) when a sheet has no cell names and you use bindAllByName().

• That’s it. We tell the builder to compile the thing. It returns a so-called engine (which we’ll later use to save, reload, and decompile), but the most important method on the engine is the one we use on our second method: getComputationFactory().

The Computation

We now look at how the resulting price finder is used. Since AFC compiles spreadsheets to regular JVM classes, there’s no magic at all when using a precompiled computation:

private BigDecimal compute( PriceFinderFactory factory )
{
  PriceFinder priceFinder = factory.newInstance( getCurrentLineItem() );
  return priceFinder.getPrice();
}

So let’s make this runnable:

public static void main( String[] args ) throws Exception
{
  QuickStart app = new QuickStart();
  SaveableEngine engine = app.compile();
  PriceFinderFactory factory = app.factoryFor( engine );
  BigDecimal price = app.compute( factory );
  System.out.println( "The result is " + price );
}

Go ahead. Run it.

The Code

Would you also like to know just what exactly the generated PriceFinder implementation does? You can. AFC wraps a nifty external library, Jode, to decompile generated code to plain Java source again for just this purpose. Let’s use this:

private void decompile( SaveableEngine engine ) throws Exception
{
  ByteCodeEngineSource source = FormulaDecompiler.decompile( engine );
  source.saveTo( new File( "temp/test/decompiled/quickstart" ) );
}

You can change the output folder, of course. Now add the following at the end of the main method:

app.decompile( engine );

Run it again. Then look into the output folder. You should find the following there. First, the generated computation:

package org.formulacompiler.gen;
import java.math.BigDecimal;

import org.formulacompiler.runtime.Computation;
import org.formulacompiler.runtime.FormulaException;
import org.formulacompiler.runtime.internal.Environment;
import org.formulacompiler.runtime.internal.RuntimeDouble_v2;
import org.formulacompiler.runtime.internal.Runtime_v2;
import org.formulacompiler.tutorials.QuickStart;

final class $Root implements Computation, QuickStart.PriceFinder
{
    private final QuickStart.LineItem $inputs;
    final Environment $environment;
    private String[] $constarr$0;
    
    $Root(QuickStart.LineItem lineitem, Environment environment) {
        $environment = environment;
        $inputs = lineitem;
    }
    
    final double get$0() {
        return (get$1() * get$2()
                * (1.0 - $idx$0(Runtime_v2.fun_MATCH_Ascending(get$3(),
                                                               $constarr$0(),
                                                               $environment)
                                - 1)));
    }
    
    public final BigDecimal getPrice() {
        return BigDecimal.valueOf(Runtime_v2.checkDouble(get$0()));
    }
    
    final String[] $constarr$0() {
        if ($constarr$0 == null)
            $constarr$0 = new String[] { "A", "B", "C", "D" };
        return $constarr$0;
    }
    
    final double $idx$0(int i) {
        switch (i) {
        case 0:
            return 0.06;
        case 1:
            return 0.04;
        case 2:
            return 0.01;
        case 3:
            return 0.0;
        default:
            throw new FormulaException
                      ("#VALUE/REF! because index is out of range in INDEX");
        }
    }
    
    final double get$1() {
        return RuntimeDouble_v2.numberToNum($inputs.getArticlePrice());
    }
    
    final double get$2() {
        return (double) $inputs.getItemCount();
    }
    
    final String get$3() {
        return Runtime_v2.stringFromString($inputs.getCustomerCategory());
    }
}

Second, it’s corresponding factory:

package org.formulacompiler.gen;
import org.formulacompiler.runtime.Computation;
import org.formulacompiler.runtime.ComputationFactory;
import org.formulacompiler.runtime.internal.Environment;
import org.formulacompiler.tutorials.QuickStart;

public final class $Factory
    implements ComputationFactory, QuickStart.PriceFinderFactory
{
    private final Environment $environment;
    
    public $Factory(Environment environment) {
        $environment = environment;
    }
    
    public final Computation newComputation(Object object) {
        return new $Root((QuickStart.LineItem) object, $environment);
    }
    
    public final QuickStart.PriceFinder newInstance
        (QuickStart.LineItem lineitem) {
        return new $Root(lineitem, $environment);
    }
}

The Runtime

Computations compiled by AFC are plain compiled Java classes. They rely only on a single small runtime library, formulacompiler-runtime.jar. So it’s good practice to simply save them to a .jar file and reuse them from there across application starts (or to even split the compiling application from the using application). However, they do require a special classloader. AFC therefore provides special methods to save and load engines properly.

First, let’s save our engine:

private void save( SaveableEngine engine ) throws Exception
{
  engine.saveTo( new FileOutputStream( "temp/test/CustomPriceFormula.jar" ) );
}

Loading it back is just as easy:

private Engine load() throws Exception
{
  return FormulaRuntime.loadEngine( new FileInputStream( "temp/test/CustomPriceFormula.jar" ) );
}

Again, add the following to the main method to make it runnable:

app.save( engine );

QuickStart app2 = new QuickStart();
Engine engine2 = app2.load();
PriceFinderFactory factory2 = app2.factoryFor( engine2 );
BigDecimal price2 = app2.compute( factory2 );
System.out.println( "The result is " + price2 );

Alright. Run it.

Summary

As promised, you just…

• created a spreadsheet defining a customized line item price computation,
• made AFC compile it to Java classes,
• used those classes in your application,
• looked at the generated code in Java source form, and
• saved and reloaded the classes from a .jar file.

Hopefully, you also played around with the spreadsheet, compiling different formulas. You really should.

Not bad. Now learn more about the goals, the design, the API, and the supported functions of AFC.