pjfanning opened a new issue, #1416:
URL: https://github.com/apache/poi/issues/1416
## Summary
Add an opt-in `CellCopyPolicy` setting that makes `CellUtil.copyCell` adjust
a copied formula's relative references by the row/column offset between the
source and destination cells. This is what Excel does when you copy and paste a
formula.
## Motivation
Today, `CellUtil.copyCell(src, dest, policy, context)` copies a formula
verbatim:
```java
case FORMULA:
destCell.setCellFormula(srcCell.getCellFormula());
```
Formula references are only adjusted when a whole row is copied with
`XSSFRow.copyRowFrom` / `HSSFRow.copyRowFrom`, which run
`FormulaShifter.createForRowCopy` over the destination row afterwards. This
leaves gaps:
- **No column copy.** There is no `copyColumn` equivalent, so copying a
formula sideways (e.g. a horizontal template fill) has no POI API.
- **No SXSSF.** `SXSSFRow` has no `copyRowFrom`, so streaming writers cannot
copy a formula row at all.
- **All or nothing.** `copyRowFrom` copies every cell in the row. Template
engines want to copy the formula cells and write their own values elsewhere.
`CellCopyPolicy` cannot copy formulas without also copying values
(`copyCellFormula` only applies when `copyCellValue` is set).
As a result, downstream libraries reimplement the logic themselves: parse
with an `*EvaluationWorkbook`, build a row-copy or column-copy
`FormulaShifter`, `adjustFormula`, then render with `FormulaRenderer`. Apache
Fesod is currently adding about 100 lines of exactly this for template fills
(apache/fesod#1215), including a separate code path for the horizontal
direction.
The public `SharedFormula.convertSharedFormulas(ptgs, row, col)` looks like
it would do this, but it is not suitable as a general copy helper. It rebuilds
every `RefPtgBase`/`AreaPtgBase` as a plain `RefPtg`/`AreaPtg`, so 3D
references (`Ref3DPtg`, `Area3DPxg`, …) lose their sheet: `Sheet2!A1` becomes
`A1`. It also wraps references past the sheet edge instead of producing `#REF!`.
## Proposal
1. Add `CellCopyPolicy#isAdjustFormulaReferences()` /
`Builder#adjustFormulaReferences(boolean)`, **defaulting to `false`**.
2. When it is set and a formula is copied, `CellUtil.copyCell` parses the
source formula against the destination workbook, shifts relative row references
by `destRow - srcRow` and relative column references by `destCol - srcCol`, and
renders the result. Absolute references and absolute parts of mixed references
stay put. Areas, 3D references and references to other sheets keep their sheet.
References that would go past the destination's `SpreadsheetVersion` limits
become `#REF!`, as `FormulaShifter` already does.
- Implementation: `FormulaShifter.createForRowCopy` followed by
`createForColumnCopy` (or a combined row+column copy mode), with the
parsing/rendering workbook chosen from the destination cell's workbook:
`HSSFEvaluationWorkbook`, `XSSFEvaluationWorkbook` or
`SXSSFEvaluationWorkbook`. Because it works on a single `Cell`, it covers HSSF,
XSSF and SXSSF destinations alike.
- Same-workbook copies are the main case. Copying across workbooks can
keep the current verbatim behaviour, or be documented as unsupported.
3. Optional follow-up: allow formulas to be copied without values, e.g. a
`copyCellValue=false, copyCellFormula=true` combination meaning "formulas
only", or a cell filter. That is a separate change and could be left out of
this one.
With this, a template engine can copy just the formula cells into each
created row or column:
```java
CellCopyPolicy policy = new CellCopyPolicy.Builder()
.adjustFormulaReferences(true)
.build();
CellUtil.copyCell(templateFormulaCell, targetCell, policy, context);
```
## Behaviour-change risk
None by default: the setting is off unless the caller turns it on, and
current verbatim copying is unchanged.
Things to decide before switching it on anywhere by default:
- **`XSSFRow.copyRowFrom`/`HSSFRow.copyRowFrom`** already shift the
destination row after copying. If they are moved onto the new setting, they
must not shift twice. They could set the flag internally and drop their
post-copy `updateRowFormulas`, which gives the same results for existing
callers.
- **Existing `CellUtil.copyCell` callers** that copy formulas and then
adjust references themselves would double-shift if a future major release
turned the setting on by default. Keeping it opt-in avoids that.
- **Copies across workbooks** depend on sheet names and defined names
resolving in the destination workbook. Falling back to verbatim (or throwing)
when parsing fails is safer than silently guessing.
## Related
- #1414: `HSSFRow.copyRowFrom` passes `SpreadsheetVersion.EXCEL2007` and the
workbook sheet index to `createForRowCopy`. A shared implementation that takes
the version and extern sheet index from the destination workbook would fix that
as well.
- A possible follow-up: `SXSSFRow.copyRowFrom(Row, CellCopyPolicy)`, limited
to sources still in memory (an SXSSF row still in the window, or an `XSSFRow`
from the wrapped template workbook), built on this per-cell support.
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: [email protected]
For queries about this service, please contact Infrastructure at:
[email protected]
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
