Java How to Iterate over Sheets Rows and Cells of Excel file using Apache POI

Tags: Apache POI Excel

In this Java Apache POI tutorial we learn how to iterate over Excel file sheets, rows and cells in Java application using the Apache POI API.

Table of contents

  1. Add Apache POI dependencies to Java project
  2. Example Excel file with Multiple Sheets
  3. How to iterate Excel sheets rows and cells using for each loop
  4. How to iterate Excel sheets rows and cells using Iterator and while loop

Add Apache POI dependencies to Java project

If you use Gradle build project, add the following dependencies to the build.gradle file. 

implementation group: 'org.apache.poi', name: 'poi', version: '5.2.2'
implementation group: 'org.apache.poi', name: 'poi-ooxml', version: '5.2.2'

If you use Maven build project, add the following dependencies to the pom.xml file.

<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi</artifactId>
    <version>5.2.2</version>
</dependency>
<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi-ooxml</artifactId>
    <version>5.2.2</version>
</dependency>

Example Excel file with Multiple Sheets

For example, we have an Excel file located at D:\SimpleSolution\Data\data.xlsx with two sheets and contents as following screenshots.

Java How to Iterate over Sheets Rows and Cells of Excel file using Apache POI

Java How to Iterate over Sheets Rows and Cells of Excel file using Apache POI

How to iterate Excel sheets rows and cells using for each loop

With Apache POI library, we can iterate over multiple sheets, rows and cells using the for-each loop as Java code below.

for(Sheet sheet : workbook) {
	for(Row row : sheet) {
		for(Cell cell : row) {
			// read cell data 
		}
	}
}

In the following example Java program, we show step by step how to read the Excel file and use the for-each loop to read data from multiple Excel sheets.

LoopExcelFileExample1.java 

import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellType;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.usermodel.WorkbookFactory;

import java.io.File;
import java.io.IOException;

public class LoopExcelFileExample1 {
    public static void main(String... args) {
        String fileName = "D:\\SimpleSolution\\Data\\data.xlsx";
        File file = new File(fileName);

        try(Workbook workbook = WorkbookFactory.create(file)) {
            for(Sheet sheet : workbook) {
                System.out.println("Sheet: " + sheet.getSheetName());
                for(Row row : sheet) {
                    for(Cell cell : row) {
                        CellType cellType = cell.getCellType();
                        if(cellType == CellType.STRING) {
                            System.out.print(cell.getStringCellValue() + "\t");
                        } else if(cellType == CellType.NUMERIC) {
                            System.out.print(cell.getNumericCellValue() + "\t");
                        }
                    }
                    System.out.println();
                }
            }
        } catch (IOException e) {
            e.printStackTrace();
        }

    }
}
The output as below. 
Sheet: Customer
First Name	Last Name	Age	
Josef	Goldsmith	23.0	
Spencer	Mckeown	31.0	
Derek	Gill	47.0	
Sheet: Employee
First Name	Last Name	Email	
Emily	Miller	emily@simplesolution.dev	
Leah	Bevan	leah@simplesolution.dev	
Fearne	Myers	fearne@simplesolution.dev

How to iterate Excel sheets rows and cells using Iterator and while loop

We can also use the following API to get the Iterator.

  • Workbook.sheetIterator() method to return iterator of the sheets in the workbook.
  • Sheet.rowIterator() method to return iterator of rows of the sheet.
  • Row.cellIterator() method to return iterator of cells of the row.

And use a while loop to traverse through the Excel file as Java code below.

Iterator<Sheet> sheetIterator = workbook.sheetIterator();
while(sheetIterator.hasNext()) {
	Sheet sheet = sheetIterator.next();
	Iterator<Row> rowIterator = sheet.rowIterator();
	while(rowIterator.hasNext()) {
		Row row = rowIterator.next();
		Iterator<Cell> cellIterator = row.cellIterator();
		while (cellIterator.hasNext()) {
			Cell cell = cellIterator.next();
			// Read cell data
		}
	}
}

In the following example Java program, we show step by step how to read the Excel file and use the Iterator and while loop to read data from multiple Excel sheets.

LoopExcelFileExample2.java 

import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellType;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.usermodel.WorkbookFactory;

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

public class LoopExcelFileExample2 {
    public static void main(String... args) {
        String fileName = "D:\\SimpleSolution\\Data\\data.xlsx";
        File file = new File(fileName);

        try(Workbook workbook = WorkbookFactory.create(file)) {
            Iterator<Sheet> sheetIterator = workbook.sheetIterator();
            while(sheetIterator.hasNext()) {
                Sheet sheet = sheetIterator.next();
                System.out.println("Sheet: " + sheet.getSheetName());
                Iterator<Row> rowIterator = sheet.rowIterator();
                while(rowIterator.hasNext()) {
                    Row row = rowIterator.next();
                    Iterator<Cell> cellIterator = row.cellIterator();
                    while (cellIterator.hasNext()) {
                        Cell cell = cellIterator.next();
                        CellType cellType = cell.getCellType();
                        if(cellType == CellType.STRING) {
                            System.out.print(cell.getStringCellValue() + "\t");
                        } else if(cellType == CellType.NUMERIC) {
                            System.out.print(cell.getNumericCellValue() + "\t");
                        }
                    }
                    System.out.println();
                }
            }
        } catch (IOException e) {
            e.printStackTrace();
        }
    }
}
The output as below. 
Sheet: Customer
First Name	Last Name	Age	
Josef	Goldsmith	23.0	
Spencer	Mckeown	31.0	
Derek	Gill	47.0	
Sheet: Employee
First Name	Last Name	Email	
Emily	Miller	emily@simplesolution.dev	
Leah	Bevan	leah@simplesolution.dev	
Fearne	Myers	fearne@simplesolution.dev

Happy Coding 😊

Java Read Password Protected Excel File using Apache POI

Java Apache POI Tutorial

Spring Boot Download Excel File Export from MySQL Database

Spring Boot Web Application Download Excel File

Java Read Excel File using Apache POI

Java Read Excel Workbook from File using Apache POI

Java Read Excel Workbook from InputStream using Apache POI

Java Create Excel File .xlsx using Apache POI

Java Add Rows to Existing Excel File using Apache POI

Java Add Sheet to Existing Excel File using Apache POI

Java Remove Sheet from Existing Excel File using Apache POI

Java Create Formula Excel Cells using Apache POI