使用Apache POI生成excel檔案，是否可以防止Excel在公式中添加隱式交集運算子（@）？
例如，使用下面的代碼，我想要做的就是復制從A至K列里面所有的值，使用Excel陣列溢位行為。但是，當使用 Excel Desktop（版本 16.54）打開檔案時，它會自動在公式中添加 @ 運算子。
在作業簿sheet表的內部，在單元格 A1 中，而不是=IF(otherSheet!A:K=""; ""; otherSheet!A:K)，我得到=@IF(@otherSheet!A:K=""; ""; otherSheet!A:K)的結果不同，因為我只從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))
}
}
uj5u.com熱心網友回復：
您不能在 Apache POI 5.1.0 中使用動態陣列公式和溢位陣列行為。Excel 版本 365 中引入了溢位陣列行為。它在以前的版本中不可用。而 Apache POI 基于Excel 2007 發布的Office Open XML。所以使用 Apache POI 生成的 Excel 檔案是 Excel 2007 檔案。
為什么添加@？這在“我們何時將 @ 添加到舊公式中？”一節中講述：隱式交集運算子：@：
一般來說，如果函式是在舊版本的 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 行。遺留陣列也是如此。一個從來不應該使用具有整列參考陣列。
轉載請註明出處，本文鏈接：https://www.uj5u.com/qukuanlian/358572.html
標籤：爪哇 擅长 斯卡拉 excel-公式 apache-poi