How do you export a JasperReport to an Excel file with multiple worksheets?

Question

We have a report that the customer would like to have exported to an excel format where it has multiple worksheets. Essentially the two queries share the same parameters, but everything else is different.

In jasper-reports how do you export to an excel file with multiple worksheets (ideally from different data sources)?

Answer 1

Thanks to this thread it really was easier for me to create an Excel export with multiple sheets. What I found out was that you could use the following:

ArrayList<JasperPrint> list = new  ArrayList<JasperPrint>();
list.add(jp1); list.add(jp2);
exporter.setParameter(JRXlsExporterParameter.JASPER_PRINT_LIST, list);

and the exporter will automatically use every JasperPrint object to construct each sheet; also the name of the Jasper report (as specified in the jrxml file) is used as the name of each sheet.

Currently this solution works on my local project, so I just wanted to let you know.

Answer 2

Thanks to belisarius link we seem to have figured it out. The basics of how to do it is create your JasperPrint objects for each sheet as you normally would. So you have:

JasperPrint firstWorkSheet = ...;
JasperPrint secondWorkSheet = ...;

The JasperPrint objects are already filled with the datasource at this point. Then you do:

List<JRPrintPage> pages = new ArrayList<JRPrintPage>(secondWorkSheet.getPages());
int i = firstWorkSheet.getPages().size();
for (int count = 0; count < pages.size(); count++) {
    firstWorkSheet.addPage(i, (JRPrintPage) pages.get(count));
    i++;
}

What this does it sets i to the number of pages currently in the firstWorkSheet (which should be one). Then it loops thourgh the pages in the secondWorkSheet and adds them to the firstWorkSheet.

Make sure in you jasperReport you have it set to print as one page for each of the work sheet jrxml files and you should be good to go. I will come update this if anything changes, but this should work.

UPDATE:

Discovered you need to use net.sf.jasperreports.engine.export.ooxml.JRXlsxExporter

instead of

net.sf.jasperreports.engine.export.JRXlsExporter

as there seems to be an issue when exporting to multiple work sheets.

Also the setting in the jrxml file for isIgnorePagination needs to be:

isIgnorePagination="true"

so that each jrxml file is exported as a single page.

Then you need to set JRXlsExporterParameter.IS_ONE_PAGE_PER_SHEET parameter to true so it breaks out each page to a separate worksheet.

Answer 3

As per current version 6.1.1, JRXlsExporter.setParameter is deprecated. It should be replaced by JRXlsExporter.setExporterInput. So, the updated code would be:

ArrayList<JasperPrint> sheets = new ArrayList<JasperPrint>();
sheets.add(sheet1); 
sheets.add(sheet2);

exporter.setExporterInput(SimpleExporterInput.getInstance(sheets));