0

I am applying IRR formula in workbook using apache poi and able to see the value in excel sheet. But am getting the value as #NUM! when I tried to retrieve the value using FormulaEvaluator. Example

    Cell cell = contentRow.createCell(3);
    cell.setCellFormula(irrFormula + "*12");        
    FormulaEvaluator evaluator = sheet.getWorkbook().getCreationHelper().createFormulaEvaluator();
    CellValue cellValue = evaluator.evaluate(cell);
    if (Constants.NUM_ERROR.equals(cellValue.formatAsString().trim())) {
        calculateTotal.setIrrTotal(Constants.IS_NAN);
    }else {
        double irrValue = cellValue.getNumberValue();
    }

Also I tried the below approach to get the value but its not retrieving the value. Please provide your suggestion

for(Cell cell : row) {
     if(cell.getCellType() == Cell.CELL_TYPE_FORMULA) {
        System.out.println("Formula is " + cell.getCellFormula());
        switch(cell.getCachedFormulaResultType()) {
            case Cell.CELL_TYPE_NUMERIC:
                System.out.println("Last evaluated as: " + cell.getNumericCellValue());
                break;
            case Cell.CELL_TYPE_STRING:
                System.out.println("Last evaluated as \"" + cell.getRichStringCellValue() + "\"");
                break;
        }
     }
 } 

cell.getRawValue();
Prem Kumar
  • 9
  • 3
  • 1
    #NUM! where you are getting #NUM! in excel sheet?? – abhinavsinghvirsen Feb 26 '19 at 09:41
  • I am not getting #NUM! in excel sheet. Am getting proper IRR value in excel sheet. – Prem Kumar Feb 26 '19 at 09:57
  • Eg IRR Program: OutPut: IsNan public class Sample { public static void main(String[] args) { double[] income = { -7418.53, 0.00, 1950.56, 0.00, 22.48, 71.92, 71.92, 71.92, 71.92, 71.92, 71.92, 71.92, 71.92, 71.92, 71.92, 71.92, 71.92, 71.92, 71.92, 71.92, 71.92, 71.92, 71.92, 71.92, 71.92, 71.92, 71.92, 71.92, 71.92, 71.92, 71.92, 71.92, 71.92, 71.92, 71.92, 71.92 }; double irrvalue = Irr.irr(income); System.out.println("irrvalue is " + irrvalue);System.out.println("Annual income is" + (irrvalue * 12)); } } But if put the same value is excel and output is -4 (=IRR(A1:AJ1)) – Prem Kumar Feb 26 '19 at 10:05

0 Answers0