How do I read Excel file? - Learn Java by Examples

In this example we demonstrate how to read data from an Excel file. In this example we limit to read a string data only. After reading the data, iterating each row and cells of the Excel file we display the content to the program console.

package org.kodejava.poi;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;

import java.io.FileInputStream;
import java.io.IOException;
import java.util.ArrayList;
import java.util.Iterator;
import java.util.List;

public class ExcelReadExample {

    public static void main(String[] args) {
        // Excel file name. You can create a file name with a full
        // path information.
        String filename = "data.xls";

        // Create an ArrayList to store the data read from Excel sheet.
        List<List<HSSFCell>> sheetData = new ArrayList<>();

        try (FileInputStream fis = new FileInputStream(filename)) {
            // Create an Excel workbook from the file system.
            HSSFWorkbook workbook = new HSSFWorkbook(fis);
            // Get the first sheet on the workbook.
            HSSFSheet sheet = workbook.getSheetAt(0);

            // When we have a sheet object in hand we can iterator on
            // each sheet's rows and on each row's cells. We store the
            // data read on an ArrayList so that we can print the
            // content of the Excel to the console.
            Iterator<Row> rows = sheet.rowIterator();
            while (rows.hasNext()) {
                HSSFRow row = (HSSFRow) rows.next();
                Iterator<Cell> cells = row.cellIterator();

                List<HSSFCell> data = new ArrayList<>();
                while (cells.hasNext()) {
                    HSSFCell cell = (HSSFCell) cells.next();
                    data.add(cell);
                }
                sheetData.add(data);
            }
        } catch (IOException e) {
            e.printStackTrace();
        }

        showExcelData(sheetData);
    }

    private static void showExcelData(List<List<HSSFCell>> sheetData) {
        // Iterates the data and print it out to the console.
        for (List<HSSFCell> data : sheetData) {
            for (HSSFCell cell : data) {
                System.out.println(cell);
            }
        }
    }
}

Our program above print out the following lines:

AAAAAAAAAA
BBBBBBBBBB
CCCCCCCCCC
DDDDDDDDDD
EEEEEEEEEE
FFFFFFFFFF
GGGGGGGGGG
HHHHHHHHHH
IIIIIIIIII
JJJJJJJJJJ

Maven Dependencies

<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi</artifactId>
    <version>5.2.5</version>
</dependency>

Author
Recent Posts

Wayan

Founder at Kode Java

A programmer, runner and recreational diver, lives on the island of Bali, Indonesia. Programming in Java, Spring Framework, Hibernate / JPA, MySQL. To support me working on this project, buy me a cup of coffee, every little bit helps. Thank you.

1 Comments

Aurélien

September 30, 2017 at 10:56 pm
I wrote a library to simplify the reading and the parsing of CSV/Excel files: https://github.com/Coreoz/Windmill

With this library, the article corresponding code to read the Excel file can be reduced to:
```
try (Stream rowStream = Windmill.parse(FileSource.of(new FileInputStream("data.xls")))) {
  rowStream
    .forEach(row -> {
      for(ImportCell cell : row) {
          System.out.print(cell);
      }
      System.out.println("");
    });
}
```

1 Comments

Leave a ReplyCancel reply