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

  • Print blank line in Python
    23 September

    Print blank line in Python

    Table of ContentsWhat is a blank line in Python?Ways to print blank line in PythonUsing an empty print() functionUsing the print() function with newline characterUsing the print() function with an empty stringUsing the print() function with the sep parameterWays to print multiple blank lines in PythonUsing the print() function with multiple newline charactersUsing the print() […]

  • 17 September

    Spectrogram in Python

    Table of ContentsSpectrogramPlotting Spectrogram using the matplotlib.pyplot.specgram function of the Matplotlib Library.Plotting Spectrogram using the scipy.signal.spectrogram function of the SciPy Library Spectrogram A spectrogram is a wave-like graph which is used to represent measures like loudness, frequencies, and other signals that change over time. With the help of a spectrogram, these signals and measures are […]

  • 17 September

    27 Best and Safe ROM Sites in 2021

    Table of ContentsThe Best and Safe ROM Sites in 2021DopeROMsCOOL ROMROMsmaniaGamulatorROM HustlerRetrosticROMspediaROMs WorldROM Nation ROMUIationROMs UniverseClassic GameROMsCDROMance Emulator Game     EmuparadiseWoWROMsEmulator ZoneNitroblogVimm’s LairFree ROMsOther Working Safe Rom Sites in 2021Frequently Asked Questions Are there any free and safe ROM sites?What are the safest ROM sites?Is it legal to download a ROM?Wrapping Up Do you still wish to play […]

  • 17 September

    Write list to CSV in Python

    Table of ContentsUsing the write() function to write a list to a CSV fileUsing the csv module to write a list to a CSV fileUsing the numpy module to write a list to a CSV fileUsing the numpy.savetxt() function to write a list to a CSV fileUsing the numpy.tofile() function to write a list to […]

  • Python add commas to number
    11 September

    Python add commas to number

    Table of ContentsUsing the format() function to add commas to numbers in PythonUsing the fstrings to add commas to numbers in PythonUsing the regular expressions to add commas to numbers in PythonUsing the locale module to add commas to numbers in Python Python allows us to format values to get the final result in our […]

  • 11 September

    RSA Encryption and Decryption in Java

    Table of ContentsIntroductionGenerate RSA key pairEncrypt a random textDecrypt the random textConclusion Introduction RSA is a short form for Rivest, Shamir, and Adleman, are the people who first publicly described it in 1977. It is an algorithm for asymmetric cryptography which involves the use of two keys. A public key, which can be known to […]

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. Required fields are marked *

Subscribe to our newletter

Get quality tutorials to your inbox. Subscribe now.