Checking for empty row in excel using apache poi methods

Question

Can I check for empty rows in excel using apache POI methods

XSSFSheet sheet = workbook.getSheetAt(i); 
sheet.getRow(0)!= null;

I am thinking of trying this, but I just wanted to check if there would be any problems later on with this. Any suggestion?
Thanks

sheet.getPhysicalNumberOfRows() will only give you valid rows else you have to go over ever cell and check if the value is empty on a particular row. — lsiva, Aug 31 '16 at 15:20
If data validations are applied in cells in particular row/rows,even it is empty will not return null.so blank check and null check validations need to done for all cells in row — Akhil S Kamath, Jul 07 '17 at 07:21

score 2 · Answer 1 · answered Sep 01 '16 at 05:38

The Excel file formats only store rows that were actually created, i.e. "sparse storage". That means for rows-indices that were not used at all you will get back null.

But there might be cases where a row was created but no cells added or all cells removed again, so to be absolutely sure you likely will also need to check the existing rows and make sure that there are cells in it.

For cells it is similar, they are only non-null for a certain cell-index if they have been created before.

score 1 · Answer 2 · answered Aug 23 '18 at 18:41

1

You can use the below method to check for empty row.

private boolean checkIfRowIsEmpty(HSSFRow row) {
        if (row == null || row.getLastCellNum() <= 0) {
            return true;
        }
        HSSFCell cell = row.getCell((int)row.getFirstCellNum());
        if (cell == null || "".equals(cell.getRichStringCellValue().getString())) {
            return true;
        }
        return false;
    }

answered Aug 23 '18 at 18:41

sandeep

61
2

For ex: if there are 10 cells in the row and first cell is empty but other 9 cells are not.. Then acccording to ur logic , ur function will still return true.. – Stunner Jun 11 '20 at 10:07

score -1 · Answer 3 · answered Apr 08 '20 at 03:37

-1

We can use this if XSSFWorkbook used

private boolean checkIfRowIsEmpty(XSSFRow row) {
    if (row == null || row.getLastCellNum() <= 0) {
        return true;
    }
    XSSFCell cell = row.getCell((int)row.getFirstCellNum());
    if (cell == null || "".equals(cell.getRichStringCellValue().getString())) {
        return true;
    }
    return false;
}

answered Apr 08 '20 at 03:37

Suryanand Premkumar

1
2

isn't this checking only the first cell? What about the other cells in the row – Stunner Jun 11 '20 at 10:05

Checking for empty row in excel using apache poi methods

3 Answers3

Linked