如何使用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)");

实现效果：