pjfanning opened a new issue, #1414:
URL: https://github.com/apache/poi/issues/1414
`HSSFRow.copyRowFrom(Row, CellCopyPolicy, CellCopyContext)` builds its
formula shifter like this (trunk, `HSSFRow.java` ~line 848):
```java
final int sheetIndex = sheet.getWorkbook().getSheetIndex(sheet);
...
final FormulaShifter formulaShifter = FormulaShifter.createForRowCopy(
sheetIndex, sheetName, srcRowNum, srcRowNum, rowDifference,
SpreadsheetVersion.EXCEL2007);
```
`HSSFSheet.shiftRows` does the equivalent operation like this:
```java
int sheetIndex = _workbook.getSheetIndex(this);
short externSheetIndex = _book.checkExternSheet(sheetIndex);
FormulaShifter formulaShifter = FormulaShifter.createForRowShift(
externSheetIndex, sheetName, startRow, endRow, n,
SpreadsheetVersion.EXCEL97);
```
There are two differences:
1. **`SpreadsheetVersion.EXCEL2007` instead of `EXCEL97`.** The version sets
the last valid row index the shifter uses. With `EXCEL2007`, copying a row with
a relative reference to a position where the shifted reference would go past
row 65,536 does not turn it into `#REF!`. The cell ends up with an out-of-range
reference that is invalid for `.xls`. This looks like a copy-paste from
`XSSFRow.copyRowFrom`.
2. **Workbook sheet index instead of the extern sheet index.**
`FormulaShifter` compares the index it is given with the extern sheet index
stored in `Ref3DPtg`/`Area3DPtg`. In HSSF that comes from the EXTERNSHEET table
and is not guaranteed to equal the workbook sheet index. Same-sheet 3D
references, such as `Sheet2!A1` on Sheet2, may therefore not be adjusted, or
references to another sheet may be adjusted by mistake, whenever the two
indexes differ. I have not written a reproducer for this one. It just differs
from how `HSSFSheet.shiftRows`/`shiftColumns` compute the index.
Suggested fix: use `SpreadsheetVersion.EXCEL97` and
`checkExternSheet(sheetIndex)`, matching `HSSFSheet.shiftRows`.
Behaviour impact is limited to cases that are currently broken: copies whose
relative references end up past the `.xls` row limit, and workbooks whose
EXTERNSHEET order differs from sheet order. Normal copies give the same result.
Found while reviewing a downstream project (Apache Fesod) that does its own
per-cell formula copying with `FormulaShifter.createForRowCopy`; that code
passes the parsing workbook's `SpreadsheetVersion` and so is not affected.
--
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]
