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
– lsiva, Commented Aug 31, 2016 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
– Akhil S Kamath, Commented Jul 7, 2017 at 7:21

centic · Accepted Answer · 2016-09-01 05:38:23Z

2

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.

answered Sep 1, 2016 at 5:38

centic

16k10 gold badges74 silver badges131 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

sandeep · Accepted Answer · 2018-08-23 18:41:21Z

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, 2018 at 18:41

sandeep

612 bronze badges

1 Comment

Stunner Over a year ago

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..

Suryanand Premkumar · Accepted Answer · 2020-04-08 03:37:13Z

-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 8, 2020 at 3:37

Suryanand Premkumar

12 bronze badges

1 Comment

Stunner Over a year ago

isn't this checking only the first cell? What about the other cells in the row

Collectives™ on Stack Overflow

Checking for empty row in excel using apache poi methods

3 Answers 3

Comments

1 Comment

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related