java - Apache POI Excel 公式输入不属于它们的@符号

Question

我遇到了一个问题，我使用的公式非常具体。通过编程输入公式时，我得到了意想不到的结果。例如

MF = "IF(INDEX(CELL(\"width\",INDIRECT(ADDRESS(ROW(),COLUMN()))),1)<6,\"\",AGGREGATE(4,5,INDIRECT(ADDRESS(ROW()-"+readingsPerThirty+",COLUMN())):INDIRECT(ADDRESS(ROW()-1,COLUMN()))))";

我将该 Excel 函数存储为字符串，稍后将被调用:

data.getRow(r+1).getCell(c,Row.MissingCellPolicy.CREATE_NULL_AS_BLANK).setCellFormula(readings.get(r).getC(c));

其中数据是 XSSFSheet
当程序运行时，实际输入单元格的公式是:

=@IF(@INDEX(CELL("width",INDIRECT(ADDRESS(ROW(),COLUMN()))),1)<6,"",AGGREGATE(4,5,INDIRECT(ADDRESS(ROW()-1,COLUMN())):INDIRECT(ADDRESS(ROW()-1,COLUMN()))))

我找不到任何关于@ 符号来自何处或导致它们的原因的文档或报告。我的问题是它们为什么会出现，我可以做些什么来防止它出现。 @ 导致 Excel 出现错误，公式在没有它们的情况下也能正常工作。
谢谢

Answer 1

问题出在 Excel 365以及通常以 _xlfn 为前缀的新函数在 Excel的文件系统存储。
让我们有一个完整的例子来重现这个问题:

import java.io.FileInputStream;
import java.io.FileOutputStream;

import org.apache.poi.ss.usermodel.*;

class ExcelSetCellFormula {
 
 static final String WRONG = "IF(INDEX(CELL(\"width\",INDIRECT(ADDRESS(ROW(),COLUMN()))),1)<6,\"\",AGGREGATE(4,5,INDIRECT(ADDRESS(ROW()-1,COLUMN())):INDIRECT(ADDRESS(ROW()-1,COLUMN()))))";

 static final String CORRECT = "IF(INDEX(CELL(\"width\",INDIRECT(ADDRESS(ROW(),COLUMN()))),1)<6,\"\",_xlfn.AGGREGATE(4,5,INDIRECT(ADDRESS(ROW()-1,COLUMN())):INDIRECT(ADDRESS(ROW()-1,COLUMN()))))";

 public static void main(String[] args) throws Exception {
  
  Workbook workbook = WorkbookFactory.create(new FileInputStream("./Excel.xlsx")); String filePath = "./ExcelNew.xlsx";
  Sheet data = workbook.getSheetAt(0);

  int r = 0;
  int c = 4;
  data.getRow(r+1).getCell(c,Row.MissingCellPolicy.CREATE_NULL_AS_BLANK).setCellFormula(WRONG);
  //data.getRow(r+1).getCell(c,Row.MissingCellPolicy.CREATE_NULL_AS_BLANK).setCellFormula(CORRECT);
  System.out.println(data.getRow(r+1).getCell(c));

  FileOutputStream out = new FileOutputStream(filePath);
  workbook.write(out);
  out.close() ;
  workbook.close();
 }
}

Excel.xlsx需要至少有一个工作表已经包含第 1 到 2 行。代码在单元格中设置公式 E2 .
如果

static final String WRONG = "IF(INDEX(CELL(\"width\",INDIRECT(ADDRESS(ROW(),COLUMN()))),1)<6,\"\",AGGREGATE(4,5,INDIRECT(ADDRESS(ROW()-1,COLUMN())):INDIRECT(ADDRESS(ROW()-1,COLUMN()))))";

设置为公式，您正在打开结果 ExcelNew.xlsx使用 Excel 365并且您正在尝试更改 E2 中的公式，然后你会得到弹出窗口:

Why is the @ operator here?

We've upgraded Excel's Formula languageand, as a result, you might notice the @ operator in some formulas.Your formulas will behave the same way they always have.

这是指 Implicit intersection operator: @ .
但是，如果我们仔细观察，在 IF 之前，甚至没有理由在该公式中使用该隐式交集运算符。 .那么为什么要使用它呢？
原因是新 AGGREGATE函数通常以 _xlfn. 为前缀在 Excel s 文件系统存储。但是 Apache POI不是这样做的。所以 Excel 365在渲染时将该函数解释为用户定义的函数，并为其添加前缀 _xludf. .这就是为什么 #NAME?错误。这就是为什么使用隐式交集运算符 @根本。
因此，如果

static final String CORRECT = "IF(INDEX(CELL(\"width\",INDIRECT(ADDRESS(ROW(),COLUMN()))),1)<6,\"\",_xlfn.AGGREGATE(4,5,INDIRECT(ADDRESS(ROW()-1,COLUMN())):INDIRECT(ADDRESS(ROW()-1,COLUMN()))))";

设置为公式，其中 AGGREGATE以 _xlfn. 为前缀, #NAME?错误消失。和隐式交集运算符 @ Excel 365 不使用.