How to determine empty row?

Question

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

score 51 · Answer 1 · edited Nov 08 '14 at 23:28

51

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;
}

edited Nov 08 '14 at 23:28

Peter Perháč

20,434
21
120
152

answered May 06 '13 at 16:23

Takaitra

744
5
6

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 07:01

score 42 · Answer 2 · edited Nov 18 '21 at 15:20

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;
}

From POI version 3.15 to 4.2 cell.getCellTypeEnum() does not exist, instead can use if (cell != null && cell.getCellType() != Cell.CELL_TYPE_BLANK && !cell.toString().isEmpty()) { return false; } — Julian Solarte, Aug 26 '19 at 18:15
Not sure when it was introduced, but I'm on 5.2.3 and (assuming you dont need all the special isBlank checks) it seems like you can just do `if (row.getLastCellNum() == -1) {return true; } — CeePlusPlus, Aug 31 '23 at 17:53

score 5 · Answer 3 · answered Aug 31 '12 at 14:12

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

score 4 · Answer 4 · answered Aug 31 '12 at 15:01

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;
       }
    }
 }

score 4 · Answer 5 · answered Nov 15 '13 at 13:55

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;
   }

score 4 · Answer 6 · answered Jan 19 '19 at 06:12

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!

score 2 · Answer 7 · answered May 17 '19 at 10:45

2

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;
}

answered May 17 '19 at 10:45

Gangatharan Arumugam

29
1

score 1 · Answer 8 · edited Mar 01 '17 at 12:40

1

try using if(iterator.hasNext)

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

edited Mar 01 '17 at 12:40

answered Mar 01 '17 at 11:33

beginnerCoder

19
1

That iteration would be while file has any row and it would be despite row has empty cells – Dred May 05 '22 at 06:53

score 0 · Answer 9 · answered Aug 31 '12 at 14:24

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/

Alex IP · Answer 10 · 2021-05-19T01:01:51.033

0

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++;
}

edited May 19 '21 at 01:01

answered May 18 '21 at 11:41

Alex IP

21
4

score 0 · Answer 11 · answered May 18 '22 at 10:12

0

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

answered May 18 '22 at 10:12

Darshan Kumar

11
6

score 0 · Answer 12 · answered Aug 31 '23 at 17:55

At least as of poi-ooxm 5.2.3 it seems like you can do:

for (Row row : sheet) {
    if (row.getLastCellNum() == -1) {
        continue;
    }

    //do processing
}

This would not check for empty text etc, you would probably want @EpicPandaForce's robust answer. But for the case where someone on your team added a bunch of blank rows, this seems to be all you need.

How to determine empty row?

12 Answers12

Linked