How to set formulas in cells using Apache POI?

43
votes

I am currently using Apache POI for Java to set formulas in cells.

But after I run the program and open the Excel file that I created and processed, the cells with the formula include the formula as a string, rather than the value the formula should have returned.

7
javaapache-poi
Are you using setCellFormula (poi.apache.org/apidocs/org/apache/poi/hssf/usermodel/…)? – Matthew Flaschen
yep. Basically my formula is a user defined one, not supported by defualt by excel. Its something like this: "=IQLink|yhoo!change" All it does it gets the stocks current price via its stock program. But point being is the formula is not supported by excel by default and does that have anything to do with it? – vamsi
What happens if you tell Excel to manually recalculate all formulas (office.microsoft.com/en-us/excel/…)? – Matthew Flaschen
when i go into the excel and manually recalculate, they work as expected...i don't know why poi can't do the same. – vamsi
I dont believe poi can do any formula evaluating. – mP.

7 Answers

55
votes

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);
17
votes

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);
13
votes

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

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!

6
votes

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();
3
votes

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.

1
votes

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.