fbpx

Read Excel sheet in Java

The example below shows opening and reading Excel documents, using Apache POI library.

File file = new File("sample.xlsx");
Workbook workbook = WorkbookFactory.create(file);

workbook.sheetIterator().forEachRemaining(sheet -> {
    for(int i = 0; i < sheet.getLastRowNum(); i++) {
        Row row = sheet.getRow(i);
        for(int j = 0; j < row.getLastCellNum(); j++) {
            Cell cell = row.getCell(j);
            System.out.println(cell.getStringCellValue());
        }
    }
});

The above example prints a string value of all cells in a sequential order, for all sheets present inside the workbook.

Maven import for Apache POI library is mentioned below. The library can be downloaded from other sources as well.

<!-- https://mvnrepository.com/artifact/org.apache.poi/poi -->
<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi</artifactId>
    <version>3.17</version>
</dependency>

<!-- https://mvnrepository.com/artifact/org.apache.poi/poi-ooxml -->
<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi-ooxml</artifactId>
    <version>3.17</version>
</dependency>

The Workbook object represents a single Excel document. The Excel may contain multiple sheets within it, wherein each sheet is represented by the Sheet object.

workbook.sheetIterator().forEachRemaining(sheet -> {});

Is used to iterate over all sheets present inside the Workbook. It is possible for the Workbook to not have any sheet.

sheet.getLastRowNum();

Gets the number of rows present in the respective sheet. This value is used to iterate through all rows present in the sheet. The row count is expected to differ per sheet.

Row row = sheet.getRow(i);

Gets the complete row at the specified row index. The row indexes start from 0. A row is a collection of cells, where each cell is a column. A row does not contain data values. A Row contains cells and each Cell in the Row contains a data value.

row.getLastCellNum();

Is used to get the number of cells in the Row. Typically each row in the sheet is expected to have same number of cells. However if some rows have merged cells, the number of cells in one row maybe different than the number of cells in another row in the same sheet. It is thereby important to get the cell count for each row and then iterate through each cell in the respective row.

Cell cell = row.getCell(j);

Gets a single Cell present at position j from within the row. The cell index starts from 0.

cell.getStringCellValue();

Assuming the cell contains a String value, the same can be got by using the getStringCellValue() function on the cell object. The cell may however contain numerical data, date-time field or maybe a formula. The contents of the cell need to be picked up appropriately to prevent errors.

%d bloggers like this: