In Microsoft Excel, the blank rows or columns usually indicate the boundaries of data ranges. Therefore, if a blank row or blank column appears in the wrong place will prevent Excel from recognizing the data range correctly when applying some built-in features such as sorting, removing duplicates and subtotals. In such a case, you can delete the blank rows or columns to create a tidy dataset that fit for further processing and analysis. This article will introduce how to programmatically delete blank rows and columns in an Excel document 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>14.11.0</version>
    </dependency>
</dependencies>
    

Delete Blank Rows and Columns in Excel

The detailed steps are as follows.

• Create a Workbook object.
• Load a sample Excel document using Workbook.loadFromFile() method.
• Get a specified worksheet using Workbook.getWorksheets().get() method.
• Loop through all used rows in the specified worksheet and determine whether the row is blank using XlsRange.isBlank() method.
• Delete the blank rows using Worksheet.deleteRow() method.
• Loop through all used columns in the specified worksheet and determine whether the column is blank using XlsRange.isBlank() method.
• Delete the blank columns using Worksheet.deleteColumn() method.
• Save the result to another file using Workbook.saveToFile() method.

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

public class DeleteBlankRowsAndColumns {

    public static void main(String[] args) {

        //Create a Workbook object.
        Workbook wb = new Workbook();

        //Load a sample Excel document
        wb.loadFromFile("sample.xlsx ");

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

        //Loop through all used rows
        for (int i = sheet.getLastRow(); i >= 1; i--)
        {
            //Detect if a row is blank
            if (sheet.getRows()[i-1].isBlank())
            {
                //Remove blank rows
                sheet.deleteRow(i);
            }
        }

        //Loop through all used columns
        for (int j = sheet.getLastColumn(); j >= 1; j--)
        {
            //Detect if a column is blank
            if (sheet.getColumns()[j-1].isBlank())
            {
                //Remove blank columns
                sheet.deleteColumn(j);
            }
        }

        //Save the document
        wb.saveToFile("DeleteBlankRowsAndColumns.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.

The excel zoom factor could help us to display the data on Excel worksheet clearly or completely. This article will demonstrate how to set the zoom factor on Excel work sheet in Java application by Spire.XLS for Java.

import com.spire.xls.*;

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

        //Create a workbook and load a file
        Workbook workbook = new Workbook();
        workbook.loadFromFile("Sample.xlsx");

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

        //Set the zoom factor of the sheet to 150
        sheet.setZoom(150);

        //Save the Excel file
        workbook.saveToFile("Zoomfactor.xlsx", ExcelVersion.Version2010);
    }
}

Effective screenshot after setting the zoom factor of the sheet to 150.

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.

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.

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

Usually, an Excel document may contain several worksheets with similar names such as Sheet1, Sheet2, Sheet3. In order to make the document more organized and at the same time facilitate your search, it is advisable to rename these worksheets and set different tab colors. In this article, you will learn how to achieve this task programmatically 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>14.11.0</version>
    </dependency>
</dependencies>
    

Rename Excel Worksheets and Set Tab Colors

The detailed steps are as follows:

• Create a Workbook object.
• Load a sample Excel document using Workbook.loadFromFile() method.
• Get a specified worksheet using Workbook.getWorksheets().get() method.
• Rename the specified worksheet using Worksheet.setName() method.
• Set tab color for the specified worksheet using Worksheet.setTabColor() method.
• Save the document to another file using Workbook.saveToFile() method.

import com.spire.xls.*;
import java.awt.*;

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

        // Load a sample Excel document
        workbook.loadFromFile("input.xlsx");

        //Get the first worksheet
        Worksheet worksheet = workbook.getWorksheets().get(0);
        //Rename the first worksheet and set its tab color
        worksheet.setName("Data");
        worksheet.setTabColor(Color.red);

        //Get the second worksheet
        worksheet = workbook.getWorksheets().get(1);
        //Rename the second worksheet and set its tab color
        worksheet.setName("Chart");
        worksheet.setTabColor(Color.green);

        //Get the third worksheet
        worksheet = workbook.getWorksheets().get(2);
        //Rename the third worksheet and set its tab color
        worksheet.setName("Summary");
        worksheet.setTabColor(Color.blue);

        //Save the document to file
        workbook.saveToFile("RenameSheet.xlsx", ExcelVersion.Version2010);
    }
}

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.

Worksheets are essential components of spreadsheets, primarily used for storing and managing data. In daily work, we often need to perform certain operations on worksheets in Excel. For instance, we may need to add new worksheets to an existing Excel file to append related contents to the file or divide existing data into different types or labels, which is conducive to organizing and managing the spreadsheet well. Likewise, we can also delete unnecessary or invalid worksheets to save storage space and make the file clearer. Adding or removing worksheets programmatically can avoid the inconvenience of manual operations and allow for efficient handling of large workbooks in a short time. This article introduces how to add or remove worksheets in Excel using Spire.XLS for Java.

• Add a Worksheet to an Existing Workbook in Java
• Remove a Worksheet from the Workbook 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>
    

Add a Worksheet to an Existing Workbook in Java

Spire.XLS for Java provides Workbook.getWorksheets().add() method to add new worksheets to an existing workbook. The following are the detailed steps for this operation.

• Specify input and output paths.
• Create a Workbook object.
• Load a sample Excel file using Workbook.loadFromFile() method.
• Add a new worksheet named "AddNewSheet" to the Excel file using Workbook.getWorksheets().add() method.
• Call Worksheet.getCellRange().setText() method to write text to the specific cell of the new sheet.
• Save the result file using Workbook.saveToFile() method.

import com.spire.xls.*;

public class AddWorksheet {
    public static void main(String[] args) throws Exception {

        //Specify input and output paths
        String inputFile = "sample.xlsx";
        String outputFile = "output/AddWorksheet.xlsx";

        //Create a workbook and load a file
        Workbook workbook = new Workbook();

        //Load a sample Excel file
        workbook.loadFromFile(inputFile);

        //Add a new worksheet named “AddNewSheet”
        Worksheet sheet = workbook.getWorksheets().add("AddNewSheet");

        //Write text to the cell C5 of the new worksheet
        sheet.getCellRange("C5").setText("This is a new sheet.");

        //Save the Excel file
        workbook.saveToFile(outputFile, ExcelVersion.Version2010);
        workbook.dispose();
    }
}

Remove a Worksheet from the Workbook in Java

Removing unnecessary worksheets from an Excel file can reduce the file size. Spire.XLS for Java provides Worksheet.remove() method to remove worksheets from an existing workbook. The following are the detailed steps.

• Specify input and output paths.
• Create a Workbook object.
• Load a sample Excel file using Workbook.loadFromFile() method.
• Get the second worksheet of this file using Workbook.getWorksheets().get() method.
• Remove it from the file using Worksheet.remove() method.
• Save the result file using Workbook.saveToFile() method.

import com.spire.xls.*;

public class RemoveWorksheet {
    public static void main(String[] args) throws Exception {

        //Specify input and output paths
        String inputFile = "sample.xlsx";
        String outputFile = "output/RemoveWorksheet.xlsx";

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

        //Load a sample Excel file
        workbook.loadFromFile(inputFile);

        //Get the second worksheet and remove it
        Worksheet sheet1 = workbook.getWorksheets().get(1);
        sheet1.remove();

        //Save the Excel file
        workbook.saveToFile(outputFile, ExcelVersion.Version2010);
        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.