convert spreadsheet formulas to java

Question

What tools are available to convert an OpenOffice or Excel spreadsheet (with all its formulas) into a Java object that can be called at run-time?

Obviously this would only make a calculation engine and just be about numbers and text, not timing or API calls.

Even with named cell ranges being used to (effectively) name variables the output code would presumably be difficult to understand. It would need refactoring to get more like normal Java code. However I think it would be useful for prototyping some data processing type jobs. Or for embedding some calculation engines maintained by an advanced Excel user.

Edit : A trivial example:

APPEARANCE

        A               B               C               D
1       Mortgage Value  100,000.00
2       Interest rate   4.5%
3       Type            Interest-only
4       Years           3
5       Regular payment 4,500.00
6       Total interest  13,500.00

CELL NAMES

        A               B               C               D
1       Mortgage Value  VALUE
2       Interest rate   INTEREST
3       Type            TYPE
4       Years           YEARS
5       Regular payment REGPYMT
6       Total interest  TOTALPYMT

FORMULAS

        A               B               C               D
1       Mortgage Value  100,000.00
2       Interest rate   4.5%
3       Type            Interest-only
4       Years           3
5       Regular payment =VALUE*INTEREST
6       Total interest  =YEARS*REGPYMT

would translate into Java as something like:

package example.calcengine;
import java.math.*;
public class MyCalcEngine {

    // unnamed cells
    public String A1 = "Mortgage Value";
    public String A2 = "Interest rate";
    public String A3 = "Type";
    public String A4 = "Years";
    public String A5 = "Regular payment";
    public String A6 = "Total interest";

    // named cells
    public BigDecimal VALUE = new BigDecimal(100000.00);
    public BigDecimal INTEREST = new BigDecimal(0.045);
    public String TYPE = "Interest-only";
    public BigDecimal YEARS = new BigDecimal(3);
    public BigDecimal REGPYMT = new BigDecimal(0);
    public BigDecimal TOTALPYMT = new BigDecimal(0);

    // formulas
    public void calculate() {
      REGPYMT = VALUE.multiply(INTEREST);
      TOTALPYMT = REGPYMT.multiply(YEARS);
    }
}

I'd assume fixed type for cells - either a java.math.BigDecimal or a String.

Answer 1

• The Apache POI project provides Java code that can read (mostly) Excel spreadsheets.

• Another project, Jacob, provides a Java interface for COM automation of arbitrary Windows programs, of course including Excel. You're essentially working Excel's API from the outside, in Java.

Answer 2

JExcelApi lets you load a .xls file and read/write to cells.

http://www.andykhan.com/jexcelapi/

Answer 3

We had done a similar project with SmartXLS for java. It had a runtime calculate engine and support add-in formulas.The source workbook is converted to a java class which can be embedded in java program and calls with parameters just like input in Excel.

Answer 4

One more option is Expr4J. It implements a general purpose expression language parser that is compatible with Excel and has a dependency engine for calculating expression graphs. It also implements the vast majorit of Excel's built in functions.

From the website, an example is:

public class DependencyExample
{
    public static void main(String[] args) throws Exception {
        DependencyEngine e = new DependencyEngine(new BasicEngineProvider());
        e.set("B1", "=A1*2");
        e.set("A1", "=12*2");
        e.set("C1", "=B1*A1");
        System.out.println(e.getValue(Range.valueOf("B1")));
        System.out.println(e.getValue(Range.valueOf("C1")));
        e.set("A1", "2");
        System.out.println(e.getValue(Range.valueOf("B1")));
        System.out.println(e.getValue(Range.valueOf("C1")));
    }
}

(I work on the project so would obviously be interested in any feedback)

---

Answer 5

Anybody know how to get in touch with Peter Smith? I found out about Expr4J on this thread, but the project on sourceforge is missing an important file and I can't find any email address to get in touch with him (tried his sourceforge account but I don't know if he checks that). If you can help please email me at [email protected] Thanks

Answer 6

for Peter, Is the Expr4J project actually still a work in progress, or has it been dropped? I am interested in using the calculation engine concept for some ideas, and looks as though there is some good solid work here, but would like to know whether to try and pick up what is currently there and run with it, or to input to its development? Thanks David