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

Question

使用Apache POI生成excel文件，是否可以阻止excel在公式中添加隐式交集运算符(@(？

例如，使用以下代码，我想做的是使用Excel数组溢出行为将列中的所有值从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))
}
}

Answer 1

您不能在Apache POI 5.1.0中使用动态数组公式和溢出数组行为。溢出的数组行为是在Excel版本365中引入的。它在以前的版本中不可用。Apache POI基于Excel 2007发布的Office Open XML。因此，使用ApachePOI生成的Excel文件就是Excel2007文件。

为什么添加@？这在"；我们什么时候将@添加到旧公式中"隐式交集运算符的：@：

一般来说，返回多单元格范围或数组的函数如果它们是在旧版本的擅长一个常见的异常是，如果它们被封装在函数中接受数组或范围(例如SUM((或AVERAGE(((。

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

使用ApachePOI唯一能实现的就是设置一个遗留阵列公式。参见示例：

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跨越1048576行。旧式阵列也是如此。一个never应该使用具有完整列引用的数组。