Easy methods to Iterate Over Rows and Cells of Excel file in Java

Howdy guys, if wish to iterate over all of the rows in a excel sheet inside your Java program to course of information row by row for instance copying from Excel file to Database and you’re in search of a pure Java resolution then you might have come to the suitable place. Earlier, I’ve shared the way to learn from excel file and the way to write into Excel file in Java and at present, I’ll present you the way to go over all of the rows in a Excel file one after the other. We are going to use Apache POI, a well-liked core Java library to work with Excel recordsdata. It help XLS, XLSX and different open workplace Excel file codecs. The library can also be open supply so that you needn’t purchase any license or pay for it. All, you want is to incorporate the JAR file in your utility’s classpath.

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

import java.io.File;
import java.io.IOException;
import java.textual content.DateFormat;
import java.textual content.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;


public class Testing {

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

        for (Row row : firstSheet) {
            for (Cell cell : row) {
                print(cell);
            }
            System.out.println(""); 
        }

    }

    
    public static void print(Cell cell) {
        swap (cell.getCellType()) {
            case XSSFCell.CELL_TYPE_STRING:
                System.out.printf("%st", cell.getRichStringCellValue().getString());
                break;
            case XSSFCell.CELL_TYPE_NUMERIC:
                System.out.printf("%st", 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 is all about the way to iterate over rows and cells of an Excel in Java utilizing Apache POI library. If you happen to discover, our date values are usually not displayed as they had been proven within the Excel file, as a result of Microsoft Excel internally represents dates as a numeric cell and you must put particular logic to transform these numbers into dates. Properly that is a subject for an additional weblog submit although, keep tuned and continue to learn Java.