问题描述
这是我用于在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 .我们可以从 XSSFPicture CTPicture .
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在图像中放置超链接的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！