When importing data from a database into Excel, you may encounter blank rows, columns or cells in the result file. The presence of blank rows, columns, or cells in Excel can be a visual annoyance. More importantly, it may let Excel misunderstand data ranges and cause errors when applying certain functions. In this article, I will demonstrate how to delete blank rows, columns and cells from Excel using Java.
Add Dependencies
This article uses Free Spire.XLS for Java to detect and remove blank rows, columns and cells from Excel. Before coding, you need to add needed dependencies for including Free Spire.XLS for Java into your Java project. There are two ways to do that.
Method 1: If you are using maven, you can easily import the JAR file in your application by adding the following code to your project’s pom.xml file.
<repositories>
<repository>
<id>com.e-iceblue</id>
<name>e-iceblue</name>
<url>https://repo.e-iceblue.com/nexus/content/groups/public/</url>
</repository>
</repositories>
<dependencies>
<dependency>
<groupId>e-iceblue</groupId>
<artifactId>spire.xls.free</artifactId>
<version>5.1.0</version>
</dependency>
</dependencies>
Method 2: If you are not using maven, you can download the JAR file from the official website, extract the zip file and then import the Spire.Xls.jar file under the lib folder into your project as a dependency.
Delete Blank Rows from Excel in Java
The following are the steps to delete blank rows in Excel:
- Initialize an instance of the Workbook class.
- Load an Excel file using Workbook.loadFromFile() method.
- Get the desired worksheet that contains blank rows using Workbook.getWorksheets().get(sheetIndex) method.
- Loop through all rows in the worksheet.
- Check if the current row is blank or not using Worksheet.getRows()[rowIndex].isBlank() method. If the result is true, remove it using Worksheet.deleteRow(rowIndex) method. Note the row index here is 1-based.
- Save the result file using Workbook.saveToFile() method.
import com.spire.xls.ExcelVersion;
import com.spire.xls.Workbook;
import com.spire.xls.Worksheet;
public class DeleteBlankRows {
public static void main(String []args){
//Instantiate a Workbook object
Workbook workbook = new Workbook();
//Load the Excel file
workbook.loadFromFile("Sample2.xlsx");
//Get the first worksheet
Worksheet sheet = workbook.getWorksheets().get(0);
//Loop through all rows in the worksheet
for (int i = sheet.getRows().length - 1; i >= 0; i--)
{
//Check if the current row is blank
if (sheet.getRows()[i].isBlank())
{
//Delete the blank row
sheet.deleteRow(i + 1);
}
}
//Save the result file
workbook.saveToFile("DeleteBlankRows.xlsx", ExcelVersion.Version2013);
}
}
Delete Blank Columns from Excel in Java
The following are the steps to delete blank columns from Excel:
- Initialize an instance of the Workbook class.
- Load an Excel file using Workbook.loadFromFile() method.
- Get the desired worksheet that contains blank columns using Workbook.getWorksheets().get(sheetIndex) method.
- Loop through all columns in the worksheet.
- Check if the current column is blank or not using Worksheet.getColumns()[columnIndex].isBlank() method. If the result is true, remove it using Worksheet.deleteColumn(columnIndex) method. Note the column index here is 1-based.
- Save the result file using Workbook.saveToFile() method.
import com.spire.xls.ExcelVersion;
import com.spire.xls.Workbook;
import com.spire.xls.Worksheet;
public class DeleteBlankColumns {
public static void main(String []args){
//Instantiate a Workbook object
Workbook workbook = new Workbook();
//Load the Excel file
workbook.loadFromFile("Sample2.xlsx");
//Get the first worksheet
Worksheet sheet = workbook.getWorksheets().get(0);
//Loop through all rows in the worksheet
for (int i = sheet.getColumns().length - 1; i >= 0; i--)
{
//Check if the current row is blank
if (sheet.getColumns()[i].isBlank())
{
//Delete the blank row
sheet.deleteColumn(i + 1);
}
}
//Save the result file
workbook.saveToFile("DeleteBlankColumns.xlsx", ExcelVersion.Version2013);
}
}
Delete Blank Cells from Excel in Java
The following are the steps to delete blank cells from Excel:
- Initialize an instance of the Workbook class.
- Load an Excel file using Workbook.loadFromFile() method.
- Get the worksheet that contains blank cells using Workbook.getWorksheets().get(sheetIndex) method.
- Loop through the rows and columns in the worksheet.
- Access the current cell using Worksheet.getCellRange(rowIndex, columnIndex) method. Note the row index and column index here are 1-based.
- Check if the current cell is blank or not using CellRange.isBlank() method. If the result is true, remove it using Worksheet.deleteRange(CellRange, DeleteOption) method.
- Save the result file using Workbook.saveToFile() method.
import com.spire.xls.*;
public class DeleteBlankCells {
public static void main(String []args){
//Instantiate a Workbook object
Workbook workbook = new Workbook();
//Load the Excel file
workbook.loadFromFile("Sample1.xlsx");
//Get the first worksheet
Worksheet sheet = workbook.getWorksheets().get(0);
//Get the row count
int maxRow = sheet.getRows().length;
//Get the column count
int maxColumn = sheet.getColumns().length;
//Loop through the rows and columns
for (int i = maxRow - 1; i >= 0; i--)
{
for (int j = maxColumn - 1; j >= 0; j--)
{
//Get the current cell
CellRange cell = sheet.getCellRange(i + 1, j + 1);
//Determine if the cell is blank or not
if (cell.isBlank())
{
//Remove the blank cell with DeleteOption (MoveLeft/MoveUp)
sheet.deleteRange(cell, DeleteOption.MoveLeft);
}
}
}
//Save the result file
workbook.saveToFile("DeleteBlankCells.xlsx", ExcelVersion.Version2013);
}
}
Office Dev Blog 