How to read excel files in java using Apache POI

In this post, we will see how to read excel in java using Apache POI example.

The Apache POI Project’s mission is to create and maintain Java APIs for manipulating various file formats based upon the Office Open XML standards (OOXML) and Microsoft’s OLE 2 Compound Document format (OLE2). In short, you can read and write MS Excel files using Java.

Some basics about Apache POI:

There are two prefixes which you encounter while reading/writing excel in java

HSSF: Used for dealing with files excel 2003 or earlier(.xls). Some of classes with HSSF prefix are HSSFWorkbook , HSSFSheet , HSSFRow and HSSFCell.

XSSF: Used for dealing with files excel 2007 or later(.xlsx). Some of classes with XSSF prefix are XSSFWorkbook , XSSFSheet , XSSFRow and XSSFCell.

Here are few classes which you need to aware of.

  • Workbook : This is high level class for representing excel workbook.
  • Sheet : This is high level class for representing excel sheet.
  • Row : This is high level class for representing excel row. It has methods which are related to row.
  • Cell: This is high level class for representing individual excel cell. It has methods which are related to cell for example : getDataType().

Dependency:

If you are using maven, then you need to add below dependency in pom.xml.

If you are not using maven, then you need to add below jars in classpath.

  • poi-3.13.jar
  • commons-codec-1.9.jar
  • poi-ooxml-3.13.jar
  • poi-ooxml-schemas-3.13.jar
  • xmlbeans-2.6.0.jar
  • stax-api-1.0.1.jar

Read excel file using poi:

Java Program:

We are going to read countries.xlsx. Its content is :

Create ReadWriteExcelMain.java as below

When you run above program, you will get following output:

Lets be more object oriented.

We will read each row and create country object. Obviously we will skip header row.
Create a class called Country.java in package com.arpit.java2blog.model

ReadExcelWithCounryMain.java
When you run above program, you will get following output:

Was this post helpful?

Related Posts

  • 25 June

    Convert Roman Number to Integer in Python

    Table of ContentsHow to Convert Roman Number to Integer in PythonHow to convert roman number to integer in python?Using the if...else statement to convert roman number to integer in Python.Using classes and a dictionary to convert roman number to integer in Python.Using the roman module to convert roman number to integer in Python.ConclusionWas this post […]

  • 11 June

    Remove Single Quotes from String in Python

    Table of ContentsRemove single quotes from string in Python.How to remove single quotes from string in Python?Using the replace() function to remove single quotes from string in Python.Using a for loop to remove single quotes from string in Python.Using the join() function to remove single quotes from string in Python.Using the re module functions to […]

  • 11 June

    Escape Backslash Character in Python

    Table of ContentsEscape backslash character in python.How to use escape backslash character in python?Using the escape backslash character in python to represent whitespace characters.Using the escape backslash character in python to turn special characters into ordinary characters.Using the escape backslash character in f-strings in Python.Using the escape backslash character in raw strings in Python.Was this […]

  • 11 June

    Remove Parentheses From String in Java

    Table of ContentsJava StringsRemove Parentheses From a String Using the replaceAll() MethodRemove Parentheses From a String by TraversingConclusionWas this post helpful? Java uses the Strings data structure to store the text data. This article discusses methods to remove parentheses from a String in Java. Java Strings Java Strings is a class that stores the text […]

  • 11 June

    Break out of function in Python

    Table of ContentsBreak out of function in PythonHow to break out of function in Python?Using the return statement to break out of function in PythonUsing the try and except block to break out of function in PythonUsing the sys.exit() function to break out of function in PythonConclusionWas this post helpful? In this post, we will […]

  • 11 June

    Check if Object Is Iterable in Python

    Table of ContentsWhat are iterables in Python?How to check if object is iterable in Python?Using the iter() function to check if object is iterable in PythonUsing the for loop to check if object is iterable in PythonUsing the isinstance() function to check if object is iterable in PythonConclusionWas this post helpful? In this post, we […]

Comments

  1. I cannot run program,i use 2010 excel version.my excel file path is under D,so
    String excelFilePath = “D:\\Countries.xlsx”;
    FileInputStream inputStream = new FileInputStream(new File(classLoader.getResource(excelFilePath).getFile()));
    i have that Exception.
    Exception in thread “main” java.lang.NullPointerException
    at com.excel.ReadExcelMain.readFileUsingPOI(ReadExcelMain.java:26)
    at com.excel.ReadExcelMain.main(ReadExcelMain.java:19)
    at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
    at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
    at java.lang.reflect.Method.invoke(Method.java:483)
    at com.intellij.rt.execution.application.AppMain.main(AppMain.java:140)
    Thank & Regards
    SuLabt

  2. Hi,
    If you are directly using absolute path of the file, please use below code
    String excelFilePath = “D:\\\\Countries.xlsx”;
    FileInputStream inputStream = new FileInputStream(new File(excelFilePath));

    Let me know if it works now
    Thanks

Leave a Reply

Your email address will not be published.

Subscribe to our newletter

Get quality tutorials to your inbox. Subscribe now.