Importing data from excel made easy: Jxls library

Every application requires to upload or import data from an Excel sheet at some point of time. Different ways are used for importing data such as Apache POI, Jxls,etc.
Apache POI library: Provides the mechanism to read data cell by cell
JXLS library: xml mapping for objection from formatted Excel

A) Apache POI APIs:

It helps you to read Excel files and also write them using Java.
In case of a maven project, following dependency can be included into the pom.xml:

<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>3.9</version> // any latest version can be used
</dependency>

Some of the important POI classes that are frequently used are as below:

• The Apache POI API Basics

• HSSF: denotes the API is for working with Excel 2003 and earlier.
• XSSF: denotes the API is for working with Excel 2007 and later.

• Workbook: high level representation of an Excel workbook. Concrete implementations are: HSSFWorkbook and XSSFWorkbook.
• Sheet: Represents worksheet in Excel. Typical implementing classes are HSSFSheet and XSSFSheet.
• Row: Representation of a row in a spreadsheet. HSSFRow and XSSFRow are two concrete classes.
• Cell: Representation of a cell in a row. HSSFCell and XSSFCell are the typical implementing classes.

Another useful class FormulaEvaluator is used to evaluate the formula cells in Excel sheet.
Data validator class used to add drop downs with different criteria of data

• Writing an Excel file
  
  Writing a file using POI is very simple and involve following steps:
  
  
• Create a workbook
• Create a sheet in workbook
• Create a row in sheet
• Add cells in sheet
• Repeat step 3 and 4 to write more data

//import statements

public class WriteExcelDemo

{

public static void main(String[] args)

{

//Blank workbook

XSSFWorkbook workbook = new XSSFWorkbook();

//Create a blank sheet

XSSFSheet sheet = workbook.createSheet("Employee Data");

//Data needs to be written (Object[][])

//Iterate over data and write to sheet

Set<String> keyset = data.keySet();

int rownum = 0;

for (String key : keyset)

{

Row row = sheet.createRow(rownum++);

Object [] objArr = data.get(key);

int cellnum = 0;

for (Object obj : objArr)

{

Cell cell = row.createCell(cellnum++);

if(obj instanceof String)

cell.setCellValue((String)obj);

else if(obj instanceof Integer)

cell.setCellValue((Integer)obj);

}

}

// Write the workbook in file system
}
• Reading an Excel file

• Create workbook instance from Excel sheet
• Get to the desired sheet
• Increment row number
• Iterate overall cells in a row
• Repeat step 3 and 4 until all data is read

Let’s take a look at the code doing these steps:

public class SimpleExcelReaderExample {

public static void main(String[] args) throws IOException {
String ExcelFilePath = "Books.xlsx";
FileInputStream inputStream = new FileInputStream(newFile(ExcelFilePath));
Workbook workbook = new XSSFWorkbook(inputStream);
Sheet firstSheet = workbook.getSheetAt(0);
Iterator<Row> iterator = firstSheet.iterator();
while (iterator.hasNext()) {
Row nextRow = iterator.next();
Iterator<Cell> cellIterator = nextRow.cellIterator();

while (cellIterator.hasNext()) {
Cell cell = cellIterator.next();

switch (cell.getCellType()) {
case Cell.CELL_TYPE_STRING:
System.out.print(cell.getStringCellValue());
break;
case Cell.CELL_TYPE_BOOLEAN:
System.out.print(cell.getBooleanCellValue());
break;
case Cell.CELL_TYPE_NUMERIC:
System.out.print(cell.getNumericCellValue());
break;
}
System.out.print(" - ");
}
System.out.println();
}
workbook.close();
inputStream.close();
}
}

B) Introduction to Jxls library:

As can be observed from the code snippets above, Apache POI though used widely, follows lengthy process that involves lots of looping and conditions. This affects the understandability and the readability of the code. Especially if the Excel file to be read from or to be written to is lengthy. This is where the Jxls library comes to the rescue.

Let’s us take a look at what process does Jxls follow in order to overcome the draw backs of Apache POI.

Jxls provides special formatting for data layout and output formatting with the use of Excel templates.

In addition not all Excel features are supported and can be manipulated with API (for example macros or graphs). The suggested workaround for unsupported features is to create the object manually in an Excel template and fill the template with data after that.
All you need to do when working with Jxls is just to define all the formatting and data in an Excel template and run Jxls engine providing it with the data to fill in the template. The only code you need to write in most cases is a simple invocation of Jxls engine with proper configuration.

Write Excel files through Jxls:

To use Jxls to output collection into an Excel we need to do the following:

• Add required Jxls libraries to your project
• Create an Excel template using a special markup
• Use Jxls API to process the prepared template and fill it with data

<dependency>
<groupId>org.jxls</groupId>
<artifactId>jxls</artifactId>
<version>2.4.2</version> //use latest available version
</dependency>

Besides the dependency to core Jxls module, we need to add a dependency to an implementation of Jxls transformer engine which will execute all the underlying Java to Excel manipulations.

To use Apache POI API based transformer implementation add the following dependency:

<dependency>
<groupId>org.jxls</groupId>
<artifactId>jxls-poi</artifactId>
<version>1.0.13</version>
</dependency>

Reading data from Excel file may require the following dependency:

<dependency>
<groupId>org.jxls</groupId>
<artifactId>jxls-reader</artifactId>
<version>2.0.3</version>
</dependency>

Create an Excel template using a special markup:
The Excel template is a file that uses special markup to map collection data into the Excel column by column. For instance, let us consider sample object to be written into Excel sheet as below,

public class ObjectModel{
private int srNo;
private String objectDescription;
private String creationDate;

}

The Excel template for this object will look like,

By default Jxls supports Apache JEXL as an expression language that can be used in an Excel template to refer to Java object properties and methods. The object must be available in Jxls context under a certain key. To output the object description in a cell we can put the following text in the cell ${objectModel.objectDescription}. Basically we just surrounded Jexl expression with ${ and }. We assume that in the context there is an ObjectModel object under the objectModel key.

Use Jxls API to process the prepared template
Below code snippet shows you how the template can be used to map data into the Excel.

try(InputStream is = ObjectCollectionDemo.class.getResourceAsStream("object_collection_template.xls")) {
try (OutputStream os = new FileOutputStream("target/object_collection_output.xls")) {
Context context = new Context();
context.putVar("objectModels", objectModels); //objectModels is list of objectModel
JxlsHelper.getInstance().processTemplate(is, os, context);
}
}

Where, context.putVar("objectModels", objectModels); iterates over the collection of objectModels to map data into the corresponding columns in the Excel sheet.
In this example, we are loading the template from the classpath resource object_collection_template.xls. And the target Excel file will be written to target/object_collection_output.xls.

All the main processing is performed in a single line
JxlsHelper.getInstance().processTemplate(is, os, context);

By default JxlsHelper assumes that you want to override the template sheet with the data.
But you may also choose to generate the data at another sheet by using the following method JxlsHelper.getInstance().processTemplateAtCell(is, os, context, "Result!A1");

Read Excel files using Jxls:

Reading of Excel files require creation of mapping file in the form of xml file. This xml contains the column wise mapping of Excel sheet into Java POJO.
Mapping xml file looks as given below, let us consider the same object as above to be read from an Excel file.

<?xml version="1.0" encoding="ISO-8859-1"?>
<workbook>
<worksheet name="Sheet1">
<loop startRow="7" endRow="7" items = “objectModels” var="objectModel" varType="org.jxls.reader.sample.ObjectModel"> //startRow
and endRow specify range of rows to be mapped from Excel
<section startRow="0" endRow="6">
<mapping cell="B1">objectModel.description</mapping>// B1, A4 represent columns to be mapped
<mapping cell="A4"> objectModel.creationDate </mapping>
</section>
<loopbreakcondition>
<rowcheck offset="0">
<cellcheck offset="0">Object Models::</cellcheck>
</rowcheck>
</loopbreakcondition>
</loop>
</worksheet>
</workbook>

As we can see the root element of xml file is workbook and it can contain any number of child worksheet elements. worksheet tag should contain name attribute indicating the name of Excel worksheet which it describes (Sheet1 in our case).
worksheet element can contain any number of section and loop child elements.

section element represents a simple block of spreadsheet cells. The first and the last rows of the block are specified with startRow and endRow attributes.

loop element defines loop (repetitive) block of Excel rows. It should contain startRow and endRow attributes to specify start and end row of this repetitive block, items attribute names collection which should be populated with loop block data as it is known in our bean context map. varattribute specifies how to refer to each collection item during iteration in the inner sections. varType attribute defines full Java class name for collection item.

loop element have to contain loopbreakcondition definition. This describes break condition to stop loop iteration.

Next is a simple sample of code which uses ReaderBuilder class to apply XML mapping file to departmentdata.xls to construct XLSReader class and read XLS data populating corresponding Java beans with XLS data

InputStream inputXML = new BufferedInputStream(getClass().getResourceAsStream(xmlConfig));
XLSReader mainReader = ReaderBuilder.buildFromXML( inputXML );
InputStream inputXLS = new BufferedInputStream(getClass().getResourceAsStream(dataXLS));
ObjectModel objectModel = new ObjectModel();
List objectModels = new ArrayList();
Map beans = new HashMap();
beans.put("objectModel", objectModel);
beans.put("objectModels", objectModels);
XLSReadStatus readStatus = mainReader.read( inputXLS, beans);

Thus, it can be seen that use of jxls helps to avoid the usage of long conditional code and tedious looping in order to read or write any Excel file.

Happy reading/writing Excel sheets with Jxls!

References

• http://www.codeJava.net/coding/how-to-read-Excel-files-in-Java-using-apache-poi
• Apache POI - the Java API for Microsoft Documents
• POI API Documentation (Javadocs)
• Apache POI Quick Guide
• Apache POI HOWTO
  
• http://jxls.sourceforge.net/index.html
• http://jxls.sourceforge.net/getting_started.html
• http://jxls.sourceforge.net/Javadoc/jxls/index.html
• http://jxls.sourceforge.net/Javadoc/jxls-poi/index.html
• http://jxls.sourceforge.net/Javadoc/jxls-jExcel/index.html
• http://jxls.sourceforge.net/Javadoc/jxl