How to Iterate Over Rows and Cells of Excel file in Java - Example

Hello guys, if want to iterate over all the rows in a excel sheet inside your Java program to process data row by row for example copying from Excel file to Database and you are looking for a pure Java solution then you have come to the right place. Earlier, I have shared how to read from excel file and how to write into Excel file in Java and today, I will show you how to go over all the rows in a Excel file one by one. We will use Apache POI, a popular core Java library to work with Excel files. It support XLS, XLSX and other open office Excel file formats. The library is also open source so you don't need to buy any license or pay for it. All, you need is to include the JAR file in your application's classpath.

Sometimes, you'd like to just iterate over all the rows in a sheet, or all the cells in a row. This is possible with a simple for loop.  Luckily, this is very easy. Row defines a CellIterator inner class to handle iterating over the cells (get one with a call to row.cellIterator(), and Sheet provides a rowIterator() method to give an iterator over all the rows. 

These implement the java.lang.Iterable interface to allow foreach loops.

  Sheet sheet = wb.getSheetAt(0);
    for (Row row : sheet) {
      for (Cell cell : row) {
        // Do something here
      }
    }

Java Program to loop over all rows of XLSX file in Java 

Here is a simple Java program to loop through all rows and cells of Excel file :

import java.io.File;
import java.io.IOException;
import java.text.DateFormat;
import java.text.SimpleDateFormat;
import java.util.Date;

import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
import org.apache.poi.ss.format.CellDateFormatter;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.DateUtil;
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 org.apache.poi.xssf.usermodel.XSSFCell;

/**
* Java program to iterate over all rows and cells in Excel using Apache POI.
*
* @author
*/
public class Testing {

    public static void main(String args[]) throws InvalidFormatException, IOException {
        Workbook wb = WorkbookFactory.create(new File("Authors.xlsx"));
        Sheet firstSheet = wb.getSheetAt(0); // get the first sheet

        for (Row row : firstSheet) {
            for (Cell cell : row) {
                print(cell);
            }
            System.out.println(""); // to start displaying each row in separate line
        }

    }

    /**
     * Print value of Cell regardless of its type.
     *
     * @param cell
     */
    public static void print(Cell cell) {
        switch (cell.getCellType()) {
            case XSSFCell.CELL_TYPE_STRING:
                System.out.printf("%s\t", cell.getRichStringCellValue().getString());
                break;
            case XSSFCell.CELL_TYPE_NUMERIC:
                System.out.printf("%s\t", cell.getNumericCellValue());
            default:
                break;
        }
    }

}

Output:
101.0     John       39886.0        
102.0     Peter     39136.0        
103.0     Steve     41121.0        
104.0     Joe         41053.0        
104.0     Frank     40343.0    

That's all about how to iterate over rows and cells of an Excel in Java using Apache POI library. If you notice, our date values are not displayed as they were shown in the Excel file, because Microsoft Excel internally represents dates as a numeric cell and you need to put special logic to convert those numbers into dates. Well that's a topic for another blog post though, stay tuned and keep learning Java.