Apache POI CellReference处理多工作表引用公式报错，求解决方法

首先得明确：CellReference类的设计目标是处理单个单元格或单元格区域的引用，而非包含多个独立引用的公式片段。你遇到的Bad sheet name quote escaping错误，本质是因为你把一个包含多个工作表引用的复合字符串丢给了只支持单个引用的CellReference，它的parseSheetName方法根本没考虑这种场景，自然会解析失败。

下面给你两个可行的解决思路：

思路1：拆分单个引用后分别解析 #

既然CellReference能正确处理单个工作表引用，那我们可以先把复合公式拆分成多个独立的引用片段，再逐一处理：

示例代码 #

String originalFormula = "('1 - Sheet'!$P:$P,'1 - Sheet'!$E:$E,'Another Calc'!$A10)";

// 1. 去掉首尾的括号，按逗号加空格分割成单个引用
String[] individualRefs = originalFormula.substring(1, originalFormula.length() - 1).split(", ");

for (String refStr : individualRefs) {
    try {
        CellReference cellRef = new CellReference(refStr);
        // 提取你需要的信息
        String sheetName = cellRef.getSheetName();
        String cellAddress = cellRef.formatAsString();
        
        System.out.println("工作表名：" + sheetName);
        System.out.println("单元格/区域：" + cellAddress);
    } catch (IllegalArgumentException e) {
        // 这里拆分后基本不会报错，除非原公式格式本身有问题
        System.err.println("解析单个引用出错：" + e.getMessage());
    }
}

为什么可行？ #

拆分后每个子串都是标准的单工作表引用格式（比如'1 - Sheet'!$P:$P），完全符合CellReference的预期输入，parseSheetName能正确识别带单引号的工作表名，不会再抛出转义错误。

思路2：用POI的公式解析器处理完整公式 #

如果你的公式更复杂（比如嵌套函数、动态引用等），拆分字符串的方法可能不够灵活，这时可以用POI内置的FormulaParser来解析整个公式，它能处理各种复杂的Excel公式结构：

示例代码 #

import org.apache.poi.ss.formula.FormulaParser;
import org.apache.poi.ss.formula.ptg.AreaPtg;
import org.apache.poi.ss.formula.ptg.Ptg;
import org.apache.poi.ss.formula.ptg.RefPtgBase;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

public class FormulaParseExample {
    public static void main(String[] args) throws Exception {
        String originalFormula = "('1 - Sheet'!$P:$P,'1 - Sheet'!$E:$E,'Another Calc'!$A10)";
        Workbook wb = new XSSFWorkbook();
        
        // 初始化公式解析器
        FormulaParser parser = new FormulaParser(
            wb,
            wb.getCreationHelper().createFormulaEvaluator(),
            null,
            FormulaParser.DEFAULT_PARSER_CONFIG
        );
        
        // 去掉首尾括号，解析内部的多引用内容
        Ptg[] ptgs = parser.parse(originalFormula.substring(1, originalFormula.length() - 1));
        
        for (Ptg ptg : ptgs) {
            if (ptg instanceof RefPtgBase) {
                // 处理单个单元格引用（比如'A10'）
                RefPtgBase refPtg = (RefPtgBase) ptg;
                String sheetName = refPtg.getSheetName();
                int rowNum = refPtg.getRow() + 1; // POI行索引从0开始，转成Excel的1-based
                int colNum = refPtg.getColumn() + 1;
                
                System.out.printf("单个单元格引用：工作表[%s]，行[%d]，列[%d]%n", sheetName, rowNum, colNum);
            } else if (ptg instanceof AreaPtg) {
                // 处理单元格区域引用（比如'$P:$P'）
                AreaPtg areaPtg = (AreaPtg) ptg;
                String sheetName = areaPtg.getSheetName();
                String startCol = CellReference.convertNumToColString(areaPtg.getFirstColumn());
                int startRow = areaPtg.getFirstRow() + 1;
                String endCol = CellReference.convertNumToColString(areaPtg.getLastColumn());
                int endRow = areaPtg.getLastRow() + 1;
                
                System.out.printf("区域引用：工作表[%s]，范围[%s%d:%s%d]%n", sheetName, startCol, startRow, endCol, endRow);
            }
        }
        wb.close();
    }
}

为什么可行？ #

FormulaParser是POI专门用来解析Excel公式的工具，它能识别公式中的各种操作数（Ptg），包括单个单元格引用、区域引用、函数调用等，完全支持多引用的复合场景，不会受到单引号转义的限制。

总结 #

• 如果你只是处理简单的多引用拼接，拆分单个引用后用CellReference是最直接的方案；
• 如果公式结构复杂，一定要用FormulaParser这类专业的公式解析工具，不要强行用CellReference处理超出它能力范围的场景。

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