Report Generation Using Jxls v2

What is Jxls? 

Jxls is a Java library developed as a wrapper around existing open source projects known by the name of Apache POI and Java Excel API, to generated Excel reports quickly. 

Why Jxls, if Apache POI is already there?

We often come across a general scenario where we want a report to be highly presentable and crisp on information. For such data formatting and layout, one ends up with tonnes of code using low-level libraries like Apache POI or has to do it manually while generating such a report. With Jxls one can provide all these formattings in the form of an Excel template. Along with the layout details, this template contains instructions for data manipulation, coded in a special markup language for Excel sheets. 
Apart from generating reports, it is also helpful in uploading bulk data using Excel.

How does it work?

For the sake of simplicity and existing user base, we will include dependencies for Apache POI only. Otherwise, we have an option to do the same for Java Excel API.

First of all, let us see how the bulk upload will work.

• Add these dependencies to your pom file.

  <dependency>
  <groupId>org.jxls</groupId>
  <artifactId>jxls-reader</artifactId>
  <version>2.0.2</version>
  </dependency>
   
  <dependency>
  <groupId>org.jxls</groupId>
  <artifactId>jxls</artifactId>
  <version>2.2.6</version>
  </dependency>
   
  <dependency>
  <groupId>org.jxls</groupId>
  <artifactId>jxls-poi</artifactId>
  <version>1.0.5</version>
  </dependency>

• Add XML configuration file which will help Jxls reader to map Excel data to Java class. The configuration will look like this:

  <?xml version="1.0" encoding="ISO-8859-1"?>
  <workbook>
    <worksheet name="Sheet1">
      <section startRow="1" endRow="3">
      </section>
      <loop startRow="4" endRow="4" items="students" var="student" varType="ttn.example.Student">
        <section startRow="4" endRow="4">
          <mapping row="4" col="0">student.admissionNumber</mapping>
          <mapping row="4" col="1">student.centerCode</mapping>
          <mapping row="4" col="2">student.admissionDate</mapping>
          <mapping row="4" col="3">student.firstName</mapping>
          <mapping row="4" col="4">student.lastName</mapping>
          <mapping row="4" col="5">student.dob</mapping>
          <mapping row="4" col="6">student.gender</mapping>
          <mapping row="4" col="7">student.nationality</mapping>
        </section>
        <loopbreakcondition>
          <rowcheck offset="0">
            <cellcheck offset="0"></cellcheck>
          </rowcheck>
        </loopbreakcondition>
      </loop>
    </worksheet>
  </workbook>

• Create a Java Class Student with mentioned fields along with their getter and setter methods.

• Code to read the uploaded Excel file.

  public List importStudentRecords(MultipartFile file)
  {
    Map<String, ArrayList> beans = new HashMap<>();
    beans.put("students", new ArrayList());
    try
    {
      ReaderBuilder.buildFromXML(ExcelImportService.class.getClassLoader()
                   .getResourceAsStream("student.xml"))
                   .read(new ByteArrayInputStream(file.getBytes()), beans);
    }
    catch (Exception exp)
    {
      logger.error("Error importing records : {}", exp);
    }
    return beans.get("students");
  }
  

• Your upload sheet will look like this.

Now let us take a look at report generation.

• The dependencies will remain same here. Your excel template will look like this.
  • The yellow coloured comment boxes define how the data should be parsed on the template.
  • The first comment in cell A1 states the area to be parsed which is H5 here.
  • The second comment in cell A5 defines an iterative instruction to print the student records.

• Code to generate the Excel report.

  Path dirpath = Paths.get(exportDirectory);
  String filename = dirpath.resolve(UUID.randomUUID().toString() + ".xls").toString();
  try (InputStream is = StudentService.class.getClassLoader().getResourceAsStream("student.xls"))
  {
   try (OutputStream os = new FileOutputStream(filename))
   {
     Context context = new Context();
     context.putVar("students", students);
     JxlsHelper.getInstance().processTemplate(is, os, context);
   }
  }
  return filename;

Jxls working example

Hope this blog was instrumental in building an understanding of report generation using Jxls, version 2 and how it can help in uploading bulk data using Excel.