如何在Apache POI中实现Excel多单元格值组合的MATCH函数求值

使用Apache POI处理带公式的Excel表格时，多数公式可正常求值，但一个组合多单元格值的MATCH函数无法在POI中运行。该公式在Excel内正常工作，仅包含单个单元格值的MATCH函数也能在POI中正常求值。

公式对比：

• 可正常求值的公式：=MATCH(VALUE(A10),('referral sheet 2'!A1:A20),0)
• 无法求值的公式：=MATCH(VALUE(A10&A11),('referral sheet 2'!A1:A20&'referral sheet 2'!B1:B20),0)

使用的Maven依赖：

<groupId>commons-io</groupId>
<artifactId>commons-io</artifactId>
<version>2.8.0</version>
</dependency>
<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi</artifactId>
    <version>4.1.2</version>
</dependency>
<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi-ooxml</artifactId>
    <version>4.1.2</version>

方案1：升级Apache POI版本 #

POI 4.1.2版本对数组类公式操作（如多单元格用&组合成数组）的支持有限，后续5.x及以上版本优化了这部分逻辑。建议升级到最新稳定版（如5.2.5）后重新测试公式求值。

方案2：改用辅助列简化公式 #

在referral sheet 2中新增辅助列（比如C列），在C1单元格设置公式=A1&B1并下拉填充到C20，然后将目标公式修改为：
=MATCH(VALUE(A10&A11),('referral sheet 2'!C1:C20),0)
这种单列匹配的形式，POI可正常处理。

方案3：自定义代码实现求值逻辑 #

如果无法修改Excel或升级POI，可手动实现该公式的求值逻辑：

1. 获取A10和A11的值，拼接后转换为数值
2. 遍历referral sheet 2的A、B列对应单元格，逐个拼接并转成数值
3. 对比找到匹配项，返回Excel格式的索引（从1开始计数）

示例Java代码：

// 获取目标匹配值
Sheet mainSheet = workbook.getSheetAt(0);
String a10Val = mainSheet.getRow(9).getCell(0).getStringCellValue();
String a11Val = mainSheet.getRow(10).getCell(0).getStringCellValue();
double target = Double.parseDouble(a10Val + a11Val);

// 遍历引用表查找匹配项
Sheet refSheet = workbook.getSheet("referral sheet 2");
int matchIndex = -1;
for (int i = 0; i < 20; i++) {
    Row row = refSheet.getRow(i);
    if (row == null) continue;
    Cell aCell = row.getCell(0);
    Cell bCell = row.getCell(1);
    if (aCell == null || bCell == null) continue;
    
    String refVal = aCell.getStringCellValue() + bCell.getStringCellValue();
    double refNum = Double.parseDouble(refVal);
    if (refNum == target) {
        matchIndex = i + 1; // Excel行号从1开始计数
        break;
    }
}
// matchIndex即为原公式的求值结果

内容的提问来源于stack exchange，提问作者Deepu