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，其中可以从 XSSFDrawing 获取PackagePart。

完整示例：

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