In this tutorial, we will learn to how to read excel file in Java using Apache POI. POI stands for Poor Obfuscation Implementation, is a powerful Java library which works with different Microsoft Office formats such as Excel, Word etc. Let’s dig into the code now.
Apache POI can read both Excel formats XLS (Excel 2003 and earlier) and XLSX (Excel 2007 and later). In order to use POI we need to have the following dependencies added in your project.
- poi-3.15.jar
- poi-ooxml-3.15.jar
- poi-ooxml-schemas-3.15.jar
- xmlbeans-2.6.jar
If you are running on maven add the below dependency to your pom.xml
<dependency> <groupId>org.apache.poi</groupId> <artifactId>poi</artifactId> <version>3.15</version> </dependency> <dependency> <groupId>org.apache.poi</groupId> <artifactId>poi-ooxml</artifactId> <version>3.15</version> </dependency>
Read Excel File in Java using POI – XLS format (2003 or earlier)
package com.javainterviewpoint;
import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.util.Iterator;
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;
public class XLSReader
{
public static void main(String[] args)
{
try
{
String excelPath = "C:\\Jackson\\Employee.xls";
FileInputStream fileInputStream = new FileInputStream(new File(excelPath));
// Create Workbook instance holding .xls file
HSSFWorkbook workbook = new HSSFWorkbook(fileInputStream);
// Get the first worksheet
HSSFSheet sheet = workbook.getSheetAt(0);
// Iterate through each rows
Iterator<Row> rowIterator = sheet.iterator();
while (rowIterator.hasNext())
{
// Get Each Row
Row row = rowIterator.next();
// Iterating through Each column of Each Row
Iterator<Cell> cellIterator = row.cellIterator();
while (cellIterator.hasNext())
{
Cell cell = cellIterator.next();
// Checking the cell format
switch (cell.getCellType())
{
case Cell.CELL_TYPE_NUMERIC:
System.out.print(cell.getNumericCellValue() + "\t");
break;
case Cell.CELL_TYPE_STRING:
System.out.print(cell.getStringCellValue() + "\t");
break;
case Cell.CELL_TYPE_BOOLEAN:
System.out.print(cell.getBooleanCellValue() + "\t");
break;
}
}
System.out.println("");
}
} catch (IOException ie)
{
ie.printStackTrace();
}
}
}
- Create HSSFWorkbook instance from the Excel sheet
HSSFWorkbook workbook = new HSSFWorkbook(fileInputStream);
- Get into the desired sheet using getSheetAt() method
HSSFSheet sheet = workbook.getSheetAt(0);
- Iterate the sheet to get the individual rows.
Iterator rowIterator = sheet.iterator();
- Iterate over the rows which is acquired above to get the individual cells.
Iterator cellIterator = row.cellIterator();
- Finally, display the output based on the format of the cell.
switch (cell.getCellType())
{
case Cell.CELL_TYPE_NUMERIC:
System.out.print(cell.getNumericCellValue() + "\t");
break;
case Cell.CELL_TYPE_STRING:
System.out.print(cell.getStringCellValue() + "\t");
break;
case Cell.CELL_TYPE_BOOLEAN:
System.out.print(cell.getBooleanCellValue() + "\t");
break;
}
Output :
Read Excel File in Java using POI – XLSX format (2007 or later)
In order to read XLSX file format we just need to replace HSSF to XSSF in the above code.
package com.javainterviewpoint;
import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.util.Iterator;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
public class XLSXReader
{
public static void main(String[] args)
{
try
{
String excelPath = "C:\\Jackson\\Employee.xlsx";
FileInputStream fileInputStream = new FileInputStream(new File(excelPath));
// Create Workbook instance holding .xls file
XSSFWorkbook workbook = new XSSFWorkbook(fileInputStream);
// Get the first worksheet
XSSFSheet sheet = workbook.getSheetAt(0);
// Iterate through each rows
Iterator<Row> rowIterator = sheet.iterator();
while (rowIterator.hasNext())
{
// Get Each Row
Row row = rowIterator.next();
// Iterating through Each column of Each Row
Iterator<Cell> cellIterator = row.cellIterator();
while (cellIterator.hasNext())
{
Cell cell = cellIterator.next();
// Checking the cell format
switch (cell.getCellType())
{
case Cell.CELL_TYPE_NUMERIC:
System.out.print(cell.getNumericCellValue() + "\t");
break;
case Cell.CELL_TYPE_STRING:
System.out.print(cell.getStringCellValue() + "\t");
break;
case Cell.CELL_TYPE_BOOLEAN:
System.out.print(cell.getBooleanCellValue() + "\t");
break;
}
}
System.out.println("");
}
} catch (IOException ie)
{
ie.printStackTrace();
}
}
}
Output :
Employee ID Employee Name Country 1.0 Robert Australia 2.0 Peter England 3.0 Sam Australia 4.0 Domic Brazil 5.0 Godwin India
Convert Excel To Java
In order to convert Excel To Java, we will follow the same above procedure instead of displaying it out we will have a POJO class for each property and set the value to it.
package com.javainterviewpoint;
import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.util.ArrayList;
import java.util.Iterator;
import java.util.List;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
public class ConvertExcelToJava
{
public static void main(String[] args)
{
List employees = ConvertExcelToJava();
for(Employee employee : employees)
{
System.out.println("Employee Id : "+employee.getEmpId());
System.out.println("Employee Name : "+employee.getEmpName());
System.out.println("Country : "+employee.getCountry());
System.out.println(" ");
}
}
public static List ConvertExcelToJava()
{
List employeeList = new ArrayList();
try
{
String excelPath = "C:\\Jackson\\Employee.xlsx";
FileInputStream fileInputStream = new FileInputStream(new File(excelPath));
// Create Workbook instance holding .xls file
XSSFWorkbook workbook = new XSSFWorkbook(fileInputStream);
// Get the first worksheet
XSSFSheet sheet = workbook.getSheetAt(0);
// Iterate through each rows
Iterator rowIterator = sheet.iterator();
while (rowIterator.hasNext())
{
// Get Each Row
Row row = rowIterator.next();
//Leaving the first row alone as it is header
if(row.getRowNum() == 0)
continue;
// Iterating through Each column of Each Row
Iterator cellIterator = row.cellIterator();
Employee employee = new Employee();
while (cellIterator.hasNext())
{
Cell cell = cellIterator.next();
int columnIndex = cell.getColumnIndex();
switch (columnIndex+1)
{
case 1:
employee.setEmpId(cell.getNumericCellValue());
break;
case 2:
employee.setEmpName(cell.getStringCellValue());
break;
case 3:
employee.setCountry(cell.getStringCellValue());
break;
}
}
employeeList.add(employee);
}
} catch (IOException ie)
{
ie.printStackTrace();
}
return employeeList;
}
}
Output:
Employee Id : 1.0 Employee Name : Robert Country : Australia Employee Id : 2.0 Employee Name : Peter Country : England Employee Id : 3.0 Employee Name : Sam Country : Australia Employee Id : 4.0 Employee Name : Domic Country : Brazil Employee Id : 5.0 Employee Name : Godwin Country : India
Leave a Reply