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

Question

I have requirement to write data to excel file which I have achieved. And another requirement is to include piece cell in the summation if status cell of same row is "Recived".

And the summation should be done for visible cells only. I have 2 column like "Status"(Column A2:A10) and "piece" (B2:B10) now I have put the formula in my java class using apache-poi library:

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

After executing the Java code it writes the file successfully but when I open the file it shows the formula cell as #VALUE!. When I go to formula bar and press enter then the value for the formula comes.

I want the value for the formula to be displayed every time I open the Excel file.

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\")))");

LittlePanda · Accepted Answer · 2015-04-13 16:49:54Z

2

I have tried a sample code. You need to set the cell type 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..");

I have tried this example and it is not giving me #VALUE! error.

answered Apr 13, 2015 at 16:49

LittlePanda

2,5251 gold badge23 silver badges34 bronze badges

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

Collectives™ on Stack Overflow

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

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