Read Excel File using JXLS

A primer on  JXLS parsing technique with example

Often we need to parse Excel file and feed its data in java program. There are many third party libraries to parse the Excel file here we will cover the JXLS framework. By JXLS we can easily parse an excel file without writing many lines of code.

Problem Statement:

We need to parse a Players.xlsx file and convert each row to a Player Object.

JXLS Set-Up:

We need to create a maven project and add following  pom.xml file into the project.

<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">

 <modelVersion>4.0.0</modelVersion>

 <groupId>com.example</groupId>

 <artifactId>JxlsExample</artifactId>

 <version>0.0.1-SNAPSHOT</version>

 <dependencies>

     <dependency>

       <groupId>org.jxls</groupId>

       <artifactId>jxls-reader</artifactId>

       <version>2.0.2</version>

     </dependency>

</dependencies>

</project>

JXLS Three pillar components

JXLS-Reader: XLS-Reader is the main component or heart of JXLS it is created from org.apache.commons.digester3.Digester.RederBuilder class, by Reder builder we can apply a jXLS mapping file over input stream (Excel data in input stream format) and provide corresponding Java Object. So we have to provide the JXLS mapping file and Excel file whose data we are going to parse.

JXLS mapping file: To parse Excel data we need to create a simple XML file where we define the mapping between Excel cell and corresponding  Java properties which will hold the cell value.

Now JXLS framework will loop over the given Excel files row and map the POJO class with Excel row.

Let’s look how this file looks like

<?xml version="1.0" encoding="ISO-8859-1"?>

    <workbook>

       <worksheet name="Sheet1">

      <section startRow="0" endRow="0" />

            <loop startRow="1" endRow="1" items="players" var="player" varType="com.example.jxls.entity.Player">

               <section startRow="1" endRow="1">

                   <mapping row="1" col="0">player.name</mapping>

                   <mapping row="1" col="1">player.rank</mapping>

                   <mapping row="1" col="2">player.sports</mapping>

                   <mapping row="1" col="3">player.country</mapping>

               </section>

               <loopbreakcondition>

                   <rowcheck offset="0">

             <cellcheck offset="0" />

           </rowcheck>

               </loopbreakcondition>

           </loop>

       </worksheet>

    </workbook>

Here we use a loop tag and define start row as 1 and end row as 1 as we need to read the Excel header and then map each header cell with respective properties of com.example.jxls.entity.Player object.Using vartype property in loop tag we tell to JXLS use this POJO fro mapping Excel row.

We need to define a loop break condition also to tell JXLS when to exit from the loop.

Actual Excel File : We need to provide the path of the Excel file. Here is the Excel file we are going to parse.

The Example :

We need to parse above Excel file

So the mapping file look like below

<?xml version="1.0" encoding="ISO-8859-1"?>

    <workbook>

       <worksheet name="Sheet1">

      <section startRow="0" endRow="0" />

            <loop startRow="1" endRow="1" items="players" var="player" varType="com.example.jxls.entity.Player">

               <section startRow="1" endRow="1">

                   <mapping row="1" col="0">player.name</mapping>

                   <mapping row="1" col="1">player.rank</mapping>

                   <mapping row="1" col="2">player.sports</mapping>

                   <mapping row="1" col="3">player.country</mapping>

               </section>

               <loopbreakcondition>

                   <rowcheck offset="0">

             <cellcheck offset="0" />

           </rowcheck>

               </loopbreakcondition>

           </loop>

       </worksheet>

    </workbook>

The Player POJO

package com.example.jxls.entity;

import java.util.Date;

public class Player {

   

   private String name;

   private String sports;

   private int rank;

   private String country;

   public String getName() {

      return name;

   }

   public void setName(String name) {

      this.name = name;

   }

   public String getSports() {

      return sports;

   }

   public void setSports(String sports) {

      this.sports = sports;

   }

   public int getRank() {

      return rank;

   }

   public void setRank(int rank) {

      this.rank = rank;

   }

   

   public String getCountry() {

      return country;

   }

   public void setCountry(String country) {

      this.country = country;

   }

   @Override

   public String toString() {

      return "Player [name=" + name + ", sports=" + sports + ", rank=" + rank

              + ", country=" + country + "]";

   }

   

   

   

}

The Parser class

package com.example.jxls.parser;

import java.io.BufferedInputStream;

import java.io.File;

import java.io.FileInputStream;

import java.io.FileNotFoundException;

import java.io.IOException;

import java.io.InputStream;

import java.util.ArrayList;

import java.util.HashMap;

import java.util.List;

import java.util.Map;

import org.apache.poi.openxml4j.exceptions.InvalidFormatException;

import org.jxls.reader.ReaderBuilder;

import org.jxls.reader.XLSReadStatus;

import org.jxls.reader.XLSReader;

import org.xml.sax.SAXException;

import com.example.jxls.entity.Person;

import com.example.jxls.entity.Player;

public class PlayerParser {

   String mappingFile;

   String excelFile;

   

   private PlayerParser()

   {

     

   }

   

   private static PlayerParser init(){

   

      return new PlayerParser();

   }

   

   private void setExternalFileLocation(String mappingFile , String excelFile){

      this.mappingFile=mappingFile;

      this.excelFile=excelFile;

   }

   

   

   public static void parseAndDisplay(String mappingFile , String excelFile ) throws Exception

   {

      PlayerParser ref = init();

      ref.setExternalFileLocation(mappingFile,excelFile);

      List<Player> players = ref.doConfigtask();

      ref.display(players);

     

   }

   

   private void display(List<Player> players ){

      for(Player  player : players)

      {

          System.out.println(player);

      }

   }

   

   private List<Player>  doConfigtask() throws Exception{

      final XLSReader xlsReader = ReaderBuilder.buildFromXML(new File(mappingFile));

       final List<Player> result = new ArrayList<Player>();

       final Map<String, Object> beans = new HashMap<String, Object>();

       beans.put("players", result);

       InputStream inputStream = new BufferedInputStream(new FileInputStream(excelFile));

       xlsReader.read(inputStream, beans);      

       return result;

     

   }

   

}

Testing the Application

package com.example.jxls.test;

import com.example.jxls.parser.PlayerParser;

public class PlayerParserTest {

   

   public static void main(String[] args) {

      try {

          PlayerParser.parseAndDisplay("/home/shamik/jxls/PlayerMapping.xml", "/home/shamik/jxls/Players.xlsx");

      } catch (Exception e) {

          // TODO Auto-generated catch block

          e.printStackTrace();

      }

   }

}

Output:

Player [name=Virat Kohli, sports=Cricket, rank=1, country=India]

Player [name=PVSindhu, sports=Badminton, rank=2, country=India]

Player [name=Suni Chetri, sports=Football, rank=1, country=India]

Read more

Spring XML Based DI and Builder Pattern

Recently, I had faced a situation where a project using Spring framework for its Business layer and maintains the Spring dependencies in the XML file. They can not upgrade this XML based configuration to java based configuration as this project is old it will take time and they can’t afford it.

That is fine but the problem is they want to add a third party module (another module developed by other set of developers of that project family) to re-use some utilities of that library.

But when they try to incorporate aforesaid module they discovered that many classes which are exposed as services were created using Builder pattern. So we will see here How we can incorporate Builder pattern in Spring XML-based DI.

Builder Pattern :

In a brief Builder pattern is used to create complex Object,  which has some required and some optional parameters,

We create a static inner class called Builder and pass require parameters in the constructor of that builder. Then set Optional parameter calling the Setter method of that Builder.

Lastly, it has a build() method, which will produce the actual Object.

Say, We create an Employee Object using builder pattern where id and the name of the employee are mandatory but a hobby, gender, address are optional.

Code:

package com.example.advanceSpring.entity;

public class Employee {

   

    private Long id;

    private String name;

    private int gender;

    private String address;

    private String hobby;

    private Employee()

    {

       

    }

   

    public static class EmployeeBuilder{

       

        private Long id;

        private String name;

        private int gender;

        private String address;

        private String hobby;

       

        public EmployeeBuilder(Long id, String name)

        {

            this.id=id;

            this.name=name;

        }

       

        public void setGender(int gender)

        {

            this.gender = gender;

       

        }

        public void setAddress(String address)

        {

            this.address = address;

           

        }

        public void setHobby(String  hobby)

        {

            this.hobby = hobby;

           

        }

       

        public Employee build()

        {

            Employee emp = new Employee();

            emp.id=id;

            emp.name=name;

            emp.gender=gender;

            emp.address=address;

            emp.hobby=hobby;

            return emp;

                   

        }

    }

    @Override

    public String toString() {       

        return "Employee [id=" + id + ", name=" + name + ", gender="+( gender == 1?"Male":"Female") + ", address=" + address + ", hobby="

                + hobby + "]";

    }

   

   

   

}

Say, This class comes from a third party jar and we need to create Employee Object using Spring Xml based Dependency injection.

How we can achieve this?

Step 1. Register the builder class using the context of Employee class with $. So class attribute will be look like this

Class =”com.example.advanceSpring.entity.Employee$EmployeeBuilder” by doing this Spring will understand Employee builder is an inner class of Employee.

Step 2: pass the required parameters using constructor-args.

Step3: Pass the optional parameter using property tag.

Step 4: Register the Employee bean in Spring XML, While registering one thing you can notice that Employee Object constructor is private so Spring bean can’t call it by reflection. So we will use the factory-bean and  the factory-method attribute of spring xml.

factory-bean tells Spring to use the bean as a Factory class for creating the Object, so here Spring treats EmployeeBuider as a Factory of Employee Object.

factory-method tells Spring to, upon calling the method instance of the Object will return so here build() method act as factory method to create Employee Object.

The Spring  Xml file

<beans xmlns="http://www.springframework.org/schema/beans"

    xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"

    xsi:schemaLocation="http://www.springframework.org/schema/beans

    http://www.springframework.org/schema/beans/spring-beans-2.5.xsd">

    <bean id="helloBean" class="com.example.advanceSpring.HelloWorld">

        <property name="name" value="Shamik" />

    </bean>

   

    <bean id="builder" class="com.example.advanceSpring.entity.Employee$EmployeeBuilder">

        <constructor-arg value="1"/>

       <constructor-arg value="Shamik Mitra"/>

       <property name="gender" value="1"/>

       <property name="hobby" value="Blogging"/>

    </bean>

    <bean id="employee" class="com.example.advanceSpring.entity.Employee" factory-bean="builder" factory-method="build"/>

   

</beans>

Step5: Now we will test the settings.

import org.springframework.context.ApplicationContext;

import org.springframework.context.support.ClassPathXmlApplicationContext;

import com.example.advanceSpring.entity.Employee;

public class SpringTest {

   

    public static void main(String[] args) {

        ApplicationContext context = new ClassPathXmlApplicationContext(

                "SpringTest.xml");

        Employee employee = (Employee) context.getBean("employee");

        System.out.println(employee);

    }

}

Step 6:  Output:

Employee [id=1, name=Shamik Mitra, gender=Male, address=null, hobby=Blogging]

Please note that If the Setter method using Fluent Style, that is calling setter it return the instance of that object(this) then Spring can’t call this Setter as this is not the method signature Spring calls internally.

Read more