Sunday, April 21, 2024
HomeJavaEasy methods to Iterate Over Rows and Cells of Excel file in...

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.

Generally, you want to only iterate over all of the rows in a sheet, or all of the cells in a row. That is potential with a easy for loop.  Fortunately, that is very straightforward. Row defines a CellIterator interior class to deal with iterating over the cells (get one with a name to row.cellIterator(), and Sheet supplies a rowIterator() technique to present an iterator over all of the rows. 

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

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

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

Right here is an easy Java program to loop by all rows and cells of Excel file :

How to Iterate Over Rows and Cells of Excel file in Java - Example
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.



RELATED ARTICLES

LEAVE A REPLY

Please enter your comment!
Please enter your name here

Most Popular

Recent Comments