Take the 2-minute tour ×

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.

share|improve this question
add comment

2 Answers

up vote 0 down vote accepted

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();
        case Cell.CELL_TYPE_FORMULA:
            CellValue objCellValue = formulaEvaluation(cell);
            if (objCellValue.getCellType() == Cell.CELL_TYPE_NUMERIC) {
                dblValue = objCellValue.getNumberValue();
    return dblValor;
share|improve this answer
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 6 '12 at 17:04
Taken from the Apache POI eval documentation: "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 6 '12 at 17:10
I managed to solve it in the end. –  The Gav Lad Jun 7 '12 at 10:20
The documentation has been updated! –  Gagravarr Jun 10 '12 at 22:27
add comment

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)";
share|improve this answer
add comment

Your Answer


