refactored XSSFSheet.shiftRows to use FormulaShifter, use a common test superclass for both hssf and xssf

git-svn-id: https://svn.apache.org/repos/asf/poi/trunk@744750 13f79535-47bb-0310-9956-ffa450edef68

1 files changed, 276 insertions, 0 deletions

diff --git a/src/testcases/org/apache/poi/ss/usermodel/BaseTestSheetShiftRows.java b/src/testcases/org/apache/poi/ss/usermodel/BaseTestSheetShiftRows.java
new file mode 100755
index 0000000000..7b83cbbd53
--- /dev/null
+++ b/src/testcases/org/apache/poi/ss/usermodel/BaseTestSheetShiftRows.java
@@ -0,0 +1,276 @@
+/* ====================================================================

+   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 junit.framework.TestCase;

+

+/**

+ * 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 {

+    /**

+     * Override to provide HSSF / XSSF specific way for re-serialising a workbook

+     */

+    protected abstract Workbook writeOutAndReadBack(Workbook wb);

+    /**

+     * Override to provide way of loading HSSF / XSSF sample workbooks

+     * @param sampleFileName without the ".xls" or ".xlsx" suffix

+     */

+    protected abstract Workbook openSampleWorkbook(String sampleFileName);

+    /**

+     * Override to provide way of creating HSSF / XSSF workbooks

+     */

+    protected abstract Workbook createWorkbook();

+

+    /**

+     * 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.

+     *

+     * @author Shawn Laubach (slaubach at apache dot org)

+     */

+    public final void baseTestShiftRows(String sampleName){

+        // Read initial file in

+        Workbook wb = openSampleWorkbook(sampleName);

+        Sheet s = wb.getSheetAt( 0 );

+

+        // Shift the second row down 1 and write to temp file

+        s.shiftRows( 1, 1, 1 );

+

+        wb = 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 = 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 = openSampleWorkbook(sampleName);

+        s = wb.getSheetAt( 0 );

+

+        // Shift rows 3 and 4 up and write to temp file

+        s.shiftRows( 2, 3, -2 );

+        wb = 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.

+     *

+     * @author Toshiaki Kamoshida (kamoshida.toshiaki at future dot co dot jp)

+     */

+    public final void baseTestShiftRow() {

+        Workbook b = 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.

+     *

+     * @author Toshiaki Kamoshida (kamoshida.toshiaki at future dot co dot jp)

+     */

+    public final void baseTestShiftRow0() {

+        Workbook b = 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 final void baseTestShiftRowBreaks() {

+        Workbook b = 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 final void baseTestShiftWithComments(String sampleName) {

+        Workbook wb = openSampleWorkbook(sampleName);

+

+        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");

+

+        // 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 = 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);

+    }

+

+    /**

+     * See bug #34023

+     */

+    public void baseTestShiftWithFormulas(String sampleName) {

+        Workbook wb = openSampleWorkbook(sampleName);

+

+        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());

+    }

+}