How to use Array Spilling with Apache POI 5.1.0

Using Apache POI to generate excel files, is it possible to prevent Excel from adding the implicit intersection operator (@) inside formulas ?

For instance, using the following code, what I want to do is copy all the values inside the columns from A to K, using Excel Array Spilling behaviour. However, when opening the file using Excel Desktop (version 16.54), it automatically adds the @ operator inside the formula.

Inside of the workbook sheet sheet, in cell A1, instead of =IF(otherSheet!A:K=""; ""; otherSheet!A:K), I get =@IF(@otherSheet!A:K=""; ""; otherSheet!A:K) which does not have the same result since I only get the value inside A1 from anotherSheet.

import org.apache.poi.ss.usermodel.CellType
import org.apache.poi.xssf.usermodel.XSSFWorkbook

import java.io.FileOutputStream
import java.nio.file._

object Main {
  def main(args: Array[String]): Unit = {

    val workbook = new XSSFWorkbook()
    val sheet = workbook.createSheet("sheet")
    val row = sheet.createRow(0)
    val cell = row.createCell(0, CellType.FORMULA)

    // Filling dummy data to another sheet
    val otherSheet = workbook.createSheet("otherSheet")
    val otherRow = otherSheet.createRow(0)
    for (i <- 0 to 10) {
      otherRow.createCell(i, CellType.STRING).setCellValue("Something")
    }

    // Copying values
    val otherSheetContent = f"otherSheet!A:K"
    cell.setCellFormula(f"""IF($otherSheetContent="", "", $otherSheetContent)""")
    println(cell.getCellFormula) // IF(otherSheet!A:K="", "", otherSheet!A:K)

    // Saving file
    val file = Paths.get("workbook.xlsx")
    workbook.write(new FileOutputStream(file.toFile))

  }
}

Answer

You cannot use Dynamic array formulas and spilled array behavior with Apache POI 5.1.0. The spilled array behavior was introduced in Excel version 365. It is not usable in former versions. And Apache POI bases on Office Open XML published with Excel 2007. So Excel files generated using Apache POI are Excel 2007 files.

Why the added @? This 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 IF does not expect an array in any of its parameters.

The only thing you can achieve using Apache POI, is setting an legacy array formula. 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 dummy data to another sheet
   Sheet otherSheet = workbook.createSheet("otherSheet");
   for (int r = 0; r < 5; r++) {
    row = otherSheet.createRow(r);
    for (int c = 0; c < 11; c++) {
     row.createCell(c).setCellValue("OS-R" + (r+1) + "C" + (c+1));
    }
   }
   
   row = sheet.createRow(0);
   cell = row.createCell(0);
   cell.setCellFormula("IF(otherSheet!A1:K5="", "", otherSheet!A1:K5)");
   if (cell instanceof XSSFCell) {
    setArrayToFormula((XSSFCell)cell, "A1:K5");
   }

   workbook.write(fileout);
  }

 }
}

But should you do things like that, either using spilled array behavior of legacy array formulas? No, you should not, in my opinion. If you are using the formula =IF(otherSheet!A:K="", "", otherSheet!A:K) using spilled array behavior, the resulting file will be of huge size. This is because of the full column reference A:K which spans 1,048,576 rows. Same for legacy arrays. One never should use arrays having full column references.