CODEWITHSUNDEEP
excel-2007apache-poiopenxml
Stefan Chonov
Stefan Chonov

Reputation: 121

How to add hyperlink to Excel simple shape using apache poi?

I use Microsoft Excel 2007. I have a simple shape on the first excel sheet. I would like to add a hyperlink on this simple shape which refer to another sheet on the specific row and column. I make a research about this but I only found examples which demonstrate how to add a hyperlink to a given cell. How can I add a hyperlink to a given simple shape with the help of Apache POI?

Upvotes: 1

Views: 1541

Answers (1)

Stefan Chonov
Stefan Chonov

Reputation: 121

I found solution of my issue. I hope it will help to someone else.

    // Example for hyperlink address
    // String hyperlinkAddress = sheet.getPackagePart().getPartName() + "/#'Sheet name'!Cell Address"

    // My hyperlink address
    String hyperlinkAddress = sheet.getPackagePart().getPartName()
                + "/#'List_of_Items'!A12";

    // Create URI object which will containing our hyperlink address.
    URI uri = new URI(null, null, hyperlinkAddress, null, null);

    // Add relationship to XSSFDrawing object. 
    aPatriarch.getPackagePart().addRelationship(uri, TargetMode.INTERNAL,
                    XSSFRelation.SHEET_HYPERLINKS.getRelation());

    // We need to extract the ID of the Relationship.
    // We'll set the ID of the hyperlink to be the same as ID of the Relationship.
    // To find appropriate Relationship we will traverse through all Relationships in the 'aPatriarch' object. 
    String relationshipId = null;
    PackageRelationshipCollection prc = aPatriarch.getPackagePart().getRelationships();
    for (PackageRelationship pr : prc) {
        URI targetURI = pr.getTargetURI();
        String target = targetURI.getPath();
        if (target.equals(hyperlinkAddress)) {
           relationshipId = pr.getId();
           break;
        }
    }

    // Create the hyperlink object
    CTHyperlink hyperlink = CTHyperlink.Factory.newInstance();
    // Set ID of hyperlink to be the same as Relationship ID
    hyperlink.setId(relationshipId);

    // Add hyperlink to the given shape
    shape.getCTShape().getNvSpPr().getCNvPr().setHlinkClick(hyperlink);

Upvotes: 2

Related Questions