Export of a single collection into multiple worksheets

Sometimes it is required to export every collection item into separate worksheet. jXLS provides a special way to do this.

Let's look at the following method of XLSTransformer class

 public HSSFWorkbook transformMultipleSheetsList(InputStream is, List objects, List newSheetNames, String beanName, Map beanParams, int startSheetNum)

Here

 is             - InputStream for source XLS template file
 objects        - List of beans where each list item should be exported into a separated worksheet
 newSheetNames  - Sheet names to be used for newly created worksheets
 beanName       - Bean name to be used for a list item when processing sheet
 beanParams     - Common bean map containing all other objects to be used in the workbook
 startSheetNum  - Worksheet number (zero based) of the worksheet that needs to be used to create multiple worksheets

Using of this method is simple. Here is an example

InputStream is = new BufferedInputStream(new FileInputStream("multipleSheetList.xls"));
XLSTransformer transformer = new XLSTransformer();
List sheetNames = new ArrayList();
for(int i = 0; i < departments.size(); i++){
    Department department = (Department) departments.get( i );
    sheetNames.add( department.getName() );
}
HSSFWorkbook resultWorkbook = transformer.transformMultipleSheetsList(is, departments, sheetNames, "department", new HashMap(), 0);

In this example we export each department into a separate worksheet using the first worksheet as the main template for a single department export. Every sheet will be named as corresponding department name.

You can also export many objects per each worksheet. This can be done using Maps. Just put all the objects that needs to be accessed from the corresponding worksheet into a Map. Create a List of such Map objects (one Map per each Worksheet). Then pass it to transformMultipleSheetsList() method.

In your template you have to access each object via Map using map values access notation ${map.get('object1').prop1}. As an example take a look at the following code which demonstrates this by exporting every Department object into a separate worksheet

                List sheetNames = new ArrayList();
                List maps = new ArrayList();
                for(int i = 0; i < departments.size(); i++){
                    Map map = new HashMap();
                    Department department = (Department) departments.get( i );
                    map.put("department", department );
                    sheetNames.add( department.getName() );
                    map.put("name", "Number " + i);
                    maps.add( map );
                }
                XLSTransformer transformer = new XLSTransformer();
                org.apache.poi.ss.usermodel.Workbook resultWorkbook = transformer.transformMultipleSheetsList(inputStream, maps, sheetNames, "map", new HashMap(), 0);