CODEWITHSUNDEEP
javaapache-poi
user582485
user582485

Reputation: 539

How to loop through all the rows and cells in an excel file

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.

Upvotes: 20

Views: 69350

Answers (4)

Ajay Kumar
Ajay Kumar

Reputation: 3250

Here is what I did to keep the things in simplest possible way - to loop through all rows and columns :

import java.io.FileInputStream;
import java.io.IOException;
import java.util.Iterator;

import org.apache.poi.ss.usermodel.DataFormatter;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

public class ReadExcelTest {

    @SuppressWarnings("resource")
    public static void main(String[] args) throws IOException {
        
        // Read input file
        FileInputStream fis = new FileInputStream("sample.xlsx");

        XSSFWorkbook wbook = new XSSFWorkbook(fis);
        XSSFSheet sheet = wbook.getSheetAt(0);
        
        // formatter for all your data to string (in this example) from date/number etc
        DataFormatter formatter = new DataFormatter();
        
        // Below code is self explanatory

        Iterator<Row> rowIterator = sheet.iterator();

        while (rowIterator.hasNext()) {

            Row row = rowIterator.next();
            // Since I needed only five columns, I hardcoded the column numbers
            System.out.println(formatter.formatCellValue(row.getCell(0)));
            System.out.println(formatter.formatCellValue(row.getCell(1)));
            System.out.println(formatter.formatCellValue(row.getCell(2)));
            System.out.println(formatter.formatCellValue(row.getCell(3)));
            System.out.println(formatter.formatCellValue(row.getCell(4)));

        }

    }
}

Disclaimer: I used the latest POI as on date:

    <dependency>
        <groupId>org.apache.poi</groupId>
        <artifactId>poi</artifactId>
        <version>5.0.0</version>
    </dependency>

Upvotes: 0

spectrum
spectrum

Reputation: 397

You can also use a common for-loop:

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

    for (int j = 0; j < row.getPhysicalNumberOfCells(); j++) {
        final Cell cell = row.getCell(j);
        // do stuff to each cell here...
    }
}

Upvotes: 4

snovelli
snovelli

Reputation: 6058

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());

Upvotes: 12

omarello
omarello

Reputation: 2693

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

Upvotes: 30

Related Questions