Apache POI生成含IFS公式的Excel单元格显示#NAME?问题排查

我尝试在Excel中使用嵌套公式，根据当前工作表其他列的条件计算「Upgrade Effort」，所用公式如下：

=@IFERROR(@IFS(C3="css", IFS(D3>0.5, "High",
 D3>0.3, "Medium", D3>0.2, "Low",D3>0,"Very Low", 
 D3=0, "None")),
 IFS(D3>0.5,"High",D3>0.3,"Medium",D3>0.2,"Low",D3>0,
 "Very Low",D3=0,"None"))

通过Apache POI编写Java代码生成Excel后，公式单元格未自动计算，显示#NAME?；仅选中单元格按回车，弹出警告后公式才会正确计算。

所用Java代码示例 #

import java.awt.Desktop;
import java.io.File;
import java.io.FileOutputStream;

import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFFormulaEvaluator;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

public class CreateExcekFormula {

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

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

            Sheet dataSheet = workbook.createSheet("Data");

            Row row;
            Cell cell;
            String strFormula = "IFERROR(IFS(%s=\"css\", IFS(%s>0.5, \"High\", %s>0.3, \"Medium\", %s>0.2, \"Low\", %s>0, \"Very Low\", %s=0, \"None\")),IFS(%s>0.5,\"High\",%s>0.3,\"Medium\",%s>0.2,\"Low\",%s>0,\"Very Low\",%s=0,\"None\"))\r\n"
                    + "";
            Object[][] data = new Object[][] 
                { new Object[] 
                            { "Header", "Complexity", "Type of File", "Change", "Upgrade Effort" },
                  new Object[] 
                  { "A", "High", "css", "1" },
                  new Object[] 
                  { "A", "Low", "txt", "0.4" },
                  new Object[] 
                  { "C", "Moderate", "css", "0.4" },
                  new Object[] 
                  { "D", "High", "txt", "0.7" },
                  new Object[] 
                  { "A", "High", "css", "0.8" },
                  new Object[] 
                  { "B", "Low", "css", "0.2" },
                  new Object[] 
                  { "G", "Low", "css", "0.1" },
                  new Object[] 
                  { "G", "High", "properties", "0.4" },
                  new Object[] 
                  { "G", "High", "css", "1" },
                  new Object[] 
                  { "G", "High", "css", "0.2" },
                  new Object[] 
                  { "G", "Low", "jsp", "0.8" },
                  new Object[] 
                  { "H", "Low", "jsp", "0.6" },
                  new Object[] 
                  { "H", "Low", "html", "0.4" } };
            for (int r = 0; r < data.length; r++) {
                row = dataSheet.createRow(r);
                Object[] rowData = data[r];
                for (int c = 0; c < rowData.length; c++) {
                    cell = row.createCell(c);
                    if (rowData[c] instanceof String) {
                        cell.setCellValue((String) rowData[c]);
                    } else if (rowData[c] instanceof Number) {
                        cell.setCellValue(((Number) rowData[c]).doubleValue());
                    }
                }
                if (rowData.length < 5) {
                    cell = row.createCell(4);
                    String fileTypeCol = "C" + (r + 1);
                    String changeCol = "D" + (r + 1);
                    String formula = String.format(strFormula, fileTypeCol, changeCol, changeCol,
                            changeCol, changeCol, changeCol, changeCol, changeCol, changeCol,
                            changeCol, changeCol);
                    System.out.println("Formula ====== " + formula);
                    cell.setCellFormula(formula);
                }
            }

            XSSFFormulaEvaluator.evaluateAllFormulaCells(workbook);

            workbook.write(fileout);
            fileout.close();
            Desktop.getDesktop().open(new File("ItemFilter.xlsx"));

        }

    }
}

问题现象 #

内容的提问来源于stack exchange，提问作者pranavs