CODEWITHSUNDEEP
javaapache-poi
tdchien
tdchien

Reputation: 1

Put hyperlink into image in excel (Apache POI)

I can set hyperlink for cell in Apache POI, but I don't know how to put the hyperlink into an image (I'm using XSSF)

Here is the function of putting cell hyperlink :

/**
 * Helper function for putting hyperlink into specified cell
 * @param label
 * @param value
 * @param col
 * @param row
 * @param sheet
 */
private static void putImageHyperlink(Cell cell, CellStyle hyperlinkStyle, String value, Workbook wb) {
    try {
        CreationHelper createHelper = wb.getCreationHelper();
        Hyperlink link = createHelper.createHyperlink(Hyperlink.LINK_URL);
        link.setAddress(value);
        cell.setHyperlink(link);
        cell.setCellStyle(hyperlinkStyle);

        // Put hyperlink value
        cell.setCellValue(value);
    } catch (Exception e) {
        log.severe("Can't create hyperlink : " + Utils.exceptionToString(e));
    }
}

And here is the function for putting image into specified cell :

    /**
 * Put image into sheet at position [row,col]
 * @param sheet
 * @param col
 * @param row
 * @param imgData
 * @throws Exception
 */
private static void putImage(Workbook wb, Sheet sheet, int col, int row, byte[] imgData) throws Exception {
    try {
        Drawing drawing = sheet.createDrawingPatriarch();
        int pictureIdx = wb.addPicture(imgData, Workbook.PICTURE_TYPE_PICT);

        CreationHelper helper = wb.getCreationHelper();
        ClientAnchor anchor4 = helper.createClientAnchor();

        //set top-left corner of the picture,
        //subsequent call of Picture#resize() will operate relative to it
        anchor4.setCol1(col);
        anchor4.setRow1(row);
        anchor4.setCol2(col+1);
        anchor4.setRow2(row+1);

        drawing.createPicture(anchor4, pictureIdx);
    } catch (Exception ex) {
        log.severe("Exception : " + Utils.exceptionToString(ex));
    }
}

=> How to put hyperlink into picture created by command drawing.createPicture(anchor4, pictureIdx); ?

Thanks in advance!

Upvotes: 0

Views: 1420

Answers (1)

centic
centic

Reputation: 15880

When looking at how Excel stores this it seems to be stored differently for Images in the xl\drawings\_rels\drawing1.xml.rels and xl\drawings\drawing1.xml part of the XLSX file:

<Relationship Id="rId1" Target="http://poi.apache.org" TargetMode="External" Type="http://schemas.openxmlformats.org/officeDocument/2006/relationships/hyperlink"/>

<xdr:cNvPr descr="Picture" id="2" name="Picture 1"> <a:hlinkClick xmlns:r="http://schemas.openxmlformats.org/officeDocument/2006/relationships" r:id="rId1"/> </xdr:cNvPr>

POI does not support adding such hyperlinks via it's API yet, but you can use the underlying lowlevel-API as follows to first create the relationship for the hyperlink and then set the relation to the hyperlink in the Picture-object:

    PackageRelationship rel = ((XSSFDrawing)patriarch).getPackagePart().addRelationship(
            new URI("http://poi.apache.org"),
            TargetMode.EXTERNAL, "http://schemas.openxmlformats.org/officeDocument/2006/relationships/hyperlink");
    ((XSSFDrawing)patriarch).addRelation(rel.getId(),new POIXMLDocumentPart());

    CTPictureNonVisual nvPicPr = ((XSSFPicture)picture).getCTPicture().getNvPicPr();
    CTHyperlink hLinkClick = nvPicPr.getCNvPr().addNewHlinkClick();
    hLinkClick.setId(rel.getId());

Upvotes: 3

Related Questions