我们试图在java maven项目中使用poi来计算xlsx公式,但是如果公式有一个范围或数组,输出值是重复的。
下面是预期与POI输出:
预期产出
poi输出
列J和K没有任何公式单元格。列L和M具有依赖于J和K数据的公式单元格。
下面是在J列和K列的7行中定义的公式:
Column J :
=IF(COUNTIF($J$6:$J$62,"?*")<ROW(J6)-5,"",INDEX(J:J,SMALL(IF(J$6:J$62<>"",ROW(J$6:J$62)),ROWS(J$6:J6))))
=IF(COUNTIF($J$6:$J$62,"?*")<ROW(J7)-5,"",INDEX(J:J,SMALL(IF(J$6:J$62<>"",ROW(J$6:J$62)),ROWS(J$6:J7))))
=IF(COUNTIF($J$6:$J$62,"?*")<ROW(J8)-5,"",INDEX(J:J,SMALL(IF(J$6:J$62<>"",ROW(J$6:J$62)),ROWS(J$6:J8))))
=IF(COUNTIF($J$6:$J$62,"?*")<ROW(J9)-5,"",INDEX(J:J,SMALL(IF(J$6:J$62<>"",ROW(J$6:J$62)),ROWS(J$6:J9))))
=IF(COUNTIF($J$6:$J$62,"?*")<ROW(J10)-5,"",INDEX(J:J,SMALL(IF(J$6:J$62<>"",ROW(J$6:J$62)),ROWS(J$6:J10))))
=IF(COUNTIF($J$6:$J$62,"?*")<ROW(J11)-5,"",INDEX(J:J,SMALL(IF(J$6:J$62<>"",ROW(J$6:J$62)),ROWS(J$6:J11))))
=IF(COUNTIF($J$6:$J$62,"?*")<ROW(J12)-5,"",INDEX(J:J,SMALL(IF(J$6:J$62<>"",ROW(J$6:J$62)),ROWS(J$6:J12))))
Column K :
=IF(COUNTIF($J$6:$J$62,"?*")<ROW(K6)-5,"",INDEX(K:K,SMALL(IF(K$6:K$62<>"",ROW(K$6:K$62)),ROWS(K$6:K6))))
=IF(COUNTIF($J$6:$J$62,"?*")<ROW(K7)-5,"",INDEX(K:K,SMALL(IF(K$6:K$62<>"",ROW(K$6:K$62)),ROWS(K$6:K7))))
=IF(COUNTIF($J$6:$J$62,"?*")<ROW(K8)-5,"",INDEX(K:K,SMALL(IF(K$6:K$62<>"",ROW(K$6:K$62)),ROWS(K$6:K8))))
=IF(COUNTIF($J$6:$J$62,"?*")<ROW(K9)-5,"",INDEX(K:K,SMALL(IF(K$6:K$62<>"",ROW(K$6:K$62)),ROWS(K$6:K9))))
=IF(COUNTIF($J$6:$J$62,"?*")<ROW(K10)-5,"",INDEX(K:K,SMALL(IF(K$6:K$62<>"",ROW(K$6:K$62)),ROWS(K$6:K10))))
=IF(COUNTIF($J$6:$J$62,"?*")<ROW(K11)-5,"",INDEX(K:K,SMALL(IF(K$6:K$62<>"",ROW(K$6:K$62)),ROWS(K$6:K11))))
=IF(COUNTIF($J$6:$J$62,"?*")<ROW(K12)-5,"",INDEX(K:K,SMALL(IF(K$6:K$62<>"",ROW(K$6:K$62)),ROWS(K$6:K12))))
如果我们比较上面的图像(预期与POI输出),那么我们看到POI给出了不正确的结果并重复了J和K中的项目,而MS Excel能够正确计算。
已尝试公式赋值器evaluateAll
和evaluateFormulaCell
。不会产生正确的结果。
尝试使用evaluator.clearAllCachedResultValues();
和evaluator.notifySetFormula(cell);
这看起来像poi不支持这些公式。使用XSSFWorkBook。
FileInputStream fis = new FileInputStream(inputFile);
Workbook workbook = new XSSFWorkbook(fis);
FormulaEvaluator evaluator = workbook.getCreationHelper().createFormulaEvaluator();
// some code in between, loops etc
if (cell.getCellType() == CellType.FORMULA) {
evaluator.evaluateFormulaCell(cell);
}
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>5.2.4</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>5.2.4</version>
</dependency>
1条答案
按热度按时间mitkmikd1#
这里的主要问题是Apache POI没有提供
ArrayFunction
的所有功能。在这种特殊情况下,Excel function ROW由Java
函数org.apache.poi.ss.formula.functions.RowFunc表示,该函数尚未准备好在数组上下文中运行。这可以通过改变
Java
函数的代码来改变。该代码需要考虑“如果引用是一个单元格区域,并且如果ROW作为垂直数组输入,则ROW将引用的行号作为垂直数组返回。"。我的函数RowFuncArrayReady
就是这么做的。要使该新函数成为Excel的ROW函数的默认表示形式,它需要位于org.apache.poi.ss.formula.eval.FunctionEval的
Function[] functions
数组中,索引为8。在下面的代码中,方法prepareFunctionEval
将在作为prepareFunctionEval(8, new RowFuncArrayReady());
调用时执行此操作。我的
ExcelUsingRowFormulaInArrayContext.xlsx
看起来像这样:注意,公式
{=IF(COUNTIF($J$6:$J$62,"?*")<ROW(J6)-5,"",INDEX(J:J,SMALL(IF(J$6:J$62<>"",ROW(J$6:J$62)),ROWS(J$6:J6))))}
是使用CtrlShiftEnter输入的数组公式。Apache POI无法评估Excel 365的新动态数组公式和溢出数组行为。我的
ExcelEvaluateROWFormulaAsArray
然后打印:这与Excel评估的结果相同。
要检查Excel函数的哪些Apache POI Java函数表示可以在数组上下文中工作,除了查看源代码之外没有其他方法。所有这些代码都在https://svn.apache.org/viewvc/poi/tags/REL_5_2_4/poi/src/main/java/org/apache/poi/ss/formula/和子目录中。命名函数位于/functions或/atp子目录中。
但有时操作数也需要是
ArrayFunction
s。大多数操作数位于/eval子目录中。例如,UnaryPlusEval
是数组上下文就绪的。所以+A1:A10
将在数组上下文中工作。但是ConcatEval
不是。所以A1:A10&B1:B10
不能在数组上下文中工作。但也有一些函数,其中数组上下文中的函数根本没有记录在某处。例如,对于COUNTIF function,如果在数组上下文中像
COUNTIF($B$69:B69, $D$6:$D$62)
一样使用,则不清楚会发生什么。如何处理What do you want to look for?
中的数组?微软对此没有透露任何信息。因此,我们所能做的就是检查Excel在这种情况下的行为,然后尝试使用Java编程Excel的行为。这是非常具有挑战性和容易出错。我怀疑有人会这样做。我怀疑甚至微软也不知道Excel用户发现了什么有趣的公式解决方案,特别是使用数组上下文。因此,如果有人需要那些有趣的公式解决方案,那么这一个应该使用真实的Excel应用程序。