Apache poi 在图像中放置超链接 [英] Apache poi putting hyperlink in images

问题描述

这是我用来在 Excel 中插入图像作为图标的方法:

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?

我读到显然 POI 不支持，但使用底层的低级 API 是可能的.但是我并没有真正成功地使用它.

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.

有什么建议吗?

推荐答案

如果你只支持 XSSF 就可以了，那么这真的可以使用底层的低级对象来完成.

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

如何开始?使用 Excel 创建一个工作簿，其中包含带有超链接的图片.然后解压缩 *.xlsx 并查看 /xl/drawings/drawing1.xml.在那里你会发现:

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>
...

因此图片具有非视觉图片设置，具有非视觉属性，具有设置了 rId 的超链接点击.

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

rId 指向一个关系，所以查看 xl/drawings/_rels/drawing1.xml.rels.在那里，您会发现设置为该 rId 的超链接目标.

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.

因此我们需要设置具有非视觉属性的非视觉图片设置，并具有超链接点击.但我们还需要设置获取 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.

首先我们从 org.openxmlformats.schemas.drawingml.x2006.spreadsheetDrawing.CTPicture.我们可以从 CTPicture>XSSFPicture.

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

要做第二个，我们需要PackagePart.addExternalRelationship 其中 PackagePart 可以从 XSSFDrawing.

To do the second we need PackagePart.addExternalRelationship where PackagePart can be got from 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();

 }

}

这篇关于Apache poi 在图像中放置超链接的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！