/**
* 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.
*
* @param sampleName the sample file to test against
*/
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);
}
public void testShiftWithComments() { // TODO - enable XSSF test
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");
// 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);
}
/** Test that when reading a workbook from input stream, array formulas are recognized */
public final void testReadArrayFormula() {
Cell[] cells;
Workbook workbook = _testDataProvider.createWorkbook();
Sheet sheet1 = workbook.createSheet();
cells =
sheet1
.setArrayFormula("SUM(A1:A3*B1:B3)", CellRangeAddress.valueOf("C4:C6"))
.getFlattenedCells();
assertEquals(3, cells.length);
cells =
sheet1
.setArrayFormula("MAX(A1:A3*B1:B3)", CellRangeAddress.valueOf("A4:A6"))
.getFlattenedCells();
assertEquals(3, cells.length);
Sheet sheet2 = workbook.createSheet();
cells =
sheet2
.setArrayFormula("MIN(A1:A3*B1:B3)", CellRangeAddress.valueOf("D2:D4"))
.getFlattenedCells();
assertEquals(3, cells.length);
workbook = _testDataProvider.writeOutAndReadBack(workbook);
sheet1 = workbook.getSheetAt(0);
for (int rownum = 3; rownum <= 5; rownum++) {
Cell cell1 = sheet1.getRow(rownum).getCell(2);
assertTrue(cell1.isPartOfArrayFormulaGroup());
Cell cell2 = sheet1.getRow(rownum).getCell(0);
assertTrue(cell2.isPartOfArrayFormulaGroup());
}
sheet2 = workbook.getSheetAt(1);
for (int rownum = 1; rownum <= 3; rownum++) {
Cell cell1 = sheet2.getRow(rownum).getCell(3);
assertTrue(cell1.isPartOfArrayFormulaGroup());
}
}