Reading .xls and .xlsx Excel file using Apache POI Java Library


In this example, we will take a look at how to read/parse .xlsand .xlsx Excel files using Apache POI Java Library,

POI Maven Dependency

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

POI Gradle Dependency

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

Reading/Parsing Excel file with .xlsx or .xls extension

Sample Excel File:
Sample Excel File Data
Java Code:
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.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

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

/**
 * Java Program to read a Excel .xlsx or .xls file
 */
public class ExcelReaderPOIExample {

    public static void main(String[] args) {

        String excelFileName = "/Users/code2care/Desktop/Students-Details.xlsx";
        File excelXlsxFile = new File(excelFileName);
        try (FileInputStream fileInputStream = new FileInputStream(excelXlsxFile);

             XSSFWorkbook workbook = new XSSFWorkbook(fileInputStream)) {
             //reading the 1st tab
             XSSFSheet spreadsheet = workbook.getSheetAt(0); 

            for (Row cells : spreadsheet) {

                XSSFRow row = (XSSFRow) cells;
                Iterator<Cell> cellIterator = row.cellIterator();

                while (cellIterator.hasNext()) {
                    Cell cell = cellIterator.next();
                    if (cell.getCellType() == CellType.NUMERIC) {
                        System.out.print((int) cell.getNumericCellValue() + "\t");
                    } else if (cell.getCellType() == CellType.STRING) {
                        System.out.print(cell.getStringCellValue() + "\t");
                    } else if(cell.getCellType() == CellType.FORMULA) {
                        System.out.print(cell.getCellFormula() + "\t");
                    }
                }
                System.out.println(""); //break the line
            }
        } catch (IOException e) {
            e.printStackTrace();
            System.out.println("Error occurred while reading Excel file!");
        }
    }
}
Output:

Sr.No Student Name City Age
1 Justin Chicago 23
2 Alex Austin 24
3 Brain New York 21
4 Samantha San Jose 24
5 Tiffany Chicago 23



Have Questions? Post them here!
Advertisements
Try Out Code2care Dev Tools:

Advertisements

Advertisements
Code2care is an initiative to publish and share varied knowledge in programming and technical areas gathered during day-to-day learnings and development activities.

Students and software developers can leverage this portal to find solutions to their various queries without re-inventing the wheel by referring to our easy to understand posts. Technical posts might include learnings, tutorials, trouble-shooting steps, video tutorials, code snippets, how-to, blogs, articles, etc.

🎉 We are celebrating the 10th years of Code2care! Thank you for all your support!

We strongly support Gender Equality & Diversity.