如何在Apache POI 5.1.0中使用数组溢出 避免公式自动添加@运算符

问题原因 #

使用Apache POI生成Excel文件时，普通公式写入方法setCellFormula不会标记公式为动态数组类型，支持动态数组特性的Excel版本打开文件时，会对返回多值的公式自动触发隐式交集逻辑，添加@运算符，仅返回单个单元格结果。

修复方案 #

Apache POI 4.1.0及以上版本已提供动态数组公式专属写入API，无需修改公式内容，仅需替换公式写入逻辑即可：

核心修改点 #

将原有代码中设置公式的行：

cell.setCellFormula(f"""IF($otherSheetContent="", "", $otherSheetContent)""")

替换为：

// 需将Cell强转为XSSFCell以调用动态数组相关API
cell.asInstanceOf[XSSFCell].setDynamicArrayFormula(f"""IF($otherSheetContent="", "", $otherSheetContent)""", null)

完整修改后代码 #

import org.apache.poi.ss.usermodel.CellType
import org.apache.poi.xssf.usermodel.XSSFCell
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)

    // 向另一张工作表填充测试数据
    val otherSheet = workbook.createSheet("otherSheet")
    val otherRow = otherSheet.createRow(0)
    for (i <- 0 to 10) {
      otherRow.createCell(i, CellType.STRING).setCellValue("测试内容")
    }

    // 写入动态数组公式实现跨表批量复制
    val otherSheetContent = f"otherSheet!A:K"
    cell.asInstanceOf[XSSFCell].setDynamicArrayFormula(f"""IF($otherSheetContent="", "", $otherSheetContent)""", null)
    println(cell.getCellFormula) // 输出：IF(otherSheet!A:K="", "", otherSheet!A:K)

    // 保存文件
    val file = Paths.get("workbook.xlsx")
    workbook.write(new FileOutputStream(file.toFile))

  }
}

注意事项 #

• 必须使用4.1.0及以上版本的Apache POI，低版本无动态数组公式相关API
• 生成的文件仅可在支持动态数组特性的Excel版本（Excel 2021、Excel 365）中正常实现溢出效果，旧版本Excel打开仍会显示@运算符
• 无需手动为公式添加大括号作为传统数组公式处理，动态数组公式不需要手动标记数组属性

内容的提问来源于stack exchange，提问作者Thibaut Septon