How to set formulas in cells using Apache POI?

Question

The HSSFCell object has methods .setCellType and .setCellFormula which you need to call like this:

// "cell" object previously created or looked up
String strFormula= "SUM(A1:A10)";
cell.setCellType(HSSFCell.CELL_TYPE_FORMULA);
cell.setCellFormula(strFormula);

Cell Constants are deprecated and will be removed from version 4.0 instead of Cell Use

CellType.FORMULA

String formula= "SUM(B4:B20)"; cell.setCellType(CellType.FORMULA); cell.setCellFormula(formula);

UPDATE

setCellType() :Based on @fenix comment. This method is deprecated and will be removed in POI 5.0. Use explicit setCellFormula(String), setCellValue(...) or setBlank() to get the desired result.

String formula= "SUM(B4:B20)";
cell.setCellFormula(formula);

The below code worked fine for me, hope this could be useful to someone.

cell.setCellType(Cell.CELL_TYPE_FORMULA);
cell.setCellFormula("SUM(C70:C76)");

Apache POI does not support user-defined functions.

From the documentation:

Note that user-defined functions are not supported, and is not likely to done any time soon... at least, not till there is a VB implementation in Java!

You could use this to evaluate the formulas in the workbook:

        // Evaluate all formulas in the sheet, to update their value
    FormulaEvaluator formulaEvaluator = workbook.getCreationHelper().createFormulaEvaluator();
    formulaEvaluator.evaluateAll();

I was having the similar problem and "SUM(B4:B20)" does not work for me directly as the sheet was generated dynamically. The problem was with the cell reference.

On the basis of https://stackoverflow.com/a/2339262/2437655 and https://stackoverflow.com/a/33098060/2437655 I was able to generate the actual formula. e.g.

 val totalProductQtyPerUserCell = userDetailsRow.createCell(products.size + 1)
 totalProductQtyPerUserCell.cellType = HSSFCell.CELL_TYPE_FORMULA
 totalProductQtyPerUserCell.cellFormula = "SUM(${CellReference.convertNumToColString(1)}${totalProductQtyPerUserCell.row.rowNum + 1}:${CellReference.convertNumToColString(products.size)}${totalProductQtyPerUserCell.row.rowNum + 1})"

Hope that help.

Here is one suggested way:

Workbook workbook1 = new SXSSFWorkbook(-1);
Sheet newSheet = workbook1.createSheet("Valuations");
Row newRow = newSheet.createRow(i-1);
newRow.createCell(L++).setCellFormula("AVERAGE(" + "E" + i + ":" + "G" + i + ")");

Where E and G are the columns like A1,B1,C1....E1...G1
and i represent a number in the above Column.

L++ is simply an incremental counter.