Examples with Workbook org.apache.poi.ss.usermodel.Workbook used on opensource projects

Example 66 with Workbook

use of org.apache.poi.ss.usermodel.Workbook in project poi by apache.

the class TestXSSFSheetShiftRows method testBug53798.

/**  negative row shift causes corrupted data or throws exception */
@Test
public void testBug53798() throws IOException {
    // NOTE that for HSSF (.xls) negative shifts combined with positive ones do work as expected  
    Workbook wb = XSSFTestDataSamples.openSampleWorkbook("53798.xlsx");
    Sheet testSheet = wb.getSheetAt(0);
    // 1) corrupted xlsx (unreadable data in the first row of a shifted group) already comes about  
    // when shifted by less than -1 negative amount (try -2)
    testSheet.shiftRows(3, 3, -2);
    // 2) attempt to create a new row IN PLACE of a removed row by a negative shift causes corrupted
    // xlsx file with  unreadable data in the negative shifted row. 
    // NOTE it's ok to create any other row.
    Row newRow = testSheet.createRow(3);
    Cell newCell = newRow.createCell(0);
    newCell.setCellValue("new Cell in row " + newRow.getRowNum());
    // 3) once a negative shift has been made any attempt to shift another group of rows 
    // (note: outside of previously negative shifted rows) by a POSITIVE amount causes POI exception: 
    // org.apache.xmlbeans.impl.values.XmlValueDisconnectedException.
    // NOTE: another negative shift on another group of rows is successful, provided no new rows in  
    // place of previously shifted rows were attempted to be created as explained above.
    // -- CHANGE the shift to positive once the behaviour of the above has been tested
    testSheet.shiftRows(6, 7, 1);
    Workbook read = XSSFTestDataSamples.writeOutAndReadBack(wb);
    wb.close();
    assertNotNull(read);
    Sheet readSheet = read.getSheetAt(0);
    verifyCellContent(readSheet, 0, "0.0");
    verifyCellContent(readSheet, 1, "3.0");
    verifyCellContent(readSheet, 2, "2.0");
    verifyCellContent(readSheet, 3, "new Cell in row 3");
    verifyCellContent(readSheet, 4, "4.0");
    verifyCellContent(readSheet, 5, "5.0");
    verifyCellContent(readSheet, 6, null);
    verifyCellContent(readSheet, 7, "6.0");
    verifyCellContent(readSheet, 8, "7.0");
    read.close();
}

Example 67 with Workbook

use of org.apache.poi.ss.usermodel.Workbook in project poi by apache.

the class TestXSSFSheetShiftRows method testBug56017.

/** Shifting rows with comment result - Unreadable content error and comment deletion */
@Test
public void testBug56017() throws IOException {
    Workbook wb = XSSFTestDataSamples.openSampleWorkbook("56017.xlsx");
    Sheet sheet = wb.getSheetAt(0);
    Comment comment = sheet.getCellComment(new CellAddress(0, 0));
    assertNotNull(comment);
    assertEquals("Amdocs", comment.getAuthor());
    assertEquals("Amdocs:\ntest\n", comment.getString().getString());
    sheet.shiftRows(0, 1, 1);
    // comment in row 0 is gone
    comment = sheet.getCellComment(new CellAddress(0, 0));
    assertNull(comment);
    // comment is now in row 1
    comment = sheet.getCellComment(new CellAddress(1, 0));
    assertNotNull(comment);
    assertEquals("Amdocs", comment.getAuthor());
    assertEquals("Amdocs:\ntest\n", comment.getString().getString());
    Workbook wbBack = XSSFTestDataSamples.writeOutAndReadBack(wb);
    wb.close();
    assertNotNull(wbBack);
    Sheet sheetBack = wbBack.getSheetAt(0);
    // comment in row 0 is gone
    comment = sheetBack.getCellComment(new CellAddress(0, 0));
    assertNull(comment);
    // comment is now in row 1
    comment = sheetBack.getCellComment(new CellAddress(1, 0));
    assertNotNull(comment);
    assertEquals("Amdocs", comment.getAuthor());
    assertEquals("Amdocs:\ntest\n", comment.getString().getString());
    wbBack.close();
}

Example 68 with Workbook

use of org.apache.poi.ss.usermodel.Workbook in project poi by apache.

the class TestXSSFSheetShiftRows method bug59733.

// This test is written as expected-to-fail and should be rewritten
// as expected-to-pass when the bug is fixed.
//@Ignore("Bug 59733 - shiftRows() causes org.apache.xmlbeans.impl.values.XmlValueDisconnectedException")
@Test
public void bug59733() throws IOException {
    Workbook workbook = new XSSFWorkbook();
    Sheet sheet = workbook.createSheet();
    for (int r = 0; r < 4; r++) {
        sheet.createRow(r);
    }
    // Shift the 2nd row on top of the 0th row
    sheet.shiftRows(2, 2, -2);
    // FIXME: remove try, catch, and testPassesNow, skipTest when test passes
    try {
        sheet.removeRow(sheet.getRow(0));
        assertEquals(1, sheet.getRow(1).getRowNum());
        testPassesNow(59733);
    } catch (XmlValueDisconnectedException e) {
        skipTest(e);
    }
    workbook.close();
}

Example 69 with Workbook

use of org.apache.poi.ss.usermodel.Workbook in project poi by apache.

the class TestSubtotal method testAvg.

@Test
public void testAvg() throws IOException {
    Workbook wb = new HSSFWorkbook();
    FormulaEvaluator fe = wb.getCreationHelper().createFormulaEvaluator();
    Sheet sh = wb.createSheet();
    Cell a1 = sh.createRow(1).createCell(1);
    a1.setCellValue(1);
    Cell a2 = sh.createRow(2).createCell(1);
    a2.setCellValue(3);
    Cell a3 = sh.createRow(3).createCell(1);
    a3.setCellFormula("SUBTOTAL(1,B2:B3)");
    Cell a4 = sh.createRow(4).createCell(1);
    a4.setCellValue(1);
    Cell a5 = sh.createRow(5).createCell(1);
    a5.setCellValue(7);
    Cell a6 = sh.createRow(6).createCell(1);
    a6.setCellFormula("SUBTOTAL(1,B2:B6)*2 + 2");
    Cell a7 = sh.createRow(7).createCell(1);
    a7.setCellFormula("SUBTOTAL(1,B2:B7)");
    Cell a8 = sh.createRow(8).createCell(1);
    a8.setCellFormula("SUBTOTAL(1,B2,B3,B4,B5,B6,B7,B8)");
    fe.evaluateAll();
    assertEquals(2.0, a3.getNumericCellValue(), 0);
    assertEquals(8.0, a6.getNumericCellValue(), 0);
    assertEquals(3.0, a7.getNumericCellValue(), 0);
    assertEquals(3.0, a8.getNumericCellValue(), 0);
    wb.close();
}

Example 70 with Workbook

use of org.apache.poi.ss.usermodel.Workbook in project poi by apache.

the class TestSubtotal method testMin.

@Test
public void testMin() throws IOException {
    Workbook wb = new HSSFWorkbook();
    FormulaEvaluator fe = wb.getCreationHelper().createFormulaEvaluator();
    Sheet sh = wb.createSheet();
    Cell a1 = sh.createRow(1).createCell(1);
    a1.setCellValue(1);
    Cell a2 = sh.createRow(2).createCell(1);
    a2.setCellValue(3);
    Cell a3 = sh.createRow(3).createCell(1);
    a3.setCellFormula("SUBTOTAL(5,B2:B3)");
    Cell a4 = sh.createRow(4).createCell(1);
    a4.setCellValue(1);
    Cell a5 = sh.createRow(5).createCell(1);
    a5.setCellValue(7);
    Cell a6 = sh.createRow(6).createCell(1);
    a6.setCellFormula("SUBTOTAL(5,B2:B6)*2 + 2");
    Cell a7 = sh.createRow(7).createCell(1);
    a7.setCellFormula("SUBTOTAL(5,B2:B7)");
    Cell a8 = sh.createRow(8).createCell(1);
    a8.setCellFormula("SUBTOTAL(5,B2,B3,B4,B5,B6,B7,B8)");
    fe.evaluateAll();
    assertEquals(1.0, a3.getNumericCellValue(), 0);
    assertEquals(4.0, a6.getNumericCellValue(), 0);
    assertEquals(1.0, a7.getNumericCellValue(), 0);
    assertEquals(1.0, a8.getNumericCellValue(), 0);
    wb.close();
}