Changeset 7709


Ignore:
Timestamp:
May 15, 2019, 3:22:52 PM (2 years ago)
Author:
Nicklas Nordborg
Message:

Fixes #2177: Better handling of Excel files with formula errors

This should fix the problem with errors. If the error is due to a missing function we also try to output some information about this. It can probably be improved to cover more cases, but cells with an error should no longer crash the entire import.

File:
1 edited

Legend:

Unmodified
Added
Removed
  • branches/3.15-stable/src/core/net/sf/basedb/util/excel/XlsxToCsvUtil.java

    r7689 r7709  
    1414
    1515import org.apache.poi.poifs.filesystem.FileMagic;
     16import org.apache.poi.ss.formula.eval.NotImplementedException;
     17import org.apache.poi.ss.formula.eval.NotImplementedFunctionException;
    1618import org.apache.poi.ss.usermodel.Cell;
    1719import org.apache.poi.ss.usermodel.CellType;
    1820import org.apache.poi.ss.usermodel.DataFormatter;
    1921import org.apache.poi.ss.usermodel.DateUtil;
     22import org.apache.poi.ss.usermodel.FormulaError;
    2023import org.apache.poi.ss.usermodel.FormulaEvaluator;
    2124import org.apache.poi.ss.usermodel.Row;
     
    481484      String value = null;
    482485      CellType cellType = cell.getCellType();
     486      CellTypeWithInfo info = null;
    483487      if (cellType == CellType.FORMULA && evaluateFormulas)
    484488      {
    485         cellType = cell.getCachedFormulaResultType();
     489        info = evaluateFormula(cell);
     490        cellType = info.cellType;
    486491      }
    487492      if (cellType == CellType.NUMERIC)
     
    519524        value = dataFormat.formatCellValue(cell, formulaEval);
    520525      }
     526      if (info != null && info.info != null) value += info.info;
    521527      return value;
    522528    }
     
    535541      if (cellType == CellType.FORMULA && evaluateFormulas)
    536542      {
    537         cellType = cell.getCachedFormulaResultType();
     543        cellType = evaluateFormula(cell).cellType;
    538544      }
    539545      if (cellType == CellType.NUMERIC)
     
    563569      if (cellType == CellType.FORMULA && evaluateFormulas)
    564570      {
    565         cellType = cell.getCachedFormulaResultType();
     571        cellType = evaluateFormula(cell).cellType;
    566572      }
    567573      if (cellType == CellType.NUMERIC)
     
    575581      }
    576582      return value;
     583    }
     584   
     585    private CellTypeWithInfo evaluateFormula(Cell cell)
     586    {
     587      CellTypeWithInfo ct = new CellTypeWithInfo();
     588      try
     589      {
     590        ct.cellType = formulaEval.evaluateFormulaCell(cell);
     591      }
     592      catch (NotImplementedException ex)
     593      {
     594        cell.setCellType(CellType.ERROR);
     595        cell.setCellErrorValue(FormulaError.NAME.getCode());
     596        ct.cellType = CellType.ERROR;
     597        if (ex.getCause() instanceof NotImplementedFunctionException)
     598        {
     599          NotImplementedFunctionException nof = (NotImplementedFunctionException)ex.getCause();
     600          ct.info = nof.getFunctionName();
     601        }
     602      }
     603      catch (RuntimeException ex)
     604      {
     605        cell.setCellType(CellType.ERROR);
     606        cell.setCellErrorValue(FormulaError.VALUE.getCode());
     607        ct.cellType = CellType.ERROR;
     608      }
     609      return ct;
    577610    }
    578611   
     
    725758  }
    726759 
     760  // Used to return some error information if formula evaluation fails
     761  static class CellTypeWithInfo
     762  {
     763    CellType cellType;
     764    String info;
     765  }
     766 
    727767}
Note: See TracChangeset for help on using the changeset viewer.