poi 读取公式的值出现的问题

最新推荐文章于 2024-08-20 15:54:44 发布

tt07406

最新推荐文章于 2024-08-20 15:54:44 发布

阅读量6.6k

点赞数 1

分类专栏： Java

本文链接：https://blog.csdn.net/tt07406/article/details/82055711

版权

Java 专栏收录该内容

16 篇文章 0 订阅

订阅专栏

在使用poi读取excel表里，常常遇到类型是CELL_TYPE_FORMULA的单元格时，通常使用evaluator

Workbook wb = cell.getSheet().getWorkbook();
CreationHelper crateHelper = wb.getCreationHelper();
FormulaEvaluator evaluator = crateHelper.createFormulaEvaluator();
ret = getCellValue(evaluator.evaluateInCell(cell));
但是，在旧的版本poi中，如果对同一个cell多次使用evaluate方法，会导致cache数组越界，报错如下：

java.lang.ArrayIndexOutOfBoundsException: -2
at
org.apache.poi.ss.formula.FormulaCellCacheEntrySet.addInternal(FormulaCellCacheEntrySet.java:83)
at
org.apache.poi.ss.formula.FormulaCellCacheEntrySet.add(FormulaCellCacheEntrySet.java:72)
at
org.apache.poi.ss.formula.CellCacheEntry.addConsumingCell(CellCacheEntry.java:85)
at
org.apache.poi.ss.formula.FormulaCellCacheEntry.changeConsumingCells(FormulaCellCacheEntry.java:80)
at
org.apache.poi.ss.formula.FormulaCellCacheEntry.setSensitiveInputCells(FormulaCellCacheEntry.java:60)
at
org.apache.poi.ss.formula.FormulaCellCacheEntry.updateFormulaResult(FormulaCellCacheEntry.java:109)
at
org.apache.poi.ss.formula.CellEvaluationFrame.updateFormulaResult(CellEvaluationFrame.java:75)
at
org.apache.poi.ss.formula.EvaluationTracker.updateCacheResult(EvaluationTracker.java:94)
at
org.apache.poi.ss.formula.WorkbookEvaluator.evaluateAny(WorkbookEvaluator.java:286)
at
org.apache.poi.ss.formula.WorkbookEvaluator.evaluate(WorkbookEvaluator.java:221)
at
org.apache.poi.hssf.usermodel.HSSFFormulaEvaluator.evaluateFormulaCellValue(HSSFFormulaEvaluator.java:320)
at
org.apache.poi.hssf.usermodel.HSSFFormulaEvaluator.evaluate(HSSFFormulaEvaluator.java:182)

源代码中FormulaCellCacheEntrySet.java:83:

int startIx = cce.hashCode() % arr.length; 
      for(int i=startIx; i<arr.length; i++) { 
startIx could be negative hence the ArrayIndexOutOfBoundsException

解决方法是使用evaluate之前调用

HSSFFormulaEvaluator.clearAllCachedResultValues()

FormulaEvaluator evaluator = wb.getCreationHelper().createFormulaEvaluator();
XSSFCell formulaCell = row.createCell(6);
formulaCell.setCellFormula("Date(2011,10,6)");
CellValue cellValue = evaluator.evaluate(formulaCell);
System.out.println(cellValue.getNumberValue());

formulaCell.setCellFormula("Date(1911,3,4)");
formulaEvaluator.clearAllCachedResultValues();
cellValue = evaluator.evaluate(formulaCell);
System.out.println(cellValue.getNumberValue());
不过，在多线程环境中，这种方法不管用。
最终解决方法：不用evaluate也能求出公式的值
Workbook wb = cell.getSheet().getWorkbook(); 
CreationHelper crateHelper = wb.getCreationHelper(); 
FormulaEvaluator evaluator = crateHelper.createFormulaEvaluator();
evaluator.clearAllCachedResultValues();

 private static String getCellFormatValue(HSSFCell cell)
    {
        String cellvalue = "";
        if (cell != null) 
         {
            // 判断当前Cell的Type
            switch (cell.getCellType()) 
            {
               // 如果当前Cell的Type为NUMERIC
               case HSSFCell.CELL_TYPE_NUMERIC: 
               case HSSFCell.CELL_TYPE_FORMULA: 
               {
                  // 判断当前的cell是否为Date
                  if (HSSFDateUtil.isCellDateFormatted(cell)) {
                 // 如果是Date类型则，取得该Cell的Date值
                          Date date = cell.getDateCellValue();
                 // 把Date转换成本地格式的字符串
                          cellvalue = cell.getDateCellValue().toLocaleString();
                  }
                  // 如果是纯数字
                      else {
              switch (cell.getCachedFormulaResultType()){
                case HSSFCell.CELL_TYPE_NUMERIC: 
                    cellvalue = String.valueOf(cell.getNumericCellValue());
                    break;
                case HSSFCell.CELL_TYPE_STRING:
                    cellvalue = cell.getRichStringCellValue.toString();
                    break;
                case HSSFCell.CELL_TYPE_ERROR:
                    throw new Exception();
               }
                       break;
               }
               // 如果当前Cell的Type为STRIN
               case HSSFCell.CELL_TYPE_STRING:
                  // 取得当前的Cell字符串
                  cellvalue = cell.getStringCellValue().replaceAll("'", "''");
                  break;
               // 默认的Cell值
               default:
                  cellvalue = " ";
            }
         }
         else 
         {
            cellvalue = "";
         }
        return cellvalue;
    }