Java实现Excel公式从原单元格到目标单元格的调整算法

我完全懂你想要做的事——就是实现Excel里复制公式时自动调整相对引用的功能，用Java来做对吧？先把你给出的核心规则梳理清楚，确保我们对齐需求：

核心调整规则 #

• 相对引用（如A2）：根据原单元格和目标单元格的行差、列差，同步偏移行号和列号
• 混合/绝对引用（如$A2、A$2、$A$2）：仅不带$标记的部分会被偏移，带$的部分保持不变
• 跨工作表引用（如SheetA2!A2）：工作表名称完全保留，仅调整感叹号后的单元格引用地址
• 批量替换：公式中所有符合规则的单元格引用都要逐一调整（比如IF函数里的多个引用）

方案一：手动实现核心逻辑 #

如果你不想依赖第三方库，可以自己实现单元格地址的解析、偏移计算和公式替换。下面是完整的可运行代码：

import java.util.regex.Matcher;
import java.util.regex.Pattern;

public class ExcelFormulaAdjuster {

    // 存储解析后的单元格地址信息
    static class CellAddress {
        String sheetName;
        String column;
        int row;
        boolean isAbsoluteColumn;
        boolean isAbsoluteRow;

        public CellAddress(String sheetName, String column, int row, boolean isAbsoluteColumn, boolean isAbsoluteRow) {
            this.sheetName = sheetName;
            this.column = column;
            this.row = row;
            this.isAbsoluteColumn = isAbsoluteColumn;
            this.isAbsoluteRow = isAbsoluteRow;
        }

        // 生成调整后的地址字符串
        public String getAdjustedAddress(int columnOffset, int rowOffset) {
            StringBuilder sb = new StringBuilder();
            // 添加工作表名（如果有）
            if (sheetName != null) {
                sb.append(sheetName).append("!");
            }
            // 处理列部分
            if (isAbsoluteColumn) {
                sb.append("$").append(column);
            } else {
                int newColNum = columnNameToNumber(column) + columnOffset;
                sb.append(columnNumberToName(newColNum));
            }
            // 处理行部分
            if (isAbsoluteRow) {
                sb.append("$").append(row);
            } else {
                sb.append(row + rowOffset);
            }
            return sb.toString();
        }

        // 列名转数字（A=1, B=2...Z=26, AA=27）
        private int columnNameToNumber(String column) {
            int result = 0;
            for (char c : column.toUpperCase().toCharArray()) {
                result = result * 26 + (c - 'A' + 1);
            }
            return result;
        }

        // 数字转列名
        private String columnNumberToName(int columnNum) {
            StringBuilder sb = new StringBuilder();
            while (columnNum > 0) {
                int remainder = (columnNum - 1) % 26;
                sb.insert(0, (char) ('A' + remainder));
                columnNum = (columnNum - 1) / 26;
            }
            return sb.toString();
        }
    }

    // 正则匹配Excel单元格引用（支持带工作表、绝对引用）
    private static final String CELL_REF_PATTERN = "([a-zA-Z0-9_]+!)?\\$?([a-zA-Z]+)\\$?(\\d+)";

    // 解析单元格地址（如C2、$D$10）
    private static CellAddress parseCell(String cellAddr) {
        Matcher matcher = Pattern.compile("\\$?([a-zA-Z]+)\\$?(\\d+)").matcher(cellAddr);
        if (!matcher.find()) {
            throw new IllegalArgumentException("无效的单元格地址：" + cellAddr);
        }
        String column = matcher.group(1);
        int row = Integer.parseInt(matcher.group(2));
        boolean isAbsoluteCol = cellAddr.startsWith("$");
        boolean isAbsoluteRow = cellAddr.contains("$" + row);
        return new CellAddress(null, column, row, isAbsoluteCol, isAbsoluteRow);
    }

    // 计算目标单元格相对于原单元格的偏移量
    private static int[] calculateOffsets(CellAddress original, CellAddress dest) {
        int originalColNum = original.columnNameToNumber(original.column);
        int destColNum = dest.columnNameToNumber(dest.column);
        int colOffset = destColNum - originalColNum;
        int rowOffset = dest.row - original.row;
        return new int[]{colOffset, rowOffset};
    }

    // 主方法：调整公式
    public static String adjustFormula(String originalFormula, String originalCellAddr, String destCellAddr) {
        CellAddress originalCell = parseCell(originalCellAddr);
        CellAddress destCell = parseCell(destCellAddr);
        int[] offsets = calculateOffsets(originalCell, destCell);
        int colOffset = offsets[0];
        int rowOffset = offsets[1];

        Pattern pattern = Pattern.compile(CELL_REF_PATTERN);
        Matcher matcher = pattern.matcher(originalFormula);
        StringBuffer result = new StringBuffer();

        while (matcher.find()) {
            String sheetName = matcher.group(1);
            String column = matcher.group(2);
            int row = Integer.parseInt(matcher.group(3));
            // 判断是否为绝对引用
            boolean isAbsoluteCol = matcher.group(0).contains("$" + column);
            boolean isAbsoluteRow = matcher.group(0).contains("$" + row);
            // 去掉工作表名后的感叹号
            if (sheetName != null) {
                sheetName = sheetName.substring(0, sheetName.length() - 1);
            }
            CellAddress cellRef = new CellAddress(sheetName, column, row, isAbsoluteCol, isAbsoluteRow);
            matcher.appendReplacement(result, cellRef.getAdjustedAddress(colOffset, rowOffset));
        }
        matcher.appendTail(result);
        return result.toString();
    }

    // 测试你的示例场景
    public static void main(String[] args) {
        // 示例1
        System.out.println(adjustFormula("=(A2+B2)", "C2", "C3")); // 输出：=(A3+B3)
        // 示例2
        System.out.println(adjustFormula("=(A2+B2)", "C2", "D2")); // 输出：=(B2+C2)
        // 示例3
        System.out.println(adjustFormula("=(A2+$B$2)", "C2", "D10")); // 输出：=(B10+$B$2)
        // 示例4
        System.out.println(adjustFormula("=(SheetA2!A2+B2)", "C2", "C3")); // 输出：=(SheetA2!A3+B3)
        // 示例5
        System.out.println(adjustFormula("=IF(A2=A3,A4,A5)", "A6", "C6")); // 输出：=IF(C2=C3,C4,C5)
    }
}

方案二：借助Apache POI简化开发 #

如果你的项目已经用到Apache POI（处理Excel的常用库），直接用它内置的工具类会更可靠，能自动处理很多边缘情况（比如带特殊字符的工作表名、复杂的列名转换等）。

首先需要引入POI的依赖（Maven为例）：

<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi</artifactId>
    <version>5.2.5</version>
</dependency>
<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi-ooxml</artifactId>
    <version>5.2.5</version>
</dependency>

然后是简化的实现代码：

import org.apache.poi.ss.util.CellReference;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

public class ExcelFormulaAdjusterPOI {

    private static final String CELL_REF_PATTERN = "([a-zA-Z0-9_]+!)?\\$?([a-zA-Z]+)\\$?(\\d+)";

    public static String adjustFormula(String originalFormula, String originalCellAddr, String destCellAddr) {
        CellReference originalRef = new CellReference(originalCellAddr);
        CellReference destRef = new CellReference(destCellAddr);
        // 计算偏移量（POI的行是0-based，注意转换）
        int colOffset = destRef.getCol() - originalRef.getCol();
        int rowOffset = destRef.getRow() - originalRef.getRow();

        Pattern pattern = Pattern.compile(CELL_REF_PATTERN);
        Matcher matcher = pattern.matcher(originalFormula);
        StringBuffer result = new StringBuffer();

        while (matcher.find()) {
            String sheetName = matcher.group(1);
            String column = matcher.group(2);
            int row = Integer.parseInt(matcher.group(3)) - 1; // 转成0-based行号
            boolean isAbsoluteCol = matcher.group(0).contains("$" + column);
            boolean isAbsoluteRow = matcher.group(0).contains("$" + (row + 1)); // 转回1-based判断

            // 创建原始引用
            CellReference ref = new CellReference(
                    sheetName != null ? sheetName.substring(0, sheetName.length()-1) : null,
                    CellReference.convertColStringToIndex(column),
                    row,
                    isAbsoluteCol,
                    isAbsoluteRow
            );
            // 创建调整后的引用
            CellReference adjustedRef = new CellReference(
                    ref.getSheetName(),
                    isAbsoluteCol ? ref.getCol() : ref.getCol() + colOffset,
                    isAbsoluteRow ? ref.getRow() : ref.getRow() + rowOffset,
                    isAbsoluteCol,
                    isAbsoluteRow
            );
            matcher.appendReplacement(result, adjustedRef.formatAsString());
        }
        matcher.appendTail(result);
        return result.toString();
    }

    // 同样可以用之前的测试用例验证
    public static void main(String[] args) {
        System.out.println(adjustFormula("=(A2+$B$2)", "C2", "D10")); // 输出：=(B10+$B$2)
    }
}

注意事项 #

1. 手动实现的正则表达式仅匹配常规的单元格引用，如果你的公式包含命名区域、数组公式、带特殊字符的工作表名（比如带空格、!的表名），需要额外修改正则和解析逻辑
2. 优先推荐使用Apache POI方案，它经过了大量测试，能处理更多Excel的特殊语法场景

备注：内容来源于stack exchange，提问作者Nguyen Manh Cuong