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".
Newest
LibreOffice Calc
supportsIFS
. But if it saves*.xlsx
file then it storesIFS
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 thatLibreOffice Calc
tries saving inExcel 2016
compatible mode. The IFS function is fromOffice 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 onlyIFS
(tested today, January 21, 2019). SoLibreOffice Calc
is correct expecting that prefix too.The following works for me using
apache poi 4.0.1
for creating the*.xlsx
and usingLibreOffice Calc
(Version: 6.0.7.3 Build ID: 1:6.0.7-0ubuntu0.18.04.2) as well as usingOffice 365
for opening the*.xlsx
then.