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]
Posts
