Apache Poi在图像中放置超链接

Question

这是我用于在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();
    }
}

这是我使用此方法创建图标的方法：

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

是否可以在此图标中放置一个超链接以转到同一电子表格中的另一个工作表或网站？

我读到的内容显然没有POI支持，但是可以使用底层的低级API来实现。但是我还不能真正成功地使用它。

有什么建议？

Answer 1

如果只支持可以XSSF，那么实际上可以使用底层的低级对象来完成。

如何开始？使用Excel其中包含超链接的图片来创建工作簿。然后解压缩*.xlsx并查看/xl/drawings/drawing1.xml。在那里您会发现：

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

因此，图片具有非可视图片设置，该设置具有非可视属性，该属性具有设置的超链接单击rId。

rId关系的要点，所以看xl/drawings/_rels/drawing1.xml.rels。在那里，您将找到设置为的超链接目标rId。

因此，我们需要设置具有非视觉属性并具有超链接单击的非视觉图片设置。但是我们还需要设置关系来获取rId。

首先，我们从org.openxmlformats.schemas.drawingml.x2006.spreadsheetDrawing.CTPicture开始。在CTPicture我们可以从XSSFPicture。

要执行第二步，我们需要PackagePart.addExternalRelationship，PackagePart可以从XSSFDrawing获得。

完整的例子：

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();

 }

}