如何使用Java在Excel中添加動態數組公式？

本文由葡萄城技術團隊發布。轉載請注明出處：葡萄城官網，葡萄城為開發者提供專業的開發工具、解決方案和服務，賦能開發者。

前言

動態數組公式是 Excel 引入的一項重要功能，它將 Excel 分為兩種風格：Excel 365 和傳統 Excel（2019 或更早版本）。動態數組功能允許用戶從單個單元格中的公式返回多個結果值，并將這些值自動填充到與公式單元格相鄰的單元格中。由于結果會溢出到多個單元格中，這也被稱為溢出范圍功能。在傳統 Excel 中，用戶必須使用 Ctrl + Shift + Enter 來將公式識別為數組公式，否則公式結果將僅返回單個值。因此，動態數組為用戶提供了更加便利的使用體驗。現在我們將這樣的返回多個值的公式稱為動態數組公式。

今天小編就為大家介紹如何使用葡萄城公司的Java API 組件GrapeCity Documents for Excel（以下簡稱GcExcel）實現在Excel中添加動態數組。

動態數組公式

下表總結了GcExcel所有支持的公式及其語法、說明和代碼示例：

功能	代碼片段	圖像
FILTER ：FILTER 公式根據您指定的條件篩選范圍或數組。 FILTER(array,include,[if_empty])	sheet.getRange(“I4”).setFormula2(“=FILTER(D4:E12,E4:E12>G4,\”\“)”);
RANDARRAY：RANDARRAY 公式允許您在數組中生成從 0 到 1 的隨機 numbers 列表。 RANDARRAY ([rows],[columns])	sheet.getRange(“$D$3”).setFormula2(“=RANDARRAY(4,5)”);
SEQUENCE ：SEQUENCE 公式允許您在數組中生成序列號列表。 SEQUENCE(rows,[columns],[start],[step])	sheet.getRange(“$D$4”).setFormula2(“=SEQUENCE(10,100,-10)”);
SORTBY ：SORTBY 公式允許您根據相應范圍或數組中的值對范圍或數組進行排序。 SORTBY(array, by_array1, [sort_order1], [[by_array2], [sort_order2]],…)	sheet.getRange(“$G$5”).setFormula2(“=SORTBY($D$5:$E$12,$E$5:$E$12)”);

SORT：SORT 公式用于按升序或降序對范圍或數組進行排序。 SORT(array, [sort_index], [sort_order], [by_col])	sheet.getRange(“$I$5”).setFormula2(“=SORT(D5: G13,4,1, FALSE)”);
UNIQUE：UNIQUE 公式允許您從項目范圍或數組中返回唯一列表。UNIQUE(array, [by_col], occurs_once])	sheet.getRange(“$G$4:$I$4”).setFormula2(“=UNIQUE(B4: B12)”);

詳細代碼請點擊這里。

@ 運算符

@ 運算符（也稱為隱式**交集運算符）**實現一種稱為隱式交集的公式行為，該行為將一組值減少為單個值。這適用于返回多個值的數組公式，在這種情況下，將根據單元格位置（即行和列）返回單個值。在老版本 Excel 中，這是默認行為，因此不需要顯式運算符。但是，在 Excel 365 中，所有公式都是數組公式，因此，如果您不希望數組公式溢出，則可以在公式前面加上 @ 運算符，它只會返回一個值。

實現代碼：

public void ImplicitIntersection() {Workbook wb = new Workbook();//初始化工作表IWorksheet sheet = wb.getWorksheets().get(0);sheet.setName("IMPLICIT INTERSECTION");//添加樣例數據sheet.getRange("$D$3:$D12").setValue(new Object[]{"Products", "Apple", "Grape", "Pear", "Banana","Apple", "Grape", "Pear", "Banana", "Banana"});sheet.getRange("$E$3").setValue("Unique Products");//添加含有隱式運算符的動態數組公式sheet.getRange("$E$4").setFormula2("=@UNIQUE(D4:D12)");wb.save("output/ImplicitIntersection.xlsx");
}

實現效果：

溢出范圍參考（#）

動態數組公式將其多值結果溢出到的單元格范圍稱為**溢出范圍。**每當單擊溢出范圍中的任何單元格時，溢出范圍都會以藍色邊框突出顯示，表示該范圍中的所有值都是通過該范圍左上角單元格中的公式計算得出的。

溢出范圍參考運算符用于引用此溢出范圍。要引用溢出范圍，請在溢出范圍中左上角單元格的地址后放置一個主題標簽或井號（#）。

例如，若要查找單元格 E4 中應用的 UNIQUE 公式提取了多少個唯一值，請提供對 COUNTA 公式的溢出范圍引用，如下面的代碼所示：

public void SpillReference() {Workbook wb = new Workbook();//初始化工作表IWorksheet sheet = wb.getWorksheets().get(0);sheet.setName("SPILL REFERENCE");//添加樣例數據sheet.getRange("$D$3:$D12").setValue(new Object[]{"Products", "Apple", "Grape", "Pear", "Banana","Apple", "Grape", "Pear", "Banana", "Banana"});sheet.getRange("$E$3").setValue("Unique Products");sheet.getRange("F3").setValue("Unique Products Count");//設置動態數組公式sheet.getRange("$E$4").setFormula2("=UNIQUE(D4:D12)");//設置帶有溢出范圍引用的公式sheet.getRange("$F$4").setFormula2("=COUNTA(E4#)");wb.save("output/SpillReference.xlsx");
}

實現效果：

現在，我們已經了解了 GcExcel 如何在 Java 中為動態數組公式提供支持，下面小編將用一個簡單的例子展示動態數組公式的用途。

用例：使用動態數組在 Excel 中創建交互式圖表

考慮這樣一個場景：我們有一些表格數據，想用柱狀圖來展示。通常情況下，表格數據有很多行和列，但在制作圖表時，我們需要按照某些條件來選擇部分數據。為了提取所需數據，我們需要根據一些條件來進行篩選，這就是動態數組公式的應用場景。我們使用FILTER函數根據定義的條件來篩選表格數據，一旦獲取到篩選后的數據，我們就可以用它來創建柱狀圖。

柱狀圖繪制來自預定義單元格范圍的數據，該范圍綁定到圖表系列。因此，如果過濾后的行數發生變化，例如用戶更改了“Show”列中的值，就會導致FILTER公式重新計算，從而篩選后的數據單元格范圍也會發生變化。但是，圖表系列始終引用相同的單元格范圍，因此任何新的篩選數據行如果不在系列單元格范圍內，則不會在圖表上繪制。但是，我們希望所有經過篩選的數據都能在圖表上顯示，這就需要將簡單靜態圖表轉換為交互式圖表，以便刷新其繪圖區域來繪制所有篩選數據的值。

因此，請繼續了解實現上述定義方案的詳細步驟。

步驟 1：加載數據文件

我們將首先在 GcExcel 工作簿中加載源數據，該數據將用于繪制圖表。

源數據文件下載（源文件文章系統的附件中）

將 Excel 文件加載到Workbook 中：

//創建一個工作簿，并打開數據文件
Workbook workbook = new GrapeCity.Documents.Excel.Workbook();
workbook.Open("xlsx\\SalesReport_sourcedata.xlsx");

加載效果：

步驟 2：使用動態數組公式提取圖表數據

現在將開始提取相關數據，以便在圖表中進行繪制。我們將根據產品的銷售額繪制圖表。因此，圖表數據應包含來自產品列和金額列的數值。從這兩列中提取特定數值的標準是基于展示列。我們將從展示列的值為1的每一行中提取產品和金額數值，并在圖表上繪制這些篩選后的數據。

使用FILTER函數從產品列中過濾數據，然后使用SORTBY函數按照金額列的值進行降序排序。因此，我們將通過連接FILTER和SORTBY這兩個動態數組函數來創建一個動態數組公式。

將使用VLOOKUP函數提取金額列中的數值，并將FILTER函數的溢出范圍作為參數傳遞，以便獲取與每個篩選產品對應的金額值。

以下是相應的代碼片段：

IWorksheet worksheet = workbook.getWorksheets().get(0);
worksheet.getRange("G3").setValue("Product");
worksheet.getRange("H3").setValue("Sales");
worksheet.getRange("G3").getFont().setBold(true);
worksheet.getRange("H3").getFont().setBold(true);
//使用動態數組公式來獲取產品列的值
worksheet.getRange("G4").setFormula2("=SORTBY(FILTER(B4:B13,E4:E13=1),FILTER(D4:D13,E4:E13=1),-1)");
//引用溢出區域來獲取金額列的值
worksheet.getRange("H4").setFormula2("=VLOOKUP(G4#,B4:D13,3,FALSE)");

以下是對工作表的快速瀏覽，其中包含 G 列和 H 列中經過篩選和排序的數據，并突出顯示了溢出范圍：

步驟 3：使用篩選后的數據添加簡單圖表

上述步驟已生成要在圖表中繪制的數據。在這里，我們通過向工作表添加兩個命名范圍來添加一個簡單的圖表來展示銷售額，其中一個引用篩選出的數據中的“產品”列，另一個引用“銷售”列。然后，這些命名區域將用于創建圖表系列。代碼如下所示：

//添加數據引用
wb.getNames().add("Product", "=Sheet1!$G$4:$G$8");
wb.getNames().add("Sales", "=Sheet1!$H$4:$H$8");//添加圖表
IShape chartShape = sheet.getShapes().addChart(ChartType.ColumnClustered, 600, 170, 500, 300);
IChart chart = chartShape.getChart();
//添加系列
ISeries series = chart.getSeriesCollection().newSeries();
series.setFormula("=SERIES(\"Sales\", Product, Sales, 1)");

生成的圖表如下所示:

步驟 4：使圖表具有交互性

讓我們首先了解使用此圖表具有交互性的需求。觀察下面的 GIF，會發現更改“Show”列中的值會重新計算動態數組公式并更新過濾后的數據單元格范圍。但是，僅當新篩選的數據位于單元格區域 G3：H8（即系列單元格區域）時，此更改才會在圖表中可見。如果過濾后的數據不在單元格范圍 G3：H8 中，則它不會顯示在圖表中，如本示例中過濾后的數據范圍擴展到 G3：H10，但圖表僅呈現來自 G3：H8 的數據：

此圖表應繪制所有過濾掉的數據。為此，我們必須使用溢出范圍參考更新序列單元格范圍，這將確保序列單元格范圍始終包含包含過濾數據的完整單元格范圍。下面是示例代碼片段，該代碼片段更新命名區域以使用溢出范圍引用，從而根據動態數組公式的結果使引用的單元格區域動態化。然后，這些動態命名區域用于創建圖表系列，使圖表具有交互性。

代碼如下所示：

//添加命名引用，用于圖表數據
wb.getNames().add("Product", "=Sheet1!$G$4#");
wb.getNames().add("Sales", "=Sheet1!$H$4#");//添加圖表
IShape chartShape = sheet.getShapes().addChart(ChartType.ColumnClustered, 600, 170, 500, 300);
IChart chart = chartShape.getChart();//添加系列
ISeries series = chart.getSeriesCollection().newSeries();
series.setFormula("=SERIES(\"Sales\", Product, Sales, 1)");

實現效果：