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:

Related Posts

  • Make requirements.txt in Python
    03 May

    Make requirements.txt in python

    Table of ContentsWhat is the requirements.txt file in Python?Ways to make requirements.txt file in PythonUsing the pip to make requirements.txt in PythonUsing the conda command to make requirements.txt file in PythonUsing the pipreqs package to make requirements.txt file in PythonConclusion What is the requirements.txt file in Python? Every package is also updated regularly and features […]

  • Create an array of 1 to 10 in Python
    03 May

    Create an Array of 1 to 10 in Python

    Table of ContentsIntroductionHow to create an array of 1 to 10 in Python?Using the range() function to create an array of 1 to 10 in Python.Using list comprehension along with the range() function to create an array of 1 to 10 in Python.Using a user-defined function to create an array of 1 to 10 in […]

  • Remove Urls from String in Python
    03 May

    Remove Urls from Text in Python

    Table of ContentsIntroductionWays to remove URLs from Text in PythonUsing the re.sub() function to remove URLs from Text in PythonUsing the re.findall() function to remove URLs from Text in PythonUsing the re.search() function to remove URLs from Text in PythonUsing the urllib.urlparse class to remove URLs from Text in PythonConclusion In this post, we will […]

  • Create a list from 1 to 100 in Python
    03 May

    Create a List from 1 to 100 in Python

    Table of ContentsWays to create a list from 1 to 100 in PythonUsing the range() function to create a list from 1 to 100 in PythonUsing the numpy.arange() function to create a list from 1 to 100 in PythonUsing the for loop with range() to create a list from 1 to 100 in PythonConclusion In […]

  • How to initialize an array in Constructor in C++
    23 April

    How to initialize an Array in Constructor in C++

    Table of ContentsArraysConstructors of a ClassWays to initialize an array in Constructor in C++Initialize an array in Constructor With std::fill()Initialize an array in Constructor Using for LoopInitialize an array in Constructor Using Member Initializer ListConclusion This article discusses methods of initializing an array in constructor in C++. Let us first discuss arrays and constructors in […]

  • Set an array to another array in java
    23 April

    Set an Array Equal to Another Array in Java

    Table of ContentsSetting an Array Variable Equal to Another Array VariableSet an Array Equal to Another Array in Java Using the clone() MethodSet an Array Equal to Another Array in Java Using the arraycopy() MethodSet an Array Equal to Another Array in Java Using the copyOf() MethodSet an Array Equal to Another Array in Java […]

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.