java - Get all shapes in ShapeGroup

Question

My code basically like this:

worksheet.getDrawingPatriarch().getShapes().get(0)

This will return the XSSFShapeGroup object, but then from this point I have no idea how to get XSSFSimpleShape or XSSFShape in the group. I want to output the position(row & col) of every shape in that group but I don't know how to extract them from the group.

Answer 1

To get XSSFSimpleShape or XSSFShape out of a shape group (XSSFShapeGroup) is not possible using apache poi until now.

It would be possible to get the low level CTShapes starting from the CTGroupShape. But "to output the position(row & col) of every shape in that group" is nearly impossible since there is only one anchor (row, col) for the group and all the grouped shapes are positioned relative to that anchor in units EMU (English Metric Units).

So we can get the anchor of the shape group and the position of the group members in EMU relative to that anchor:

import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.*;
import org.apache.poi.openxml4j.exceptions.InvalidFormatException;

import org.openxmlformats.schemas.drawingml.x2006.spreadsheetDrawing.CTConnector;
import org.openxmlformats.schemas.drawingml.x2006.spreadsheetDrawing.CTGraphicalObjectFrame;
import org.openxmlformats.schemas.drawingml.x2006.spreadsheetDrawing.CTPicture;
import org.openxmlformats.schemas.drawingml.x2006.spreadsheetDrawing.CTGroupShape;
import org.openxmlformats.schemas.drawingml.x2006.spreadsheetDrawing.CTShape;
import org.openxmlformats.schemas.drawingml.x2006.spreadsheetDrawing.CTTwoCellAnchor;

import java.io.*;

class ReadGroupedShapes {

 public static void main(String[] args) {
  try {

   InputStream inp = new FileInputStream("Shapegroup.xlsx");
   Workbook wb = WorkbookFactory.create(inp);
  
   Sheet sheet = wb.getSheetAt(0);

   XSSFDrawing drawing = (XSSFDrawing)sheet.getDrawingPatriarch();

   for (XSSFShape shape : drawing.getShapes()) { 
    // possible:   XSSFConnector, XSSFGraphicFrame, XSSFPicture, XSSFShapeGroup, XSSFSimpleShape
    if (shape instanceof XSSFConnector) {
     //System.out.println(((XSSFConnector)shape).getCTConnector());
     
    } else if (shape instanceof XSSFGraphicFrame) {
     //System.out.println(((XSSFGraphicFrame)shape).getCTGraphicalObjectFrame());

    } else if (shape instanceof XSSFPicture) {
     //System.out.println(((XSSFPicture)shape).getCTPicture());

    } else if (shape instanceof XSSFShapeGroup) { //we have a shape group
     //System.out.println(((XSSFShapeGroup)shape).getCTGroupShape());
     System.out.println("Whole group is anchored upper left:");
     int groupRow = ((XSSFClientAnchor)shape.getAnchor()).getRow1();
     long groupRowDy = ((XSSFClientAnchor)shape.getAnchor()).getDy1();
     System.out.print("Row: " + groupRow);
     System.out.println(" + " + groupRowDy + " EMU");

     int groupCol = ((XSSFClientAnchor)shape.getAnchor()).getCol1();
     long groupColDx = ((XSSFClientAnchor)shape.getAnchor()).getDx1();
     System.out.print("Col: " + groupCol);
     System.out.println(" + " + groupColDx + " EMU");

     //go through all shapes in the group
     for (CTConnector lowLewelConnector : ((XSSFShapeGroup)shape).getCTGroupShape().getCxnSpList()) {
      //System.out.println(lowLewelConnector);
      System.out.println("A connector is in the group:");
      String cxnName = lowLewelConnector.getNvCxnSpPr().getCNvPr().getName();
      String cxnDescr = lowLewelConnector.getNvCxnSpPr().getCNvPr().getDescr();
      System.out.println("Name: " + cxnName + ((!"".equals(cxnDescr)) ? ": " + cxnDescr : ""));
      System.out.println("positioned upper left:");
      long connectorDy = lowLewelConnector.getSpPr().getXfrm().getOff().getY();
      System.out.println("Row " + groupRow + " + " + groupRowDy + " + " + connectorDy + " EMU");

      long connectorDx = lowLewelConnector.getSpPr().getXfrm().getOff().getX();
      System.out.println("Col " + groupCol + " + " + groupColDx + " + " + connectorDx + " EMU");

     }
     for (CTGraphicalObjectFrame lowLewelGrFrame : ((XSSFShapeGroup)shape).getCTGroupShape().getGraphicFrameList()) {
      //System.out.println(lowLewelGrFrame);

     }
     for (CTPicture lowLewelPic : ((XSSFShapeGroup)shape).getCTGroupShape().getPicList()) {
      //System.out.println(lowLewelPic);
      System.out.println("A picture is in the group:");
      String picName = lowLewelPic.getNvPicPr().getCNvPr().getName();
      String picDescr = lowLewelPic.getNvPicPr().getCNvPr().getDescr();
      System.out.println("Name: " + picName + ((!"".equals(picDescr)) ? ": " + picDescr : ""));
      System.out.println("positioned upper left:");
      long pictDy = lowLewelPic.getSpPr().getXfrm().getOff().getY();
      System.out.println("Row " + groupRow + " + " + groupRowDy + " + " + pictDy + " EMU");

      long pictDx = lowLewelPic.getSpPr().getXfrm().getOff().getX();
      System.out.println("Col " + groupCol + " + " + groupColDx + " + " + pictDx + " EMU");

     }
     for (CTGroupShape lowLewelGroupShape : ((XSSFShapeGroup)shape).getCTGroupShape().getGrpSpList()) {
      //System.out.println(lowLewelGroupShape);

     }
     for (CTShape lowLewelShape : ((XSSFShapeGroup)shape).getCTGroupShape().getSpList()) {
      //System.out.println(lowLewelShape);
      System.out.println("A simple shape is in the group:");
      String spName = lowLewelShape.getNvSpPr().getCNvPr().getName();
      String spDescr = lowLewelShape.getNvSpPr().getCNvPr().getDescr();
      System.out.println("Name: " + spName + ((!"".equals(spDescr)) ? ": " + spDescr : ""));
      System.out.println("positioned upper left:");
      long simpleShapeDy = lowLewelShape.getSpPr().getXfrm().getOff().getY();
      System.out.println("Row " + groupRow + " + " + groupRowDy + " + " + simpleShapeDy + " EMU");

      long simpleShapeDx = lowLewelShape.getSpPr().getXfrm().getOff().getX();
      System.out.println("Col " + groupCol + " + " + groupColDx + " + " + simpleShapeDx + " EMU");

     }

    } else if (shape instanceof XSSFSimpleShape) {
     //System.out.println(((XSSFSimpleShape)shape).getCTShape());

    }
   }


  } catch (InvalidFormatException ifex) {
  } catch (FileNotFoundException fnfex) {
  } catch (IOException ioex) {
  }
 }
}

---

In October 2016, current apache poi version was 3.14 or 3.15. Now in 2021 we are at version 5.0.0 and things have changed.

Now (since apache poi 3.17) there is ShapeContainer which provides an iterator over all shapes. And XSSFShapeGroup also implements ShapeContainer. So the above usage the low level classes is not more necessary. To get all shapes out of a XSSFDrawing and/or a XSSFShapeGroup is now as simple as:

import org.apache.poi.ss.usermodel.WorkbookFactory;
import org.apache.poi.ss.usermodel.ShapeContainer;
import org.apache.poi.xssf.usermodel.*;

import java.io.FileInputStream;

class ReadGroupedShapes {

 static void traverseShapeContainer(ShapeContainer<XSSFShape> container) {
  for (XSSFShape shape : container) { 
   // possible:   XSSFConnector, XSSFGraphicFrame, XSSFPicture, XSSFShapeGroup, XSSFSimpleShape
   if (shape instanceof XSSFConnector) {
    XSSFConnector connector = (XSSFConnector)shape;
    System.out.println(connector);

   } else if (shape instanceof XSSFGraphicFrame) {
    XSSFGraphicFrame graphicFrame = (XSSFGraphicFrame)shape;
    System.out.println(graphicFrame);

   } else if (shape instanceof XSSFPicture) {
    XSSFPicture picture = (XSSFPicture)shape;
    System.out.println(picture);

   } else if (shape instanceof XSSFShapeGroup) { //we have a shape group
    XSSFShapeGroup shapeGroup = (XSSFShapeGroup)shape;
    System.out.println(shapeGroup);

    traverseShapeContainer(shapeGroup); // we now can simply get the XSSFShapeGroup as ShapeContainer<XSSFShape>

   } else if (shape instanceof XSSFSimpleShape) {
    XSSFSimpleShape simpleShape = (XSSFSimpleShape)shape;
    System.out.println(simpleShape);

   }
  }

 }

 public static void main(String[] args) throws Exception {
  FileInputStream in = new FileInputStream("Shapegroup.xlsx");
  XSSFWorkbook wb = (XSSFWorkbook)WorkbookFactory.create(in);
  XSSFSheet sheet = wb.getSheetAt(0);
  XSSFDrawing drawing = sheet.getDrawingPatriarch();
  traverseShapeContainer(drawing);
 }
}