1

Previously I was using POI 3.1.7 with setCellFormula without any issues. Now I've migrated to 4.1.1 and noticed that although the cell sets the formula correctly, it doesn't display any value (e.g the cell is set to "Accounting" format, the cell shows =PRODUCT(I4*L4), on screen it shows $ - . The code is as follow:

XSSFCell cell = null;
cell = row.createCell(12);
cell.setCellFormula("PRODUCT(I"+(processingRow+1)+",L"+(processingRow+1)+")");
cell.setCellStyle(getCellStyle("ACCOUNTING", sheet, true,true, false,false));

By the way, I'm using it on Excel 2013 (which I don't think should have any impact?). If I were to go into the formula cell and press enter at the end of the formula, the value will only then be displayed as it should be. Am I missing something?

huffie
  • 11
  • 2
  • 1
    The result `$ -` is the value `0` in accounting format. So `=PRODUCT(I4*L4)` results in `0`. So either the value of cell `I4` or the value of cell `V4` or both the values are `0`. – Axel Richter Feb 06 '20 at 14:57
  • The issue is that I4 and L4 do have valid values and if I were to go to the formula bar and press enter, the actual results will be displayed. It worked on POI 3.1.17 with the same set of input though. – huffie Feb 06 '20 at 15:03
  • Not possible to answer without having [minimal reproducible example](https://stackoverflow.com/help/minimal-reproducible-example). But a guess: See https://poi.apache.org/components/spreadsheet/eval.html#recalculation. To check whether this is the reason, simply put `wb.setForceFormulaRecalculation(true);` before writing out the workbook `wb`. – Axel Richter Feb 06 '20 at 15:23
  • Thanks Axel, the setForceFormlaRecalculation(true) did the trick and resolved my problem. :) – huffie Feb 07 '20 at 00:34

2 Answers2

1

You are correct. There is a retrograde from apache poi 3.17 to apache poi 4.x. The apache poi developer team has made the decision that formula cells always have a value. See CellBase.setCellFormula. That might be a correct decision.

But instead calculating the correct value then, they set it to 0. But 0 is a valuable value of it's own. The value 0 cannot be the placeholder for "not yet present".

In apache poi 3.17 the setCellFormula had only set the formula without a value. So Excel missed the value while parsing the sheet and calculated the value from the formula. So while creating a new workbook from scratch, formula evaluation is not necessary using apache poi 3.17.

Now using apache poi 4.x all formulas needs to be evaluated. Or setForceFormulaRecalculation must be set for the workbook. See Recalculation of Formulas.

Example:

import java.io.FileOutputStream;

import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

class CreateExcelFormula {

 public static void main(String[] args) throws Exception {

  Workbook workbook  = new XSSFWorkbook();
  FormulaEvaluator formulaEvaluator = workbook.getCreationHelper().createFormulaEvaluator(); // this is not necessary using apache poi 3.17

  Sheet sheet = workbook.createSheet();
  Row row = sheet.createRow(3);
  Cell cell = row.createCell(8); cell.setCellValue(21.7);
  cell = row.createCell(11); cell.setCellValue(20.0);

  cell = row.createCell(0); cell.setCellFormula("PRODUCT(I4,L4)");
  formulaEvaluator.evaluateFormulaCell(cell); // this is not necessary using apache poi 3.17

  FileOutputStream out = new FileOutputStream("Excel.xlsx");
  workbook.write(out);
  out.close();
  workbook.close();

 }
}
Axel Richter
  • 56,077
  • 6
  • 60
  • 87
0

This solved the issue for me-

        sheet.setForceFormulaRecalculation(true);
Deva44
  • 93
  • 2
  • 9