Apache POI 3.17 HSSF not accepting "IFERROR"

Question

I'm using Apache POI 3.17 HSSF to create a spreadsheet with a formula that uses "IFERROR". Does anyone know why this version doesn't accept "IFERROR" and show @@...? Below is a simple code. If I use XSSF it works, but I wonder why HSSF shows @@....

pom.xml

...
<dependencies>
    <dependency>
        <groupId>org.apache.poi</groupId>
        <artifactId>poi</artifactId>
        <version>3.17</version>
    </dependency>
    <dependency>
        <groupId>org.apache.poi</groupId>
        <artifactId>poi-ooxml</artifactId>
        <version>3.17</version>
    </dependency>
</dependencies>

Main.java

  public static void main(String[] args) throws Exception{
    HSSFWorkbook w = new HSSFWorkbook();
    HSSFSheet s = w.createSheet("test");

    HSSFRow r = s.createRow(0);
    HSSFCell c = r.createCell(0);

    c.setCellFormula("IFERROR(1/0,-1)");

    File f = new File("c:/temp/test.xls");
    OutputStream o = new BufferedOutputStream(new FileOutputStream(f));
    w.write(o);
    o.flush();
    o.close();

    w.close();
  }

Answer 1

It has happen to me too. As they say before Apache poi's HSSF produces *.xls files in binary BIFF format of Excel 97 - 2003. But the IFERROR function was introduced in Excel 2007. So HSSF cannot store IFERROR function properly. Only XSSF is able to do so since XSSF produces *.xlsx files in Office Open XML format of Excel 2007.

The solution I have found: In the formula adding "_xlfn." before the function that are give you an error.

In that case

//PREVIOUS
//c.setCellFormula("IFERROR(1/0,-1)");
//
//MODIFY TO
c.setCellFormula("_xlfn.IFERROR(1/0,-1)");

As I have seen "_xlfn." means that it is a formula that is not exist in that version and make it to no validate it in java and write it in the excel as you expect.