CODEWITHSUNDEEP
javaapache-poiuri
badvi93d
badvi93d

Reputation: 133

Excel Hyperlinks URI fragment not encoding

I have a string like

String uriString = "#'Tab1 Tab2'!A8";
URI uri = new URI(uriString);

This fails as it is not a well formed URI (there is a space). So i changed to

String uriString = "#'Tab1%20Tab2'!A8";
URI uri = new URI(uriString);

This is encoded correctly but the fragment becomes "'Tab1%20Tab2'!A8"

Then in the excel the link (attached to an image) points to "Tab1%20Tab2'!A8" which does not exists.

I tried to change constructor to

URI uri = new URI(null,null,uriString,null,null)

As a result now on the excel the link is "%23'Tab1 Tab2'!A8" which is now not working because it is the hashtag now that has been converted. This is driving me crazy.

Here is the code that builds the URI and sets the link

      String hyperlinkAddress = "#'"+destinationTab + "'!" + destinationCell + "";
      hyperlinkAddress = hyperlinkAddress.replaceAll(" ", "%20");
      URI hyperLinkUri;
try {
                    hyperLinkUri = new URI(hyperlinkAddress);
                    PackageRelationship packagerelationship = drawing.getPackagePart().addRelationship(hyperLinkUri,TargetMode.EXTERNAL, PackageRelationshipTypes.HYPERLINK_PART);
                    String rid = packagerelationship.getId();
                    if (cthyperlink == null) cthyperlink = ctnonvisualdrawingprops.addNewHlinkClick();
                    cthyperlink.setId(rid);
                } catch (URISyntaxException e) {
                    // TODO Auto-generated catch block
                    e.printStackTrace();
                }

How can i achieve that in the hyperlink the space is just a space and the # is not converted to %23?

UPDATE

Appearently in Office 365 it displays %23 while in office 2013 the correct name file instead

UPDATE This is how one of the hyperlink shows in the drawing xml while export the excel.

 <Relationship Id="rId28" Target="%23Coverage%20Summary%20(Pool)!A8" TargetMode="External" Type="http://schemas.openxmlformats.org/officeDocument/2006/relationships/hyperlink"/>

However, when I hover the link in Excel the link shows as 

file:///C:\Users\myname\download\%23Coverage Summary (Pool)!A8

So, spaces where correctly replaced but not the # URI fragment. In office 2013, same file, the %23 is replaced with the filename correctly opening the hyperlink

Upvotes: 1

Views: 754

Answers (1)

Axel Richter
Axel Richter

Reputation: 61945

You seems want creating a hyperlink of HyperlinkType.DOCUMENT which links to cell A8 in sheet "Tab1 Tab2".

There is an example for how to create different types of hyperlinks in https://svn.apache.org/repos/asf/poi/tags/REL_3_17_FINAL/src/examples/src/org/apache/poi/xssf/usermodel/examples/ for apache poi 3.17, https://svn.apache.org/repos/asf/poi/tags/REL_4_1_2/src/examples/src/org/apache/poi/xssf/usermodel/examples/ for apache poi 4.1.2 and https://svn.apache.org/repos/asf/poi/tags/REL_5_0_0/src/examples/src/org/apache/poi/examples/xssf/usermodel/ for apache poi 5.0.0.

Here is an excerpt of this showing complete code example for your use case only:

import java.io.FileOutputStream;

import org.apache.poi.common.usermodel.HyperlinkType;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

//See https://svn.apache.org/repos/asf/poi/trunk/src/examples/src/org/apache/poi/xssf/usermodel/examples/HyperlinkExample.java

public class HyperlinkTypeDocumentExample {

    public static void main(String[]args) throws Exception {
        try (Workbook wb = new XSSFWorkbook()) { //or new HSSFWorkbook();
            CreationHelper createHelper = wb.getCreationHelper();

            //cell style for hyperlinks
            //by default hyperlinks are blue and underlined
            CellStyle hlink_style = wb.createCellStyle();
            Font hlink_font = wb.createFont();
            hlink_font.setUnderline(Font.U_SINGLE);
            hlink_font.setColor(IndexedColors.BLUE.getIndex());
            hlink_style.setFont(hlink_font);

            Cell cell;
            Sheet sheet = wb.createSheet("Hyperlinks");
            //URL
            cell = sheet.createRow(0).createCell(0);
            cell.setCellValue("URL Link");

            //link to a place in this workbook

            //create a target sheet and cell
            Sheet sheet2 = wb.createSheet("Tab1 Tab2");
            sheet2.createRow(7).createCell(0).setCellValue("Target Cell");

            cell = sheet.createRow(1).createCell(0);
            cell.setCellValue("Worksheet Link");
            Hyperlink link = createHelper.createHyperlink(HyperlinkType.DOCUMENT);
            link.setAddress("'" + sheet2.getSheetName() + "'!A8");
            cell.setHyperlink(link);
            cell.setCellStyle(hlink_style);

            try (FileOutputStream out = new FileOutputStream("Hyperinks.xlsx")) {
                wb.write(out);
            }
        }
    }
}

Turns out of a comment from author of the question that the hyperlink shall be on an image. But it is only possible to insert a hyperlink pointing to another sheet in the same workbook to an image if the sheet name does not contain any spaces. This is shown here: Apache poi putting hyperlink in images.

For links being on pictures (images) the target needs to be a URI and a URI must be URI-encoded. So if the sheet name contains spaces, then those needs to be encoded as %20. So #'Tab1 Tab2'!A8 needs to be #'Tab1%20Tab2'!A8. But Excel itself violates those rules as it expects the target to be #'Tab1 Tab2'!A8. I cannot see any way to produce that wrong URI using apache poi.

Upvotes: 1

Related Questions