Knowledgebase (2300)
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.
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.
<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);
}
}
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);
}
}
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.
When it comes to managing images in Excel, the ability to replace or extract them efficiently is important. If there are outdated or incorrect images in Excel, replacing them ensures that your data remains accurate and up-to-date. While extracting images from Excel files can be useful when you need to reuse them in other documents or presentations. In this article, you will learn how to replace or extract images in Excel in Java using Spire.XLS for 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.
<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>
Replace Images in Excel in Java
To replace a picture in Excel, you can load a new picture and then pass it as a parameter to the ExcelPicture.setPicture() method. The following are the detailed steps to replace an Excel image with another one.
- Create a Workbook instance.
- Load an Excel file using Workbook.loadFromFile() method.
- Get the first worksheet using Workbook.getWorksheets().get() method.
- Get the first picture in the worksheet using Worksheet.getPictures().get() method.
- Load an image, and then pass it as a parameter to the ExcelPicture.setPicture() method to replace the original picture with the new one.
- Save the result file using Workbook.saveToFile() method.
- Java
import com.spire.xls.*;
import javax.imageio.ImageIO;
import java.awt.image.BufferedImage;
import java.io.*;
public class replaceImages {
public static void main(String[] args) throws IOException {
// Create a Workbook instance
Workbook workbook = new Workbook();
// Load an Excel file
workbook.loadFromFile("ExcelImg.xlsx");
//Get the first worksheet
Worksheet sheet = workbook.getWorksheets().get(0);
//Get the first image in the worksheet
ExcelPicture pic = sheet.getPictures().get(0);
// Load an image
BufferedImage bufferedImage = ImageIO.read(new File("logo.png"));
// Replace the first image with the loaded one
pic.setPicture(bufferedImage);
// Save the result file
workbook.saveToFile("ReplaceImage.xlsx", ExcelVersion.Version2016);
}
}
Extract Images from Excel in Java
With Spire.XLS for Java, you can also extract all images in an Excel worksheet at once. The following are the detailed steps.
- Create a Workbook instance.
- Load an Excel file using Workbook.loadFromFile() method.
- Get the first worksheet using Workbook.getWorksheets().get() method.
- Get all the images in the worksheet using Worksheet.getPictures() method.
- Loop through to get each image, and then save them to a specified file path.
- Java
import com.spire.xls.*;
import com.spire.xls.collections.PicturesCollection;
import javax.imageio.ImageIO;
import java.awt.image.BufferedImage;
import java.io.*;
public class extractImages {
public static void main(String[] args) throws IOException {
// Create a Workbook instance
Workbook workbook = new Workbook();
// Load an Excel file
workbook.loadFromFile("Test.xlsx");
// Get the first worksheet
Worksheet sheet = workbook.getWorksheets().get(0);
// Get all the pictures in the worksheet
PicturesCollection pic = sheet.getPictures();
// Iterate through each image
for (int i = pic.getCount() - 1; i >= 0; i--)
{
// Get a specified image
ExcelPicture excelPic = pic.get(i);
BufferedImage loImage = excelPic.getPicture();
// Save the image to a specified file path
File file = new File(String.format("ExtractImages\\Image-%d.png", i));
ImageIO.write(loImage, "PNG", file);
}
}
}
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.
In Excel worksheets, a table is a specially designated area within a worksheet that comes with headers, optional total rows, and built-in features such as filtering, sorting, data inserting and deleting, and calculated columns, which greatly facilitate data handling and analysis. For developers looking to automate or integrate Excel data operations within their Java applications, the ability to create, modify, or remove tables within these worksheets becomes an essential skill. This article explores how to create, modify, and remove tables in Excel worksheets with Java using Spire.XLS for Java to manage data in Excel files effectively.
- Create Tables in Excel Worksheets with Java
- Modify Tables in Excel Worksheets with Java
- Remove Tables from Excel Worksheets with 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.
<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>
Create Tables in Excel Worksheets with Java
To create a table in an Excel worksheet using Java, you can use the Worksheet.getListObjects().create(String tableName, IXLSRange cellRange) method. Follow these steps to create and customize a table:
- Create an instance of the Workbook class.
- Use the Workbook.loadFromFile() method to load an existing Excel file.
- Retrieve the desired worksheet using the Workbook.getWorksheets().get() method.
- Obtain the table’s cell range using the Worksheet.getRange().get() method.
- Use the Worksheet.getListObjects().create(String tableName, IXLSRange cellRange) method to create the table with a name and range.
- Format the table as needed.
- Save the changes using the Workbook.saveToFile() method.
- Java
import com.spire.xls.CellRange;
import com.spire.xls.TableBuiltInStyles;
import com.spire.xls.Workbook;
import com.spire.xls.Worksheet;
import com.spire.xls.core.IListObject;
public class CreateTableExcel {
public static void main(String[] args) {
// Create an instance of Workbook
Workbook workbook = new Workbook();
// Load an Excel file
workbook.loadFromFile("Sample.xlsx");
// Get the first worksheet
Worksheet sheet = workbook.getWorksheets().get(0);
// Get the cell range of the table
CellRange range = sheet.getCellRange("A1:I11");
// Create a table
IListObject table1 = sheet.getListObjects().create("Table1", range);
// Apply a built-in style to the table
table1.setBuiltInTableStyle(TableBuiltInStyles.TableStyleLight16);
// Save the workbook
workbook.saveToFile("output/CreateTableExcel.xlsx");
workbook.dispose();
}
}
Modify Tables in Excel Worksheets with Java
Spire.XLS for Java provides methods in the IListObject class to modify worksheet table properties, such as the table name, cell range, style, and header visibility. Follow these steps to modify tables in Excel worksheets:
- Create an instance of the Workbook class.
- Use the Workbook.loadFromFile() method to open an existing Excel file.
- Retrieve the worksheet containing the table using the Workbook.getWorksheets().get() method.
- Access the table using the Worksheet.getListObjects().get() method.
- Use the methods in the IListObject class to update the table's properties, such as its name, style, or headers.
- Save the updated workbook using the Workbook.saveToFile() method.
- Java
import com.spire.xls.TableBuiltInStyles;
import com.spire.xls.Workbook;
import com.spire.xls.Worksheet;
import com.spire.xls.core.IListObject;
public class ModifyTableExcel {
public static void main(String[] args) {
// Create an instance of Workbook
Workbook workbook = new Workbook();
// Load an Excel file
workbook.loadFromFile("output/CreateTableExcel.xlsx");
// Get the first worksheet
Worksheet sheet = workbook.getWorksheets().get(0);
// Get the table
IListObject table = sheet.getListObjects().get(0);
// Modify the table
table.setName("NewTable"); // Change the name
table.setLocation(sheet.getRange().get("A1:D11")); // Change the location
table.setBuiltInTableStyle(TableBuiltInStyles.TableStyleDark5); // Change the style
table.setDisplayHeaderRow(false); // Hide the header row
// Save the workbook
workbook.saveToFile("output/ModifyTableExcel.xlsx");
workbook.dispose();
}
}
Remove Tables from Excel Worksheets with Java
You can remove tables from Excel worksheets using the Worksheet.getListObjects().removeAt(int index) method. This action converts the table area back to a normal cell range and removes any associated formatting. Follow these steps to remove tables:
- Create an instance of the Workbook class.
- Open an existing Excel file using the Workbook.loadFromFile() method.
- Retrieve the worksheet containing the table using the Workbook.getWorksheets().get() method.
- Use the Worksheet.getListObjects().removeAt() method to delete the table by its index.
- Save the changes using the Workbook.saveToFile() method.
- Java
import com.spire.xls.Workbook;
import com.spire.xls.Worksheet;
public class RemoveTableExcel {
public static void main(String[] args) {
// Create an instance of Workbook
Workbook workbook = new Workbook();
// Load an Excel file
workbook.loadFromFile("output/CreateTableExcel.xlsx");
// Get the first worksheet
Worksheet sheet = workbook.getWorksheets().get(0);
// Remove the table
sheet.getListObjects().removeAt(0);
// Save the workbook
workbook.saveToFile("output/RemoveTableExcel.xlsx");
workbook.dispose();
}
}
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.
