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>15.11.3</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.

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.

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.

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.

When you work with Excel worksheets on a daily basis, you sometimes need to convert them to images for storage or preventing data from being changed when shared with others. This article will show you how to convert a whole Excel worksheet or a specific cell range to an image using Spire.XLS for Java.

• Convert a Whole Excel Worksheet to Image
• Convert a Specific Cell Range to Image

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>

Convert a Whole Excel Worksheet to Image

The following steps will demonstrate how to convert a whole Excel worksheet to an image.

• Create a Workbook instance.
• Load a sample Excel document using Workbook.loadFromFile() method.
• Get a specific worksheet of the document using Workbook.getWorksheets().get() method.
• Convert the worksheet to an image using Worksheet.saveToImage() method.

import com.spire.xls.*;

public class ExcelToImage {
    public static void main(String[] args){

        //Create a workbook instance
        Workbook workbook = new Workbook();
        //Load a sample Excel document
        workbook.loadFromFile("C:\\Users\\Test1\\Desktop\\sample.xlsx");

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

        //Save the sheet to an image
        sheet.saveToImage("output/SheetToImage.png");
    }
}

Convert a Specific Cell Range to Image

Spire.XLS for Java supports converting a specific cell range to an image by using the Worksheet.toImage (int firstRow,int firstColumn,int lastRow,int lastColumn) method. Detailed steps are listed below.

• Create a Workbook instance.
• Load a sample Excel document using Workbook.loadFromFile() method.
• Get a specific worksheet of the document using Workbook.getWorksheets().get() method.
• Create a BufferedImage instance.
• Convert a specific cell range to the ButteredImage object using Worksheet.toImage () method.
• Save the BufferedImage object to a .png image.

import com.spire.xls.Workbook;
import com.spire.xls.Worksheet;
import javax.imageio.ImageIO;
import java.awt.image.BufferedImage;
import java.io.*;

public class SpecificCellsToImage {
    public static void main(String[] args) throws IOException {

        //Create a workbook instance
        Workbook workbook = new Workbook();
        //Load a sample Excel document
        workbook.loadFromFile("C:\\Users\\Test1\\Desktop\\sample.xlsx");

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

        //Convert a specific cell range to the BufferedImage object  
        BufferedImage bufferedImage = sheet.toImage(1, 1, 7, 4);
        //Save as a .png image
ImageIO.write(bufferedImage,"PNG",new File("output/specificCellsToImage.png"));
    }
}

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.

Excel Panes can be frozen to make certain rows or columns visible even when scrolling through the worksheet. This feature is helpful for viewing or editing data in a large worksheet. For example, once the header row is frozen, we can easily find cells in the lower part of the worksheet without having to go back to the top to check the name of the header. This article will introduce how to freeze rows or/and columns in Excel using Spire.XLS for Java.

Spire.XLS for Java provides the Worksheet.freezePanes(int rowIndex, int columnIndex) method to freeze all rows and columns above and left of the selected cell which is specified by the rowIndex and the columnIndex.

This tutorial provides the code examples for the following cases:

• Freeze the Top Row
• Freeze the First Column
• Freeze the First Row and the First Column

Install Spire.XLS for Java

First, 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>

Freeze the Top Row

In order to freeze the top row, we should select the cell (2, 1) – "A2" and freeze the row above. The following are the steps to freeze the top row using Spire.XLS for Java.

• Create a Workbook object.
• Load an Excel file using Workbook.loadFromFile() method.
• Get a specific worksheet using Workbook.getWorksheets().get() method.
• Freeze the top row by passing (2, 1) to the Worksheet.freezePanes(int rowIndex, int columnIndex) method as the parameter.
• Save the workbook to another Excel file using Workbook.saveToFile() method.

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

public class FreezeTopRow {

    public static void main(String[] args) {

        //Create a Workbook instance
        Workbook workbook = new Workbook();

        //Load an Excel document
        workbook.loadFromFile("C:\\Users\\Administrator\\Desktop\\sample.xlsx");

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

        //Freeze the first row
        sheet.freezePanes(2, 1);

        //Save to another file
        workbook.saveToFile("output/FreezeTopRow.xlsx", ExcelVersion.Version2016);
    }
}

Freeze the First Column

To freeze the first column, we should select the cell (1, 2) – "B1" and freeze the column on the left. The following are the steps to freeze the first column using Spire.XLS for Java.

• Create a Workbook object.
• Load an Excel file using Workbook.loadFromFile() method.
• Get a specific worksheet using Workbook.getWorksheets().get() method.
• Freeze the top row by passing (1, 2) to the Worksheet.freezePanes(int rowIndex, int columnIndex) method as the parameter.
• Save the workbook to another Excel file using Workbook.saveToFile() method.

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

public class FreezeFirstColumn {

    public static void main(String[] args) {

        //Create a Workbook instance
        Workbook workbook = new Workbook();

        //Load an Excel document
        workbook.loadFromFile("C:\\Users\\Administrator\\Desktop\\sample.xlsx");

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

        //Freeze the first column
        sheet.freezePanes(1, 2);

        //Save to another file
        workbook.saveToFile("output/FreezeFirstColumn.xlsx", ExcelVersion.Version2016);
    }
}

Freeze the First Row and the First Column

To freeze the first row and the first column, the selected cell should be cell (2, 2) – "B2". The following are the detailed steps.

• Create a Workbook object.
• Load an Excel file using Workbook.loadFromFile() method.
• Get a specific worksheet using Workbook.getWorksheets().get() method.
• Freeze the first row and the first column by passing (2, 2) to the Worksheet.freezePanes(int rowIndex, int columnIndex) method as the parameter.
• Save the workbook to another Excel file using Workbook.saveToFile() method.

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

public class FreezeFirstRowAndFirstColumn {

    public static void main(String[] args) {

        //Create a Workbook instance
        Workbook workbook = new Workbook();

        //Load an Excel document
        workbook.loadFromFile("C:\\Users\\Administrator\\Desktop\\sample.xlsx");

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

        //Freeze the first row and the first column
        sheet.freezePanes(2, 2);

        //Save to another file
        workbook.saveToFile("output/FreezeFirstRowAndFirstColumn.xlsx", ExcelVersion.Version2016);
    }
}

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.