Efficient formula binding at runtime

Question

One of the elements of my UI is a table showing a list of items, aggregated by category, with some formulae (see target output below).

What would be the best approach to allow the user to bind cells to spreadsheet-like formulae?

• performance: the actual data changes frequently, the tables can be big and the formulae complex, so performance and CPU usage are a concern.
• ease of configuration (assuming configuration is done by a developer so it can involve programming)
• maintainability of configuration

The approaches I have envisaged so far:

1. use the Java Scripting API but I'd rather stick to a JVM based approach for better performance
2. create an interface Formula { double calculate(); } and generate, compile and load implementations at runtime - that allows the JIT to compile the generated methods
3. same as 2 but using another JVM based language, such as clojure, which allows easy on-the-fly compiling and has a well-suited functional approach (map/reduce would help for the average and sum calculations below for example) - not sure about the performance implications though.

Which approach would make more sense? Are there any other solutions or libraries that I should consider?

---

Example

To make the goal clearer, here is a contrived example - let's assume the items in the table are:

public class Item() {
    public String category;
    public String name;
    public int quantity;
    public double price;
}

Target Output

Category     Name        Quantity    Price (avg)     Value (sum)
All                                      82            1,090

Bikes                                    45              650
             Bike 1         10           40              400
             Bike 2          5           50              250

Cars                                    120              440
             Car 1           3          100              300
             Car 2           1          140              140

Columns configuration

The columns could be defined like this:

Column #      Formula
    1         item.name;
    2         item.quantity;
    3         item.price;
    4         Math.abs(column_2 * column_3); //calls a JDK method

Aggregation configuration

And the aggregation categories and summary formulae could be defined like this:

AGGREGATION #1: Category cat = item.category();
Column #      Formula
    1         cat;
    2         "";
    3         thisColumn.filter(cat).items().average(); //utility method
    //this one is more complex
    4         { double sum = 0;
                for (double value : thisColumn.filter(cat).items())
                    sum += value;
                return sum;
              }

Answer 1

I'd use a custom domain specific language that your users can use, and let ANTLR generate a native parser in java that parses the expression to an executable function.

See e.g. http://arcanecoder.blogspot.be/2008/04/using-antlr-to-create-excel-like.html for an example that did something similar, or http://www.antlr.org/wiki/display/ANTLR3/Expression+evaluator for a more recent one.

You can define functions like 'sum', 'average', etc. and map it to custom java code, and use e.g. $1 or ${name} to let them refer to properties.

You only define those functions that you want your user to use, so they cannot use any harmfull functions that would be available in a generic scripting language.