How to Create Hyperlink in Spreadsheet using Java?

Apache POI is a popular API that allows programmers to create, modify, and display MS Office files using Java programs. It is an open-source library developed and distributed by Apache Software Foundation to design or modify Microsoft Office files using Java programs. It contains classes and methods to decode the user input data or a file into MS Office documents.

Apache POI allows us to create hyperlinks in spreadsheets. It is useful to set the web address in the cell and redirect it to the server when clicked.

Approach for Creating Hyperlinks in SpreadSheet

• Import all the necessary .jar files like XSSF, XML and also you can add Maven Dependency in the maven project as:

<dependency>
<groupld>org.apache.poi</groupld>
<artifactld>poi</artifactld>
<version>3.9</version>
</dependency>
Eclipse

• Create an instance of the workbook
• Create a spreadsheet in the above workbook.
• Create rows using XSSFRow
• Create a cell using XSSFCell.
• Set hyperlink along with cell value. ting cell values.
• Writing the content to the workbook by defining the object of type FileOutputStream
• Close the connection of the file.

Procedure:

1. Create a Maven project in Eclipse and add Apache POI(used for setting value type of cells) and also import all the necessary .jar files like HSSF, and XML.
2. Give the name to the workbook.
3. Create a workbook using “new XSSFWorkbook()” in which we have to create the spreadsheet.
4. Create a spreadsheet in the workbook using “workbook.createSheet(‘Sheet1’)” and also give the name to the sheet as “Sheet1”
5. Create a row using XSSFRow. Rows are 0-based.
6. Create a cell using XSSFCell.
7. Set a hyperlink using cell.setCellFormula() along with setting a value to a cell using cell.setCellValue();
8. Place the output file in the default location and also kept in the try-catch block using FileOutputStream().
9. Write it to the workbook created in the initial step using the workbook.write();
10. Close the output file.
11. Display a message on the console window when the program is successfully executed.
12. Display an error message on the console window when the program is not successful this statement is kept in the catch block.

Example for Create Hyperlink in Spreadsheet using Java

Let’s take an example to create a hyperlink in the cell that redirects to geeksforgeeks main page using Java.

Below is the implementation of the above approach:

Java

// Java Program to Apply Hyperlink to a Cell
// in a Spreadsheet Using Apache POI

// Importing required classes
import java.io.*;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

// Main class
class GFG {

    // Main driver method
    public static void main(String[] args) throws Exception
    {
        // Name of WorkBook (custom input)
        String excelfilename = "GeeksForGeeks.xlsx";

        // Creating a WorkBook by
        // creating an object of XSSFWorkbook class
        XSSFWorkbook workbook = new XSSFWorkbook();

        // Creating a Spread Sheet by creating an object of
        // XSSFSheet and also give name
        XSSFSheet spreadsheet
            = workbook.createSheet("Sheet1");

        // Creating a row
        XSSFRow row = spreadsheet.createRow(1);

        // Creating a cell and put a cell index value in it.
        XSSFCell cell = row.createCell(1);

        // Adding hyperlink along with cell value in it.
        cell.setCellFormula(
            "HYPERLINK(\"https://www.geeksforgeeks.org/\", \"click here\")");

        // Try block to check for exceptions
        try {

            // Step 9
            FileOutputStream outputfile
                = new FileOutputStream(excelfilename);
            workbook.write(outputfile);

            // Closing connections using close() method
            outputfile.close();

            // Step 11
            System.out.println(
                excelfilename + " is written successfully");
        }

        // Catch block to handle exceptions
        catch (FileNotFoundException e) {

            // Step 12
            System.out.println("ERROR!! " + e.getMessage());
        }
    }
}

Output: (On the console window)

1. When the program is successfully executed.

GeeksForGeeks.xlsx is written successfully.

2. When the program is not successfully executed. 

ERROR!! GeeksForGeeks.xlsx 
(The process cannot access the file because it is being used by another process)

Output: Workbook(excel file)

Frequently Asked Questions

1. What is a hyperlink in a Spreadsheet?

A Hyperlink in a spreadsheet is a reference or link to another location(which is in complete detail) such as a webpage, a specific cell in the same sheet, or may be a different sheet, or an external file.

2. How to create a Hyperlink in a spreadsheet using Java?

To add a hyperlink to a spreadsheet using Java, you can use a library called Apache POI. This library offers various classes and methods to work with Microsoft Excel Files, You can use CreationHelper class, which will help you to create hyperlinks easily.

3. How to create hyperlinks to external Files using Apache POI?

To create hyperlinks to external files using Apache POI. Set the hyperlink to ‘HyperlinkType.FILE’ and provide the path as the address.

4. What are the limitations to the number of hyperlinks that can be created in a spreadsheet using Apache POI?

The number of Hyperlinks that are created in a spreadsheet using Apache POI is limited by the capacity of the Spreadsheet file format itself. There is an upper limit on the number of hyperlinks that can be created on a single sheet.