src/testcases/org/apache/poi/ss/usermodel/BaseTestSheetShiftRows.java - poi - Git at Google

 /* ====================================================================
    Licensed to the Apache Software Foundation (ASF) under one or more
    contributor license agreements.  See the NOTICE file distributed with
    this work for additional information regarding copyright ownership.
    The ASF licenses this file to You under the Apache License, Version 2.0
    (the "License"); you may not use this file except in compliance with
    the License.  You may obtain a copy of the License at

        http://www.apache.org/licenses/LICENSE-2.0

    Unless required by applicable law or agreed to in writing, software
    distributed under the License is distributed on an "AS IS" BASIS,
    WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
    See the License for the specific language governing permissions and
    limitations under the License.
 ==================================================================== */

 package org.apache.poi.ss.usermodel;

 import java.io.IOException;

 import junit.framework.TestCase;

 import org.apache.poi.hssf.usermodel.HSSFWorkbook;
 import org.apache.poi.ss.ITestDataProvider;
 import org.apache.poi.ss.util.CellRangeAddress;
 import org.apache.poi.ss.util.CellReference;

 /**
  * Tests row shifting capabilities.
  *
  * @author Shawn Laubach (slaubach at apache dot com)
  * @author Toshiaki Kamoshida (kamoshida.toshiaki at future dot co dot jp)
  */
 public abstract class BaseTestSheetShiftRows extends TestCase {

     private final ITestDataProvider _testDataProvider;

     protected BaseTestSheetShiftRows(ITestDataProvider testDataProvider) {
         _testDataProvider = testDataProvider;
     }

     /**
      * Tests the shiftRows function.  Does three different shifts.
      * After each shift, writes the workbook to file and reads back to
      * check.  This ensures that if some changes code that breaks
      * writing or what not, they realize it.
      */
     public final void testShiftRows(){
         // Read initial file in
         String sampleName = "SimpleMultiCell." + _testDataProvider.getStandardFileNameExtension();
         Workbook wb = _testDataProvider.openSampleWorkbook(sampleName);
         Sheet s = wb.getSheetAt( 0 );

         // Shift the second row down 1 and write to temp file
         s.shiftRows( 1, 1, 1 );

         wb = _testDataProvider.writeOutAndReadBack(wb);

         // Read from temp file and check the number of cells in each
         // row (in original file each row was unique)
         s = wb.getSheetAt( 0 );

         assertEquals(s.getRow(0).getPhysicalNumberOfCells(), 1);
         confirmEmptyRow(s, 1);
         assertEquals(s.getRow(2).getPhysicalNumberOfCells(), 2);
         assertEquals(s.getRow(3).getPhysicalNumberOfCells(), 4);
         assertEquals(s.getRow(4).getPhysicalNumberOfCells(), 5);

         // Shift rows 1-3 down 3 in the current one.  This tests when
         // 1 row is blank.  Write to a another temp file
         s.shiftRows( 0, 2, 3 );
         wb = _testDataProvider.writeOutAndReadBack(wb);

         // Read and ensure things are where they should be
         s = wb.getSheetAt(0);
         confirmEmptyRow(s, 0);
         confirmEmptyRow(s, 1);
         confirmEmptyRow(s, 2);
         assertEquals(s.getRow(3).getPhysicalNumberOfCells(), 1);
         confirmEmptyRow(s, 4);
         assertEquals(s.getRow(5).getPhysicalNumberOfCells(), 2);

         // Read the first file again
         wb = _testDataProvider.openSampleWorkbook(sampleName);
         s = wb.getSheetAt( 0 );

         // Shift rows 3 and 4 up and write to temp file
         s.shiftRows( 2, 3, -2 );
         wb = _testDataProvider.writeOutAndReadBack(wb);
         s = wb.getSheetAt( 0 );
         assertEquals(s.getRow(0).getPhysicalNumberOfCells(), 3);
         assertEquals(s.getRow(1).getPhysicalNumberOfCells(), 4);
         confirmEmptyRow(s, 2);
         confirmEmptyRow(s, 3);
         assertEquals(s.getRow(4).getPhysicalNumberOfCells(), 5);
     }
     private static void confirmEmptyRow(Sheet s, int rowIx) {
         Row row = s.getRow(rowIx);
         assertTrue(row == null || row.getPhysicalNumberOfCells() == 0);
     }

     /**
      * Tests when rows are null.
      */
     public final void testShiftRow() {
         Workbook b = _testDataProvider.createWorkbook();
         Sheet s	= b.createSheet();
         s.createRow(0).createCell(0).setCellValue("TEST1");
         s.createRow(3).createCell(0).setCellValue("TEST2");
         s.shiftRows(0,4,1);
     }

     /**
      * Tests when shifting the first row.
      */
     public final void testActiveCell() {
         Workbook b = _testDataProvider.createWorkbook();
         Sheet s	= b.createSheet();

         s.createRow(0).createCell(0).setCellValue("TEST1");
         s.createRow(3).createCell(0).setCellValue("TEST2");
         s.shiftRows(0,4,1);
     }

     /**
      * When shifting rows, the page breaks should go with it
      */
     public void testShiftRowBreaks() { // TODO - enable XSSF test
         Workbook b = _testDataProvider.createWorkbook();
         Sheet s	= b.createSheet();
         Row row = s.createRow(4);
         row.createCell(0).setCellValue("test");
         s.setRowBreak(4);

         s.shiftRows(4, 4, 2);
         assertTrue("Row number 6 should have a pagebreak", s.isRowBroken(6));
     }

     public void testShiftWithComments() {
         Workbook wb = _testDataProvider.openSampleWorkbook("comments." + _testDataProvider.getStandardFileNameExtension());

         Sheet sheet = wb.getSheet("Sheet1");
         assertEquals(3, sheet.getLastRowNum());

         // Verify comments are in the position expected
         assertNotNull(sheet.getCellComment(0,0));
         assertNull(sheet.getCellComment(1,0));
         assertNotNull(sheet.getCellComment(2,0));
         assertNotNull(sheet.getCellComment(3,0));

         String comment1 = sheet.getCellComment(0,0).getString().getString();
         assertEquals(comment1,"comment top row1 (index0)\n");
         String comment3 = sheet.getCellComment(2,0).getString().getString();
         assertEquals(comment3,"comment top row3 (index2)\n");
         String comment4 = sheet.getCellComment(3,0).getString().getString();
         assertEquals(comment4,"comment top row4 (index3)\n");

         //Workbook wbBack = _testDataProvider.writeOutAndReadBack(wb);

         // Shifting all but first line down to test comments shifting
         sheet.shiftRows(1, sheet.getLastRowNum(), 1, true, true);

         // Test that comments were shifted as expected
         assertEquals(4, sheet.getLastRowNum());
         assertNotNull(sheet.getCellComment(0,0));
         assertNull(sheet.getCellComment(1,0));
         assertNull(sheet.getCellComment(2,0));
         assertNotNull(sheet.getCellComment(3,0));
         assertNotNull(sheet.getCellComment(4,0));

         String comment1_shifted = sheet.getCellComment(0,0).getString().getString();
         assertEquals(comment1,comment1_shifted);
         String comment3_shifted = sheet.getCellComment(3,0).getString().getString();
         assertEquals(comment3,comment3_shifted);
         String comment4_shifted = sheet.getCellComment(4,0).getString().getString();
         assertEquals(comment4,comment4_shifted);

         // Write out and read back in again
         // Ensure that the changes were persisted
         wb = _testDataProvider.writeOutAndReadBack(wb);
         sheet = wb.getSheet("Sheet1");
         assertEquals(4, sheet.getLastRowNum());

         // Verify comments are in the position expected after the shift
         assertNotNull(sheet.getCellComment(0,0));
         assertNull(sheet.getCellComment(1,0));
         assertNull(sheet.getCellComment(2,0));
         assertNotNull(sheet.getCellComment(3,0));
         assertNotNull(sheet.getCellComment(4,0));

         comment1_shifted = sheet.getCellComment(0,0).getString().getString();
         assertEquals(comment1,comment1_shifted);
         comment3_shifted = sheet.getCellComment(3,0).getString().getString();
         assertEquals(comment3,comment3_shifted);
         comment4_shifted = sheet.getCellComment(4,0).getString().getString();
         assertEquals(comment4,comment4_shifted);

         // Shifting back up again, now two rows
         sheet.shiftRows(2, sheet.getLastRowNum(), -2, true, true);

         // TODO: it seems HSSFSheet does not correctly remove comments from rows that are overwritten
         // by shifting rows...
         if(!(wb instanceof HSSFWorkbook)) {
         	assertEquals(2, sheet.getLastRowNum());

         	// Verify comments are in the position expected
         	assertNull("Had: " + (sheet.getCellComment(0,0) == null ? "null" : sheet.getCellComment(0,0).getString()),
         			sheet.getCellComment(0,0));
         	assertNotNull(sheet.getCellComment(1,0));
         	assertNotNull(sheet.getCellComment(2,0));
         }

         comment1 = sheet.getCellComment(1,0).getString().getString();
         assertEquals(comment1,"comment top row3 (index2)\n");
         String comment2 = sheet.getCellComment(2,0).getString().getString();
         assertEquals(comment2,"comment top row4 (index3)\n");
     }

     public final void testShiftWithNames() {
         Workbook wb = _testDataProvider.createWorkbook();
         Sheet sheet1 = wb.createSheet("Sheet1");
         wb.createSheet("Sheet2");
         Row row = sheet1.createRow(0);
         row.createCell(0).setCellValue(1.1);
         row.createCell(1).setCellValue(2.2);

         Name name1 = wb.createName();
         name1.setNameName("name1");
         name1.setRefersToFormula("Sheet1!$A$1+Sheet1!$B$1");

         Name name2 = wb.createName();
         name2.setNameName("name2");
         name2.setRefersToFormula("Sheet1!$A$1");

         //refers to A1 but on Sheet2. Should stay unaffected.
         Name name3 = wb.createName();
         name3.setNameName("name3");
         name3.setRefersToFormula("Sheet2!$A$1");

         //The scope of this one is Sheet2. Should stay unaffected.
         Name name4 = wb.createName();
         name4.setNameName("name4");
         name4.setRefersToFormula("A1");
         name4.setSheetIndex(1);

         sheet1.shiftRows(0, 1, 2);  //shift down the top row on Sheet1.
         name1 = wb.getNameAt(0);
         assertEquals("Sheet1!$A$3+Sheet1!$B$3", name1.getRefersToFormula());

         name2 = wb.getNameAt(1);
         assertEquals("Sheet1!$A$3", name2.getRefersToFormula());

         //name3 and name4 refer to Sheet2 and should not be affected
         name3 = wb.getNameAt(2);
         assertEquals("Sheet2!$A$1", name3.getRefersToFormula());

         name4 = wb.getNameAt(3);
         assertEquals("A1", name4.getRefersToFormula());
     }

     public final void testShiftWithMergedRegions() {
         Workbook wb = _testDataProvider.createWorkbook();
         Sheet sheet	= wb.createSheet();
         Row row = sheet.createRow(0);
         row.createCell(0).setCellValue(1.1);
         row.createCell(1).setCellValue(2.2);
         CellRangeAddress region = new CellRangeAddress(0, 0, 0, 2);
         assertEquals("A1:C1", region.formatAsString());

         sheet.addMergedRegion(region);

         sheet.shiftRows(0, 1, 2);
         region = sheet.getMergedRegion(0);
         assertEquals("A3:C3", region.formatAsString());
    }

     /**
      * See bug #34023
      */
     public final void testShiftWithFormulas() {
         Workbook wb = _testDataProvider.openSampleWorkbook("ForShifting." + _testDataProvider.getStandardFileNameExtension());

         Sheet sheet = wb.getSheet("Sheet1");
         assertEquals(20, sheet.getLastRowNum());

         confirmRow(sheet, 0, 1, 171, 1, "ROW(D1)", "100+B1", "COUNT(D1:E1)");
         confirmRow(sheet, 1, 2, 172, 1, "ROW(D2)", "100+B2", "COUNT(D2:E2)");
         confirmRow(sheet, 2, 3, 173, 1, "ROW(D3)", "100+B3", "COUNT(D3:E3)");

         confirmCell(sheet, 6, 1, 271, "200+B1");
         confirmCell(sheet, 7, 1, 272, "200+B2");
         confirmCell(sheet, 8, 1, 273, "200+B3");

         confirmCell(sheet, 14, 0, 0.0, "A12"); // the cell referred to by this formula will be replaced

         // -----------
         // Row index 1 -> 11 (row "2" -> row "12")
         sheet.shiftRows(1, 1, 10);

         // Now check what sheet looks like after move

         // no changes on row "1"
         confirmRow(sheet, 0, 1, 171, 1, "ROW(D1)", "100+B1", "COUNT(D1:E1)");

         // row "2" is now empty
         confirmEmptyRow(sheet, 1);

         // Row "2" moved to row "12", and the formula has been updated.
         // note however that the cached formula result (2) has not been updated. (POI differs from Excel here)
         confirmRow(sheet, 11, 2, 172, 1, "ROW(D12)", "100+B12", "COUNT(D12:E12)");

         // no changes on row "3"
         confirmRow(sheet, 2, 3, 173, 1, "ROW(D3)", "100+B3", "COUNT(D3:E3)");


         confirmCell(sheet, 14, 0, 0.0, "#REF!");


         // Formulas on rows that weren't shifted:
         confirmCell(sheet, 6, 1, 271, "200+B1");
         confirmCell(sheet, 7, 1, 272, "200+B12"); // this one moved
         confirmCell(sheet, 8, 1, 273, "200+B3");

         // check formulas on other sheets
         Sheet sheet2 = wb.getSheet("Sheet2");
         confirmCell(sheet2,  0, 0, 371, "300+Sheet1!B1");
         confirmCell(sheet2,  1, 0, 372, "300+Sheet1!B12");
         confirmCell(sheet2,  2, 0, 373, "300+Sheet1!B3");

         confirmCell(sheet2, 11, 0, 300, "300+Sheet1!#REF!");


         // Note - named ranges formulas have not been updated
     }

     private static void confirmRow(Sheet sheet, int rowIx, double valA, double valB, double valC,
                 String formulaA, String formulaB, String formulaC) {
         confirmCell(sheet, rowIx, 4, valA, formulaA);
         confirmCell(sheet, rowIx, 5, valB, formulaB);
         confirmCell(sheet, rowIx, 6, valC, formulaC);
     }

     private static void confirmCell(Sheet sheet, int rowIx, int colIx,
             double expectedValue, String expectedFormula) {
         Cell cell = sheet.getRow(rowIx).getCell(colIx);
         assertEquals(expectedValue, cell.getNumericCellValue(), 0.0);
         assertEquals(expectedFormula, cell.getCellFormula());
     }

     public final void testShiftSharedFormulasBug54206() {
         Workbook wb = _testDataProvider.openSampleWorkbook("54206." + _testDataProvider.getStandardFileNameExtension());

         Sheet sheet = wb.getSheetAt(0);
         assertEquals("SUMIF($B$19:$B$82,$B4,G$19:G$82)", sheet.getRow(3).getCell(6).getCellFormula());
         assertEquals("SUMIF($B$19:$B$82,$B4,H$19:H$82)", sheet.getRow(3).getCell(7).getCellFormula());
         assertEquals("SUMIF($B$19:$B$82,$B4,I$19:I$82)", sheet.getRow(3).getCell(8).getCellFormula());

         assertEquals("SUMIF($B$19:$B$82,$B15,G$19:G$82)", sheet.getRow(14).getCell(6).getCellFormula());
         assertEquals("SUMIF($B$19:$B$82,$B15,H$19:H$82)", sheet.getRow(14).getCell(7).getCellFormula());
         assertEquals("SUMIF($B$19:$B$82,$B15,I$19:I$82)", sheet.getRow(14).getCell(8).getCellFormula());

         // now the whole block G4L:15
         for(int i = 3; i <= 14; i++){
             for(int j = 6; j <= 8; j++){
                 String col = CellReference.convertNumToColString(j);
                 String expectedFormula = "SUMIF($B$19:$B$82,$B"+(i+1)+","+col+"$19:"+col+"$82)";
                 assertEquals(expectedFormula, sheet.getRow(i).getCell(j).getCellFormula());
             }
         }

         assertEquals("SUM(G24:I24)", sheet.getRow(23).getCell(9).getCellFormula());
         assertEquals("SUM(G25:I25)", sheet.getRow(24).getCell(9).getCellFormula());
         assertEquals("SUM(G26:I26)", sheet.getRow(25).getCell(9).getCellFormula());

         sheet.shiftRows(24, sheet.getLastRowNum(), 4, true, false);

         assertEquals("SUMIF($B$19:$B$86,$B4,G$19:G$86)", sheet.getRow(3).getCell(6).getCellFormula());
         assertEquals("SUMIF($B$19:$B$86,$B4,H$19:H$86)", sheet.getRow(3).getCell(7).getCellFormula());
         assertEquals("SUMIF($B$19:$B$86,$B4,I$19:I$86)", sheet.getRow(3).getCell(8).getCellFormula());

         assertEquals("SUMIF($B$19:$B$86,$B15,G$19:G$86)", sheet.getRow(14).getCell(6).getCellFormula());
         assertEquals("SUMIF($B$19:$B$86,$B15,H$19:H$86)", sheet.getRow(14).getCell(7).getCellFormula());
         assertEquals("SUMIF($B$19:$B$86,$B15,I$19:I$86)", sheet.getRow(14).getCell(8).getCellFormula());

         // now the whole block G4L:15
         for(int i = 3; i <= 14; i++){
             for(int j = 6; j <= 8; j++){
                 String col = CellReference.convertNumToColString(j);
                 String expectedFormula = "SUMIF($B$19:$B$86,$B"+(i+1)+","+col+"$19:"+col+"$86)";
                 assertEquals(expectedFormula, sheet.getRow(i).getCell(j).getCellFormula());
             }
         }

         assertEquals("SUM(G24:I24)", sheet.getRow(23).getCell(9).getCellFormula());

         // shifted rows
         assertTrue( sheet.getRow(24) == null || sheet.getRow(24).getCell(9) == null);
         assertTrue( sheet.getRow(25) == null || sheet.getRow(25).getCell(9) == null);
         assertTrue( sheet.getRow(26) == null || sheet.getRow(26).getCell(9) == null);
         assertTrue( sheet.getRow(27) == null || sheet.getRow(27).getCell(9) == null);

         assertEquals("SUM(G29:I29)", sheet.getRow(28).getCell(9).getCellFormula());
         assertEquals("SUM(G30:I30)", sheet.getRow(29).getCell(9).getCellFormula());
     }

 	public void testBug55280() throws IOException {
         Workbook w = _testDataProvider.createWorkbook();
         try {
             Sheet s = w.createSheet();
             for (int row = 0; row < 5000; ++row)
                 s.addMergedRegion(new CellRangeAddress(row, row, 0, 3));

             s.shiftRows(0, 4999, 1);        // takes a long time...
         } finally {
             w.close();
         }
 	}

     public void test47169() throws IOException {
         Workbook wb = _testDataProvider.createWorkbook();
         Sheet sheet = wb.createSheet();
         sheet.createRow(30);
         sheet.shiftRows(29, 29, 1, true, true);
         sheet.createRow(30);

         wb.close();
     }
 }
	/* ====================================================================
	Licensed to the Apache Software Foundation (ASF) under one or more
	contributor license agreements. See the NOTICE file distributed with
	this work for additional information regarding copyright ownership.
	The ASF licenses this file to You under the Apache License, Version 2.0
	(the "License"); you may not use this file except in compliance with
	the License. You may obtain a copy of the License at

	http://www.apache.org/licenses/LICENSE-2.0

	Unless required by applicable law or agreed to in writing, software
	distributed under the License is distributed on an "AS IS" BASIS,
	WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
	See the License for the specific language governing permissions and
	limitations under the License.
	==================================================================== */

	package org.apache.poi.ss.usermodel;

	import java.io.IOException;

	import junit.framework.TestCase;

	import org.apache.poi.hssf.usermodel.HSSFWorkbook;
	import org.apache.poi.ss.ITestDataProvider;
	import org.apache.poi.ss.util.CellRangeAddress;
	import org.apache.poi.ss.util.CellReference;

	/**
	* Tests row shifting capabilities.
	*
	* @author Shawn Laubach (slaubach at apache dot com)
	* @author Toshiaki Kamoshida (kamoshida.toshiaki at future dot co dot jp)
	*/
	public abstract class BaseTestSheetShiftRows extends TestCase {

	private final ITestDataProvider _testDataProvider;

	protected BaseTestSheetShiftRows(ITestDataProvider testDataProvider) {
	_testDataProvider = testDataProvider;
	}

	/**
	* Tests the shiftRows function. Does three different shifts.
	* After each shift, writes the workbook to file and reads back to
	* check. This ensures that if some changes code that breaks
	* writing or what not, they realize it.
	*/
	public final void testShiftRows(){
	// Read initial file in
	String sampleName = "SimpleMultiCell." + _testDataProvider.getStandardFileNameExtension();
	Workbook wb = _testDataProvider.openSampleWorkbook(sampleName);
	Sheet s = wb.getSheetAt( 0 );

	// Shift the second row down 1 and write to temp file
	s.shiftRows( 1, 1, 1 );

	wb = _testDataProvider.writeOutAndReadBack(wb);

	// Read from temp file and check the number of cells in each
	// row (in original file each row was unique)
	s = wb.getSheetAt( 0 );

	assertEquals(s.getRow(0).getPhysicalNumberOfCells(), 1);
	confirmEmptyRow(s, 1);
	assertEquals(s.getRow(2).getPhysicalNumberOfCells(), 2);
	assertEquals(s.getRow(3).getPhysicalNumberOfCells(), 4);
	assertEquals(s.getRow(4).getPhysicalNumberOfCells(), 5);

	// Shift rows 1-3 down 3 in the current one. This tests when
	// 1 row is blank. Write to a another temp file
	s.shiftRows( 0, 2, 3 );
	wb = _testDataProvider.writeOutAndReadBack(wb);

	// Read and ensure things are where they should be
	s = wb.getSheetAt(0);
	confirmEmptyRow(s, 0);
	confirmEmptyRow(s, 1);
	confirmEmptyRow(s, 2);
	assertEquals(s.getRow(3).getPhysicalNumberOfCells(), 1);
	confirmEmptyRow(s, 4);
	assertEquals(s.getRow(5).getPhysicalNumberOfCells(), 2);

	// Read the first file again
	wb = _testDataProvider.openSampleWorkbook(sampleName);
	s = wb.getSheetAt( 0 );

	// Shift rows 3 and 4 up and write to temp file
	s.shiftRows( 2, 3, -2 );
	wb = _testDataProvider.writeOutAndReadBack(wb);
	s = wb.getSheetAt( 0 );
	assertEquals(s.getRow(0).getPhysicalNumberOfCells(), 3);
	assertEquals(s.getRow(1).getPhysicalNumberOfCells(), 4);
	confirmEmptyRow(s, 2);
	confirmEmptyRow(s, 3);
	assertEquals(s.getRow(4).getPhysicalNumberOfCells(), 5);
	}
	private static void confirmEmptyRow(Sheet s, int rowIx) {
	Row row = s.getRow(rowIx);
	assertTrue(row == null \|\| row.getPhysicalNumberOfCells() == 0);
	}

	/**
	* Tests when rows are null.
	*/
	public final void testShiftRow() {
	Workbook b = _testDataProvider.createWorkbook();
	Sheet s = b.createSheet();
	s.createRow(0).createCell(0).setCellValue("TEST1");
	s.createRow(3).createCell(0).setCellValue("TEST2");
	s.shiftRows(0,4,1);
	}

	/**
	* Tests when shifting the first row.
	*/
	public final void testActiveCell() {
	Workbook b = _testDataProvider.createWorkbook();
	Sheet s = b.createSheet();

	s.createRow(0).createCell(0).setCellValue("TEST1");
	s.createRow(3).createCell(0).setCellValue("TEST2");
	s.shiftRows(0,4,1);
	}

	/**
	* When shifting rows, the page breaks should go with it
	*/
	public void testShiftRowBreaks() { // TODO - enable XSSF test
	Workbook b = _testDataProvider.createWorkbook();
	Sheet s = b.createSheet();
	Row row = s.createRow(4);
	row.createCell(0).setCellValue("test");
	s.setRowBreak(4);

	s.shiftRows(4, 4, 2);
	assertTrue("Row number 6 should have a pagebreak", s.isRowBroken(6));
	}

	public void testShiftWithComments() {
	Workbook wb = _testDataProvider.openSampleWorkbook("comments." + _testDataProvider.getStandardFileNameExtension());

	Sheet sheet = wb.getSheet("Sheet1");
	assertEquals(3, sheet.getLastRowNum());

	// Verify comments are in the position expected
	assertNotNull(sheet.getCellComment(0,0));
	assertNull(sheet.getCellComment(1,0));
	assertNotNull(sheet.getCellComment(2,0));
	assertNotNull(sheet.getCellComment(3,0));

	String comment1 = sheet.getCellComment(0,0).getString().getString();
	assertEquals(comment1,"comment top row1 (index0)\n");
	String comment3 = sheet.getCellComment(2,0).getString().getString();
	assertEquals(comment3,"comment top row3 (index2)\n");
	String comment4 = sheet.getCellComment(3,0).getString().getString();
	assertEquals(comment4,"comment top row4 (index3)\n");

	//Workbook wbBack = _testDataProvider.writeOutAndReadBack(wb);

	// Shifting all but first line down to test comments shifting
	sheet.shiftRows(1, sheet.getLastRowNum(), 1, true, true);

	// Test that comments were shifted as expected
	assertEquals(4, sheet.getLastRowNum());
	assertNotNull(sheet.getCellComment(0,0));
	assertNull(sheet.getCellComment(1,0));
	assertNull(sheet.getCellComment(2,0));
	assertNotNull(sheet.getCellComment(3,0));
	assertNotNull(sheet.getCellComment(4,0));

	String comment1_shifted = sheet.getCellComment(0,0).getString().getString();
	assertEquals(comment1,comment1_shifted);
	String comment3_shifted = sheet.getCellComment(3,0).getString().getString();
	assertEquals(comment3,comment3_shifted);
	String comment4_shifted = sheet.getCellComment(4,0).getString().getString();
	assertEquals(comment4,comment4_shifted);

	// Write out and read back in again
	// Ensure that the changes were persisted
	wb = _testDataProvider.writeOutAndReadBack(wb);
	sheet = wb.getSheet("Sheet1");
	assertEquals(4, sheet.getLastRowNum());

	// Verify comments are in the position expected after the shift
	assertNotNull(sheet.getCellComment(0,0));
	assertNull(sheet.getCellComment(1,0));
	assertNull(sheet.getCellComment(2,0));
	assertNotNull(sheet.getCellComment(3,0));
	assertNotNull(sheet.getCellComment(4,0));

	comment1_shifted = sheet.getCellComment(0,0).getString().getString();
	assertEquals(comment1,comment1_shifted);
	comment3_shifted = sheet.getCellComment(3,0).getString().getString();
	assertEquals(comment3,comment3_shifted);
	comment4_shifted = sheet.getCellComment(4,0).getString().getString();
	assertEquals(comment4,comment4_shifted);

	// Shifting back up again, now two rows
	sheet.shiftRows(2, sheet.getLastRowNum(), -2, true, true);

	// TODO: it seems HSSFSheet does not correctly remove comments from rows that are overwritten
	// by shifting rows...
	if(!(wb instanceof HSSFWorkbook)) {
	assertEquals(2, sheet.getLastRowNum());

	// Verify comments are in the position expected
	assertNull("Had: " + (sheet.getCellComment(0,0) == null ? "null" : sheet.getCellComment(0,0).getString()),
	sheet.getCellComment(0,0));
	assertNotNull(sheet.getCellComment(1,0));
	assertNotNull(sheet.getCellComment(2,0));
	}

	comment1 = sheet.getCellComment(1,0).getString().getString();
	assertEquals(comment1,"comment top row3 (index2)\n");
	String comment2 = sheet.getCellComment(2,0).getString().getString();
	assertEquals(comment2,"comment top row4 (index3)\n");
	}

	public final void testShiftWithNames() {
	Workbook wb = _testDataProvider.createWorkbook();
	Sheet sheet1 = wb.createSheet("Sheet1");
	wb.createSheet("Sheet2");
	Row row = sheet1.createRow(0);
	row.createCell(0).setCellValue(1.1);
	row.createCell(1).setCellValue(2.2);

	Name name1 = wb.createName();
	name1.setNameName("name1");
	name1.setRefersToFormula("Sheet1!$A$1+Sheet1!$B$1");

	Name name2 = wb.createName();
	name2.setNameName("name2");
	name2.setRefersToFormula("Sheet1!$A$1");

	//refers to A1 but on Sheet2. Should stay unaffected.
	Name name3 = wb.createName();
	name3.setNameName("name3");
	name3.setRefersToFormula("Sheet2!$A$1");

	//The scope of this one is Sheet2. Should stay unaffected.
	Name name4 = wb.createName();
	name4.setNameName("name4");
	name4.setRefersToFormula("A1");
	name4.setSheetIndex(1);

	sheet1.shiftRows(0, 1, 2); //shift down the top row on Sheet1.
	name1 = wb.getNameAt(0);
	assertEquals("Sheet1!$A$3+Sheet1!$B$3", name1.getRefersToFormula());

	name2 = wb.getNameAt(1);
	assertEquals("Sheet1!$A$3", name2.getRefersToFormula());

	//name3 and name4 refer to Sheet2 and should not be affected
	name3 = wb.getNameAt(2);
	assertEquals("Sheet2!$A$1", name3.getRefersToFormula());

	name4 = wb.getNameAt(3);
	assertEquals("A1", name4.getRefersToFormula());
	}

	public final void testShiftWithMergedRegions() {
	Workbook wb = _testDataProvider.createWorkbook();
	Sheet sheet = wb.createSheet();
	Row row = sheet.createRow(0);
	row.createCell(0).setCellValue(1.1);
	row.createCell(1).setCellValue(2.2);
	CellRangeAddress region = new CellRangeAddress(0, 0, 0, 2);
	assertEquals("A1:C1", region.formatAsString());

	sheet.addMergedRegion(region);

	sheet.shiftRows(0, 1, 2);
	region = sheet.getMergedRegion(0);
	assertEquals("A3:C3", region.formatAsString());
	}

	/**
	* See bug #34023
	*/
	public final void testShiftWithFormulas() {
	Workbook wb = _testDataProvider.openSampleWorkbook("ForShifting." + _testDataProvider.getStandardFileNameExtension());

	Sheet sheet = wb.getSheet("Sheet1");
	assertEquals(20, sheet.getLastRowNum());

	confirmRow(sheet, 0, 1, 171, 1, "ROW(D1)", "100+B1", "COUNT(D1:E1)");
	confirmRow(sheet, 1, 2, 172, 1, "ROW(D2)", "100+B2", "COUNT(D2:E2)");
	confirmRow(sheet, 2, 3, 173, 1, "ROW(D3)", "100+B3", "COUNT(D3:E3)");

	confirmCell(sheet, 6, 1, 271, "200+B1");
	confirmCell(sheet, 7, 1, 272, "200+B2");
	confirmCell(sheet, 8, 1, 273, "200+B3");

	confirmCell(sheet, 14, 0, 0.0, "A12"); // the cell referred to by this formula will be replaced

	// -----------
	// Row index 1 -> 11 (row "2" -> row "12")
	sheet.shiftRows(1, 1, 10);

	// Now check what sheet looks like after move

	// no changes on row "1"
	confirmRow(sheet, 0, 1, 171, 1, "ROW(D1)", "100+B1", "COUNT(D1:E1)");

	// row "2" is now empty
	confirmEmptyRow(sheet, 1);

	// Row "2" moved to row "12", and the formula has been updated.
	// note however that the cached formula result (2) has not been updated. (POI differs from Excel here)
	confirmRow(sheet, 11, 2, 172, 1, "ROW(D12)", "100+B12", "COUNT(D12:E12)");

	// no changes on row "3"
	confirmRow(sheet, 2, 3, 173, 1, "ROW(D3)", "100+B3", "COUNT(D3:E3)");


	confirmCell(sheet, 14, 0, 0.0, "#REF!");


	// Formulas on rows that weren't shifted:
	confirmCell(sheet, 6, 1, 271, "200+B1");
	confirmCell(sheet, 7, 1, 272, "200+B12"); // this one moved
	confirmCell(sheet, 8, 1, 273, "200+B3");

	// check formulas on other sheets
	Sheet sheet2 = wb.getSheet("Sheet2");
	confirmCell(sheet2, 0, 0, 371, "300+Sheet1!B1");
	confirmCell(sheet2, 1, 0, 372, "300+Sheet1!B12");
	confirmCell(sheet2, 2, 0, 373, "300+Sheet1!B3");

	confirmCell(sheet2, 11, 0, 300, "300+Sheet1!#REF!");


	// Note - named ranges formulas have not been updated
	}

	private static void confirmRow(Sheet sheet, int rowIx, double valA, double valB, double valC,
	String formulaA, String formulaB, String formulaC) {
	confirmCell(sheet, rowIx, 4, valA, formulaA);
	confirmCell(sheet, rowIx, 5, valB, formulaB);
	confirmCell(sheet, rowIx, 6, valC, formulaC);
	}

	private static void confirmCell(Sheet sheet, int rowIx, int colIx,
	double expectedValue, String expectedFormula) {
	Cell cell = sheet.getRow(rowIx).getCell(colIx);
	assertEquals(expectedValue, cell.getNumericCellValue(), 0.0);
	assertEquals(expectedFormula, cell.getCellFormula());
	}

	public final void testShiftSharedFormulasBug54206() {
	Workbook wb = _testDataProvider.openSampleWorkbook("54206." + _testDataProvider.getStandardFileNameExtension());

	Sheet sheet = wb.getSheetAt(0);
	assertEquals("SUMIF($B$19:$B$82,$B4,G$19:G$82)", sheet.getRow(3).getCell(6).getCellFormula());
	assertEquals("SUMIF($B$19:$B$82,$B4,H$19:H$82)", sheet.getRow(3).getCell(7).getCellFormula());
	assertEquals("SUMIF($B$19:$B$82,$B4,I$19:I$82)", sheet.getRow(3).getCell(8).getCellFormula());

	assertEquals("SUMIF($B$19:$B$82,$B15,G$19:G$82)", sheet.getRow(14).getCell(6).getCellFormula());
	assertEquals("SUMIF($B$19:$B$82,$B15,H$19:H$82)", sheet.getRow(14).getCell(7).getCellFormula());
	assertEquals("SUMIF($B$19:$B$82,$B15,I$19:I$82)", sheet.getRow(14).getCell(8).getCellFormula());

	// now the whole block G4L:15
	for(int i = 3; i <= 14; i++){
	for(int j = 6; j <= 8; j++){
	String col = CellReference.convertNumToColString(j);
	String expectedFormula = "SUMIF($B$19:$B$82,$B"+(i+1)+","+col+"$19:"+col+"$82)";
	assertEquals(expectedFormula, sheet.getRow(i).getCell(j).getCellFormula());
	}
	}

	assertEquals("SUM(G24:I24)", sheet.getRow(23).getCell(9).getCellFormula());
	assertEquals("SUM(G25:I25)", sheet.getRow(24).getCell(9).getCellFormula());
	assertEquals("SUM(G26:I26)", sheet.getRow(25).getCell(9).getCellFormula());

	sheet.shiftRows(24, sheet.getLastRowNum(), 4, true, false);

	assertEquals("SUMIF($B$19:$B$86,$B4,G$19:G$86)", sheet.getRow(3).getCell(6).getCellFormula());
	assertEquals("SUMIF($B$19:$B$86,$B4,H$19:H$86)", sheet.getRow(3).getCell(7).getCellFormula());
	assertEquals("SUMIF($B$19:$B$86,$B4,I$19:I$86)", sheet.getRow(3).getCell(8).getCellFormula());

	assertEquals("SUMIF($B$19:$B$86,$B15,G$19:G$86)", sheet.getRow(14).getCell(6).getCellFormula());
	assertEquals("SUMIF($B$19:$B$86,$B15,H$19:H$86)", sheet.getRow(14).getCell(7).getCellFormula());
	assertEquals("SUMIF($B$19:$B$86,$B15,I$19:I$86)", sheet.getRow(14).getCell(8).getCellFormula());

	// now the whole block G4L:15
	for(int i = 3; i <= 14; i++){
	for(int j = 6; j <= 8; j++){
	String col = CellReference.convertNumToColString(j);
	String expectedFormula = "SUMIF($B$19:$B$86,$B"+(i+1)+","+col+"$19:"+col+"$86)";
	assertEquals(expectedFormula, sheet.getRow(i).getCell(j).getCellFormula());
	}
	}

	assertEquals("SUM(G24:I24)", sheet.getRow(23).getCell(9).getCellFormula());

	// shifted rows
	assertTrue( sheet.getRow(24) == null \|\| sheet.getRow(24).getCell(9) == null);
	assertTrue( sheet.getRow(25) == null \|\| sheet.getRow(25).getCell(9) == null);
	assertTrue( sheet.getRow(26) == null \|\| sheet.getRow(26).getCell(9) == null);
	assertTrue( sheet.getRow(27) == null \|\| sheet.getRow(27).getCell(9) == null);

	assertEquals("SUM(G29:I29)", sheet.getRow(28).getCell(9).getCellFormula());
	assertEquals("SUM(G30:I30)", sheet.getRow(29).getCell(9).getCellFormula());
	}

	public void testBug55280() throws IOException {
	Workbook w = _testDataProvider.createWorkbook();
	try {
	Sheet s = w.createSheet();
	for (int row = 0; row < 5000; ++row)
	s.addMergedRegion(new CellRangeAddress(row, row, 0, 3));

	s.shiftRows(0, 4999, 1); // takes a long time...
	} finally {
	w.close();
	}
	}

	public void test47169() throws IOException {
	Workbook wb = _testDataProvider.createWorkbook();
	Sheet sheet = wb.createSheet();
	sheet.createRow(30);
	sheet.shiftRows(29, 29, 1, true, true);
	sheet.createRow(30);

	wb.close();
	}
	}