Delete Blank Rows and Columns in Excel using Java

Requirement

I have an Excel document as below and there are some blank rows and columns in the second worksheet. Now I want to delete all blank rows and columns once through running Java codes in the background, and the operation can be finished without installing Microsoft Office. 

Solution

I need to use a third-party library to accomplish the operation above. After finding and testing some libraries, I find a free and professional API called Free Spire.XLS for Java can do it best.

Before running codes, I need to install JDK and Intellij IDEA, and then add a Jar file in the library to IDEA. I can get it through the official download link, or refer to it by using the following Maven configuration.

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

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 an Excel document
        Workbook wb = new Workbook();
        wb.loadFromFile("C:\\Users\\Test1\\Desktop\\Sample.xlsx");

        //Get the second worksheet
        Worksheet sheet = wb.getWorksheets().get(1);

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

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

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

Output