When xlsx is output @ is added to the formula

Question

I am using Apache POI to generate xlsx. I need to count the unique number of rows(by cells) through the formula in xlsx

cell.setCellFormula("SUM(1/COUNTIF(I7:I200,I7:I200))");

but if you open it through excel 2021, it is added to the formula @:

=SUM(1/COUNTIF(I7:I27,@I7:I27))

I understand Apache POI uses excel 2007 when forming xlsx, and these functions are not supported in the early excel versions. If anyone faced such a problem, how was it circumvented?

Provide the full code. You must be doing something else there. For me there is no issue. Also which apache poi version are you using? — XtremeBaumer, Jul 18 '22 at 12:41
See [here](https://stackoverflow.com/q/61138029/9758194). It will explain the "Why.....", but doesn't directly answer the "How.....". Check the linked questions to see if anything useful pops up. I'm pretty sure you'll need to find a function/method to insert an array formula instead. For example, is SetFormulaArray a thing? — JvdV, Jul 18 '22 at 14:36

Axel Richter · Answer 1 · 2022-07-19T15:04:20.957

Using current Excel versions the formula SUM(1/COUNTIF(I7:I200,I7:I200)) uses Dynamic array formulas and spilled array behavior. In former versions that formula could only worked when entered as an array formula using Ctrl+Shift+Enter.

You cannot use dynamic array formulas and spilled array behavior with Apache POI up to now.

Why the @ is added is told in section "When do we add the @ to old formulas? " of Implicit intersection operator: @:

Generally speaking, functions that return multi-cell ranges or arrays will be prefixed with @ if they were authored in an older version of Excel. ... A common exception is if they are wrapped in a function that accepts an array or range (e.g. SUM() or AVERAGE()).

So the @ was added because COUNTIF does not expect an array in its second parameter. And because of not being a legacy array formula, the usage of implicit intersection is shown.

The only thing you can achieve using Apache POI, is setting an legacy array formula as in former Excel versions. See example:

import java.io.FileOutputStream;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.apache.poi.xssf.usermodel.XSSFCell;

class CreateExcelArrayFormula {
    
 static void setArrayToFormula(XSSFCell cell, String ref) {
  if (cell.getCTCell().getF() != null) {
   cell.getCTCell().getF().setT(org.openxmlformats.schemas.spreadsheetml.x2006.main.STCellFormulaType.ARRAY);
   cell.getCTCell().getF().setRef(ref);
  }      
 }

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

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

   Sheet sheet = workbook.createSheet();
   Row row;
   Cell cell;
   
   // filling some data in I7:I200
   for (int r = 6; r < 200; r++) {
    row = sheet.createRow(r);
    row.createCell(8).setCellValue("V" + (r-6)%12);
   }
   
   row = sheet.createRow(0);
   cell = row.createCell(0);
   cell.setCellFormula("SUM(1/COUNTIF(I7:I200,I7:I200))");
   if (cell instanceof XSSFCell) {
    setArrayToFormula((XSSFCell)cell, "A1");
   }

   workbook.write(fileout);
  }

 }
}

In that case Sheet.setArrayFormula can also be used. Then the code will fit to XSSFWorkbook as well as to HSSFWorkbook. I had have cases, where Sheet.setArrayFormula had not worked for such array formulas which only return one value instead of an array of values. Thats why I had created setArrayToFormula(XSSFCell cell, String ref) for XSSF only.

import java.io.FileOutputStream;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.util.CellRangeAddress;

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

  try (
       Workbook workbook = new XSSFWorkbook(); FileOutputStream fileout = new FileOutputStream("Excel.xlsx") ) {
       //Workbook workbook = new HSSFWorkbook(); FileOutputStream fileout = new FileOutputStream("Excel.xls") ) {

   Sheet sheet = workbook.createSheet();
   Row row;
   Cell cell;
   
   // filling some data in I7:I200
   for (int r = 6; r < 200; r++) {
    row = sheet.createRow(r);
    row.createCell(8).setCellValue("V" + (r-6)%12);
   }
   
   row = sheet.createRow(0);
   cell = row.createCell(0);
   sheet.setArrayFormula("SUM(1/COUNTIF(I7:I200,I7:I200))", CellRangeAddress.valueOf("A1"));
   
   workbook.write(fileout);
  }

 }
}

All the code is tested and works using current apache poi 5.2.2. No warranty for older versions.

@wuttke: You can't. But that was not the question. Setting array formulas using `SXSSF` is not possible as this violates the streaming approach. Array formulas need access to all rows their arrays span. There is a reason why `SXSSFSheet.setArrayFormula` is not implemented. — Axel Richter, Jul 19 '22 at 08:02

When xlsx is output @ is added to the formula

1 Answers1