Thursday, December 29, 2016

Create an Excel file using JXLS

Create an Excel file using JXLS


In my previous article, I provide an example where we can parse an Excel file using JXLS. In this article we see the reverse one i.e Create an Excel file using JXLS


To create an Excel file first we have to know what will be the columns will present in that Excel and How it maps to Java POJO properties.

To do it declaratively JXLS provide an option to Create an Excel Template

Excel Template
Excel Template is nothing but a plain excel file where we mention the Column header and POJO properties map with each cell/Column.

To map POJO properties with template cell JXLS use a special expression language Apache JEXL

Expression is enclosed by ${}

Let see How an Excel Template look like if we try to Create an Excel  from Player POJO 



Java POJO
Excel Template
Player Collection
player context
name
${player.name}
sports
${player.sports}
rank
${player.rank}
country
${player.country}


By doing this we instruct JXLS to map the Player POJO Collection with the template and iterate each Player and map it’s property with corresponding (see the above table) cell and replace the expression with actual value.


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 + "]";
   }


}


Converter:

We wrote a custom converter class where we take two argument

  1. Template File path
  2. OutPut Path (Where the newly created Excel stored)

Then we create a collection of Player Object and Transform it to Excel


PlayerBeanToExcelConverter.java

package com.example.jxls.parser;

import java.io.BufferedInputStream;
import java.io.File;
import java.io.FileInputStream;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import net.sf.jxls.transformer.XLSTransformer;

import org.jxls.reader.ReaderBuilder;
import org.jxls.reader.XLSReader;

import com.example.jxls.entity.Player;

public class PlayerBeanToExcelConverter {
   
   String templateFile;
   String destinationFile;
   
   private PlayerBeanToExcelConverter()
   {
     
   }
   
   private static PlayerBeanToExcelConverter init(){
   
      return new PlayerBeanToExcelConverter();
   }
   
   private void setExternalFileLocation(String templateFile , String destinationFile){
      this.templateFile=templateFile;
      this.destinationFile=destinationFile;
   }
   
   
   public static void convert(String templateFile , String destinationFile ) throws Exception
   {
      PlayerBeanToExcelConverter ref = init();
      ref.setExternalFileLocation(templateFile,destinationFile);
      ref.doConfigtask();
     
     
   }
   
   private List<Player>  cretePlayerList(){
     
      List<Player> players = new ArrayList<Player>();
      Player player1 = new Player();
      player1.setCountry("India");
      player1.setName("MS Dhoni");
      player1.setRank(1);
      player1.setSports("Cricket");
     
      Player player2 = new Player();
      player2.setCountry("India");
      player2.setName("PV Sindhu");
      player2.setRank(1);
      player2.setSports("Badminton");
     
      players.add(player1);
      players.add(player2);
      return players;
     
     
     
   }
   
   private void doConfigtask() throws Exception{
     
      List<Player> players = cretePlayerList();
       Map beans = new HashMap();
       beans.put("player", players);
       XLSTransformer transformer = new XLSTransformer();
       transformer.transformXLS(templateFile, beans, destinationFile);
       System.out.println("Done");
     
   }
   

}


Test the Application

package com.example.jxls.test;

import com.example.jxls.parser.PlayerBeanToExcelConverter;
import com.example.jxls.parser.PlayerParser;

public class PlayerParserTest {
   
   public static void main(String[] args) {
      try {
             PlayerBeanToExcelConverter.convert("/home/shamik/jxls/PlayerTemplate.xlsx", "/home/shamik/jxls/PlayersList.xlsx");
      } catch (Exception e) {
          // TODO Auto-generated catch block
          e.printStackTrace();
      }
   }

}


Output

A file will create under /home/shamik/jxls






Sunday, December 25, 2016

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]