Java操作Excel公式出现#Value错误（数据类型不符）求解决方案

核心错误原因 #

1. 正则表达式匹配失效
   你的Java代码使用的正则(?<=[A-Za-z])\d+仅能匹配A1格式单元格引用（如A123）中字母后的数字，但原公式采用的是R1C1相对引用格式（R[-69]C[-14]），数字被包裹在[-...]括号内，正则无法命中目标内容，导致公式里的行偏移量完全没有被更新。

2. 无效相对引用触发错误
   克隆工作表后，原公式的相对行偏移R[-69]在新工作表的单元格位置下，可能指向无效行（比如当前行号小于69时，偏移后行号为负数）。OFFSET函数返回错误值后，TEXT函数无法处理错误类型的数据，最终抛出#VALUE!。

解决办法 #

1. 修正正则表达式，适配R1C1格式引用 #

根据你的业务需求选择对应的替换逻辑：

• 如果要将相对行偏移改为绝对行号（比如引用第i+1行）：

  String newFormula = formula.replaceAll("R\\[-?\\d+\\]", "R[" + (i+1) + "]");
  

  该正则会匹配所有R[±数字]格式的行引用，替换为目标绝对行号。

• 如果要调整相对偏移量（比如原偏移是-69，改为新的偏移值）：
  先计算出新的偏移量（比如newOffset = -69 + i），再用以下正则替换：

  int newOffset = -69 + i; // 根据业务逻辑调整计算方式
  String newFormula = formula.replaceAll("(?<=R\\[)-?\\d+", String.valueOf(newOffset));
  

2. 优化公式，增强容错性 #

在Excel公式中增加错误处理，避免无效引用导致的#VALUE!：

IFERROR(IF(OFFSET('Data sheet'!R[-69]C[-14],1,0)="","",TEXT(OFFSET('Data sheet'!R[-69]C[-14],1,0),"#,##0")), "")

或者改用非易失性的INDEX函数替代OFFSET，提升稳定性：

IFERROR(IF(INDEX('Data sheet'!C[-14],ROW()-69+1)="","",TEXT(INDEX('Data sheet'!C[-14],ROW()-69+1),"#,##0")), "")

3. 验证替换结果 #

在Java代码中保留System.out.println(newFormula);，确认替换后的公式行引用是否符合预期，避免正则匹配逻辑再次出错。

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