POI-XSSF:细胞类型是公式，但不能获得cachedResultType

In my code, i am checking for the cell type and if the cell type is formula, then i am trying to get the cached value, like this:

在我的代码中，我检查单元格类型，如果单元格类型是公式，那么我试图获取缓存的值，如下所示:

else if(cell.getCellType() == XSSFCell.CELL_TYPE_FORMULA){
        System.out.println("formula result type:" +cell.getCachedFormulaResultType());
        switch(cell.getCachedFormulaResultType()){
            case XSSFCell.CELL_TYPE_NUMERIC:
                    ....    
                    ....        
            break;

It works fine for some cells, but in one of the cells, i get ILLEGAL STATE EXCEPTION:

它对一些单元格很有效，但是在其中一个单元格中，我获得了非法状态异常:

java.lang.IllegalStateException: Only formula cells have cached results

I am not able to find out the cause for the error. I am not using cell.getCachedFormulaResult() outside of this else if, and even this works for all of the preceding cells (which have the same formula)

我不能找出错误的原因。我不使用cell. getcachedformula()在这个else if中，甚至对于前面所有的单元格(有相同的公式)

2 个解决方案

#1

If the cached result is not available, maybe it helps to trigger the computation explicitly:

如果缓存的结果不可用，它可能有助于显式地触发计算:

Workbook wb = ...
Sheet sheet = wb.getSheetAt(0);
FormulaEvaluator evaluator = wb.getCreationHelper().createFormulaEvaluator();

// suppose your formula is in B3
CellReference cellReference = new CellReference("B3"); 
Row row = sheet.getRow(cellReference.getRow());
Cell cell = row.getCell(cellReference.getCol()); 

CellValue cellValue = evaluator.evaluate(cell);

You could do something like this e.g. inside a try/catch(IllegalStateException)

您可以这样做，例如在try/catch中(IllegalStateException)

#2

Late answer, but I was also facing the same issue with my XLSM file.

虽然回答晚了，但是我的XLSM文件也面临同样的问题。

You said,

你说的,

"and even this works for all of the preceding cells (which have the same formula)".

“甚至这也适用于所有前面的单元格(它们有相同的公式)”。

As per https://bz.apache.org/bugzilla/show_bug.cgi?id=57798, The method cell.getCachedFormulaResultType() returns an exception when the cell is part of an array formula and is not the first cell of the array formula.

根据https://bz.apache.org/bugzilla/show_bug.cgi?id=57798，当单元格是数组公式的一部分并且不是数组公式的第一个单元格时，方法cell. getcachedformula aresulttype()返回一个异常。

In my case, there were old versions of the poi and related jars lying scattered across many folders. That was causing the issue even though I had also added the latest jars. So I did some cleaning-up and that fixed the issue for me. Hope this helps someone.

在我的例子中，有旧版本的poi和相关的jar分散在许多文件夹中。这引起了这个问题，尽管我也添加了最新的jar。所以我做了一些清理工作，为我解决了这个问题。希望这可以帮助别人。

#1