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.