How to read Excel file using java and Apache poi API ?

Apache POI

The Apache POI Project classes are very useful for generating/accessing data from MS Excel files containing text (numeric/non-numeric), lists, rows and images. The library is especially useful in combination with Javatechnology-based classes. The Apache POI API enables a Java developer to access Microsoft Excel data files programmatically. See more about poi

Maven Dependency

Below maven dependency required to get poi jars

<dependency>

<groupId>org.apache.poi</groupId>

<artifactId>poi</artifactId>

<version>3.7</version>

</dependency>

Creating the HSSFWorkbook Object

HSSFWorkbook is the layout of the entire data to be accessed in the form of various objects. All kinds of text elements can be accessed via HSSFWorkbook. We can create an instance of the HSSFWorkbook object as follows.

//filename is the name of the MS Excel file which will be accessed/read/ written.

// Constructor of HSSFWorkbook takes name of the file.

Accessing data from HSSFWorkbook instance

Once the HSSFWorkbook instance is created as mentioned in the first step, then we are ready to access data from given excel file as follows.

HSSFSheet sheet = workbook.getSheetAt (int sheetindex);

//sheetindex is the index of the sheet of Excel file (starts from 0).

HSSFSheet sheet = workbook.getSheet(String sheetname);

//sheetname is the name of the sheet of Excel file

HSSFRow row = sheet.getRow(int rowno);

//rowno is the row number.

Java.util.Iterator iterator = sheet.rowIterator();

HSSFRow row = (HSSFRow) rowIterator.next();

HSSFSheet.rowIterator()

//returns java.util.Iterator, which fetches all rows of the sheet to HSSFRow sequentially.

HSSFCell cell = row.getCell(short cellno);

HSSFRow.getCell() // returns the cell of a particular row to HSSFCell.

String stringValue = cell.getStringCellValue();

double numericValue = cell.getNumericCellValue();

//Method HSSFCell.getStringCellValue() helps us to read string data and method

//HSSFCell.getNumericCellValue helps us to read numeric data from the given cell.

Example

In this example we will see how to read excel cell using poi. Create a maven project with above poi dependency in pom.xml file and create an excel file with below data (This file should be placed in project folder as Student.xls)