java POI XSSF FormulaEvaluator

Question

I am having a problem when i save my new excel file. I want it that when it gets saved the formula calculates itself but at the moment it is just returning a string in the excel file. The formula is correct. I don't know exactly to get the FormulaEvaluator to work.

Here is where I enter my formula that returns a string:

dataRow1.createCell((short)5).setCellValue("=VLOOKUP(A"+rowCountVlookup+",'C:\\Users\\Admin\\Documents\\JCreator LE\\MyProjects\\WordCount\\classes\\[Pricing.xlsx]Sheet1'!$B$3:$E$41,4, FALSE)*E"+rowCountVlookup+"");

Any help would be much appreciated.

score 1 · Answer 1 · answered Jun 07 '12 at 10:22

I managed to solve it in the end.

String strFormula = "ROUND((VLOOKUP(A"+rowCountVlookup+",'C:\\Users\\Admin\\Desktop\\[pricing.xlsx]Sheet1'!$B$3:$E$41,4, FALSE)*E"+rowCountVlookup+"),2)";
                            dataRow1.createCell((short)5).setCellType(Cell.CELL_TYPE_FORMULA);
                            dataRow1.createCell((short)5).setCellFormula(strFormula);

score 0 · Accepted Answer · answered Jun 06 '12 at 13:54

0

I use this code to evaluate a formula

//I use an instance of the workbook for the Excel workbook I'm working at the moment
Workbook wbook;

private CellValue formulaEvaluation(Cell cell) {
    FormulaEvaluator formulaEval = wbook.getCreationHelper().createFormulaEvaluator();
    return formulaEval.evaluate(cell);
}

public Double obtieneObjetoNumericoCelda(Cell cell) {
    Double dblValue = null;
    if (cell != null) {
        switch(cell.getCellType()) {
        case Cell.CELL_TYPE_NUMERIC:
            dblValue = cell.getNumericCellValue();
            break;
        case Cell.CELL_TYPE_FORMULA:
            CellValue objCellValue = formulaEvaluation(cell);
            if (objCellValue.getCellType() == Cell.CELL_TYPE_NUMERIC) {
                dblValue = objCellValue.getNumberValue();
            }
            break;
        }
    }
    return dblValor;
}

answered Jun 06 '12 at 13:54

Luiggi Mendoza

85,076
16
154
332

You'd probably be best off just creation the formula evaluator once and re-using it, rather than creating it once per cell – Gagravarr Jun 06 '12 at 17:04
Taken from the Apache POI [eval documentation](http://poi.apache.org/spreadsheet/eval.html): "Generally you should have to create only one FormulaEvaluator instance per sheet, but there really is no overhead in creating multiple FormulaEvaluators per sheet other than that of the FormulaEvaluator object creation.". In my case, there is a wrapper class for a workbook, so _creating_ the formula more than once will not have impact on performace. – Luiggi Mendoza Jun 06 '12 at 17:10
The [documentation has been updated](http://poi.apache.org/spreadsheet/eval.html#Performance)! – Gagravarr Jun 10 '12 at 22:27

java POI XSSF FormulaEvaluator

2 Answers2

Linked