Ohhnews

分类导航

$ cd ..
DZone Java原文

动态数组、溢出与LET：Excel新特性解析及其对Java应用的影响

#excel#动态数组#java#公式引擎#电子表格

在上一篇 Working with Spreadsheets in Java: A Practical Overview 中，我们介绍了 Java 应用需要与电子表格交互的常见场景，以及可用于此项任务的工具类别。文中提到的一个考量因素是对现代 Excel 公式的支持——这个话题值得用更多篇幅来展开，而不是一个要点就能带过。

Java 应用 与 Excel 交互的频率往往高于多数团队的预期：接收财务部门上传的文件、加载工作簿中编写好的计算逻辑、把报表导出并回传给业务用户。这些用户今天生成的文件，已经和五年前大不相同。Excel 365 和 Excel 2021 引入了一套新的公式模型，用这些版本创建的工作簿普遍会用到这一模型。根据你所使用的程序库，这些公式可能被正确计算，也可能因过期的缓存值而静默失败，或在重新计算时抛出异常。

本文将深入讨论这一主题：什么是动态数组和溢出（spill）行为、新函数集有什么特点、为什么支持它们在技术上有难度，以及 Java 开发者在评估某个库能否正确处理这些特性时应该关注什么。

为什么真实工作簿中会出现这些公式

动态数组能迅速推广，是因为它们消除了旧版 Excel 工作簿赖以工作的许多辅助列、复制公式，以及 Ctrl+Shift+Enter 数组公式。工作簿因此变得更简短，也更易于审计和维护。随着组织陆续迁往 Microsoft 365，在与 Java 应用交换的电子表格中，这些较新的公式会越来越常见——即使应用本身并没有任何变化。

变化在哪里：一个公式，多个值

在动态数组出现之前，需要返回多个值的公式通常要预先划定一个数组区域，并使用传统的数组公式语法。动态数组改变了这一点：它允许单个公式返回大小可变的数组，并自动“溢出”到相邻单元格。例如：

=UNIQUE(A1:A6)

[LOADING...]

在某个单元格中输入该公式，它会返回 A1:A6 中全部不重复的值。结果会填满与不重复值数量相同的单元格。如果源数据改变，不重复值的数量也随之变化，溢出区域便会自动扩大或缩小。

这不仅是一个新函数的问题，更意味着求值模型本身发生了改变。

溢出的运行机制

溢出公式会产生一片单元格区域，其中各单元格承担特定的角色：

  • 锚点单元格（anchor cell）：唯一包含公式的单元格，“拥有”公式结果。
  • 溢出单元格（spilled cells）：显示其余数值的相邻单元格。它们本身不包含公式，只是呈现锚点单元格结果的一部分。

可以使用 # 运算符从另一个公式引用整个溢出区域。# 引用并不是固定的单元格区域，如 A1:A6；它指向锚点单元格当前溢出到的任意范围。如果 A1 中包含 =UNIQUE(...)，结果溢入 A1:A6，那么 =COUNTA(A1#) 会统计整个溢出区域中的值。溢出区域扩大或缩小时，# 引用会自动随之调整。

[LOADING...]

#SPILL! 错误。如果公式需要溢入的区域被既有值、合并区域或 Excel 表格阻挡，公式将无法溢出，锚点单元格会显示 #SPILL! 而不是结果。清除障碍后，公式便能够完成计算。

使用 @ 的隐式交集。旧版 Excel 在很多场景下会把数组静默折叠为单个值；现代 Excel 则默认返回完整数组，除非公式使用了 @ 前缀。例如，在现代版 Excel 的单元格中输入 =A1:A10，会溢出 A1:A10 中的值；而 =@A1:A10 会应用隐式交集，返回与公式所在行对应的值。从旧版 Excel 迁移而来的文件通常会包含自动插入的 @ 前缀，以保留原有行为。

新的函数家族

与 Excel 动态数组模型相关联的现代函数，大致可以分成三大类。理解这种归类很有价值，因为这些类别的行为方式并不相同。

第一组：语言特性

这类功能其实并不属于传统意义上“函数”的范畴，它们为 Excel 的公式语言增加了表达式级别的结构。

  • LET 在公式内部把名称绑定到中间值。于是你可以编写 =LET(total, SUM(B2:B100), tax, total*0.1, total+tax)，而不必重复书写三次 SUM(B2:B100)。
  • LAMBDA 在工作簿内定义可复用的函数。与命名区域组合使用时，LAMBDA 实际上提供了不需要 VBA 的用户自定义函数。
  • ISOMITTED 在 LAMBDA 内部使用，用来检测某个可选参数是否被传入。

这些函数本身并不会产生溢出数组。LET 返回的是其表达式计算的结果，而这个结果本身有可能是数组。

第二组：动态数组函数

这组函数通常才是人们说“新的 Excel 函数”时所指的对象。它们以返回数组为设计目标，当结果包含多个值时，Excel 可以将结果溢出到相邻单元格。

  • UNIQUE 返回某个区域中的不重复值。
  • SORT 和 SORTBY 返回排序后的数组。
  • FILTER 返回满足条件的行。
  • SEQUENCE 生成一个数字序列。
  • RANDARRAY 生成一个随机数数组。

数组整形函数构成这一组的子集。它们接收数组作为输入，返回重新整形后的数组：CHOOSECOLS、CHOOSEROWS、DROP、EXPAND、HSTACK、VSTACK、TAKE、TOCOL、TOROW、WRAPCOLS、WRAPROWS。TEXTSPLIT 也归入此类——它把字符串拆分成数组。其他函数，如 BYROW、BYCOL、MAP、REDUCE 和 SCAN，也都建立在同样的动态数组模型之上。

第三组：同期引入的标量函数

动态数组首先是一种求值模型；现代函数则是一组利用该模型或与之共存的函数。第三组函数是作为更广泛的现代 Excel 函数集合的一部分引入的，但它们本身并不以生成数组为主要目的。

  • XLOOKUP 和 XMATCH 是 VLOOKUP 与 MATCH 的现代替代品。它们通常返回单个值，不过在传入查找值数组时也可以返回数组。
  • TEXTAFTER 和 TEXTBEFORE 返回子字符串。
  • VALUETOTEXT 和 ARRAYTOTEXT 把值转换为文本（ARRAYTOTEXT 接收数组作为输入，但返回单个字符串）。

因为这些函数与动态数组函数同期出现，人们常把它们归在一起讨论，但它们的求值模型其实更接近 VLOOKUP，而不是 UNIQUE。

为什么这对公式引擎来说很难

支持这些特性，并不只是往函数列表里添加几个新函数名。动态数组模型要求求值引擎本身做出重大改变。传统的“一次只处理一个单元格”的公式模型，并不足以实现动态数组。引擎必须能够表示结果形状可变的公式，并把该结果传播到多个单元格。

一个现代引擎还要处理四个额外问题：

数组形状的结果。公式的返回值可能是一个二维数组，其尺寸取决于输入数据。=UNIQUE(A1:A100) 返回的行数，取决于该区域中有多少个不重复值。引擎必须在求值时、而不是在解析时确定结果的形状。

溢出区域跟踪。引擎必须为公式要溢入的单元格预留空间，并阻止其他内容占用它们。当某个溢出目标被内容占用时，锚点单元格必须返回 #SPILL!，而不是覆盖障碍内容。当结果形状发生变化时，预留区域也必须同步更新。

下游引用。类似 A1# 的表达式引用的是整个溢出区域。当锚点公式的形状变化时，每个下游引用都必须按新的尺寸重新求值。这使得依赖关系图比“每格一值”的模型更加动态。

隐式交集兼容性。旧版 Excel 在许多上下文中会把数组静默折叠为单个值；现代 Excel 则返回完整数组。当旧版 Excel 创建的文件在现代 Excel 中打开时，系统会自动插入 @ 前缀来保留原行为。读取现代 .xlsx 文件的引擎必须支持 @ 运算符，否则导入的公式会产生不同的结果。

在以“一公式一值”模型设计的引擎中加入这些行为，本质上是一次大规模重写，而不是增量式地添加功能。这也是 Java 生态中相关支持水平参差不齐的部分原因。

Java 开发者应该检查什么

Java 电子表格库对这些能力的支持差异很大。有些引擎最初就是按传统的“一个单元格一个结果”的求值模式设计的，对现代 Excel 模型只实现了部分子集；另一些则扩展或重新设计了求值器，以支持动态数组。

与其只看功能清单，更可靠的做法是拿能代表你自身业务场景的工作簿，去验证类库的实际行为。如果你的应用需要计算现代 Excel 公式，在最终选定类库之前，以下检查值得逐一执行。

用一个包含溢出公式的文件测试。创建一个小的 .xlsx 文件，在某个单元格中放入 =UNIQUE(A1:A100) 或 =SORT(A1:A100)。用候选库加载该文件，并尝试重新计算锚点单元格。支持动态数组的库会返回数组；不支持的库通常会抛出异常，或只返回第一个值。

检查 # 溢出运算符。在同一个文件中另外添加一个单元格，写入 =COUNTA(A1#)，其中 A1 是锚点单元格。这可以验证库是否理解溢出区域引用——这是与计算锚点公式本身相互独立的能力。

测试 @ 运算符。在某个单元格中添加 =@A1:A10，检查库是否正确返回当前行对应的值，而不是整个数组。从旧版 Excel 迁移的文件经常会包含 @ 前缀；不能处理该前缀的库会产生与 Excel 不同的结果。

用 LET 和 LAMBDA 测试。编写像 =LET(total, SUM(A1:A100), total * 1.1) 这样的公式，分别验证求值结果与 .xlsx 往返读写。将 LET 和 LAMBDA 分开独立测试。解析、保留、求值这些函数是彼此独立的能力，因此一个能读取或写出公式文本的库，并不一定能正确地对它求值。

测试往返（round-trip）。保存工作簿后，再在 Excel 中重新打开，确认公式仍能产生正确结果。有些引擎在保存时会剥离现代结构。

检查失败时的行为。当库遇到一个它尚未实现的函数时，它是抛出异常、返回错误值，还是静默回退到文件中保存的缓存值？静默回退是最危险的行为，因为它会在开发阶段掩盖问题，直到生产环境中数据发生变化时才真正暴露。

结论

Excel 公式语言在过去几年里的变化，比此前二十年的总和还要多。动态数组、溢出行为和新函数集并非实验性特性——它们是 Excel 365 和 Excel 2021 的标准能力，并且已经出现在 Java 应用日常需要处理的工作簿中。

对 Java 开发者来说，实际的启示是：“支持 Excel 公式”已不再是一个库“有”或“没有”的单一属性。它涉及多种不同的能力，不同库在这每一项能力上的表现差异很大。

正如上一篇文章所述，Java 电子表格生态涵盖 Apache POI 这样的开源库、商用无头（headless）公式引擎，以及 Keikai 这类嵌入式电子表格组件。无论哪种类别适合你的场景，上述检查都不失为一种理性的验证方式，可以借助真实用户产生的工作簿，判断候选库能否正确处理现代 Excel 行为。

本文所表达的观点仅代表 DZone 撰稿人个人观点。