java - Apache poi putting hyperlink in images

Question

This is a Method I use for inserting images as icons in Excel:

public void insertIcons(String URL, Sheet sheet, int colBegin, int colEnd, int rowBegin, int rowEnd) {
    try {
        InputStream iconInput = new FileInputStream(URL);
        byte[] byteTransf = IOUtils.toByteArray(iconInput);
        int pictureIdx = workbook.addPicture(byteTransf, org.apache.poi.ss.usermodel.Workbook.PICTURE_TYPE_PNG);
        iconInput.close();


        CreationHelper helper = workbook.getCreationHelper();
        Drawing drawingIcon = sheet.createDrawingPatriarch();

        ClientAnchor anchorIcon = helper.createClientAnchor();
        anchorIcon.setCol1(colBegin);
        anchorIcon.setCol2(colEnd);
        anchorIcon.setRow1(rowBegin);
        anchorIcon.setRow2(rowEnd);

        Picture iconReady = drawingIcon.createPicture(anchorIcon, pictureIdx);
        iconReady.resize(1);

    } catch (Exception e) {
        e.printStackTrace();
    }
}

This is me using this method to create the Icon:

insertIcons(".idea/Icons/table.png", sheetName, 4, 4, 6, 9);

Is it possible to put a Hyperlink in this Icon to either go to another Sheet in this same Spreadsheet, or to a website?

I read that apparently there is no support in POI, but it could be possible using the underlying lowlevel-API. However I haven't been able to actually succeed in using it.

Any suggestions?

Answer 1

If you are fine with only supporting XSSF, then this really can be done using the underlying low level objects.

How to start? Create a workbook using Excel having pictures having hyperlinks in it. Then unzip the *.xlsx and have a look into the /xl/drawings/drawing1.xml. There you will find:

<xdr:pic>
 <xdr:nvPicPr>
  <xdr:cNvPr id="1" name="Picture 1" descr="Picture">
   <a:hlinkClick r:id="rId2"/>
  </xdr:cNvPr>
...

So the picture has non visual picture settings having non visual properties having a hyperlink click having a rId set.

The rId points to a relationship, so look at xl/drawings/_rels/drawing1.xml.rels. There you will find the hyperlink target set to that rId.

So we need setting the non visual picture settings having non visual properties having a hyperlink click. But also we need setting the relationship for getting the rId.

To do the first we start at org.openxmlformats.schemas.drawingml.x2006.spreadsheetDrawing.CTPicture. The CTPicture we can get from XSSFPicture.

To do the second we need PackagePart.addExternalRelationship where PackagePart can be got from XSSFDrawing.

Complete Example:

import java.io.*;

import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.*;
import org.apache.poi.ss.usermodel.ClientAnchor.AnchorType;
import org.apache.poi.util.IOUtils;

import org.openxmlformats.schemas.drawingml.x2006.spreadsheetDrawing.CTPicture;
import org.openxmlformats.schemas.drawingml.x2006.spreadsheetDrawing.CTPictureNonVisual;
import org.openxmlformats.schemas.drawingml.x2006.main.CTNonVisualDrawingProps;
import org.openxmlformats.schemas.drawingml.x2006.main.CTHyperlink;

import org.apache.poi.openxml4j.opc.PackageRelationship;
import org.apache.poi.openxml4j.opc.PackageRelationshipTypes;

public class CreateExcelPicturesHyperlink {

 private static Picture drawImageOnExcelSheet(Sheet sheet, int row1, int col1, 
  int row2, int col2, String pictureurl, int picturetype) throws Exception {

  InputStream is = new FileInputStream(pictureurl);
  byte[] bytes = IOUtils.toByteArray(is);
  int pictureIdx = sheet.getWorkbook().addPicture(bytes, picturetype);
  is.close();

  CreationHelper helper = sheet.getWorkbook().getCreationHelper();

  Drawing drawing = sheet.createDrawingPatriarch();

  ClientAnchor anchor = helper.createClientAnchor();
  anchor.setAnchorType(AnchorType.MOVE_AND_RESIZE);

  anchor.setRow1(row1); //first anchor determines upper left position
  anchor.setCol1(col1); 
  anchor.setRow2(row2); //second anchor determines bottom right position
  anchor.setCol2(col2);

  Picture picture = drawing.createPicture(anchor, pictureIdx);
  return picture;
 }

 private static void setHyperlinkToPicture(Picture picture, String hyperlinkurl) throws Exception {
  if (picture instanceof XSSFPicture) {
   XSSFPicture xssfpicture = (XSSFPicture)picture;

   XSSFDrawing drawing = xssfpicture.getSheet().createDrawingPatriarch();
   PackageRelationship packagerelationship = 
    drawing.getPackagePart().addExternalRelationship(hyperlinkurl, PackageRelationshipTypes.HYPERLINK_PART);
   String rid = packagerelationship.getId();

   CTPicture ctpicture = xssfpicture.getCTPicture();
   CTPictureNonVisual ctpicturenonvisual = ctpicture.getNvPicPr();
   if (ctpicturenonvisual == null) ctpicturenonvisual = ctpicture.addNewNvPicPr();
   CTNonVisualDrawingProps ctnonvisualdrawingprops = ctpicturenonvisual.getCNvPr();
   if (ctnonvisualdrawingprops == null) ctnonvisualdrawingprops = ctpicturenonvisual.addNewCNvPr();
   CTHyperlink cthyperlink = ctnonvisualdrawingprops.getHlinkClick();
   if (cthyperlink == null) cthyperlink = ctnonvisualdrawingprops.addNewHlinkClick();
   cthyperlink.setId(rid);

  }
 }

 public static void main(String[] args) throws Exception {

  Workbook workbook = new XSSFWorkbook();
  Sheet sheet = workbook.createSheet("Sheet1");

  Picture picture = drawImageOnExcelSheet(sheet, 2, 2, 4, 4, "samplePict1.jpeg", Workbook.PICTURE_TYPE_JPEG);
  setHyperlinkToPicture(picture, "http://www.google.de");

  workbook.createSheet("Sheet2");
  picture = drawImageOnExcelSheet(sheet, 6, 2, 8, 4, "samplePict2.png", Workbook.PICTURE_TYPE_PNG);
  setHyperlinkToPicture(picture, "#Sheet2!B3");

  FileOutputStream out = new FileOutputStream("./CreateExcelPicturesHyperlink.xlsx");
  workbook.write(out);
  out.close();
  workbook.close();

 }

}