0

From this answer regarding embedding an object into an Excel spreadsheet using Apache POI:

Workbook wb1 = new XSSFWorkbook();
Sheet sh = wb1.createSheet();
int picIdx = wb1.addPicture(getSamplePng(), Workbook.PICTURE_TYPE_PNG);
byte samplePPTX[] = getSamplePPT(true);
int oleIdx = wb1.addOlePackage(samplePPTX, "dummy.pptx", "dummy.pptx", "dummy.pptx");

Drawing<?> pat = sh.createDrawingPatriarch();
ClientAnchor anchor = pat.createAnchor(0, 0, 0, 0, 1, 1, 3, 6);
pat.createObjectData(anchor, oleIdx, picIdx);

This creates the embedded object with the image from getSamplePng() as the preview image. This image is anchored to the cell at row = 1, column = 1, and stretches to row = 6, column = 3. Unless the image aspect ratio perfectly matches, this results in a distorted image.

The Picture interface provides the resize method, but there doesn't appear to be a way to get a Picture instance from ObjectData.

The following works if the image dimensions are within those of the cell it is being anchored to:

ClientAnchor anchor = drawing.createAnchor(0, 
                                           0, 
                                           imgDim.width * Units.EMU_PER_PIXEL,
                                           imgDim.height * Units.EMU_PER_PIXEL,
                                           columnIndex,
                                           rowIndex,
                                           columnIndex,
                                           rowIndex);

This does not work if the image is larger than the cell dimensions; the image ends up stretched to the bounds of the cell.

I have also tried setAnchorType(AnchorType.MOVE_DONT_RESIZE), which doesn't appear to have any effect.

How can I size the preview image to its original size when using createObjectData to embed an object?

tvStatic
  • 921
  • 1
  • 9
  • 26

1 Answers1

0

The XSSFClientAnchor class provides a constructor that allows to supply the top-left cell (as a CTPoint2D object) and an object size (in the form of CTPositiveSize2D). However, this constructor is protected.

The workaround is to extend this class to one that accepts a top-left cell coordinates and a width and height (in EMU units):

class SizedClientAnchor extends XSSFClientAnchor
{
    private SizedClientAnchor(XSSFSheet sheet, int columnIndex, int rowIndex, int w, int h)
    {
        super((XSSFSheet) cell.getSheet(), createCTMarker(columnIndex, rowIndex), getSize2D(w, h));
    }

    private static CTMarker createCTMarker(int columnIndex, int rowIndex)
    {
        CTMarker ret = CTMarker.Factory.newInstance();
        ret.setCol(columnIndex);
        ret.setColOff(0);
        ret.setRow(rowIndex);
        ret.setRowOff(0);
        return ret;
    }

    private static CTPositiveSize2D getSize2D(int w, int h)
    {
        CTPositiveSize2D ret = CTPositiveSize2D.Factory.newInstance();
        ret.setCx(w);
        ret.setCy(h);
        return ret;
    }
}

The original code becomes something like the following:

ClientAnchor anchor = new SizedClientAnchor(sh, 
                                            columnIndex, 
                                            rowIndex, 
                                            imgDim.width * Units.EMU_PER_PIXEL,
                                            imgDim.height * Units.EMU_PER_PIXEL);

Note however, that the width of the resulting image will be incorrect if the font for the columns that the image goes through is different from the "standard" font (Calibri or Arial 11pt).

The sizing code is in calcCell() in XSSFClientAnchor. This uses Units.columnWidthToEMU(), which in turn uses Units.charactersToEMU(). This uses DEFAULT_CHARACTER_WIDTH, which is documented as applying only for the default font (Calibri or Arial).

Workarounds for this would involve re-implementing the Units.charactersToEMU() and XSSFClientAnchor.calcCell() to apply to the applicable font for the applicable cell(s).

tvStatic
  • 921
  • 1
  • 9
  • 26