You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@poi.apache.org by ki...@apache.org on 2021/08/28 23:48:49 UTC
svn commit: r1892683 [4/4] - in /poi/trunk: ./ poi-examples/ poi-excelant/
poi-integration/ poi-integration/src/test/java/org/apache/poi/stress/
poi-ooxml-lite/ poi-ooxml/src/main/java/org/apache/poi/poifs/crypt/dsig/
poi-ooxml/src/main/java/org/apache...
Modified: poi/trunk/poi/src/test/java/org/apache/poi/ss/usermodel/BaseTestSheetShiftRows.java
URL: http://svn.apache.org/viewvc/poi/trunk/poi/src/test/java/org/apache/poi/ss/usermodel/BaseTestSheetShiftRows.java?rev=1892683&r1=1892682&r2=1892683&view=diff
==============================================================================
--- poi/trunk/poi/src/test/java/org/apache/poi/ss/usermodel/BaseTestSheetShiftRows.java (original)
+++ poi/trunk/poi/src/test/java/org/apache/poi/ss/usermodel/BaseTestSheetShiftRows.java Sat Aug 28 23:48:48 2021
@@ -17,8 +17,6 @@
package org.apache.poi.ss.usermodel;
-import static org.apache.poi.POITestCase.skipTest;
-import static org.apache.poi.POITestCase.testPassesNow;
import static org.junit.jupiter.api.Assertions.assertDoesNotThrow;
import static org.junit.jupiter.api.Assertions.assertEquals;
import static org.junit.jupiter.api.Assertions.assertNotEquals;
@@ -60,57 +58,55 @@ public abstract class BaseTestSheetShift
public final void testShiftRows() throws IOException {
// Read initial file in
String sampleName = "SimpleMultiCell." + _testDataProvider.getStandardFileNameExtension();
- Workbook wb1 = _testDataProvider.openSampleWorkbook(sampleName);
- Sheet s = wb1.getSheetAt( 0 );
+ try (Workbook wb1 = _testDataProvider.openSampleWorkbook(sampleName)) {
+ Sheet s = wb1.getSheetAt(0);
- // Shift the second row down 1 and write to temp file
- s.shiftRows( 1, 1, 1 );
+ // Shift the second row down 1 and write to temp file
+ s.shiftRows(1, 1, 1);
- Workbook wb2 = _testDataProvider.writeOutAndReadBack(wb1);
- wb1.close();
+ try (Workbook wb2 = _testDataProvider.writeOutAndReadBack(wb1)) {
- // Read from temp file and check the number of cells in each
- // row (in original file each row was unique)
- s = wb2.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 );
- Workbook wb3 = _testDataProvider.writeOutAndReadBack(wb2);
- wb2.close();
-
- // Read and ensure things are where they should be
- s = wb3.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);
-
- wb3.close();
+ // Read from temp file and check the number of cells in each
+ // row (in original file each row was unique)
+ s = wb2.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);
+ try (Workbook wb3 = _testDataProvider.writeOutAndReadBack(wb2)) {
+ // Read and ensure things are where they should be
+ s = wb3.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
- Workbook wb4 = _testDataProvider.openSampleWorkbook(sampleName);
- s = wb4.getSheetAt( 0 );
+ try (Workbook wb4 = _testDataProvider.openSampleWorkbook(sampleName)) {
+ Sheet s = wb4.getSheetAt(0);
- // Shift rows 3 and 4 up and write to temp file
- s.shiftRows( 2, 3, -2 );
- Workbook wb5 = _testDataProvider.writeOutAndReadBack(wb4);
- wb4.close();
- s = wb5.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);
- wb5.close();
+ // Shift rows 3 and 4 up and write to temp file
+ s.shiftRows(2, 3, -2);
+ try (Workbook wb5 = _testDataProvider.writeOutAndReadBack(wb4)) {
+ s = wb5.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);
@@ -149,195 +145,188 @@ public abstract class BaseTestSheetShift
@Test
void testShiftWithComments() throws IOException {
- Workbook wb1 = _testDataProvider.openSampleWorkbook("comments." + _testDataProvider.getStandardFileNameExtension());
+ try (Workbook wb1 = _testDataProvider.openSampleWorkbook("comments." + _testDataProvider.getStandardFileNameExtension())) {
- Sheet sheet = wb1.getSheet("Sheet1");
- assertEquals(3, sheet.getLastRowNum());
-
- // Verify comments are in the position expected
- assertNotNull(sheet.getCellComment(new CellAddress(0,0)));
- assertNull(sheet.getCellComment(new CellAddress(1,0)));
- assertNotNull(sheet.getCellComment(new CellAddress(2,0)));
- assertNotNull(sheet.getCellComment(new CellAddress(3,0)));
-
- String comment1 = sheet.getCellComment(new CellAddress(0,0)).getString().getString();
- assertEquals(comment1,"comment top row1 (index0)\n");
- String comment3 = sheet.getCellComment(new CellAddress(2,0)).getString().getString();
- assertEquals(comment3,"comment top row3 (index2)\n");
- String comment4 = sheet.getCellComment(new CellAddress(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(new CellAddress(0,0)));
- assertNull(sheet.getCellComment(new CellAddress(1,0)));
- assertNull(sheet.getCellComment(new CellAddress(2,0)));
- assertNotNull(sheet.getCellComment(new CellAddress(3,0)));
- assertNotNull(sheet.getCellComment(new CellAddress(4,0)));
-
- String comment1_shifted = sheet.getCellComment(new CellAddress(0,0)).getString().getString();
- assertEquals(comment1,comment1_shifted);
- String comment3_shifted = sheet.getCellComment(new CellAddress(3,0)).getString().getString();
- assertEquals(comment3,comment3_shifted);
- String comment4_shifted = sheet.getCellComment(new CellAddress(4,0)).getString().getString();
- assertEquals(comment4,comment4_shifted);
-
- // Write out and read back in again
- // Ensure that the changes were persisted
- Workbook wb2 = _testDataProvider.writeOutAndReadBack(wb1);
- wb1.close();
-
- sheet = wb2.getSheet("Sheet1");
- assertEquals(4, sheet.getLastRowNum());
-
- // Verify comments are in the position expected after the shift
- assertNotNull(sheet.getCellComment(new CellAddress(0,0)));
- assertNull(sheet.getCellComment(new CellAddress(1,0)));
- assertNull(sheet.getCellComment(new CellAddress(2,0)));
- assertNotNull(sheet.getCellComment(new CellAddress(3,0)));
- assertNotNull(sheet.getCellComment(new CellAddress(4,0)));
-
- comment1_shifted = sheet.getCellComment(new CellAddress(0,0)).getString().getString();
- assertEquals(comment1,comment1_shifted);
- comment3_shifted = sheet.getCellComment(new CellAddress(3,0)).getString().getString();
- assertEquals(comment3,comment3_shifted);
- comment4_shifted = sheet.getCellComment(new CellAddress(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(!(wb2 instanceof HSSFWorkbook)) {
- assertEquals(2, sheet.getLastRowNum());
+ Sheet sheet = wb1.getSheet("Sheet1");
+ assertEquals(3, sheet.getLastRowNum());
// Verify comments are in the position expected
- assertNull(sheet.getCellComment(new CellAddress(0,0)),
- "Had: " + (sheet.getCellComment(new CellAddress(0,0)) == null ? "null" : sheet.getCellComment(new CellAddress(0,0)).getString()));
- assertNotNull(sheet.getCellComment(new CellAddress(1,0)));
- assertNotNull(sheet.getCellComment(new CellAddress(2,0)));
+ assertNotNull(sheet.getCellComment(new CellAddress(0, 0)));
+ assertNull(sheet.getCellComment(new CellAddress(1, 0)));
+ assertNotNull(sheet.getCellComment(new CellAddress(2, 0)));
+ assertNotNull(sheet.getCellComment(new CellAddress(3, 0)));
+
+ String comment1 = sheet.getCellComment(new CellAddress(0, 0)).getString().getString();
+ assertEquals(comment1, "comment top row1 (index0)\n");
+ String comment3 = sheet.getCellComment(new CellAddress(2, 0)).getString().getString();
+ assertEquals(comment3, "comment top row3 (index2)\n");
+ String comment4 = sheet.getCellComment(new CellAddress(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(new CellAddress(0, 0)));
+ assertNull(sheet.getCellComment(new CellAddress(1, 0)));
+ assertNull(sheet.getCellComment(new CellAddress(2, 0)));
+ assertNotNull(sheet.getCellComment(new CellAddress(3, 0)));
+ assertNotNull(sheet.getCellComment(new CellAddress(4, 0)));
+
+ String comment1_shifted = sheet.getCellComment(new CellAddress(0, 0)).getString().getString();
+ assertEquals(comment1, comment1_shifted);
+ String comment3_shifted = sheet.getCellComment(new CellAddress(3, 0)).getString().getString();
+ assertEquals(comment3, comment3_shifted);
+ String comment4_shifted = sheet.getCellComment(new CellAddress(4, 0)).getString().getString();
+ assertEquals(comment4, comment4_shifted);
+
+ // Write out and read back in again
+ // Ensure that the changes were persisted
+ try (Workbook wb2 = _testDataProvider.writeOutAndReadBack(wb1)) {
+
+ sheet = wb2.getSheet("Sheet1");
+ assertEquals(4, sheet.getLastRowNum());
+
+ // Verify comments are in the position expected after the shift
+ assertNotNull(sheet.getCellComment(new CellAddress(0, 0)));
+ assertNull(sheet.getCellComment(new CellAddress(1, 0)));
+ assertNull(sheet.getCellComment(new CellAddress(2, 0)));
+ assertNotNull(sheet.getCellComment(new CellAddress(3, 0)));
+ assertNotNull(sheet.getCellComment(new CellAddress(4, 0)));
+
+ comment1_shifted = sheet.getCellComment(new CellAddress(0, 0)).getString().getString();
+ assertEquals(comment1, comment1_shifted);
+ comment3_shifted = sheet.getCellComment(new CellAddress(3, 0)).getString().getString();
+ assertEquals(comment3, comment3_shifted);
+ comment4_shifted = sheet.getCellComment(new CellAddress(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 (!(wb2 instanceof HSSFWorkbook)) {
+ assertEquals(2, sheet.getLastRowNum());
+
+ // Verify comments are in the position expected
+ assertNull(sheet.getCellComment(new CellAddress(0, 0)),
+ "Had: " + (sheet.getCellComment(new CellAddress(0, 0)) == null ? "null" : sheet.getCellComment(new CellAddress(0, 0)).getString()));
+ assertNotNull(sheet.getCellComment(new CellAddress(1, 0)));
+ assertNotNull(sheet.getCellComment(new CellAddress(2, 0)));
+ }
+
+ comment1 = sheet.getCellComment(new CellAddress(1, 0)).getString().getString();
+ assertEquals(comment1, "comment top row3 (index2)\n");
+ String comment2 = sheet.getCellComment(new CellAddress(2, 0)).getString().getString();
+ assertEquals(comment2, "comment top row4 (index3)\n");
+ }
}
-
- comment1 = sheet.getCellComment(new CellAddress(1,0)).getString().getString();
- assertEquals(comment1,"comment top row3 (index2)\n");
- String comment2 = sheet.getCellComment(new CellAddress(2,0)).getString().getString();
- assertEquals(comment2,"comment top row4 (index3)\n");
-
- wb2.close();
}
@Test
public final void testShiftWithNames() throws IOException {
- 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.getName("name1");
- assertEquals("Sheet1!$A$3+Sheet1!$B$3", name1.getRefersToFormula());
-
- name2 = wb.getName("name2");
- assertEquals("Sheet1!$A$3", name2.getRefersToFormula());
-
- //name3 and name4 refer to Sheet2 and should not be affected
- name3 = wb.getName("name3");
- assertEquals("Sheet2!$A$1", name3.getRefersToFormula());
-
- name4 = wb.getName("name4");
- assertEquals("A1", name4.getRefersToFormula());
+ try (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.getName("name1");
+ assertEquals("Sheet1!$A$3+Sheet1!$B$3", name1.getRefersToFormula());
+
+ name2 = wb.getName("name2");
+ assertEquals("Sheet1!$A$3", name2.getRefersToFormula());
+
+ //name3 and name4 refer to Sheet2 and should not be affected
+ name3 = wb.getName("name3");
+ assertEquals("Sheet2!$A$1", name3.getRefersToFormula());
- wb.close();
+ name4 = wb.getName("name4");
+ assertEquals("A1", name4.getRefersToFormula());
+ }
}
@Test
public final void testShiftWithMergedRegions() throws IOException {
- 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());
-
- assertEquals(0, sheet.addMergedRegion(region));
-
- sheet.shiftRows(0, 1, 2);
- region = sheet.getMergedRegion(0);
- assertEquals("A3:C3", region.formatAsString());
- wb.close();
+ try (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());
+
+ assertEquals(0, sheet.addMergedRegion(region));
+
+ sheet.shiftRows(0, 1, 2);
+ region = sheet.getMergedRegion(0);
+ assertEquals("A3:C3", region.formatAsString());
+ }
}
//@Disabled("bug 56454: Incorrectly handles merged regions that do not contain column 0")
@Test
public final void shiftWithMergedRegions_bug56454() throws IOException {
- Workbook wb = _testDataProvider.createWorkbook();
- Sheet sheet = wb.createSheet();
- // populate sheet cells
- for (int i = 0; i < 10; i++) {
- Row row = sheet.createRow(i);
-
- for (int j = 0; j < 10; j++) {
- Cell cell = row.createCell(j, CellType.STRING);
- cell.setCellValue(i + "x" + j);
+ try (Workbook wb = _testDataProvider.createWorkbook()) {
+ Sheet sheet = wb.createSheet();
+ // populate sheet cells
+ for (int i = 0; i < 10; i++) {
+ Row row = sheet.createRow(i);
+
+ for (int j = 0; j < 10; j++) {
+ Cell cell = row.createCell(j, CellType.STRING);
+ cell.setCellValue(i + "x" + j);
+ }
}
- }
- CellRangeAddress A4_B7 = CellRangeAddress.valueOf("A4:B7");
- CellRangeAddress C4_D7 = CellRangeAddress.valueOf("C4:D7");
+ CellRangeAddress A4_B7 = CellRangeAddress.valueOf("A4:B7");
+ CellRangeAddress C4_D7 = CellRangeAddress.valueOf("C4:D7");
- assertEquals(0, sheet.addMergedRegion(A4_B7));
- assertEquals(1, sheet.addMergedRegion(C4_D7));
+ assertEquals(0, sheet.addMergedRegion(A4_B7));
+ assertEquals(1, sheet.addMergedRegion(C4_D7));
- assumeTrue(sheet.getLastRowNum() > 8);
+ assumeTrue(sheet.getLastRowNum() > 8);
- // Insert a row in the middle of both merged regions.
- sheet.shiftRows(4, sheet.getLastRowNum(), 1);
-
- // all regions should still start at row 3, and elongate by 1 row
- List<CellRangeAddress> expectedMergedRegions = new ArrayList<>();
- CellRangeAddress A4_B8 = CellRangeAddress.valueOf("A4:B8"); //A4:B7 should be elongated by 1 row
- CellRangeAddress C4_D8 = CellRangeAddress.valueOf("C4:D8"); //C4:B7 should be elongated by 1 row
- expectedMergedRegions.add(A4_B8);
- expectedMergedRegions.add(C4_D8);
-
- // This test is written as expected-to-fail and should be rewritten
- // as expected-to-pass when the bug is fixed.
- // FIXME: remove try, catch, and testPassesNow, skipTest when test passes
- try {
- assertEquals(expectedMergedRegions, sheet.getMergedRegions());
- testPassesNow(56454);
- } catch (AssertionError e) {
- skipTest(e);
+ // Insert a row in the middle of both merged regions.
+ sheet.shiftRows(4, sheet.getLastRowNum(), 1);
+
+ // all regions should still start at row 3, and elongate by 1 row
+ List<CellRangeAddress> expectedMergedRegions = new ArrayList<>();
+ CellRangeAddress A4_B8 = CellRangeAddress.valueOf("A4:B8"); //A4:B7 should be elongated by 1 row
+ CellRangeAddress C4_D8 = CellRangeAddress.valueOf("C4:D8"); //C4:B7 should be elongated by 1 row
+ expectedMergedRegions.add(A4_B8);
+ expectedMergedRegions.add(C4_D8);
+
+ // This test is written as expected-to-fail and should be rewritten
+ // as expected-to-pass when the bug is fixed.
+ // FIXME: remove try, catch, and testPassesNow, skipTest when test passes
+ assertNotEquals(expectedMergedRegions, sheet.getMergedRegions());
}
- wb.close();
}
@@ -347,60 +336,59 @@ public abstract class BaseTestSheetShift
*/
@Test
public final void testShiftWithFormulas() throws IOException {
- Workbook wb = _testDataProvider.openSampleWorkbook("ForShifting." + _testDataProvider.getStandardFileNameExtension());
-
- Sheet sheet = wb.getSheet("Sheet1");
- assertEquals(20, sheet.getLastRowNum());
+ try (Workbook wb = _testDataProvider.openSampleWorkbook("ForShifting." + _testDataProvider.getStandardFileNameExtension())) {
- 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)");
+ Sheet sheet = wb.getSheet("Sheet1");
+ assertEquals(20, sheet.getLastRowNum());
- confirmCell(sheet, 6, 1, 271, "200+B1");
- confirmCell(sheet, 7, 1, 272, "200+B2");
- confirmCell(sheet, 8, 1, 273, "200+B3");
+ 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, 14, 0, 0.0, "A12"); // the cell referred to by this formula will be replaced
+ confirmCell(sheet, 6, 1, 271, "200+B1");
+ confirmCell(sheet, 7, 1, 272, "200+B2");
+ confirmCell(sheet, 8, 1, 273, "200+B3");
- // -----------
- // Row index 1 -> 11 (row "2" -> row "12")
- sheet.shiftRows(1, 1, 10);
+ confirmCell(sheet, 14, 0, 0.0, "A12"); // the cell referred to by this formula will be replaced
- // Now check what sheet looks like after move
+ // -----------
+ // Row index 1 -> 11 (row "2" -> row "12")
+ sheet.shiftRows(1, 1, 10);
- // no changes on row "1"
- confirmRow(sheet, 0, 1, 171, 1, "ROW(D1)", "100+B1", "COUNT(D1:E1)");
+ // Now check what sheet looks like after move
- // row "2" is now empty
- confirmEmptyRow(sheet, 1);
+ // no changes on row "1"
+ confirmRow(sheet, 0, 1, 171, 1, "ROW(D1)", "100+B1", "COUNT(D1:E1)");
- // 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)");
+ // row "2" is now empty
+ confirmEmptyRow(sheet, 1);
- // no changes on row "3"
- confirmRow(sheet, 2, 3, 173, 1, "ROW(D3)", "100+B3", "COUNT(D3:E3)");
+ // 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!");
+ 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");
+ // 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");
- confirmCell(sheet2, 11, 0, 300, "300+Sheet1!#REF!");
+ // 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
- wb.close();
+ // Note - named ranges formulas have not been updated
+ }
}
private static void confirmRow(Sheet sheet, int rowIx, double valA, double valB, double valC,
@@ -419,72 +407,72 @@ public abstract class BaseTestSheetShift
@Test
public final void testShiftSharedFormulasBug54206() throws IOException {
- 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());
+ try (Workbook wb = _testDataProvider.openSampleWorkbook("54206." + _testDataProvider.getStandardFileNameExtension())) {
- 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());
+ 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());
+ 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());
+ 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);
+ // 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());
- wb.close();
+ assertEquals("SUM(G29:I29)", sheet.getRow(28).getCell(9).getCellFormula());
+ assertEquals("SUM(G30:I30)", sheet.getRow(29).getCell(9).getCellFormula());
+ }
}
@Test
void testBug55280() throws IOException {
- Workbook w = _testDataProvider.createWorkbook();
- Sheet s = w.createSheet();
- for (int row = 0; row < 5000; ++row) {
- assertEquals(row, s.addMergedRegion(new CellRangeAddress(row, row, 0, 3)));
- }
+ try (Workbook w = _testDataProvider.createWorkbook()) {
+ Sheet s = w.createSheet();
+ for (int row = 0; row < 5000; ++row) {
+ assertEquals(row, s.addMergedRegion(new CellRangeAddress(row, row, 0, 3)));
+ }
- s.shiftRows(0, 4999, 1); // takes a long time...
- w.close();
+ s.shiftRows(0, 4999, 1); // takes a long time...
+ }
}
@Test
@@ -504,203 +492,191 @@ public abstract class BaseTestSheetShift
*/
@Test
void testBug46742_52903_shiftHyperlinks() throws IOException {
- Workbook wb = _testDataProvider.createWorkbook();
- Sheet sheet = wb.createSheet("test");
- Row row = sheet.createRow(0);
-
- // How to create hyperlinks
- // https://poi.apache.org/spreadsheet/quick-guide.html#Hyperlinks
- CreationHelper helper = wb.getCreationHelper();
- CellStyle hlinkStyle = wb.createCellStyle();
- Font hlinkFont = wb.createFont();
- hlinkFont.setUnderline(Font.U_SINGLE);
- hlinkFont.setColor(IndexedColors.BLUE.getIndex());
- hlinkStyle.setFont(hlinkFont);
-
- // 3D relative document link
- // CellAddress=A1, shifted to A4
- Cell cell = row.createCell(0);
- cell.setCellStyle(hlinkStyle);
- createHyperlink(helper, cell, HyperlinkType.DOCUMENT, "test!E1");
-
- // URL
- cell = row.createCell(1);
- // CellAddress=B1, shifted to B4
- cell.setCellStyle(hlinkStyle);
- createHyperlink(helper, cell, HyperlinkType.URL, "https://poi.apache.org/");
-
- // row0 will be shifted on top of row1, so this URL should be removed from the workbook
- Row overwrittenRow = sheet.createRow(3);
- cell = overwrittenRow.createCell(2);
- // CellAddress=C4, will be overwritten (deleted)
- cell.setCellStyle(hlinkStyle);
- createHyperlink(helper, cell, HyperlinkType.EMAIL, "mailto:poi@apache.org");
-
- // hyperlinks on this row are unaffected by the row shifting, so the hyperlinks should not move
- Row unaffectedRow = sheet.createRow(20);
- cell = unaffectedRow.createCell(3);
- // CellAddress=D21, will be unaffected
- cell.setCellStyle(hlinkStyle);
- createHyperlink(helper, cell, HyperlinkType.FILE, "54524.xlsx");
-
- cell = wb.createSheet("other").createRow(0).createCell(0);
- // CellAddress=Other!A1, will be unaffected
- cell.setCellStyle(hlinkStyle);
- createHyperlink(helper, cell, HyperlinkType.URL, "http://apache.org/");
-
- int startRow = 0;
- int endRow = 0;
- int n = 3;
- sheet.shiftRows(startRow, endRow, n);
-
- Workbook read = _testDataProvider.writeOutAndReadBack(wb);
- wb.close();
-
- Sheet sh = read.getSheet("test");
-
- Row shiftedRow = sh.getRow(3);
-
- // document link anchored on a shifted cell should be moved
- // Note that hyperlinks do not track what they point to, so this hyperlink should still refer to test!E1
- verifyHyperlink(shiftedRow.getCell(0), HyperlinkType.DOCUMENT, "test!E1");
-
- // URL, EMAIL, and FILE links anchored on a shifted cell should be moved
- verifyHyperlink(shiftedRow.getCell(1), HyperlinkType.URL, "https://poi.apache.org/");
-
- // Make sure hyperlinks were moved and not copied
- assertNull(sh.getHyperlink(0, 0), "Document hyperlink should be moved, not copied");
- assertNull(sh.getHyperlink(0, 1), "URL hyperlink should be moved, not copied");
-
- // Make sure hyperlink in overwritten row is deleted
- assertEquals(3, sh.getHyperlinkList().size());
- CellAddress unexpectedLinkAddress = new CellAddress("C4");
- for (Hyperlink link : sh.getHyperlinkList()) {
- final CellAddress linkAddress = new CellAddress(link.getFirstRow(), link.getFirstColumn());
- assertNotEquals(linkAddress, unexpectedLinkAddress,
- "Row 4, including the hyperlink at C4, should have " +
- "been deleted when Row 1 was shifted on top of it.");
- }
-
- // Make sure unaffected rows are not shifted
- Cell unaffectedCell = sh.getRow(20).getCell(3);
- assertTrue(cellHasHyperlink(unaffectedCell));
- verifyHyperlink(unaffectedCell, HyperlinkType.FILE, "54524.xlsx");
-
- // Make sure cells on other sheets are not affected
- unaffectedCell = read.getSheet("other").getRow(0).getCell(0);
- assertTrue(cellHasHyperlink(unaffectedCell));
- verifyHyperlink(unaffectedCell, HyperlinkType.URL, "http://apache.org/");
+ try (Workbook wb = _testDataProvider.createWorkbook()) {
+ Sheet sheet = wb.createSheet("test");
+ Row row = sheet.createRow(0);
- read.close();
+ // How to create hyperlinks
+ // https://poi.apache.org/spreadsheet/quick-guide.html#Hyperlinks
+ CreationHelper helper = wb.getCreationHelper();
+ CellStyle hlinkStyle = wb.createCellStyle();
+ Font hlinkFont = wb.createFont();
+ hlinkFont.setUnderline(Font.U_SINGLE);
+ hlinkFont.setColor(IndexedColors.BLUE.getIndex());
+ hlinkStyle.setFont(hlinkFont);
+
+ // 3D relative document link
+ // CellAddress=A1, shifted to A4
+ Cell cell = row.createCell(0);
+ cell.setCellStyle(hlinkStyle);
+ createHyperlink(helper, cell, HyperlinkType.DOCUMENT, "test!E1");
+
+ // URL
+ cell = row.createCell(1);
+ // CellAddress=B1, shifted to B4
+ cell.setCellStyle(hlinkStyle);
+ createHyperlink(helper, cell, HyperlinkType.URL, "https://poi.apache.org/");
+
+ // row0 will be shifted on top of row1, so this URL should be removed from the workbook
+ Row overwrittenRow = sheet.createRow(3);
+ cell = overwrittenRow.createCell(2);
+ // CellAddress=C4, will be overwritten (deleted)
+ cell.setCellStyle(hlinkStyle);
+ createHyperlink(helper, cell, HyperlinkType.EMAIL, "mailto:poi@apache.org");
+
+ // hyperlinks on this row are unaffected by the row shifting, so the hyperlinks should not move
+ Row unaffectedRow = sheet.createRow(20);
+ cell = unaffectedRow.createCell(3);
+ // CellAddress=D21, will be unaffected
+ cell.setCellStyle(hlinkStyle);
+ createHyperlink(helper, cell, HyperlinkType.FILE, "54524.xlsx");
+
+ cell = wb.createSheet("other").createRow(0).createCell(0);
+ // CellAddress=Other!A1, will be unaffected
+ cell.setCellStyle(hlinkStyle);
+ createHyperlink(helper, cell, HyperlinkType.URL, "http://apache.org/");
+
+ int startRow = 0;
+ int endRow = 0;
+ int n = 3;
+ sheet.shiftRows(startRow, endRow, n);
+
+ try (Workbook read = _testDataProvider.writeOutAndReadBack(wb)) {
+ Sheet sh = read.getSheet("test");
+ Row shiftedRow = sh.getRow(3);
+
+ // document link anchored on a shifted cell should be moved
+ // Note that hyperlinks do not track what they point to, so this hyperlink should still refer to test!E1
+ verifyHyperlink(shiftedRow.getCell(0), HyperlinkType.DOCUMENT, "test!E1");
+
+ // URL, EMAIL, and FILE links anchored on a shifted cell should be moved
+ verifyHyperlink(shiftedRow.getCell(1), HyperlinkType.URL, "https://poi.apache.org/");
+
+ // Make sure hyperlinks were moved and not copied
+ assertNull(sh.getHyperlink(0, 0), "Document hyperlink should be moved, not copied");
+ assertNull(sh.getHyperlink(0, 1), "URL hyperlink should be moved, not copied");
+
+ // Make sure hyperlink in overwritten row is deleted
+ assertEquals(3, sh.getHyperlinkList().size());
+ CellAddress unexpectedLinkAddress = new CellAddress("C4");
+ for (Hyperlink link : sh.getHyperlinkList()) {
+ final CellAddress linkAddress = new CellAddress(link.getFirstRow(), link.getFirstColumn());
+ assertNotEquals(linkAddress, unexpectedLinkAddress,
+ "Row 4, including the hyperlink at C4, should have " +
+ "been deleted when Row 1 was shifted on top of it.");
+ }
+
+ // Make sure unaffected rows are not shifted
+ Cell unaffectedCell = sh.getRow(20).getCell(3);
+ assertTrue(cellHasHyperlink(unaffectedCell));
+ verifyHyperlink(unaffectedCell, HyperlinkType.FILE, "54524.xlsx");
+
+ // Make sure cells on other sheets are not affected
+ unaffectedCell = read.getSheet("other").getRow(0).getCell(0);
+ assertTrue(cellHasHyperlink(unaffectedCell));
+ verifyHyperlink(unaffectedCell, HyperlinkType.URL, "http://apache.org/");
+ }
+ }
}
//@Disabled("bug 56454: Incorrectly handles merged regions that do not contain column 0")
@Test
void shiftRowsWithMergedRegionsThatDoNotContainColumnZero() throws IOException {
- Workbook wb = _testDataProvider.createWorkbook();
- Sheet sheet = wb.createSheet("test");
+ try (Workbook wb = _testDataProvider.createWorkbook()) {
+ Sheet sheet = wb.createSheet("test");
- // populate sheet cells
- for (int i = 0; i < 10; i++) {
- Row row = sheet.createRow(i);
- for (int j = 0; j < 12; j++) {
- Cell cell = row.createCell(j);
- cell.setCellValue(i + "x" + j);
+ // populate sheet cells
+ for (int i = 0; i < 10; i++) {
+ Row row = sheet.createRow(i);
+ for (int j = 0; j < 12; j++) {
+ Cell cell = row.createCell(j);
+ cell.setCellValue(i + "x" + j);
+ }
}
- }
- CellRangeAddress A4_B7 = new CellRangeAddress(3, 6, 0, 1);
- CellRangeAddress C5_D7 = new CellRangeAddress(4, 6, 2, 3);
+ CellRangeAddress A4_B7 = new CellRangeAddress(3, 6, 0, 1);
+ CellRangeAddress C5_D7 = new CellRangeAddress(4, 6, 2, 3);
- assertEquals(0, sheet.addMergedRegion(A4_B7));
- assertEquals(1, sheet.addMergedRegion(C5_D7));
+ assertEquals(0, sheet.addMergedRegion(A4_B7));
+ assertEquals(1, sheet.addMergedRegion(C5_D7));
- // A4:B7 will elongate vertically
- // C5:D7 will be shifted down with same size
- sheet.shiftRows(4, sheet.getLastRowNum(), 1);
-
- // This test is written as expected-to-fail and should be rewritten
- // as expected-to-pass when the bug is fixed.
- // FIXME: remove try, catch, and testPassesNow, skipTest when test passes
- try {
+ // A4:B7 will elongate vertically
+ // C5:D7 will be shifted down with same size
+ sheet.shiftRows(4, sheet.getLastRowNum(), 1);
+
+ // This test is written as expected-to-fail and should be rewritten
+ // as expected-to-pass when the bug is fixed.
+ // FIXME: remove try, catch, and testPassesNow, skipTest when test passes
assertEquals(2, sheet.getNumMergedRegions());
- assertEquals(CellRangeAddress.valueOf("A4:B8"), sheet.getMergedRegion(0));
- assertEquals(CellRangeAddress.valueOf("C5:D8"), sheet.getMergedRegion(1));
- testPassesNow(56454);
- } catch (AssertionError e) {
- skipTest(e);
+ assertNotEquals(CellRangeAddress.valueOf("A4:B8"), sheet.getMergedRegion(0));
+ assertNotEquals(CellRangeAddress.valueOf("C5:D8"), sheet.getMergedRegion(1));
}
-
- wb.close();
}
@Test
void shiftMergedRowsToMergedRowsUp() throws IOException {
- Workbook wb = _testDataProvider.createWorkbook();
- Sheet sheet = wb.createSheet("test");
- populateSheetCells(sheet, 2);
-
+ try (Workbook wb = _testDataProvider.createWorkbook()) {
+ Sheet sheet = wb.createSheet("test");
+ populateSheetCells(sheet, 2);
- CellRangeAddress A1_E1 = new CellRangeAddress(0, 0, 0, 4);
- CellRangeAddress A2_C2 = new CellRangeAddress(1, 1, 0, 2);
- assertEquals(0, sheet.addMergedRegion(A1_E1));
- assertEquals(1, sheet.addMergedRegion(A2_C2));
+ CellRangeAddress A1_E1 = new CellRangeAddress(0, 0, 0, 4);
+ CellRangeAddress A2_C2 = new CellRangeAddress(1, 1, 0, 2);
- // A1:E1 should be removed
- // A2:C2 will be A1:C1
- sheet.shiftRows(1, sheet.getLastRowNum(), -1);
+ assertEquals(0, sheet.addMergedRegion(A1_E1));
+ assertEquals(1, sheet.addMergedRegion(A2_C2));
- assertEquals(1, sheet.getNumMergedRegions());
- assertEquals(CellRangeAddress.valueOf("A1:C1"), sheet.getMergedRegion(0));
+ // A1:E1 should be removed
+ // A2:C2 will be A1:C1
+ sheet.shiftRows(1, sheet.getLastRowNum(), -1);
- wb.close();
+ assertEquals(1, sheet.getNumMergedRegions());
+ assertEquals(CellRangeAddress.valueOf("A1:C1"), sheet.getMergedRegion(0));
+ }
}
@Test
void shiftMergedRowsToMergedRowsOverlappingMergedRegion() throws IOException {
- Workbook wb = _testDataProvider.createWorkbook();
- Sheet sheet = wb.createSheet("test");
- populateSheetCells(sheet, 10);
-
- CellRangeAddress A1_E1 = new CellRangeAddress(0, 0, 0, 4);
- CellRangeAddress A2_C2 = new CellRangeAddress(1, 7, 0, 2);
+ try (Workbook wb = _testDataProvider.createWorkbook()) {
+ Sheet sheet = wb.createSheet("test");
+ populateSheetCells(sheet, 10);
- assertEquals(0, sheet.addMergedRegion(A1_E1));
- assertEquals(1, sheet.addMergedRegion(A2_C2));
+ CellRangeAddress A1_E1 = new CellRangeAddress(0, 0, 0, 4);
+ CellRangeAddress A2_C2 = new CellRangeAddress(1, 7, 0, 2);
- // A1:E1 should move to A5:E5
- // A2:C2 should be removed
- sheet.shiftRows(0, 0, 4);
+ assertEquals(0, sheet.addMergedRegion(A1_E1));
+ assertEquals(1, sheet.addMergedRegion(A2_C2));
- assertEquals(1, sheet.getNumMergedRegions());
- assertEquals(CellRangeAddress.valueOf("A5:E5"), sheet.getMergedRegion(0));
+ // A1:E1 should move to A5:E5
+ // A2:C2 should be removed
+ sheet.shiftRows(0, 0, 4);
- wb.close();
+ assertEquals(1, sheet.getNumMergedRegions());
+ assertEquals(CellRangeAddress.valueOf("A5:E5"), sheet.getMergedRegion(0));
+ }
}
@Test
void bug60384ShiftMergedRegion() throws IOException {
- Workbook wb = _testDataProvider.createWorkbook();
- Sheet sheet = wb.createSheet("test");
- populateSheetCells(sheet, 9);
-
+ try (Workbook wb = _testDataProvider.createWorkbook()) {
+ Sheet sheet = wb.createSheet("test");
+ populateSheetCells(sheet, 9);
- CellRangeAddress A8_E8 = new CellRangeAddress(7, 7, 0, 4);
- CellRangeAddress A9_C9 = new CellRangeAddress(8, 8, 0, 2);
- assertEquals(0, sheet.addMergedRegion(A8_E8));
- assertEquals(1, sheet.addMergedRegion(A9_C9));
+ CellRangeAddress A8_E8 = new CellRangeAddress(7, 7, 0, 4);
+ CellRangeAddress A9_C9 = new CellRangeAddress(8, 8, 0, 2);
- // A1:E1 should be removed
- // A2:C2 will be A1:C1
- sheet.shiftRows(3, sheet.getLastRowNum(), 1);
+ assertEquals(0, sheet.addMergedRegion(A8_E8));
+ assertEquals(1, sheet.addMergedRegion(A9_C9));
- assertEquals(2, sheet.getNumMergedRegions());
- assertEquals(CellRangeAddress.valueOf("A9:E9"), sheet.getMergedRegion(0));
- assertEquals(CellRangeAddress.valueOf("A10:C10"), sheet.getMergedRegion(1));
+ // A1:E1 should be removed
+ // A2:C2 will be A1:C1
+ sheet.shiftRows(3, sheet.getLastRowNum(), 1);
- wb.close();
+ assertEquals(2, sheet.getNumMergedRegions());
+ assertEquals(CellRangeAddress.valueOf("A9:E9"), sheet.getMergedRegion(0));
+ assertEquals(CellRangeAddress.valueOf("A10:C10"), sheet.getMergedRegion(1));
+ }
}
private void populateSheetCells(Sheet sheet, int rowCount) {
@@ -716,80 +692,79 @@ public abstract class BaseTestSheetShift
@Test
void shiftMergedRowsToMergedRowsDown() throws IOException {
- Workbook wb = _testDataProvider.createWorkbook();
- Sheet sheet = wb.createSheet("test");
-
- // populate sheet cells
- populateSheetCells(sheet, 2);
+ try (Workbook wb = _testDataProvider.createWorkbook()) {
+ Sheet sheet = wb.createSheet("test");
- CellRangeAddress A1_E1 = new CellRangeAddress(0, 0, 0, 4);
- CellRangeAddress A2_C2 = new CellRangeAddress(1, 1, 0, 2);
+ // populate sheet cells
+ populateSheetCells(sheet, 2);
- assertEquals(0, sheet.addMergedRegion(A1_E1));
- assertEquals(1, sheet.addMergedRegion(A2_C2));
+ CellRangeAddress A1_E1 = new CellRangeAddress(0, 0, 0, 4);
+ CellRangeAddress A2_C2 = new CellRangeAddress(1, 1, 0, 2);
- // A1:E1 should be moved to A2:E2
- // A2:C2 will be removed
- sheet.shiftRows(0, 0, 1);
+ assertEquals(0, sheet.addMergedRegion(A1_E1));
+ assertEquals(1, sheet.addMergedRegion(A2_C2));
- assertEquals(1, sheet.getNumMergedRegions());
- assertEquals(CellRangeAddress.valueOf("A2:E2"), sheet.getMergedRegion(0));
+ // A1:E1 should be moved to A2:E2
+ // A2:C2 will be removed
+ sheet.shiftRows(0, 0, 1);
- wb.close();
+ assertEquals(1, sheet.getNumMergedRegions());
+ assertEquals(CellRangeAddress.valueOf("A2:E2"), sheet.getMergedRegion(0));
+ }
}
@Test
void test61840_shifting_rows_up_does_not_produce_REF_errors() throws IOException {
- Workbook wb = _testDataProvider.createWorkbook();
- Sheet sheet = wb.createSheet();
- Cell cell = sheet.createRow(4).createCell(0);
-
- cell.setCellFormula("(B5-C5)/B5");
- sheet.shiftRows(4, 4, -1);
+ try (Workbook wb = _testDataProvider.createWorkbook()) {
+ Sheet sheet = wb.createSheet();
+ Cell cell = sheet.createRow(4).createCell(0);
- // Cell objects created before a row shift are still valid.
- // The row number of those cell references will be shifted if
- // the cell is within the shift range.
- assertEquals("(B4-C4)/B4", cell.getCellFormula());
+ cell.setCellFormula("(B5-C5)/B5");
+ sheet.shiftRows(4, 4, -1);
- // New cell references are also valid.
- Cell shiftedCell = sheet.getRow(3).getCell(0);
- assertNotNull(shiftedCell);
- assertEquals("(B4-C4)/B4", shiftedCell.getCellFormula());
+ // Cell objects created before a row shift are still valid.
+ // The row number of those cell references will be shifted if
+ // the cell is within the shift range.
+ assertEquals("(B4-C4)/B4", cell.getCellFormula());
- wb.close();
+ // New cell references are also valid.
+ Cell shiftedCell = sheet.getRow(3).getCell(0);
+ assertNotNull(shiftedCell);
+ assertEquals("(B4-C4)/B4", shiftedCell.getCellFormula());
+ }
}
@Test
- void checkMergedRegions56454() {
- Workbook wb = _testDataProvider.createWorkbook();
- Sheet sheet = wb.createSheet();
+ void checkMergedRegions56454() throws IOException {
+ try (Workbook wb = _testDataProvider.createWorkbook()) {
+ Sheet sheet = wb.createSheet();
- // populate sheet cells
- for (int i = 0; i < 10; i++) {
- Row row = sheet.createRow(i);
+ // populate sheet cells
+ for (int i = 0; i < 10; i++) {
+ Row row = sheet.createRow(i);
- for (int j = 0; j < 10; j++) {
- Cell cell = row.createCell(j, CellType.STRING);
+ for (int j = 0; j < 10; j++) {
+ Cell cell = row.createCell(j, CellType.STRING);
- cell.setCellValue(i + "x" + j);
+ cell.setCellValue(i + "x" + j);
+ }
}
- }
- CellRangeAddress region1 = new CellRangeAddress(3, 6, 0, 1);
- CellRangeAddress region2 = new CellRangeAddress(3, 6, 2, 3);
+ CellRangeAddress region1 = new CellRangeAddress(3, 6, 0, 1);
+ CellRangeAddress region2 = new CellRangeAddress(3, 6, 2, 3);
- sheet.addMergedRegion(region1);
- sheet.addMergedRegion(region2);
+ sheet.addMergedRegion(region1);
+ sheet.addMergedRegion(region2);
- sheet.shiftRows(4, sheet.getLastRowNum(), 1);
+ sheet.shiftRows(4, sheet.getLastRowNum(), 1);
- // check, if all regions still start at row 3
- for (int i = 0; i < sheet.getNumMergedRegions(); i++) {
- CellRangeAddress cr = sheet.getMergedRegion(i);
+ // check, if all regions still start at row 3
+ for (int i = 0; i < sheet.getNumMergedRegions(); i++) {
+ CellRangeAddress cr = sheet.getMergedRegion(i);
- assertEquals(cr.getFirstRow(), 3);
+ assertEquals(cr.getFirstRow(), 3);
+ }
}
}
Modified: poi/trunk/poi/src/test/java/org/apache/poi/ss/usermodel/BaseTestWorkbook.java
URL: http://svn.apache.org/viewvc/poi/trunk/poi/src/test/java/org/apache/poi/ss/usermodel/BaseTestWorkbook.java?rev=1892683&r1=1892682&r2=1892683&view=diff
==============================================================================
--- poi/trunk/poi/src/test/java/org/apache/poi/ss/usermodel/BaseTestWorkbook.java (original)
+++ poi/trunk/poi/src/test/java/org/apache/poi/ss/usermodel/BaseTestWorkbook.java Sat Aug 28 23:48:48 2021
@@ -724,7 +724,7 @@ public abstract class BaseTestWorkbook {
}
@Test
- void changeSheetNameWithSharedFormulas() throws IOException {
+ protected void changeSheetNameWithSharedFormulas() throws IOException {
String sampleFile = "shared_formulas.xls" + (getClass().getName().contains("xssf") ? "x" : "");
try (Workbook wb = _testDataProvider.openSampleWorkbook(sampleFile)) {
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@poi.apache.org
For additional commands, e-mail: commits-help@poi.apache.org