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]