ManishChristian February 2016

Get value from cell containing array formula

I have XSSFWorkbook and I've put array formulas in two column and I want to copy the values from these columns and paste onto other columns.

Formula looks like this:

{=IFERROR(INDEX(Sheet2!$A$2:$C$13,MATCH(1,(Sheet2!$A$2:$A$13=$A2)*(Sheet2!$C$2:$C$13=$D2),0),2),"FAIL")}

I have placed the formula (by running a loop on multiple cells) like this (sheet is XSSFSheet):

String formula = "AboveFormulaWithIterativeCell"  // (without {} and =)
String range = "XFC" + i;  // i is iterative row number
sheet.setArrayFormula(formula, CellRangeAddress.valueOf(range));

And then evaluated the formulas like this (wb is XSSFWorkbook):

XSSFFormulaEvaluator.evaluateAllFormulaCells(wb);

Everything is working fine till this point. Formulas are displaying valid result.

Now if I run a loop on these cells and read the value like this (c is Cell):

String cellValue = c.getStringCellValue();  

It always returning "FAIL", like it is returning the result after evaluating the formula as non-array formula.

So I've tried same thing with this formula:

{=IF(TRUE,TRUE,FALSE)}

And surprisingly it is returning me TRUE.

So, my question is how this formula is returning correct result and not my main formula.

Answers


ManishChristian February 2016

So Apache Poi is still not supporting the manipulating table formulas (Array formulas).

Not yet supported Manipulating table formulas (In Excel, formulas that look like "{=...}" as opposed to "=...")

See here for more details.

So I had to convert my array formula to non-array formula.

Note:
You can still use array formulas, if you just want to display the result.
But you can not get the value from cell which contains array formula.

Post Status

Asked in February 2016
Viewed 3,971 times
Voted 7
Answered 1 times

Search




Leave an answer