How to Add Hyperlink to the Contents of a Cell In Excel using Java?

Using Java and Apache POI, we can add a hyperlink to the cell content in an Excel File. Apache POI is a Java library for handling Microsoft Office documents.

Apache POI library in Java:

ApachePOI is an abbreviation for Poor Obfuscation Implementation, a Java API for reading and writing Microsoft documents. It includes the Wordbook, Sheet, Row, and Cell classes and interfaces. Apache POI may be used to access files with the ‘xlsx’ extension as well as files with the ‘xlsx’ extension.

Pre-requisites:

We required the following software on your computers to work with these.

  • Check to see if your machine has Java; if not, obtain the most recent Java SDK version from here.
  • Create a Maven project.
  • Add apache poi dependency.

Adding Hyperlink to the Contents of a Cell In Excel using Java

Here we use STS(Spring Tool Suite) to add a HyperLink to the contents of a cell in an excel sheet.

You can either use MyEclipse or STS(SpringToolSuite) for this. Here I am using STS.

STS:

Spring Tool Suite (STS) is a Java IDE designed specifically for the development of Spring-based enterprise applications. It is simpler, quicker, and more convenient. Most notably, it is built on the Eclipse IDE. STS is a free, open-source software platform powered by VMware. Spring Tools 4 represents the next generation of Spring tooling for your preferred development environment. It was largely redesigned from the bottom up to give world-class support for developing Spring-based enterprise apps, regardless of whether you choose Eclipse, Visual Studio Code, or Theia IDE.

Note:

If you are using MyEclipse then create a maven project.

STS directly imports all the classes from maven if we just add dependencies.

Step#1: Open STS(SpringToolSuite) and create a spring stater project say AddHyperLinksToCellData in a package com.sheetstips.

Step#2: Add the spring web dependency and click on Finish. Now the project is created successfully.

Step#3: Open the pom.xml file and add the following apache poi dependency from the maven repository.

https://mvnrepository.com/artifact/org.apache.poi/poi

<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>3.12</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>3.12</version>
</dependency>

Step#4: Create a new class say HyperlinkCellData in the src/main/java folder with the same package com.sheetstips.

Step#5: Write the below code in the above HyperlinkCellData class

Approach:

  • Import all the required libraries using the import keyword.
  • Import apache POI libaries using the import keyword.
  • Create a class say HyperlinkCellData.
  • Inside the Main driver method, create a new XSSFWorkbook object using the new keyword
  • Create a new worksheet in the above workbook object by passing the sheet name as an argument to the createSheet() function(Sheet1 by default)
  • Store it in another variable
  • Create Helpers using the getCreationHelper() function
  • Set the Link Style using the setUnderline(), setColor(), setFont() functions.
  • setColor() function is used to set the color of the hyperlink.
  • setFont() function is used to set the font style of the hyperlink
  • Adding a Link to the cell by creating the cell at the first row using the createRow() and createCell() functions.
  • Set the cell value to some random text using the setCellValue() function by passing some random string argument to it.
  • Create the hyper link using the createHyperLink() function
  • Set the address to be redirected when the user clicks on the Website Link cell to the setAddress() function.
  • Set the above Hyper Link using the setHyperLink() function by passing the above link as argument to it
  • Set the style of the link as linkStyle using the setCellStyle() function.
  • Create an object for the FileOutputStream by passing the output excel file path as an argument to it to place the output file in that location.
  • Write the data which is added in the above steps i.e write the outputFile to the workbook Object using the write() function.
  • Close the above-opened output file using the close() function.
  • The Exit of the Program

Below is the implementation:

package com.sheetstips;

//Import all the required libraries using the import keyword
import java.io.File;
import java.io.FileOutputStream;

//Import apache POI libaries using the import keyword
import org.apache.poi.common.usermodel.Hyperlink;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.ss.usermodel.CreationHelper;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFCellStyle;
import org.apache.poi.xssf.usermodel.XSSFFont;
import org.apache.poi.xssf.usermodel.XSSFHyperlink;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

//Create a class say HyperlinkCellData
public class HyperlinkCellData {
    // Main driver method
    public static void main(String[] args) throws Exception {
        // Create a new XSSFWorkbook object using the new keyword
        XSSFWorkbook workbookObj = new XSSFWorkbook();

        // Create a new worksheet in the above workbook object by passing
        // the sheet name as an argument to the createSheet() function(Sheet1 by
        // default)
        // Store it in another variable
        XSSFSheet worksheet = workbookObj.createSheet("HyperLinks");

        XSSFCell cell;

        // Create Helpers using the getCreationHelper() function
        CreationHelper helper = workbookObj.getCreationHelper();
        XSSFCellStyle linkStyle = workbookObj.createCellStyle();
        XSSFFont linkFont = workbookObj.createFont();

        // Set the Link Style using the setUnderline(), setColor(), setFont() functions
        linkFont.setUnderline(XSSFFont.U_SINGLE);
        // setColor() function is used to set the color of the hyperlink
        linkFont.setColor(HSSFColor.GREEN.index);
        // setFont() function is used to set the font style of the hyperlink
        linkStyle.setFont(linkFont);

        // Adding a Link to the cell by creating the cell at the first row using the
        // createRow() and createCell() functions
        cell = worksheet.createRow(1).createCell((short) 2);
        // Set the cell value to some random text using the setCellValue() function
        // by passing some random string argument to it
        cell.setCellValue("Website Link");
        // Create the hyper link using the createHyperLink() function
        XSSFHyperlink link = (XSSFHyperlink) helper.createHyperlink(Hyperlink.LINK_URL);
        // Set the address to be redirected when the user clicks on the Website Link
        // cell to the setAddress() function
        link.setAddress("https://sheetstips.com/");
        // Set the above Hyper Link using the setHyperLink() function by passing the
        // above link as argument to it
        cell.setHyperlink((XSSFHyperlink) link);
        // Set the style of the link as linkStyle using the setCellStyle() function
        cell.setCellStyle(linkStyle);

        // Create an object for the FileOutputStream by passing the output excel file
        // path as an argument to it to place the output file in that location
        FileOutputStream outputfile = new FileOutputStream(new File("HyperLinkCellData.xlsx"));

        // Write the data which is added in the above steps
        // i.e write the outputFile to the workbook Object using the write() function
        workbookObj.write(outputfile);
        // Close the above opened outputfile using the close() function
        outputfile.close();
    }

}

Step#6: Now run the code by Right-clicking on the project and click on run –> Run as spring boot App

Output:

Hyperlink Cell Data

Leave a Comment