How to determine empty row?

How I can determine empty rows in .xls documents using Apache POI?

7 Answers
7

I'm using the following method in my POI project and it's working well. It is a variation of zeller's solution.

public static boolean isRowEmpty(Row row) {
for (int c = row.getFirstCellNum(); c < row.getLastCellNum(); c++) {
Cell cell = row.getCell(c);
if (cell != null && cell.getCellType() != Cell.CELL_TYPE_BLANK)
return false;
}
return true;
}


I have used the same piece of code but it returns false for cell value empty. If I check if(cell.getStringCellValue != null && cell.getNumericValue != null) it works for String value but for BigDecimal value it returns IllegalStateException: Cannot convert text to numeric value. Can you suggest me how to handle isRowEmpty to return true for empty row?
– Harleen
Mar 21 '16 at 11:47

And what if Cell Type is of Formula?
– Prashant Prabhakar Singh
Feb 17 '17 at 7:01

The row iterator returns only rows that contain data, however if they are completely empty then iterating by row index, getRow(index) returns null

getRow(index)

null

Solution:

Up to POI version 3.14 (thanks to Sergii Lisnychyi):

private boolean checkIfRowIsEmpty(Row row) {
if (row == null) {
return true;
}
if (row.getLastCellNum() <= 0) {
return true;
}
for (int cellNum = row.getFirstCellNum(); cellNum < row.getLastCellNum(); cellNum++) {
Cell cell = row.getCell(cellNum);
if (cell != null && cell.getCellType() != Cell.CELL_TYPE_BLANK && StringUtils.isNotBlank(cell.toString())) {
return false;
}
}
return true;
}


From POI version 3.15 to 4.2 (int getCellType() is deprecated):

int getCellType()

private boolean checkIfRowIsEmpty(Row row) {
if (row == null) {
return true;
}
if (row.getLastCellNum() <= 0) {
return true;
}
for (int cellNum = row.getFirstCellNum(); cellNum < row.getLastCellNum(); cellNum++) {
Cell cell = row.getCell(cellNum);
if (cell != null && cell.getCellTypeEnum() != CellType.BLANK && StringUtils.isNotBlank(cell.toString())) {
return false;
}
}
return true;
}


From POI version 4 (CellTypeEnum getCellTypeEnum() will return the Enum not int):

CellTypeEnum getCellTypeEnum()

private boolean checkIfRowIsEmpty(Row row) {
if (row == null) {
return true;
}
if (row.getLastCellNum() <= 0) {
return true;
}
for (int cellNum = row.getFirstCellNum(); cellNum < row.getLastCellNum(); cellNum++) {
Cell cell = row.getCell(cellNum);
if (cell != null && cell.getCellTypeEnum() != CellType.BLANK && StringUtils.isNotBlank(cell.toString())) {
return false;
}
}
return true;
}


You have to iterate through all cells in the row and check if they are all empty. I don't know any other solution...

short c;
for (c = lastRow.getFirstCellNum(); c <= lastRow.getLastCellNum(); c++) {
cell = lastRow.getCell(c);
if (cell != null && lastRow.getCell(c).getCellType() != HSSFCell.CELL_TYPE_BLANK) {
nonBlankRowFound = true;
}
}


The code is from here

Yes, but if in some row we will have in some cell = " " and empty values in another cells.
This method will be work better:

boolean isEmptyRow(Row row){
boolean isEmptyRow = true;
for(int cellNum = row.getFirstCellNum(); cellNum < row.getLastCellNum(); cellNum++){
Cell cell = row.getCell(cellNum);
if(cell != null && cell.getCellType() != Cell.CELL_TYPE_BLANK && StringUtils.isNotBlank(cell.toString())){
isEmptyRow = false;
}
}
return isEmptyRow;
}


Assuming you want to check if row n is empty, remembering that rows in Apache POI are zero based not one based, you'd want something like:

n

Row r = sheet.getRow(n-1); // 2nd row = row 1
boolean hasData = true;

if (r == null) {
// Row has never been used
hasData = false;
} else {
// Check to see if all cells in the row are blank (empty)
hasData = false;
for (Cell c : r) {
if (c.getCellType() != Cell.CELL_TYPE_BLANK) {
hasData = true;
break;
}
}
}


get the instance of CellReference and use formatAsString() on the instance. compare it with an empty String

`

if("".equals(cellRef.formatAsString())){
System.out.println("this is an empty cell");
}else{
System.out.println("Cell value : "+cellRef.formatAsString());
}


`
Reference : http://www.javabeat.net/2007/10/apache-poi-reading-excel-sheet-using-java/

try using if(iterator.hasNext)

Row nextRow = null;
Cell nextCell = null;
Iterator<Row> iterator = firstSheet.rowIterator();
if(iterator.hasNext) {
return true;
}
else {
return false;
}


By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.