https://bz.apache.org/bugzilla/show_bug.cgi?id=59673
Bug ID: 59673
Summary: HSSFWorkbook#setSheetOrder does not update external
sheet indexes in formula references with ranges rather
than single cells
Product: POI
Version: unspecified
Hardware: PC
OS: Linux
Status: NEW
Severity: regression
Priority: P2
Component: HSSF
Assignee: [email protected]
Reporter: [email protected]
Created attachment 33926
--> https://bz.apache.org/bugzilla/attachment.cgi?id=33926&action=edit
Patch making treatment of Area3DPtg and Ref3DPtg consistent
Since POI 3.11, the setSheetOrder method on HSSFWorkbook changes formulas and
named ranges which refer to areas rather than single cells to point to the
wrong sheet.
This happens because the move now changes the external sheet indexes for
sheets, but when FormulaShifter is used to update formulas and named ranges
that refer to those sheets, only references which consist of a single cell
(Ref3DPtg) are updated, leaving references to an area (Area3DPtg) unchanged but
pointing at the wrong external sheet index.
For example:
=========
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.usermodel.HSSFName;
public class Demo {
public static void main(String ... args) throws Exception {
final HSSFWorkbook test = new HSSFWorkbook();
HSSFSheet sheetA = test.createSheet("A");
HSSFSheet sheetB = test.createSheet("B");
HSSFName name = test.createName();
name.setNameName("cellsOnA");
name.setRefersToFormula("B!A1:A2");
HSSFName name2 = test.createName();
name2.setNameName("cellOnA");
name2.setRefersToFormula("B!A1");
System.out.println("Formulas before sheet re-ordering:");
System.out.println(name.getRefersToFormula());
System.out.println(name2.getRefersToFormula());
test.setSheetOrder("B", 0);
System.out.println("Formulas after sheet re-ordering:");
System.out.println(name.getRefersToFormula());
System.out.println(name2.getRefersToFormula());
}
}
=========
outputs
=========
Formulas before sheet re-ordering:
B!A1:A2
B!A1
Formulas after sheet re-ordering:
A!A1:A2
B!A1
=========
demonstrating that the behaviour for references to cells and references to
ranges of cells are inconsistent.
Attached is a patch which changes the behaviour for
FormulaShifter#adjustPtgDueToSheetMove to be consistent for Area3DPtg and
Ref3DPtg, adding a unittest based on that for Bug 58746. However, I think this
needs more work - it looks like HSSFWorkbook#setSheetOrder is passing
*internal* sheet indexes in to FormulaShifter's constructor, but the values
being passed to and from the Ptg appear to be *external* sheet indexes, and the
two are not guaranteed to be in sync.
--
You are receiving this mail because:
You are the assignee for the bug.
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
