POI Formula Evaluater IFERROR

Question

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)

IFERROR(VLOOKUP(C2,Ingredient!A:B,2,FALSE),IFERROR(VLOOKUP(C2,Prep!A:B,2,FALSE),IFERROR(VLOOKUP(C2,Menu!A:B,2,FALSE),"#N/A"))) This is the actula formula nee to evaluate — ShijuJohn1093, May 16 '12 at 02:19
Are you willing to put in a little bit of work, and implement this missing formula function yourself? — Gagravarr, May 16 '12 at 02:49
@Gagravarr i just checked the formula of cell if it contain "IFERROR" string then i wrote a parser to parse its both argument "its recursive" again if parameter contain IFERROR then do the same arguments extracting else set the current subformula to cell and evaluate it ... and finally set the original formula back only to maintaine workbook original — ShijuJohn1093, May 22 '12 at 07:00

Unglued · Answer 1 · 2014-07-11T01:57:11.687

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

score 6 · Accepted Answer · answered Jun 19 '14 at 12:04

6

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

answered Jun 19 '14 at 12:04

fgb

18,439
2
38
52

Thanks a ton for posting this update. Helped me solve the error quickly. – Sushant Gupta Jul 08 '14 at 07:24

POI Formula Evaluater IFERROR

2 Answers2

Linked