Excel formula gives error when write using Apache-poi library in Java

Question

I needed to write data to an excel file, which I was able to do. Another criterion is that if the status cell for the same row is "Received," the piece cell must be included in the summing.

Additionally, only visible cells should be included in the summing. I currently have two columns named "Status" (Column A2:A10) and "piece" (Column B2:B10), and I've added the following formula using the Apache-poi library:

SUMPRODUCT(SUBTOTAL(9,OFFSET(B2:B10,ROW(B2:B10)-(ROW(B2)),0,1)),(--(A2:A10="Recived")))

When the Java code is executed, the file is correctly written, but when I access the file, the formula cell displays as #VALUE! The value for the formula appears when I select the formula bar and press Enter.

Every time I open the Excel file, I want the formula's value to be visible.

Below is Java code

cell.setCellType(Cell.CELL_TYPE_FORMULA);
cell.setCellFormula("SUMPRODUCT(SUBTOTAL(9,OFFSET(B2:B10,ROW(B2:B10)-(ROW(B2)),0,1)),(--(A2:A10=\"Recived\")))");

Answer 1

I tested some sample code. The cell type must be set to Numeric (or whichever data type your excel formula will return).

See below example:

FormulaEvaluator evaluator = workbook.getCreationHelper().createFormulaEvaluator();     
Row row = sheet.getRow(0);
Cell cell2 = row.createCell(2);
//set cell formula
cell2.setCellFormula("SUM(A1:B1)+10");
//evaluate the formula
evaluator.evaluateFormulaCell(cell2);
//set the cell type to numeric
cell2.setCellType(Cell.CELL_TYPE_NUMERIC);
System.out.println(cell2.getNumericCellValue());

//write data to excel file
FileOutputStream out = new FileOutputStream(new File("res/Book1.xlsx"));
workbook.write(out);
out.close();
System.out.println("Excel written successfully..");