Java & Excel - Formulas updating

Question

I'm using Apache POI to save data to an excel file. Basic data is being saved fine, but I need to also use formula's.

Its adding the formula in, but its not being evaluated until I refresh the cell (clicking into and pressing enter)

The code I'm using to create the cells. Removed code that's not relevant

public void writeExcel(ClassManager cm) throws FileNotFoundException, IOException {
    setupRows();
    workbook.setForceFormulaRecalculation(true);

    FileOutputStream outputStream = new FileOutputStream(fileLocation);
    workbook.write(outputStream);
    workbook.close();
}

public void setupRows() {
    setupRow15();
}

public void setupRow15() {
    int start = 2;
    Row row = sheet.createRow(16);
    
    // Create 1st Cell
    Cell cell = row.createCell(0);
    cell.setCellValue("templateId = ");
        
    for (int i = 0; i < classes.size(); i++) {
        // Get class
        Classes c = classes.get(i);
        
        // Create cell
        cell = row.createCell(start);
        
        // Set contents
        cell.setCellFormula("IF(C3=\"\",\"\",CONCAT($A17,$B17,C" + (start + 1) + ",$B17,$A$16))");
        
        start++;
    }
}

It's resulting in the formula

Have you reviewed formula evaluation? (Side note: Is that final sentence in your question incomplete?) — andrewJames
– andrewJames, Commented Jan 15, 2021 at 20:29

Ceri Turner · Accepted Answer · 2021-01-15 22:15:37Z

1

Solved it by running after setting all formulas

FormulaEvaluator evaluator = workbook.getCreationHelper().createFormulaEvaluator();
    for (Row r : sheet) {
        for (Cell c : r) {
            evaluator.evaluateFormulaCell(c);
        }
    }

answered Jan 15, 2021 at 22:15

Ceri Turner

9763 gold badges13 silver badges41 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Java & Excel - Formulas updating

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related