Java EXCEL中的公式单元格显示#NAME

我正试图添加一个Excel公式来计算升级工作量的基础上，将参考其他列在同一Excel工作表的某些条件.

下面是我的公式

=@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"))

不计算公式单元格的值.当您 Select 一个单元格并按Enter键时，会出现一个弹出警告，并正确计算公式.

示例代码

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"));

        }

    }
}

收到的结果: