Delete Blank Rows and Columns in Excel in Java

If there are some blank rows and columns in your Excel workbook that make your excel data seem not so easy to read or edit, you need to delete them manually and accurately. The process is time-consuming. Here I’ll introduce a time-saver method to help you delete blank rows and columns more efficiently.

Add Dependencies

Before beginning, you need to add required dependencies into your Java project. There are two ways to do that.

Method 1: Please download FreeSpire.XLS for Java pack from the link, and then import Spire.Xls.jar located in the “lib” folder into your project IDEA as a dependency.

Method 2:  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>

Sample Document

Using the code

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

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

        //Get the third worksheet
        Worksheet sheet = wb.getWorksheets().get(2);

        //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);
            }
        }

        //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 blank column
                sheet.deleteColumn(j);
            }
        }

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

Output