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.

6 Answers

Answers 1

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

Answers 2

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.

Answers 3

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

They already mentioned this subject. Look at Apache POI HSSF+XSSF sections Iterate over rows and cells and Iterate over cells, with control of missing / blank cells

Instead of implementing whiles or loops I use Apache Commons. Due to POI works with Iterators, you could use Apache IteratorUtils.

This Utils comes with Apache-common-collections which probably is at your classpath because its very common to find it as 3th party lib's dependency.

In order to make your code clean and polite. This would be the solution

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

Then in your main code:

Iterator<Row> rawIterator = rowIterator; Iterator<Row> cleanIterator = IteratorUtils.filteredIterator(rawIterator , new ValidRowPredicate());  while(cleanIterator.hasNext()){    Row row = cleanIterator.next();    // some code }  

You may thing that we are looping over the book 2 times. Yes we are. But look at its benefits. In this way we made portable the validation of empty cell 2 so we can reproduce it whenever is needed at any place in the code.

We also got a valid Iterator which have only valid rows so me don't need to we worry about blanks or empties. We can move this iterator through any other component or layer and we will be sure that target wont need to check it out again.

Predicates gives lot of possibilites. Like chaining Predicats by inheritance, execute predicates standalone, It can be parametrized, ...

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 and we often have'm into our classpath and nobody dare to used it. The question is Why not?

Hope it helps!

Answers 4

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.

Answers 5

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.

Answers 6

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 You Enjoyed This, Take 5 Seconds To Share It

• Facebook
• Twitter
• Google+
• StumbleUpon
• Digg
• Delicious
• LinkedIn
• Reddit
• Technorati