I am having an issue where the formula that I am using is very specific. When entering the formula in via the programming, I am getting unexpected results. For example
MF = "IF(INDEX(CELL(\"width\",INDIRECT(ADDRESS(ROW(),COLUMN()))),1)<6,\"\",AGGREGATE(4,5,INDIRECT(ADDRESS(ROW()-"+readingsPerThirty+",COLUMN())):INDIRECT(ADDRESS(ROW()-1,COLUMN()))))";
I have that Excel function stored as String, to be called later as such:
data.getRow(r+1).getCell(c,Row.MissingCellPolicy.CREATE_NULL_AS_BLANK).setCellFormula(readings.get(r).getC(c));
Where data is a XSSFSheet
When the program runs the formula that actually entered into the cell is :
=@IF(@INDEX(CELL("width",INDIRECT(ADDRESS(ROW(),COLUMN()))),1)<6,"",AGGREGATE(4,5,INDIRECT(ADDRESS(ROW()-1,COLUMN())):INDIRECT(ADDRESS(ROW()-1,COLUMN()))))
I can not find any documentation or reports of where the @ symbols are coming from or what causes them. My question is why are they appearing and what can I do to prevent it. The @ causes Excel to have an error with a formula that works perfectly fine without them.
Thanks
The problem is with Excel 365 and the new functions which are normally prefixed with _xlfn in Excel's file system storage.
Lets have a complete example to reproduce that issue:
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();
}
}
The Excel.xlsx needs to have at least one worksheet having the rows 1 to 2 already. The code sets the formula in cell E2.
If the
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()))))";
is set as the formula and you are opening the resulting ExcelNew.xlsx using Excel 365 and you are trying to change the formula in E2, then you get the popup:
Why is the @ operator here?
We've upgraded Excel's Formula language and, as a result, you might notice the @ operator in some formulas. Your formulas will behave the same way they always have.
This refers to Implicit intersection operator: @.
But if we look closer, there is not even a reason to use that implicit intersection operator in that formula before IF. So why is it used?
The reason is that the new AGGREGATE function normally gets prefixed with _xlfn. in Excels file system storage. But Apache POI is not doing this. So Excel 365 interprets that function as user defined function while rendering and prefixes it with _xludf.. That's why the #NAME? error. And that's why the usage of implicit intersection operator @ at all.
So if
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()))))";
is set as the formula, where AGGREGATE is prefixed by _xlfn., the #NAME? error disappears. And the implicit intersection operator @ is not used by Excel 365.