Examples with HSSFPatriarch org.apache.poi.hssf.usermodel.HSSFPatriarch used on opensource projects

Example 11 with HSSFPatriarch

use of org.apache.poi.hssf.usermodel.HSSFPatriarch in project poi by apache.

the class BiffDrawingToXml method writeToFile.

public static void writeToFile(OutputStream fos, InputStream xlsWorkbook, boolean excludeWorkbookRecords, String[] params) throws IOException {
    HSSFWorkbook workbook = new HSSFWorkbook(xlsWorkbook);
    InternalWorkbook internalWorkbook = workbook.getInternalWorkbook();
    DrawingGroupRecord r = (DrawingGroupRecord) internalWorkbook.findFirstRecordBySid(DrawingGroupRecord.sid);
    StringBuilder builder = new StringBuilder();
    builder.append("<workbook>\n");
    String tab = "\t";
    if (!excludeWorkbookRecords && r != null) {
        r.decode();
        List<EscherRecord> escherRecords = r.getEscherRecords();
        for (EscherRecord record : escherRecords) {
            builder.append(record.toXml(tab));
        }
    }
    List<Integer> sheets = getSheetsIndexes(params, workbook);
    for (Integer i : sheets) {
        HSSFPatriarch p = workbook.getSheetAt(i).getDrawingPatriarch();
        if (p != null) {
            builder.append(tab).append("<sheet").append(i).append(">\n");
            builder.append(p.getBoundAggregate().toXml(tab + "\t"));
            builder.append(tab).append("</sheet").append(i).append(">\n");
        }
    }
    builder.append("</workbook>\n");
    fos.write(builder.toString().getBytes(StringUtil.UTF8));
    fos.close();
    workbook.close();
}

Example 12 with HSSFPatriarch

use of org.apache.poi.hssf.usermodel.HSSFPatriarch in project poi by apache.

the class CellComments method main.

public static void main(String[] args) throws IOException {
    HSSFWorkbook wb = new HSSFWorkbook();
    try {
        HSSFSheet sheet = wb.createSheet("Cell comments in POI HSSF");
        // Create the drawing patriarch. This is the top level container for all shapes including cell comments.
        HSSFPatriarch patr = sheet.createDrawingPatriarch();
        //create a cell in row 3
        HSSFCell cell1 = sheet.createRow(3).createCell(1);
        cell1.setCellValue(new HSSFRichTextString("Hello, World"));
        //anchor defines size and position of the comment in worksheet
        HSSFComment comment1 = patr.createComment(new HSSFClientAnchor(0, 0, 0, 0, (short) 4, 2, (short) 6, 5));
        // set text in the comment
        comment1.setString(new HSSFRichTextString("We can set comments in POI"));
        //set comment author.
        //you can see it in the status bar when moving mouse over the commented cell
        comment1.setAuthor("Apache Software Foundation");
        // The first way to assign comment to a cell is via HSSFCell.setCellComment method
        cell1.setCellComment(comment1);
        //create another cell in row 6
        HSSFCell cell2 = sheet.createRow(6).createCell(1);
        cell2.setCellValue(36.6);
        HSSFComment comment2 = patr.createComment(new HSSFClientAnchor(0, 0, 0, 0, (short) 4, 8, (short) 6, 11));
        //modify background color of the comment
        comment2.setFillColor(204, 236, 255);
        HSSFRichTextString string = new HSSFRichTextString("Normal body temperature");
        //apply custom font to the text in the comment
        HSSFFont font = wb.createFont();
        font.setFontName("Arial");
        font.setFontHeightInPoints((short) 10);
        font.setBold(true);
        font.setColor(HSSFColorPredefined.RED.getIndex());
        string.applyFont(font);
        comment2.setString(string);
        //by default comments are hidden. This one is always visible.
        comment2.setVisible(true);
        comment2.setAuthor("Bill Gates");
        /**
             * The second way to assign comment to a cell is to implicitly specify its row and column.
             * Note, it is possible to set row and column of a non-existing cell.
             * It works, the comment is visible.
             */
        comment2.setRow(6);
        comment2.setColumn(1);
        FileOutputStream out = new FileOutputStream("poi_comment.xls");
        wb.write(out);
        out.close();
    } finally {
        wb.close();
    }
}

Example 13 with HSSFPatriarch

use of org.apache.poi.hssf.usermodel.HSSFPatriarch in project poi by apache.

the class TestDrawingShapes method testReadWriteRectangle.

/**
     * create a rectangle, save the workbook, read back and verify that all shape properties are there
     */
@Test
public void testReadWriteRectangle() throws IOException {
    HSSFWorkbook wb1 = new HSSFWorkbook();
    HSSFSheet sheet = wb1.createSheet();
    HSSFPatriarch drawing = sheet.createDrawingPatriarch();
    HSSFClientAnchor anchor = new HSSFClientAnchor(10, 10, 50, 50, (short) 2, 2, (short) 4, 4);
    anchor.setAnchorType(AnchorType.MOVE_DONT_RESIZE);
    assertEquals(AnchorType.MOVE_DONT_RESIZE, anchor.getAnchorType());
    //noinspection deprecation
    anchor.setAnchorType(AnchorType.MOVE_DONT_RESIZE.value);
    assertEquals(AnchorType.MOVE_DONT_RESIZE, anchor.getAnchorType());
    HSSFSimpleShape rectangle = drawing.createSimpleShape(anchor);
    rectangle.setShapeType(HSSFSimpleShape.OBJECT_TYPE_RECTANGLE);
    rectangle.setLineWidth(10000);
    rectangle.setFillColor(777);
    assertEquals(rectangle.getFillColor(), 777);
    assertEquals(10000, rectangle.getLineWidth());
    rectangle.setLineStyle(10);
    assertEquals(10, rectangle.getLineStyle());
    assertEquals(rectangle.getWrapText(), HSSFSimpleShape.WRAP_SQUARE);
    rectangle.setLineStyleColor(1111);
    rectangle.setNoFill(true);
    rectangle.setWrapText(HSSFSimpleShape.WRAP_NONE);
    rectangle.setString(new HSSFRichTextString("teeeest"));
    assertEquals(rectangle.getLineStyleColor(), 1111);
    EscherContainerRecord escherContainer = HSSFTestHelper.getEscherContainer(rectangle);
    assertNotNull(escherContainer);
    EscherRecord childById = escherContainer.getChildById(EscherOptRecord.RECORD_ID);
    assertNotNull(childById);
    EscherProperty lookup = ((EscherOptRecord) childById).lookup(EscherProperties.TEXT__TEXTID);
    assertNotNull(lookup);
    assertEquals(((EscherSimpleProperty) lookup).getPropertyValue(), "teeeest".hashCode());
    assertEquals(rectangle.isNoFill(), true);
    assertEquals(rectangle.getWrapText(), HSSFSimpleShape.WRAP_NONE);
    assertEquals(rectangle.getString().getString(), "teeeest");
    HSSFWorkbook wb2 = HSSFTestDataSamples.writeOutAndReadBack(wb1);
    wb1.close();
    sheet = wb2.getSheetAt(0);
    drawing = sheet.getDrawingPatriarch();
    assertEquals(1, drawing.getChildren().size());
    HSSFSimpleShape rectangle2 = (HSSFSimpleShape) drawing.getChildren().get(0);
    assertEquals(HSSFSimpleShape.OBJECT_TYPE_RECTANGLE, rectangle2.getShapeType());
    assertEquals(10000, rectangle2.getLineWidth());
    assertEquals(10, rectangle2.getLineStyle());
    assertEquals(anchor, rectangle2.getAnchor());
    assertEquals(rectangle2.getLineStyleColor(), 1111);
    assertEquals(rectangle2.getFillColor(), 777);
    assertEquals(rectangle2.isNoFill(), true);
    assertEquals(rectangle2.getString().getString(), "teeeest");
    assertEquals(rectangle.getWrapText(), HSSFSimpleShape.WRAP_NONE);
    rectangle2.setFillColor(3333);
    rectangle2.setLineStyle(9);
    rectangle2.setLineStyleColor(4444);
    rectangle2.setNoFill(false);
    rectangle2.setLineWidth(77);
    rectangle2.getAnchor().setDx1(2);
    rectangle2.getAnchor().setDx2(3);
    rectangle2.getAnchor().setDy1(4);
    rectangle2.getAnchor().setDy2(5);
    rectangle.setWrapText(HSSFSimpleShape.WRAP_BY_POINTS);
    rectangle2.setString(new HSSFRichTextString("test22"));
    HSSFWorkbook wb3 = HSSFTestDataSamples.writeOutAndReadBack(wb2);
    wb2.close();
    sheet = wb3.getSheetAt(0);
    drawing = sheet.getDrawingPatriarch();
    assertEquals(1, drawing.getChildren().size());
    rectangle2 = (HSSFSimpleShape) drawing.getChildren().get(0);
    assertEquals(HSSFSimpleShape.OBJECT_TYPE_RECTANGLE, rectangle2.getShapeType());
    assertEquals(rectangle.getWrapText(), HSSFSimpleShape.WRAP_BY_POINTS);
    assertEquals(77, rectangle2.getLineWidth());
    assertEquals(9, rectangle2.getLineStyle());
    assertEquals(rectangle2.getLineStyleColor(), 4444);
    assertEquals(rectangle2.getFillColor(), 3333);
    assertEquals(rectangle2.getAnchor().getDx1(), 2);
    assertEquals(rectangle2.getAnchor().getDx2(), 3);
    assertEquals(rectangle2.getAnchor().getDy1(), 4);
    assertEquals(rectangle2.getAnchor().getDy2(), 5);
    assertEquals(rectangle2.isNoFill(), false);
    assertEquals(rectangle2.getString().getString(), "test22");
    HSSFSimpleShape rect3 = drawing.createSimpleShape(new HSSFClientAnchor());
    rect3.setShapeType(HSSFSimpleShape.OBJECT_TYPE_RECTANGLE);
    HSSFWorkbook wb4 = HSSFTestDataSamples.writeOutAndReadBack(wb3);
    wb3.close();
    drawing = wb4.getSheetAt(0).getDrawingPatriarch();
    assertEquals(drawing.getChildren().size(), 2);
    wb4.close();
}

Example 14 with HSSFPatriarch

use of org.apache.poi.hssf.usermodel.HSSFPatriarch in project poi by apache.

the class TestDrawingShapes method testRemoveShapes.

@Test
public void testRemoveShapes() throws IOException {
    HSSFWorkbook wb1 = new HSSFWorkbook();
    HSSFSheet sheet = wb1.createSheet();
    HSSFPatriarch patriarch = sheet.createDrawingPatriarch();
    HSSFSimpleShape rectangle = patriarch.createSimpleShape(new HSSFClientAnchor());
    rectangle.setShapeType(HSSFSimpleShape.OBJECT_TYPE_RECTANGLE);
    int idx = wb1.addPicture(new byte[] { 1, 2, 3 }, Workbook.PICTURE_TYPE_JPEG);
    patriarch.createPicture(new HSSFClientAnchor(), idx);
    patriarch.createCellComment(new HSSFClientAnchor());
    HSSFPolygon polygon = patriarch.createPolygon(new HSSFClientAnchor());
    polygon.setPoints(new int[] { 1, 2 }, new int[] { 2, 3 });
    patriarch.createTextbox(new HSSFClientAnchor());
    HSSFShapeGroup group = patriarch.createGroup(new HSSFClientAnchor());
    group.createTextbox(new HSSFChildAnchor());
    group.createPicture(new HSSFChildAnchor(), idx);
    assertEquals(patriarch.getChildren().size(), 6);
    assertEquals(group.getChildren().size(), 2);
    assertEquals(HSSFTestHelper.getEscherAggregate(patriarch).getShapeToObjMapping().size(), 12);
    assertEquals(HSSFTestHelper.getEscherAggregate(patriarch).getTailRecords().size(), 1);
    HSSFWorkbook wb2 = HSSFTestDataSamples.writeOutAndReadBack(wb1);
    wb1.close();
    sheet = wb2.getSheetAt(0);
    patriarch = sheet.getDrawingPatriarch();
    assertEquals(HSSFTestHelper.getEscherAggregate(patriarch).getShapeToObjMapping().size(), 12);
    assertEquals(HSSFTestHelper.getEscherAggregate(patriarch).getTailRecords().size(), 1);
    assertEquals(patriarch.getChildren().size(), 6);
    group = (HSSFShapeGroup) patriarch.getChildren().get(5);
    group.removeShape(group.getChildren().get(0));
    assertEquals(HSSFTestHelper.getEscherAggregate(patriarch).getShapeToObjMapping().size(), 10);
    assertEquals(HSSFTestHelper.getEscherAggregate(patriarch).getTailRecords().size(), 1);
    HSSFWorkbook wb3 = HSSFTestDataSamples.writeOutAndReadBack(wb2);
    wb2.close();
    sheet = wb3.getSheetAt(0);
    patriarch = sheet.getDrawingPatriarch();
    assertEquals(HSSFTestHelper.getEscherAggregate(patriarch).getShapeToObjMapping().size(), 10);
    assertEquals(HSSFTestHelper.getEscherAggregate(patriarch).getTailRecords().size(), 1);
    group = (HSSFShapeGroup) patriarch.getChildren().get(5);
    patriarch.removeShape(group);
    assertEquals(HSSFTestHelper.getEscherAggregate(patriarch).getShapeToObjMapping().size(), 8);
    assertEquals(HSSFTestHelper.getEscherAggregate(patriarch).getTailRecords().size(), 1);
    HSSFWorkbook wb4 = HSSFTestDataSamples.writeOutAndReadBack(wb3);
    wb3.close();
    sheet = wb4.getSheetAt(0);
    patriarch = sheet.getDrawingPatriarch();
    assertEquals(HSSFTestHelper.getEscherAggregate(patriarch).getShapeToObjMapping().size(), 8);
    assertEquals(HSSFTestHelper.getEscherAggregate(patriarch).getTailRecords().size(), 1);
    assertEquals(patriarch.getChildren().size(), 5);
    HSSFShape shape = patriarch.getChildren().get(0);
    patriarch.removeShape(shape);
    assertEquals(HSSFTestHelper.getEscherAggregate(patriarch).getShapeToObjMapping().size(), 6);
    assertEquals(HSSFTestHelper.getEscherAggregate(patriarch).getTailRecords().size(), 1);
    assertEquals(patriarch.getChildren().size(), 4);
    HSSFWorkbook wb5 = HSSFTestDataSamples.writeOutAndReadBack(wb4);
    wb4.close();
    sheet = wb5.getSheetAt(0);
    patriarch = sheet.getDrawingPatriarch();
    assertEquals(HSSFTestHelper.getEscherAggregate(patriarch).getShapeToObjMapping().size(), 6);
    assertEquals(HSSFTestHelper.getEscherAggregate(patriarch).getTailRecords().size(), 1);
    assertEquals(patriarch.getChildren().size(), 4);
    HSSFPicture picture = (HSSFPicture) patriarch.getChildren().get(0);
    patriarch.removeShape(picture);
    assertEquals(HSSFTestHelper.getEscherAggregate(patriarch).getShapeToObjMapping().size(), 5);
    assertEquals(HSSFTestHelper.getEscherAggregate(patriarch).getTailRecords().size(), 1);
    assertEquals(patriarch.getChildren().size(), 3);
    HSSFWorkbook wb6 = HSSFTestDataSamples.writeOutAndReadBack(wb5);
    wb5.close();
    sheet = wb6.getSheetAt(0);
    patriarch = sheet.getDrawingPatriarch();
    assertEquals(HSSFTestHelper.getEscherAggregate(patriarch).getShapeToObjMapping().size(), 5);
    assertEquals(HSSFTestHelper.getEscherAggregate(patriarch).getTailRecords().size(), 1);
    assertEquals(patriarch.getChildren().size(), 3);
    HSSFComment comment = (HSSFComment) patriarch.getChildren().get(0);
    patriarch.removeShape(comment);
    assertEquals(HSSFTestHelper.getEscherAggregate(patriarch).getShapeToObjMapping().size(), 3);
    assertEquals(HSSFTestHelper.getEscherAggregate(patriarch).getTailRecords().size(), 0);
    assertEquals(patriarch.getChildren().size(), 2);
    HSSFWorkbook wb7 = HSSFTestDataSamples.writeOutAndReadBack(wb6);
    wb6.close();
    sheet = wb7.getSheetAt(0);
    patriarch = sheet.getDrawingPatriarch();
    assertEquals(HSSFTestHelper.getEscherAggregate(patriarch).getShapeToObjMapping().size(), 3);
    assertEquals(HSSFTestHelper.getEscherAggregate(patriarch).getTailRecords().size(), 0);
    assertEquals(patriarch.getChildren().size(), 2);
    polygon = (HSSFPolygon) patriarch.getChildren().get(0);
    patriarch.removeShape(polygon);
    assertEquals(HSSFTestHelper.getEscherAggregate(patriarch).getShapeToObjMapping().size(), 2);
    assertEquals(HSSFTestHelper.getEscherAggregate(patriarch).getTailRecords().size(), 0);
    assertEquals(patriarch.getChildren().size(), 1);
    HSSFWorkbook wb8 = HSSFTestDataSamples.writeOutAndReadBack(wb7);
    wb7.close();
    sheet = wb8.getSheetAt(0);
    patriarch = sheet.getDrawingPatriarch();
    assertEquals(HSSFTestHelper.getEscherAggregate(patriarch).getShapeToObjMapping().size(), 2);
    assertEquals(HSSFTestHelper.getEscherAggregate(patriarch).getTailRecords().size(), 0);
    assertEquals(patriarch.getChildren().size(), 1);
    HSSFTextbox textbox = (HSSFTextbox) patriarch.getChildren().get(0);
    patriarch.removeShape(textbox);
    assertEquals(HSSFTestHelper.getEscherAggregate(patriarch).getShapeToObjMapping().size(), 0);
    assertEquals(HSSFTestHelper.getEscherAggregate(patriarch).getTailRecords().size(), 0);
    assertEquals(patriarch.getChildren().size(), 0);
    HSSFWorkbook wb9 = HSSFTestDataSamples.writeOutAndReadBack(wb8);
    wb8.close();
    sheet = wb9.getSheetAt(0);
    patriarch = sheet.getDrawingPatriarch();
    assertEquals(HSSFTestHelper.getEscherAggregate(patriarch).getShapeToObjMapping().size(), 0);
    assertEquals(HSSFTestHelper.getEscherAggregate(patriarch).getTailRecords().size(), 0);
    assertEquals(patriarch.getChildren().size(), 0);
    wb9.close();
}

Example 15 with HSSFPatriarch

use of org.apache.poi.hssf.usermodel.HSSFPatriarch in project poi by apache.

the class TestDrawingShapes method testReadExistingRectangle.

/* assert shape properties when reading shapes from a existing workbook */
@Test
public void testReadExistingRectangle() throws IOException {
    HSSFWorkbook wb = HSSFTestDataSamples.openSampleWorkbook("drawings.xls");
    HSSFSheet sheet = wb.getSheet("rectangles");
    HSSFPatriarch drawing = sheet.getDrawingPatriarch();
    assertEquals(1, drawing.getChildren().size());
    HSSFSimpleShape shape = (HSSFSimpleShape) drawing.getChildren().get(0);
    assertEquals(shape.isNoFill(), false);
    assertEquals(shape.getLineStyle(), HSSFShape.LINESTYLE_DASHDOTGEL);
    assertEquals(shape.getLineStyleColor(), 0x616161);
    assertEquals(HexDump.toHex(shape.getFillColor()), shape.getFillColor(), 0x2CE03D);
    assertEquals(shape.getLineWidth(), HSSFShape.LINEWIDTH_ONE_PT * 2);
    assertEquals(shape.getString().getString(), "POItest");
    assertEquals(shape.getRotationDegree(), 27);
    wb.close();
}