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:

  • Import all the necessary .jar files like XSSF, XML and also you can add Maven Dependency in the maven project as:
   <dependency>  
       <groupId>org.apache.poi</groupId>  
       <artifactId>poi</artifactId>  
       <version>3.9</version>  
   </dependency>
  • 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, 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 set 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 workbook.write();
  10. Close the output file.
  11. Display message on console window when the program is successfully executed.
  12. Display error message on console window when the program is not successful this statement is kept in the catch block.

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

Example:

Java

 

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;

 

class GFG {

 

    

    public static void main(String[] args) throws Exception

    {

        

        String excelfilename = "GeeksForGeeks.xlsx";

 

        

        

        XSSFWorkbook workbook = new XSSFWorkbook();

 

        

        

        XSSFSheet spreadsheet

            = workbook.createSheet("Sheet1");

 

        

        XSSFRow row = spreadsheet.createRow(1);

 

        

        XSSFCell cell = row.createCell(1);

 

        

        cell.setCellFormula(

 

        

        try {

 

            

            FileOutputStream outputfile

                = new FileOutputStream(excelfilename);

            workbook.write(outputfile);

 

            

            outputfile.close();

 

            

            System.out.println(

                excelfilename + " is written successfully");

        }

 

        

        catch (FileNotFoundException e) {

 

            

            System.out.println("ERROR!! " + e.getMessage());

        }

    }

}

Output: On the console window

A. When the program is successfully executed.

GeeksForGeeks.xlsx is written successfully.

B. 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)

Leave a Comment