Add Hyperlink to Cells - Excel Java POI Example Program

Hyperlinks - Introduction


In this post, we will explain how to add hyperlinks to your Excel cell data, in Java, using Apache POI library. A hyperlink in a cell allows you to link to an external URL, a file or even to an email address. We will provide examples of XLS and XLSX file types, so that you can understand and incorporate hyperlinks across any kind of workbook format. Let us get started with basic examples of creating hyperlinks in workbooks.

XLS - Create Hyperlink in Cells - HSSFHyperlink- Java Program Example


You use HSSFHyperlink class to create the required hyperlink and attach it to a cell by using setHyperlink method. Easy in words, and in program too. Here is an example.

import java.io.*;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.hssf.usermodel.HSSFHyperlink;
public class CreateCellHyperlink {  
        public static void main(String[] args) throws Exception{
                /* Create Workbook and Worksheet */
                HSSFWorkbook my_workbook = new HSSFWorkbook();
                HSSFSheet my_sheet = my_workbook.createSheet("Cell Hyperlink");
                
                /* Let us create some HSSFHyperlink objects */
                HSSFHyperlink url_link=new HSSFHyperlink(HSSFHyperlink.LINK_URL);
                HSSFHyperlink file_link=new HSSFHyperlink(HSSFHyperlink.LINK_FILE);
                HSSFHyperlink email_link=new HSSFHyperlink(HSSFHyperlink.LINK_EMAIL);
                
                /* Define the data for these hyperlinks */
                url_link.setAddress("http://www.google.com");
                file_link.setAddress("file:///c://test.csv");
                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.xls"));
                my_workbook.write(out);
                out.close();
        }
}

If you want a hyperlink style, you should set that in the cell style, underline / font color. Refer to our tutorials in the related post section, that explains how to do this. The example above creates three different types of hyperlink viz URL, File and Email and places them to the workbook. When you click on them, required applications are automatically launched..viz browser, mail client etc.

XLSX - Add Hyperlink to Cells - XSSFHyperlink - Java Program Example


For Office 2007 + formats, you have to use XSSFHyperlink to create hyperlink object and use setHyperlink method in XSSFCell to add the hyperlink to the cell. XSSFHyperlink also has a feature to add tooltip to your hyperlinks. Unfortunately, XSSFHyperlink does not have a public constructor, so you have to get an XSSFHyperlink object through org.apache.poi.xssf.usermodel.XSSFCreationHelper. You get an instance of XSSFCreationHelper by using workbook.getCreationHelper() method. Roundabout..here is a Java program that would clarify things.
import java.io.*;
import org.apache.poi.xssf.usermodel.*; 
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFHyperlink;
import org.apache.poi.xssf.usermodel.XSSFCreationHelper;
public class CreateCellHyperlinkXLSX {  
        public static void main(String[] args) throws Exception{
                /* Create Workbook and Worksheet XLSX Format */
                XSSFWorkbook my_workbook = new XSSFWorkbook();
                XSSFSheet my_sheet = my_workbook.createSheet("Cell Hyperlink");
                
                /* Let us create some XSSFHyperlink objects */
                /* First get XSSFCreationHelper object using the workbook*/
                XSSFCreationHelper helper= my_workbook.getCreationHelper();
                /* Now use createHyperlink method to get XSSFHyperlink */
                XSSFHyperlink url_link=helper.createHyperlink(Hyperlink.LINK_URL);
                XSSFHyperlink file_link=helper.createHyperlink(Hyperlink.LINK_FILE);
                XSSFHyperlink email_link=helper.createHyperlink(Hyperlink.LINK_EMAIL);
                
                /* Define the data for these hyperlinks */
                /* Define tooltip for the hyperlinks */
                url_link.setAddress("http://www.google.com");
                url_link.setTooltip("Click to go to Google");
                file_link.setAddress("file:///c://test.csv");
                file_link.setTooltip("Click to open the file");
                email_link.setAddress("mailto:test@gmail.com");
                email_link.setTooltip("send email now");
                
                /* 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_with_tooltip.xlsx"));
                my_workbook.write(out);
                out.close();
        }
}

That completes our breezy tutorial to create hyperlinks in Excel document using Java and Apache POI. We have covered XLS and XLSX formats, and you may want to try these examples and check the output yourself. If you have a question, you can post it in the comments section of this blog.

1 comment:

  1. How do I create a hyperlink that returns another tab excel, please'll be grateful for the answer.

    ReplyDelete