如何在Apache POI 5.1.0中使用数组溢出

使用Apache POI生成Excel文件，是否可以阻止Excel在公式中添加implicit intersection operator(@)？

例如，使用以下代码，我想做的是使用Excel Array Spilling behaviour将列中的所有值从A复制到K。但是，使用Excel Desktop(版本16.54)打开文件时，它会自动在公式中添加@运算符。

在工作簿sheet工作表的单元格A1中，我得到的不是=IF(otherSheet!A:K=""; ""; otherSheet!A:K)，而是=@IF(@otherSheet!A:K=""; ""; otherSheet!A:K)，这不会有相同的结果，因为我只从anotherSheet获得A1内部的值。

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))

  }
}

---

解决方案

不能将Dynamic array formulas and spilled array behaviour与Apache POI 5.1.0一起使用。Excel 365版中引入了溢出数组行为。它在以前的版本中不可用。Apache POI基于随Excel2007发布的Office Open XML。因此，使用Apache POI生成的Excel文件是Excel 2007文件。

为什么添加了@？我们什么时候将@添加到旧公式中？Implicit intersection operator: @：

的第&q；节中说明了这一点

一般而言，返回多单元格区域或数组的函数 如果它们是在旧版本的中创作的，则将以@为前缀 Excel。..。一个常见的例外情况是，如果它们包装在一个函数中 它接受数组或范围(例如SUM()或Average())。

因此添加了@，因为IF的任何参数中都不需要数组。

使用Apache POI唯一可以实现的功能是设置遗留数组公式。参见示例：

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);
  }

 }
}

但是您是否应该这样做，或者使用遗留数组公式的溢出数组行为？不，在我看来，你不应该这样做。如果使用公式=IF(otherSheet!A:K="", "", otherSheet!A:K)并使用溢出的数组行为，则生成的文件将非常大。这是因为整个列引用A:K跨越1,048,576行。旧式阵列也是如此。一个从不应使用具有完整列引用的数组。