Java - Delete Blank Rows and Columns in Excel

Blank rows or columns in Excel can leave your client with the impression that you are unprofessional. In this article, I am going to show you how to quickly remove the blank rows and columns from a worksheet using Spire.XLS for Java.

Spire.Doc offers the Worksheet.getRows() method and the Worksheet.getColumns() method to obtain a CellRange collection. Then, you can determine if a certain cell range (an entire row or column) is blank using isBlank() method of the IXLSRange interface.

If you use Maven, you can easily import the jar in your application by adding the following code to your project’s pom.xml file. For non-Maven projects, download the jar file from this link and add it as a dependency in your application.

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

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 the sample document
        wb.loadFromFile("C:\\Users\\Administrator\\Desktop\\sample.xlsx");

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

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

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

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