如何使用Java在Excel中添加动态数组公式

动态数组公式是 Excel 引入的一项重要功能，允许用户从单个单元格中的公式返回多个结果值，并将这些值自动填充到与公式单元格相邻的单元格中。在传统 Excel 中，用户必须使用 Ctrl + Shift + Enter 来将公式识别为数组公式，否则公式结果将仅返回单个值。因此，动态数组为用户提供了更加便利的使用体验。现在我们将这样的返回多个值的公式称为动态数组公式。今天就为大家介绍如何使用葡萄城公司的Java API 组件GrapeCity Documents for Excel（以下简称GcExcel）实现在Excel中添加动态数组。

发布于 2023/12/06 20:39

GcExcel

前言

动态数组公式是 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)");

实现效果：

总结

以上就是如何使用Java在Excel中添加动态数组公式的全过程，如果您想要了解更多的资料，可以点击下方链接查看：

GcExcel | 下载试用

GrapeCity Documents for Excel （简称：GcExcel）是一款基于 Java 平台的服务端高性能表格组件，可与纯前端表格控件 SpreadJS 前后端兼容，无需依赖 Office、POI 或第三方应用软件，在前端展示电子表格数据，在服务端批量创建、加载、编辑、打印、导入/导出 Excel 文档，为您开发的应用程序提供在线文档的前后端数据同步、在线填报与服务端批量导出与打印，以及类 Excel 报表模板设计与服务端高性能处理等一整套类 Excel 全栈解决方案。

热门博客分类
ComponentOne Enterprise SpreadJS ActiveReports 活字格 Wyn 商业智能 WijmoJS GcExcel 行业动态 ActiveReportsJS Spread .NET
相关产品

GcExcel

高效创建、编辑、解析、导出多种流行文档

免费试用 产品详情
推荐相关案例
云南柠能 - SpreadJS与GcExcel协同，实现BI-N平台一站式解决方案

云南柠能企业管理咨询有限公司是一家致力于打造业财融合、综合绩效管理平台的数字化产品研发及服务提供商。其中BI-N是一款一站式PaaS平台，主要包含数据平台、数据应用、数据分析、数据云产品四大部分，使用SpreadJS表格控件+Hadoop生态构建，提供数据分析、绩效管理等功能。SpreadJS在平台中能够还原Excel功能，支持多维数据分析和大数据场景下的表格渲染处理，配合GcExcel实现高性能公式计算，提高平台的访问效率，同时丰富的API接口也能够帮助用户灵活扩展平台的业务功能。

苏交科 - SpreadJS解锁TIC行业报表开发难题

通过使用SpreadJS+GcExcel助力TIC系统开发，加快了报表模板开发速度，提升了报表的计算效率，提高了系统的稳定性和用户体验。

上海佳软 - 优化多人协作，SpreadJS+GcExcel构建无缝协同系统

上海佳软推出了雷鸟365在线表格文档系统，这是一款有着Excel强大功能、允许多人实时协作编辑的产品。雷鸟365的出现让Excel联网成为可能，只需要导入本地文件，即可实现多人共享编辑文档，还可以设置访问修改权限。

首厚智能 - GcExcel助力LIMS系统，共塑卓越实验室之路

首厚智能以“互联网+智慧实验室解决方案”为核心，业务范围覆盖实验室智能化、数字化发展等多态融合的全新实验室服务，为科研企业提供应用系统开发、定制化系统开发、异构系统集成、信息系统运行维护、系统培训、软件及计算机硬件产品销售等服务。

某基金公司 - GcExcel助力基金数据监控，提升操作效率

某大型基金公司为提升投资管理水平，引入SpreadJS实现在线Excel文件管理、监管报送、数据解析和数据订阅等功能，提升工作效率和协作效果。SpreadJS带来类Excel的交互体验、数据处理能力和数据共享协作，提供了高效的投资管理解决方案。

查看更多精彩案例
关注微信
葡萄城社区二维码

关注“葡萄城社区”

活字格低代码二维码

关注“活字格低代码”

想了解更多信息，请联系我们, 随时掌握技术资源和产品动态

葡萄城热门产品

活字格
打通现有软件，快速定制面向未来的个性化应用
免费试用 产品详情
SpreadJS
可嵌入您系统的在线Excel的纯前端表格控件
免费试用 产品详情
Wyn商业智能
可与企业自有系统深度集成，增强数据分析的 BI 工具
免费试用 产品详情