How to Read Excel File in Java using POI

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

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

Your email address will not be published. Required fields are marked *