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