CODEWITHSUNDEEP
javaapache-poi
Wade Nolan
Wade Nolan

Reputation: 53

Apache POI Excel Formula entering @ Symbols where they don't belong

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

Upvotes: 4

Views: 1527

Answers (1)

Axel Richter
Axel Richter

Reputation: 61860

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.

Upvotes: 10

Related Questions