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

Example 36 with Workbook

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

the class TestXSSFComment method bug57838DeleteRowsWthCommentsBug.

@Test
public void bug57838DeleteRowsWthCommentsBug() throws IOException {
    Workbook wb = XSSFTestDataSamples.openSampleWorkbook("57838.xlsx");
    Sheet sheet = wb.getSheetAt(0);
    Comment comment1 = sheet.getCellComment(new CellAddress(2, 1));
    assertNotNull(comment1);
    Comment comment2 = sheet.getCellComment(new CellAddress(2, 2));
    assertNotNull(comment2);
    Row row = sheet.getRow(2);
    assertNotNull(row);
    // Remove row from index 2
    sheet.removeRow(row);
    row = sheet.getRow(2);
    // Row is null since we deleted it.
    assertNull(row);
    comment1 = sheet.getCellComment(new CellAddress(2, 1));
    // comment should be null but will fail due to bug
    assertNull(comment1);
    comment2 = sheet.getCellComment(new CellAddress(2, 2));
    // comment should be null but will fail due to bug
    assertNull(comment2);
    wb.close();
}

Example 37 with Workbook

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

the class TestXSSFDataFormat method testConditionalFormattingEvaluation.

@Test
public void testConditionalFormattingEvaluation() throws IOException {
    final Workbook wb = XSSFTestDataSamples.openSampleWorkbook("61060-conditional-number-formatting.xlsx");
    final DataFormatter formatter = new DataFormatter();
    final FormulaEvaluator evaluator = wb.getCreationHelper().createFormulaEvaluator();
    final ConditionalFormattingEvaluator cfEvaluator = new ConditionalFormattingEvaluator(wb, (WorkbookEvaluatorProvider) evaluator);
    CellReference ref = new CellReference("A1");
    Cell cell = wb.getSheetAt(0).getRow(ref.getRow()).getCell(ref.getCol());
    assertEquals("0.10", formatter.formatCellValue(cell, evaluator, cfEvaluator));
    // verify cell format without the conditional rule applied
    assertEquals("0.1", formatter.formatCellValue(cell, evaluator));
    ref = new CellReference("A3");
    cell = wb.getSheetAt(0).getRow(ref.getRow()).getCell(ref.getCol());
    assertEquals("-2.00E+03", formatter.formatCellValue(cell, evaluator, cfEvaluator));
    // verify cell format without the conditional rule applied
    assertEquals("-2000", formatter.formatCellValue(cell, evaluator));
    ref = new CellReference("A4");
    cell = wb.getSheetAt(0).getRow(ref.getRow()).getCell(ref.getCol());
    assertEquals("100", formatter.formatCellValue(cell, evaluator, cfEvaluator));
    ref = new CellReference("A5");
    cell = wb.getSheetAt(0).getRow(ref.getRow()).getCell(ref.getCol());
    assertEquals("$1,000", formatter.formatCellValue(cell, evaluator, cfEvaluator));
    // verify cell format without the conditional rule applied
    assertEquals("1000", formatter.formatCellValue(cell, evaluator));
    wb.close();
}

Example 38 with Workbook

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

the class TestXSSFComment method testBug58175.

@Test
public void testBug58175() throws IOException {
    Workbook wb = new SXSSFWorkbook();
    try {
        Sheet sheet = wb.createSheet();
        Row row = sheet.createRow(1);
        Cell cell = row.createCell(3);
        cell.setCellValue("F4");
        CreationHelper factory = wb.getCreationHelper();
        // When the comment box is visible, have it show in a 1x3 space
        ClientAnchor anchor = factory.createClientAnchor();
        anchor.setCol1(cell.getColumnIndex());
        anchor.setCol2(cell.getColumnIndex() + 1);
        anchor.setRow1(row.getRowNum());
        anchor.setRow2(row.getRowNum() + 3);
        XSSFClientAnchor ca = (XSSFClientAnchor) anchor;
        // create comments and vmlDrawing parts if they don't exist
        CommentsTable comments = ((SXSSFWorkbook) wb).getXSSFWorkbook().getSheetAt(0).getCommentsTable(true);
        XSSFVMLDrawing vml = ((SXSSFWorkbook) wb).getXSSFWorkbook().getSheetAt(0).getVMLDrawing(true);
        CTShape vmlShape1 = vml.newCommentShape();
        if (ca.isSet()) {
            String position = ca.getCol1() + ", 0, " + ca.getRow1() + ", 0, " + ca.getCol2() + ", 0, " + ca.getRow2() + ", 0";
            vmlShape1.getClientDataArray(0).setAnchorArray(0, position);
        }
        // create the comment in two different ways and verify that there is no difference
        XSSFComment shape1 = new XSSFComment(comments, comments.newComment(CellAddress.A1), vmlShape1);
        shape1.setColumn(ca.getCol1());
        shape1.setRow(ca.getRow1());
        CTShape vmlShape2 = vml.newCommentShape();
        if (ca.isSet()) {
            String position = ca.getCol1() + ", 0, " + ca.getRow1() + ", 0, " + ca.getCol2() + ", 0, " + ca.getRow2() + ", 0";
            vmlShape2.getClientDataArray(0).setAnchorArray(0, position);
        }
        CellAddress ref = new CellAddress(ca.getRow1(), ca.getCol1());
        XSSFComment shape2 = new XSSFComment(comments, comments.newComment(ref), vmlShape2);
        assertEquals(shape1.getAuthor(), shape2.getAuthor());
        assertEquals(shape1.getClientAnchor(), shape2.getClientAnchor());
        assertEquals(shape1.getColumn(), shape2.getColumn());
        assertEquals(shape1.getRow(), shape2.getRow());
        assertEquals(shape1.getCTComment().toString(), shape2.getCTComment().toString());
        assertEquals(shape1.getCTComment().getRef(), shape2.getCTComment().getRef());
        /*CommentsTable table1 = shape1.getCommentsTable();
            CommentsTable table2 = shape2.getCommentsTable();
            assertEquals(table1.getCTComments().toString(), table2.getCTComments().toString());
            assertEquals(table1.getNumberOfComments(), table2.getNumberOfComments());
            assertEquals(table1.getRelations(), table2.getRelations());*/
        assertEquals("The vmlShapes should have equal content afterwards", vmlShape1.toString().replaceAll("_x0000_s\\d+", "_x0000_s0000"), vmlShape2.toString().replaceAll("_x0000_s\\d+", "_x0000_s0000"));
    } finally {
        wb.close();
    }
}

Example 39 with Workbook

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

the class TestXSSFFont method testCanComputeWidthXSSF.

// store test from TestSheetUtil here as it uses XSSF
@Test
public void testCanComputeWidthXSSF() throws IOException {
    Workbook wb = new XSSFWorkbook();
    // cannot check on result because on some machines we get back false here!
    SheetUtil.canComputeColumnWidth(wb.getFontAt((short) 0));
    wb.close();
}

Example 40 with Workbook

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

the class TestXSSFFormulaEvaluation method testBug56655.

@Test
public void testBug56655() throws IOException {
    Workbook wb = new XSSFWorkbook();
    Sheet sheet = wb.createSheet();
    setCellFormula(sheet, 0, 0, "#VALUE!");
    setCellFormula(sheet, 0, 1, "SUMIFS(A:A,A:A,#VALUE!)");
    wb.getCreationHelper().createFormulaEvaluator().evaluateAll();
    assertEquals(CellType.ERROR, getCell(sheet, 0, 0).getCachedFormulaResultTypeEnum());
    assertEquals(FormulaError.VALUE.getCode(), getCell(sheet, 0, 0).getErrorCellValue());
    assertEquals(CellType.ERROR, getCell(sheet, 0, 1).getCachedFormulaResultTypeEnum());
    assertEquals(FormulaError.VALUE.getCode(), getCell(sheet, 0, 1).getErrorCellValue());
    wb.close();
}