CODEWITHSUNDEEP
javaexcelspreadsheet
martinr
martinr

Reputation: 3812

convert spreadsheet formulas to java

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.

Upvotes: 4

Views: 15493

Answers (4)

Peter Smith
Peter Smith

Reputation: 763

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)

Upvotes: 2

liya
liya

Reputation: 792

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.

Upvotes: 1

torbjoernwh
torbjoernwh

Reputation: 445

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

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

Upvotes: 1

Carl Smotricz
Carl Smotricz

Reputation: 67790

  • 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.

Upvotes: 6

Related Questions