Exemplo n.º 1
0
  /**
   * 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);
  }
Exemplo n.º 2
0
  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());
    }
  }