Remove blank rows from Excel file in Java

In Excel, if a blank row appears in the wrong place it will prevent Excel from performing basic functions such as sorting, removing, duplicates and subtotals. Learn how to programmatically delete blank rows in an Excel document in Java. We can do such types of things by using JAVA API, jar files, etc. In this tutorial, we are going to focus on one of the jar files named spire.xls for Java. It is genuine, verified, and trusted by many universities and developers. It has a community edition and a commercial edition. The community version is limited to 5 sheets per workbook and 200 rows per sheet when creating or loading an XLS file. No limitation is enforced when creating an XLS file.

We have to first import the JAR file in our program by using the below code template/boilerplate code provided to you into our program’s .xml file similarly we are doing in servlet and JSP.

<repositories>
    <repository>
        <id> </id>
        <name> </name>
        <url> </url>
    </repository>
</repositories>
<dependencies>
    <dependency>
        <groupId> </groupId>
        <artifactId> </artifactId>
        <version> </version>
    </dependency>
</dependencies>

Also read: Fetch data from Excel file in Java

Steps to follow:

  1. Import the spire.xls jar file containing packages and sub packages which contains predefined classes, interfaces and methods.
  2. Create a Workbook object through which we can load an Excel document using <Workbook-object>.loadFromFile() method. It takes string as parameter that is name of the excel file. It is a non static method which requires Workbook object to call it just like we have to create a Scanner class object to call nextInt().
  3. Create a Worksheet object in which we can store the particular worksheet present in Workbook.
  4. We can get a particular worksheet using <Workbook-object>.getWorksheets().get() method. It is also a non static method since require object to call. get() takes integer as parameter where integer value is the index number of the particular worksheet. getWorksheets() is a method to load the particular worksheet in worksheet object.
  5. Then iterate through all used rows in the particular worksheet and determine whether the row is blank or not using XlsRange.isBlank() method.
  6. Then we can delete the blank rows using Worksheet.deleteRow() method. deleteRow() is also a non static method of Worksheet class since we require object. It takes index as parameter which is of integer type.
  7. getLastRow() is used to get the index of last row of a particular worksheet and getRows() is used to get the data of any row by taking index as parameter which is of integer type.
  8. At last save the result to another file using <Workbook-object>.saveToFile() method. It is also a non static method.

Refer to the below code:

import com.spire.xls.ExcelVersion;
import com.spire.xls.Workbook;
import com.spire.xls.Worksheet;
public class RemoveBlankRows {
    public static void main(String[] args) {
        Workbook wb = new Workbook();
        wb.loadFromFile("<name.xlsx>");
        Worksheet sheet = wb.getWorksheets().get(<index>);
        for (int i = sheet.getLastRow(); i >= 1; i--)
        {
            if (sheet.getRows()[i-1].isBlank())
            {
                sheet.deleteRow(i);
            }
        }
        wb.saveToFile("<new file name>.xlsx", <version>);
    }
}
Output

Remove blank rows from Excel file in Java

remove excel blank rows java

Leave a Reply

Your email address will not be published.