Gridlines are horizontal and vertical faint lines that differentiate between cells in a worksheet. All Excel worksheets have gridlines by default, but sometimes you may need to remove the gridlines as they might interfere with your work. In this article, you will learn how to programmatically show or hide/remove gridlines in an Excel worksheet using Spire.XLS for Java.

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.4.1</version>
    </dependency>
</dependencies>
    

Hide or Show Gridlines 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.
• Hide or show gridlines in the specified worksheet using Worksheet.setGridLinesVisible() method.
• Save the result file using Workbook.saveToFile() method.

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

public class HideOrShowGridlines {

    public static void main(String[] args) {

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

        //Load a sample Excel document
        workbook.loadFromFile("E:\\Files\\Test.xlsx");

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

        //Hide gridlines
        worksheet.setGridLinesVisible(false);

        ////Show gridlines
        //worksheet.setGridLinesVisible(true);

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

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

This article will demonstrate how to add textbox into Excel worksheet with Spire.XLS for Java. We could fill in the textbox with text and image.

import com.spire.xls.*;
import com.spire.xls.core.ITextBox;
import com.spire.xls.core.ITextBoxShape;

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

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

        //Get the first sheet
        Worksheet sheet = workbook.getWorksheets().get(0);        
        
        //Insert the textbox with text
        ITextBox textBox = sheet.getTextBoxes().addTextBox(5, 3, 128, 196);
        textBox.setText("Insert TextBox in Excel");
        textBox.setHAlignment(CommentHAlignType.Center);
        textBox.setVAlignment(CommentVAlignType.Center);

        //Insert the textbox with picture
        ITextBoxShape shape = sheet.getTextBoxes().addTextBox(5, 8, 128, 196);
        shape.getFill().customPicture("logo.png");
        shape.getFill().setFillType(ShapeFillType.Picture);

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

Effective screenshot after adding textbox with text and picture in Excel worksheet:

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