hyperlink to a different sheet within the same workbook using apache poi

Question

I am using Apache POI to generate an excel workbook containing multiple sheets. I want to create a hyperlink from one sheet to another. How to accomplish this ? There are solutions I found for establishing hyperlinks to websites, even other excel files but not to other sheets within the same workbook. Does Apache POI allow us to do this ?

Answer 1

Yes, Apache POI allows you to create a hyperlink to another sheet in the same workbook. According to the Apache POI Quick Guide:

cell = sheet.createRow(3).createCell((short)0);
cell.setCellValue("Worksheet Link");
Hyperlink link2 = createHelper.createHyperlink(Hyperlink.LINK_DOCUMENT);
link2.setAddress("'Target Sheet'!A1");
cell.setHyperlink(link2);
cell.setCellStyle(hlink_style);

This creates a cell with a hyperlink in it of type LINK_DOCUMENT, with an address of a cell reference, which can be in the same sheet or another sheet. Then it sets the cell style to an existing cell style (created earlier in the code sample), so that it looks like a hyperlink.

Answer 2

You can also link to a cell address in another worksheet using POI.

Create your link as type Hyperlink.LINK_DOCUMENT. Then send setHyperlink() a string in this format :

file:/E:/PROJECTS/SomePrj/ExcelFileName.xlsx#'ExcelSheetName'!B5

that will work - at least for XSSF.

Use aFile.toURI().toString() to build the filepart before the '#' character. Don't forget to add both # , ' and ! characters (exactly as in my example) or it will not work.

I do not see this technique demonstrated in the (otherwise very good) POI documentation.

Answer 3

I found the right way. Tested on poi 3.16 and solved the annoying problem of not allowing space in the link(sheet name can have space). you will get error if you try link.setAddress("ExcelWrite.xlsx#'Sheet 1'!A2");

RIGHT CODE:

    cell = sheet.createRow(1).createCell(0);
    cell.setCellValue("File Link");
    XSSFHyperlink link = (XSSFHyperlink) createHelper.createHyperlink(HyperlinkType.FILE);
    link.setAddress("ExcelWrite.xlsx");
    link.setLocation("'Sheet 1'!A2");
    cell.setHyperlink(link);
    cell.setCellStyle(hlink_style);