You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@poi.apache.org by jo...@apache.org on 2009/02/09 22:08:58 UTC
svn commit: r742733 - in
/poi/trunk/src/testcases/org/apache/poi/hssf/usermodel:
BaseTestSheetShiftRows.java TestSheetShiftRows.java
Author: josh
Date: Mon Feb 9 21:08:58 2009
New Revision: 742733
URL: http://svn.apache.org/viewvc?rev=742733&view=rev
Log:
Generalised TestSheetShiftRows for both HSSF and XSSF test cases
Added:
poi/trunk/src/testcases/org/apache/poi/hssf/usermodel/BaseTestSheetShiftRows.java
- copied, changed from r742674, poi/trunk/src/testcases/org/apache/poi/hssf/usermodel/TestSheetShiftRows.java
Modified:
poi/trunk/src/testcases/org/apache/poi/hssf/usermodel/TestSheetShiftRows.java
Copied: poi/trunk/src/testcases/org/apache/poi/hssf/usermodel/BaseTestSheetShiftRows.java (from r742674, poi/trunk/src/testcases/org/apache/poi/hssf/usermodel/TestSheetShiftRows.java)
URL: http://svn.apache.org/viewvc/poi/trunk/src/testcases/org/apache/poi/hssf/usermodel/BaseTestSheetShiftRows.java?p2=poi/trunk/src/testcases/org/apache/poi/hssf/usermodel/BaseTestSheetShiftRows.java&p1=poi/trunk/src/testcases/org/apache/poi/hssf/usermodel/TestSheetShiftRows.java&r1=742674&r2=742733&rev=742733&view=diff
==============================================================================
--- poi/trunk/src/testcases/org/apache/poi/hssf/usermodel/TestSheetShiftRows.java (original)
+++ poi/trunk/src/testcases/org/apache/poi/hssf/usermodel/BaseTestSheetShiftRows.java Mon Feb 9 21:08:58 2009
@@ -17,257 +17,269 @@
package org.apache.poi.hssf.usermodel;
-import java.io.ByteArrayInputStream;
-import java.io.ByteArrayOutputStream;
-import java.io.FileNotFoundException;
-import java.io.FileOutputStream;
-import java.io.IOException;
-import java.io.OutputStream;
-
import junit.framework.TestCase;
-import org.apache.poi.hssf.HSSFTestDataSamples;
+import org.apache.poi.ss.usermodel.Cell;
+import org.apache.poi.ss.usermodel.Row;
+import org.apache.poi.ss.usermodel.Sheet;
+import org.apache.poi.ss.usermodel.Workbook;
/**
- * Tests row shifting capabilities.
+ * Tests row shifting capabilities (common base class for HSSF and XSSF tests).
*
*
* @author Shawn Laubach (slaubach at apache dot com)
* @author Toshiaki Kamoshida (kamoshida.toshiaki at future dot co dot jp)
*/
-public final class TestSheetShiftRows extends TestCase {
-
- /**
- * 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 void testShiftRows() throws Exception
- {
- // Read initial file in
- HSSFWorkbook wb = HSSFTestDataSamples.openSampleWorkbook("SimpleMultiCell.xls");
- HSSFSheet s = wb.getSheetAt( 0 );
-
- // Shift the second row down 1 and write to temp file
- s.shiftRows( 1, 1, 1 );
-
- wb = HSSFTestDataSamples.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 );
- assertTrue( s.getRow( 1 ) == null || s.getRow( 1 ).getPhysicalNumberOfCells() == 0 );
- 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 = HSSFTestDataSamples.writeOutAndReadBack(wb);
-
- // Read and ensure things are where they should be
- s = wb.getSheetAt( 0 );
- assertTrue( s.getRow( 0 ) == null || s.getRow( 0 ).getPhysicalNumberOfCells() == 0 );
- assertTrue( s.getRow( 1 ) == null || s.getRow( 1 ).getPhysicalNumberOfCells() == 0 );
- assertTrue( s.getRow( 2 ) == null || s.getRow( 2 ).getPhysicalNumberOfCells() == 0 );
- assertEquals( s.getRow( 3 ).getPhysicalNumberOfCells(), 1 );
- assertTrue( s.getRow( 4 ) == null || s.getRow( 4 ).getPhysicalNumberOfCells() == 0 );
- assertEquals( s.getRow( 5 ).getPhysicalNumberOfCells(), 2 );
-
- // Read the first file again
- wb = HSSFTestDataSamples.openSampleWorkbook("SimpleMultiCell.xls");
- s = wb.getSheetAt( 0 );
-
- // Shift rows 3 and 4 up and write to temp file
- s.shiftRows( 2, 3, -2 );
- wb = HSSFTestDataSamples.writeOutAndReadBack(wb);
- s = wb.getSheetAt( 0 );
- assertEquals( s.getRow( 0 ).getPhysicalNumberOfCells(), 3 );
- assertEquals( s.getRow( 1 ).getPhysicalNumberOfCells(), 4 );
- assertTrue( s.getRow( 2 ) == null || s.getRow( 2 ).getPhysicalNumberOfCells() == 0 );
- assertTrue( s.getRow( 3 ) == null || s.getRow( 3 ).getPhysicalNumberOfCells() == 0 );
- assertEquals( s.getRow( 4 ).getPhysicalNumberOfCells(), 5 );
- }
-
- /**
- * Tests when rows are null.
- *
- * @author Toshiaki Kamoshida (kamoshida.toshiaki at future dot co dot jp)
- */
- public void testShiftRow(){
- HSSFWorkbook b = new HSSFWorkbook();
- HSSFSheet 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 void testShiftRow0(){
- HSSFWorkbook b = new HSSFWorkbook();
- HSSFSheet 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(){
- HSSFWorkbook b = new HSSFWorkbook();
- HSSFSheet s = b.createSheet();
- HSSFRow 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() throws Exception {
- HSSFWorkbook wb = HSSFTestDataSamples.openSampleWorkbook("comments.xls");
-
- HSSFSheet 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);
- ByteArrayOutputStream outputStream = new ByteArrayOutputStream();
- wb.write(outputStream);
-
- // 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 = new HSSFWorkbook( new ByteArrayInputStream(outputStream.toByteArray()) );
- 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 testShiftWithFormulas() {
- HSSFWorkbook wb = HSSFTestDataSamples.openSampleWorkbook("ForShifting.xls");
-
- HSSFSheet 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
- assertEquals(0, sheet.getRow(1).getPhysicalNumberOfCells());
-
- // 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
- HSSFSheet 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(HSSFSheet 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(HSSFSheet sheet, int rowIx, int colIx,
- double expectedValue, String expectedFormula) {
- HSSFCell cell = sheet.getRow(rowIx).getCell(colIx);
- assertEquals(expectedValue, cell.getNumericCellValue(), 0.0);
- assertEquals(expectedFormula, cell.getCellFormula());
- }
+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 testShiftRows(){
+ final String sampleName = "SimpleMultiCell";
+ // 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 testShiftRow() {
+ 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 testShiftRow0() {
+ 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 testShiftRowBreaks() {
+ 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 testShiftWithComments() {
+ final String sampleName = "comments";
+ 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 testShiftWithFormulas() {
+ String sampleName = "ForShifting";
+ 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
+ assertEquals(0, sheet.getRow(1).getPhysicalNumberOfCells());
+
+ // 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());
+ }
}
Modified: poi/trunk/src/testcases/org/apache/poi/hssf/usermodel/TestSheetShiftRows.java
URL: http://svn.apache.org/viewvc/poi/trunk/src/testcases/org/apache/poi/hssf/usermodel/TestSheetShiftRows.java?rev=742733&r1=742732&r2=742733&view=diff
==============================================================================
--- poi/trunk/src/testcases/org/apache/poi/hssf/usermodel/TestSheetShiftRows.java (original)
+++ poi/trunk/src/testcases/org/apache/poi/hssf/usermodel/TestSheetShiftRows.java Mon Feb 9 21:08:58 2009
@@ -17,257 +17,33 @@
package org.apache.poi.hssf.usermodel;
-import java.io.ByteArrayInputStream;
-import java.io.ByteArrayOutputStream;
-import java.io.FileNotFoundException;
-import java.io.FileOutputStream;
-import java.io.IOException;
-import java.io.OutputStream;
-
-import junit.framework.TestCase;
-
import org.apache.poi.hssf.HSSFTestDataSamples;
+import org.apache.poi.ss.usermodel.Workbook;
/**
* Tests row shifting capabilities.
- *
- *
+ *
+ *
* @author Shawn Laubach (slaubach at apache dot com)
* @author Toshiaki Kamoshida (kamoshida.toshiaki at future dot co dot jp)
*/
-public final class TestSheetShiftRows extends TestCase {
-
- /**
- * 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 void testShiftRows() throws Exception
- {
- // Read initial file in
- HSSFWorkbook wb = HSSFTestDataSamples.openSampleWorkbook("SimpleMultiCell.xls");
- HSSFSheet s = wb.getSheetAt( 0 );
-
- // Shift the second row down 1 and write to temp file
- s.shiftRows( 1, 1, 1 );
-
- wb = HSSFTestDataSamples.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 );
- assertTrue( s.getRow( 1 ) == null || s.getRow( 1 ).getPhysicalNumberOfCells() == 0 );
- 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 = HSSFTestDataSamples.writeOutAndReadBack(wb);
-
- // Read and ensure things are where they should be
- s = wb.getSheetAt( 0 );
- assertTrue( s.getRow( 0 ) == null || s.getRow( 0 ).getPhysicalNumberOfCells() == 0 );
- assertTrue( s.getRow( 1 ) == null || s.getRow( 1 ).getPhysicalNumberOfCells() == 0 );
- assertTrue( s.getRow( 2 ) == null || s.getRow( 2 ).getPhysicalNumberOfCells() == 0 );
- assertEquals( s.getRow( 3 ).getPhysicalNumberOfCells(), 1 );
- assertTrue( s.getRow( 4 ) == null || s.getRow( 4 ).getPhysicalNumberOfCells() == 0 );
- assertEquals( s.getRow( 5 ).getPhysicalNumberOfCells(), 2 );
-
- // Read the first file again
- wb = HSSFTestDataSamples.openSampleWorkbook("SimpleMultiCell.xls");
- s = wb.getSheetAt( 0 );
-
- // Shift rows 3 and 4 up and write to temp file
- s.shiftRows( 2, 3, -2 );
- wb = HSSFTestDataSamples.writeOutAndReadBack(wb);
- s = wb.getSheetAt( 0 );
- assertEquals( s.getRow( 0 ).getPhysicalNumberOfCells(), 3 );
- assertEquals( s.getRow( 1 ).getPhysicalNumberOfCells(), 4 );
- assertTrue( s.getRow( 2 ) == null || s.getRow( 2 ).getPhysicalNumberOfCells() == 0 );
- assertTrue( s.getRow( 3 ) == null || s.getRow( 3 ).getPhysicalNumberOfCells() == 0 );
- assertEquals( s.getRow( 4 ).getPhysicalNumberOfCells(), 5 );
- }
-
- /**
- * Tests when rows are null.
- *
- * @author Toshiaki Kamoshida (kamoshida.toshiaki at future dot co dot jp)
- */
- public void testShiftRow(){
- HSSFWorkbook b = new HSSFWorkbook();
- HSSFSheet 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 void testShiftRow0(){
- HSSFWorkbook b = new HSSFWorkbook();
- HSSFSheet 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(){
- HSSFWorkbook b = new HSSFWorkbook();
- HSSFSheet s = b.createSheet();
- HSSFRow 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() throws Exception {
- HSSFWorkbook wb = HSSFTestDataSamples.openSampleWorkbook("comments.xls");
-
- HSSFSheet 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);
- ByteArrayOutputStream outputStream = new ByteArrayOutputStream();
- wb.write(outputStream);
-
- // 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 = new HSSFWorkbook( new ByteArrayInputStream(outputStream.toByteArray()) );
- 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 testShiftWithFormulas() {
- HSSFWorkbook wb = HSSFTestDataSamples.openSampleWorkbook("ForShifting.xls");
-
- HSSFSheet 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
- assertEquals(0, sheet.getRow(1).getPhysicalNumberOfCells());
-
- // 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
- HSSFSheet 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(HSSFSheet 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);
- }
+public final class TestSheetShiftRows extends BaseTestSheetShiftRows {
- private static void confirmCell(HSSFSheet sheet, int rowIx, int colIx,
- double expectedValue, String expectedFormula) {
- HSSFCell cell = sheet.getRow(rowIx).getCell(colIx);
- assertEquals(expectedValue, cell.getNumericCellValue(), 0.0);
- assertEquals(expectedFormula, cell.getCellFormula());
- }
+ @Override
+ protected Workbook openSampleWorkbook(String sampleFileName) {
+ return HSSFTestDataSamples.openSampleWorkbook(sampleFileName + ".xls");
+ }
+
+ @Override
+ protected Workbook writeOutAndReadBack(Workbook wb) {
+ if (wb instanceof HSSFWorkbook) {
+ return HSSFTestDataSamples.writeOutAndReadBack((HSSFWorkbook) wb);
+ }
+ throw new IllegalArgumentException("bad workbook type (" + wb.getClass().getName() + ")");
+ }
+
+ @Override
+ protected Workbook createWorkbook() {
+ return new HSSFWorkbook();
+ }
}
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@poi.apache.org
For additional commands, e-mail: commits-help@poi.apache.org