Processing Excel files in Java is a common requirement for data-driven applications. One common task is identifying empty rows in an Excel sheet. Let us delve into understanding how to check for empty rows in an Excel file using Java.
1. Introduction
Empty rows in an Excel file may appear when a row has no content in any of its cells. Accurately detecting these rows is crucial when processing data to avoid unexpected errors or miscalculations. For this tutorial, consider the following Excel sheet and save it as example.xlsx on your local machine:
Data 1 Data 2 Data 3 Data 4
2. Using Apache POI
Apache POI is a popular library for working with Microsoft Office files. Below is an example of identifying empty rows in an Excel file using Apache POI:
import org.apache.poi.ss.usermodel.*;
import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
public class EmptyRowPOI {
public static void main(String[] args) throws IOException {
FileInputStream file = new FileInputStream(new File("example.xlsx"));
Workbook workbook = WorkbookFactory.create(file);
Sheet sheet = workbook.getSheetAt(0);
for (Row row: sheet) {
boolean isEmpty = true;
for (Cell cell: row) {
if (cell.getCellType() != CellType.BLANK && cell.toString().trim().length() > 0) {
isEmpty = false;
break;
}
}
if (isEmpty) {
System.out.println("Row " + row.getRowNum() + " is empty.");
}
}
workbook.close();
file.close();
}
}
2.1 Code explanation
The main class, EmptyRowPOI, begins by reading an Excel file named example.xlsx using a FileInputStream. The Workbook object is created via WorkbookFactory.create(), allowing the program to work with the Excel file, irrespective of whether it is in .xls or .xlsx format. A specific sheet (the first one, in this case) is accessed using the getSheetAt(0) method.
The program iterates over each Row in the sheet using a for-each loop. For every row, a flag isEmpty is initialized as true. Within the row, the program iterates through each Cell. If a cell is neither blank (CellType.BLANK) nor empty after trimming its content, the isEmpty flag is set to false, and the loop breaks, indicating that the row is not empty.
If the isEmpty flag remains true after checking all the cells in a row, the program considers the row empty and prints its row number using row.getRowNum(). Finally, the program ensures the proper release of resources by closing the Workbook and FileInputStream.
2.2 Code output
When the above code is executed, the following output will be displayed in the IDE console.
Row 2 is empty. Row 4 is empty.
3. Using JExcel
JExcelAPI is another library for interacting with Excel files. The following code demonstrates how to identify empty rows:
import jxl.Workbook;
import jxl.Sheet;
import jxl.Cell;
import java.io.File;
public class EmptyRowJExcel {
public static void main(String[] args) throws Exception {
Workbook workbook = Workbook.getWorkbook(new File("example.xls"));
Sheet sheet = workbook.getSheet(0);
for (int i = 0; i < sheet.getRows(); i++) {
boolean isEmpty = true;
for (int j = 0; j < sheet.getColumns(); j++) {
Cell cell = sheet.getCell(j, i);
if (cell.getContents().trim().length() > 0) {
isEmpty = false;
break;
}
}
if (isEmpty) {
System.out.println("Row " + i + " is empty.");
}
}
workbook.close();
}
}
3.1 Code explanation
The program begins by creating a Workbook instance from an Excel file named example.xls. It retrieves the first sheet of the workbook using the getSheet(0) method.
To process the rows, the code iterates through each row of the sheet using a for loop, where i represents the current row index. A boolean flag, isEmpty, is initialized to true for each row. Within the row, another loop iterates through each column, represented by j. For every cell, the getCell(j, i) method retrieves its content, and the trim() method ensures that leading and trailing spaces are ignored. If any cell in the row contains non-empty content, the isEmpty flag is set to false, and the inner loop breaks.
After checking all the cells in a row, if the isEmpty flag remains true, the program prints the row number, indicating that the row is empty. Finally, the Workbook is closed to release resources properly.
3.2 Code output
When the above code is executed, the following output will be displayed in the IDE console.
Row 2 is empty. Row 4 is empty.
4. Using fastexcel
FastExcel is a lightweight library designed for processing large Excel files efficiently. Here’s an example of detecting empty rows:
import com.github.pjfanning.xlsx.StreamingReader;
import org.apache.poi.ss.usermodel.*;
import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
public class EmptyRowFastExcel {
public static void main(String[] args) throws IOException {
FileInputStream file = new FileInputStream(new File("example.xlsx"));
Workbook workbook = StreamingReader.builder().open(file);
Sheet sheet = workbook.getSheetAt(0);
for (Row row: sheet) {
boolean isEmpty = true;
for (Cell cell: row) {
if (cell.getCellType() != CellType.BLANK && cell.toString().trim().length() > 0) {
isEmpty = false;
break;
}
}
if (isEmpty) {
System.out.println("Row " + row.getRowNum() + " is empty.");
}
}
workbook.close();
file.close();
}
}
4.1 Code explanation
The provided Java code demonstrates how to check for empty rows in an Excel file using the FastExcel library, which is designed for the efficient handling of large Excel files. The program starts by opening an Excel file named example.xlsx through a FileInputStream. The StreamingReader.builder().open(file) method is used to create a Workbook instance, enabling streaming access to the file without loading it entirely into memory. This makes it particularly suitable for large datasets.
The program retrieves the first sheet of the workbook using getSheetAt(0). It then iterates through each Row in the sheet using a for-each loop. A boolean variable, isEmpty, is initialized as true for every row. Within the row, another loop processes each Cell. The condition checks whether the cell is non-blank (cell.getCellType() != CellType.BLANK) and has non-empty content after trimming whitespace (cell.toString().trim().length() > 0). If any such cell is found, the isEmpty flag is set to false, and the loop breaks.
If the isEmpty flag remains true, it indicates that the row contains no meaningful data, and the program prints the row number using row.getRowNum(). After processing all rows, the program ensures proper resource management by closing both the Workbook and FileInputStream objects.
4.2 Code output
When the above code is executed, the following output will be displayed in the IDE console.
Row 2 is empty. Row 4 is empty.
5. Conclusion
In this article, we explored different approaches for identifying empty rows in Excel files using Java. Each library has its strengths:
- Apache POI: Robust and feature-rich for most use cases.
- JExcel: Suitable for legacy projects but less active in updates.
- Fastexcel: Excellent for large-scale data processing due to its streaming capabilities.
