How to Create Hyperlink in Spreadsheet using Java?

Java programmers may create, edit, and display MS Office files using the widely used Apache POI API. Through its classes and methods, this open-source library developed by the Apache Software Foundation provides a variety of functions for turning user input data or files into Microsoft Office documents.

Apache POI's versatility and reliability have made it a popular option among developers worldwide. Its broad capability and smooth integration make it an invaluable asset for any project needing MS Office file handling. The API's continuous updates and active community guarantee that it is always up to date with the newest file format requirements, ensuring compatibility across MS Office versions.

Apache POI is the preferred choice for developers looking to interact with MS Office files in Java due to its great performance, thorough documentation, and example code. Apache POI provides an easy way to create hyperlinks within spreadsheets.

Users may quickly redirect to the intended server or web page when the hyperlink is clicked by entering the relevant URL in a column. This functionality is especially useful when dealing with huge datasets or when providing information that need immediate access to extra resources.

Let us now look at the Approach in creating hyperlink in the spreadsheet using java −

• Make sure to include all of the necessary JAR files that are required to write the data to the Microsoft format files like Excel(.xls,.xlsx) etc.

• After including all the jar’s we will Create a workbook instance.

• In the worksheet mentioned above, create a spreadsheet.

• Use XSSFRow to create rows.

• Use XSSFCell to create a cell.

• Create a hyperlink that connects them by designating a certain cell value.

• For Writing data into a workbook we will use FileOutputStream.

• Finally we will close the file connection.

The steps that are mentioned above will give us a high level of picture. Now let's dive into the Procedure −

• Firstly we need to create a new maven project in Eclipse then we need to import all the jar files that are required for creating a workbook using Apache POI you can get this dependency from maven repository

For Example −

<dependency>
   <groupId>org.apache.poi</groupId>
   <artifactId>poi</artifactId>
   <version>4.1.2</version>
</dependency>

Or else we have one another way to import all the jar’s at once go to => apache official website => then in the binary Artifacts download required version

• Then Right click your project => choose Build Path => Under the libraries tab you see an option Add External Jars => select all the jars from the above downloaded file => Then after adding select Apply and close.

• Name the workbook as per your wish with an extension .xlsx.

• Use "new XSSFWorkbook()" to construct the workbook in which we'll create the spreadsheet.

• Use "workbook.createSheet('Sheet1')" to create a spreadsheet in the workbook and call it "Sheet1".

• Use XSSFRow and XSSFCell to create rows and cells where Row indexing begins at 0.

• Use "cell.setCellFormula()" to create a hyperlink and "cell.setCellValue()" to set a cell's value;

• Using FileOutputStream(), create the output file in the current working directory and we will place it inside the try-catch block.

• Use "workbook.write()" to write it to the workbook you generated in the first step.

• We will close the file connection.

• When the Program is successfully executed then we will display a message in the console.

• If not, we will display an error message.

We Will create a hyperlink that will redirect us to the Tutorials point Page.

Example

import java.io.FileNotFoundException;
import java.io.FileOutputStream;

import org.apache.poi.xssf.usermodel.*;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;

public class TutorialsPoint {
   public static void main( String[] args ) throws Exception {
		//Name of the spreadsheet-replace as per the requirement
         String file_name = "TutorialsPoint.xlsx";
		//create a new WorkBook
         XSSFWorkbook wb = new XSSFWorkbook();
		// create a spreadsheet in the workbook and call it "Sheet1"
         XSSFSheet sheet = wb.createSheet("Sheet1");
		// creation of row
         XSSFRow r = sheet.createRow(1);
		//create a cell and pass cell index value
         XSSFCell c = r.createCell(1);
		// insert cell value and adding hyperlink to it
      c.setCellFormula(
         "HYPERLINK("https://www.tutorialspoint.com/index.htm", "Tutorials Point")");
      //In this try block, exceptions are checked.
      try {
         FileOutputStream output
         = new FileOutputStream(file_name);
         //Writing output to the workbook
         wb.write(output);
         //Closing the connection
         output.close();

         System.out.println(file_name + " is written successfully please check in current working directory to verify.");
      }
      //In this catch block, exceptions are handled.
      catch (FileNotFoundException e) {
         System.out.println("error:" + e.getMessage());
      }
   }
}

Output

TutorialsPoint.xlsx is written successfully please check in current working directory to verify.

You will see a file named TutorialsPoint downloaded in the current working directory.

When you open the spreadsheet we will see a cell with the data Tutorials Point when we click that it will redirect us to the Tutorials Point page.

Conclusion

In this article we looked at the approach and procedure to create hyperlink in the spreadsheet using java and we have also learned about how Apache POI helps us to achieve the above goal.