Apache POI Relative file hyperlink

Question

I'm trying to create an hyperlink to a file using APACHE POI XSSF using a relative path. When opening the .xlsx file created with Microsft Excel the relative path is modified and it is not well linked. Microsoft Excel adds some '../../' in front of the path. I try opening it with OpenOffice and it works OK. Also I tried it with HSSF and it works in both Microsoft Excel and OpenOffice. Any idea why is this happening? This is an example code:

import java.io.File;
import java.io.FileOutputStream;
import java.io.UnsupportedEncodingException;
import java.net.URLEncoder;

import org.apache.poi.hssf.usermodel.HSSFHyperlink;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CreationHelper;
import org.apache.poi.ss.usermodel.Hyperlink;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
public class ExcelHyperlinks {
 public static void main(String[] args) throws Exception{
     /* Create Workbook and Worksheet */
     XSSFWorkbook my_workbook = new XSSFWorkbook();
     XSSFSheet my_sheet = my_workbook.createSheet("Cell Hyperlink");
     CreationHelper createHelper = my_workbook.getCreationHelper();
     Hyperlink  url_link=createHelper.createHyperlink(HSSFHyperlink.LINK_URL);
     Hyperlink  file_link=createHelper.createHyperlink(HSSFHyperlink.LINK_FILE);
     Hyperlink  email_link=createHelper.createHyperlink(HSSFHyperlink.LINK_EMAIL);

     /* Define the data for these hyperlinks */
     url_link.setAddress("http://www.google.com");
     try {
         file_link.setAddress((URLEncoder.encode("Encuesta de bienvenida","UTF-8")+"/"+"E18").replace("\\", "/").replace("+","%20"));
     } catch (UnsupportedEncodingException e) {
         e.printStackTrace();
     }
     System.out.println("addres--> " + file_link.getAddress());
     email_link.setAddress("mailto:test@gmail.com");

     /* Attach these links to cells */
     Row row = my_sheet.createRow(0);                
     Cell cell = row.createCell(0);
     cell.setCellValue("Take me to Google");         
     cell.setHyperlink(url_link);

     row = my_sheet.createRow(1);            
     cell = row.createCell(1);
     cell.setCellValue("Click to Open the file");            
     cell.setHyperlink(file_link);

     row = my_sheet.createRow(2);            
     cell = row.createCell(2);
     cell.setCellValue("Send an Email");             
     cell.setHyperlink(email_link);

     /* Write changes to the workbook */
     FileOutputStream out = new FileOutputStream(new File("C:/cell_hyperlink_example.xlsx"));
     my_workbook.write(out);
     out.close();
 }

}

this http://stackoverflow.com/a/17383431/624003 may be useful. — Sankumarsingh, Sep 03 '13 at 11:26
I'm not reciving any Exception from apache POI. My link seems to be OK. It works if I open it with OpenOffice. It works if I use HSSF instead of XSSF. But when using XSSF and Microsoft Excel it add some dots in front the relative path. This is the path I indicate for the hyperlink in the code -->'Encuesta%20de%20bienvenida/E18' And this is the path I see when consulting the hyperlink in Microsoft Excel --> '../../../../../Encuesta%20de%20bienvenida/E18' — ProgramadorJunior, Sep 03 '13 at 11:33

score 1 · Answer 1 · answered Sep 03 '13 at 11:49

1

It does not seems to be an issue. It is excel feature that whatever path you are giving, it creates a relative path from excel location to the hyperlinked file location.

each "../" represents one directory up. If you move by this way from current excel sheet position, you will reach to the hyperlinked file directory.

for example if your excel sheet is in say c:\folder1\folder2\excelDir\test.xls and the directory of the file that was hyperlinked is say c:\folder1\folder3\imageDir\test.jpg

the relative path marked in excel would be like

../../folder3/imageDir/test.jpg

for first ../ we will move from test.xls to excelDir, for second ../ we will move from excelDir to folder2 now the same directory contains folder3 so it will continue from here to test.jpg.

answered Sep 03 '13 at 11:49

Sankumarsingh

9,889
11
50
74

But the Excel sheet is in the same directory than the file hyperlinked. So no dots are needed as I indicate in my code. I don't know how Excel calculate the number of directories up it has to go. But it is wrong. Or maybe I'm doing something wrong. Maybe is useful for you to know that my hyperlink is linked to a folder, not a file. Could it be a problem? – ProgramadorJunior Sep 03 '13 at 12:07
This is the structure of the directories used in the example: C:/cell_hyperlink_example.xlsx --> (Excel file) C:/Encuesta de bienvenida/E18 --> (Folder where the hyperlink points to) As you can see, both are in C: – ProgramadorJunior Sep 03 '13 at 12:12
It it is working fine on your end, it will not create any problem, I have faced the same, but till now it has not create any problem even a single time. Just forget about this and enjoy. :) – Sankumarsingh Sep 03 '13 at 12:53
Noo, I can't enjoy :). It is not working fine. I try to open the link using Microsoft Excel and it says that it can't find the directory – ProgramadorJunior Sep 03 '13 at 13:01

Apache POI Relative file hyperlink

1 Answers1