在java中添加org.apache.poi.xssf.usermodel.XSSFTextBox的超链接

Question

我想使用Textbox添加一个超链接(指具有不同表单的同一文档，例如E1(。超链接是在这里创建的，但这不是指表E1。

下面是我的代码。

XSSFSheet sheet = templateWorkbook.getSheetAt(0);
XSSFDrawing drawing = sheet.createDrawingPatriarch();
anchor.setAnchorType(AnchorType.MOVE_DONT_RESIZE);
XSSFTextBox textBox = drawing.createTextbox(anchor);
textBox.setFillColor(112, 80, 151);
textBox.setVerticalAlignment(VerticalAlignment.CENTER);
XSSFRichTextString text = new XSSFRichTextString("text");

textBox.setTopInset(0.25D);

textBox.setText("text");
CTTextCharacterProperties rpr = textBox.getCTShape().getTxBody().getPArray(0).getRArray(0).getRPr();
Color color = Color.white;
rpr.addNewSolidFill().addNewSrgbClr().setVal(new byte[]{(byte)color.getRed(),(byte)color.getGreen(),(byte)color.getBlue()});
CTHyperlink hyperlink = CTHyperlink.Factory.newInstance();


textBox.getCTShape().getNvSpPr().getCNvPr().setHlinkClick(hyperlink);

Answer 1

关于图片的相同问题，请参阅Apache poi在图片中放置超链接。

不直接在CTHyperlink中设置超链接目标。相反，CTHyperlink指的是图纸的包内关系中的参考。然后，包关系引用超链接目标。因此，需要创建该包关系，并将其id设置为CTHyperlink。

使用当前apache poi 5.0.0:测试并工作的完整示例

import java.io.FileInputStream;
import java.io.FileOutputStream;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.*;
import org.openxmlformats.schemas.drawingml.x2006.spreadsheetDrawing.CTShape;
import org.openxmlformats.schemas.drawingml.x2006.spreadsheetDrawing.CTShapeNonVisual;
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 CreateExcelTextBoxHyperlink {

private static void setHyperlinkToShape(XSSFSimpleShape shape, String hyperlinkurl) throws Exception {
XSSFDrawing drawing = shape.getDrawing();
PackageRelationship packagerelationship = 
drawing.getPackagePart().addExternalRelationship(hyperlinkurl, PackageRelationshipTypes.HYPERLINK_PART);
String rid = packagerelationship.getId();
CTShape ctshape = shape.getCTShape();
CTShapeNonVisual crshapenonvisual = ctshape.getNvSpPr();
if (crshapenonvisual == null) crshapenonvisual = ctshape.addNewNvSpPr();
CTNonVisualDrawingProps ctnonvisualdrawingprops = crshapenonvisual.getCNvPr();
if (ctnonvisualdrawingprops == null) ctnonvisualdrawingprops = crshapenonvisual.addNewCNvPr();
CTHyperlink cthyperlink = ctnonvisualdrawingprops.getHlinkClick();
if (cthyperlink == null) cthyperlink = ctnonvisualdrawingprops.addNewHlinkClick();
cthyperlink.setId(rid);
}
public static void main(String[] args) throws Exception {
XSSFWorkbook workbook = (XSSFWorkbook)WorkbookFactory.create(new FileInputStream("./Excel.xlsx"));
XSSFSheet sheet = workbook.getSheetAt(0);

XSSFDrawing drawing = sheet.createDrawingPatriarch();
XSSFClientAnchor anchor =  drawing.createAnchor(0, 0, 0, 0, 2, 2, 3, 3);
anchor.setAnchorType(ClientAnchor.AnchorType.MOVE_DONT_RESIZE);
XSSFTextBox textBox = drawing.createTextbox(anchor);
textBox.setFillColor(112, 80, 151);
textBox.setVerticalAlignment(VerticalAlignment.CENTER);
textBox.setTopInset(0.25D);

XSSFRichTextString text = workbook.getCreationHelper().createRichTextString("text");
XSSFFont font = workbook.createFont();
font.setColor(IndexedColors.WHITE.getIndex()); 
text.applyFont(font);
textBox.setText(text);

setHyperlinkToShape(textBox, "#Sheet2!E1");
FileOutputStream out = new FileOutputStream("./ExcelNew.xlsx");
workbook.write(out);
out.close();
workbook.close();
}
}

超链接目标URI#Sheet2!E1是指名为Sheet2的表中的单元格E1。

由于apache poi将所有超链接目标作为需要对URI进行编码的URIs，因此目标工作表名称不得包含空格。如果目标图纸名称为Sheet 2，则目标URI必须为#'Sheet%202'!E1。但Excel期望#'Sheet 2'!E1。因此，对于Excel，这些目标根本不是URIs。但对于apache poi来说，它们是。