CODEWITHSUNDEEP
javaapacheapache-poi
kamil
kamil

Reputation: 119

How to iterate by all not empty rows without using break or continue statement?

I want to get data from Excel file. I'm using while loop, iterator and hasNext() method to go by all rows. My problem: sometimes after rows with data there are empty rows (propably with cell type string and value "" or null), which I don't want to iterate by. So I added method isCellEmpty():

public static boolean isCellEmpty(final Cell cell) {
    if (cell == null || cell.getCellType() == Cell.CELL_TYPE_BLANK) {
        return true;
    }
    if (cell.getCellType() == Cell.CELL_TYPE_STRING && cell.getStringCellValue().isEmpty()) {
        return true;
    }
    return false;
}

and added it after starring while loop in main method:

while (rowIterator.hasNext()) {
    row = rowIterator.next();
    if (isCellEmpty(row.getCell(2))) {
        break;
    }
    // some code ...
}

But now I have a break statement. How can I iterate by all not empty rows without using break or continue? Now (with break) my algorithm is working properly - I'm getting data which I need. I'm just wondering if it's possible to write code without break or continue.

Upvotes: 1

Views: 12019

Answers (7)

Laiv
Laiv

Reputation: 316

Looks like POI have no enanchements or features to iterate just over non-empty rows.

POIS' devs posted about this subject. Check out Apache POI HSSF+XSSF sections Iterate over rows and cells and Iterate over cells, with control of missing / blank cells

Note that POI works with Iterators, so instead of loops, I would use Apache IteratorUtils from Apache Commons Collections, which is likely to be alredy in your classpath due to some transitive dependency.

In order to make your code cleaner and readable, the solution would be similiar to 

    import org.apache.commons.collections.Predicate;

    public class ValidRowPredicate implements Predicate{
        @Override
        public boolean evaluate(Object object) {
           Row row = (Row) object;
           Cell cell = row.getCell(2);
           if (cell == null || cell.getCellType() == Cell.CELL_TYPE_BLANK) {
              return false;
           } else if (cell.getCellType() == Cell.CELL_TYPE_STRING && 
                      cell.getStringCellValue().isEmpty()) {
             return false;
          }
          return true;
        }
    }

The consumer would looks like

    Iterator<Row> rawIterator = rowIterator;
    Iterator<Row> cleanIterator = IteratorUtils.filteredIterator(rawIterator , new ValidRowPredicate());

    while(cleanIterator.hasNext()){
       Row row = cleanIterator.next();
       // some code
    }

You might belive that we are iterating book 2 times but we aren't.

The first loop does, but further iterations are over a subset of valid rows. Additionaly, we made reusable the validation of empty cell #2. We also got a reliable Iterator which only have valid and computable entries.

Predicates gives lot of possibilites. Like chaining Predicats by inheritance or composition.

Its cost is in infact, the first loop all over the main Iterator. But the result worth it.

IteratorUtils as CollectionUtils are really good utils that we often have'm in the classpath but we ignore.

Upvotes: 3

Damith Ganegoda
Damith Ganegoda

Reputation: 4328

Able to remove empty rows based on the Laiv's answer's. I've modified to support passing the Row. This is the modified answer

import org.apache.commons.collections4.Predicate;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellType;
import org.apache.poi.ss.usermodel.Row;

public class ValidRowPredicate implements Predicate<Row> {

    @Override
    public boolean evaluate(Row row) {
        Cell cell = row.getCell(2);
        if (cell == null || cell.getCellType() == CellType.BLANK) {
            return false;
        } else if (cell.getCellType() == CellType.STRING &&
                cell.getStringCellValue().isEmpty()) {
            return false;
        }
        return true;
    }
}

Credits goes to Laiv

Upvotes: 0

uniknow
uniknow

Reputation: 938

Might be too simplistic but wouldn't the following be enough?

while (rowIterator.hasNext()) {
    row = rowIterator.next();
    if (!isCellEmpty(row.getCell(2))) {
        // some code ...
    }
}

If we would like to stop the iteration once an empty row/cell is encountered the following approach could be taken:

for(boolean shouldContinue = true; shouldContinue && rowIterator.hasNext(); ) {
   if (!isCellEmpty(row.getCell(2))) {
        // some code ...
    } else {
        shouldContinue = false;
    }
}

Upvotes: 0

Andy Turner
Andy Turner

Reputation: 140319

You have several options to exit a loop without using break:

  • Use some other control flow, e.g. return, throw;

  • Add an extra condition to the loop guard:

    boolean shouldContinue = true;
while (shouldContinue && rowIterator.hasNext()) {
  row = rowIterator.next();
  if (isCellEmpty(...)) {
    shouldContinue = false;
  }
}

  • Exhaust the iterator inside the loop body:

    while (rowIterator.hasNext()) {
  row = rowIterator.next();
  if (isCellEmpty(...)) {
    while (rowIterator.hasNext()) rowIterator.next();
  }
}

Or just use break. It's not so bad.

Upvotes: 1

Vikrant Kashyap
Vikrant Kashyap

Reputation: 6846

Change your function isCellEmpty() use switch than a nested if-else.

public static boolean isCellEmpty(final Cell cell) {
   switch(cell.getCellType()){
       case Cell.CELL_TYPE_BLANK :
       case cell.CELL_TYPE_STRING :
          if(StringUtils.isBlank(cell.getCellValue())
               return true;
          else
               return false;
         break;
       default :
               return false;
     break;               
   }

}

Now use this code 

boolean hasCellData= true;
while (rowIterator.hasNext() && hasCellData) {
   row = rowIterator.next();  //iterate through each rows.
   if (row == null || isCellEmpty(row.getCell(2))) {
      hasData = false;
   } else {
      //if row contains data then do your stuffs.
   }
}

This while (rowIterator.hasNext() && hasCellData) loop will stop at point of time if a row contains null values. It never check whether beyond of this row have some data.

Example :- Suppose In your sheet data is filled from row 1 to 50 but in between there is a row number 30 which is blank then this will not Iterate after row number 30.

Thanks.

Upvotes: 0

Gagravarr
Gagravarr

Reputation: 48326

If you want to keep your while loop, and avoid a break, the easiest is probably a status boolean, eg

boolean inData = true;
while (rowIterator.hasNext() && inData) {
   row = rowIterator.next();
   if (row == null || isCellEmpty(row.getCell(2))) {
      inData = false;
   } else {
      // Use the row
   }
}

Otherwise, I'd suggest reading the Apache POI documentation on iterating over rows and cells, there are other approaches you could take which might work even better!

Oh, and don't forget that rows can be null, so you need to check that before you try fetching the cell

Upvotes: 4

nyname00
nyname00

Reputation: 2566

Not sure if I get the question right, are you looking for something like this?

Row row;
while (rowIterator.hasNext() 
          && !isCellEmpty((row = rowIterator.next()).getCell(2))) {
    // do something with row
}

This would process all rows until it finds an empty cell and ends the loop.

Upvotes: 1

Related Questions