Java: Insert or Delete Rows and Columns in Excel

In MS Excel, rows arrange objects from left to right and are identified by row numbers. While columns, on the contrary, arrange objects from top to bottom and are identified by column headers. When processing data in Excel, sometimes you may need to insert additional columns and rows in the middle of your data table, or delete unwanted columns and rows. In this article, you will learn how to complete the below tasks programmatically using Spire.XLS for Java.

• Insert a Row and a Column in Excel
• Insert Multiple Rows and Columns in Excel
• Delete a Specific Row and Column in Excel
• Delete Multiple Rows and Columns in Excel

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 Row and a Column in Excel

Below are the steps to insert a blank row and a blank column in an Excel worksheet.

• Create a Workbook instance.
• Load a sample Excel file using Workbook.loadFromFile() method.
• Get a specified worksheet using Workbook.getWorksheets().get() method.
• Insert a row into the worksheet using Worksheet.insertRow(int rowIndex) method.
• Insert a column into the worksheet using Worksheet.insertColumn(int columnIndex) method.
• Save the result file using Workbook.saveToFile() method.

import com.spire.xls.*;

public class InsertRowandColumn {
    public static void main(String[] args) throws Exception {
        //Create a Workbook instance
        Workbook workbook = new Workbook();

        //Load a sample Excel file
        workbook.loadFromFile("Sample.xlsx");

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

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

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

        //Save the result file
        workbook.saveToFile("InsertRowAndColumn.xlsx", ExcelVersion.Version2013);
    }
}

Insert Multiple Rows and Columns in Excel

Below are the steps to insert multiple blank rows and columns in an Excel worksheet.

• Create a Workbook instance.
• Load a sample Excel file using Workbook.loadFromFile() method.
• Get a specified worksheet using Workbook.getWorksheets().get() method.
• Insert multiple rows into the worksheet using Worksheet.insertRow((int rowIndex, int rowCount) method.
• Insert multiple columns into the worksheet using Worksheet.insertColumn(int columnIndex, int columnCount) method.
• Save the result file using Workbook.saveToFile() method.

import com.spire.xls.*;

public class InsertRowsandColumns {
    public static void main(String[] args) throws Exception {
        //Create a Workbook instance
        Workbook workbook = new Workbook();

        //Load a sample Excel file
        workbook.loadFromFile("Sample.xlsx");

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

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

        //Insert multiple columns into the worksheet.
        worksheet.insertColumn(4, 2);
        
        //Save the result file
        workbook.saveToFile("InsertRowsAndColumns.xlsx", ExcelVersion.Version2013);
    }
}

Delete a Specific Row and Column in Excel

Below are the steps to delete a specific row and column in an Excel worksheet.

• Create a Workbook instance.
• Load a sample Excel file using Workbook.loadFromFile() method.
• Get a specified worksheet using Workbook.getWorksheets().get() method.
• Delete a specific row from the worksheet using Worksheet.deleteRow(int index) method.
• Delete a specific column from the worksheet using Worksheet.deleteColumn(int columnIndex) method.
• Save the result file using Workbook.saveToFile() method.

import com.spire.xls.*;

public class DeleteRowColumn {
    public static void main(String[] args) throws Exception {
        //Create a Workbook instance
        Workbook workbook = new Workbook();

        //Load the sample Excel file
        workbook.loadFromFile("Sample.xlsx");

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

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

        //Delete a specific column from the worksheet
        worksheet.deleteColumn(1);

        //Save to file.
        workbook.saveToFile("DeleteRowAndColumn.xlsx", ExcelVersion.Version2013);

    }
}

Delete Multiple Rows and Columns in Excel

Below are the steps to delete multiple rows and columns in an Excel worksheet.

• Create a Workbook instance.
• Load a sample Excel file using Workbook.loadFromFile() method.
• Get a specified worksheet using Workbook.getWorksheets().get() method.
• Delete multiple rows from the worksheet using Worksheet.deleteRow(int index, int count) method.
• Delete multiple columns from the worksheet using Worksheet.deleteColumn(int columnIndex, int count) method.
• Save the result file using Workbook.saveToFile() method.

import com.spire.xls.*;

public class DeleteRowColumn {
    public static void main(String[] args) throws Exception {
        //Create a Workbook instance
        Workbook workbook = new Workbook();

        //Load the sample Excel file
        workbook.loadFromFile("E:\\Files\\Sample.xlsx");

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

        //Delete multiple rows from the worksheet
        worksheet.deleteRow(5, 3);

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

        //Save to file.
        workbook.saveToFile("DeleteRowsAndColumns.xlsx", ExcelVersion.Version2013);

    }
}

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.