Textboxes in Excel are versatile tools that enhance the functionality of your spreadsheets. They allow users to add annotations, labels, or supplementary information, making it easier to convey important messages or insights. Whether you're looking to highlight critical data points, provide detailed explanations, or create visually appealing reports, effectively managing textboxes is essential.
In this article, you will learn how to insert a textbox, extract text from a textbox, and delete a textbox in Excel using Java and Spire.XLS for Java.
- Insert a Textbox to Excel in Java
- Extract Text from a Textbox in Excel in Java
- Delete a Textbox in 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.
<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>14.11.0</version>
</dependency>
</dependencies>
Insert a Textbox to Excel in Java
A textbox can be inserted into a worksheet using the Worksheet.getTextBoxes().addTextBox() method. This method returns an ITextBoxShape object, which provides various methods like setText(), setHAlignment(), and getFill() for configuring the text and formatting of the textbox.
To add a textbox with personalized text and formatting in Excel, follow these steps:
- Create a Workbook object.
- Load an Excel file from the specified file path.
- Retrieve a specific worksheet from the workbook.
- Insert a textbox at the desired location using Worksheet.getTextBoxes().addTextBox() method.
- Set the textbox text using ITextBoxShape.setText() method.
- Customize the textbox's appearance using other methods available in the ITextBoxShape object.
- Save the workbook as a new Excel file.
- Java
import com.spire.xls.*;
import com.spire.xls.core.ITextBoxShape;
import java.awt.*;
public class AddTextbox {
public static void main(String[] args) {
// Create a Workbook object
Workbook workbook = new Workbook();
// Load an Excel document
workbook.loadFromFile("C:\\Users\\Administrator\\Desktop\\Input.xlsx");
// Get a specific sheet
Worksheet sheet = workbook.getWorksheets().get(0);
// Add a textbox to the specified location
ITextBoxShape textBoxShape = sheet.getTextBoxes().addTextBox(5, 4, 60, 200);
// Set text of the textbox
textBoxShape.setText("This is a text box, with sample text.");
// Create a font
ExcelFont font = workbook.createFont();
font.setFontName("Times New Roman");
font.setSize(14);
font.setColor(Color.red);
// Apply font to the text
textBoxShape.getRichText().setFont(0, textBoxShape.getText().length() - 1, font);
// Set horizontal alignment
textBoxShape.setHAlignment(CommentHAlignType.Left);
// Set the fill color of the shape
textBoxShape.getFill().setFillType(ShapeFillType.SolidColor);
textBoxShape.getFill().setForeColor(Color.LIGHT_GRAY);
// Save the Excel file
workbook.saveToFile("output/AddTextBox.xlsx", ExcelVersion.Version2010);
// Dispose resources
workbook.dispose();
}
}
Extract Text from a Textbox in Excel in Java
You can access a specific textbox using the Worksheet.getTextBoxes().get() method. After retrieving it, the text can be accessed with the ITextBox.getText() method.
Here are the steps to extract text from a textbox in Excel:
- Create a Workbook object.
- Load an Excel file from the specified file path.
- Retrieve a specific worksheet from the workbook.
- Access the desired textbox using Worksheet.getTextBoxes().get() method.
- Get the textbox's text using ITextBox.getText() method.
- Java
import com.spire.xls.Workbook;
import com.spire.xls.Worksheet;
import com.spire.xls.core.ITextBox;
public class ExtractTextbox {
public static void main(String[] args) {
// Create a Workbook object
Workbook workbook = new Workbook();
// Load an Excel file
workbook.loadFromFile("C:\\Users\\Administrator\\Desktop\\TextBox.xlsx");
// Get a specific worksheet
Worksheet sheet = workbook.getWorksheets().get(0);
// Get a specific textbox
ITextBox textBox = sheet.getTextBoxes().get(0);
// Get text from the textbox
String text = textBox.getText();
// Print out result
System.out.println(text);
}
}
Delete a Textbox in Excel in Java
To delete a specific textbox from a worksheet, utilize the ITextBoxes.get().Remove() method. If you want to clear all textboxes, first obtain the count with the Worksheet.getTextBoxes().getCount() method, then loop through the collection to remove each textbox one by one.
Here's how to remove a textbox from Excel:
- Create a Workbook object.
- Load an Excel file from the desired file path.
- Access a specific worksheet within the workbook.
- Retrieve the textbox collection using Worksheet.getTextBoxes() method.
- Delete the targeted textbox using ITextBoxes.get().Remove() method.
- Save the modified workbook to a new Excel file.
- Java
import com.spire.xls.ExcelVersion;
import com.spire.xls.Workbook;
import com.spire.xls.Worksheet;
import com.spire.xls.core.ITextBoxes;
public class DeleteTextbox {
public static void main(String[] args) {
// Create a Workbook object
Workbook workbook = new Workbook();
// Load an Excel file
workbook.loadFromFile("C:\\Users\\Administrator\\Desktop\\TextBox.xlsx");
// Get a specific worksheet
Worksheet sheet = workbook.getWorksheets().get(0);
// Get textbox collection from the worksheet
ITextBoxes textBoxes = sheet.getTextBoxes();
// Remove a specific textbox
textBoxes.get(0).remove();
// Save the updated document to a different Excel file
workbook.saveToFile("output/DeleteTextbox.xlsx", ExcelVersion.Version2016);
// Dispose resources
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.
