How to loop through all the rows and cells in an e

Question

I want to use foreach to iterate through all the cells in my excel file in order to set a single foreground color. This is what I have so far.

HSSFWorkbook wb = new HSSFWorkbook();
HSSFSheet sheet = wb.createSheet("Sheet1");
sheet = wb.getSheetAt(0);

for (HSSFRow myrow : sheet){
    for (HSSFCell mycell : myrow){
        //set foreground color here
    }
}

The problem is for the statements for (HSSFRow myrow : sheet) and for (HSSFCell mycell : myrow) I am getting:

Can only iterate over an array or an instance of java.lang.Iterable

I checked HSSFSheet and HSSFRow - they implement java.lang.Iterable(Row) and java.lang.Iterable(Cell) respectively.

Answer 1

You can also do this:

for ( int r=0; r < sheet.getPhysicalNumberOfRows(); r++ )
{
    Row row = sheet.getRow(r);

    for ( int c=0; c < sheet.getRow(0).getPhysicalNumberOfCells(); c++ )
    {
        // do stuff to each cell here...
    }
}

Answer 2

Try this. It compiles ok

HSSFWorkbook wb = new HSSFWorkbook();
HSSFSheet sheet = wb.createSheet("Sheet1");
sheet = wb.getSheetAt(0);

for (Row myrow : sheet) {
    for (Cell mycell : myrow) {
        //set foreground color here
    }
}

I am using POI 3.7 Stable

Answer 3

Please consider using stream for a more declarative style iteration:

Workbook wb = WorkbookFactory.create(new FileInputStream("filename.xlsx"));    
Sheet sheet = wb.getSheetAt(0);

StreamSupport.stream(sheet.spliterator(), false)
         .filter(...)
         .map(...)
         .collect(Collectors.toList());