In this post I'm going to demonstrate how to read excel file content from Apache POI library. When consider about Microsoft Excel there are two main versions in excel files. One is XLS format which results excel files save on Excel 1997-2003 format and other one is XLSX format which results excel files save Excel workbook format. To read these two formats we need to implement it in two different ways. I'll explain both ways in this post.
Prerequisites
- You should have install java 1.7.
- You should have Eclipse installed in your PC.
- Your PC should setup Maven installed and configured.
First lets create maven project in Eclipse. Go to File -> New -> Maven Project then tick the check fox for the Create Simple Project (Skip archetype selection) as show on below figure and then click next. On next screen just provide some name for Group ID and Artifact ID then click finished.
First of all we need to add Apache POI dependencies in to project. (https://poi.apache.org/download.html from this link you can get latest version details. For this post I'll use current latest version which is 3.15) Then add following dependencies for your POM.xml file. This should be based on the file format you are going to read as mentioned in below.
For excel format XLS (1997-2003)
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>3.15</version>
</dependency>
For excel format XLSX(Workbook)
<dependency> <groupId>org.apache.poi</groupId> <artifactId>poi-ooxml</artifactId> <version>3.15</version></dependency><dependency> <groupId>org.apache.poi</groupId> <artifactId>poi-ooxml-schemas</artifactId> <version>3.15</version></dependency>
Following main class can be used to read a excel file specified path.
package com.sample;
import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.util.Iterator;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
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.xssf.usermodel.XSSFWorkbook;
public class ExcelReader {
public static void main(String[] args) {
try {
String excelFilePath = "src/main/java/com/sample/Orders.xlsx";
FileInputStream inputStream = new FileInputStream(new File(excelFilePath));
System.out.println(excelFilePath);
Workbook workbook = getRelevantWorkbook(inputStream, excelFilePath);
Sheet firstSheet = workbook.getSheetAt(0);
Iterator<Row> iterator = firstSheet.iterator();
while (iterator.hasNext()) {
Row nextRow = iterator.next();
Iterator<Cell> cellIterator = nextRow.cellIterator();
while (cellIterator.hasNext()) {
Cell cell = cellIterator.next();
switch (cell.getCellType()) {
case Cell.CELL_TYPE_STRING:
System.out.print(cell.getStringCellValue());
break;
case Cell.CELL_TYPE_NUMERIC:
System.out.print(cell.getNumericCellValue());
break;
case Cell.CELL_TYPE_BOOLEAN:
System.out.print(cell.getBooleanCellValue());
break;
default:
break;
}
System.out.print(" ");
}
System.out.println();
}
workbook.close();
inputStream.close();
} catch (IOException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
private static Workbook getRelevantWorkbook(FileInputStream inputStream, String excelFilePath) throws IOException
{
Workbook workbook = null;
if (excelFilePath.endsWith("xls")) {
workbook = new HSSFWorkbook(inputStream);
} else if (excelFilePath.endsWith("xlsx")) {
workbook = new XSSFWorkbook(inputStream);
} else {
throw new IllegalArgumentException("Incorrect file format");
}
return workbook;
}
}
I will create excel file with following contents to read from our application.
When you run the main class you will be able to get similar output to below figure,
You can access sample project from following GIT hub location.
