Formula recalculation in excel template POI JAVA

I have an Excel template file with formulas in some cells, I open it and fill in the values using POI. The problem is that the formulas aren’t automatically recalculated. But if I open this file and click on a cell with a value, it recalculates that cell.

I found two ways to solve this issue in the documentation, but neither helped me.

The point is that Excel caches previously calculated results and you need to trigger recalculation to updated them.

Re-evaluate formulas with POI’s FormulaEvaluator

workbook.getCreationHelper().createFormulaEvaluator().evaluateAll();
Delegate re-calculation to Excel. The application will perform a full recalculation when the workbook is opened

workbook.setForceFormulaRecalculation(true);

My code:

    public static String fillBook(String filename, String outFilename,  int sheetNumber, String[] params){ 
        String result = "";
        try {
            File file = new File(filename);

            Workbook workbook = null;
            workbook = WorkbookFactory.create(file);
            Sheet sheet = workbook.getSheetAt(sheetNumber);

            for (String param: params)
            {
                String[] paramArray = param.split("u0001");
                String address = paramArray[0];
                String value;

                if (paramArray.length > 1)
                {
                    value = paramArray[1];
                } else {
                    value = "";
                }

                CellReference cellReference = new CellReference(address);
                Row row = sheet.getRow(cellReference.getRow());

                if (row == null) {
                    row = sheet.createRow(cellReference.getRow());
                }

                Cell cell = row.getCell(cellReference.getCol(), Row.MissingCellPolicy.CREATE_NULL_AS_BLANK);
                cell.setCellValue(value);
            }

            //workbook.getCreationHelper().createFormulaEvaluator().evaluateAll();
            workbook.setForceFormulaRecalculation(true);

            FileOutputStream out = new FileOutputStream(outFilename);
            workbook.write(out);
            out.close();

        } catch (Exception e) {
            StringWriter sw = new StringWriter();
            e.printStackTrace(new PrintWriter(sw));
            String exceptionAsString = sw.toString();

            result = e.toString() + " " + exceptionAsString;
        }
        return result;
    }

Answer

You should not set all cell values as string values in Excel. Formulas which expect numeric values or date values will not work when the referred cells contain string values.

As of your provided code, all new values you set are string values. So if String[] paramArray contains “123” for example, you set the string “123” as cell value instead of the number 123. But for example formula SUM cannot sum strings. It only sums numeric values. So it only will work if you change cell having “123” in it to have 123 then. So set numeric values as numeric values. There are methods in Cell for this. void setCellValue(double value) for example.

The same is with date values. Those also needs to be set as date values into the cells instead simply as strings.

So you need a different object than String[] params to hand over the cell values. This object must be able to store numeric values too. For example a List<Object> or a List<POJO> where POJO is any kind of plain old Java object storing the information. Then use different setCellValue methods of Cell to set string cell values or numeric cell values or date cell values.

Else you need creating a numeric value (double) from the string got from String[] params before you set it into the Cell. Same for date values – create a date value (Calendar or LocalDate or LocalDateTime) from the string got from String[] params before you set it into the Cell. But that is the more error prone approach in my opinion.

Advertisement

Answer