Java Delete Blank Rows and Columns in Excel

In this article, we will demonstrate how to delete blank rows and columns in Excel in Java applications by using Spire.XLS for Java. We can detect if the rows and columns is blank or not using the method Worksheet.getRows().isBlank() and Worksheet.getColumns().isBlank(). After that we can easily remove rows and columns from Excel worksheets programmatically using the method Worksheet.deleteRow(int) and Worksheet.deleteColumn(int).

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

Delete Blank Rows in Excel using Java

The following are the steps to remove the blank rows in Excel:

• Create a Workbook object and load the sample Excel document using Workbook.loadFromFile() method.
• Get the desired worksheet using Workbook.getWorksheets().get() method.
• Traverse the rows and determine if the rows is blank using Worksheet.getRows().isBlank() method.
• Delete the blank rows using Worksheet.deleteRow(int) method.
• Save the document to another file using Workbook.saveToFile() method.

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


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

        //Load the sample document
        Workbook wb = new Workbook();
        wb.loadFromFile("Sample.xlsx ");

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

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


        //Save the document
        wb.saveToFile("DeleteBlankRowsAndColumns.xlsx", ExcelVersion.Version2016);


    }
}

Delete Blank Columns in Excel using Java

The following are the steps to remove the blank columns in Excel.

• Create a Workbook object and load the sample Excel document using Workbook.loadFromFile() method.
• Get the desired worksheet using Workbook.getWorksheets().get() method.
• Traverse the columns and determine if the columns is blank using Worksheet.getColumns().isBlank() method.
• Delete the blank rows using Worksheet.deleteColumn(int) method.
• Save the document to another file using Workbook.saveToFile() method.

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


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

        //Load the sample document
        Workbook wb = new Workbook();
        wb.loadFromFile("Sample01.xlsx ");

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

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

        //Save the document
        wb.saveToFile("DeleteBlankColumns.xlsx", ExcelVersion.Version2016);
    }
}