Attachment #33926 for bug #59673

View | Details | Raw Unified | Return to bug 59673
Collapse All | Expand All




    }


    private Integer findNewSheetIndexDueToSheetMove(int oldSheetIndex) {
        if(ptg instanceof Ref3DPtg) {
            Ref3DPtg ref = (Ref3DPtg)ptg;
            int oldSheetIndex = ref.getExternSheetIndex();
            
            // we have to handle a few cases here
            
            // 1. sheet is outside moved sheets, no change necessary

            
            // 2. ptg refers to the moved sheet
            if(oldSheetIndex == _srcSheetIndex) {
                return _dstSheetIndex;
                return ref;
            }

            // 3. new index is lower than old one => sheets get moved up
            if (_dstSheetIndex < _srcSheetIndex) {
                return oldSheetIndex+1;
                return ref;
            }

            // 4. new index is higher than old one => sheets get moved down
            if (_dstSheetIndex > _srcSheetIndex) {
                return oldSheetIndex-1;
                return ref;
            }
            return null;
    }

    private Ptg adjustPtgDueToSheetMove(Ptg ptg) {
        if(ptg instanceof Ref3DPtg) {
            Ref3DPtg ref = (Ref3DPtg)ptg;
            Integer newSheetIndex = findNewSheetIndexDueToSheetMove(ref.getExternSheetIndex());
            if (newSheetIndex != null) {
                ref.setExternSheetIndex(newSheetIndex);
                return ref;
            } else {
                return null;
            }
        } else if (ptg instanceof Area3DPtg) {
            Area3DPtg ref = (Area3DPtg)ptg;
            Integer newSheetIndex = findNewSheetIndexDueToSheetMove(ref.getExternSheetIndex());
            if (newSheetIndex != null) {
                ref.setExternSheetIndex(newSheetIndex);
                return ref;
            } else {
                return null;
            }
        } 
        return null;
    }





        
        wb.close();
    }
    @Test
    public void test58746_ranges() throws IOException {
        HSSFWorkbook wb = new HSSFWorkbook();
        
        HSSFSheet first = wb.createSheet("first");
        first.createRow(0).createCell(0).setCellValue(1);
        
        HSSFSheet second = wb.createSheet("second");
        second.createRow(0).createCell(0).setCellValue(2);
        
        HSSFSheet third = wb.createSheet("third");
        HSSFRow row = third.createRow(0);
        row.createCell(0).setCellFormula("SUM(first!A1:A2)");
        row.createCell(1).setCellFormula("SUM(second!A1:A2)");

        // re-order for sheet "third"
        wb.setSheetOrder("third", 0);
        
        // verify results
        assertEquals("third", wb.getSheetAt(0).getSheetName());
        assertEquals("first", wb.getSheetAt(1).getSheetName());
        assertEquals("second", wb.getSheetAt(2).getSheetName());
        
        assertEquals("SUM(first!A1:A2)", wb.getSheetAt(0).getRow(0).getCell(0).getCellFormula());
        assertEquals("SUM(second!A1:A2)", wb.getSheetAt(0).getRow(0).getCell(1).getCellFormula());
        
        wb.close();
    }

    @Test
    public void bug59135() throws IOException {

Return to bug 59673

Lines 282-292 public final class FormulaShifter { Link Here

(-)a/src/java/org/apache/poi/ss/formula/FormulaShifter.java (-12 / +26 lines)
282	}	282	}
283		283
284		284
285	private Ptg adjustPtgDueToSheetMove(Ptg ptg) {	285	private Integer findNewSheetIndexDueToSheetMove(int oldSheetIndex) {
286	if(ptg instanceof Ref3DPtg) {
287	Ref3DPtg ref = (Ref3DPtg)ptg;
288	int oldSheetIndex = ref.getExternSheetIndex();
289
290	// we have to handle a few cases here	286	// we have to handle a few cases here
291		287
292	// 1. sheet is outside moved sheets, no change necessary	288	// 1. sheet is outside moved sheets, no change necessary
Lines 301-323 public final class FormulaShifter { Link Here
301		297
302	// 2. ptg refers to the moved sheet	298	// 2. ptg refers to the moved sheet
303	if(oldSheetIndex == _srcSheetIndex) {	299	if(oldSheetIndex == _srcSheetIndex) {
304	ref.setExternSheetIndex(_dstSheetIndex);	300	return _dstSheetIndex;
305	return ref;
306	}	301	}
307		302
308	// 3. new index is lower than old one => sheets get moved up	303	// 3. new index is lower than old one => sheets get moved up
309	if (_dstSheetIndex < _srcSheetIndex) {	304	if (_dstSheetIndex < _srcSheetIndex) {
310	ref.setExternSheetIndex(oldSheetIndex+1);	305	return oldSheetIndex+1;
311	return ref;
312	}	306	}
313		307
314	// 4. new index is higher than old one => sheets get moved down	308	// 4. new index is higher than old one => sheets get moved down
315	if (_dstSheetIndex > _srcSheetIndex) {	309	if (_dstSheetIndex > _srcSheetIndex) {
316	ref.setExternSheetIndex(oldSheetIndex-1);	310	return oldSheetIndex-1;
317	return ref;
318	}	311	}
319	}	312	return null;
		313	}
320		314
		315	private Ptg adjustPtgDueToSheetMove(Ptg ptg) {
		316	if(ptg instanceof Ref3DPtg) {
		317	Ref3DPtg ref = (Ref3DPtg)ptg;
		318	Integer newSheetIndex = findNewSheetIndexDueToSheetMove(ref.getExternSheetIndex());
		319	if (newSheetIndex != null) {
		320	ref.setExternSheetIndex(newSheetIndex);
		321	return ref;
		322	} else {
		323	return null;
		324	}
		325	} else if (ptg instanceof Area3DPtg) {
		326	Area3DPtg ref = (Area3DPtg)ptg;
		327	Integer newSheetIndex = findNewSheetIndexDueToSheetMove(ref.getExternSheetIndex());
		328	if (newSheetIndex != null) {
		329	ref.setExternSheetIndex(newSheetIndex);
		330	return ref;
		331	} else {
		332	return null;
		333	}
		334	}
321	return null;	335	return null;
322	}	336	}
323		337

Lines 1216-1221 public final class TestHSSFSheet extends BaseTestSheet { Link Here

(-)a/src/testcases/org/apache/poi/hssf/usermodel/TestHSSFSheet.java (+28 lines)
1216		1216
1217	wb.close();	1217	wb.close();
1218	}	1218	}
		1219	@Test
		1220	public void test58746_ranges() throws IOException {
		1221	HSSFWorkbook wb = new HSSFWorkbook();
		1222
		1223	HSSFSheet first = wb.createSheet("first");
		1224	first.createRow(0).createCell(0).setCellValue(1);
		1225
		1226	HSSFSheet second = wb.createSheet("second");
		1227	second.createRow(0).createCell(0).setCellValue(2);
		1228
		1229	HSSFSheet third = wb.createSheet("third");
		1230	HSSFRow row = third.createRow(0);
		1231	row.createCell(0).setCellFormula("SUM(first!A1:A2)");
		1232	row.createCell(1).setCellFormula("SUM(second!A1:A2)");
		1233
		1234	// re-order for sheet "third"
		1235	wb.setSheetOrder("third", 0);
		1236
		1237	// verify results
		1238	assertEquals("third", wb.getSheetAt(0).getSheetName());
		1239	assertEquals("first", wb.getSheetAt(1).getSheetName());
		1240	assertEquals("second", wb.getSheetAt(2).getSheetName());
		1241
		1242	assertEquals("SUM(first!A1:A2)", wb.getSheetAt(0).getRow(0).getCell(0).getCellFormula());
		1243	assertEquals("SUM(second!A1:A2)", wb.getSheetAt(0).getRow(0).getCell(1).getCellFormula());
		1244
		1245	wb.close();
		1246	}
1219		1247
1220	@Test	1248	@Test
1221	public void bug59135() throws IOException {	1249	public void bug59135() throws IOException {