Java Insert or Delete Rows and Columns in Excel Worksheet

With the help of Spire.XLS for Java, we can easily insert or delete the rows or columns in the Excel worksheets programmatically. In this article, we will demonstrate how to insert or delete rows or columns in Excel in Java applications from the following parts.

• Insert Rows in Excel worksheet in Java
• Insert Columns in Excel worksheet in Java
• Delete Rows and Columns in Excel worksheet
• Delete Blank Rows and Columns in Excel worksheet

Insert Rows in Excel worksheet in Java

Spire.XLS offers Worksheet.insertRow() method to insert rows in an Excel worksheet. Here are the steps to insert rows in Excel worksheet.

• Create a Workbook instance and load a sample Excel document using Workbook.loadFromFile() method.
• Get a specified worksheet using Workbook.getWorksheets().get() method.
• Insert a row using Worksheet.insertRow(int rowIndex)method.
• Insert multiple rows using Worksheet.insertRow(int rowIndex, rowCount) method.
• Save the document to file using Workbook.saveToFile() method.

import com.spire.xls.*;


public class insertRows {
    public static void main(String[] args) {
        //Create a workbook and load an Excel file from disk.
        Workbook workbook = new Workbook();
        workbook.loadFromFile("Sample.xlsx");

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

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

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

        //Save to file.
        workbook.saveToFile("output/InsertRows.xlsx", ExcelVersion.Version2013);
    }
}

Insert Columns in Excel Sheets in Java

Spire.XLS offers Worksheet.insertColumn() method to insert columns in an Excel worksheet. Here are the steps to insert columns in a worksheet in Java.

• Create a Workbook instance and load a sample Excel document using Workbook.loadFromFile() method.
• Get a specified worksheet using Workbook.getWorksheets().get() method.
• Insert a column using Worksheet.insertColumn(int columnIndex) method.
• Insert multiple columns using Worksheet.insertColumn (int columnIndex, columnCount)method.
• Save the document to file using Workbook.saveToFile() method.

import com.spire.xls.*;


public class insertColumn {
    public static void main(String[] args) {
        //Create a workbook and load an Excel file from disk.
        Workbook workbook = new Workbook();
        workbook.loadFromFile("Sample.xlsx");

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

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

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

        //Save to file.
        workbook.saveToFile("output/InsertColumns.xlsx", ExcelVersion.Version2013);
    }
}

Delete Rows and Columns in Excel worksheet

The steps of adding the columns to an Excel sheet is similar to addition of the rows. So does the steps of the deletion of rows and columns. Here are the steps to delete rows and columns from an Excel worksheet in Java.

• Create a Workbook instance and load a sample Excel document using Workbook.loadFromFile() method.
• Get a specified worksheet using Workbook.getWorksheets().get() method.
• Delete a row using Worksheet.deleteRow(index) method.
• Delete multiple columns using Worksheet.deleteColumn(index, count) method.
• Save the document to file using Workbook.saveToFile() method.

import com.spire.xls.*;


public class deleteRowsColumns {
    public static void main(String[] args) {
        //Create a workbook and load an Excel file from disk.
        Workbook workbook = new Workbook();
        workbook.loadFromFile("InsertColumns.xlsx");

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

        //Delete the third row 
        worksheet.deleteRow(3);

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

        //Save the document to file
        workbook.saveToFile("output/DeleteRowsColumns.xlsx", ExcelVersion.Version2013);
    }
}

Delete Blank Rows and Columns from worksheet:

As a programmer, you may come across scenarios when you have to remove all the blank rows and columns in Excel worksheet. Spire.XLS for Java also supports to delete all the blank rows and columns.

• Create a Workbook instance and load a sample Excel document using Workbook.loadFromFile() method.
• Get a specified worksheet using Workbook.getWorksheets().get() method.
• Traverse all the rows and use worksheet.getRows().isBlank() to check if the row is Blank.
• If yes, delete the blank row using Worksheet.deleteRow()method.
• Use worksheet.getColumns().isBlank() to check if the column is blank.
• If yes, delete the blank columns using Worksheet.deleteColumn() method.
• Save the document to file using Workbook.saveToFile() method.

import com.spire.xls.*;


public class deleteBlankRowsColumns {
    public static void main(String[] args) {
        //Create a workbook and load an Excel file from disk.
        Workbook workbook = new Workbook();
        workbook.loadFromFile("Result.xlsx");

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


        //Delete blank rows from the worksheet.
        for (int i = worksheet.getRows().length - 1; i >= 0; i--) {
            if (worksheet.getRows()[i].isBlank()) {
                worksheet.deleteRow(i + 1);
            }
        }

        //Delete blank columns from the worksheet.
        for (int j = worksheet.getColumns().length - 1; j >= 0; j--) {
            if (worksheet.getColumns()[j].isBlank()) {
                worksheet.deleteColumn(j + 1);
            }
        }

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

Conclusion

In this article, you have learned how to manipulate rows and columns in Excel programmatically. You have seen how to delete blank rows and columns in Excel worksheets using Java. Spire.XLS for Java is a popular Excel API that enables developers to create, manage, manipulate, convert and print Excel worksheets without using Microsoft Excel. You can get more features from the Excel forum.