POI Formula Evaluater IFERROR

问题:

I am using POI 3.8 to read the excel i am using User model api of POI which is capable of reading HSSF and XSSF both but there is some problem in evaluating the fomula POI 3.8 doesnt support the IFERROR function of Excle is there any alternate and i dont want to convert the formula to ISERROR caz it not supported by older version of excel.

I know POI 3.8 doesnt support IFERROR but what can i do to make it done -- Thanks in advance

os this Following is the Exception Exception in thread "main" org.apache.poi.ss.formula.eval.NotImplementedException: Error evaluating cell Sheet1!F1 at org.apache.poi.ss.formula.WorkbookEvaluator.addExceptionInfo(WorkbookEvaluator.java:356) at org.apache.poi.ss.formula.WorkbookEvaluator.evaluateAny(WorkbookEvaluator.java:297) at org.apache.poi.ss.formula.WorkbookEvaluator.evaluate(WorkbookEvaluator.java:229) at org.apache.poi.xssf.usermodel.XSSFFormulaEvaluator.evaluateFormulaCellValue(XSSFFormulaEvaluator.java:264) at org.apache.poi.xssf.usermodel.XSSFFormulaEvaluator.evaluate(XSSFFormulaEvaluator.java:117) at TestFormula.cellValue(TestFormula.java:48) at TestFormula.loadCell(TestFormula.java:37) at TestFormula.loadRows(TestFormula.java:29) at TestFormula.testRun(TestFormula.java:22) at FISValidator.main(FISValidator.java:27) Caused by: org.apache.poi.ss.formula.eval.NotImplementedException: IFERROR at org.apache.poi.ss.formula.atp.AnalysisToolPak$NotImplemented.evaluate(AnalysisToolPak.java:40) at org.apache.poi.ss.formula.UserDefinedFunction.evaluate(UserDefinedFunction.java:64) at org.apache.poi.ss.formula.OperationEvaluatorFactory.evaluate(OperationEvaluatorFactory.java:129) at org.apache.poi.ss.formula.WorkbookEvaluator.evaluateFormula(WorkbookEvaluator.java:491) at org.apache.poi.ss.formula.WorkbookEvaluator.evaluateAny(WorkbookEvaluator.java:287)

回答1:

IFERROR has now been implemented in poi-3.10. So you can upgrade to that version without the need for hacks.

回答2:

EDIT: My answer has been outdated due to the fact that newer Apache POI APIs support IFERROR()

---

I've recently had this problem w/ IFERROR as well. So I've written a little hack for it. Hope this helps:

Note that IFERROR(value, value_if_error) works the same as IF(ISERROR(value), value_if_error, value)

so I've replaced these formulas before evaluating them. All you need to do is to call replaceIfErrorFormulas() that it will go through the entire sheet automatically.

public static final int SIZE = "IFERROR(".length();   

private void replaceIfErrorFormulas(Sheet pSheet)
{
   for (Row row : pSheet)
   {
      for (Cell cell : row)
      {
         if ((cell != null) && 
             (cell.getCellType() == Cell.CELL_TYPE_FORMULA) &&
             (cell.getCellFormula().indexOf("IFERROR") != -1))
         {
            cell.setCellFormula(buildFormulaString(cell.getCellFormula()));
         }
      }
   }
}

private String buildFormulaString(String pFormula)
{
   if (pFormula.indexOf("IFERROR") == -1)
   {
      return pFormula;
   }

   String[] values = new String[2]; // will hold value, value_if_error
   char[] tokens = pFormula.toCharArray();
   int length = computeLength(pFormula); // length of IFERROR substring
   int iBegin = pFormula.indexOf("IFERROR");
   int iEnd = iBegin + length - 1;
   assert (iEnd < pFormula.length());      
   int iParam = 0;  // 0: value; 1: value_if_error
   int numPar = 0;  // number of parentheses

   // Split the parameters into VALUE and VALUE_IF_ERROR in values[]
   for (int i = iBegin; i < (length + iBegin) ; i++)
   {
      if (tokens[i] == '(')
      {
         values[iParam] += tokens[i];
         numPar++;
      }
      else if (tokens[i] == ')')
      {
         if (iParam < 1)
         {
            values[iParam] += tokens[i];
         }
         numPar--;
      }
      else if (Character.getType(tokens[i]) == Character.MATH_SYMBOL)
      {
         values[iParam] += tokens[i];
      }
      else if (tokens[i] == ',')
      {
         if (numPar > 1)
         {
            values[iParam] += tokens[i];
         }
         else
         {
            values[iParam++] += ')';
            numPar--;
         }
      }
      else
      {
         values[iParam] += tokens[i];
      }
      if (numPar < 0 && iParam == 1)
      {
         break;
      }
   }

   // Re-assign those parameters back to strings, removing the null character 
   String value        = values[0];
   String valueIfError = values[1];
   value        = value.substring(4 + SIZE - 1);
   valueIfError = valueIfError.substring(4);


   // Build new Formula that is equivalent to the old one.
   String newFormula = "IF(ISERROR(" + value + ")," 
                                     + valueIfError + ","
                                     + value +")";

   // Concatenate the untouched parts of the old formula to the new one      
   String left = pFormula.substring(0, iBegin);
   String right = pFormula.substring(iEnd + 1, pFormula.length());
   newFormula = left + newFormula + right;

   return buildFormulaString(newFormula);
}

// by checking the parentheses proceededing IFERROR, this method
// determines what is the size of the IFERROR substring
private int computeLength(String pFormula) 
{
   int length = SIZE;
   int numPar = 1; // Number of parentheses
   int iStart = pFormula.indexOf("IFERROR");
   char [] tokens = pFormula.toCharArray();

   for (int i = length + iStart; i < pFormula.length(); i++)
   {
      if (numPar == 0)
          break;
      if (tokens[i] == '(')
          numPar++;
      else if (tokens[i] == ')')
          numPar--;
      length++;
   }
   return length;
}

---

update: I've modified the formula to MUCH BETTER! =D Before: it would only replace the formula if it were in the beginning of the string, and if it didn't have any other formulas or parameters besides it. After: Now it searches the whole string for instances of IFERROR and replaces them ALL =D