首页 > 编程语言 >如何使用Java在Excel中添加动态数组公式?

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

时间:2024-01-16 09:12:59浏览次数:34  
标签:getRange Java 公式 单元格 Excel 图表 数组 sheet

本文由葡萄城技术团队发布。转载请注明出处:葡萄城官网,葡萄城为开发者提供专业的开发工具、解决方案和服务,赋能开发者。

前言

动态数组公式是 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 Java 在线Demo | 动态数组的用例

GcExcel Java 在线Demo | 动态数组

动态数组 - GcExcel 中文文档Java版 | 服务端高性能表格组件 - 葡萄城

扩展链接:

Redis从入门到实践

一节课带你搞懂数据库事务!

Chrome开发者工具使用教程

从表单驱动到模型驱动,解读低代码开发平台的发展趋势

低代码开发平台是什么?

基于分支的版本管理,帮助低代码从项目交付走向定制化产品开发

标签:getRange,Java,公式,单元格,Excel,图表,数组,sheet
From: https://www.cnblogs.com/powertoolsteam/p/17861259.html

相关文章

  • Java控制流
    Java流程控制Scanner对象基本语法:Scannerscn=newScanner(System.in); //Scanner类来获取用户的输入​ 通过Scanner类的next()与nextLine()方法获取输入的字符串,在读取前我们一般需要使用hasNext()与hasNextLine()判断是否还有输入的数据。1、next()一定要读取到有效......
  • Java方法
    Java方法一、方法定义与调用1、定义方法语法:[修饰符列表]返回值类型方法名(形式参数列表){ 方法体;}注意:[]符号叫做中括号,以上中括号[]里面的内容表示不是必须的,是可选的方法体由Java语句构成方法定义之后需要去调用,不调用是不会执行的Java都是值传递2、方法调......
  • Java就业学习 Day 1
    Java开发能力:系统学习Java开发的第一天,终于知道Java和JavaSE的区别是什么了。。过完了Java初阶的课程,从Java历史、数据类型到二位数组。Java中阶课程面向对象这一节还没看完,有几点影响还挺深的,大一刚学Java的时候确实没怎么明白。①方法的重载:之前不知道方法重载有什么用,现在......
  • java基础
    Java基础部分基础部分的顺序:基本语法,类相关的语法,内部类的语法,继承相关的语法,异常的语法,线程的语法,集合的语法,io的语法,虚拟机方面的语法。1、一个".java"源文件中是否可以包括多个类(不是内部类)?有什么限制?可以有多个类,但只能有一个public的类,并且public的类名必须......
  • JAVA学习笔记 - Day3
    IDEA优化Bilibili:https://www.bilibili.com/video/BV1664y1U73u/?spm_id_from=333.337.search-card.all.click&vd_source=1939d858b6429ddf9c340541a096299d大小写联想,不区分大小写。勾选掉/显示注释(时间可调)自动导包,选择"always"选择JDK版本及编译输出配......
  • Java运用构造方法,计算并输出两点间的距离
    importjava.util.Scanner;publicclassjavaTest{publicstaticvoidmain(String[]args){//TODOAuto-generatedmethodstubdoublex1,y1,x2,y2;Scannersc=newScanner(System.in);while(sc.hasNext()){x1=sc.nextDo......
  • Java中::作用及应用场景
    Java中的::Java中双冒号(::)是一种全新的操作符,也称为方法引用,它允许开发人员通过名称来引用现有方法,可以视为Lambda表达式的一种缩写应用格式:类名::方法名四种方法引用类名::静态方法名对象实例名::实例方法名类名::实例方法名构造方法引用前提条件Lambda体中只有......
  • 14_Java基础-运算符3:逻辑运算符+位
    逻辑运算符&&(与)逻辑与运算,两个变量都为真,结果为true||(或)逻辑或运算,两个变量有一个为真,结果为true!(非)如果是真,则变为假,如果是假则变为真短路运算第一个数为错,后面不执行位运算符&(和)|(或)^(异或)~(取反)位运算,不要轻易用,容易出错,效率极高<<:左移:x2》》:右移:/2......
  • java中 Happens-Before 原则
    前言并发问题有三个根本原因:cpu缓存导致可见性问题线程切换导致原子性问题:线程切换是发生于任何一条cpu指令级别的,而不是高级语言中的语句,例如i++是三个cpu指令编译器优化导致有序性问题CPU缓存导致可见性问题与Java内存模型(JMM)的问题实际上是两个相互关联的概念。CPU......
  • SparkStreaming in Java
    参考地址:SparkStreamingProgrammingGuide1.新建Maven项目,POM引入依赖<dependency><groupId>org.apache.spark</groupId><artifactId>spark-streaming_2.13</artifactId><version>3.5.0</ve......