The goal was to make online calculator that will produce the same results calculations that there was alredy made in  spreadsheet. 

First of all I thought How I do that. It looked quite hard to code all non-transparent logic and data with different formulas that was already done  in xls file that I’ve got from my custumer. But what if just open xls(odf) file change some cells value (input  params )  and read the the result of calculations from others. This thought had crossed my mind. There sould be tools that could work with .xls or .odf files and I need a crossplatform one.  And I start looking up….

So Google proposed me : 

1. http://www.andykhan.com/jexcelapi/
2. JOpenDocument http://www.jopendocument.org/documentation.html
3. JExcel http://teamdev.com/jexcel/
4. Apache POI http://poi.apache.org/spreadsheet/

First two are nice, crossplatform and easy to use  but… but they don’t recalculate value of cells with formulas (their API  return value of cell with formula but that value isn’t right if any dependent cell changed). That’s not what I want.

JExcel as I know is very powerfull but it has a strong dependency on Microsoft Office. That isn’t for me.

And Last one Apache POI is crossplatform, powerfull  and has “Formula Evaluation” support (http://poi.apache.org/spreadsheet/eval.html ).  This is exactly what I need.

FileInputStream fis = new FileInputStream(“c:/temp/test.xls”);

Workbook wb = new HSSFWorkbook(fis); //or new XSSFWorkbook("c:/temp/test.xls")
Sheet sheet = wb.getSheetAt(0);
FormulaEvaluator evaluator = wb.getCreationHelper().createFormulaEvaluator();

// suppose your formula is in B3
CellReference cellReference = new CellReference("B3");
Row row = sheet.getRow(cellReference.getRow());
Cell cell = row.getCell(cellReference.getCol()); 

CellValue cellValue = evaluator.evaluate(cell);

switch (cellValue.getCellType()) {
    case Cell.CELL_TYPE_BOOLEAN:
        System.out.println(cellValue.getBooleanValue());
        break;
    case Cell.CELL_TYPE_NUMERIC:
        System.out.println(cellValue.getNumberValue());
        break;
    case Cell.CELL_TYPE_STRING:
        System.out.println(cellValue.getStringValue());
        break;
    case Cell.CELL_TYPE_BLANK:
        break;
    case Cell.CELL_TYPE_ERROR:
        break;

    // CELL_TYPE_FORMULA will never happen
    case Cell.CELL_TYPE_FORMULA:
        break;
}

Finnally, I’ve created a SOAP-service that gets some input params, makes calculations using Apache POI from nessesary xls file, and returns calculated data. That’s all.