记录一次 ApachePoi 表格导入的问题

起因

上周客户在导入数据的时候发现导入的表格（含有计算公式）结果的值有些为空，查后台日志发现是导入失败，Exception: IllegalStateException: value changed。

猜测

将表格拿到开发环境测试成功复现问题，打断点查看 StackTrace 找到如下代码：

public class EvaluationCache {
    
    //...........
    
    public PlainValueCellCacheEntry getPlainValueEntry(int bookIndex, int sheetIndex,
                                                       int rowIndex, int columnIndex, ValueEval value) {

        Loc loc = new Loc(bookIndex, sheetIndex, rowIndex, columnIndex);
        PlainValueCellCacheEntry result = _plainCellCache.get(loc);
        if (result == null) {
            result = new PlainValueCellCacheEntry(value);
            _plainCellCache.put(loc, result);
            if (_evaluationListener != null) {
                _evaluationListener.onReadPlainValue(sheetIndex, rowIndex, columnIndex, result);
            }
        } else {
            // TODO - if we are confident that this sanity check is not required, we can remove 'value' from plain value cache entry
            if (!areValuesEqual(result.getValue(), value)) {
                throw new IllegalStateException("value changed"); //在这里报错！
            }
            if (_evaluationListener != null) {
                _evaluationListener.onCacheHit(sheetIndex, rowIndex, columnIndex, value);
            }
        }
        return result;
    }
    
    //................
    
}

打断点找到导致缓存和当前值不一致的值为单元格值：0.7734，表格中类型为数值。 抛出错误时缓存的值（PlainValueCellCacheEntry result）为 0.7734、当前值（ValueEval value）为 0.77339999....998，推测为该单元格参与了两次公式计算、且两次获取到的值不一致。

继续深入

找到 ValueEval value 的值是从哪来的

往上找到调用 getPlainValueEntry 的方法

public class EvaluationTracker {
    
    //.............

    public void acceptPlainValueDependency(int bookIndex, int sheetIndex,
                                           int rowIndex, int columnIndex, ValueEval value) {
        // Tell the currently evaluating cell frame that it has a dependency on the specified
        int prevFrameIndex = _evaluationFrames.size() - 1;
        if (prevFrameIndex < 0) {
            // Top level frame, there is no 'cell' above this frame that is using the current cell
        } else {
            CellEvaluationFrame consumingFrame = _evaluationFrames.get(prevFrameIndex);
            if (value == BlankEval.instance) {
                consumingFrame.addUsedBlankCell(bookIndex, sheetIndex, rowIndex, columnIndex);
            } else {
                PlainValueCellCacheEntry cce = _cache.getPlainValueEntry(bookIndex, sheetIndex,
                        rowIndex, columnIndex, value);
                consumingFrame.addSensitiveInputCell(cce);
            }
        }
    }
}

往上找到调用 acceptPlainValueDependency 的方法

public class WorkbookEvaluator {
    
    //....................
    
    private ValueEval evaluateAny(EvaluationCell srcCell, int sheetIndex,
                                  int rowIndex, int columnIndex, EvaluationTracker tracker) {

        // avoid tracking dependencies to cells that have constant definition
        boolean shouldCellDependencyBeRecorded = _stabilityClassifier == null ? true
                : !_stabilityClassifier.isCellFinal(sheetIndex, rowIndex, columnIndex);
        if (srcCell == null || srcCell.getCellTypeEnum() != CellType.FORMULA) {
            ValueEval result = getValueFromNonFormulaCell(srcCell); //找到 result 值是从哪来的
            if (shouldCellDependencyBeRecorded) {
                tracker.acceptPlainValueDependency(_workbookIx, sheetIndex, rowIndex, columnIndex, result);
            }
            return result;
        }
        //............
    }
    
    //.....................

    /* package */ static ValueEval getValueFromNonFormulaCell(EvaluationCell cell) {
        if (cell == null) {
            return BlankEval.instance;
        }
        CellType cellType = cell.getCellTypeEnum();
        switch (cellType) {
            case NUMERIC:
                return new NumberEval(cell.getNumericCellValue());
            case STRING:
                return new StringEval(cell.getStringCellValue());
            case BOOLEAN:
                return BoolEval.valueOf(cell.getBooleanCellValue());
            case BLANK:
                return BlankEval.instance;
            case ERROR:
                return ErrorEval.valueOf(cell.getErrorCellValue());
            default:
                throw new RuntimeException("Unexpected cell type (" + cellType + ")");
        }
    }
    
    //......................
    
}

最终定位到 getValueFromNonFormulaCell 方法为 value 的来源

找出为什么 getValueFromNonFormulaCell 返回的值会出现两种结果（0.7734 | 0.77339999....998）

打断点发现返回 0.7734 时，cellType 为 STRING，找到了导致 value changed 原因。

解决

最终在项目所使用的工具类中找到了有修改 CellType 的代码

public class ExcelReader{
    private static String getValue(Cell c, XSSFFormulaEvaluator eval) {
        if (c.getCellTypeEnum() == CellType.FORMULA)
            return eval.evaluate(c).formatAsString().replaceAll("\"", "");
        else {
            c.setCellType(CellType.STRING); //此处修改了单元格类型
            return c.getStringCellValue().trim();
        }
    }
}

修改为使用 c.toString() 方法避免修改单元格类型。

注意

直接使用 c.toString() 方法可能会导致某些长数字单元格获取到的值为科学计数法结果的问题！如：2.0190263013071E13

public class ExcelReader{
    private static String getValue(Cell c, XSSFFormulaEvaluator eval) {
        if (c.getCellTypeEnum() == CellType.FORMULA)
            return eval.evaluate(c).formatAsString().replaceAll("\"", "");
        else {
            //c.setCellType(CellType.STRING);
            //return c.getStringCellValue().trim();
            return c.toString().trim();
        }
    }
}