In this post we’ll see how you can read Excel sheet in Java using Apache POI library.
- Refer How to Write Excel File in Java Using Apache POI to see how to write to excel sheet using Java program.
Apache POI is an open source library using which you can read and write Excel files from your Java program.
Maven Dependencies
You will need to include following maven dependencies for Apache POI in your pom.xml file.
<dependency> <groupId>org.apache.poi</groupId> <artifactId>poi</artifactId> <version>4.0.0</version> </dependency> <dependency> <groupId>org.apache.poi</groupId> <artifactId>poi-ooxml</artifactId> <version>4.0.0</version> </dependency>
Here the first dependency is required for working with older excel format having .xls extension.
Second dependency is required for working with the OOXML based file format having .xlsx extension.
These dependencies add the following jars-
poi-4.0.0.jar commons-codec-1.10.jar commons-collections4-4.2.jar poi-ooxml-4.0.0.jar poi-ooxml-schemas-4.0.0.jar xmlbeans-3.0.1.jar commons-compress-1.18.jar curvesapi-1.04.jar
Apache POI classes for working with Excel spreadsheets
Before getting into example for reading excel spreadsheet in Java using Apache POI first let’s get some idea about the classes that are used in the code.
With in Apache POI there are two implementations for two types of spread sheets-
- HSSF- It is the POI Project's pure Java implementation of the Excel '97(-2007) file format (.xls).
- XSSF- It is the POI Project's pure Java implementation of the Excel 2007 OOXML (.xlsx) file format.
Note that there is a component module that attempts to provide a common high level Java API to both OLE2 and OOXML document formats which is SS for Excel workbooks. So it's better to use SS package as much as possible so that one implementation can be replaced by another seamlessly.
The following interfaces from the SS model will be used in the example to read excel file in Java using Apache POI-
- org.apache.poi.ss.usermodel.Workbook- High level representation of a Excel workbook. This is the first object most users will construct whether they are reading or writing a workbook. Implementing classes for the HSSF and XSSF respectively are HSSFWorkbook and XSSFWorkbook.
- org.apache.poi.ss.usermodel.Sheet- High level representation of a Excel worksheet. Implementing classes for the HSSF and XSSF respectively are HSSFSheet and XSSFSheet.
- org.apache.poi.ss.usermodel.Row- High level representation of a row of a spreadsheet. Implementing classes for the HSSF and XSSF respectively are HSSFRow and XSSFRow.
- org.apache.poi.ss.usermodel.Cell- High level representation of a cell in a row of a spreadsheet. Cells can be numeric, formula-based or string-based (text). Implementing classes for the HSSF and XSSF respectively are HSSFCell and XSSFCell.
Apart from these interfaces there is a class WorkbookFactory that is used to create the appropriate workbook.
WorkbookFactory- Factory for creating the appropriate kind of Workbook (be it HSSFWorkbook or XSSFWorkbook), by auto-detecting from the supplied input.
Reading excel file in Java using Apache POI example
In the example following excel spreadsheet is being read which has two sheets- Users and Books.
For the first sheet after reading each row in the excel sheet an object of type User is created and added to an ArrayList. For the second sheet cell values are displayed on the console.
Model class (User.java) whose objects are created by reading each row of the excel sheet.
public class User {
private String firstName;
private String lastName;
private String email;
private Date DOB;
public User() {
}
public User(String firstName, String lastName, String email, Date DOB) {
this.firstName = firstName;
this.lastName = lastName;
this.email = email;
this.DOB = DOB;
}
public String getFirstName() {
return firstName;
}
public void setFirstName(String firstName) {
this.firstName = firstName;
}
public String getLastName() {
return lastName;
}
public void setLastName(String lastName) {
this.lastName = lastName;
}
public String getEmail() {
return email;
}
public void setEmail(String email) {
this.email = email;
}
public Date getDOB() {
return DOB;
}
public void setDOB(Date dOB) {
DOB = dOB;
}
}
Class for reading excel file in Java.
import java.io.FileInputStream;
import java.io.IOException;
import java.text.ParseException;
import java.util.ArrayList;
import java.util.Date;
import java.util.Iterator;
import java.util.List;
import org.apache.poi.EncryptedDocumentException;
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.netjs.Model.User;
public class ReadExcel {
private static final String PATH_TO_EXCEL="resources\\user.xlsx";
public static void main(String[] args) {
new ReadExcel().readExcel(PATH_TO_EXCEL);
}
private void readExcel(String pathToExcel) {
try {
Workbook workbook = WorkbookFactory.create(new FileInputStream(pathToExcel));
// If you have only one sheet you can get it by index of the sheet
//Sheet sheet = workbook.getSheetAt(0);
Iterator<Sheet> sheetItr = workbook.sheetIterator();
while(sheetItr.hasNext()) {
Sheet sheet = sheetItr.next();
// For Users sheet create List of objects
if(sheet.getSheetName().equals("Users")) {
readExcelSheet(sheet);
}else {
// For other sheet just print the cell values
printExcelSheet(sheet);
}
}
} catch (EncryptedDocumentException | IOException | ParseException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
private void readExcelSheet(Sheet sheet) throws ParseException{
System.out.println("Starting to read sheet- " + sheet.getSheetName());
Iterator<Row> rowItr = sheet.iterator();
List<User> userList = new ArrayList<>();
// Iterate each row in the sheet
while(rowItr.hasNext()) {
User user = new User();
Row row = rowItr.next();
// First row is header so skip it
if(row.getRowNum() == 0) {
continue;
}
Iterator<Cell> cellItr = row.cellIterator();
// Iterate each cell in a row
while(cellItr.hasNext()) {
Cell cell = cellItr.next();
int index = cell.getColumnIndex();
switch(index) {
case 0:
user.setFirstName((String)getValueFromCell(cell));
break;
case 1:
user.setLastName((String)getValueFromCell(cell));
break;
case 2:
user.setEmail((String)getValueFromCell(cell));
break;
case 3:
user.setDOB((Date)getValueFromCell(cell));
break;
}
}
userList.add(user);
}
for(User user : userList) {
System.out.println(user.getFirstName() + " " + user.getLastName() + " " + user.getEmail() + " " + user.getDOB());
}
}
// This method is used to print cell values
private void printExcelSheet(Sheet sheet) throws ParseException{
System.out.println("Starting to read sheet- " + sheet.getSheetName());
Iterator<Row> rowItr = sheet.iterator();
while(rowItr.hasNext()) {
Row row = rowItr.next();
if(row.getRowNum() == 0) {
continue;
}
Iterator<Cell> cellItr = row.cellIterator();
while(cellItr.hasNext()) {
Cell cell = cellItr.next();
System.out.println("Cell Type- " + cell.getCellType().toString() + " Value- " + getValueFromCell(cell));
}
}
}
// Method to get cell value based on cell type
private Object getValueFromCell(Cell cell) {
switch(cell.getCellType()) {
case STRING:
return cell.getStringCellValue();
case BOOLEAN:
return cell.getBooleanCellValue();
case NUMERIC:
if(DateUtil.isCellDateFormatted(cell)) {
return cell.getDateCellValue();
}
return cell.getNumericCellValue();
case FORMULA:
return cell.getCellFormula();
case BLANK:
return "";
default:
return "";
}
}
}
Output
Starting to read sheet- Users Jack Reacher abc@xyz.com Sat Jul 12 00:00:00 IST 1975 Remington Steele rs@cbd.com Thu Apr 28 00:00:00 IST 2016 Jonathan Raven jr@sn.com Thu Jan 06 00:00:00 IST 1966 Starting to read sheet- Books Cell Type- STRING Value- Five Little Pigs Cell Type- NUMERIC Value- 12.56 Cell Type- STRING Value- And Then There Were None Cell Type- NUMERIC Value- 15.89 Cell Type- STRING Value- Dumb Witness Cell Type- NUMERIC Value- 22.0 Cell Type- STRING Value- Curtain Cell Type- NUMERIC Value- 18.99
That's all for this topic How to Read Excel File in Java Using Apache POI. If you have any doubt or any suggestions to make please drop a comment. Thanks!
>>>Return to Java Programs Page
Related Topics
You may also like-