This is an automated email from the ASF dual-hosted git repository.

pjfanning pushed a commit to branch trunk
in repository https://gitbox.apache.org/repos/asf/poi.git


The following commit(s) were added to refs/heads/trunk by this push:
     new 772515ef1a CellCopyPolicy: opt-in adjustment of relative formula 
references (#1418)
772515ef1a is described below

commit 772515ef1afb2a119572f70b119a61741f62fe96
Author: PJ Fanning <[email protected]>
AuthorDate: Fri Oct 16 20:26:08 2026 +0100

    CellCopyPolicy: opt-in adjustment of relative formula references (#1418)
    
    Adds CellCopyPolicy.adjustFormulaReferences, off by default. When set,
    CellUtil.copyCell moves the relative references of a copied formula by
    the row and column offset between the source and destination cells, as
    Excel does on copy and paste. References past the destination's
    SpreadsheetVersion limits become #REF!. Formulas that cannot be parsed
    in the destination workbook are copied unchanged.
    
    copyRowFrom already shifts the whole row, so it turns the setting off
    for its per-cell copies to avoid shifting twice.
    
    Fixes #1416
    
    Co-authored-by: Claude Opus 5.5 <[email protected]>
---
 .../org/apache/poi/xssf/usermodel/XSSFRow.java     |  7 ++-
 .../org/apache/poi/xssf/usermodel/TestXSSFRow.java | 14 ++++++
 .../org/apache/poi/hssf/usermodel/HSSFRow.java     |  7 ++-
 .../apache/poi/ss/usermodel/CellCopyPolicy.java    | 48 ++++++++++++++++++
 .../main/java/org/apache/poi/ss/util/CellUtil.java | 57 +++++++++++++++++++++-
 .../org/apache/poi/hssf/usermodel/TestHSSFRow.java | 14 ++++++
 .../apache/poi/ss/util/BaseTestCellUtilCopy.java   | 55 +++++++++++++++++++++
 7 files changed, 197 insertions(+), 5 deletions(-)

diff --git a/poi-ooxml/src/main/java/org/apache/poi/xssf/usermodel/XSSFRow.java 
b/poi-ooxml/src/main/java/org/apache/poi/xssf/usermodel/XSSFRow.java
index 93826bdc5b..19830a2196 100644
--- a/poi-ooxml/src/main/java/org/apache/poi/xssf/usermodel/XSSFRow.java
+++ b/poi-ooxml/src/main/java/org/apache/poi/xssf/usermodel/XSSFRow.java
@@ -705,10 +705,13 @@ public class XSSFRow implements Row, Comparable<XSSFRow> {
      */
     @Beta
     public void copyRowFrom(Row srcRow, CellCopyPolicy policy, CellCopyContext 
context) {
+        // formula references are shifted for the whole row below, so don't 
also adjust them per cell
+        final CellCopyPolicy cellPolicy = policy.isAdjustFormulaReferences()
+                ? 
policy.createBuilder().adjustFormulaReferences(false).build() : policy;
         if (srcRow == null) {
             // srcRow is blank. Overwrite cells with blank values, blank 
styles, etc per cell copy policy
             for (Cell destCell : this) {
-                CellUtil.copyCell(null, destCell, policy, context);
+                CellUtil.copyCell(null, destCell, cellPolicy, context);
             }
 
             if (policy.isCopyMergedRegions()) {
@@ -733,7 +736,7 @@ public class XSSFRow implements Row, Comparable<XSSFRow> {
         } else {
             for (final Cell c : srcRow) {
                 final XSSFCell destCell = createCell(c.getColumnIndex());
-                CellUtil.copyCell(c, destCell, policy, context);
+                CellUtil.copyCell(c, destCell, cellPolicy, context);
             }
 
             final int sheetIndex = _sheet.getWorkbook().getSheetIndex(_sheet);
diff --git 
a/poi-ooxml/src/test/java/org/apache/poi/xssf/usermodel/TestXSSFRow.java 
b/poi-ooxml/src/test/java/org/apache/poi/xssf/usermodel/TestXSSFRow.java
index 22cf5f45d7..52f0b48853 100644
--- a/poi-ooxml/src/test/java/org/apache/poi/xssf/usermodel/TestXSSFRow.java
+++ b/poi-ooxml/src/test/java/org/apache/poi/xssf/usermodel/TestXSSFRow.java
@@ -580,4 +580,18 @@ public final class TestXSSFRow extends BaseTestXRow {
             wb.write(fileOut);
         }
     }
+
+    @Test
+    void testCopyRowFromWithAdjustFormulaReferencesShiftsOnce() throws 
IOException {
+        try (Workbook workbook = new XSSFWorkbook()) {
+            final Sheet sheet = workbook.createSheet("Sheet1");
+            final Row srcRow = sheet.createRow(0);
+            srcRow.createCell(0).setCellFormula("B1+$B$1");
+
+            final CellCopyPolicy policy = new 
CellCopyPolicy.Builder().adjustFormulaReferences(true).build();
+            final XSSFRow destRow = (XSSFRow) sheet.createRow(3);
+            destRow.copyRowFrom(srcRow, policy);
+            assertEquals("B4+$B$1", destRow.getCell(0).getCellFormula());
+        }
+    }
 }
diff --git a/poi/src/main/java/org/apache/poi/hssf/usermodel/HSSFRow.java 
b/poi/src/main/java/org/apache/poi/hssf/usermodel/HSSFRow.java
index d1866444dc..3371ceba0b 100644
--- a/poi/src/main/java/org/apache/poi/hssf/usermodel/HSSFRow.java
+++ b/poi/src/main/java/org/apache/poi/hssf/usermodel/HSSFRow.java
@@ -808,10 +808,13 @@ public final class HSSFRow implements Row, 
Comparable<HSSFRow> {
      */
     @Beta
     public void copyRowFrom(Row srcRow, CellCopyPolicy policy, CellCopyContext 
context) {
+        // formula references are shifted for the whole row below, so don't 
also adjust them per cell
+        final CellCopyPolicy cellPolicy = policy.isAdjustFormulaReferences()
+                ? 
policy.createBuilder().adjustFormulaReferences(false).build() : policy;
         if (srcRow == null) {
             // srcRow is blank. Overwrite cells with blank values, blank 
styles, etc per cell copy policy
             for (Cell destCell : this) {
-                CellUtil.copyCell(null, destCell, policy, context);
+                CellUtil.copyCell(null, destCell, cellPolicy, context);
             }
 
             if (policy.isCopyMergedRegions()) {
@@ -836,7 +839,7 @@ public final class HSSFRow implements Row, 
Comparable<HSSFRow> {
         } else {
             for (final Cell c : srcRow) {
                 final HSSFCell destCell = createCell(c.getColumnIndex());
-                CellUtil.copyCell(c, destCell, policy, context);
+                CellUtil.copyCell(c, destCell, cellPolicy, context);
             }
 
             final int sheetIndex = sheet.getWorkbook().getSheetIndex(sheet);
diff --git a/poi/src/main/java/org/apache/poi/ss/usermodel/CellCopyPolicy.java 
b/poi/src/main/java/org/apache/poi/ss/usermodel/CellCopyPolicy.java
index 722d697f3a..ac92f43f4a 100644
--- a/poi/src/main/java/org/apache/poi/ss/usermodel/CellCopyPolicy.java
+++ b/poi/src/main/java/org/apache/poi/ss/usermodel/CellCopyPolicy.java
@@ -27,6 +27,8 @@ public class CellCopyPolicy {
     public static final boolean DEFAULT_COPY_CELL_FORMULA_POLICY = true;
     public static final boolean DEFAULT_COPY_HYPERLINK_POLICY = true;
     public static final boolean DEFAULT_MERGE_HYPERLINK_POLICY = false;
+    /** @since 6.0.0 */
+    public static final boolean DEFAULT_ADJUST_FORMULA_REFERENCES_POLICY = 
false;
     
     // row-level policies
     public static final boolean DEFAULT_COPY_ROW_HEIGHT_POLICY = true;
@@ -41,6 +43,7 @@ public class CellCopyPolicy {
     private boolean copyCellFormula = DEFAULT_COPY_CELL_FORMULA_POLICY;
     private boolean copyHyperlink = DEFAULT_COPY_HYPERLINK_POLICY;
     private boolean mergeHyperlink = DEFAULT_MERGE_HYPERLINK_POLICY;
+    private boolean adjustFormulaReferences = 
DEFAULT_ADJUST_FORMULA_REFERENCES_POLICY;
     
     // row-level policies
     private boolean copyRowHeight = DEFAULT_COPY_ROW_HEIGHT_POLICY;
@@ -66,6 +69,7 @@ public class CellCopyPolicy {
         copyCellFormula = other.isCopyCellFormula();
         copyHyperlink = other.isCopyHyperlink();
         mergeHyperlink = other.isMergeHyperlink();
+        adjustFormulaReferences = other.isAdjustFormulaReferences();
         
         copyRowHeight = other.isCopyRowHeight();
         condenseRows = other.isCondenseRows();
@@ -82,6 +86,7 @@ public class CellCopyPolicy {
         copyCellFormula = builder.copyCellFormula;
         copyHyperlink = builder.copyHyperlink;
         mergeHyperlink = builder.mergeHyperlink;
+        adjustFormulaReferences = builder.adjustFormulaReferences;
         
         copyRowHeight = builder.copyRowHeight;
         condenseRows = builder.condenseRows;
@@ -96,6 +101,7 @@ public class CellCopyPolicy {
         private boolean copyCellFormula = DEFAULT_COPY_CELL_FORMULA_POLICY;
         private boolean copyHyperlink = DEFAULT_COPY_HYPERLINK_POLICY;
         private boolean mergeHyperlink = DEFAULT_MERGE_HYPERLINK_POLICY;
+        private boolean adjustFormulaReferences = 
DEFAULT_ADJUST_FORMULA_REFERENCES_POLICY;
         
         // row-level policies
         private boolean copyRowHeight = DEFAULT_COPY_ROW_HEIGHT_POLICY;
@@ -131,6 +137,16 @@ public class CellCopyPolicy {
             this.mergeHyperlink = mergeHyperlink;
             return this;
         }
+        /**
+         * @param adjustFormulaReferences whether to adjust the relative 
references of copied formulas
+         * @return this builder
+         * @see CellCopyPolicy#setAdjustFormulaReferences(boolean)
+         * @since 6.0.0
+         */
+        public Builder adjustFormulaReferences(boolean 
adjustFormulaReferences) {
+            this.adjustFormulaReferences = adjustFormulaReferences;
+            return this;
+        }
         
         // row-level policies
         public Builder rowHeight(boolean copyRowHeight) {
@@ -159,6 +175,7 @@ public class CellCopyPolicy {
                 .cellFormula(copyCellFormula)
                 .copyHyperlink(copyHyperlink)
                 .mergeHyperlink(mergeHyperlink)
+                .adjustFormulaReferences(adjustFormulaReferences)
                 .rowHeight(copyRowHeight)
                 .condenseRows(condenseRows)
                 .mergedRegions(copyMergedRegions);
@@ -209,6 +226,37 @@ public class CellCopyPolicy {
         this.copyCellFormula = copyCellFormula;
     }
     
+    /**
+     * @return whether the relative references of copied formulas are adjusted
+     * @see #setAdjustFormulaReferences(boolean)
+     * @since 6.0.0
+     */
+    public boolean isAdjustFormulaReferences() {
+        return adjustFormulaReferences;
+    }
+
+    /**
+     * Sets whether {@link org.apache.poi.ss.util.CellUtil#copyCell} adjusts 
the relative references
+     * of a copied formula by the row and column offset between the source and 
destination cells,
+     * as Excel does when a formula is copied and pasted.
+     * <p>
+     * Absolute references, and the absolute parts of mixed references, are 
not changed.
+     * References keep the sheet they refer to. A reference that would move 
past the limits of the
+     * destination workbook's {@link org.apache.poi.ss.SpreadsheetVersion} 
becomes {@code #REF!}.
+     * The formula is parsed against the destination workbook. If it cannot be 
parsed there
+     * (e.g. it refers to a sheet or name that the destination workbook does 
not have),
+     * it is copied unchanged.
+     * <p>
+     * Defaults to {@code false}, which copies formulas unchanged.
+     * Row copies (e.g. {@code copyRowFrom}) shift formula references whatever 
this setting is.
+     *
+     * @param adjustFormulaReferences whether to adjust the relative 
references of copied formulas
+     * @since 6.0.0
+     */
+    public void setAdjustFormulaReferences(boolean adjustFormulaReferences) {
+        this.adjustFormulaReferences = adjustFormulaReferences;
+    }
+
     /**
      * @return the copyHyperlink
      */
diff --git a/poi/src/main/java/org/apache/poi/ss/util/CellUtil.java 
b/poi/src/main/java/org/apache/poi/ss/util/CellUtil.java
index 929f676313..5c78c02dfe 100644
--- a/poi/src/main/java/org/apache/poi/ss/util/CellUtil.java
+++ b/poi/src/main/java/org/apache/poi/ss/util/CellUtil.java
@@ -28,6 +28,16 @@ import java.util.Set;
 import org.apache.logging.log4j.Logger;
 import org.apache.poi.common.Duplicatable;
 import org.apache.poi.logging.PoiLogManager;
+import org.apache.poi.ss.SpreadsheetVersion;
+import org.apache.poi.ss.formula.EvaluationWorkbook;
+import org.apache.poi.ss.formula.FormulaParseException;
+import org.apache.poi.ss.formula.FormulaParser;
+import org.apache.poi.ss.formula.FormulaParsingWorkbook;
+import org.apache.poi.ss.formula.FormulaRenderer;
+import org.apache.poi.ss.formula.FormulaRenderingWorkbook;
+import org.apache.poi.ss.formula.FormulaShifter;
+import org.apache.poi.ss.formula.FormulaType;
+import org.apache.poi.ss.formula.ptg.Ptg;
 import org.apache.poi.ss.usermodel.BorderStyle;
 import org.apache.poi.ss.usermodel.Cell;
 import org.apache.poi.ss.usermodel.CellCopyContext;
@@ -423,7 +433,8 @@ public final class CellUtil {
                         
destCell.setCellValue(srcCell.getRichStringCellValue());
                         break;
                     case FORMULA:
-                        destCell.setCellFormula(srcCell.getCellFormula());
+                        
destCell.setCellFormula(policy.isAdjustFormulaReferences()
+                                ? adjustFormulaReferences(srcCell, destCell) : 
srcCell.getCellFormula());
                         break;
                     case BLANK:
                         destCell.setBlank();
@@ -486,6 +497,50 @@ public final class CellUtil {
         }
     }
 
+
+    /**
+     * Returns the formula of srcCell with its relative references moved by 
the row and column
+     * offset from srcCell to destCell, as Excel does when a formula is copied 
and pasted.
+     * The formula is parsed and rendered against the destination workbook. It 
is returned
+     * unchanged if it cannot be parsed there.
+     */
+    private static String adjustFormulaReferences(Cell srcCell, Cell destCell) 
{
+        final String formula = srcCell.getCellFormula();
+        final int rowOffset = destCell.getRowIndex() - srcCell.getRowIndex();
+        final int columnOffset = destCell.getColumnIndex() - 
srcCell.getColumnIndex();
+        if (formula == null || formula.isEmpty() || (rowOffset == 0 && 
columnOffset == 0)) {
+            return formula;
+        }
+        final Sheet destSheet = destCell.getSheet();
+        final Workbook destWorkbook = destSheet.getWorkbook();
+        final EvaluationWorkbook evaluationWorkbook = 
destWorkbook.createEvaluationWorkbook();
+        if (!(evaluationWorkbook instanceof FormulaParsingWorkbook 
parsingWorkbook)
+                || !(evaluationWorkbook instanceof FormulaRenderingWorkbook 
renderingWorkbook)) {
+            return formula;
+        }
+        final int sheetIndex = destWorkbook.getSheetIndex(destSheet);
+        final String sheetName = destSheet.getSheetName();
+        final SpreadsheetVersion version = 
destWorkbook.getSpreadsheetVersion();
+        try {
+            final Ptg[] ptgs = FormulaParser.parse(formula, parsingWorkbook, 
FormulaType.CELL,
+                    sheetIndex, srcCell.getRowIndex());
+            boolean changed = false;
+            if (rowOffset != 0) {
+                final int srcRow = srcCell.getRowIndex();
+                changed = FormulaShifter.createForRowCopy(sheetIndex, 
sheetName, srcRow, srcRow, rowOffset, version)
+                        .adjustFormula(ptgs, sheetIndex);
+            }
+            if (columnOffset != 0) {
+                final int srcColumn = srcCell.getColumnIndex();
+                changed |= FormulaShifter.createForColumnCopy(sheetIndex, 
sheetName, srcColumn, srcColumn, columnOffset, version)
+                        .adjustFormula(ptgs, sheetIndex);
+            }
+            return changed ? 
FormulaRenderer.toFormulaString(renderingWorkbook, ptgs) : formula;
+        } catch (FormulaParseException e) {
+            LOGGER.atWarn().withThrowable(e).log("Copying formula unchanged, 
as it could not be parsed in the destination workbook");
+            return formula;
+        }
+    }
     /**
      * Take a cell, and align it.
      * <p>
diff --git a/poi/src/test/java/org/apache/poi/hssf/usermodel/TestHSSFRow.java 
b/poi/src/test/java/org/apache/poi/hssf/usermodel/TestHSSFRow.java
index 460d6f02b5..f692b3e9e1 100644
--- a/poi/src/test/java/org/apache/poi/hssf/usermodel/TestHSSFRow.java
+++ b/poi/src/test/java/org/apache/poi/hssf/usermodel/TestHSSFRow.java
@@ -264,4 +264,18 @@ final class TestHSSFRow extends BaseTestRow {
         assertEquals(styleCount, workbook.getNumCellStyles(), "no new styles 
should be added by copyRow");
         workbook.close();
     }
+
+    @Test
+    void testCopyRowFromWithAdjustFormulaReferencesShiftsOnce() throws 
IOException {
+        try (Workbook workbook = new HSSFWorkbook()) {
+            final Sheet sheet = workbook.createSheet("Sheet1");
+            final Row srcRow = sheet.createRow(0);
+            srcRow.createCell(0).setCellFormula("B1+$B$1");
+
+            final CellCopyPolicy policy = new 
CellCopyPolicy.Builder().adjustFormulaReferences(true).build();
+            final HSSFRow destRow = (HSSFRow) sheet.createRow(3);
+            destRow.copyRowFrom(srcRow, policy);
+            assertEquals("B4+$B$1", destRow.getCell(0).getCellFormula());
+        }
+    }
 }
diff --git a/poi/src/test/java/org/apache/poi/ss/util/BaseTestCellUtilCopy.java 
b/poi/src/test/java/org/apache/poi/ss/util/BaseTestCellUtilCopy.java
index 14d3fa00a1..5b1eb59621 100644
--- a/poi/src/test/java/org/apache/poi/ss/util/BaseTestCellUtilCopy.java
+++ b/poi/src/test/java/org/apache/poi/ss/util/BaseTestCellUtilCopy.java
@@ -215,4 +215,59 @@ public abstract class BaseTestCellUtilCopy {
     protected boolean compareRichText(RichTextString rts1, RichTextString 
rts2) {
         return rts1.getString().equals(rts2.getString());
     }
+
+    @Test
+    public final void 
testCopyCellFrom_CellCopyPolicy_adjustFormulaReferences() {
+        setUp_testCopyCellFrom_CellCopyPolicy();
+        final Sheet sheet = srcCell.getSheet();
+        sheet.getWorkbook().createSheet("Other");
+        srcCell = sheet.createRow(1).createCell(1); // B2
+        srcCell.setCellFormula("A1+$A$1+A$1+$A1+SUM(A1:B2)+Other!A1");
+
+        final CellCopyPolicy policy = new 
CellCopyPolicy.Builder().adjustFormulaReferences(true).build();
+        assertTrue(policy.isAdjustFormulaReferences());
+        assertTrue(policy.createBuilder().build().isAdjustFormulaReferences());
+        assertTrue(new CellCopyPolicy(policy).isAdjustFormulaReferences());
+
+        // 3 rows down and 2 columns right
+        destCell = sheet.createRow(4).createCell(3); // D5
+        CellUtil.copyCell(srcCell, destCell, policy, new CellCopyContext());
+        assertEquals("C4+$A$1+C$1+$A4+SUM(C4:D5)+Other!C4", 
stringWithoutSpaces(destCell.getCellFormula()));
+
+        // same row, so only columns move
+        destCell = sheet.getRow(1).createCell(2); // C2
+        CellUtil.copyCell(srcCell, destCell, policy, new CellCopyContext());
+        assertEquals("B1+$A$1+B$1+$A1+SUM(B1:C2)+Other!B1", 
stringWithoutSpaces(destCell.getCellFormula()));
+
+        // source cell unchanged
+        assertEquals("A1+$A$1+A$1+$A1+SUM(A1:B2)+Other!A1", 
stringWithoutSpaces(srcCell.getCellFormula()));
+    }
+
+    @Test
+    public final void 
testCopyCellFrom_CellCopyPolicy_adjustFormulaReferencesOffSheet() {
+        setUp_testCopyCellFrom_CellCopyPolicy();
+        final Sheet sheet = srcCell.getSheet();
+        srcCell = sheet.createRow(1).createCell(1); // B2
+        srcCell.setCellFormula("A1+$A$1");
+
+        // moving A1 up one row would go past the top of the sheet
+        destCell = sheet.getRow(0).createCell(1); // B1
+        final CellCopyPolicy policy = new 
CellCopyPolicy.Builder().adjustFormulaReferences(true).build();
+        CellUtil.copyCell(srcCell, destCell, policy, new CellCopyContext());
+        assertEquals("#REF!+$A$1", 
stringWithoutSpaces(destCell.getCellFormula()));
+    }
+
+    @Test
+    public final void 
testCopyCellFrom_CellCopyPolicy_noAdjustFormulaReferencesByDefault() {
+        setUp_testCopyCellFrom_CellCopyPolicy();
+        final Sheet sheet = srcCell.getSheet();
+        srcCell = sheet.createRow(1).createCell(1); // B2
+        srcCell.setCellFormula("A1+$A$1");
+        destCell = sheet.createRow(4).createCell(3); // D5
+
+        final CellCopyPolicy policy = new CellCopyPolicy();
+        assertFalse(policy.isAdjustFormulaReferences());
+        CellUtil.copyCell(srcCell, destCell, policy, new CellCopyContext());
+        assertEquals("A1+$A$1", 
stringWithoutSpaces(destCell.getCellFormula()));
+    }
 }


---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]

Reply via email to