Delete Blank Rows and Columns in Excel using Java

Delete Blank Rows and Columns in Excel using Java

We can easily remove blank rows and columns from Excel worksheets programmatically. In this article, we will learn how to delete blank rows and columns in Excel using Java.

The following topics shall be covered in this article:

Java API to Delete Blank Rows and Columns in Excel

For removing blank rows and columns from XLSX files, we will be using Aspose.Cells for Java API. Please either download the JAR of the API or just add the following pom.xml configuration in a Maven-based Java application.

<repository>
    <id>AsposeJavaAPI</id>
    <name>Aspose Java API</name>
    <url>https://repository.aspose.com/repo/</url>
</repository>
<dependency>
    <groupId>com.aspose</groupId>
    <artifactId>aspose-cells</artifactId>
    <version>21.11</version>
</dependency>

Delete Blank Rows in Excel using Java

We can delete blank rows from Excel worksheets programmatically by following the steps given below:

  • Firstly, load an Excel file using the Workbook class.
  • Next, call the Workbook.getWorksheets() method and get worksheets in WorksheetCollection object.
  • Then, access the worksheet that has the blank rows either by its index (zero-based) or by name.
  • After that, call the Cells.deleteBlankRows() method to delete the blank rows from the accessed worksheet.
  • Finally, call the save() method with output file path to save the output file.

The following sample code shows how to remove blank rows from Excel using Java.

Delete Blank Rows in Excel using Java.
Delete Blank Rows in Excel using Java.

Similarly, we can delete blank rows from all worksheets in an Excel document. We will simply iterate over WorksheetCollection and call the deleteBlankRows() method on each worksheet as shown in the following code example:

Delete Blank Columns in Excel using Java

We can delete blank columns from Excel worksheets programmatically by following the steps given below:

  • Firstly, load an Excel file using the Workbook class.
  • Next, call the Workbook.getWorksheets() method and get worksheets in WorksheetCollection object.
  • Then, access the worksheet that has the blank columns either by its index (zero-based) or by name.
  • After that, call the Cells.deleteBlankColumns() method to delete the blank columns from the accessed worksheet.
  • Finally, call the save() method with output file path to save the output file.

The following sample code shows how to remove blank columns from Excel using Java.

Delete Blank Columns in Excel using Java.
Delete Blank Columns in Excel using Java.

Update References Automatically while Deleting Blank Rows and Columns

Deleting blank rows or columns can break references in formulas, charts, and tables. For example, cell A1 in Sheet2 has a formula =Sheet1!C7 which is referring to cell C7 of the first worksheet as shown in the following figure.

A cell A1 in Sheet2 is referring to a value of cell C7 in Sheet1.
A cell A1 in Sheet2 is referring to a value of cell C7 in Sheet1.

If we remove blank rows in Sheet1, the value of C7 (650000) will be moved to cell C6. But the formula (=Sheet1!C7) will not update and cell A1 will show the value of C7 which will be 550000 in this case. We can avoid this issue by setting the DeleteOptions.setUpdateReference to true. It will ensure that the references are updated while deleting blank rows.

We can update references automatically while deleting blank rows from Excel worksheets programmatically by following the steps given below:

  • Firstly, load an Excel file using the Workbook class.
  • Next, call the Workbook.getWorksheets() method and get worksheets in WorksheetCollection object.
  • Then, access the worksheet either by index (zero-based) or by name that has the blank rows.
  • Next, create an instance of the DeleteOptions class
  • Then, call setUpdateReferences() to true. It will update the references in other sheets while deleting the blank rows.
  • After that, call the Cells.deleteBlankRows() method with DeleteOptions object as an argument to delete the blank rows from the accessed worksheet.
  • Finally, call the save() method with output file path to save the output file.

The following sample code shows how to update references while deleting the blank rows in Excel.

Similarly, we can update references while deleting the blank columns in Excel. However, we just need to call the deleteBlankColumns() method with the DeleteOptions as an argument.

Get a Free License

Please try the API without evaluation limitations by requesting a free temporary license.

Conclusion

In this article, we have learned how to delete rows and columns in Excel using Java. We have also seen how to update references while deleting rows and columns programmatically. Besides, you can learn more about Aspose.Cells for Java API using the documentation. In case of any ambiguity, please feel free to contact us on the forum.

See Also