Created attachment 33926 [details]
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.