Why does the logical IFS() function keep getting rendered in lowercase?

Question

I am dynamically generating XLSX files from java code, and setting the formulas as well. All work fine except for the IFS() function while always seems to render using lowercase "ifs()" and so is not recognized by libreoffice as a function when the resulting file is opened. All other formulas e.g. plain old "IF" work just fine

I've tried debugging into the POI ooxml sourcecode and the last I can tell is that the cell is being set correctly in uppercase. I've tried updating to the latest version, pre-formatting cell contents... no luck so far. This code runs on poi 4.0.1 and I'm opening the file with libreoffice 6.1.3.2 (in case this could be a libreoffice issue?). I don't have access to EXCEL 2016+ to check how that handles the resulting file.

public void testIFS(){
    try {
        String IFSformula = "IFS(1,\"yes\",0,\"no\")";
        String IFformula = "IF(1,\"yes\",\"no\")";
        String outputFileName = "IFStest.xlsx";
        XSSFWorkbook workbook = new XSSFWorkbook();
        XSSFSheet poiSheet = workbook.createSheet("ifstest");
        XSSFRow row = poiSheet.createRow(0);
        XSSFCell cell0 = row.createCell(0);
        cell0.setCellFormula(IFSformula);
        XSSFCell cell1 = row.createCell(1);
        cell1.setCellFormula(IFformula);
        workbook.write(new FileOutputStream(outputFileName));
    } catch (IOException ex) {
        Logger.getLogger(IFSTest.class.getName()).log(Level.SEVERE, null, ex);
    }
}

Now, cell 0 ends up with =ifs(1,"yes",0,"no") - which is wrong (result is #NAME), but cell 1 works just fine and has a cell formula =IF(1,"yes","no") (result "yes"). If I manually change "ifs" to "IFS" the formula works fine and also shows "yes".

Answer 1

Newest LibreOffice Calc supports IFS. But if it saves *.xlsx file then it stores IFS formulas using _xlfn prefix. Normally that _xlfn prefix means The Excel workbook contains a function that is not supported in the version of Excel that you are currently running.. So seems that LibreOffice Calc tries saving in Excel 2016 compatible mode. The IFS function is from Office 365 upwards only. And since it stores using that prefix, it seems expecting that prefix while reading *.xlsx too.

And even Office 365 Excel stores _xlfn.IFS into the *.xlsx file instead of only IFS (tested today, January 21, 2019). So LibreOffice Calc is correct expecting that prefix too.

The following works for me using apache poi 4.0.1 for creating the *.xlsx and using LibreOffice Calc (Version: 6.0.7.3 Build ID: 1:6.0.7-0ubuntu0.18.04.2) as well as using Office 365 for opening the *.xlsx then.

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

class TestIFS {

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

  try (XSSFWorkbook workbook = new XSSFWorkbook(); FileOutputStream out = new FileOutputStream("TestIFS.xlsx")) { 
   String formulaIFS = "_xlfn.IFS(1=0,\"first\",0=0,\"second\")";
   String formulaIF = "IF(1=0,\"yes\",\"no\")";
   Sheet sheet = workbook.createSheet("IFStest");
   Row row = sheet.createRow(0);
   Cell cell = row.createCell(0);
   cell.setCellFormula(formulaIFS);
   cell = row.createCell(1);
   cell.setCellFormula(formulaIF);
   workbook.write(out); 
  }
 }
}