How to use evaluateInCell with cell of IFERROR funcation?

Question

set cell Formula :

IFERROR("FY"&YEAR("2019/04/26")-"6","NA")

look cellType:

evaluateFormulaCell:

get cell value：

but :

and I try this:

update:

@Test
    public void ifERRORTest() {
        Workbook workbook = new XSSFWorkbook();
        workbook.setForceFormulaRecalculation(true);
        Sheet sheet = workbook.createSheet("1");
        Cell cell = sheet.createRow(0).createCell(0);
        cell.setCellFormula("IFERROR(\"FY\"&2019-\"1\",\"NA\")");
//        cell.setCellFormula("YEAR(\"2018-01-01\")");
        FormulaEvaluator formulaEvaluator = workbook.getCreationHelper().createFormulaEvaluator();
        formulaEvaluator.evaluateInCell(cell);
        System.out.println(cell.getCellType());
        System.out.println(cell.getStringCellValue());
    }

this is work. so YEAR("2019/01/01") not work

https://stackoverflow.com/questions/10611175/poi-formula-evaluater-iferror but my version is 4.1.0 — beer, Aug 28 '19 at 03:53
Please help us to help you! Please don't post your code as picture. — Axel Richter, Aug 28 '19 at 05:39

score 3 · Accepted Answer · answered Aug 28 '19 at 05:49

Your evaluation problem is not about IFERROR but about YEAR function. The description clearly states:

Syntax

YEAR(serial_number)

The YEAR function syntax has the following arguments:

Serial_number Required. The date of the year you want to find. Dates should be entered by using the DATE function, or as results of other formulas or functions. For example, use DATE(2008,5,23) for the 23rd day of May, 2008. Problems can occur if dates are entered as text.

But your formula uses the string "2019/04/26" as parameter in YEAR. Do using YEAR(DATE(2019,4,26)) and it will work.

Example:

import java.io.FileOutputStream;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

class ExcelEvaluateIFERROR {

 public static void main(String[] args) throws Exception {

  try (Workbook workbook = new XSSFWorkbook(); 
       FileOutputStream fileout = new FileOutputStream("Excel.xlsx") ) {

   Sheet sheet = workbook.createSheet();
   Cell cell = sheet.createRow(0).createCell(0);
   //cell.setCellFormula("IFERROR(\"FY\"&YEAR(\"2019/04/26\")-\"6\",\"NA\")"); // will not work
   cell.setCellFormula("IFERROR(\"FY\"&YEAR(DATE(2019,4,26))-\"6\",\"NA\")"); // works
   FormulaEvaluator formulaEvaluator = workbook.getCreationHelper().createFormulaEvaluator();
   CellValue cellValue = formulaEvaluator.evaluate(cell);
   System.out.println(cellValue);

   workbook.write(fileout);
  }

 }
}

How to use evaluateInCell with cell of IFERROR funcation?

1 Answers1