CODEWITHSUNDEEP
javaapache-poi
kscherrer
kscherrer

Reputation: 5766

How to move images in an excel sheet with java

I have an XSSFSheet with images at the end of my used rows. When adding a new row, I would like to shift all images one row down. I have found this similar question about moving charts, and have tried to use the part of the answer that does the actual moving of the charts, because It looked like it would work for images as well.

java.util.List<CTTwoCellAnchor> drawingAnchors = ((XSSFDrawing)sheet.getDrawingPatriarch()).getCTDrawing().getTwoCellAnchorList();
for (CTTwoCellAnchor drawingAnchor : drawingAnchors) {
    int fromRow = drawingAnchor.getFrom().getRow();
    int toRow = drawingAnchor.getTo().getRow();
    if (fromRow >= startRow) {
        drawingAnchor.getFrom().setRow(fromRow + n);
        drawingAnchor.getTo().setRow(toRow + n);
    }
}

but this did not work but throws a NoClassDefFoundError instead.
(Edit: I now found out that this error can be solved by providing the full jar of all of the schemas ooxml-schemas as mentioned in FAQ-N10025. thanks @Axel Richter)

After trying out several different approaches, I found a way to do it. Since it took me so long, and there is no info about this anywhere yet, I decided to post my findings here on SO.
Please find my solution in my own answer. Cheers

Upvotes: 0

Views: 679

Answers (1)

kscherrer
kscherrer

Reputation: 5766

The following code gets the Drawing Patriarch of the sheet, iterates over all it's shapes, and if the shape is of type XSSFPicture it modifies the rowindexes through its XSSFClientAnchor.

int moveRowsBy = 1; // 1 will move the images 1 row down. moveRowsBy can be negative to move up

XSSFDrawing drawing = sheet.getDrawingPatriarch();
for (XSSFShape shape : drawing.getShapes()) {
    if (shape instanceof XSSFPicture){
        XSSFClientAnchor anchor = ((XSSFPicture)shape).getClientAnchor();

        anchor.setRow1(anchor.getRow1() +moveRowsBy); 
        anchor.setRow2(anchor.getRow2() +moveRowsBy);

        // if needed you could change column too, using one of these:
        // anchor.setCol1(newColumnInt)
        // anchor.setCol1(anchor.getCol1() + moveColsBy)
    }
}

Upvotes: 3

Related Questions