Read Excel through Java using XSSF and SAX (Apache POI)

read excel through java using xssf and saxRead Excel through Java using XSSF and SAX (Apache POI); ready to use Java library/classes for reading XLSX file into Java List Object or process row wise data conveniently.  You may think how this article differs from plethora of articles over internet.

Of-course it has uniqueness within:

  • Ready to use Java Classes for reading excel Workbook, as simple!
  • Advantages of XSSF Streaming API type (aka SAX); CPU & Memory efficiency for reading
  • Simply described usage & benefits via Test classes ExcelWorkSheetHandlerTest &  ExcelWorkSheetRowCallbackHandlerTest
  • Javadocs for ExcelReader, ExcelWorkSheetHandler<T> &  ExcelXSSFRowCallbackHandler
  • Excel Template Header Validation (Row Zero is header)
  • Skip No. of rows
  • Excel Callback handlers for in-depth processing/handling excel row values – ExcelSheetCallback and ExcelRowContentCallback

Obtaining ExcelReader Library & Source Code

For Maven based project can easily refer below pom definition:

Need an excelReader jar file instead, click here to download.

ExcelReader project scoure code as excelReader-master.zip, click here to download OR alternative you can clone it from GitHub-


How to Use ExcelReader

It is simple and easy to utilize and take advantage of theses classes.  Will describe usage in two steps:

Note: ExcelWorkSheetHandler implements the Interface XSSFSheetXMLHandler.SheetContentsHandler for processing the excel work sheet.

Example Code from ExcelWorkSheetHandlerTest.java


Example code from ExcelWorkSheetRowCallbackHandlerTest.java

  • Thanks for the sample code. It only took a tweak or two (change the map values and modify the PersonVO setters and getters)to be able to read my .xlsx file. Built using NetBeans 7.2. Needed two jar files not included with Tomcat and NetBeans dom4j-1.x.jar and xmlbeans-2.x.0.jar

    • You’re Welcome Jim, thanks for the details of built using NetBeans.

      Cheers,
      Jeeva

      • Christopher Daniel

        Hi Jeevan,
        Your example is really awesome. But the example ExcelRowCallbackDemo.java is giving only the first sheet result

        • Thanks. I just noticed that. Will add support for sheet wise and all sheets processing in day or two.

          Will be available in v1.3 :)

        • @disqus_EA7G6Oixi3:disqus – excelReader v1.2 version is released. I have published the library to Maven Central Repo too.

  • Ashwath

    Hi Jeeva,

    Could you please let me know, what are the library files I need to use in-order to use this code snippet.

    Thanks

    Ashwath

  • Akshay

    Hi Jeeva,

    I need read all type of excel file and then convert into CSV. Which API should i use to read both type of excel’s including the format as xls and xlsx.

    Regards,

    Akshay

    • Hello Akshay –

      It’s not feasible with unified code, since the file formats are fundamentally different. You’ll have to write code using the HSSF package to read XLS files and XSSF package to read XLSX.

      Cheers,
      Jeeva

  • Sujith krishnan

    Hi Jeeva,
    how i can get sheet name using poi event model

  • Ramya A

    Hi Jeeva,

    Is it possible to get a specific row record without looping thru entire rows?

  • @ramyaanand:disqus just to clarify, Let’s say sheet has 20 rows and you want to get 12th row alone from sheet using excelReader library or POI library directly?

    Cheers,
    Jeeva

  • Carlos De La Vega König

    Hi Jeeva,
    Because I get GRAVE: Unknown property ‘Name’ if my class property exists, getter and setter, and my cellMapping is correct.
    Grettings

  • Virendra Kumar

    Hi Jeeva

    I am getting following exception when implementing this code

    in thread “main” java.lang.AbstractMethodError: com.myjeeva.poi.ExcelWorkSheetRowCallbackHandler.cell(Ljava/lang/String;Ljava/lang/String;Lorg/apache/poi/xssf/usermodel/XSSFComment;)V

  • @disqus_UU6HAkMxG6:disqus – Typically when you use row callback handler, it will not use much memory. This is odd, can you try to reading that excel file with separate program and then integrate with your application, it might help you to figure memory issue.

    Cheers, Jeeva

    • Virendra Kumar

      Thanks jeeva,
      My code is successfully executed and now I am able to read Xlsx file without any error

      • Matthew N

        Hi Virendra,
        how did you solve the AbstractMethodError?
        Cannot get it to work and already checked my library version etc…

        Jeeva, are you still developing 1.3…?

        • @disqus_DpbxYBELZy:disqus Still, I maintain this library. There is gap happen in the release. In upcoming days I will release v1.3 with enhancements from issues and will add further options for excel processing.

          For now kindly try with library version used in the POM file, I’m sure it will do excel sheet processing. Will upgrade library version later on (or you can also do upgrade of library and submit pull request, I’m glad to accept it).

          Cheers,
          Jeeva

          • Matthew N

            Thanks for your fast reply.
            Looking forward to the 1.3 release :-).
            I have a password protected excel sheet that i decrypt with Apache POI which gives back an inputStream. I saw you used POI 3.9, but that version couldn’t decrypt password protected sheets, that’s why i need to use the latest version.
            Could that be the issue?

  • Ramakant Bhoi

    Hello thanks for your article..it worked for me smoothly…
    i have one query- can i read dropdown from excel with this code or adding extra any code? can u help me with it if you have?

    if anybody wants my working code you can ask..free to help
    thanks and regards
    ramakant bhoi

  • Shobhit

    Hi Jeeva,

    How do i accomplish row by row comparison of two excel files (huge excel files) using these libraries?

    • @disqus_OxdGXYPqj8:disqus – Please make use of ExcelWorkSheetRowCallbackHandler to read huge excel files row by row. This way you get one row at a time. Also have a look on ExcelWorkSheetRowCallbackHandlerTest.java for how to use it.

      Cheers,
      Jeeva

      • Shobhit

        Jeeva, i tried using RowCallBackHandler. But in excel comparison, i need to open two excel files. How do i call the ExcelReader.process function on both the excel sheets simultaneously, do the comparison and then move on to the next row.
        Also, data might not be on the same rownumber in both sheets. It is possible that the id that exists on row 10 in sheet 1 exists in row 13 in sheet

        • @disqus_OxdGXYPqj8:disqus Let me explain clearly. You will not able to use excelReader library directly. You need to customize it for your need.
          Note: First of all don’t use RowCallBackHandler for large files, instead use above suggested one.

          My view: Typically for comparison related work, you need to have sorted excel sheet or you have to sort programmatically else you end up scenario you have described “id exits in row 10…”.

          Please have a look read (https://github.com/jeevatkm/excelReader/blob/master/src/main/java/com/myjeeva/poi/ExcelReader.java#L152) method, it will give you an idea for you goal.

          Cheers,
          Jeeva

  • FB

    Hi,
    first all: good job!
    One note: the library begins to be old. Version 1.2 works with POI 3.9 only as dependency, recent versions of POI do not work because some POI’s methods has been changed.
    Do you intend to relase a new version of ExcelReader?