'How to determine empty row?
How I can determine empty rows in .xls documents using Apache POI?
Solution 1:[1]
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;
}
Solution 2:[2]
The row iterator returns only rows that contain data, however if they are completely empty then iterating by row index, getRow(index) returns 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):
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):
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 5.1 (CellTypeEnum getCellTypeEnum() renamed to 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.getCellType() != CellType.BLANK && StringUtils.isNotBlank(cell.toString())) {
return false;
}
}
return true;
}
Solution 3:[3]
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
Solution 4:[4]
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;
}
Solution 5:[5]
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:
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;
}
}
}
Solution 6:[6]
If you are using apache-poi [4+]:
Then the below method works for you. As the other methods suggested did not work for me, I had to do it this way.
public static boolean isRowEmpty(Row row) {
boolean isEmpty = true;
DataFormatter dataFormatter = new DataFormatter();
if(row != null) {
for(Cell cell: row) {
if(dataFormatter.formatCellValue(cell).trim().length() > 0) {
isEmpty = false;
break;
}
}
}
return isEmpty;
}
The method dataFormatter.formatCellValue(cell) would return "", an empty / ZERO length string when the cell is either null or BLANK.
The import statements for your reference:
import org.apache.poi.ss.usermodel.DataFormatter;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Cell;
Hope this helps!
Solution 7:[7]
boolean isEmptyRow(Row row) {
boolean isEmpty=true;
String data="";
for(Cell cell:row) {
data=data.concat(cell.getStringCellValue());
}
if(!data.trim().isEmpty()) {
isEmpty=false;
}
return isEmpty;
}
Solution 8:[8]
try using if(iterator.hasNext)
Row nextRow = null;
Cell nextCell = null;
Iterator<Row> iterator = firstSheet.rowIterator();
if(iterator.hasNext) {
return true;
}
else {
return false;
}
Solution 9:[9]
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/
Solution 10:[10]
Row == null is work in my case.
int total_row = mySheet.getLastRowNum();
int current_row = 0;
HSSFRow hssf_Row;
while (current_row <= total_row) {
hssf_Row = mySheet.getRow(current_row);
if (hssf_Row == null) Log.d("empty row","row=" + String.valueOf(current_row));
current_row++;
}
Solution 11:[11]
you could also use row.cellIterator() method which returns a iterator of the row containing all the cells.
If the row is blank
Iterators.size(row.cellIterator()) would be zero
Sources
This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.
Source: Stack Overflow