java - 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 开始.我们可以从XSSFPicture得到CTPicture .

要完成第二个，我们需要 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();

 }

}