jxls collection printing order

Question

This is my excel template. Unfortunately, employee.email is ArrayList and can have more than one value.

The result becomes

Actually, aaa1.gmail.com and aaa2.gmail.com belong to AAA and similarly, bbb1.gmail.com and bbb2.gmail.com belong to BBB. The excel output is very misleading in this case.

Is it possible to get as below?

Since I am very new to jxls, any help is really appreciate.

Answer 1

First you need to have a POJO like below to hold the data.

public class Employee {
    private int id = 0;

    private String name = null;

    private List<String> mails = new ArrayList<String>();

    // getters & setters
}

Now create data objects that you want to display in your excel file

Employee emp1 = new Employee();
emp1.setId(1);
emp1.setName("AAA");
emp1.getMails().add("[email protected]");
emp1.getMails().add("[email protected]");

Employee emp2 = new Employee();
emp2.setId(2);
emp2.setName("BBB");
emp2.getMails().add("[email protected]");
emp2.getMails().add("[email protected]");

List<Employee> employees = new ArrayList<Employee>();
employees.add(emp1);
employees.add(emp2);

then add the data into java.util.Map

Map<String, List<Employee>> beanParams = new HashMap<String, List<Employee>>();
beanParams.put("employees", employees);

then create XLSTransformer object and set the source file, destination file and the map which is holding your data

XLSTransformer former = new XLSTransformer();
former.transformXLS(srcFilePath, beanParams, destFilePath);

you need to use two <jx:forEach> tags to iterate over the list which is in your map and then you can set the values into your result excel file.

Change your excel template file with the following

and the final result will look like