Array Formula in Apache poi

Question

i have an excel sheet (xslx) where I have defined an array formula (https://support.office.com/en-us/article/Guidelines-and-examples-of-array-formulas-7D94A64E-3FF3-4686-9372-ECFD5CAA57C7). That works like a charme in excel itselt. I use apache poi to manipulate some values in that excel sheet programmaticly. After that manipulation I want to recalculate the result of that array formula but I don't know how to do it. With "normal" formula I do it as following:

FormulaEvaluator evaluator = workbook.getCreationHelper().createFormulaEvaluator();
XSSFRow row = sheet.getRow(i);
Cell cell = row.getCell(j);
evaluator.evaluateCell(cell);
double value = cell.getNumericValue();

For using array formula there is the method

void setCellArrayFormula(String formula, CellRangeAddress range)

in the class XSSFCell and

public CellRange<XSSFCell> setArrayFormula(String formula, CellRangeAddress range)

in the class XSSFSheet.

After setting the formula with a call like cell.setCellArrayFormula("SUM(ABS(H2:H5))", new CellRangeAddress(2,5,7,7)); i don't know how to get the result of that formula (but i am also not sure whether this is the correct way to use the array formula in apache poi. In Excel i have to do it like this {=SUM(ABS(H2:H5))})

Does anyone know how to use the array formula in apache poi? Thank you!

bobsyouruncle bobsyouruncle · Accepted Answer · 2017-11-06T23:02:23

The current version of Apache POI (3.17) doesn't support array formulae like this. What (I believe) Sheet.setArrayFormula() does is apply the same formula to a number of cells to optimise file size.

What you want POI sometimes refers to as a "table formula" and is not supported (https://poi.apache.org/spreadsheet/formula.html).

The good news, however, is that some support for these types of formulas is coming in POI 4.0 (currently limited to matrix-type operations though, so your example still doesn't work in the current nightly). Why is this good news? Because the pieces are starting to come into place that will allow all such formulae to be supported at some point. So I think it will happen (note: I'm not involved in the POI project, I just use it, and this feature is important to me to).

Array Formula in Apache poi

1 Answers