Quick introduction to Apache POI

A hyperlink is a text or an image on which a user can click on, and following that they jump to another document. 

This chapter shows how to apply hyperlink to the cells. Email, Web URL and files are accessed through the hyperlinks.

Here is the code example to work with hyper links with Apache POI. The version of Apache POI we are using is version 3.11, but this feature should be no different in earlier or newer versions of POI

Reading a hyperlink with Apache POI

    ....
    ....

    Sheet sheet0 = workbook.getSheetAt(0);
    Cell cell = sheet0.getRow(0).getCell((short)0);
    Hyperlink link = cell.getHyperlink();
    // check if link is not null
    if(link != null){
        // just print out the link with getAddress() method
        System.out.println(link.getAddress());
    }
    .....
    .....

Applying Hyperlinks to cell values

Here is an example of how to apply a hyperlink to a cell.

package com.sc;

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

import java.io.File;
import java.io.FileOutputStream;

/**
 * Created by SubjectCoach.com on 3/12/2015.
 * POI version 3.11
 */

public class HyperLinksApachePOI {
    public static void main(String[] args) {
        Workbook wb = new XSSFWorkbook(); 
        CreationHelper createHelper = wb.getCreationHelper();

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

        Cell cell;
        Sheet sheet = wb.createSheet("HyperlinkExampleSheet1");
        // URL
        cell = sheet.createRow(0).createCell((short)0);
        cell.setCellValue("subjectcoach.com");

        Hyperlink link = createHelper.createHyperlink(Hyperlink.LINK_URL);
        link.setAddress("https://www.subjectcoach.com/");
        cell.setHyperlink(link);
        cell.setCellStyle(style);

        // If you want to add a mail to link
        cell = sheet.createRow(1).createCell((short)0);
        cell.setCellValue("SubjectCoach Sales");
        link = createHelper.createHyperlink(Hyperlink.LINK_EMAIL);
        link.setAddress("mailto:sales@subjectcoach.com");
        cell.setHyperlink(link);
        cell.setCellStyle(style);

        //add a link to file link
        cell = sheet.createRow(2).createCell((short)0);
        cell.setCellValue("");
        link = createHelper.createHyperlink(Hyperlink.LINK_EMAIL);
        //note, if subject contains white spaces, make sure they are url-encoded
        link.setAddress("somefile.zip");
        cell.setHyperlink(link);
        cell.setCellStyle(style);

        // We can also create a link between sheets
        Sheet sheet2 = wb.createSheet("HyperlinkExampleSheet2");
        sheet2.createRow(0).createCell((short)0).setCellValue("Target Cell");

        cell = sheet.createRow(3).createCell((short)0);
        cell.setCellValue("Goto HyperlinkExampleSheet2");
        Hyperlink link2 = createHelper.createHyperlink(Hyperlink.LINK_DOCUMENT);
        link2.setAddress("'HyperlinkExampleSheet2'!A1");
        cell.setHyperlink(link2);
        cell.setCellStyle(style);
        try {
            FileOutputStream out = new FileOutputStream(
                    new File("C:/poi/hyperlinksexample.xlsx"));
            wb.write(out);
            out.close();
            System.out.println("hyperlink test workbook sheet processed and written to file C:/poi/hyperlinksexample.xlsx");
        }
        catch(Exception e) {
            System.out.print(e.getMessage());
        }
    }
}

As usual save this program to HyperLinksApachePOI.java, Compile and execute it, It will produce an output as shown below

Working with Print areas

The print area is from right bottom to left top on spreadsheet. Here is a quick example of setting a print area for your SpreadSheet

    Workbook wb = new HSSFWorkbook();
    Sheet sheet = wb.createSheet("Sheet1");
    // sets the print area for Sheet1
    wb.setPrintArea(0, "$A$1:$D$5");
    
    FileOutputStream fileOut = new FileOutputStream("workbook.xls");
    wb.write(fileOut);
    fileOut.close();