Insert, Hide and Delete Excel Rows and Columns in Java

If you need to add new data or delete existing data in an Excel worksheet, you can insert or delete rows or columns in the worksheet. Besides, you will have a cleaner spreadsheet without deleting data you might need later by hiding rows and columns. In this article, I’ll show you how to insert, hide and delete Excel rows and columns by using Free Spire.XLS for Java.

ADD JAR TO PROJECT

Download the latest version of Free Spire.XLS forJava, unzip it and add Spire.Xls.jar located in the “lib” folder to your Java project as a dependency.

Of course, if you are using maven, you need to add the following code to your project’s pom.xml file.

<repositories>  

        <repository>  

            <id>com.e-iceblue</id>  

            <name>e-iceblue</name>  

            <url>http://repo.e-iceblue.com/nexus/content/groups/public/</url>  

        </repository>  

</repositories>  

<dependencies>  

    <dependency>  

        <groupId>e-iceblue</groupId>  

        <artifactId>spire.xls.free</artifactId>  

        <version>2.2.0</version>  

    </dependency>  

</dependencies>

Using the code
 1. Insert Rows and Columns in Excel 
By using the InsertRow and InsertColumn methods, we can insert one or several rows and
columns in Excel workbook. Below are the code snippets.

import com.spire.xls.*;

public class AddRowsAndColumns {
    public static void main(String[] args) {
        //Load the sample Excel file
        Workbook workbook = new Workbook();
        workbook.loadFromFile("C:\\Users\\Test1\\Desktop\\Sample.xlsx");

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

        //Insert a row into the worksheet.
        worksheet.insertRow(2);

        //Insert a column into the worksheet.
        worksheet.insertColumn(2);

        //Insert multiple rows into the worksheet.
        worksheet.insertRow(5, 2);

        //Insert multiple columns into the worksheet.
        worksheet.insertColumn(5, 2);

        //Save to file.
        workbook.saveToFile("output/InsertRowsAndColumns.xlsx", ExcelVersion.Version2013);
    }
}
Effective screenshot after inserting the empty rows and columns:


2.Hide Rows and Columns in Java
import com.spire.xls.*;

public class HideRowsAndColumns {
    public static void main(String[] args) {
        //Load the sample Excel file
        Workbook workbook = new Workbook();
        workbook.loadFromFile("C:\\Users\\Test1\\Desktop\\InsertRowsAndColumns.xlsx");

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

        //Hide the column of the worksheet.
        worksheet.hideColumn(2);

        //Hide the row of the worksheet
        worksheet.hideRow(3);

        //Save to file.
        workbook.saveToFile("output/HideRowAndColumn.xlsx", ExcelVersion.Version2013);
    }
}
Effective screenshot after hiding the second column and the third row:

3. Delete Rows and Columns in Excel

import com.spire.xls.*;

public class DeleteRowsAndColumns {
    public static void main(String[] args) {
        //Load the sample Excel file
        Workbook workbook = new Workbook();
        workbook.loadFromFile("C:\\Users\\Test1\\Desktop\\InsertRowsAndColumns.xlsx");

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

        //Delete a row from the worksheet.
        worksheet.deleteRow(2);

        //Delete multiple columns from the worksheet.
        worksheet.deleteColumn(5, 2);

        //Save to file.
        workbook.saveToFile("output/DeleteRowAndColumn.xlsx", ExcelVersion.Version2013);
    }
}
Effective screenshot after deleting the second row and the fifth and sixth columns: