Displaying items by tag: xls java Link

Sunday, 25 June 2023 03:20

Java: Modify or Delete Hyperlinks in Excel

Hyperlinks are useful features in Excel documents, providing quick access to other relevant resources such as websites, email addresses, or specific cells within the same workbook. However, sometimes you may want to modify or delete existing hyperlinks for various reasons, such as updating broken links, correcting typos, or removing outdated information. In this article, we will demonstrate how to modify or delete hyperlinks in Excel in Java using Spire.XLS for Java library.

Modify Hyperlinks in Excel in Java
Delete Hyperlinks from Excel in Java

Install Spire.XLS for Java

First of all, you're required to add the Spire.Xls.jar file as a dependency in your Java program. The JAR file can be downloaded from this link. If you use Maven, you can easily import the JAR file in your application by adding the following code to your project's pom.xml file.

Package Manager

<repositories>
    <repository>
        <id>com.e-iceblue</id>
        <name>e-iceblue</name>
        <url>https://repo.e-iceblue.com/nexus/content/groups/public/</url>
    </repository>
</repositories>
<dependencies>
    <dependency>
        <groupId>e-iceblue</groupId>
        <artifactId>spire.xls</artifactId>
        <version>15.11.3</version>
    </dependency>
</dependencies>

Modify Hyperlinks in Excel in Java

If there are issues with the functionality of a hyperlink caused by damage or spelling errors, you may need to modify it. The following steps demonstrate how to modify an existing hyperlink in an Excel file:

Create an instance of Workbook class.
Load an Excel file using the Workbook.loadFromFile() method.
Get a specific worksheet using the Workbook.getWorksheets().get() method.
Get the collection of all hyperlinks in the worksheet using the Worksheet.getHyperLinks() method.
Change the values of TextToDisplay and Address property using the HyperLinksCollection.get().setTextToDisplay() and HyperLinksCollection.get().setAddress method.
Save the result file using the Workbook.saveToFile() method.

Java

import com.spire.xls.*;
import com.spire.xls.collections.HyperLinksCollection;

public class ModifyHyperlink {
    public static void main(String[] args) {
        //Create a Workbook instance
        Workbook workbook = new Workbook();

        //Load an Excel file
        workbook.loadFromFile("Sample.xlsx");

        //Get the first worksheet
        Worksheet sheet = workbook.getWorksheets().get(0);

        //Get the collection of all hyperlinks in the worksheet
        HyperLinksCollection links = sheet.getHyperLinks();

        //Change the values of TextToDisplay and Address property
        links.get(0).setTextToDisplay("Republic of Indonesia");
        links.get(0).setAddress("https://www.indonesia.travel/gb/en/home");

        //Save the document
        workbook.saveToFile("ModifyHyperlink.xlsx", ExcelVersion.Version2013);
        workbook.dispose();
    }
}

Java: Modify or Delete Hyperlinks in Excel

Delete Hyperlinks from Excel in Java

Spire.XLS for Java also offers the Worksheet.getHyperLinks().removeAt() method to remove hyperlinks. The following are the steps to delete hyperlink from Excel in Java.

Create an instance of Workbook class.
Load an Excel file using the Workbook.loadFromFile() method.
Get a specific worksheet using the Workbook.getWorksheets().get() method.
Get the collection of all hyperlinks in the worksheet using the Worksheet.getHyperLinks() method.
Remove a specific hyperlink and keep link text using the Worksheet.getHyperLinks().removeAt() method.
Save the result file using the Workbook.saveToFile() method.

Java

import com.spire.xls.*;
import com.spire.xls.collections.HyperLinksCollection;

public class RemoveHyperlink {
    public static void main(String[] args) {
        //Create a Workbook instance
        Workbook workbook = new Workbook();

        //Load an Excel file
        workbook.loadFromFile("Sample.xlsx");

        //Get the first worksheet
        Worksheet sheet = workbook.getWorksheets().get(0);

        //Get the collection of all hyperlinks in the worksheet
        HyperLinksCollection links = sheet.getHyperLinks();

        //Remove the first hyperlink and keep link text
        sheet.getHyperLinks().removeAt(0);

        //Remove all content from the cell
        //sheet.getCellRange("A7").clearAll();

        //Save the document
        String output = "RemoveHyperlink.xlsx";
        workbook.saveToFile(output, ExcelVersion.Version2013);
        workbook.dispose();
    }
}

Java: Modify or Delete Hyperlinks in Excel

Apply for a Temporary License

If you'd like to remove the evaluation message from the generated documents, or to get rid of the function limitations, please request a 30-day trial license for yourself.

Published in Link

Java: Add Hyperlinks to Excel

Hyperlinks can direct readers from one file to a web address, an email address or another file. A hyperlink can be added to text or an image. In this article, we will introduce how to add hyperlinks to Excel in Java using Spire.XLS for Java library.

Add Text Hyperlinks to Excel in Java
Add Image Hyperlinks to Excel in Java

Install Spire.XLS for Java

Package Manager

<repositories>
    <repository>
        <id>com.e-iceblue</id>
        <name>e-iceblue</name>
        <url>https://repo.e-iceblue.com/nexus/content/groups/public/</url>
    </repository>
</repositories>
<dependencies>
    <dependency>
        <groupId>e-iceblue</groupId>
        <artifactId>spire.xls</artifactId>
        <version>15.11.3</version>
    </dependency>
</dependencies>

Add Text Hyperlinks to Excel in Java

The following are the steps to add a text hyperlink to Excel in Java:

Create an instance of Workbook class.
Get the desired worksheet using Workbook.getWorksheets().get() method.
Access the specific cell that you want to add hyperlink to using Worksheet.getRange().get() method.
Add a hyperlink to the cell using Worksheet.getHyperLinks().add() method.
Set the type, display text and address for the hyperlink using XlsHyperLink.setType(), XlsHyperLink.setTextToDisplay() and XlsHyperLink.setAddress() methods.
Autofit column width using XlsWorksheet.autoFitColumn() method.
Save the result file using Workbook.saveToFile() method.

Java

import com.spire.xls.*;

public class AddTextHyperlinks {
    public static void main(String []args){
        //Create a Workbook instance
        Workbook workbook = new Workbook();

        //Get the first worksheet
        Worksheet sheet = workbook.getWorksheets().get(0);

        //Add a text hyperlink that leads to a website
        CellRange cell1 = sheet.getRange().get("B3");
        HyperLink urlLink = sheet.getHyperLinks().add(cell1);
        urlLink.setType(HyperLinkType.Url);
        urlLink.setTextToDisplay("Link to a website");
        urlLink.setAddress("https://www.google.com/");

        //Add a text hyperlink that leads to an email address
        CellRange cell2 = sheet.getRange().get("E3");
        HyperLink mailLink = sheet.getHyperLinks().add(cell2);
        mailLink.setType(HyperLinkType.Url);
        mailLink.setTextToDisplay("Link to an email address");
        mailLink.setAddress("mailto:abc@outlook.com");

        //Add a text hyperlink that leads to an external file
        CellRange cell3 = sheet.getRange().get("B7");
        HyperLink fileLink = sheet.getHyperLinks().add(cell3);
        fileLink.setType(HyperLinkType.File);
        fileLink.setTextToDisplay("Link to an external file");
        fileLink.setAddress("C:\\Users\\Administrator\\Desktop\\Report.xlsx");

        //Add a text hyperlink that leads to a cell in another sheet
        CellRange cell4 = sheet.getRange().get("E7");
        HyperLink linkToSheet = sheet.getHyperLinks().add(cell4);
        linkToSheet.setType(HyperLinkType.Workbook);
        linkToSheet.setTextToDisplay("Link to a cell in sheet2");
        linkToSheet.setAddress("Sheet2!B5");

        //Add a text hyperlink that leads to a UNC address
        CellRange cell5 = sheet.getRange().get("B11");
        HyperLink uncLink = sheet.getHyperLinks().add(cell5);
        uncLink.setType(HyperLinkType.Unc);
        uncLink.setTextToDisplay("Link to a UNC address");
        uncLink.setAddress("\\\\192.168.0.121");

        //Autofit column width
        sheet.autoFitColumn(2);
        sheet.autoFitColumn(5);

        //Save to file
        workbook.saveToFile("AddTextHyperlinks.xlsx", ExcelVersion.Version2013);
    }
}

Java: Add Hyperlinks to Excel

Add Image Hyperlinks to Excel in Java

The following are the steps to add an image hyperlink to Excel in Java

Create an instance of Workbook class.
Get the desired worksheet using Workbook.getWorksheets().get() method.
Insert an image into the worksheet using Worksheet.getPictures().add() method and set column width and row height.
Add a hyperlink to the image using XlsBitmapShape.setHyperLink() method.
Save the result file using Workbook.saveToFile() method.

Java

import com.spire.xls.ExcelPicture;
import com.spire.xls.ExcelVersion;
import com.spire.xls.Workbook;
import com.spire.xls.Worksheet;

public class AddImageHyperlinks {
    public static void main(String []args){
        //Create a Workbook instance
        Workbook workbook = new Workbook();
        //Get the first worksheet
        Worksheet sheet = workbook.getWorksheets().get(0);

        //Insert an image into the worksheet
        ExcelPicture picture = sheet.getPictures().add(5, 3, "Logo.png");
        sheet.setRowHeight(5,60);
        sheet.setColumnWidth(3,11);

        //Add a hyperlink to the image
        picture.setHyperLink("https://www.e-iceblue.com", true);

        //Save the result file
        workbook.saveToFile("AddImageHyperlink.xlsx", ExcelVersion.Version2013);
    }
}

Java: Add Hyperlinks to Excel

Apply for a Temporary License

If you'd like to remove the evaluation message from the generated documents, or to get rid of the function limitations, please request a 30-day trial license for yourself.

Published in Link