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

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.

score 30 · Accepted Answer · answered Dec 07 '11 at 23:58

30

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

answered Dec 07 '11 at 23:58

omarello

2,683
1
23
23

weird it does not compile in mine. these are the poi imports i added: <%@ page import="org.apache.poi.hssf.usermodel.HSSFSheet"%> <%@ page import="org.apache.poi.hssf.usermodel.HSSFWorkbook"%> <%@ page import="org.apache.poi.hssf.usermodel.HSSFCell"%> <%@ page import="org.apache.poi.hssf.usermodel.HSSFRow"%> – user582485 Dec 08 '11 at 00:00
3

for the above to compile you need the regular `org.apache.poi.ss.usermodel.Cell` and `org.apache.poi.ss.usermodel.Row` – omarello Dec 08 '11 at 00:02
cannot import those 2 though. "The import org.apache.poi.ss cannot be resolved" but i guess that a different problem altogether. Thanks for the help! – user582485 Dec 08 '11 at 00:11
Hmm, sounds like you might not have configured the poi jars correctly. If you able to import the HSSFRow then you should be able to do so for Row as well. Which version are you using? – omarello Dec 08 '11 at 00:15
i have 2.5.1 installed. can i know what version you are using? i extracted the jar file and mine does no seem to have the "ss" folder containing Row and Cell – user582485 Dec 08 '11 at 00:23
2

yep that is your problem. The example above works with 3.7, and the docs you were reading are most likely for 3.7 – omarello Dec 08 '11 at 00:28
Just wondering, how does 'Row myrow : sheet' work? should it not be 'Row myrow : sheet.getRows()'? – IntelliData Dec 17 '15 at 19:16

snovelli · Answer 2 · 2016-11-07T11:06:00.610

11

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

edited Nov 07 '16 at 11:06

answered Nov 06 '16 at 15:33

snovelli

5,804
2
37
50

score 3 · Answer 3 · edited Mar 09 '21 at 08:05

3

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

edited Mar 09 '21 at 08:05

Jacob van Lingen

8,989
7
48
78

answered Apr 15 '20 at 22:30

spectrum

379
4
11

Note the [difference](http://apache-poi.1045710.n5.nabble.com/Difference-of-getLastRowNum-and-getPhysicalNumberOfRows-td5723176.html) between `getPhysicalNumberOfRows()` and `getLastRowNum()`. – Jacob van Lingen Mar 09 '21 at 08:01

score 0 · Answer 4 · answered Apr 10 '21 at 06:07

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>

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

4 Answers4

Linked