pjfanning opened a new pull request, #1312:
URL: https://github.com/apache/poi/pull/1312
## Summary
Stacked on #1311 (its commit is the first one here; this PR adds the
second). Rebase once #1311 is merged.
### Bug fix
`SharedFormula.convertSharedFormulas` replaced every
`RefPtgBase`/`AreaPtgBase` token with a plain `RefPtg`/`AreaPtg`. But
`Ref3DPtg`/`Area3DPtg` (HSSF) and `Ref3DPxg`/`Area3DPxg` (XSSF) extend those
bases too, so a shared formula that refers to another sheet **lost the sheet**
and was evaluated against its own sheet. With
`=VLOOKUP(B2,Prices!$A$2:$B$4,2,FALSE)` filled down as a shared formula,
`getCellFormula()` returned `VLOOKUP(B2,$A$2:$B$4,2,FALSE)` — for the master
cell as well as the dependents — and every lookup gave `#N/A`.
Now only `RefNPtg`/`RefPtg` and `AreaNPtg`/`AreaPtg` become plain tokens;
the four 3D tokens are copied with their sheet kept and only the relative
row/column parts fixed up. `TestSharedFormula` covers the conversion directly:
relative tRefN/tAreaN offsets, XSSF and HSSF 3D tokens (relative and absolute),
and operand-class preservation.
### Tests against shared formulas
Every xlsx written by Excel stores filled-down formulas as shared formulas,
and POI resolves each dependent through `convertSharedFormula`, so the
evaluator tests should run against them too. POI cannot create shared formulas
through the usermodel and only registers them when reading a sheet, so
`XSSFSharedFormulaFixture` rewrites the fixture workbook at the `CTCellFormula`
level (`D2:D4` — the VLOOKUPs filled down; `A6:C6` — the SUMs filled right) and
writes it out and reads it back. The cache notification, rows/columns and
stability classifier suites each get a subclass running against it (39 tests),
plus a check that the reloaded workbook really contains shared groups and that
dependents reconstruct their text. `BaseTestFormulaEvaluatorFixture` gains a
`finishWorkbook()` hook for this.
XSSF only: HSSF shared formulas (`SharedFormulaRecord`, tRefN/tAreaN tokens)
cannot be built programmatically at all — POI only preserves them from files it
read. The fix itself covers HSSF's tokens and is unit-tested for them.
All 39 shared-formula tests failed before the fix (every `D2`..`D4`
evaluated to `#N/A`) and pass after it, including the row/column shifts through
shared-formula masters.
## Test plan
- [x] `TestSharedFormula` (4)
- [x]
`TestXSSFFormulaEvaluator{CacheNotification,RowsAndColumns,StabilityClassifier}SharedFormulas`
(18 + 16 + 5)
- [x] Regression sweep: `org.apache.poi.ss.formula.*`,
`org.apache.poi.hssf.record.*`, `TestFormulas`, `TestHSSFFormulaEvaluator`,
`TestHSSFSheetShiftRows`, `TestHSSFBugs`, `org.apache.poi.xssf.usermodel.*` —
4201 tests, the only 2 failures are `stackoverflow23114397` (autoSizeColumn
font metrics), which fail identically on trunk on this machine
- [ ] CI
🤖 Generated with [Claude Code](https://claude.com/claude-code)
--
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]
